Extremely slow query

From: Baker, Barbara <bbaker_at_denvernewspaperagency.com>
Date: Fri, 14 Jun 2002 16:03:17 -0800
Message-ID: <F001.0047EADA.20020614160317@fatcity.com>

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

Received on Fri Jun 14 2002 - 19:03:17 CDT