Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: TOP SQL

RE: TOP SQL

From: Juan Miranda <j.miranda_at_sermatica.es>
Date: Wed, 10 Nov 2004 08:37:46 +0100
Message-Id: <20041110073747.5D0F43D6C3B@smtp-01.servidoresdns.net>

prompt SQL to identify heavy SQL (Get the SQL with heavy BUFFER_GETS)

select sql_text ,executions ,disk_reads ,buffer_gets   from v$sqlarea
  where decode(executions,0,buffer_gets,buffer_gets/executions)

         > (select
avg(decode(executions,0,buffer_gets,buffer_gets/executions))

                   + stddev(decode(executions,0,buffer_gets
,buffer_gets/executions))
            from v$sqlarea)

and parsing_user_id !=3D 0
/

prompt SQL to identify heavy SQL (Get the SQL with heavy DISK_READS)

select sql_text ,executions ,disk_reads ,buffer_gets   from v$sqlarea
  where decode(executions ,0,disk_reads,disk_reads/executions)

         > (select
avg(decode(executions,0,disk_reads,disk_reads/executions))

                   +
stddev(decode(executions,0,disk_reads,disk_reads/executions))
            from v$sqlarea)

and parsing_user_id !=3D 0
/
-------------------------------------------------------------------------=
---
----------

prompt 10+ SQL que realizan FULL TABLE SCAN ordenados por LECTURAS DE =
DISCO
(sin SYS)
prompt OJO con la columna EXECUTIONS

select * from=20
(select t.*,p.operation,p.options from v$sqlarea t, v$sql_plan p
	where t.hash_value=3Dp.hash_value and p.operation=3D'TABLE ACCESS' and
p.options=3D'FULL'
	and p.object_owner not in ('SYS','SYSTEM')
	order by DISK_READS DESC, EXECUTIONS DESC)
where rownum <=3D 10;


-------------------------------------------------------------------------=
---
------------

prompt 10+ SQL que realizan FULL TABLE SCAN ordenados por BUFFER_GETS =
(sin
SYS)
prompt OJO con la columna EXECUTIONS

select * from=20
(select t.* from v$sqlarea t, v$sql_plan p
	where t.hash_value=3Dp.hash_value and p.operation=3D'TABLE ACCESS' and
p.options=3D'FULL'
	and p.object_owner not in ('SYS','SYSTEM')
	order by BUFFER_GETS DESC, EXECUTIONS DESC)
where rownum <=3D 10;

-------------------------------------------------------------------------=
---
------------




prompt =
##################################################################
prompt Top 10 by Buffer Gets:=20
SELECT * FROM=20
(SELECT sql_text, buffer_gets, executions, buffer_gets/executions
"Gets/Exec", hash_value,address=20
   FROM V$SQLAREA=20
  WHERE buffer_gets > 10000=20
 ORDER BY buffer_gets DESC)=20
WHERE rownum <=3D 10=20

