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

The application response says that the system is slow, the time is not fixed, the phenomenon is not known, it is slow. I have no choice but to log in to the system to check.

View existing snapshot information on the system

SQL > col mintime for A30

SQL > col maxtime for A30

SQL >

SQL > select min (snap_id) minid, max (snap_id) maxid

2 to_char (min (begin_interval_time), 'yyyy-mm-dd hh34:mi:ss') mintime

3 to_char (max (end_interval_time), 'yyyy-mm-dd hh34:mi:ss') maxtime

4 from dba_hist_snapshot

Based on the snapshot information, let's take a look at the corresponding classification of waiting events.

SQL > 1 select wait_class_id,wait_class, count (*) cnt

2 from dba_hist_active_sess_history

3 where snap_id between 78303 and 78472

4 group by wait_class_id, wait_class

5 * order by 3

WAIT_CLASS_ID WAIT_CLASS CNT

2723168908 Idle 2

4166625743 Administrative 6

2000153315 Network 538

4217450380 Application 829

3290255840 Configuration 4128

4108307767 System I/O 9234

1893977003 Other 11043

3386400367 Commit 26802

1740759767 User I/O 28076

375421

3875070507 Concurrency 888984

11 rows selected.

Check the specific waiting events.

SQL > select event_id, event, count (*) cnt

2 from dba_hist_active_sess_history

3 where snap_id between 78303 and 78472

4 and wait_class_id=3875070507

5 group by event_id, event

6 order by 3

EVENT_ID EVENT CNT

-

877525844 cursor: mutex X 1

86156091 os thread startup 6

1242501677 latch: library cache pin 7

1714089451 row cache lock 7

2952162927 library cache load lock 10

2802704141 library cache pin 22

2032051689 latch: library cache lock 45

1117386924 latch: row cache objects 60

1394127552 latch: In memory undo latch 68

2779959231 latch: cache buffers chains 873

2161531084 buffer busy waits 4286

916468430 library cache lock 4549

2696347763 latch: shared pool 12360

589947255 latch: library cache 12718

1729366244 cursor: pin S wait on X 853972

Find out the SQL corresponding to pin S wait on X

SQL > select sql_id, count (*) cnt

From dba_hist_active_sess_histo 2 ry

Where snap_id between 78303 and 3 78472

4 and event_id in (1729366244)

5 group by sql_id

Having count (*) > 6 100

Order by 7 2 desc

SQL_ID CNT

--

0nuvj12m3ryvy 853880

Then in the above query, we can find which objects these sql statements are waiting for from the awr history information:

SQL > select owner,current_obj#,object_name,object_type, count (*) cnt

2 from dba_hist_active_sess_history a, dba_objects b

3 where snap_id between 78303 and 78472

4 and event_id in (1729366244)

5 and sql_id in ('0nuvj12m3ryvy')

6 and a.current_obj#=b.object_id

7 group by owner,current_obj#,object_name,object_type

8 having count (*) > 10

9 order by 5 desc

OWNER CURRENT_OBJ# OBJECT_NAME OBJECT_TYPE CNT

-

SETTLE 49326 T_OPERATE_LOG TABLE 654899

SYS 73541 LOG$INFORMATION TABLE 16337

SETTLE 48117 G_MENU_RIGHT TABLE 9684

SETTLE 141993 CONFIG_UNIX INDEX 9567

SETTLE 136520 T_MANAGE_WARN_CONFIG TABLE 9565

SETTLE 51955 T_BILL_LOG TABLE 9520

SETTLE 48128 G_ROLE TABLE 9458

The following is to confirm whether the waiting database is too centralized, that is, whether there is a hot block problem:

SQL > select current_file#,current_block#, count (*) cnt

2 from dba_hist_active_sess_history

3 where snap_id between 78303 and 78472

4 and event_id in (1729366244)

And sql_id in ('0nuvj12m3ryv 5y')

6 and current_obj# in (49326, 48117, 141993, 136520, 51955, 48128)

7 group by current_file#, current_block#

8 having count (*) > 50

9 order by 3

CURRENT_FILE# CURRENT_BLOCK# CNT

9 4436 9458

276 839623 9500

246 857417 9520

276 839495 9521

2 532140 9565

55 1153960 9567

276 840134 9648

25 739537 9684

275 906620 9687

276 838125 15128

276 843388 15131

275 906533 15138

275 904906 15180

275 904851 15186

275 902677 15210

276 845366 15210

275 909383 15216

275 902396 15220

275 905990 15333

275 909920 15422

276 840809 15427

276 845296 15451

275 906837 15454

276 843996 15777

276 837403 15778

