Re: "control file sequential read" on RAC

  • From: "Jared Still" <jkstill@xxxxxxxxx>
  • To: norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx
  • Date: Tue, 15 May 2007 11:09:23 -0700

On 5/15/07, Norman Dunbar <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>> "Alex Gorbachev" <gorbyx@xxxxxxxxx> 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$

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

Other related posts: