I'm looking into a select statement to see how I can improve it. I traced the execution of the single select statement using 10046 trace and used tkprof on it. Here's the output:
What I notice here is that there were alot of blocks read (either from disk and/or from cache) to return only 31 rows (see fetch)? I'm no expert in performance diagnosing, but does this strike anyone as odd? Why would Oracle read so many blocks just to find 31 rows? It's obviously using the index.Code:SELECT *
FROM NGSDMS60.NGTAGS_VIEW T1
WHERE NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B'
AND (((UPPER(NGUSRFLD2) = ('ROARK'))
AND (UPPER(NGUSRFLD19) = ('BUILTIN'))))
ORDER BY NGID DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 81.82 402.27 258094 505635 0 31
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 81.86 402.31 258094 505635 0 31
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
31 TABLE ACCESS BY INDEX ROWID NGTAGS
1726509 INDEX RANGE SCAN DESCENDING NGTAGS_PK (object id 31331)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net more data to client 5 0.00 0.00
db file sequential read 258094 0.31 352.49
latch free 87 0.05 0.12
SQL*Net message from client 4 9.26 25.61
I'm hoping this is a classic symptom that a novice like me doesn't recognize...