Top resource consuming queries in Oracle

[Gamoji Raghavendra]

Hello gurus,

I need some help from Oracle experts.

I want to identify the top resource consuming SQL queries running in the database using SQL.

Is it possible? If so how?

Any help is highly appreciated in this regard.

Oracle version is : 8i

bye,
Raghu.

[Steven Gillard_2]

I suggest taking a look at Oracle Enterprise Manager's tuning pack which has some excellent tools for diagnosing this sort of thing.

Otherwise, the following query can give a good idea of what the sessions are doing and how much cpu they have consumed:

select ss.sid,se.command,ss.value cpu ,se.username,se.program
from v$sesstat ss, v$session se
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
order by ss.sid


The V$SQLAREA view can also be a good place to look as it keeps statistics on the queries such as the number of rows processed and the number of disk reads.

Regards,
Steve

[Dave Chamberlin]

The tuning pack with Enterprise manager has Oracle SQL analyze. You can show the top n sql statements using a variety of criteria such as disk reads per execution, cpu use per execution etc. This will certainly identify statements using the most resources. It will also tell you how many executions for each statement. The statements that are executed many times give the best opportunities. The hard part is to know where those statements come from and which ones you can do anything about. If you are running oracle apps, much of your sql is not tunable. If you can identify your company's custom code - that can be tuned. It would be useful to first use a tool like Glance to determine your primary resource bottleneck, so that you can apply your efforts there for maximum effect.