Borndba

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.

Refer to below link for manually creating and evolving the baseline plan.

http://www.oracle-base.com/articles/11g/SqlPlanManagement_11gR1.php

In the example given in the url above, after adding index we need to manually evolve. This helps in accepting the right plan.

But what happens if i drop the index.

SQL> drop index spm_test_tab_idx;

Index dropped.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACE
SQL> SELECT description
2  FROM   spm_test_tab
3  WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462

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

--

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
|

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

--

|   0 | SELECT STATEMENT  |              |     1 |    25 |    13   (0)| 00:00:01

|

|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    13   (0)| 00:00:01

|

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

--

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=99)

Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement ==> Old base line is taken automatically

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
47  consistent gets
0  physical reads
0  redo size
441  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Let me try to create that index back and see what happens,

SQL> CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT description
2  FROM   spm_test_tab
3  WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3121206333

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

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

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%

CPU)| Time     |

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

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

|   0 | SELECT STATEMENT            |                  |     1 |    25 |     2
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    25 |     2
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1
(0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=99)

Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5ted3324c0" used for this statement  ==> Goes for the best plan

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
448  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Note: I have not evolved 2nd time after putting back the index.

Need to check how the auto Evolving of baseline works.

convert this post to pdf.