«
»

Many of our sessions are waiting on ‘cursor: pin S wait on X’ in one our DB. Connecting to DB via Toad,sql developer is hanging.

Most of the sessions from toad,sql developer is waiting on, as the query all_objects during the startup.

SELECT 'X' FROM SYS.ALL_OBJECTS WHERE    OBJECT_NAME = :1 AND OWNER = :2 AND
OBJECT_TYPE = 'TABLE'

Some kind of contention around all_objects.

To find the blocking session,

SELECT sid,last_call_et,p2raw
,to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX') sid "Blocking Sid"
FROM v$session
WHERE event = 'cursor: pin S wait on X'
order by last_call_et;

Get the record having max last_call_et and take the correspond “Blocking Sid”. Killing the “Blocking Sid” has cleared the DB issue. Most likely this case killing sessions will take time, so kill -9 the os process.

Blocking Sid in our case is waiting on sql_id 1j4husc7c0k5u

To get the query,

SQL> select * from table(dbms_xplan.display_awr('1j4husc7c0k5u'));

SQL_ID 1j4husc7c0k5u
--------------------
select i.obj#, i.rowcnt, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey,i.clufac, i.blevel,
i.analyzetime, i.samplesize, decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),
i.flags, ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist where
i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

Plan hash value: 1414749717

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |       |       |     4 (100)|          |
|   1 |  SORT ORDER BY                |                   |     4 |   400 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |                   |     4 |   400 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS CLUSTER       | IND$              |     4 |   192 |     2   (0)| 00:00:01 |
|   4 |     INDEX UNIQUE SCAN         | I_OBJ#            |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| IND_STATS$        |     1 |    52 |     1   (0)| 00:00:01 |
|   6 |     INDEX UNIQUE SCAN         | I_IND_STATS$_OBJ# |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

May be auto stats gathering happening on that time creating contention in IND$. This behaviour is documented in note#6011045.8

convert this post to pdf.

This entry was posted by Borndba on September 1, 2010 at 4:39 pm under Oracle Performance. Both comments and pings are currently closed.