Tuesday, August 14, 2007
Tip#3 Autotrace and MV Capabilities
MV CAPABILITIES :
Using MV Capabilities table explains what is possible with a materialized view or potential materialized view e.g. sometimes I used it to find out why MV cannot be fast refreshed or on commit refreshed.
-- create MV_CAPABILITIES_TABLE
-- invoke EXPLAIN_MVIEW with the materialized view to explain
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('MV_NAME');
-- use the SEQ column in an ORDER BY clause so the rows will display in a logical order.
-- If a capability is not possible, N will appear in the P column and an explanation in the MSGTXT column.
-- If a capability is not possible for more than one reason, a row is displayed for each reason.
SELECT capability_name, possible, SUBSTR (related_text, 1, 8) AS rel_text, SUBSTR (msgtxt, 1, 60) AS msgtxt
ORDER BY seq;
Some more detailed example can be found here
If autotrace is enabled, any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. To enabled it,
Step 1 : Run plustrce.sql as SYS
Step 2 : Grant PLUSTRACE to user
Step 3 : PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan.
For creation of plan_table ,
The AUTOTRACE options are ON, OFF, ON EXPLAIN, ON STATISTICS and ON TRACEONLY. Following is the summary ,
SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON - The AUTOTRACE report includes the data, the optimizer execution path
and the SQL statement execution statistics.
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows the data and the optimizer execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows the data and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query data.