| TOP 10 QUERIES [message #164215] |
Wed, 22 March 2006 06:07  |
gopikris_g
Messages: 7
Registered: March 2006
|
Junior Member |
|
|
HI,
Is there any way to find the top 10 queries (Source) executed by the data base users at any point of time in oracle 9i/10g.
Regards,
Gopi.
|
|
|
|
|
|
| Re: TOP 10 QUERIES [message #164253 is a reply to message #164215] |
Wed, 22 March 2006 09:58  |
alanm
Messages: 284
Registered: March 2005
|
Senior Member |
|
|
hi,
try the following (this was written in 8i)
PROMPT
PROMPT ##############################################################
PROMPT problem sql scripts. 10 worst sql statements.
prompt split into 2 sections
prompt section 1 = buffer gets
prompt section 2 = disk reads
PROMPT ##############################################################
PROMPT
select c.* from
(select disk_reads,
buffer_gets,
rows_processed,
executions,
first_load_time,
sql_text
from v$sqlarea
where parsing_user_id !=0
order by
buffer_gets/decode(executions,null,1,0,1,executions) desc ) c
where rownum < 11;
select c.* from
(select disk_reads,
buffer_gets,
rows_processed,
executions,
first_load_time,
sql_text
from v$sqlarea
order by
disk_reads/decode(rows_processed,null,1,0,1,rows_processed) desc ) c
where rownum < 11;
rgs
Alan.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
