Re: "control file sequential read" on RAC

Thanks Jared for pointing out.

Alex G.'s query works fine, just as the RULE hint. :)

On 5/15/07, Jared Still <jkstill_at_gmail.com> wrote:
>
> 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