Re: gather_stats_job died due to AUTO SGA error

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
• 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.

DBMS_STATS would seem to apply to me, except that 2:30 AM shouldn't be "a period of high workload."

Don.

On 4/11/07, rjamya <rjamya_at_gmail.com> wrote:
> John,
>
> 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.
>
> rjamya
>
>
> On 4/11/07, John Darrah <darrah.john_at_gmail.com> wrote:
> > 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.
>

-- 
Don Seiler
oracle: http://seilerwerks.blogspot.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l