TKPROF - help
From: <pihlab_at_cbr.hhcs.gov.au>
Date: 11 Nov 93 16:27:38 +1000
Message-ID: <1993Nov11.162738.1_at_cbr.hhcs.gov.au>
Date: 11 Nov 93 16:27:38 +1000
Message-ID: <1993Nov11.162738.1_at_cbr.hhcs.gov.au>
Hi,
Can somebody tell me how to read the "Execution plan" produced by the TKPROF utility (Oracle V6).
I can remember reading somewhere that you read from the furthest indented to the right and bottom up.
In the examples below I have listed 3 extra columns which show the ways in which the query could be executed and where the differences occur. Would someone please indicate which is correct, why and where I can read about this.
EG
SORT (ORDER BY) 6 6 SORT (GROUP BY) 5 5 NESTED LOOPS 4 4 TABLE ACCESS (FULL) OF 'Provider' 3 1 * TABLE ACCESS (BY ROWID) OF 'PP_Service' 2 3 * INDEX (RANGE SCAN) OF 'I_PP_Service_2' (NON-UNIQUE) 1 2 *
Here's one that's a lot more complex.
SORT(UNIQUE) 16 16 FILTER 15 15 NESTED LOOPS 10 10 NESTED LOOPS 7 7 NESTED LOOPS 4 4 TABLE ACCESS (FULL) OF 'CLIENT' 3 1 * TABLE ACCESS (BY ROWID) OF 'PP_SERVICE' 2 3 * INDEX (RANGE SCAN) OF 'I_PP_SERVICE_1' (UNIQUE) 1 2 * TABLE ACCESS (BY ROWID) OF 'PROVIDER' 6 6 INDEX (UNIQUE SCAN) OF 'I_PROVIDER_1' (UNIQUE) 5 5 TABLE ACCESS (BY ROWID) OF 'PP_SERVICE' 9 9 INDEX (RANGE SCAN) OF 'I_PP_SERVICE_1' (UNIQUE) 8 8 TABLE ACCESS (BY ROWID) OF 'PP_SERVICE' 14 12 * INDEX (RANGE SCAN) OF 'I_PP_SERVICE_1' (UNIQUE) 13 11 * TABLE ACCESS (BY ROWID) OF 'PP_SERVICE' 12 14 * INDEX (RANGE SCAN) OF 'I_PP_SERVICE_1' (UNIQUE) 11 13 *
Any help appreciated.
Thanks.
-- Bruce... pihlab_at_cbr.hhcs.gov.au "If you swallow a live frog first thing in the morning ... Nothing worse will happen to either of you for the rest of the day." *******************************************************************Received on Thu Nov 11 1993 - 07:27:38 CET
* Bruce Pihlamae -- Database Administration *
* Commonwealth Department of *
* Health, Housing, Local Government & Community Services *
* Canberra, Australia (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have. *
*******************************************************************