Have you looked at System Waits, Session Waits and or event 10046 trace data ? They hold the keys to the kingdom on where you should proceed to diagnose your performance issue.
-----Original Message----- Sent: Friday, June 14, 2002 5:03 PM To: Multiple recipients of list ORACLE-L > * 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Johnson, Michael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).