-
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
-
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!
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|