25 April 2007

ASMM Pitfalls

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.

No comments:

Post a Comment