Documentation

Get started

• WaitScope overviewWhat WaitScope reads, what the report delivers and what it does not do: the response time profile from Oracle 10046 and deadlock traces.
• First analysis with the sample traceUpload the synthetic trace through the console, follow the processing and compare the total time, profile, diagnosis and plan with the sample report.

Generate the trace

• Turn on tracing with DBMS_MONITORTurn on 10046 tracing with waits and without binds by session, by client_id or by service, module and action, check that it is on and turn it off at the end.
• Choose the event 10046 levelThe event 10046 levels, which one WaitScope needs, how to turn it on and off with ALTER SESSION and the parameters that prevent traces without timings or truncated traces.
• Find the trace file on the serverMark the name with tracefile_identifier, find the path through V$DIAG_INFO or V$PROCESS and copy the .trc from the ADR trace folder to upload it.
• Upload a deadlock trace (ORA-00060)Find through the alert log the trace that Oracle writes on its own at ORA-00060, upload the file and see the wait-for graph, the sessions and the statement in the report.
• What a 10046 trace containsThe data a 10046 trace carries, what WaitScope keeps in the report and deletes after the analysis, and what to check in the file before uploading.

Upload

• Upload traces through the browserUpload traces from the console Analyses page: files or a folder, chunked upload with resume, the question about the process and the processing queue.
• Upload traces with the Android appInstall the app from the console Android app page, choose files or folders, receive traces from other apps and upload in the background, on Wi-Fi only if you prefer.
• Upload traces through the APIUpload a trace through API v1 with the app token: sign in, open the upload, send chunks of up to 8 MiB, complete it and follow the analysis until the report.
• Compressed files and foldersFormats accepted on upload, a whole folder, .trm files, one analysis per file in a zip or tar, a compressed file inside another and a file that is not a trace.
• Analyze several traces togetherCombine into a single analysis the files of the same operation, such as those from a tracefile_identifier switch, and read the files and sessions in the collection card.
• Limits and repeated uploadSize per upload, decompressed limit, queued and stored analyses, report retention period and what happens when the same file is uploaded again.

Read the report

• Summary: total time, period and environmentThe total time, the period, the calls, waits and statements and the trace environment at the top of the report, and how to download the report or delete the analysis.
• Diagnosis: the findings in the reportThe findings point to patterns that often explain the profile, in order of severity, with the share of total time and the page for each code.
• Response time profileThe table that splits the total time into CPU by call type, wait events and unaccounted-for time, and the card that explains each row.
• Wait classes and recursive SQLThe profile grouped by Oracle wait class and the time of recursive SQL, which a statement triggers in functions, triggers and the dictionary.
• Statements by component and by classFor each profile component and each wait class, the statements that spent the most time in it, from the exclusive profile of each statement.
• SQL statements by contributionThe list of trace statements by contribution to total time, with inclusive time, depth, executions, rows and reads.
• Statement detailThe page for a statement: its share of total time, the text with the highlighted objects, the exclusive profile, the calls and the child recursive SQL.
• Execution plan and highlighted operationsThe plan from the STAT lines, with the self time and reads of each operation, the highlighted operations and estimated versus actual rows.
• Call typesThe session's PARSE, EXEC, FETCH and CLOSE calls added up by type, with CPU, duration, reads, rows and misses, as in the tkprof total.
• Longest depth-0 callsThe application calls that took the longest, with the CPU, the moment each one started, the line in the trace and the statement of each one.
• Latency distributionThe duration of each wait event and each call type in power-of-10 buckets, to tell many fast occurrences from a few slow ones.
• Reads: disk and SSD buckets and rereadsBlock reads in buckets designed for disk and for SSD, and how many times the session read again a block it had already read in the same trace.
• Objects with the most wait and the object pageThe objects that added up the most wait in the trace and the page of each one in the console, with ready-made queries to run on the trace's database.
• Shareable texts: SQL without bindsThe statements that differ only in literals, grouped into a shape with :s and :n in place of the values, with the variants, PARSE calls and time added up.
• Deadlocks: graph, sessions and diagnosisHow to read the deadlock recorded in the trace: who was waiting for whom, the sessions, the contended row, the SQL, the PL/SQL stack and the diagnosis by lock.
• ORA errors in the traceThe ORA- codes from the ERROR and PARSE ERROR lines of the trace, what each one means, where it appeared and the link to the Oracle documentation.
• Collection: several files analyzed togetherThe card that describes the collection when several traces were analyzed together: the files, the segments of each one and the time of each session.

Find the root cause

