Core Dump on ORA errors ORA-00604 ORA-04031

Frecuently, generating a lot of dumps with this ORA errors and refreshed the shared memory, due to insufficient memory for allocation.

ORA-00604: error occurred at recursive
SQL level 2#

ORA-04031: unable to allocate 4120 bytes of shared memory
("shared pool","select/*+rule*/ bucket_cn...","Typecheck","seg:kggfaAllocSeg")"
[RSQL/FTCH/SNAP_BEG ]

Attempt to increased all SGA memories at the machine's physical limits, and the error occurs again.

Solution:

There is an error cause by ORA-4031.
Please refer to SAP Note #1120481: ORA-4031 - memory allocation errors
for object-level.

Reason and Prerequisites
This is the Oracle bug 6487875/5573238, which is corrected with Oracle
10.2.0.4. This problem may occur on databases that have a large number of objects (for example, a lot of partitions).

Up to Oracle 10.2.0.4, if the above problem occurs, please set the
following init<SID>.ora parameter as a workaround
      _object_statistics=false

Also, set the parameter online, as follows:
   alter system set "_object_statistics"=false scope=spfile;

Then always restart the database to activate the parameter.

Here are some additional information regarding this on SAP OSS Note,
#839187: Oracle 10.2.0: Applying patch set/patches/patch
#23345: Consistency check of ORACLE database