-
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
PNRDBA
-
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
PNRDBA
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
PNRDBA
-
Gurus,
Please help me
thanks
PNRDBA
-
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
/
-
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.
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
|