Re: parallel query

Prasath wrote:
> 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

I noticed a similar trend in 10g R2 when the workarea_size_policy is set to auto, and the pga_aggregate_target is set to 2GB. These are likely single pass sorts to disk are are being recorded. I was able to virtually eliminate the sorts to disk on my system by setting sort_area_size to 20M, although 10M likely would have worked just as well. Specifying a sort_area_size sets the minimum used per query for the sorting phase. My database is currently reporting 51,268,373 sorts in memory and 53 sorts to disk. Making random changes to the initialization parameters is not a good idea - test first.

Jonathan Lewis has an excellent section in his "Cost-Based Oracle Fundamentals book that tells how to troubleshoot disk sorts with 10032, 10053, and 10046 traces. You might want to pick up a copy.

If you can narrow down the source of the sort to disk a single SQL statement, that might help determine the solution. If you enable a 10046 trace at level 8 or 12 for all sessions, you should be able to examine the wait events in the trace files to determine the SQL causing the problem.

Physical reads seems a bit high compared to consistent gets, but the database was likely started very recently based on the number of sorts.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jul 10 2006 - 12:33:26 CDT