RE: views on views on views

From: <Joel.Patterson_at_crowley.com>
Date: Fri, 27 Mar 2009 12:17:51 -0400
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DA0287E242_at_JAXMSG01.crowley.com>

Yes what a good way to end reading 44 messages. A lively discussion with a worthwhile ending.

Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder Sent: Friday, March 27, 2009 11:03 AM
To: oracle-l_at_freelists.org
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-colu mn-n
ames-all-the-way-to-base-tables-and-columns-in-oracle/

--
Regards,
Tanel Poder
http://blog.tanelpoder.com
Oracle Certified Master
Oracle ACE Director
OakTable Network Member

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 27 2009 - 11:17:51 CDT