Re: Extremely slow query

"Baker, Barbara" wrote:
>
> > * 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)
>

Barbara,

  Queries on V$ views (and the underlying X$ tables) are not very easy to tune, because they operate on memory structures, very volatile information, that the optimizer proceeds by guesswork (no stats) and that it doesn't necessarily do a good job at it. Add to this that looking deep inside the database puts yourself in a position well known by physicists, which is that the simple fact of taking a measure modifies the result (cf Werner Heisenberg).

   If I were you I would try the ORDERED hint, putting views and tables in the following order: V$SESSION, V$LOCK, SYS.OBJ$, SYS.USER$. In fact, since you are interested in tables only, it might also be clever to add one more table (yes) to the join, SYS.TAB$ and to join first V$LOCK and SYS.TAB$ on OBJ# as in your query, then SYS.TAB$ and SYS.OBJ$ (on OBJ#), and finally SYS.OBJ$(OWNER#) and SUS.USER$(USER#). The logic behind adding one table is that usually tables are just a small percentage of all (on Oracle 8.1.7 only 9% of all objects owned by SYS are tables - the majority of objects are views) and that by fetching tables directly you will by-pass the rubbish and scan smaller indexes; then getting to the owner name through PKs will be quickly done.   I have tried all this on my home DB without, being by definition alone on it, very significant results. But ORDERED as indicated above shows some hash join in the plan, which, for this type of query without any very significant search criterion makes more sense than nested loops; and adding TAB$ is definitely worth a try.  

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).