RE: views on views on views
- From: "Michael McMullen" <ganstadba@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Mar 2009 11:10:54 -0400
This is why mailing lists still rule!!!
Great tip.
Thanks
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tanel Poder
Sent: Friday, March 27, 2009 11:03 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: views on views on views
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;
View created.
And query this:
SQL> select count(*) from myview;
COUNT(*)
----------
31
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)
This has been quite useful time-saver, especially in cases where I've never
seen the database/schema before...
I once wrote an article about it too:
http://blog.tanelpoder.com/2007/09/16/how-to-resolve-sql-object-and-column-n
ames-all-the-way-to-base-tables-and-columns-in-oracle/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
