Re: Extremely slow query

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).