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 N queries

RE: Top N queries

From: Juan Miranda <j.miranda_at_sermatica.es>
Date: Tue, 7 Mar 2006 09:20:46 +0100
Message-Id: <20060307082048.BC95955C@smtp-01.servidoresdns.net> 







You can use statspack also.





set wrap on


set tab off


set trimspool on


set linesize 6000


set pagesize 60


set heading on


set serveroutput on


column SQL_TEXT format A80 wrapped





select * from 


(select executions, disk_reads, buffer_gets, sql_text


	from v$sqlarea 
--	where parsing_user_id != 0
	order by disk_reads / decode(executions,0,1,executions) desc)


where rownum <= 10


/










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 != 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 != 0


/









prompt 10+ SQL que realizan FULL TABLE SCAN ordenados por LECTURAS DE DISCO


(sin SYS)



prompt OJO con la columna EXECUTIONS



select * from 


(select t.*,p.operation,p.options from v$sqlarea t, v$sql_plan p



        where t.hash_value=p.hash_value and p.operation='TABLE ACCESS' and
p.options='FULL'

	and p.object_owner not in ('SYS','SYSTEM')
	order by DISK_READS DESC, EXECUTIONS DESC)


where rownum <= 10;









prompt 10+ SQL que realizan FULL TABLE SCAN ordenados por BUFFER_GETS (sin
SYS)



prompt OJO con la columna EXECUTIONS



select * from 


(select t.* from v$sqlarea t, v$sql_plan p



        where t.hash_value=p.hash_value and p.operation='TABLE ACCESS' and
p.options='FULL'

	and p.object_owner not in ('SYS','SYSTEM')
	order by BUFFER_GETS DESC, EXECUTIONS DESC)


where rownum <= 10;










prompt ##################################################################


prompt Top 10 by Buffer Gets: 


SELECT * FROM 



(SELECT sql_text, buffer_gets, executions, buffer_gets/executions

"Gets/Exec", hash_value,address 


   FROM V$SQLAREA 



  WHERE buffer_gets > 10000 


 ORDER BY buffer_gets DESC) 


WHERE rownum <= 10 


/
 


prompt ##################################################################


prompt Top 10 by Physical Reads: 


SELECT * FROM 



(SELECT sql_text, disk_reads, executions, disk_reads/executions

"Reads/Exec", hash_value,address 


   FROM V$SQLAREA 



  WHERE disk_reads > 1000 


 ORDER BY disk_reads DESC) 


WHERE rownum <= 10 


/
 


prompt ##################################################################


prompt Top 10 by Executions: 


SELECT * FROM 



(SELECT sql_text,executions, rows_processed, rows_processed/executions

"Rows/Exec", hash_value,address 


   FROM V$SQLAREA 



  WHERE executions > 100 


 ORDER BY executions DESC) 


WHERE rownum <= 10 


/ 
 


prompt ##################################################################


prompt Top 10 by Parse Calls: 


SELECT * FROM 



(SELECT sql_text, parse_calls, executions, hash_value,address 

  FROM V$SQLAREA 



  WHERE parse_calls > 1000 


 ORDER BY parse_calls DESC) 


WHERE rownum <= 10 


/
 


prompt ##################################################################


prompt Top 10 by Sharable Memory: 


SELECT * FROM  



(SELECT sql_text, sharable_mem, executions, hash_value,address 



   FROM V$SQLAREA 



  WHERE sharable_mem > 1048576 


 ORDER BY sharable_mem DESC) 


WHERE rownum <= 10 


/
 


prompt ##################################################################


prompt Top 10 by Version Count: 


SELECT * FROM  



(SELECT sql_text, version_count, executions, hash_value,address 



   FROM V$SQLAREA 



  WHERE version_count > 20 


 ORDER BY version_count DESC) 


WHERE rownum <= 10 


/






--set recsepchar '-'


--set RECSEP each



--Block gets - logical i/o, current mode, usually DML activity. 
--Consistent gets - logical i/o, consistent mode, usually SELECT statements 
--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. 
--Block changes - logical i/o, how many changes were applied to blocks due
to DML. (Changes to current mode blocks) 
--Consistent changes - logical i/o, how many changes were applied to blocks
for read consistency purposes. (Consistent mode changes) 




set tab off


set pages 50


column usern format a6 heading 'User' 


column substr(s.status,1,3) format a3 heading 'Stat'  
column program format a20 wrap heading 'Program'  
column event format a15 wrap heading 'Evento|espera'  
column SID format 999 heading 'SID'


column waitt format A8 heading 'waits' 


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ísicas



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 = 'CPU used by this session' and
		p.addr=s.paddr
		and a.statistic# = b.statistic#
		and a.sid=c.sid
		and a.sid=d.sid
		and a.sid=s.sid
		AND s.sql_address=sa.address(+)
		AND s.sql_hash_value=sa.hash_value(+)
	order by phread DESC




/







column prog format A8


column username format a8 heading 'User' 



prompt Estadísticas de cada sessión.



select s.sid,username,substr(program,-8,8) prog,substr(status,1,3)
stat,name,value 

	from v$statname n, v$sesstat s, v$session ses 
	where n.STATISTIC#=s.STATISTIC# and ses.sid=s.sid and s.value<>0 
	order by s.sid




/






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=p.addr




    and t.hash_value(+)=s.sql_hash_value
    and s.type !='BACKGROUND';





-----Mensaje original-----


De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En
nombre de RACHID TAMBA


Enviado el: martes, 07 de marzo de 2006 0:36
Para: oracle-l_at_freelists.org


Asunto: Top N queries



Hi All :



Any one has an idea how to write a script ( on UNIX ) to find out the top N 
queries which are consuming a lot of resources ?



Thank you.





Take charge with a pop-up guard built on patented Microsoft® SmartScreen 
Technology. 


http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=htt
p://hotmail.com/enca&HL=Market_MSNIS_Taglines 
  Start enjoying all the benefits of MSN® Premium right now and get the 
first two months FREE*.


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Received on Tue Mar 07 2006 - 02:20:46 CST












Original text of this message













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