Re: "control file sequential read" on RAC

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: