Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: can anyone help me tune the database

Re: can anyone help me tune the database

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 12 Sep 2006 04:42:13 -0700
Message-ID: <1158061333.673803.45360@p79g2000cwp.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US