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