Oracle Database case Consolidation-oracle system execution failure-shared pool memory causes slow database response

1.1Descriptive narrative of phenomena

The database node responds slowly and some of the user's business is affected.

Check the database alarm log to start displaying ORA-07445 errors, followed by a large number of ORA-04031 errors and ORA-00600 errors.

To check the database log, the database is still active, such as the following:

Sat Jul 17 07:51:52 2010

Thread 1 advanced to log sequence 266272

Current log# 2 seq# 266272 mem# 0:/dev/rlv_redo121

Current log# 2 seq# 266272 mem# 1:/dev/rlv_redo122

1.2Possible causes

Because the database user business is busy, more active sessions, occupy a large amount of shared pool memory. At the same time, the internal parameters of the shared pool are not properly configured resulting in a large amount of memory fragmentation. As a result, some processes are unable to request sufficient shared pool memory, resulting in ORA-07445 errors and ORA-04031 errors. Because the shared pool is under increasing pressure, ORA-00600 errors are eventually raised.

1.3Process steps

1. Log on to the database host as an Oracle user.
2. Connect the database to the sysdba user.

% Sqlplus/nolog

Sql> Conn/as sysdba;

Connected.

3. Increase the "reserved pool" ratio of the Shared pool .

Sql> alter system set "_shared_pool_reserved_pct" =10 scope=spfile;

4. Reduce the minimum request size for "Reserved Pool".

Sql> alter system set "_shared_pool_reserved_min_alloc" =4000 scope=spfile;

5. Reduce the number of Shared pool sub-pools to two.

Sql> alter system set "_kghdsidx_count" =2 scope=spfile;

6. Close the primary and standby node database separately.

sql> shutdown Immediate

7. Start the database again in the primary and standby node.

Sql> Startup

8. The number of parameters for the query setting.

Sql> Select A.ksppinm "Parameter", B.KSPPSTVL "Session Value", C.KSPPSTVL "Instance Value"

From X$ksppi A, X$KSPPCV B, X$KSPPSV c

where a.indx = b.indx and a.indx = c.indx and a.ksppinm = ' _shared_pool_reserved_pct ';  

Oracle Database case Consolidation-oracle system execution failure-shared pool memory causes slow database response