performance analysis on autotrace output

From: L. Tseng <lesliet_at_u.washington.edu>
Date: 1997/04/12
Message-ID: <5imjmj$n0a@nntp1.u.washington.edu>#1/1

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 Sat Apr 12 1997 - 00:00:00 CDT