Borndba

In one of our application, we have a daily partition on few critical tables. After data load, we gather statitics. Stats gathering should complete in 2 hrs. 2hrs is pretty high due to memory usage on the server. But that’s different issue which we are working parallely.
We had a issue, where the stats gathering job failed. Stats was scheduled to complete 9am.Since the stats failed BO reports started going slow. Business can’t afford to wait 2hrs for stats gathering.
As a quick fix we took previous day stats and applied to today’s partition.

If subpartition exist and if you are trying to copy partition statistics, we will be getting error message like below,

ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 17130
ORA-06512: at line 2

If subpartition exist, run the stats gathering first and followed by partition copy stats

exec dbms_stats.copy_table_stats(user, 'TAB1', srcpartname=>'P_20100927_S', dstpartname=>'P_20100928_S');

Followed by partition stats, to which you will get the error. Which you can ignore.

exec dbms_stats.copy_table_stats(user, 'TAB1', srcpartname=>'P_20100927', dstpartname=>'P_20100928');
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 17130
ORA-06512: at line 2

Bug#8866627 might fix it has it has fix for many dbms_stats related bugs.

convert this post to pdf.

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.