| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: TOP SQL
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)
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)
-------------------------------------------------------------------------=
---
----------
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
 |
 |