Select tablespace query... delayed for 1.15 mins...

From: Jose Luis Delgado <joseluis_delgado_at_yahoo.com>
Date: Tue, 17 May 2005 13:12:33 -0700 (PDT)
Message-ID: <20050517201233.7948.qmail@web41012.mail.yahoo.com>

DBAs...

would you mind to review the 10046 extract trace output below...?

I'm querying the list of tablespaces (via OEM), and it is delaying by 1.15 minutes...

the ELA field shows 0, and p3 field shows 2 or 3 blocks...

any idea?

TIA
JL

pd: Apologizes for sending this trace output in plain text.

/usr/local/oracle8i/admin/finderts/udump/finderts_ora_15229.trc Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
ORACLE_HOME = /usr/local/oracle8i/8.1.7
System name: SunOS

Node name:      pemex6803
Release:        5.8
Version:        Generic_117350-02
Machine:        sun4u

Instance name: finderts
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 15229, image: oracle_at_pemex6803 (TNS V1-V3)

2005-05-17 14:56:46.104
SESSION ID:(12.41681) 2005-05-17 14:56:46.101 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
PARSING IN CURSOR #1 len=1539 dep=0 uid=103 oct=3 lid=103 tim=4262158210 hv=2434703858 ad='23913bd4' /* OracleOEM */ SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", TO_CHAR(NVL(a. bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a .bytes/1024/1024, 0), '99999999.999') "Used (M)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba _tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_nam e(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t. bytes, 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') "Used (M)", TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_na me(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY' END OF STMT PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4262158210 BINDS #1: EXEC #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4262158211 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 XCTEND rlbk=0, rd_only=1 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0 WAIT #1: nam='db file scattered read' ela= 1 p1=1 p2=1629 p3=2 WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=1632 p3=1 WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=11677 p3=1 WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0 WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0 WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0 WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1 WAIT #1: nam='refresh controlfile command' ela= 0 p1=0 p2=0 p3=0 WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1 WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=23 p3=1 WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1 WAIT #1: nam='refresh controlfile command' ela= 0 p1=0 p2=0 p3=0 WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1 WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=23 p3=1
2005-05-17 14:58:31.651 WAIT #1: nam='db file scattered read' ela= 21 p1=1 p2=1629 p3=2

PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=4262168765 hv=1705880752 ad='22970548' select file# from file$ where ts#=:1
END OF STMT
PARSE
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168765 BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0

bfp=01a775b0 bln=22 avl=02 flg=05
value=7
EXEC
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168765 FETCH
#2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4262168765 FETCH
#2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168765 STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=1632 p3=1

bfp=01a77570 bln=22 avl=02 flg=05
value=9
EXEC
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 STAT #2 id=1 cnt=2 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID FILE$ '
STAT #2 id=2 cnt=3 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '

PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=4262168766 hv=1705880752 ad='22970548' select file# from file$ where ts#=:1
END OF STMT
PARSE
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0

bfp=01a775b0 bln=22 avl=02 flg=05
value=10
EXEC
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '

bfp=01a77570 bln=22 avl=02 flg=05
value=11
EXEC
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '

   bfp=01a775b0 bln=22 avl=02 flg=05
   value=12
EXEC
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4262168766 FETCH
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4262168766 STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=11677 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1629 p3=2
WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=1632 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=11677 p3=1
FETCH
#1:c=5201,e=10555,p=12,cr=7626046,cu=66,mis=0,r=10,dep=0,og=4,tim=4262168767 *** 2005-05-17 15:01:46.740
WAIT #1: nam='SQL*Net message from client' ela= 19507 p1=1413697536 p2=1 p3=0

Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 17 2005 - 16:17:16 CDT