Hi Experts,
Can someone please help me in understanding the below tkprof.Need some approximate analysis.The same is attached to notepad for better format.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 2 0
Execute 842615 19.53 19.57 0 22 0 0
Fetch 842615 23333.33 23416.81 10 3469943694 0 842615
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1685232 23352.86 23436.39 10 3469943716 2 842615
Misses in library cache during parse: 2
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 173 (recursive depth: 1)
Number of plan statistics captured: 350
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=17522 pr=0 pw=0 time=33379 us)
0 0 1 VIEW (cr=17522 pr=0 pw=0 time=33376 us cost=607 size=13 card=1)
4 4 14 COUNT STOPKEY (cr=17522 pr=0 pw=0 time=33366 us)
4 4 14 VIEW (cr=17522 pr=0 pw=0 time=33364 us cost=607 size=13 card=1)
4 4 14 SORT ORDER BY STOPKEY (cr=17522 pr=0 pw=0 time=33363 us cost=607 size=188 card=1)
465 1135 1994 FILTER (cr=17522 pr=0 pw=0 time=38758 us)
467 1139 1998 NESTED LOOPS ANTI (cr=17508 pr=0 pw=0 time=35080 us cost=597 size=188 card=1)
468 1139 1998 NESTED LOOPS (cr=15219 pr=0 pw=0 time=33437 us cost=596 size=178 card=1)
468 1139 1998 NESTED LOOPS (cr=15214 pr=0 pw=0 time=31798 us cost=595 size=165 card=1)
468 1139 1998 NESTED LOOPS (cr=14071 pr=0 pw=0 time=30329 us cost=594 size=152 card=1)
468 1139 1998 NESTED LOOPS (cr=14067 pr=0 pw=0 time=29041 us cost=593 size=139 card=1)
468 1139 1998 NESTED LOOPS (cr=12923 pr=0 pw=0 time=27217 us cost=592 size=126 card=1)
608 1472 2381 NESTED LOOPS (cr=1639 pr=0 pw=0 time=5551 us cost=450 size=6578 card=143)
1 1 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=8 us cost=2 size=19 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID XXABC_COLLEGE_MASTER_TBL (cr=2 pr=0 pw=0 time=4 us cost=1 size=6 card=1)
1 1 1 INDEX UNIQUE SCAN XXABC_COLLEGES_PK (cr=1 pr=0 pw=0 time=2 us cost=0 size=0 card=1)(object id 2313929)
1 1 1 TABLE ACCESS BY INDEX ROWID XXABC_LOV_MASTER_TBL (cr=2 pr=0 pw=0 time=3 us cost=1 size=13 card=1)
1 1 1 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=1 pr=0 pw=0 time=2 us cost=0 size=0 card=1)(object id 2313914)
608 1472 2381 TABLE ACCESS FULL XXABC_APPLICANT_COURSPREFS_TBL (cr=1635 pr=0 pw=0 time=5324 us cost=448 size=3861 card=143)
468 1139 1998 TABLE ACCESS BY INDEX ROWID XXABC_APPLICANT_DETAILS_TBL (cr=11284 pr=0 pw=0 time=18471 us cost=1 size=80 card=1)
579 1382 2339 INDEX UNIQUE SCAN XXABC_APPLICANTS_PK (cr=10419 pr=0 pw=0 time=16014 us cost=0 size=0 card=1)(object id 2316264)
0 1 3 NESTED LOOPS (cr=4420 pr=0 pw=0 time=3972 us cost=3 size=26 card=1)
2 2 13 TABLE ACCESS BY INDEX ROWID XXABC_APPLICANT_COURSPREFS_TBL (cr=4416 pr=0 pw=0 time=3594 us cost=2 size=14 card=1)
608 1472 2381 INDEX UNIQUE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=2944 pr=0 pw=0 time=2140 us cost=1 size=0 card=1)(object id 2316268)
0 1 3 TABLE ACCESS BY INDEX ROWID XXABC_CATEGORY_MASTER_TBL (cr=3 pr=0 pw=0 time=6 us cost=1 size=12 card=1)
2 2 13 INDEX UNIQUE SCAN XXABC_CATEGORY_PK (cr=2 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 2313948)
608 1472 2381 INDEX UNIQUE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=2944 pr=0 pw=0 time=2140 us cost=1 size=0 card=1)(object id 2316268)
0 1 3 TABLE ACCESS BY INDEX ROWID XXABC_CATEGORY_MASTER_TBL (cr=3 pr=0 pw=0 time=6 us cost=1 size=12 card=1)
2 2 13 INDEX UNIQUE SCAN XXABC_CATEGORY_PK (cr=2 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 2313948)
39 251 576 TABLE ACCESS BY INDEX ROWID BATCHED XXABC_APPLICANT_COURSPREFS_TBL (cr=2371 pr=0 pw=0 time=4787 us cost=3 size=10 card=1)
4693 8691 13009 INDEX RANGE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=1502 pr=0 pw=0 time=2613 us cost=2 size=0 card=4)(object id 2316268)
5 8 33 TABLE ACCESS BY INDEX ROWID BATCHED XXABC_APPLICANT_COURSPREFS_TBL (cr=2255 pr=0 pw=0 time=3760 us cost=3 size=10 card=1)
4513 8172 12952 INDEX RANGE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=1418 pr=0 pw=0 time=2105 us cost=2 size=0 card=4)(object id 2316268)
468 1139 1998 TABLE ACCESS BY INDEX ROWID XXABC_LOV_MASTER_TBL (cr=1143 pr=0 pw=0 time=1318 us cost=1 size=13 card=1)
468 1139 1998 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=4 pr=0 pw=0 time=515 us cost=0 size=0 card=1)(object id 2313914)
468 1139 1998 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=4 pr=0 pw=0 time=469 us cost=0 size=0 card=1)(object id 2313914)
468 1139 1998 TABLE ACCESS BY INDEX ROWID XXABC_LOV_MASTER_TBL (cr=1143 pr=0 pw=0 time=1077 us cost=1 size=13 card=1)
468 1139 1998 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=4 pr=0 pw=0 time=428 us cost=0 size=0 card=1)(object id 2313914)
468 1139 1998 TABLE ACCESS BY INDEX ROWID XXABC_LOV_MASTER_TBL (cr=5 pr=0 pw=0 time=1145 us cost=1 size=13 card=1)
468 1139 1998 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=4 pr=0 pw=0 time=441 us cost=0 size=0 card=1)(object id 2313914)
1 0 8 TABLE ACCESS BY INDEX ROWID XXABC_APPLICANT_COURSPREFS_TBL (cr=2289 pr=0 pw=0 time=1530 us cost=1 size=35510 card=3551)
468 1139 1998 INDEX UNIQUE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=1150 pr=0 pw=0 time=875 us cost=0 size=0 card=1)(object id 2316268)
0 0 0 VIEW index$_join$_014 (cr=13 pr=0 pw=0 time=287 us cost=0 size=12 card=1)
0 0 0 HASH JOIN (cr=13 pr=0 pw=0 time=285 us)
6 6 6 INDEX RANGE SCAN XXABC_CATEGORY_PK (cr=6 pr=0 pw=0 time=14 us cost=0 size=12 card=1)(object id 2313948)
12 11 12 INLIST ITERATOR (cr=8 pr=0 pw=0 time=23 us)
12 11 12 INDEX UNIQUE SCAN XXABC_CATEGORY_CODE_UK (cr=8 pr=0 pw=0 time=18 us cost=0 size=12 card=1)(object id 2313946)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc cr grant 2-way 9 0.00 0.00
db file sequential read 10 0.00 0.00
latch: row cache objects 10 0.00 0.00
latch: shared pool 17 0.00 0.00
resmgr:cpu quantum 21 0.00 0.00
********************************************************************************
Thank you,
satish