OPTIMIZER_FEATURES_ENABLE change caused massive hard parse and slowness

When you change your optimizer environment, you must parse a child cursor that belongs to that optimizer environment.

When you changed your optimizer environment back, your sessions were able to just re-use the child cursors that were already parsed under that optimizer environment.

If you want to know why Oracle is doing a hard parse for a query that already has a parsed cursor, check v$sql_shared_cursor for the reason.

AWR reports that include both slow and fast periods are not that useful, you want to be able to compare the two different periods not average them together.

-Edit

I opened up the attachments. What makes you think parsing was a problem?

At first sight it looks as if you've simply overloaded the machines CPU (don't be misled by the apparent 50% utilisation - you're machine is behaving like 40 CPUs, not the 80 CPUs it's pretending to be).

If you check the total time spent, a large fraction of it is in latch sleeps on the cache buffers chains, and another large fraction in global cache consistency. You have a high rate of single block reads, and a huge rate of logical I/Os, so it's likely that the rate of work (particularly the buffer activity) is resulting in CPU loading in a few hot spots in the data sets which result in lots of latch spins (burning more CPU) and long sleeps, which exacerbate any problems of buffer busy waits and global cache coherence.

You'll note, also, in the SQL order by Cluster Wait Time that the total time of the top few statement is roughly: 25% cluster time, 8% CPU time, 10% I/O time - which means 57% LOST time - which is your sessions sitting in the run queue with Oracle thinking they're running (as opposed to waiting in an instrumented wait) when they're not.

There may be something more subtle going on - for example you have a very large number of "gc buffer busy acquire" but a very small number of "gc buffer busy release" that between them don't look possible; and there's also the oddity that the O/S interconnect traffic is zero - which suggests that either you're not using the private interconnect, or there's an instrumentation error in Oracle. However, before getting too far into rocket science I would at this point check a couple of older AWR reports for about the same time period to see if "workload per execution" for some of the expensive statement has changed - drill in to a couple of the big SQL_IDs to check whether they've had multiple execution plans that vary from the 11.2.0.3 interval to the 11.2.0.4 interval - the root problem may simply be that the change in version produced a couple of very expensive changes in plan.

Regards

Jonathan Lewis

P.S  I wouldn't have made the change while the system was live - I would have tried to work out a plan to stop and restart (perhaps one instance at a time), or flush shared_pool at a quiet moment for each instance in turn to make the change.  It's possible that the excessive workload was induced by a side effect of a dynamic change when you already had a relatively high version count on some  cursors in memory.

P.P.S You'll note that your connection count jumps significantly from the start to the end of the period. It's possible that a small stutter in the database response time around the time of the change was sufficient to panic the front end into starting a lot more sessions from its connection pool which, in turn, pushed the system into a catastrophe of continuous contention.  (In other words, you just got unlucky.)

Anytime you've got all code going to any instance for any workload/sql/object, you're asking for trouble if everything isn't already super efficient... RAC exaggerates any inefficiencies. And any time you do an upgrade, you're quite likely to have one or two statements turn nastily inefficient.

Most RAC systems I've worked on, where the application code combined with data model has been inherently inefficient (so that'll be all of them), we've ended up going for node affinity even if it means that you end up with other instances doing very little ... which can be politically awkward.