Home » RDBMS Server » Performance Tuning » How i tune database and sql (Oracle 9i (9.2.0.1) on Windows 2003 Server 32 bit)
How i tune database and sql [message #321226] Mon, 19 May 2008 08:33 Go to next message
pradies
Messages: 245
Registered: May 2008
Senior Member
Dear All,

I am facing regarding the database performance and sql tuning. when my database users upto the range of apprx 200 then my database performance is quite normal then the users reached above the range of 250. The same query which is running fine previously same query taking too much time to generate the output. My SGA size is approx 24MB and I am sending the attachment of statspack report for that database along with this.



Thnax in Advance


Regards

Pradeep Sharma
  • Attachment: 19may.LST
    (Size: 85.12KB, Downloaded 289 times)
Re: How i tune database and sql [message #321233 is a reply to message #321226] Mon, 19 May 2008 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you done & what were the results?
Re: How i tune database and sql [message #321243 is a reply to message #321226] Mon, 19 May 2008 09:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a summary of my thoughts reading through the Report.

Your Statspack is over too long a time. Try one over about 15 minutes.

It does show that you're spending a lot of time waiting on (probably) index range scans and index unique scans - you've got a lot of DB Sequential Reads in there.

I'm guessing that over the 250 user mark, you've just passed beyond your disks capacity to get the data fast enough, and so everyone start to wait.

You've got a lot of queries like this:
select MAX(TRN_DATE) TRN_DATE from FIN_VCH_HDR where unit_code='P01' and JRNAL_TYPE='CPV' AND TRN_DATE <= TO_DATE('31/05/2008','DD/MM/YYYY')
that should be using bind variables, and are doing about 32,000 physical read per execution - get them tuned.

In fact, your system really needs to use bind variables more. There are lots of queries in there that differ only in hard coded variables.

All the queries in the SQL Ordered by Gets section are worth looking at - the top query might need to do 4.8 million buffer gets, but it may well not need quite that many.

Similarly, the queries in SQL Ordered by Physical Reads could live with some tuning.


Are you using a RAID array - if not, you need to be. Your Finance .dbf datfile is the main source of your read waits, and needs to be spread over as many disk heads as possible.


The summary:
1) Tune your SQL
2) Use bind variables.
Previous Topic: Please help tuning SQL
Next Topic: cursor: pin S wait on X in Oracle 10.2.0.4
Goto Forum:
  


Current Time: Mon Dec 05 18:52:52 CST 2016

Total time taken to generate the page: 0.16844 seconds