View Expansion in Oracle

Question

So we have some developers who went a little view happy. So now we have views that reference views that reference views, ad nauseum.

So what I want, in order to assist me in Tuning, is to expand these views.

I want a function that takes a string and returns a string. The input string is the query, the output string is the same query without views.

CREATE OR REPLACE VIEW myView AS
SELECT * FROM emp

Using function/stored procedure "F":

F('SELECT * FROM myView') 

...would return:

SELECT * FROM ( SELECT * FROM emp)

---

1. Is there an Oracle package for this?
2. Does someone have code in:
   1. either SQL or PL/SQL
   2. In something else

Answer 1

Short answer:

1. Not at this time

2. Not that I'm aware of

UPDATE

It looks like Oracle 12c has exactly what you need: DBMS_UTILITY.expand_sql_text

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_util.htm#ARPLS73973

Answer 2

One problem with what you are proposing is that there are usually multiple ways that a query involving views can be rewritten, so simply expanding the text of the views won't necessarily tell you a lot about how the query is being executed.

Since your purpose is tuning, I would suggest that the execution plans for the queries will probably give you the information you really need. This won't show the rewritten query, but it will show you all the actual tables and how they are referenced in executing the query.

The best way I know of to view the actual execution plan is:

SELECT /*+ gather_plan_statistics */ * FROM myView

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))