Oracle Database case study-Oracle system running failure-slow database response due to insufficient Shared Pool memory

1.1 symptom description

The response of database nodes is slow, and some user services are affected.

View database alarm logs and start to displayORA-07445Errors, followed by a large numberORA-04031Errors andORA-00600Error.

Check the database logs. The database is still active as follows:

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.2 possible causes

Due to the busy business of database users, there are many active sessions, occupying a large amount of Shared Pool memory. At the same time, the internal parameter configuration of the Shared Pool is unreasonable, resulting in a large amount of memory fragments. Therefore, some processes cannot apply for sufficient Shared Pool memory.ORA-07445Errors andORA-04031Error. As the pressure on the Shared Pool increasesORA-00600Error.

1.3 Procedure

1. ToOracleLog on to the Database Host.
2. ToSysdbaThe user connects to the database.

% 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 of the "reserved pool.

SQL> alter system set "_ shared_pool_reserved_min_alloc" = 4000 scope = spfile;

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

SQL> alter system set "_ kghdsidx_count" = 2 scope = spfile;

6. Shut down the master-slave node databases respectively.

SQL> shutdown immediate

7. Restart the database on the master and slave nodes respectively.

SQL> startup

8. Query the set parameters.

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 ';