Re: can anyone help me tune the database

Charles Hooper wrote:
> How could this be rewritten? Assume that the full WHERE clause line
> looked like this:
> AND
> TO_DATE(TO_CHAR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <=
> TO_DATE('01/02/2006','DD/MM/YYYY')
>
> This is functionally equivalent to the above, and will be able to use a
> normal index on the bldtls.bill_batch_date column:
> AND bldtls.bill_batch_date < TO_DATE('01/02/2006')+1
>
>
> You have sort_area_size at 524,288 B and pga_aggregate_target at
> 39,845,888 B. This sets the minimum value of the sort_area_size to
> 512KB, but that value can float upward, I believe to 5% (check Jonathan
> Lewis' Cost-Based Oracle Fundamentals book for the correct percentage)
> of 38MB (which ic roughly 1.9MB). Increasing the value of
> sort_area_size to 5MB and also increasing the pga_aggregate_target may
> decrease the sorts to disk, but fixing the SQL statement that I
> identified above may have a larger impact.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Fixing a syntax problem:
"This is functionally equivalent to the above, and will be able to use a
normal index on the bldtls.bill_batch_date column:"

   AND bldtls.bill_batch_date < TO_DATE('01/02/2006','DD/MM/YYYY')+1

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Sep 12 2006 - 06:42:13 CDT