Re: "control file sequential read" on RAC

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 15 May 2007 11:09:23 -0700
Message-ID: <bf46380705151109i7c814ce0o551cc292c5fd021f@mail.gmail.com>

On 5/15/07, Norman Dunbar <norman.dunbar_at_environment-agency.gov.uk> wrote:
>
>
> >> "Alex Gorbachev" <gorbyx_at_gmail.com> 05/15/07 01:28PM >>>
> >> Seems like it's inefficient execution plan.
> >> I know that Oracle might go crazy joining dictionary views and V$
> >> views.
>
> 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:
>
> * the V$ stuff is memory based and not protected by read consistency
> * the V$ stuff changes frequently
>
> When you join V$ to others, each time the V$ changes, Oracle starts the
> query again. It only finishes when it manages to join all of V$ with
> whatever without a change occurring part way through.
>
>

Though I have in the past run into difficulty with v$ views in joins, I've never before heard this bit.

Being something of a skeptic, I would probably need to be convinced with some evidence. :)

The 'ordered' hint has worked well for me in the past when joining V$ views.

For instance:

 1 select /*+ ordered */

  2     --b.kaddr,
  3     c.sid,
  4     c.username,

...
155 from

156     v$lock b
157     ,v$session c
158     ,sys.user$ u
159     ,sys.obj$ o
160     ,( select * from sys.dba_waiters) lock_blocker
161     ,( select * from sys.dba_waiters) lock_waiter

162 where
163 b.sid = c.sid
164 and u.user# = c.user#
165 and o.obj#(+) = b.id1
166 and lock_blocker.waiting_session(+) = c.sid 167 and lock_waiter.holding_session(+) = c.sid 168 and c.username != 'SYS'
169 --order by kaddr, lockwait
170* order by b.sid, object

Without the ordered hint this query can be really slow. Been awhile since I tested it though. I run it regularly on 9i and 10g, but the ordered hint was added for 8i.

Alex G. mentioned the use of subquery factoring (WITH clause).

That makes queries easier to read and offers the ability to materialize the subquery, which might be a good thing.

I recently ran into a problem with it however. When using subquery factoring with a somewhat complex bit of SQL, the optimizer would not come up with a favorable execution plan. The MATERIALIZE and INLINE hints returned the same plans.

The query would only work in a reasonable time when re-written without subquery factoring.

It would be interesting to see if SF would help your query.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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

Received on Tue May 15 2007 - 13:09:23 CDT