| Oracle Internal Query [message #460404] |
Fri, 11 June 2010 01:45  |
jarcher
Messages: 3
Registered: March 2006
Location: India
|
Junior Member |
|
|
Hi,
Observing that some of the oracle internal queries taking long time. One of the query is shown below. fyi - this is a volume DB approximately 3-4 TB.
Is there something can be tuned to bring down the fetch time for the SQLs. Also, there are other application queries whose fetch time is huge / execute.
SQL ID: cvn54b7yz0s8u
Plan Hash: 3246118364
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece
from
idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 232 0.00 0.00 0 0 0 0
Execute 232 0.01 0.01 0 0 0 0
Fetch 826 0.07 40.72 832 3208 0 806
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1290 0.09 40.75 832 3208 0 806
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=5 pr=2 pw=0 time=0 us cost=4 size=20 card=1)
1 INDEX RANGE SCAN I_IDL_UB11 (cr=4 pr=1 pw=0 time=0 us cost=3 size=0 card=1)(object id 110)
|
|
|
|
|
|
| Re: Oracle Internal Query [message #460421 is a reply to message #460404] |
Fri, 11 June 2010 03:10  |
Frank Naude
Messages: 4579
Registered: April 1998
|
Senior Member |
|
|
It doesn't look particularly expensive.
The query was executed 232 times, returned 1290 rows and only took 0.09 CPU seconds.
Total elapsed time is 40.75 secs or 0.176 secs per execute.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
