Re: super-expensive query ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 23 Feb 2007 13:34:49 -0800
Message-ID: <1172266488.829089@bubbleator.drizzle.com>

Ed wrote:
> why is this QUERY so goddamn SLOW ???
> Is this a super-expensive query or what ?
>
>
> select b.username,c.sid,c.owner, c.object,b.lockwait,a.sql_text
> from v$sqltext a,
> v$session b,
> v$access c,
> v$locked_object d,
> dba_objects e
> where a.address=b.sql_address
> and a.hash_value=b.sql_hash_value
> and b.sid=c.sid
> and d.object_id=e.object_id
> and e.object_name=c.object
> and c.owner!='SYS'
> order by b.username ;
>
> 10gR2

EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR select b.username,c.sid,c.owner, c.object,b.lockwait,a.sql_text from v$sqltext a,

      v$session b,
      v$access  c,
      v$locked_object d,
      dba_objects e
where  a.address=b.sql_address
and    a.hash_value=b.sql_hash_value
and    b.sid=c.sid
and    d.object_id=e.object_id

and e.object_name=c.object
and c.owner!='SYS'
order by b.username;

set pagesize 25
set linesize 121

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Feb 23 2007 - 15:34:49 CST