CURSOR_SHARING effect on database performance, latch: shared pool

I have upgraded our database from 11g to 12c and after that query performance degraded significantly.

I have generated ADDM report during the problematic period and found that the main problem was hard parses :

1  Hard Parse Due to Literal Usage           10.38 | 43.55%        1

Finding 1: Hard Parse Due to Literal Usage
Impact is 10.38 active sessions, 43.55% of total activity.
———————————————————-
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.

For us it is not new that our developers are not using bind variables so shared pool was/is growing and growing to retain all of the parsed SQLs and their execution plans.

But the same codes were running on 11g and were working fine. Just after upgrade database started to feel that bad 🙂

The reason is that 12c has major change in optimize behavior. So if bind variables are not used in existing application then you need to use CURSOR_SHARING=FORCE option, old value of this parameter was CURSOR_SHARING=EXACT.

alter system set cursor_sharing=FORCE;

After that database started to feel better but in any case I cleared shared pool(for clearing old,not necessary parses):

alter system flush shared_pool;

Database started to feel better!

Good Luck!