I/O Intensive queries on production

Hi All,

Can any one help me in providing the script for finding out first 10 or 20 sql queries which are I/O intensive right from the day the machine was rebooted. My version of Oracle is 9.0.1 and linux 7.1 . Our Production DB is hitting worst by these queries . I need to submit these queries immediately .

Looking forward for your great help.

thanks in advance

Are these queries currently running?
To work out with such problems, I am using Precise, it is a good software to find such problems.

Hi ,
All those queries might not run currently. Right from the time the machine is rebooted, lets say 25 days back, they will be stored in v$sqlarea, but i want a specific query which will return you the most I/O intensive.

Any one please help me this is most urgent

thanks

Quote:

---

Originally posted by pnrdba
Right from the time the machine is rebooted, lets say 25 days back, they will be stored in v$sqlarea.....

---

No, they will be not. They will be aged out eventually. Only the recently executed cursors are stored in sqlarea, and the new ones are pushing old ones out of the shared pool. The number of cursors stored in sqlarea depends on the size of the shared pool, the complexity of SQL statements, the frequency of their executions, etc etc...

For those queries that are in v$sqlarea you can find the most I/O intensive ones by looking at their values in columns: EXECUTIONS, DISK_READS, BUFFER_GETS.

Hi Jmodic,
You are correct. My shared_pool size is 320 MB. to know the queries, i'm using below script as of now. Is this the correct way to meet my requirements?? am i getting the correct output by this query? Please help me.

select address, to_char(hash_value, '999999999999') 'hash value",
disk_reads, executions, disk_reads/executions "reads/executions",
sql_text
from v$sqlarea
where disk_reads > 450 and executions >10
odrer by disk_reads;



Thanks

Gurus,
Please help me

thanks

your query's not bad, I would just order by reads/executions, which is in my opinion, more relevant of a high I/O activity :

select address, disk_reads/executions, sql_text
from v$sqlarea
where executions > 10
order by 2 desc
/

Quote:

---

Originally posted by pnrdba
Gurus,
Please help me

thanks

---

Implement statspack and generate reports using spreport.sql.. You will get all those resource intensive queries in the report.