Home » RDBMS Server » Performance Tuning » Any Recommendation for Statspack Reprot
Any Recommendation for Statspack Reprot [message #205109] Thu, 23 November 2006 06:03 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I've just started Tuning this report.

Any recommendation for this stats pack report(File attached).

Brian.
Re: Any Recommendation for Statspack Reprot [message #205122 is a reply to message #205109] Thu, 23 November 2006 06:40 Go to previous messageGo to next message
michael_bialik
Messages: 611
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.
Re: Any Recommendation for Statspack Reprot [message #205139 is a reply to message #205122] Thu, 23 November 2006 07:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It looks like you're doing a lot of full table scans - your Buffer pool is 0.5Gb, and you'd have to double it to make a noticable impact on the hit ratio.
Re: Any Recommendation for Statspack Reprot [message #205482 is a reply to message #205109] Sat, 25 November 2006 02:30 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,
Can you give me a link to a document, where it explains how to read statspack report.


Thanks
Re: Any Recommendation for Statspack Reprot [message #205483 is a reply to message #205482] Sat, 25 November 2006 02:37 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

go through below link


http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:19958767477215



http://www.akadia.com/services/ora_statspack_survival_guide.html



hope this helps
Taj
Re: Any Recommendation for Statspack Reprot [message #205487 is a reply to message #205109] Sat, 25 November 2006 03:23 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Thanks Taj
Previous Topic: "+0 trick undesirable", why?
Next Topic: Statspack
Goto Forum:
  


Current Time: Tue Dec 06 02:42:54 CST 2016

Total time taken to generate the page: 0.10006 seconds