FW: Extremely slow query

> 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:mwkillough_at_hotmail.com]
> Reply To: ORACLE-L_at_fatcity.com
> 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" <bunyamink_at_havelsan.com.tr>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >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" <ORACLE-L_at_fatcity.com>
> >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: bbaker_at_denvernewspaperagency.com
> > >
> > > 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: ListGuru_at_fatcity.com (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: bunyamink_at_havelsan.com.tr
> >
> >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: ListGuru_at_fatcity.com (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: mwkillough_at_hotmail.com
>
> 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: ListGuru_at_fatcity.com (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: bbaker_at_denvernewspaperagency.com

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: ListGuru_at_fatcity.com (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).