275 908047 15784

275 906933 15813

275 909489 15813

275 903374 15814

276 844903 15886

276 841993 15925

275 907463 15942

276 839733 15944

275 905797 15944

275 908458 15944

276 838802 15948

276 843290 15950

275 905767 16209

275 909728 16213

275 904723 16262

275 908888 16263

276 844986 16275

276 844862 16347

275 906325 16394

275 904842 16403

275 908197 24737

276 841357 25472

47 rows selected.

There is also another way of thinking on the Internet, according to the above MOS.

How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (Doc ID 786507.1)

Cursor: pin S wait on X

Cursor: pin S wait on X

When a session requests a mutex for a pin-related shared operation (such as executing a cursor), the session has a Cursor: pin S wait on X wait event.

But the mutex cannot be authorized because the mutex is being held by other session in exclusive mode (such as parsing the cursor)

The p2raw column in v$session or v$session_wait shows the blocker session (holder session) of the cursor: pin S wait on X wait event.

According to the MOS documentation method, let's take a look at

SQL > select p2raw from v$session where event = 'cursor: pin S wait on X'

P2RAW

-

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

000001B200000000

-- Parameter description P1 Hash value of cursor P2 Mutex value 64 bit platforms8 bytes are used.Top 4 bytes hold the session id (if the mutex is held X) Bottom 4 bytes hold the ref count (if the mutex is held S). 32 bit platforms 4 bytes are used.Top 2 bytes hold the session id (if the mutex is held X) Bottom 2 bytes hold the ref count (if the mutex is held S). P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps

Our operating system is 64 bit. So we can convert the first eight bits of 000001B200000000 000001B2 to decimal to get the result 434.

Of course, you can also use commands to convert them.

SQL > select p2raw sid (substr (to_char (rawtohex (p2raw)), 1mai 8), 'XXXXXXXX') number

2 from v$session

3 where event = 'cursor: pin S wait on X'

P2RAW SID

--

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

000001B200000000 434

Let's try to see if we can find the block session conversation relationship.

SQL > select p1, p2raw, count (*) from v$session

Where event = 'cursor: pin S 2 wait on X'

And wait_time = 0

3 4 group by p1, p2raw

P1 P2RAW COUNT (*)

2788948862 000001B200000000 59

-- Parameter description

P1 = the mutex Id

This has the same definition as v$mutex_sleep_history.mutex_identifier

P2raw = holding Session Id | Ref Count

The most significant bytes always store the Holding Session Id (Holding SId).

The least significant bytes always store the Ref Count.

SQL > select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT

2 from v$session where SID=434

SID SERIAL# SQL_ID BLOCKING_SESSION BLOCKING_SE EVENT

-

434 34745 0nuvj12m3ryvy UNKNOWN single-task message

SQL > select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT

2 from v$session where event = 'cursor: pin S wait on X'

3

SID SERIAL# SQL_ID BLOCKING_SESSION BLOCKING_SE EVENT

-

332 59875 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

333 27868 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

350 54031 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

365 5053 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

383 61654 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

392 13286 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

415 10261 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

442 8546 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

444 20213 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

452 18561 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

480 14834 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

484 12814 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

497 27271 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

519 18389 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

521 10435 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

533 36612 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

561 37558 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

579 24259 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

588 30464 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

589 12607 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

594 43683 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

606 720 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

612 1150 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

628 42806 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

635 13159 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

637 47496 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

655 48974 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

660 5891 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

682 6519 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

711 46117 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

716 14265 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

720 4766 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

723 61645 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

724 17910 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

729 9951 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

752 28924 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

753 12049 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

761 64354 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

839 33810 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

843 6215 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

867 7396 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

871 58051 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

880 17967 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

884 22198 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

902 65183 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

907 65065 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

914 35470 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

928 63975 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

949 42782 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

950 4799 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

951 17067 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

952 36283 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

954 17638 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

992 8218 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

999 63310 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

1006 48986 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

1028 45586 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

1043 53471 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

1082 13982 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X

59 rows selected.

I met bug. Because the current system version count is not high.

SQL > select sql_id,version_count from v$sqlarea where version_count > 100 order by 2 desc

No rows selected

Cursor: reason for pin S wait on X occurrence

Frequent Hard Parses

If the frequency of Hard Parsing is extremely high, then contention can occur on this pin.

High Version Counts

When Version counts become excessive, a long chain of versions needs to

Be examined and this can lead to contention on this event

Known bugs

Bug 5907779-Self deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS) [ID 5907779.8]

Bug 7568642: BLOCKING_SESSION EMPTY FOR "CURSOR: PIN S WAIT ON X"

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.