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: how to find the most resource intensive sql?

RE: how to find the most resource intensive sql?

From: Juan Miranda <j.miranda_at_sermatica.es>
Date: Fri, 12 Mar 2004 13:10:18 +0100
Message-Id: <20040312121101.8D3BD14F18D@smtp-01.servidoresdns.net>

Here are some querys that can help you.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


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

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

/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++
-----Mensaje original-----
De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Ryan
Enviado el: viernes, 12 de marzo de 2004 4:16 Para: oracle-l_at_freelists.org
Asunto: Re: how to find the most resource intensive sql?

it was a question. they were looking for a buzzword response. I don't have one. I thought there might be one.
----- Original Message -----
From: "Tim Johnston" <tjohnston_at_quallaby.com> To: <oracle-l_at_freelists.org>
Sent: Thursday, March 11, 2004 7:22 PM
Subject: Re: how to find the most resource intensive sql?

> Can they start a sql trace from the app?  If not, can you identify the
> session (i.e. By looking at things like the machine, program and
> username fields in v$session)?  If so, have the user log on and start a
> trace in their session (check out the dbms_system package)...  Have the
> user run through screens until the encounter the problem...  Then
> examine the resulting trace file for the problem SQL...  Usually, I skip
> straight to a 10046 at level 12 so I also get the bind variables and
> wait events...  oops...  make that  timed events...
>
> :-)
>
> Tim
>
> Lim, Binley wrote:
>
> >
> >
> >>'A user comes to you and says they get to a point in the application and
> >>
> >>
> >hit
> >
> >
> >>a button. It's slow. You do not have any access to the application. How
do
> >>you find the problem query in the database'
> >>
> >>
> >
> >In a (preferably) test system, ask the user to get to the point just
before
> >the "button".
> >
> >Flush the shared_pool, then push the button.
> >
> >This communication is confidential and may contain privileged material.
> >If you are not the intended recipient you must not use, disclose, copy or
retain it.
> >If you have received it in error please immediately notify me by return email
> >and delete the emails.
> >Thank you.
> >----------------------------------------------------------------
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >----------------------------------------------------------------
> >To unsubscribe send email to:  oracle-l-request_at_freelists.org
> >put 'unsubscribe' in the subject line.
> >--
> >Archives are at http://www.freelists.org/archives/oracle-l/
> >FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> >-----------------------------------------------------------------
> >
> >
>
> -- 
> Regards,
> Tim Johnston
> Tel: 978-322-4226
> Fax: 978-322-4100
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Mar 12 2004 - 06:08:29 CST

Original text of this message

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