| URGET:-SQL statements not performing well. [message #65391] |
Tue, 07 September 2004 02:12  |
Milind Deshpande
Messages: 93
Registered: May 2004
|
Member |
|
|
Dear Friends,
I have a problem related to SQL Tuning.My queries run fast early in the morning when sessions are less on the DB and the performance is reduced gradually till the end of the day.The Database is up 24x7.I dont understand what to do because my hit ratios are like this:-
SGA CACHE STATISTICS
********************
LIBRARY CACHE HIT RATIO(SQL AREA) = 99.96
DICTIONERY CACHE HIT RATIO = 99.65
BUFFER CACHE HIT RATIO = 80.41
REDO LOG SPACE REQUESTS = 17
INIT.ORA SETTING
****************
SHARED POOL SIZE = 209715200 Bytes
DB BLOCK BUFFER = 536870912 Blocks
LOG BUFFER = 524288 Bytes
I find Library Cache Hit Ratio to be optimal everytime in a day.Then what could be the Problem.Can anybody tell me some steps that i should perform on the database so that I will get Optimal Performance of SQL's throughout the day.I have already analyzed the explain plan of the SQL's which contain no Full Table scans and the Resource cost is also less.The queries are using CBO.The Database server is situated locally.
Kindly,help me on this.
Thanks is advance.
Milind.
|
|
|
|
| Re: URGET:-SQL statements not performing well. [message #65396 is a reply to message #65391] |
Tue, 07 September 2004 17:26  |
Bruce McCartney
Messages: 7
Registered: September 2004
|
Junior Member |
|
|
Milind:
You are likely experiencing excessive I/O caused by poor SQL; and you need to find out which SQL is causing the most Logical an Physical I/O's and reduce the IO requirements via tuning. At this point, I would sugest you examine the V$SQLAREA to determine which SQL is causing the most and attack it. Also look at the v$system_event event table for excessive waits. You can use statspack to gather this info and create reports
ps. sometimes a full table scan is the best thing to do.
good luck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
