RE: views on views on views
Date: Fri, 27 Mar 2009 17:02:51 +0200
Here's a little technical detail I've found very useful with Oracle 10.2 when troubleshooting bad plans through views on views on views.
In 10.2 you can set _dump_qbc_tree=1 and hard parse the query against the view. This will dump you the parsed query block tree into tracefile - with objects resolved all the way to the base tables (so you'll *see* through the views easily, without needing to manually parse this stuff in your head).
So if you do this:
SQL> create view myview as select * from all_users;
And query this:
SQL> select count(*) from myview;
The _dump_qbc_tree gives you following "SQL" against base tables:
- ACTION NAME:() 2007-09-16 12:19:57.500
- MODULE NAME:(SQL*Plus) 2007-09-16 12:19:57.500
- SERVICE NAME:(SYS$USERS) 2007-09-16 12:19:57.500
- SESSION ID:(146.1984) 2007-09-16 12:19:57.500 QCSDMP: ------------------------------------------------------- QCSDMP: SELECT: (qbc=2B8D1C28) QCSDMP: . (COUNT(*)) (opntyp=2 opndty=0) QCSDMP: FROM: QCSDMP: .MYVIEW QCSDMP: VQB: QCSDMP: SELECT: (qbc=2B8D163C) QCSDMP: .USERNAME QCSDMP: FROM: QCSDMP: .ALL_USERS QCSDMP: VQB: QCSDMP: SELECT: (qbc=2B8CAF78) QCSDMP: U.NAME (USERNAME) QCSDMP: FROM: QCSDMP: SYS.TS$ (TTS) QCSDMP: SYS.TS$ (DTS) QCSDMP: SYS.USER$ (U)
I once wrote an article about it too:
-- Regards, Tanel Poder http://blog.tanelpoder.com Oracle Certified Master Oracle ACE Director OakTable Network Member -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 27 2009 - 10:02:51 CDT