OraFAQ Forum: Performance Tuning » Help - Performance Issue

Home » RDBMS Server » Performance Tuning » Help - Performance Issue - statspack report attached

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help - Performance Issue - statspack report attached [message #209938]

Mon, 18 December 2006 10:11

orasaket
Messages: 70
Registered: November 2006

Member

Hi,

I am using Oracle 9iR2 on Linux (RAC)
Please find the attached statspack Report
and suggest, the SQL statement(s) to attack first for TUNING?

What could be the Tuning priority sequence for following

SQL ordered by Sharable Memory
SQL ordered by Parse Calls
SQL ordered by Reads
SQL ordered by Gets

also please advice me on the instance parameter changes
Shall i increase shared pool size to 352 Mb?

Also Please suggest what are the reference figures for the benchmarks in statspack?
i.e. 1) what should be threshold for   Buffer Gets / disk reads
     2) what is worrying starting point for Gets per Exec   
     3) what is worrying starting point Reads per Exec
     3) till what averages in following waits are normal and / or not a serious issue
                                                                        Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------

Note :statspack_report.txt is original statspack file
whereas html file is as produced above one on OraPerf.com

Thanks and Regards,
OraSaket

Attachment: statspack.zip
(Size: 52.36KB, Downloaded 1166 times)

Report message to a moderator

Re: Help - Performance Issue - statspack report attached [message #209960 is a reply to message #209938]

Mon, 18 December 2006 13:10

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Start with :

1. SQL ordered by Reads
2. SQL ordered by Gets

EXPLAIN each statement and tune it.

Report message to a moderator

Re: Help - Performance Issue - statspack report attached [message #209981 is a reply to message #209938]

Mon, 18 December 2006 18:33

orasaket
Messages: 70
Registered: November 2006

Member

Hi,
Thanks for your advice

however while going through the SQL statments with high physical reads i found the following statment listed 3rd for high physical reads


Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
365,244           26       14,047.8   25.5    28.88    537.17 1284180873
SELECT LREPSEQ FROM ( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, PS_ALT_HDR PAH
WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND PAH.STRALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1 AND LREPSEQ IS NOT NULL 
UNION 
SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PAH 
WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND STRALTTRANSHDRNBR = :B1
 AND NREPSTATCD = 2 AND DTSUBMITTED < SYSDATE )

however there is absolutely nothing wrong in the plan as you can see below
also it is accessing very few rows

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Hint=CHOOSE		2  	 	1414  	 	      	             	 
  VIEW		2  	26  	1414  	 	      	             	 
    SORT UNIQUE		2  	81  	1414  	 	      	             	 
      UNION-ALL		  	 	 	 	      	             	 
        NESTED LOOPS		1  	35  	12  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	PS_ALT_HDR	1  	22  	3  	 	      	             	 
            INDEX UNIQUE SCAN	XPKPS_ALT_HDR	1  	 	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	COM_CONTACT_HST	1  	13  	9  	 	      	             	 
            INDEX RANGE SCAN	COM_CNTHST_CLTCRTCTDT	8  	 	2  	 	      	             	 
        NESTED LOOPS		1  	46  	1392  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	PS_ALT_HDR	1  	22  	3  	 	      	             	 
            INDEX UNIQUE SCAN	XPKPS_ALT_HDR	1  	 	2  	 	      	             	 
          TABLE ACCESS FULL	OM_RUN_REPORT_QUEUE	1  	24  	1389

similar is the case for other high disk read statements (as reported by statspack) which has nothing wrong in plan and accessing very few rows

now where do i start with?

Thanks and regards,
OraSaket

Report message to a moderator

Re: Help - Performance Issue - statspack report attached [message #210025 is a reply to message #209938]

Tue, 19 December 2006 00:36

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Hi.

SELECT LREPSEQ
FROM
( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, PS_ALT_HDR PAH
WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND
PAH.STRALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1
AND LREPSEQ IS NOT NULL
UNION
SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PAH
WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND
STRALTTRANSHDRNBR = :B1
AND NREPSTATCD = 2 AND DTSUBMITTED < SYSDATE )

Questions:
1. Where following columns belong? (To what table?)
LREPSEQ, NREPSTATCD,
DTSUBMITTED
-- Post the query with these columns - prefixed.

2. How many rows in COM_CONTACT_HST table?
-- What are the columns of COM_CNTHST_CLTCRTCTDT index?

3. How many rows are in OM_RUN_REPORT_QUEUE table?
-- Post the result of following query:
SELECT COUNT(*), COUNT(DISTINCT STRPOLNBR) FROM OM_RUN_REPORT_QUEUE ;
-- If NREPSTATCD and DTSUBMITTED column belong to OM_RUN_REPORT_QUEUE table as well then post the results of:
SELECT COUNT(*) FROM OM_RUN_REPORT_QUEUE WHERE NREPSTATCD = 2 ;

SELECT COUNT(*) FROM OM_RUN_REPORT_QUEUE
WHERE DTSUBMITTED < SYSDATE;

I'm not so sure about EXPLAIN plan being OK.

Report message to a moderator

Re: Help - Performance Issue - statspack report attached [message #210056 is a reply to message #210025]

Tue, 19 December 2006 02:52

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

On a cursory examination, you've got a lot of IO waits. The 6 queries at the top of the list by buffer gets are all doing over 60,000 gets per execution, and 3 of them are doing over 1,000,000.
I'd get these tuned before looking at changing anything else.

Report message to a moderator

Previous Topic:	how to handle this wait event: "control file sequential read"?
Next Topic:	Introduce a good book for oracle performance tuning!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 14:51:40 CDT 2024