3

I program, administer, and support an ERP application. One of my more common tasks is to understand where data originates, finding the link between a particular report field and its data entry field in the application. To do this, I have to manually examine the DDL SQL of one or more views in order to understand the table columns, in order to match one view's table columns with another view's table columns. This manual task might require examination of four or five abstraction layers.

Is there a faster way to examine the metadata to find how a view column is calculated from its base table column? Is there a way to examine Oracle's execution plan to see the rewritten query, skipping the manual inspection of the intermediate views?

5

Starting with 12c, we have DBMS_UTILITY.EXPAND_SQL_TEXT.

You basically pass the query text through the first, input parameter, and receive the rewritten query back through the second, output parameter.

But since you are on 11.2, you can not use this yet. You can however collect the optimizer trace. If you already ran your query, and have its sql_id:

begin
  dbms_sqldiag.dump_trace
  (
    p_sql_id=>'1a2b3c4d5e6f',
    p_child_number=>0,
    p_component=>'optimizer',
    p_file_id=>'optimzer_trace'
  );
end;
/

Or if you do not know the sql_id and did not run the query before:

alter session set events 'trace[rdbms.sql_optimizer.*]';
-- run your query here, no need to wait for results, it just need to be hard parsed
alter session set events 'trace[rdbms.sql_optimizer.*] off';

These will generate a trace file that you can read and find the final, rewritten query in it.

To be honest, the output produced by all methods can be difficult to read once you have some complex queries, and the way to get them is not so trivial, so I usually end up reading and "parsing" the DDL statements manually...

1

Do you have access to the execution plan? This will show how Oracle resolves view columns to base indexes or tables. You can create simple, dummy queries to focus on the particular items in which you're interested.

  • Two views that reference the same table might have execution plans that read from two different indexes. This could be solved with a LEFT JOIN against DBA_INDEXES for the base table, taking the NVL of either the table from the plan or the table from the index from the plan. – durette May 12 '17 at 4:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.