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