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
- Log on to the database host as an Oracle user.
- Connect the database to the sysdba user.
% Sqlplus/nolog
Sql> Conn/as sysdba;
Connected.
- Increase the "reserved pool" ratio of the Shared pool .
Sql> alter system set "_shared_pool_reserved_pct" =10 scope=spfile;
- Reduce the minimum request size for "Reserved Pool".
Sql> alter system set "_shared_pool_reserved_min_alloc" =4000 scope=spfile;
- Reduce the number of Shared pool sub-pools to two.
Sql> alter system set "_kghdsidx_count" =2 scope=spfile;
- Close the primary and standby node database separately.
sql> shutdown Immediate
- Start the database again in the primary and standby node.
Sql> Startup
- 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