The MERITS Performance Optimization Method - Secrets of the Oracle Database - page 380

Phase 1—Measurement
I sent the file awr_capture.sql to the client and asked the DBA to capture 60 seconds of activity
from the image loading routine. I also asked the DBA to run the script statistics.sql to create
a report on the structure of the tables involved.
Phase 2—Assessment
I received a SQL trace file as well as the AWR and ASH reports created by awr_capture.sql . The
statistics.sql report showed that LOBs are stored in a tablespace with default block size. Storage
of LOB data in row was enabled.
LOB Column Block- Pct- In
Name Segment Name Tablespace size Chunk version Retention Cache Row
---------- ----------------- ---------- ------ ------ -------- --------- ----- ---
IMAGE_DATA IMAGES_IMAGE_DATA USERS 8 KB 8192 10 NO YES
I processed the SQL trace file with TKPROF. Since it was likely that EXEC calls rather than
FETCH calls would contribute most to response time, I used the sort options exeela,fchela .
$ tkprof ten_ora_3172_img_load.trc ten_ora_3172_img_load.tkp sort=exeela,fchela
Following is an excerpt of the TKPROF report for the trace file:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 213 0.01 0.00 0 0 0 0
Execute 213 0.07 0.14 1 71 495 71
Fetch 142 0.00 0.01 0 142 0 142
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 568 0.09 0.16 1 213 495 213
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 8010 0.27 13.23
SQL*Net more data from client 32182 0.00 0.43
direct path write 45619 0.01 0.69
SQL*Net message to client 8578 0.00 0.02
SQL*Net message from client 8578 0.03 4.91
db file sequential read 7130 0.25 26.11
log file sync 86 0.05 0.51
log file switch completion 5 0.99 3.23
latch: shared pool 10 0.00 0.00
latch: library cache 1 0.00 0.00
log file switch (checkpoint incomplete) 8 0.99 1.76