Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

tkprof Analysis

SGUApr 10 2020 — edited Apr 24 2020

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

This post has been answered by Jonathan Lewis on Apr 13 2020
Jump to Answer

Comments

Post Details

Added on Apr 10 2020
32 comments
1,627 views