I have the exact same explain plan when I run it, and it takes 2 seconds for me. What is this session waiting on? What are the system waits? Check v$system_event, v$system_wait, v$session_event, and v$session_wait.
>From: "Bunyamin Karadeniz" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: Extremely slow query >Date: Sun, 16 Jun 2002 23:28:19 -0800 > >Set autotrace traceonly explain normally causes extra seconds for the >query. >But this query only lasts for 20 seconds in my database. >Is hash_join_enabled=y ? what is your hash_area_size ?It must not be too >small. Must be big as to encapsulate the smallest table in the query . > >you can try merge join . > >Bunyamin > > > >----- Original Message ----- >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >Sent: Saturday, June 15, 2002 3:03 AM > > > > > * Solaris 2.6 > > > * Oracle RDBMS v8.0.5.2.1 > > > > > List: > > We are (still) having difficult with a production database. (Users > > experiencing severe slowness at times.) I'm constructing a series of > > queries to run at intervals to check the health/status of the system. > > > > I'm attempting to run the query below. (Got it from Metalink.) This >script > > takes 3 to 4 minutes to run (regardless of whether it returns any rows). > > The same script runs in less than 1 second on the test database on the >same > > box, as well as other databases on different Solaris and VMS databases. > > > > This is particularly curious. Thought I'd run an explain plan for >grins. > > The explain plan is identical on this database as on the others. >However, > > there's a 3 to 4 minute wait before the explain plan shows up. (It's > > instantaneous on other databases.) In other words, if I type in "set > > autotrace traceonly explain" and then @find_locks, the explain plan does >not > > appear for several minutes. > > > > I'm not sure where to start looking. My other health/status scripts run >in > > normal amounts of time. It's only this 1 script that's a difficulty. > > > > Any ideas? I'm not sure where to start looking. I'm certainly curious >to > > know if this is in any way related to other problems we're having with >this > > database, but I don't see the connection. > > > > Thanks for any help. > > > > Barb > > > > > > SET ECHO off > > REM NAME: TFSLKILL.SQL > > REM USAGE:"@path/tfslkill" > > > > set linesize 132 pagesize 66 > > break on Kill on username on terminal > > column Kill heading 'Kill|String' format a9 > > column res heading 'Resource Type' format 999 > > column id1 format 9999990 > > column id2 format 9999990 > > column lmode heading 'Lock Held' format a20 > > column request heading 'Lock|Requested' format a10 > > column serial# format 99999 > > column username format a8 heading "Username" > > column terminal heading Term format a7 > > column tab format a21 heading "Table Name" > > column owner format a9 > > column Address format a18 > > select nvl(S.USERNAME,'Internal') username, > > nvl(S.TERMINAL,'None') terminal, > > L.SID||','||S.SERIAL# Kill, > > U1.NAME||'.'||substr(T1.NAME,1,20) tab, > > decode(L.LMODE,1,'No Lock', > > 2,'Row Share', > > 3,'Row Exclusive', > > 4,'Share', > > 5,'Share Row Exclusive', > > 6,'Exclusive',null) lmode, > > decode(L.REQUEST,1,'No Lock', > > 2,'Row Share', > > 3,'Row Exclusive', > > 4,'Share', > > 5,'Share Row Exclusive', > > 6,'Exclusive',null) request > > from V$LOCK L, > > V$SESSION S, > > SYS.USER$ U1, > > SYS.OBJ$ T1 > > where L.SID = S.SID > > and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) > > and U1.USER# = T1.OWNER# > > and S.TYPE != 'BACKGROUND' > > order by 1,2,5 > > ; > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=154 Card=6698 Bytes= > > 1024794) > > 1 0 SORT (ORDER BY) (Cost=154 Card=6698 Bytes=1024794) > > 2 1 NESTED LOOPS (Cost=1 Card=6698 Bytes=1024794) > > 3 2 NESTED LOOPS (Cost=1 Card=82 Bytes=10086) > > 4 3 NESTED LOOPS (Cost=7 Card=1 Bytes=80) > > 5 4 NESTED LOOPS (Cost=6 Card=1 Bytes=60) > > 6 5 NESTED LOOPS (Cost=2 Card=1 Bytes=40) > > 7 6 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=1 Card=1 > > Bytes=20) > > 8 6 FIXED TABLE (FIXED INDEX #1) OF 'X$KSUSE' >(Cost=1 > > Card=1 Bytes=20) > > 9 5 VIEW OF 'GV$_LOCK' > > 10 9 UNION-ALL > > 11 10 VIEW OF 'GV$_LOCK1' (Cost=2 Card=2 Bytes=40) > > 12 11 UNION-ALL > > 13 12 FIXED TABLE (FULL) OF 'X$KDNSSF' (Cost=1 > > Card=1 Bytes=20) > > 14 12 FIXED TABLE (FULL) OF 'X$KSQEQ' (Cost=1 > > Card=1 Bytes=20) > > 15 10 FIXED TABLE (FULL) OF 'X$KTADM' (Cost=1 Card=1 > > Bytes=20) > > 16 10 FIXED TABLE (FULL) OF 'X$KTCXB' (Cost=1 Card=1 > > Bytes=20) > > 17 4 FIXED TABLE (FIXED INDEX #1) OF 'X$KSQRS' (Cost=1 > > Card=100 Bytes=2000) > > 18 3 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=1 >Card=8168 > > Bytes=351224) > > 19 18 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) > > 20 2 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=1 Card=8168 > > Bytes=245040) > > 21 20 INDEX (UNIQUE SCAN) OF 'I_USER#' (CLUSTER) > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Baker, Barbara > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing Lists > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Bunyamin Karadeniz > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists >-------------------------------------------------------------------- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _________________________________________________________________ Join the world�s largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Killough INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
