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

1.1 symptom: the response of database nodes is slow, and some users' 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 The possible cause is that the database user's business is busy and 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 and ORA-04031Error. As the pressure on the Shared Pool increases ORA-00600Error.
1.3 Procedure OracleLog on to the Database Host. To SysdbaThe user connects to the database.

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

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

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

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

Shut down the master-slave node databases respectively.

SQL> shutdown immediate

Restart the database on the master and slave nodes respectively.

SQL> startup

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