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

foreword

Small y this name, it is a pen name that the author thinks temporarily, do not have what special meaning actually, use him temporarily to represent us these to dedicate oneself youth for each data center a group of obscure IT person!

What Xiao Y wants to share with you today is the analysis process of a difficult disease. If you have the patience to read this case, there will be more or less some harvest, and there will be no waste of small y's painstaking efforts.

Specifically, it is an analysis process of intermittent partial suspension cases. The report will remind you of common risks and hidden dangers of stable operation of Oracle database.

1 Description of the problem

According to the customer, the application will have intermittent exceptions, including insert single record operation can not be completed for a long time, according to the customer, there may be "deadlock" phenomenon in the database, hope to find the root cause of the problem, put forward a solution to avoid the problem from happening again.

On December 23, 2015, the problem occurred again. The customer contacted Xiao Y again. Xiao Y collected information and diagnosed the fault remotely, and finally located the root cause of the problem.

Environment:

Operating System HPUX IA64 B.11.31

Database ORACLE 10.2.0.5, Single instance

2 Analytical process

>>> 2.1 Abnormal moment database abnormal waiting

You can see:

There are 2 sessions waiting for row locks (no transaction locks, waiting forever) and one session waiting for "undo segment extension."

>>>> 2.2 Combing the relationship between abnormal waits

1) Analyze the blockers waiting for row locks

You can see:

Both sessions of SID 285/290 were blocked by SID=315 sessions, waiting for a row lock for more than 60000 seconds.

2) See what the blocker SID=315 is doing

You can see:

SID=315 session blocked two other sessions, it is also waiting for a resource, waiting for "undo segment extension", has been waiting for 70384 seconds! This wait event has no blockers.

SID=315 SQL statement executed

INSERT INTO TABLE_NAME(COL1,COL2,COL3,COL4,COL5,COL6,COL7)VALUES(:1,:2,:3,:4,:5,:6,:7)

>>> 2.3 What is an "undo segment extension" waiting event

"undo segment extension" is waiting for the rollback segment extension to complete. When adding, deleting and modifying, the database needs a rollback segment to store the pre-mirror image, and when the rollback segment space is insufficient, it needs to be expanded.

Specifically, the undo segment is extended or shrunk by the foreground process by notifying the SMON background process.

Issue the following command to check the use of undo. There is one file 27750M available in the UNDO table, of which 8M is currently active, 1892M has not passed undo retention, and 40M has passed undo retention. No exception was found.

>>>> 2.4 Collecting database hanganalyze and systemstate information

Hanganalyze and systemstate information when issuing the following command exception

SQL> oradebug setmypid

Statement processed.

SQL> oradebug hanganalyze 3

Hang Analysis in /oracle/admin/xxdb/udump/xxdb_ora_14136.trc

SQL>

SQL> oradebug dump systemstate 266

Statement processed.

SQL> oradebug tracefile_name

/oracle/admin/xxdb/udump/xxdb_ora_14136.trc

>>>> 2.5 Problems are automatically resolved after information is collected

After issuing the above command to collect relevant information, check again and find that the database exception waiting has been automatically resolved.

From the principle and experience analysis, this is because oradebug collects systemstate dump, which will call dbx and other OS commands to print the process stack, at this time will wakeup(wake up).

shown below

>>>> 2.6 Get SID: 315 Active session history for sessions

You can see that we are waiting for the undo segment extension.

>>>> 2.7 Analyzing root causes of systemstate dump positioning problems

The session with SID=315 is waiting for "undo segment extension", and the information in the SSD corresponding to PROCESS 19 of this session is as follows:

PROCESS 19:

----------------------------------------

SO: c00000003949b948, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00

(process) Oracle pid=19, calls cur/top: c0000000397209b0/c0000000397209b0, flag: (0) -

int error: 0, call error: 0, sess error: 0, txn error 0

(post info) last post received: 0 0 121

last post received-location: kcbzww

last process to post me: c000000039496148 1 22

last post sent: 0 0 121

last post sent-location: kcbzww

last process posted by me: c000000039496148 1 22

(latch info) wait_event=0 bits=0

Process Group: DEFAULT, pseudo proc: c000000039529928

O/S info: user: oracle, term: UNKNOWN, ospid: 11880

OSD pid info: Unix process pid: 11880, p_w_picpath: oracle@ap-machine-

*** 2015-12-22 10:34:53.431

Short stack dump:

ksdxfstk()+48

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.