Glossary

The terms of the report and of the 10046 trace, grouped by the initial letter of the term in the Portuguese edition. The names in bold are the ones the console shows; the trace fields are in 10046 trace lines that WaitScope reads.

A

finding

An item of the Diagnosis: a pattern that often explains the profile, such as waiting for the application, slow reads or hard parse. Each finding has a severity, a title and, when it applies, the share of the total time involved. The finding code, such as TEMPO_NA_APLICACAO, does not appear on screen: the API returns it in the code field. It is a lead for the analysis, not a conclusion. See Diagnosis.

action

The value of ACTION NAME, which the application reports to the database to say what it is doing. The report lists the actions of the trace in Action, on the Session and environment card, unmasked.

B

bind (bind variable)

A placeholder in the SQL text, such as :1 or :b1, whose value travels separately from the statement. With binds, the same cursor serves different values. The trace writes the values in the BINDS block, at levels 4 and 12 of 10046; WaitScope ignores this block, and the values never enter the report.

C

c

The CPU time of a call, in the c= field of the PARSE, EXEC, FETCH and CLOSE lines. In the profile, it becomes the rows CPU: PARSE dbcalls, CPU: EXEC dbcalls and so on, without the CPU of recursive calls.

call

A request from the session to the database, on a trace line with its type and figures: PARSE, EXEC, FETCH, CLOSE and, less common, UNMAP, SORT UNMAP and the LOB calls. The summary counts all of them, at any depth, in Database calls.

level-0 call

A call with dep=0, made directly by the application. Recursive calls happen inside it. The interval between the end of one level-0 call and the start of the next is time between calls: waiting for the application or unaccounted-for time between calls.

wait class

The Oracle group of the wait event (wait_class), such as User I/O, System I/O, Commit, Concurrency, Network and Idle. The profile by class adds up the components of each one, with CPU, Unaccounted-for and Unclassified apart, and the sum also gives the total time. An event WaitScope cannot classify goes into Unclassified. See wait classes and recursive SQL.

CLOSE

The call that closes the cursor. It carries CPU, duration, depth and the close type, and it is usually fast.

collection

Several trace files analyzed as one. This is the case of an application that changes tracefile_identifier and splits the same session into several files. WaitScope puts the segments in tim order; with more than one session, the total time is the sum of the time of each one. See analyze several traces together.

statement

A distinct SQL statement or PL/SQL block in the trace, by sql_id or, in a trace without it, by hash value. Distinct statements, in the summary, counts how many there are, one by one, even when the lists group into one shape those that differ only in their literals. When the count is estimated, it appears as about N.

component

Each row of the response time profile: the CPU of one call type, one wait event or one of the two unaccounted-for time rows. Added together, the components give the total time.

contribution

How much a statement weighs in the total time: the duration of its level-0 calls plus the waits between calls on its cursor. The SQL statements by contribution list is sorted by it. The difference between the total time and the sum of the contributions appears as time not attributed to any statement. Compare with inclusive.

cr

Consistent reads: blocks read in the version that is valid for the start of the query. The cr= field of calls and STAT lines, Consistent (CR) column. Added to cu, it gives the LIO.

cu

Current mode reads: blocks read in their current version, usually to change data. The cu= field, Current (CU) column. Added to cr, it gives the LIO.

cursor

