|
|
| Re: Any Recommendation for Statspack Reprot [message #205122 is a reply to message #205109] |
Thu, 23 November 2006 06:40   |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
1. Low Buffer Hit ratio - 64.55%.
Probably caused by bad performung SQL statements.
2. Low Execute to Parse - 15.09%
Probably you have a low of WAITS caused by hard parses (no bind variables in SQL statement).
3. 2 major WAIT events ( CPU wait 48.33% and scattered read 34.07%) corresponds to both previous conclusions.
4. Statement
INSERT /*+ APPEND */ INTO "BOIDW"."FEM_MORTGAGES"("IDENTITY_CODE
","ID_NUMBER","GL_ACCOUNT_ID","ORG_UNIT_ID","COMMON_COA_ID","AS_
OF_DATE","ISO_CURRENCY_CD","IDENTITY_CODE_CHG","ACCOUNT_CONTRIB"
,"ACCOUNT_CONTRIB_AFTER_TAX","ACCOUNT_GROUP_CD","ACCOUNT_NUMBER"
,"ACCOUNT_OFFICER","ACCRUAL_BASIS_CD","ACCRUED_INTEREST","ADJUST ...
Find the whole statement.
Each execution is causing 35,576.1 of buffer gets and a LOT of waits ( CPU time of 67.65 sec VS 635.83 sec of ELAPSED time).
The statement spends about 570 sec in WAIT state).
5.Statement
SELECT DISTINCT TP_COA_ID FROM MIRROR_BOI_CONSUMER_LOANS WHERE D
ATA_SOURCE ='FN' AND AS_OF_DATE = '31-Oct-2006' AND SUBSTR(TP_C
OA_ID,1,3) IN (508,509,510,511).
Performs a lot of buffer gets ( 1,130,068 ), so EXPLAIN it. It probably performs FULL TABLE SCAN.
How TP_COA_ID column is defined?
If it's number - try rewriting the statement as:
... AND TP_COA_ID BETWEEN 508000000000 AND 511999999999.
Consider definig an index on ( DATA_SOURCE, AS_OF_DATE , TP_COA_ID )
Just for starters.
HTH.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
