| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: can anyone help me tune the database
trameshkumar_at_gmail.com wrote:
(Selective snipping):
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> CPU time 83
> 62.08
> direct path write 16,504 43
> 32.23
> db file sequential read 927 3
> 2.02
> db file scattered read 962 2
> 1.25
> control file parallel write 401 1
> 1.11
> CPU Elapsd
> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 1,461,109 1 1,461,109.0 89.4 45.95 47.73
> 2010239668
> begin Fspdf_Eod_Process_Pkg.FSPDFP_GEN_SALES_LEDGER_UPD(ip_compa
> ny_id_pk=>:ip_company_id_pk, ip_loggedin_id=>:ip_loggedin_id, Er
> rNumber=>:ErrNumber, ErrMessage=>:ErrMessage); end;
>> AR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE
> 1,273,844 748 1,703.0 77.9 33.95 33.89
> 2933058741
> SELECT NVL(SUM(bldtls.debt_approved_amt),0), NVL(S
> UM(bldtls.debt_unapproved_amt),0) FROM BDS_BILL_
> DETAILS bldtls WHERE bldtls.company_id_pk = :b3
> AND bldtls.debtor_account_cd = :b2 AND TO_DATE(TO_CH
>> 96706093
> 65,183 1 65,183.0 4.0 1.02 1.39
>> AR(invdtls.inv_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE
> 53,164 519 102.4 3.3 1.55 1.60
> 3550942769
> SELECT NVL(SUM(invdtls.cl_approved_amt),0), NVL(SU
> M(invdtls.cl_unapproved_amt),0) FROM FCT_INV_DET
> AILS invdtls WHERE invdtls.company_id_pk = :b3
> AND invdtls.client_account_cd = :b2 AND TO_DATE(TO_CH
>> 4103447814
> 46,758 322 145.2 2.9 0.53 0.71
> 4041023944
> SELECT invdtls.tran_id, invdtls.invoice_no, in
> vdtls.invoice_dn_flag, invdtls.debt_approved_amt FROM
> FCT_INV_DETAILS invdtls WHERE invdtls.company_
> id_pk = :b4 AND invdtls.client_account_cd = :b3 AND in
> vdtls.debt_approved_amt > 0 AND (TO_DATE(TO_CHAR(invdtls.du
>
> 40,913 1 40,913.0 2.5 1.58 4.38
>> 1428328184
> 40,533 1 40,533.0 2.5 1.98 2.11
>> different)
> 39,282 1 39,282.0 2.4 1.52 1.60
> 555896067
> INSERT INTO TMP_INV_DEBT_APP_AMT_SUM (SELECT invdtl
> s.company_id_pk, invdtls.Debtor_Account_Cd, NVL(SUM(in
> vdtls.debt_approved_amt),0), NVL(SUM(invdtls.debt_unapprove
> d_amt),0) FROM FCT_INV_DETAILS invdtls WHERE i
> nvdtls.company_id_pk = :b2 AND TO_DATE(TO_CHAR(invdtls.inv_b
End
> value
> Parameter Name Begin value (if
>
>
Statspack reports... I try not to read them too often.
Looking at the top SQL statements, it appears that this call to a
PL/SQL function is the longest running, with one execution lasting
47.73 seconds, and with 1,461,109 logical reads (reading blocks from
memory):
begin
Fspdf_Eod_Process_Pkg.FSPDFP_GEN_SALES_LEDGER_UPD(
ip_company_id_pk=>:ip_company_id_pk,
ip_loggedin_id=>:ip_loggedin_id,
ErrNumber=>:ErrNumber,
ErrMessage=>:ErrMessage);
end;
The second longest running SQL statement, with 748 executions totaling
33.89 seconds, and 1,273,844 logical reads is likely in the above named
PL/SQL function:
SELECT
NVL(SUM(bldtls.debt_approved_amt),0),
NVL(SUM(bldtls.debt_unapproved_amt),0)
FROM
BDS_BILL_DETAILS bldtls
WHERE
bldtls.company_id_pk = :b3
AND bldtls.debtor_account_cd = :b2
AND
TO_DATE(TO_CHAR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <=
TO_DATE
Take a look at
TO_DATE(TO_CHAR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY')
TRUNC(bldtls.bill_batch_date) will do the same task, and should consume
less server CPU time. However, both TRUNC() and TO_DATE(TO_CHAR())
will make it impossible for Oracle to use an index on the
bldtls.bill_batch_date column if one exists, unless there is a function
based index on
TO_DATE(TO_CHAR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY').
Preferrably, you would rework this to take advantage of an index on
bldtls.bill_batch_date without requiring a function based index.
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.
Received on Tue Sep 12 2006 - 06:38:01 CDT
 |
 |