parallel query

From: Prasath <prasath.rao_at_gmail.com>
Date: 10 Jul 2006 06:39:15 -0700
Message-ID: <1152538755.802448.285950@p79g2000cwp.googlegroups.com>

How do I avoid the sort being written to disk? Which parameter needs to be changed?

I am using Oracle 9iR2. Since the workarea_size_policy is set to AUTO, all the _size parameters become redundant.

Statistics

          0  recursive calls
          4  db block gets
     343188  consistent gets
     331539  physical reads
          0  redo size
      20273  bytes sent via SQL*Net to client
        859  bytes received via SQL*Net from client
         86  SQL*Net roundtrips to/from client
          5  sorts (memory)
          2  sorts (disk)
       1263  rows processed

SQL> show parameters hash

NAME                                 TYPE        VALUE


------------------------------------ -----------

------------------------------

hash_area_size                       integer     1048576
hash_join_enabled                    boolean     TRUE

SQL> show parameters sort

NAME                                 TYPE        VALUE


------------------------------------ -----------

------------------------------

nls_sort                             string
sort_area_retained_size              integer     0
sort_area_size                       integer     1048576

SQL> show parameters pga

NAME                                 TYPE        VALUE


------------------------------------ -----------

------------------------------

pga_aggregate_target                 big integer 2000000000

SQL> show parameters work

NAME                                 TYPE        VALUE


------------------------------------ -----------

------------------------------

workarea_size_policy                 string      AUTO

Received on Mon Jul 10 2006 - 08:39:15 CDT