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).
Received on Tue Jun 18 2002 - 13:23:39 CDT
