Home » RDBMS Server » Performance Tuning » URGET:-SQL statements not performing well.
URGET:-SQL statements not performing well. [message #65391] Tue, 07 September 2004 02:12 Go to next message
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 Go to previous message
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
Previous Topic: Help with using DBMS.PROFILER
Next Topic: Usage of 4GTon Win2k
Goto Forum:
  


Current Time: Sun Sep 27 02:54:58 CDT 2020