• The response time profile methodHow to find the root cause of a slowdown: choose the operation, measure the response time with a trace, attack the largest component and measure again.
• Session waiting for the applicationThe TEMPO_NA_APLICACAO finding appears when the SQL*Net message from client wait takes up much of the total time: the time is outside the database.
• One component dominates response timeThe COMPONENTE_DOMINANTE finding appears when a single profile component holds most of the total time. See where to go next in each case.
• CPU dominates response timeThe CPU_DOMINANTE finding appears when the CPU of the calls holds most of the total time. It usually comes from excessive logical reads or from parsing.
• Unaccounted-for time within callsThe NAO_CONTABILIZADO_DENTRO finding appears when much time stays in calls with no CPU or wait: a trace without waits, CPU queueing or swapping.
• Time gaps between callsThe NAO_CONTABILIZADO_ENTRE finding appears when much time falls between calls with no recorded wait. Almost always, the fault is in the measurement.
• Measured CPU above elapsed timeThe CPU_ACIMA_DO_DECORRIDO finding appears when the unaccounted-for time within calls turns negative. It is an effect of the CPU measurement, not of the database.
• Slow single-block readsThe LEITURA_LENTA finding appears when a single-block read takes longer on average than the storage should. See whether it is all the reads or a few.
• Many single-block readsThe MUITAS_LEITURAS finding appears when single-block reads take a large share of the total time. Before speeding up the disk, check whether the plan reads too many blocks.
• Many hard parsesThe HARD_PARSE finding appears when a good part of the session PARSE calls are hard parses, or when the PARSE of SQL with literals weighs on the time. The most common cause is SQL without bind variables.
• One PARSE per executionThe PARSE_POR_EXECUCAO finding appears when a heavily executed statement does one PARSE per execution. Keeping the cursor open avoids the repeated PARSE.
• SQL with literals instead of bind variablesThe LITERAIS_SEM_BIND finding points to the same SQL repeated with different values in the text, in many variants whose PARSE weighs on the session. The fix is to use bind variables.
• Many FETCH calls with few rowsThe FETCH_PEQUENO finding appears when the application makes many FETCH calls with few rows each, on average. A larger fetch size cuts round trips.
• Row-by-row processing in PL/SQLThe LINHA_A_LINHA finding points to recursive SQL executed or read many times, one row at a time: the PL/SQL loop that calls for set-based processing.
• Waiting on a lock held by another sessionThe BLOQUEIO finding appears when the Application class weighs on the total time: how to find the session that holds the lock and fix the cause.
• Contention on internal database structuresThe CONCORRENCIA finding appears when latches, mutexes and buffer busy waits weigh on the total time: how to tie each event to its cause and fix it.
• A lot of time waiting for redo writes at commitThe COMMIT finding appears when log file sync weighs on the total time: how to tell too many commits from slow redo and what to do in each case.
• Waits tied to database configurationThe CONFIGURACAO finding appears when redo log switches, buffers, ITL or sequences weigh on the total time: the event tells which limit to adjust.
• Block transfers between RAC instancesThe CLUSTER finding appears when the gc events weigh heavily on the total time: RAC instances contending for the same blocks, and how to reduce the transfers.
• Session throttled by Resource ManagerThe RESOURCE_MANAGER finding appears when the resmgr waits weigh on the total time: how to find the plan, the group and the window that held the session.
• Plan operation that concentrates the workThe PLANO_PONTO_QUENTE finding points to the operation that concentrates the time or the reads in the plan of a heavy statement, with a hint of what to fix.
• Wrong row estimate in the planThe ESTIMATIVA_ERRADA finding shows the operation where the optimizer got the rows wrong by 10 times or more, and how to fix statistics and bind peeking.
• Slow level-0 callsThe CHAMADAS_LENTAS finding counts the application calls that took 0.1 s or more and leads to the list of the longest ones, from which you reach the statement.
• Oracle errors in the traceThe ERROS_ORACLE finding counts the ORA- errors recorded in the trace, except ORA-01403: how to see where each code appeared and what to fix.
• ORA-01403 (no data found) in the traceThe FIM_DE_DADOS finding appears when the only error in the trace is ORA-01403, the normal end of a read. See the cases where it deserves attention.
• Rollbacks in the traceThe ROLLBACK finding counts the transactions that ended in a rollback: how to tell, from the trace itself, whether work was undone or it was only a pool routine.
• Deadlock between sessions (ORA-00060)The DEADLOCK finding classifies each ORA-00060 by the lock type in the graph (TX, TM or UL) and points to the likely cause and the fix for each type.
• Trace with no time spanThe TRACE_VAZIO finding appears when the file has 10046 lines, but no call or wait with tim: there is no response time to analyze.

Case studies

• Case 1: time between calls and row-by-row SQLGuided reading of the report from a real 10046 trace: 73.6% of the time between calls, two SELECTs with literals executed 89,112 times and the trace cut in the middle of the routine.
• Case 2: session dominated by I/O waitsGuided reading of the report from a real 10046 trace: 51.4% of the time in User I/O, one query with 72% of the time and no plan in the excerpt, and a DELETE of almost 3 million rows.

Limits and privacy

• What WaitScope does not doWhat is out of WaitScope's reach: connecting to the database, monitoring, the files it rejects and what a 10046 trace cannot show.
• Data retention, deletion and privacyHow long the report is kept, when the trace is deleted, how to delete and download, who sees your analyses and how to ask for help without sending the trace.
• Hidden literals in the SQLHow the mask replaces with *** the quoted text and the numbers in the SQL you see and download, what it does not hide and how to turn each option on or off.

Reference

• GlossaryThe terms of the report and of the 10046 trace, from response time and component to hard parse, cr and cu reads and sql_id.
• 10046 trace lines that WaitScope readsEach 10046 trace line that WaitScope reads, with the fields it uses, the ones it ignores, the time unit and where everything appears in the report.
• API v1 referenceSummary of the API v1 the Android app uses: address, token, language, error codes, session, account, analysis, report and upload routes, and the limits.
• What's new in the console and the appWhat changed in each version of the WaitScope console and Android app, newest first, from the downloaded HTML that opens bit by bit in console 1.7.0 to the terms acceptance of 1.5.0.