Re: performance analysis on autotrace output

I am no expert, but the one without the full table accesses is probably better. full table accesses are time and resource consuming.

lesliet_at_u.washington.edu (L. Tseng) wrote:

>I run autotrace on sql*plus and get two outputs from executing two
>simple sql code . Can someone tell which one is better and why?
>Thanks,
 

>Execution Plan 1
>----------------------------------------------------------
> 0 UPDATE STATEMENT Optimizer=CHOOSE
> 1 0 FILTER
> 2 1 TABLE ACCESS (FULL) OF 'TEST01'
> 3 1 TABLE ACCESS (FULL) OF 'TEST02'
> 4 0 TABLE ACCESS (FULL) OF 'TEST02'
>Statistics 1
>----------------------------------------------------------
> 0 recursive calls
> 26 db block gets
> 21 consistent gets
> 0 physical reads
> 912 redo size
> 146 bytes sent via SQL*Net to client
> 509 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 4 rows processed

>Execution Plan 2
>----------------------------------------------------------
> 0 UPDATE STATEMENT Optimizer=CHOOSE
> 1 0 MERGE JOIN
> 2 1 SORT (JOIN)
> 3 2 TABLE ACCESS (FULL) OF 'TEST01'
> 4 1 SORT (JOIN)
> 5 4 VIEW
> 6 5 SORT (UNIQUE)
> 7 6 TABLE ACCESS (FULL) OF 'TEST02'
> 8 0 TABLE ACCESS (FULL) OF 'TEST02'
 
>Statistics 2
>----------------------------------------------------------
> 0 recursive calls
> 20 db block gets
> 21 consistent gets
> 0 physical reads
> 877 redo size
> 146 bytes sent via SQL*Net to client
> 436 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 3 sorts (memory)
> 0 sorts (disk)
> 4 rows processed
 

>==
Received on Mon May 12 1997 - 00:00:00 CEST