| How i tune database and sql [message #321226] |
Mon, 19 May 2008 08:33  |
pradies
Messages: 250
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 1358 times)
|
|
|
|
|
|
| Re: How i tune database and sql [message #321243 is a reply to message #321226] |
Mon, 19 May 2008 09:15  |
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')
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
