Database Reference
In-Depth Information
The aim of SQL trace is twofold: first, to provide information in order to break up the response time between
service time and wait time, and second, to give detailed information about the used resources and synchronization
points. All this information regarding each interaction between the SQL engine and the other components is written
in a trace file. Note that in Figure 3-2 , the attribution of CPU, resource X, and synchronization point Y is artificial. The
reason for this is to show that every call may use the database engine differently.
Although I cover this in greater detail later in this chapter, let's briefly look at an example of the kind of
information provided by SQL trace and that can be extracted by a tool (in this case, TKPROF). This includes the text
of the SQL statement, some execution statistics, the waits occurred during the processing, and information about the
parsing phase, such as the generated execution plan. Note that such information is provided for each SQL statement
executed by the application and recursively by the database engine itself:
SELECT CUST_ID, EXTRACT(YEAR FROM TIME_ID), SUM(AMOUNT_SOLD)
FROM SALES
WHERE CHANNEL_ID = :B1
GROUP BY CUST_ID, EXTRACT(YEAR FROM TIME_ID)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 164 0.84 1.27 3472 1781 0 16348
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 166 0.84 1.28 3472 1781 0 16348
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77 (SH) (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ------------------------------------------
16348 16348 16348 HASH GROUP BY
540328 540328 540328 PARTITION RANGE ALL PARTITION: 1 28
540328 540328 540328 TABLE ACCESS FULL SALES PARTITION: 1 28
Elapsed times include waiting on following events:
Event waited on Times Waited Max. Wait Total Waited
----------------------------------- ------------ ---------- ------------
Disk file operations I/O 2 0.00 0.00
db file sequential read 29 0.00 0.00
direct path read 70 0.00 0.00
asynch descriptor resize 16 0.00 0.00
direct path write temp 1699 0.02 0.62
direct path read temp 1699 0.00 0.00
 
Search WWH ::




Custom Search