Shulou(Shulou.com)05/31 Report--

This article is about how to use hanganalyze in oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

There are two ways to use hanganalyze:

A.alter session set events' immediate trace name hanganalyze level'

Session level, both single instance and rac can be used.

B.--for single instance

Oradebug hanganalyze

-- for RAC

Oradebug setmypid-tracks the current session

Oradebug setinst all-rac environment

Oradebug-g def hanganalyze

Oradebug unlimit-remove the trace file size limit

Oradebug tracefile_name-View the trace file name and location

It is recommended that the command oradebug hanganalyze be executed a few more times, so that you can compare the changes in the process and see if it is really hang or very slow.

-

In addition, the common uses of oredebug tools are as follows:

Oradebug setospid-tracking system progress

Oradebug setorapid-tracking the ORACLE process

Level levels and concepts:

1-2: only hanganalyze output, not dump any process

3:level2+dump processes that are out of the in_hang state

4:level3+dump the blockers in the waiting chain (status is leaf/leaf_nw/ign_dmp)

5:level4+dump lists all processes in the waiting chain (status is nleaf)

Oracle officially advises not to exceed level 3. Level 3 is sufficient for analyzing problems. Exceeding level 3 places an additional burden on the system.

After knowing the usage, I use library cache lock,library cache pin and TX locks to get a trace file through the above method, analyze the problem through the trace file, and explain by the way how to read the trace file.

First generate a TX lock:

First session:

02:05:40 SQL > select sid,serial# from v$session where sid= (select userenv ('SID') from dual)

SID SERIAL#

--

32 45444

1 row selected.

02:05:55 SQL > create table T1 (id int)

Table created.

02:07:20 SQL > insert into T1 values (1)

1 row created.

02:07:36 SQL > commit

Commit complete.

02:07:39 SQL > update T1 set id=2 where id=1

1 row updated.

The second session to update the same row, hang live:

02:05:51 SQL > select sid,serial# from v$session where sid= (select userenv ('SID') from dual)

SID SERIAL#

--

40 17698

1 row selected.

Elapsed: 00:00:00.00

02:06:07 SQL > update T1 set id=3 where id=1

The wait event generated at this time:

INST_ID EVENT# EVENT COUNT (*)

1 341 SQL*Net message to client 1

1 237 enq: TX-row lock contention 1

The following simulates the generation of library cache pin:

Create two stored procedures:

Create or replace procedure aproc

Is

Begin

Null

End

/

Create or replace procedure bproc

Is

Begin

Aproc

Dbms_lock.sleep (3000)

End

/

The third session:

02:06:13 SQL > select sid,serial# from v$session where sid= (select userenv ('SID') from dual)

SID SERIAL#

--

1 18

1 row selected.

Execute the stored procedure:

Exec bproc

The fourth session, compiled aproc,hang resides:

02:06:24 SQL > select sid,serial# from v$session where sid= (select userenv ('SID') from dual)

SID SERIAL#

--

17 24

1 row selected.

Elapsed: 00:00:00.00

02:06:26 SQL > alter procedure aproc compile

A wait event occurs:

INST_ID EVENT# EVENT COUNT (*)

1 237 enq: TX-row lock contention 1

1 341 SQL*Net message to client 1

1 280 library cache pin 1

The fifth session executes aproc:

02:06:51 SQL > select sid,serial# from v$session where sid= (select userenv ('SID') from dual)

SID SERIAL#

--

44 48

1 row selected.

02:39:45 SQL > alter procedure aproc compile

At this time, library cache lock came into being.

INST_ID EVENT# EVENT COUNT (*)

1 237 enq: TX-row lock contention 1

1 341 SQL*Net message to client 1

1 281 library cache lock 1

1 280 library cache pin 1

Here is an overview of the process within the oracle of pin and lock.

For example, session 1 executes bproc and session 2 executes compiled aproc. At this point, session 1 holds the share pin,aproc of bproc and aproc and the lock of bproc is not held; when session 2 compiles, you need to obtain the exclusive pin of aproc, because lock has a higher priority than pin, so you should first obtain the exclusive lock of aproc's handle before obtaining pin. This process is no problem, but when you get aproc exclusive pin, it will be blocked by session 1's shared pin. At this time, the exclusive lock of aproc is held by session 2, the shared pin is held by session 1, the shared pin of bproc is held by session 1, and the exclusive lock of bproc is held by session 2.

Conversational 1:aproc shared pin,bproc shared pin

Session 2:aproc exclusive lock, waiting for aproc exclusive pin (blocked by session 1 aproc shared pin)

At this time, if session3 compiles aproc again, session 3 first needs to obtain the exclusive lock of aproc, and then obtains the exclusive pin. When obtaining lock, it will be blocked by the aproc exclusive lock held by session 2, resulting in library cache lock waiting.

If session 3 compiles the bproc, session 3 needs to first obtain the exclusive lock of the bproc, and then get the exclusive pin,lock. However, when obtaining the exclusive pin, it will be blocked by the shared pin of session 1 holding the bproc, and the poor student library cache pin will wait.

If session 3 runs aproc, you need to get an aproc shared lock, but it will be blocked by the aproc exclusive lock held by session 2, resulting in a library cache lock wait.

If session 3 runs bproc, the acquisition of bproc and aproc shared pin,aproc shared pin needs to be queued, and after session 2, bproc shared pin will be blocked by bproc exclusive pin held by session 1, resulting in library cache pin waiting. At this point, if session 1 is killed, the exclusive pin of session 1 will be acquired by session 2, and session 3 will still have to wait for session 2.

The experimental results show that it is completely correct!

Of course, the above is my own understanding of pin and lock, if you have any objection, please leave a message.

The role of library cache lock and library cache pin:

Oracle uses Library cache lock and Library cache pin to achieve concurrency control, Library cache lock is obtained on handle, and Library cache pin is obtained on data heap. When accessing an object, you must first get the lock on the handle, and then pin the accessed data in memory. The function of lock is to control concurrent access between processes, while the function of pin is to ensure data consistency and prevent data from being exchanged during access.

Reference: http://blog.csdn.net/tianlesoftware/article/details/6641440

Now let's summarize the congestion:

(32.45444) A TX lock is generated on the block (40.17698) and (40.17698).

(1.18) blocking (17.24) blocking (44.48), the first blocking generates pin and the second blocking generates lock.

Let's collect the trace file of system hanganalyze

By the way, collect system statedump.

System hanganalyze:

SQL > oradebug hanganalyze 3

Hang Analysis in / tpsys/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_22133.trc

System statedump:

SQL > oradebug setmypid

Statement processed.

SQL > oradebug unlimit

Statement processed.

SQL > oradebug dump systemstate 266

Statement processed.

SQL > oradebug dump systemstate 266

Statement processed.

SQL > oradebug tracefile_name

/ tpsys/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_22313.trc

The trace file generated by hanganalyze is relatively easy to read. There are some host and library information in front of it, which I have ignored here.

This section lists the chains that is most likely to cause system hang, and finds that there are two; when there are many trace files, you can search for the keyword Signature:

More ogg1_ora_22133.trc | grep Signature

Chains most likely to have caused the hang:

[a] Chain 1 Signature: 'SQL*Net message from client'

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.