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
@$ORACLE_HOME/rdbms/admin/utlxmv.sql

-- 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
FROM mv_capabilities_table
ORDER BY seq;

Some more detailed example can be found here

Autotrace :

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

@$ORACLE_HOME/sqlplus/admin/plustrce.sql

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 ,

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

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.