Perhaps Note:396940.1 is what he means. One section that might seem
to apply is this:
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
- 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.
DBMS_STATS would seem to apply to me, except that 2:30 AM shouldn't be
"a period of high workload."
On 4/11/07, rjamya <rjamya@(protected):
> you got the bug number handy? Since we create indexes with compute clause, I
> just want to be careful. I haven't seen it yet, but a bug# would be great.
> On 4/11/07, John Darrah <darrah.john@(protected):
> > There is a bug in 10.2.0.2 that presents as 4031 errors when rebuilding
> indexes with the compute statistics clause. It causes state objects to be
> created in the shared pool that are never destroyed.