Re: Tuning SQL

In article <369E08F9.26AE_at_sou.edu>,
  sgoldberg_at_sou.edu wrote:
> Hi, I have a query I am trying to fine tune. I am using the plan table
> to try and understand the execution steps. Here is the formatted output
> from the plan table. Can anyone tell me the order of steps that will be
> executed from this output?
> --
> SELECT STATEMENT Cost =
> 1 (1) SORT(ORDER BY)
> 2 (1) FILTER
> 3 (1) NESTED LOOPS
> 4 (1) NESTED LOOPS
> 5 (1) TABLE ACCESS(BY ROWID) SFRSTCR
> 6 (1) INDEX(RANGE SCAN) SFRSTCR_INDEX2
> 7 (2) TABLE ACCESS(BY ROWID) STVRSTS
> 8 (1) INDEX(UNIQUE SCAN) PK_STVRSTS
> 9 (2) INDEX(RANGE SCAN) SPRIDEN_KEY_INDEX
> 10 (2) TABLE ACCESS(BY ROWID) TBRACCD
> 11 (1) INDEX(RANGE SCAN) PK_TBRACCD
> 12 (3) TABLE ACCESS(BY ROWID) TBRACCD
> 13 (1) INDEX(RANGE SCAN) TBRACCD_CREDITS_INDEX
>
> Any help will be appreciated.
>
> Susan Goldberg
> Programmer
> Southern Oregon State College
> sgoldberg_at_sou.edu
>

SELECT STATEMENT Cost =

 1        (1) SORT(ORDER BY) -last
 2          (1) FILTER -eighth
 3            (1) NESTED LOOPS -seventh
 4              (1) NESTED LOOPS -third
 5                (1) TABLE ACCESS(BY ROWID) SFRSTCR
 6                  (1) INDEX(RANGE SCAN) SFRSTCR_INDEX2 -first
 7                (2) TABLE ACCESS(BY ROWID) STVRSTS
 8                  (1) INDEX(UNIQUE SCAN) PK_STVRSTS -second
 9              (2) INDEX(RANGE SCAN) SPRIDEN_KEY_INDEX -fourth
 10           (2) TABLE ACCESS(BY ROWID) TBRACCD
 11             (1) INDEX(RANGE SCAN) PK_TBRACCD -fifth
 12           (3) TABLE ACCESS(BY ROWID) TBRACCD
 13             (1) INDEX(RANGE SCAN) TBRACCD_CREDITS_INDEX -sixth

By the way, it might be faster to use full table scan on one of the tables. It looks like a typical plan for a report, but is fully indexed. Index access is not a good option for retrieving all records from the table. I bet that you have an enormous amount of I/O if my assumption is correct.

Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 14 1999 - 19:32:26 CST