Re: "control file sequential read" on RAC
- From: "Shivaswamy Raghunath" <shivaswamykr@xxxxxxxxx>
- To: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
- Date: Tue, 15 May 2007 10:29:42 -0400
When something does not work, we hear all those caveats, right? :)
How about this extract for the same query, with a hint, on the same
database.? 1725 to 7 sec?!
********************************************************************************
SELECT NVL(SUM(MAXBYTES),0)
FROM
DBA_DATA_FILES WHERE TABLESPACE_NAME=:B1 AND AUTOEXTENSIBLE ='YES'
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 394 0.10 0.10 0 0
0 0
Fetch 394 4.41 7.08 0 3156 158782
394
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 789 4.52 7.19 0 3156 158782
394
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
control file sequential read 8288 0.00
3.01
********************************************************************************
I used a RULE hint, yes on 10.2 database.
I found that, x$kccfe which is used in the defenition of dba_data_files (Now
in 10.2 to give the ONLINE_STATUS Of the data file) is causing the issue.
But I have not yet gone in to the root cause.
Meanwhile, if you have more insight do let me know.
On 5/15/07, Alex Gorbachev <gorbyx@xxxxxxxxx> wrote:
One way would be to use WITH clause and make Oracle instantiating
temporary tables that are then joined.
On 5/15/07, Norman Dunbar <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> I've been told by Oracle trainers that joining a V$ view to an physical
> table or other dictionary tyope objkect is fraught with danger because
--
Best regards,
Alex Gorbachev
http://www.oracloid.com
Other related posts: