Plan Not generated in m_sql_plan_cache

[former_member212589]

Hi,

I have created an cv view with data category Cube and when queried with required columns, the view is executing successfully. There query is getting logged in m_expensive statements. However, it is not getting in logged in m_sql_plan_cache.

Could someone tell me the reason behind this and how to get the plan created for the same.

select col1,col2,col3,sum(msr1),sum(msr2) from cv_view.

Plan is not generated.

Thank You,

Raviteja Purama

[lbreddemann]

How do you check the SQL plan view? What's the SQL you use?

[former_member212589]

It is like select * from m_sql_plan_cache where user_name = 'userid' and statement_string like '%string_name%'.

One more thing, I have used the existing CV Views in the new CV View i created.

When i set the execution hint ce2qo_disable_unfolding = 1 in the final cv view, the plan is getting generated else it is not.

I am not able to understand the relationship between this hint and plan getting generated.

Thank You,

Raviteja Purama

[lbreddemann]

The SQL plan generation cannot be avoided, regardless of whether calc views are unfolded or not.

So, I have to assume that the problem is not that the plan isn't generated, but that you don't find it. I recommend checking your query for m_sql_plan_cache.

[lbreddemann]

And of course, there is an exception to this rule, "transient plan unfolding" (SAP note 2441054 - High query compilation times and absence of plan cache entries for queries against calculation views) can lead to the mentioned effect.