Shulou(Shulou.com)06/01 Report--

It has been a year, and it happened in January 15, when a national business system, a real-time number processing system, received a text message alarm and the system was disconnected. There are a large number of enq: SQ-contention, cursor: pin S wait on X and other events in the database, and a large number of ORA-04031 errors appear in the alert log. After the flush share pool, the relevant query operations are carried out.

Around 9: 00, the number of active sessions suddenly climbed, which coincided with the first ORA-04031 error in the alert log on January 31.

TO_CHAR (TRUNC (SA COUNT (*)

--

2015-01-31 08:44 23

2015-01-31 08:45 28

2015-01-31 08:46 25

2015-01-31 08:47 27

2015-01-31 08:48 33

2015-01-31 08:49 37

2015-01-31 08:50 27

2015-01-31 08:51 23

2015-01-31 08:52 38

2015-01-31 08:53 30

2015-01-31 08:54 32

2015-01-31 08:55 35

2015-01-31 08:56 28

2015-01-31 08:57 27

2015-01-31 08:58 27

2015-01-31 08:59 41

2015-01-31 09:00

2015-01-31 09:01 754

2015-01-31 09:02 673

2015-01-31 09:03 45

2015-01-31 09:04 111

2015-01-31 09:05 36

Alert log content:

Sat Jan 31 08:47:36 2015

Thread 1 advanced to log sequence 36910 (LGWR switch)

Current log# 2 seq# 36910 mem# 0: / dev/essdb3vg2/rdb3vg2_1_redo21

Current log# 2 seq# 36910 mem# 1: / dev/essdb3vg3/rdb3vg3_1_redo22

Sat Jan 31 09:00:50 2015

Errors in file / oraclelog/admin/essdb3/bdump/essdb31_j008_15925.trc:

ORA-12012: error on auto execute of job 1

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool", "select sysdate + 1 / (24 * 6...", "sql area", "tmp")

Sat Jan 31 09:00:51 2015

Errors in file / oraclelog/admin/essdb3/bdump/essdb31_j003_24229.trc:

ORA-12012: error in automatic execution of job 1644

ORA-04031: cannot allocate 32 bytes of shared memory ("shared pool", "update seq$ set increment$=:...", "sql area", "tmp")

Sat Jan 31 09:01:31 2015

Errors in file / oraclelog/admin/essdb3/bdump/essdb31_j005_24233.trc:

ORA-12012: error in automatic execution of job 1624

ORA-04031: cannot allocate 32 bytes of shared memory ("shared pool", "update seq$ set increment$=:...", "sql area", "tmp")

Sat Jan 31 09:01:31 2015

Errors in file / oraclelog/admin/essdb3/bdump/essdb31_j008_15925.trc:

ORA-12012: error in automatic execution of job 927

ORA-04031: cannot allocate 32 bytes of shared memory ("shared pool", "update seq$ set increment$=:...", "sql area", "tmp")

Sat Jan 31 09:01:43 2015

Errors in file / oraclelog/admin/essdb3/bdump/essdb31_smon_6599.trc:

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool", "insert into sys.col_usage$ (...", "sql area", "tmp")

Analysis:

1. Because share pool cannot update the dictionary table SEQ$, it is inevitable that a large number of sequence class wait events occur, which explains why the enq: SQ-contention wait time is longer in top5.

2. By querying V$DBA_HIST_SQLTEXT, we can find a total of 592 sql texts that begin with "SELECT ROW_.*", and each SQL_ID corresponds to a large number of lines of sql text, and all SQL texts correspond to different SQL_ID, indicating that there is a problem with the binding variables of sql. Hard parsing is required for each execution of SELECT ROW_.*, which will take up a lot of share pool.

3. A large number of alert alarms can be found in the backend ora-4031 logs, and most of them are the same statement. The output of this log occurs during the process of applying for free chunk. When the same statement traverses the free list, an error will be reported every time a subpool is scanned and space is not applied for.

4 the waiting event is mainly caused by high hard resolution and high version count. Both high hard resolution and high version count will consume a lot of shared_pool space, which is the direct result of insufficient shared_pool space.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.