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$
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
