Oracle 12c execution plan : ST04

[SriKrishna]

Hello,

how to identify inactive steps in an execution plan in ST04 for Oracle 12c.

i am referring to Stefen's blog post.

http://scn.sap.com/community/oracle/blog/2013/09/24/oracle-db-optimizer-part-vii--looking-under-the-...

attached is a screenshot from one of the system, i am working on.

which of the steps are inactive.

[fidel_vales · Accepted solution]

Hi,

if you set the Oracle 12c parameters as SAP recommends then you will not get adaptive plans (not supported)

As such, ST04 is not showing the information you are looking for,

you will have to use DBMS_XPLAN.DISPLAY_CURSOR

also, you can try to use the button that shows the plan in text

[stefan_koehler · Accepted solution]

Hi Fidel,

> if you set the Oracle 12c parameters as SAP recommends then you will not get adaptive plans (not supported)

Really not supported? I mean SAPnote #1888485 includes just recommendations that work out very well for 95 % of the customers, but you are allowed to differ from it. SAP's standard monitoring transactions may not be aware of these features, but if you are using them you usually know what you are doing and how to check

@ Krishna: You can use DBACOCKPIT (SQL Command Editor) to run DBMS_XPLAN.DISPLAY_CURSOR from SAP.

Regards

Stefan

[fidel_vales · Accepted solution]

"not Supported" => we do not provide support for it (not my decission), of course you can use as some customers are still on Oracle 9i (also "not supported").

Transactions are not ready for it and "probably" will not be changed to support it (AFAIK)

As he already is on the "explain" part of ST04, probably the button "EXPLAIN: display as text" is enough

[stefan_koehler · Accepted solution]

Hi Fidel,

> "not Supported" => we do not provide support for it (not my decission), of course you can use as some customers are still on Oracle 9i (also "not supported").

This is exactly what i mean. SAP does not provide support for a lot of Oracle features, but it is allowed to use (e.g. SAPnote #105047) them. Oracle 9i is kind of different as even Oracle is not providing support for it anymore (Extended Support ended 07/2010). The point i was trying to make is that clients can differ from SAP's Oracle parameter recommendations, when needed / wanted.

> As he already is on the "explain" part of ST04, probably the button "EXPLAIN: display as text" is enough

Unfortunately not as it is an additional PL/SQL procedure option.

Regards

Stefan

[SriKrishna · Accepted solution]

Thanks Stefan and Fidel