WaitScope reads the trace that Oracle itself writes for a session and shows where its time went: how much was CPU, how much was each wait and how much each SQL statement weighed. You generate the trace in the database, upload the file through the console or the app, and the report is ready when the analysis finishes.
What WaitScope reads
- Event 10046 trace with waits, at level 8 or 12, or turned on through
DBMS_MONITORwithwaits => TRUE(turn on tracing with DBMS_MONITOR, 10046 levels). - Deadlock trace, which Oracle writes on its own when a session receives ORA-00060, either as a separate file or inside a 10046 trace (deadlock trace).
- Traces from older Oracle versions, with time in centiseconds. WaitScope detects the unit on its own, and the report shows which one it used in Time unit.
The file can go on its own, compressed as gz, bz2, zip or tar, or in a whole folder (compressed files and folders). The files of a single operation can become one analysis (analyze together).
What the report delivers
- Response time profile. The session's total time split into CPU by call type, each wait event and the time the trace does not explain. The rows add up exactly to the total time (profile).
- Wait classes. The same components grouped by Oracle wait class, such as User I/O and Commit (wait classes).
- SQL statements. Each statement by its contribution to the total time, with its calls and its own profile. Statements that differ only in literals are added up in one row, marked with the number of variants, and the statement detail shows the variants that cost the most (SQL statements).
- Plans. The plan from the STAT lines, with the time and reads of each operation and the row estimates that were off by a factor of 10 or more (execution plan).
- Diagnosis. Patterns that usually explain the profile, in order of severity: waiting for the application, slow reads, hard parse, SQL with literals instead of binds, row-by-row loops in PL/SQL, commit, locking and others (Diagnosis).
- Objects. The tables and indexes with the most wait time, with ready-made queries for you to run in your database (objects).
- Deadlocks. The wait-for graph, the sessions involved, the statement and the PL/SQL stack of the session that received ORA-00060 (Deadlocks).
The report also includes the latency distribution, disk reads, the longest calls, ORA errors and the SQL texts that differ only in literals. The sample report shows all of this from a synthetic trace. The profile follows the method described by Cary Millsap and Jeff Holt (the method).
What WaitScope does not do
- It does not install anything in the database. There is no agent, schema or connection: WaitScope only sees the file you upload.
- It is not monitoring. It analyzes the trace of one operation when you need to understand its time, and it does not collect anything continuously.
- It does not read other Oracle files. Optimizer traces (10053), incident traces, state dumps and the alert log are recognized and rejected, with an explanation of how to generate a 10046 trace.
- It does not show bind values. The bind lines in the trace are ignored.
The uploaded trace is deleted as soon as the analysis finishes, whether it succeeded or failed, and only the report remains, for the retention period (retention and privacy). With the default account settings, the SQL you see shows *** in place of quoted text and of numbers used as values (hidden literals). The full list is in what WaitScope does not do.
Where to go from here
- To see the whole path with a ready-made trace: first analysis with the sample trace.
- To generate the trace of your operation: turn on tracing with DBMS_MONITOR and find the file on the server.
- To upload: from the browser, from the Android app or through the API.
- To read the report: start with the Summary and the profile.
The console is at app.waitscope.com. During early access, request access by email to contato@waitscope.com.