Earlier this month, Jonathan Lewis revisited an earlier post of his about the problems of automatic SGA management. It was a great read, and one all too near to (and a wound so fresh in) my heart. I've yet to actually find a bug number to confirm the suggestion, other than Note 396940.1 in Metalink. It lists as a possible problem:
High parse ratios
It is important to identify what could produce high parsing calls:
- Use of dynamic plsql - Execution of DDL statements during periods of high workload. Every time a DDL statement is executed, it will cause invalidation of all the statements referencing the object involved. Next time a sql statement referencing the object is executed, it will have to be reparsed and loaded into the shared pool.
Typical operations that cause this situation is the execution of:
- Grant/revoke command - Alter view - Alter package | procedure - Analyze table |index - DBMS_STATS - Truncate table - Alter index - Alter table move
If an ORA-04031 error is associated with high parse ratios, you will also see latch contention for the library cache latch as well as indications of lots of invalidations and reloads in the Library Cache statists in a Statspack or AWR report.
Seems to fit my situation perfectly, although 2:30 AM isn't a high usage period for me, which might suggest a bug after all.