Re: "control file sequential read" on RAC

From: Shivaswamy Raghunath <shivaswamykr_at_gmail.com>
Date: Tue, 15 May 2007 10:29:42 -0400
Message-ID: <1e52ad820705150729k2650eeabidd4036e487215a56@mail.gmail.com>

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_at_gmail.com> 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_at_environment-agency.gov.uk> 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
>

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

Received on Tue May 15 2007 - 09:29:42 CDT