> It's not consistently waiting on anything. I'm running the scripts from > Perf Tuning 101 (Chapter 2) as this executes. > This session shows these waits: > > Total Time > Avg > SID Username EVENT Waits Waited > Wait > ------ ---------- ------------------------------ -------- -------- > ---------- > > 439 SYS latch free 14 5 > .357142857 > 439 SYS db file sequential read 486 130 > .267489712 > 439 SYS SQL*Net message from client 43 8550 > 198.837209 > 439 SYS SQL*Net message to client 44 1 > .022727273 > 439 SYS file open 2 0 > 0 > 439 SYS direct path write 3 2 > .666666667 > > The script that joins v$session and v$session_wait pulls no data after > several repeated attempts to run. Info from v$session_wait looks like > this: > > > > SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT > P3 > ----- ---------------- ---------- ----------- -------- -------- -------- > ------ > 1 pmon timer duration 300 0 > 0 > 6 smon timer sleep time 300 failed 0 > 0 > > > I tried Stephane's suggestion to included an ordered-by hint, but it > didn't seem to help. > > Seems odd to me. Should run in just a second. It's still taking nearly 5 > minutes to run. > > I wonder if one of my underlying views is somehow screwed up??? > > Barb > > > ---------- > From: Mike Killough[SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Tuesday, June 18, 2002 12:23 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Extremely slow query > > 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). > > -- 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).
