DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: extremely high physical reads

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Hi,
    I have an application that is doing a lot of physical reads with the hit ratio at an appalling 24%. How can I tune this application or database to achieve a 90+% recommended hit ratio? All other applications on the db are at 98+ to 100% hit ratio.


    Thanks,
    Leonard905
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Check the session sql statments and run and explain plan and see what was the execution plan for that qury. If you can tune the query go ahead and tune it.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    For check the session sql statments use query like this:

    COLUMN txt FORMAT A50 word
    heading 'SQL text' justify left
    COLUMN username FORMAT A20 heading 'User name' justify left
    COLUMN sid FORMAT 9999 heading 'SID' justify right
    COLUMN s# FORMAT 99999 heading 'Serial' justify right
    COLUMN cur_cnt FORMAT 99999 heading '#|curs' justify right
    COLUMN sort FORMAT 99999 heading 'Disk|sorts' justify right
    COLUMN mem FORMAT 999G999G999 heading 'Total|memory' justify right
    COLUMN exe FORMAT 999G999G999 heading 'Total|exec' justify right
    COLUMN gets FORMAT 99999999 heading 'Blocks|get' justify right
    COLUMN reads FORMAT 99999999 heading 'Blocks|read' justify right
    COLUMN prows FORMAT 99999999 heading '#|rows' justify right
    COLUMN hit FORMAT A24
    heading 'Hit ratio' justify left

    select
    s.sid sid,
    s.serial# s#,
    s.username username,
    a.sql_text txt,
    count(c.sid) cur_cnt,
    sum(sharable_mem)+sum(runtime_mem) mem,
    sum(executions) exe ,
    decode(sum(buffer_gets),0,'0',
    ltrim(to_char(100-(sum(disk_reads)/(sum(disk_reads)+sum(buffer_gets))*100),'990D99'))||'% ('||
    ltrim(to_char(sum(disk_reads),'99999999999'))||'/'||
    ltrim(to_char(sum(buffer_gets),'999999999999'))||')') hit,
    sum(sorts) sort,
    sum(rows_processed) prows
    from v$session s,
    v$open_cursor c,
    v$sqlarea a
    where s.SQL_ADDRESS=c.ADDRESS(+) and
    s.username is not null and
    s.SQL_ADDRESS=a.ADDRESS
    group by s.sid,
    s.serial#,
    s.username,
    s.SQL_ADDRESS,
    c.ADDRESS,
    a.sql_text
    order by 3,1;

    COLUMN txt clear
    COLUMN username clear
    COLUMN sid clear
    COLUMN s# clear
    COLUMN cur_cnt clear
    COLUMN sort clear
    COLUMN mem clear
    COLUMN exe clear
    COLUMN gets clear
    COLUMN reads clear
    COLUMN prows clear
    COLUMN hit clear


  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    It is also depena on how often this application is used. It sounds as though you are running multiple applications from the same database... maybe having different tablesapce and users for the one database. If the data in this applicaiton isn't accessed very frequntly, there is a large change the data is being read from the disks because the blocks that are required are aged out of the Buffer Cache bcause the other aplications are being used more frequntly.

    Just a thought.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Oct 2001
    Posts
    122
    It's time to tune your database and application.

    Some great scripts and help are available at
    http://www.oracletuning.com

    Have a fun.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width