12c long hard parse, cursor: pin S wait on X, library cache lock/pin

Recently we have upgraded our database for EBS to 12c version. And have got some problems with new extended statistics mechanism.

I don’t want to describe what has happened in detail, there are a lot of articles in internet which can to explain better than I’m. For example in blog of my colleague Igor: iusoltsev. I want to describe three main problems which we had stumbled upon.

1. New feature of gathering statistics in 12c, now extended statistics collected automatically. It means that the jobs makes DDL on tables and put virtual and invisible columns in there.

alter table "XXYA"."XXPO_PO_ACTION_HISTORY" add (SYS_STSGW8KZODP1ZPPQVO61_Y3Z90 as (sys_op_combined_hash("PO_HEADER_ID", "SEQUENCE_NUM", "DOCUMENT_TYPE")) virtual BY SYSTEM for statistics);

This action has brought problems with our objects which link to this table, they became invalid.

2. Under pressure of new features like feedback statistics, adaptive plans, sql directives. Some of our sql plans has changed and became terrible. We have fixed it through sql_patch, sql_profiles and baselines. Has turned off new optimizer features and had taken plan_hash_values from history.

3. Because of have extremely high COST for plans we’ve got frozen sqls spinning on CPU. Because of have invalid objects we’ve got library locks. Because of we’ve got new optimizer features we’ve got pin S wait on X and long hard parse during reading lob segments for baselines.

4. If your applications use oci8 you need to use special parameter in sqlnet.ora (SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8) otherwise it couldn’t work.

Bug 20465582 – High parse time in 12c for multi-table join SQL with SQL plan directives enabled – superseded (Doc ID 20465582.8)

Are Extended Statistics Collected Automatically on Oracle 12c? (Doc ID 1964223.1)