what are the most expensive queries?

[Turin]

Hi, anybody know how to extract the queries that are executing and the cost of them (processing, buffers, sorting, etc) ?
I want to know what is the cost of these queries in order to improve performance.

Thank you for your help.

[sreddy]

You do not need cost of any query until the results are unacceptable. Fix things only when they break (My policy in tuning atleast). So, you know whats performing in unacceptable limits when people complain, take it from there and try tuning that component/query using oracle native tools like TKPROF, STATSPACK or other 3rd party tools.

[Turin]

Good answer.
Actually I'm performing some analysis to databases but nobody has complained yet. I want to go a forward step, before the problem arrives. Meanwhile I'll follow your advice.

[gandolf989]

The Oracle tuning pack is a great tool for finding problem sql before anyone knows it is a problem. I don't always foolow its advice exactly but it points me in the right direction. I think that time wise it is a lot more effective than tkprof and statspack. There are other programs that will probably give you the same thing.

[Turin]

Recently I found this sqlplus script in the Internet. Any ideas ?

-- #############################################################################################
--
-- %Purpose: Show the most resource intensive SQL statements that have been recently executed
--
-- Displays a list of the most resource intensive SQL statements
-- that have been recently executed. Resource use is ranked by the
-- number of SGA buffer gets, which is a good indicator of the work done.
-- Only statements that are still cached in the SGA are searched -
-- statements are discarded using an LRU algorithim.
--
-- #############################################################################################
--
set linesize 1200 verify off feedback 100

accept gets default 100000 prompt "Min buffer gets [100,000] "

col sql_text for a1000

select
s.BUFFER_GETS,
s.DISK_READS,
s.ROWS_PROCESSED,
s.EXECUTIONS,
substr(u.NAME,1,10) Username,
s.SQL_TEXT
from
v$sqlarea s,
sys.user$ u
where
s.buffer_gets > &&gets and
s.parsing_user_id = u.user# and
u.name <> 'SYS'
order by
s.buffer_gets desc
/
set feedback on