The area Oracle uses to execute a statement in the session. Each trace line carries the cursor number (#1), which links PARSE, EXEC, FETCH, WAIT and STAT to the text of the PARSING IN CURSOR. The same number can be reused by another statement later.

cost

The optimizer estimate for a plan operation (cost= on the STAT line), in an internal Oracle unit. It is not time. Cost column in the plan.

D

deadlock

Two or more sessions waiting for each other. Oracle breaks the deadlock with error ORA-00060 in one of them and writes the DEADLOCK DETECTED block to that session's trace. WaitScope reads the block in a deadlock-only trace or inside a 10046 trace. See Deadlocks.

dep

The call depth (dep=): 0 for what the application called, 1 for the SQL that this statement executed, 2 for the SQL fired by level-1 SQL, and so on. Depth column in the statement list.

E

e

The duration of the call (elapsed), in the e= field. It includes the CPU, the waits inside it and the recursive calls. What remains after taking all this out is unaccounted-for time within the call.

ela

The duration of a wait, in the ela= field of the WAIT line.

wait

Time the session spent stopped, waiting for something: disk, network, a lock, the application. Each wait is a WAIT line with the event name (nam=) and the duration (ela=). It appears only with level 8 or 12 of 10046.

waits between calls

The waits outside any call, between one level-0 call and the next, such as SQL*Net message from client. The summary shows their time in Waits, as “N s between calls”.

estimated

The rows the optimizer predicted for a plan operation (card= on the STAT line). The statement detail compares them with the actual rows per execution and marks in red an error of 10 times or more. See wrong row estimate.

EXEC

The call that executes the cursor: it runs the DML, opens the query or executes the PL/SQL block. Exec column in the statement list.

F

FETCH

The call that fetches rows from a query. Each FETCH brings a batch of rows (r=); many calls with few rows each indicate a small batch. See many FETCH calls with few rows.

shape

The text of a statement with the literals replaced by placeholders: :s in place of quoted text and :n in place of numbers. Statements that differ only in their literals have the same shape and, in the report lists, become a single row, with the totals added up and the N variants badge. See Shareable texts and literals instead of binds.

H

hard parse

A PARSE that has to build the cursor from scratch, with the analysis of the text and the choice of plan by the optimizer, because it did not find a matching one ready in the shared memory. It appears as mis=1 on the PARSE line. It is common in SQL with literals instead of binds. See many hard parses.

I

Idle

The wait class for when the session is waiting for work. In a session trace, it is almost always SQL*Net message from client: the time of the application, the network and the user.

inclusive

The sum of the duration of all the calls of a statement, at any depth, with the recursive SQL they fire. Compare with contribution.

L

latency

The distribution of the duration of waits and calls in power-of-10 buckets. It shows whether the time comes from many fast occurrences or from a few slow ones. See Latency.

STAT line

A trace line with one operation of the execution plan: the operation, the object, the rows produced, the reads, the time and the optimizer estimate. Together, the STAT lines of a cursor make up the plan the report shows. See the STAT line.

LIO

Logical reads: the sum of cr and cu, in blocks. LIO column in the statement list.

literal

A value written directly in the SQL text, such as 'EXEMPLO' or 42, instead of a bind. Each different value becomes a different text for the database. The literal mask hides these values in the report. See Hidden literals in the SQL.

M

mis

The library cache misses of a call, in the mis= field. On the PARSE line, mis=1 is a hard parse. Misses column in Call types.

module

The value of MODULE NAME, which the application reports to the database. The report lists the modules of the trace in Module, on the Session and environment card, unmasked.

N

10046 level

How much event 10046 writes: level 1 writes the calls, 4 adds the bind values, 8 adds the waits and 12 adds binds and waits. The profile needs the waits: use level 8. See event 10046 levels.

O

obj#

The object number (object_id) on the WAIT line. It links the wait to the table or index read and is the basis of Objects with the most wait time. The name comes from the STAT lines of the trace, when they exist.

occurrences

How many times a component happened: the number of waits for an event or of calls of a type.

og

The optimizer goal, in the og= field of calls. WaitScope does not use this field.

P

p

The blocks read from disk by a call, in the p= field. Disk (PIO) column in Call types and PIO in the statement list.

PARSE

The call that analyzes the SQL text and prepares the cursor. It can be a hard parse (mis=1), when it builds the cursor from scratch, or reuse a ready cursor. A PARSE on every execution indicates a cursor opened and closed every time. See one PARSE per execution.

PARSING IN CURSOR

The line that carries the SQL text of a cursor, up to END OF STMT, with the length, the depth, the statement type and the sql_id.

response time profile

The table that splits the total time into components: CPU by call type, each wait event and the unaccounted-for time. The sum of the rows is exactly the total time. See Profile and the profile of the sample report.

exclusive profile

The profile of a statement without the recursive SQL it fires. It is what Top statements by component and by class uses.

period

The date and time of the start and end of the trace, computed from tim using the *** lines with date and time. It appears in Period, in the summary.

PIO

Physical reads: blocks read from disk, in the p= field of calls and pr= of STAT lines.

execution plan

The operations Oracle used to execute the statement, built from the STAT lines of the trace. It shows, for each operation, the rows, the reads and the time, and highlights the ones that weigh the most. See Execution plan.

plh

The plan hash value, in the plh= field of calls: it identifies the plan used. In the statement detail, it appears next to the title of each Execution plan.

pr

The blocks read from disk by a plan operation, in the pr= field of the STAT line, added to those of the child operations.

pw

The blocks written to disk by a plan operation, in the pw= field of the STAT line, as in a sort that does not fit in memory. Added to those of the child operations, like pr.

R

R (response time)

The total time of the session in the trace, from the start of the first call or wait to the last tim. It is the Total time in the summary and the 100% of the profile. In a collection with several sessions, it is the sum of the time of each one.

r

The rows processed by the call, in the r= field: those returned by FETCH or those changed by the EXEC of a DML. Rows column.

recursive (recursive SQL)

SQL executed inside another call, with dep greater than 0: the SQL of a PL/SQL function or procedure, of a trigger or the Oracle queries to the data dictionary. In the statement detail, it appears in Recursive SQL executed by this statement. See the recursive SQL card in the sample report.

reread

A disk read of a block the session had already read earlier in the same trace. Frequent rereads indicate a small cache or repeated access to the same data. See Reads.

S

session

The identification of the traced session, from the *** SESSION ID line: the SID and the serial#, followed by the pid of the server process, in Session, on the Session and environment card.

severity

The weight of a finding in the diagnosis: high, medium, low or info. The diagnosis lists the findings from the highest severity to the lowest.

sql_id

The 13-character identifier Oracle gives to the text of a statement. It comes in the sqlid= field of PARSING IN CURSOR and serves to find the same statement in the database, for example in V$SQL. SQL ID column. In an old trace, without it, WaitScope uses the hash value (hv=).

SQL*Net message from client

The session's wait for the next request from the application. It sits between calls and measures the time of the application, the network and the user: SQL tuning does not reduce this time. See session waiting for the application.

STAT

See STAT line.

T

unaccounted-for time within calls

The part of the duration (e) of each call that is not CPU (c), nor a wait recorded inside it, nor a recursive call. It is usually CPU queueing, memory paging or uninstrumented code. A negative value comes from the granularity of the CPU measurement. See unaccounted-for time within calls.

unaccounted-for time between calls

The time between the end of one level-0 call and the start of the next with no recorded wait: the difference between the total time and the sum of the level-0 calls and the waits between them. It grows when the trace was turned on or off midway, when the process was off the CPU or when a file of the collection is missing. See unaccounted-for time between calls.

self time

In the plan, the time of an operation without that of its child operations. Self LIO follows the same calculation.

total time

See R (response time).

shareable texts

The report section with the shapes, the statements that differ only in their literals. With binds, a single cursor would serve all the variants of a shape, and the hard parses would drop. See Shareable texts.

tim

The instant a call or a wait ended, in microseconds (in centiseconds in Oracle 7 and 8 traces). The start is tim minus e, or minus ela. WaitScope uses tim to put everything in order, measure the total time and join the files of a collection.

10046 trace

The Oracle SQL trace, turned on by event 10046 or by DBMS_MONITOR: a text file with each call, wait and plan of the session. It is what WaitScope reads. See the content of the trace.

U

time unit

The unit of the trace times: microseconds from Oracle 9i on, centiseconds in Oracle 7 and 8 traces. The summary shows which in Time unit. See time unit in the trace.

V

variant

Each statement that belongs to a shape, with its own literals. The N variants badge counts how many the shape has, or shows about N when the count is estimated. In the shape detail, the Variants that cost the most table shows the SQL ID and the literals of each one; with Report privacy on, the literals come out with *** and the SQL ID does not appear. See Variants that cost the most.

W

WAIT

See wait.

X

XCTEND

The line for the end of a transaction: rlbk=0 is a commit and rlbk=1 is a rollback. The summary counts both, as “N commits · N rollbacks”.