Select tablespace query... delayed for 1.15 mins...
- From: Jose Luis Delgado <joseluis_delgado@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 May 2005 13:12:33 -0700 (PDT)
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@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
=====================
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=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 '
=====================
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=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 '
=====================
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=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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Select tablespace query... delayed for 1.15 mins...