/
=20 prompt = ################################################################## prompt Top 10 by Physical Reads:=20 SELECT * FROM=20 (SELECT sql_text, disk_reads, executions, disk_reads/executions "Reads/Exec", hash_value,address=20 FROM V$SQLAREA=20 WHERE disk_reads > 1000=20 ORDER BY disk_reads DESC)=20 WHERE rownum <=3D 10=20
/
=20 prompt = ################################################################## prompt Top 10 by Executions:=20 SELECT * FROM=20 (SELECT sql_text,executions, rows_processed, rows_processed/executions "Rows/Exec", hash_value,address=20 FROM V$SQLAREA=20 WHERE executions > 100=20 ORDER BY executions DESC)=20 WHERE rownum <=3D 10=20
/=20
=20 prompt = ################################################################## prompt Top 10 by Parse Calls:=20 SELECT * FROM=20 (SELECT sql_text, parse_calls, executions, hash_value,address=20 FROM V$SQLAREA=20 WHERE parse_calls > 1000=20 ORDER BY parse_calls DESC)=20 WHERE rownum <=3D 10=20
/
=20 prompt = ################################################################## prompt Top 10 by Sharable Memory:=20 SELECT * FROM =20 (SELECT sql_text, sharable_mem, executions, hash_value,address=20 FROM V$SQLAREA=20 WHERE sharable_mem > 1048576=20 ORDER BY sharable_mem DESC)=20 WHERE rownum <=3D 10=20
/
=20 prompt = ################################################################## prompt Top 10 by Version Count:=20 SELECT * FROM =20 (SELECT sql_text, version_count, executions, hash_value,address=20 FROM V$SQLAREA=20 WHERE version_count > 20=20 ORDER BY version_count DESC)=20 WHERE rownum <=3D 10=20
/
--set recsepchar '-' --set RECSEP each --Block gets - logical i/o, current mode, usually DML activity.=20 --Consistent gets - logical i/o, consistent mode, usually SELECT = statements=20 --some physical reads may be direct and may bypass the buffer cache. = When that happens, physical reads are recorded but gets are not. --Physical reads - physical i/o, if a block get or consistent get = resulted in a cache miss, it caused a physical i/o.=20 --Block changes - logical i/o, how many changes were applied to blocks = due to DML. (Changes to current mode blocks)=20 --Consistent changes - logical i/o, how many changes were applied to = blocks for read consistency purposes. (Consistent mode changes)=20 set tab off set pages 50 column usern format a6 heading 'User'=20 column substr(s.status,1,3) format a3 heading 'Stat' =20 column program format a20 wrap heading 'Program' =20 column event format a15 wrap heading 'Evento|espera' =20 column SID format 999 heading 'SID' column waitt format A8 heading 'waits'=20 column txt format a200 wrapped heading 'Current Statment' column cpu format 9999 column phread format 99999 heading 'Lecturas|Fisicas' column consistent_gets format 9999999 heading 'Consistent|gets' column spid format A6 heading 'OSpid' column puser format a8 heading 'O/S|ID' noprint column BLOCK_GETS format 999999 heading 'Block|gets' column osu format a6 heading 'OsUser' column sser format 9999 heading 'Serial' prompt Estadisticas de cada session abierta actualmente ordenados por lecturas f=EDsicas select s.username usern, p.username puser, s.osuser osu, a.sid, = trim(p.spid) spid, s.serial# sser,substr(s.status,1,3), s.PROGRAM, a.value cpu, c.BLOCK_GETS, c.CONSISTENT_GETS, c.physical_reads phread, trim(d.seconds_in_wait) waitt, d.event, sa.sql_text txt from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d, v$session s, v$sqlarea sa, v$process p where b.name =3D 'CPU used by this session' and p.addr=3Ds.paddr and a.statistic# =3D b.statistic# and a.sid=3Dc.sid and a.sid=3Dd.sid and a.sid=3Ds.sid AND s.sql_address=3Dsa.address(+) AND s.sql_hash_value=3Dsa.hash_value(+) order by phread DESC
/
-------------------------------------------------------------------------= prompt Show all running SQLs select distinct spid, s.sid, s.serial#,to_char(sysdate - last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY", logon_time, osuser, s.program, schemaname, sql_text from v$session s, v$process p, v$sql t where s.paddr=3Dp.addr and t.hash_value(+)=3Ds.sql_hash_value and s.type !=3D'BACKGROUND'; -----Mensaje original----- De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] = En nombre de Seema Singh Enviado el: martes, 09 de noviembre de 2004 21:47 Para: oracle-l_at_freelists.org Asunto: TOP SQL Hi, Does any one have any good scripts to capture top 5 sql statement which = is=20 hurting to database? The selection of those top 5 sql would be either more IO/more CPU=20 usgae/spining sql etc. thanks _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today - it's = FREE!=20 http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 10 2004 - 01:33:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US