EXPLAIN PLAN

We were using state_tolerated for materialized views in OBIEE. To troubleshoot, when we ran explain plan in oracle, turns out, it does not show query was hitting materialized views.

Reason was, explain plan uses a new session, and did not take state_tolerated value for that session. So to see which MV the query was hitting, we had to get explain plan using following query syntax instead of explain icon (ambulance in toad):

EXPLAIN PLAN For

***Sql query***

Then execute following query to view the explain out put:

Select * from plan_table

In the object_name column, you should be able to view the materialized view name