| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Extremely slow query
You can run this in another window to see if the session is waiting
on particular resource:
select
s.username username, e.event event, s.sid, e.p1text, e.p1, e.p2text, e.p2, e.wait_time, e.seconds_in_wait, e.state
and s.sid = e.sid
order by s.username, upper(e.event)
/
Or, you can turn tracing on for the session in question:
prompt
prompt Turn ON SQL Trace on for a current session
prompt
col csid noprint new_value usid
col cserial noprint new_value userial
prompt Session ID (SID) ?
set term off feed off
select '&1' csid from dual;
select serial# cserial
from v$session
where sid = &&usid;
set term on feed on;
exec sys.dbms_system.set_sql_trace_in_session(&&usid, &&userial, TRUE)
undef 1
Jared
"Baker, Barbara" <bbaker_at_denvernewspaperagency.com>
Sent by: root_at_fatcity.com
06/14/2002 05:03 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Extremely slow query
> * Solaris 2.6
> * Oracle RDBMS v8.0.5.2.1
>
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'
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=1Card=8168
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: INET: Jared.Still_at_radisys.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 - 22:38:22 CDT
 |
 |