This discussion is archived
5 Replies Latest reply: Jan 17, 2013 7:55 AM by 940856 RSS

GATHER_STALE_STATS_JOB issue.

940856 Newbie
Currently Being Moderated
Hello All,

We are running 11.1.0.7.0 hosting on HP UX box. From last 2 days, we were made aware of the database slowness. Once, we tried to analyze, the GATHER_STALE_STATS_JOB was overrunning. earlier , it used to run in no time. we also noticed the session running the job experienced "library cache lock" wait events. we then stopped the job and database performance was back to normal. we also extracted the sql_id from AWR for the most time spent. Also, from the AWR , the wait , which spent most of the time waiting was "library cache lock". Just wondering , what caused this job to run crawl here. I also extracted the plan of the sql , which waits most of the time for "library cache lock" and it turn out pretty gigantic. will also paste that on board.

I will really appreciate your valuable inputs.

Note :- Had to omit the predicate part to the server the limit of maximum charecters.
Select X.sku_id,X.prod_group
,X.brand_type
from
    (select distinct iwm.sku_id, im.prod_group
    , sc2.code_desc as brand_type
    from item_whse_master iwm, sku_invn si, item_master im
    , sys_code sc2
    where iwm.sku_id = si.sku_id
      and iwm.whse = 'NDC'
      and (si.qty_on_hand - si.qty_not_alloc) > 0
      and iwm.sku_id = im.sku_id
      and not exists (select 1 from pick_locn_dtl pld where pld.sku_id = iwm.sku_id and pld.locn_id
      in (select locn_id from locn_hdr where work_grp = 'BDC'))
   --   and nvl(upper (trim(iwm.misc_alpha_1) ) ,'-') not in ('NO ACT','NO ACT LOC','NOT ACT')
    and not exists (select 1 from case_dtl cd, case_lock cl where cd.case_nbr =cl.case_nbr and cd.sku_id = iwm.sku_id)
    AND   SC2.CODE_ID(+) = IM.PROD_GROUP     AND SC2.REC_TYPE = 'B'  AND SC2.CODE_TYPE = '664')X,
    (
    select distinct PD.SKU_ID from
    PKT_HDR_INTRNL PH inner join PKT_DTL PD on PH.PKT_CTRL_NBR = PD.PKT_CTRL_NBR
    where PH.STAT_CODE = 10
    )Y
    where x.sku_id = y.sku_ID

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                                |       |       |  3366 (100)|          |
|   1 |  NESTED LOOPS SEMI                        |                                |    16 |  3632 |  3366   (2)| 00:00:41 |
|   2 |   VIEW                                    |                                |    16 |  3568 |  3225   (2)| 00:00:39 |
|   3 |    HASH UNIQUE                            |                                |    16 |  5216 |  3225   (2)| 00:00:39 |
|*  4 |     FILTER                                |                                |       |       |            |          |
|   5 |      HASH GROUP BY                        |                                |    16 |  5216 |  3225   (2)| 00:00:39 |
|   6 |       NESTED LOOPS                        |                                |     1 |   326 |  3220   (2)| 00:00:39 |
|*  7 |        HASH JOIN                          |                                |    10 |  1410 |  2364   (1)| 00:00:29 |
|   8 |         TABLE ACCESS BY INDEX ROWID       | SYS_CODE                       |     1 |    63 |     1   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN                 | PK_SYS_CODE                    |     1 |       |     1   (0)| 00:00:01 |
|  10 |         NESTED LOOPS                      |                                |       |       |            |          |
|  11 |          NESTED LOOPS                     |                                |  3045 |   231K|  2363   (1)| 00:00:29 |
|* 12 |           INDEX FULL SCAN                 | PK_ITEM_WHSE_MASTER            |  3045 |   145K|  1601   (1)| 00:00:20 |
|  13 |            NESTED LOOPS                   |                                |       |       |            |          |
|  14 |             NESTED LOOPS                  |                                |     1 |    35 |     2   (0)| 00:00:01 |
|  15 |              TABLE ACCESS BY INDEX ROWID  | PICK_LOCN_DTL                  |     1 |    21 |     1   (0)| 00:00:01 |
|* 16 |               INDEX RANGE SCAN            | PICK_LOCN_DTL_IND_1            |     1 |       |     1   (0)| 00:00:01 |
|* 17 |              INDEX UNIQUE SCAN            | PK_LOCN_HDR                    |     1 |       |     1   (0)| 00:00:01 |
|* 18 |             TABLE ACCESS BY INDEX ROWID   | LOCN_HDR                       |     1 |    14 |     1   (0)| 00:00:01 |
|  19 |            NESTED LOOPS                   |                                |     4 |   140 |     2   (0)| 00:00:01 |
|* 20 |             INDEX RANGE SCAN              | CD_WAVE_IND1                   |     3 |    69 |     1   (0)| 00:00:01 |
|* 21 |             INDEX RANGE SCAN              | PK_CASE_LOCK                   |     1 |    12 |     1   (0)| 00:00:01 |
|* 22 |           INDEX UNIQUE SCAN               | PK_ITEM_MASTER                 |     1 |       |     1   (0)| 00:00:01 |
|  23 |          TABLE ACCESS BY INDEX ROWID      | ITEM_MASTER                    |     1 |    29 |     1   (0)| 00:00:01 |
|  24 |        VIEW                               |                                |     1 |   185 |    86   (4)| 00:00:02 |
|  25 |         UNION ALL PUSHED PREDICATE        |                                |       |       |            |          |
|  26 |          NESTED LOOPS                     |                                |     3 |   285 |     3   (0)| 00:00:01 |
|  27 |           TABLE ACCESS BY INDEX ROWID     | CASE_DTL                       |     3 |   228 |     2   (0)| 00:00:01 |
|* 28 |            INDEX RANGE SCAN               | CD_WAVE_IND1                   |     3 |       |     1   (0)| 00:00:01 |
|* 29 |           INDEX RANGE SCAN                | CH_WAVE_IND1                   |     1 |    19 |     1   (0)| 00:00:01 |
|  30 |          NESTED LOOPS                     |                                |       |       |            |          |
|  31 |           NESTED LOOPS                    |                                |     1 |   137 |     6  (17)| 00:00:01 |
|  32 |            NESTED LOOPS                   |                                |     1 |    61 |     5  (20)| 00:00:01 |
|  33 |             VIEW                          | VW_SQ_1                        |     1 |    42 |     3   (0)| 00:00:01 |
|  34 |              SORT UNIQUE                  |                                |     1 |    32 |            |          |
|  35 |               NESTED LOOPS                |                                |     1 |    32 |     3   (0)| 00:00:01 |
|* 36 |                TABLE ACCESS BY INDEX ROWID| SYS_CODE                       |     1 |    17 |     1   (0)| 00:00:01 |
|* 37 |                 INDEX RANGE SCAN          | PK_SYS_CODE                    |     1 |       |     1   (0)| 00:00:01 |
|* 38 |                INDEX FULL SCAN            | PK_CASE_LOCK                   |     1 |    15 |     2   (0)| 00:00:01 |
|* 39 |             INDEX RANGE SCAN              | CH_WAVE_IND1                   |     1 |    19 |     1   (0)| 00:00:01 |
|* 40 |            INDEX RANGE SCAN               | CASE_DTL_IND_2                 |     1 |       |     1   (0)| 00:00:01 |
|* 41 |           TABLE ACCESS BY INDEX ROWID     | CASE_DTL                       |     1 |    76 |     1   (0)| 00:00:01 |
|  42 |          NESTED LOOPS                     |                                |     1 |    73 |     2   (0)| 00:00:01 |
|* 43 |           TABLE ACCESS BY INDEX ROWID     | ALLOC_INVN_DTL                 |     1 |    54 |     1   (0)| 00:00:01 |
|* 44 |            INDEX RANGE SCAN               | FK_ALLOC_INVN_DTL_TO_ITEM_MAST |     2 |       |     1   (0)| 00:00:01 |
|* 45 |           INDEX RANGE SCAN                | CH_WAVE_IND1                   |     1 |    19 |     1   (0)| 00:00:01 |
|  46 |          NESTED LOOPS SEMI                |                                |     1 |    88 |     3   (0)| 00:00:01 |
|  47 |           NESTED LOOPS                    |                                |     1 |    77 |     2   (0)| 00:00:01 |
|* 48 |            TABLE ACCESS BY INDEX ROWID    | ALLOC_INVN_DTL                 |     1 |    58 |     1   (0)| 00:00:01 |
|* 49 |             INDEX RANGE SCAN              | FK_ALLOC_INVN_DTL_TO_ITEM_MAST |     2 |       |     1   (0)| 00:00:01 |
|* 50 |            INDEX RANGE SCAN               | CH_WAVE_IND1                   |     1 |    19 |     1   (0)| 00:00:01 |
|* 51 |           TABLE ACCESS BY INDEX ROWID     | INVN_NEED_TYPE                 |    14 |   154 |     1   (0)| 00:00:01 |
|* 52 |            INDEX RANGE SCAN               | FK_INT_TO_INT_MASTER           |     1 |       |     1   (0)| 00:00:01 |
|  53 |          NESTED LOOPS                     |                                |       |       |            |          |
|  54 |           NESTED LOOPS                    |                                |     1 |    96 |     3   (0)| 00:00:01 |
|  55 |            NESTED LOOPS                   |                                |     1 |    83 |     2   (0)| 00:00:01 |
|* 56 |             TABLE ACCESS BY INDEX ROWID   | TASK_DTL                       |     1 |    64 |     1   (0)| 00:00:01 |
|* 57 |              INDEX RANGE SCAN             | FK_TASK_DTL_TO_ITEM_MASTER     |     2 |       |     1   (0)| 00:00:01 |
|* 58 |             INDEX RANGE SCAN              | CH_WAVE_IND1                   |     1 |    19 |     1   (0)| 00:00:01 |
|* 59 |            INDEX UNIQUE SCAN              | PK_TASK_HDR                    |     1 |       |     1   (0)| 00:00:01 |
|* 60 |           TABLE ACCESS BY INDEX ROWID     | TASK_HDR                       |     1 |    13 |     1   (0)| 00:00:01 |
|  61 |          NESTED LOOPS SEMI                |                                |     1 |   111 |     4   (0)| 00:00:01 |
|  62 |           NESTED LOOPS                    |                                |     1 |   100 |     3   (0)| 00:00:01 |
|  63 |            NESTED LOOPS                   |                                |     1 |    83 |     2   (0)| 00:00:01 |
|* 64 |             TABLE ACCESS BY INDEX ROWID   | TASK_DTL                       |     1 |    64 |     1   (0)| 00:00:01 |
|* 65 |              INDEX RANGE SCAN             | FK_TASK_DTL_TO_ITEM_MASTER     |     2 |       |     1   (0)| 00:00:01 |
|* 66 |             INDEX RANGE SCAN              | CH_WAVE_IND1                   |     1 |    19 |     1   (0)| 00:00:01 |
|* 67 |            TABLE ACCESS BY INDEX ROWID    | TASK_HDR                       |     1 |    17 |     1   (0)| 00:00:01 |
|* 68 |             INDEX UNIQUE SCAN             | PK_TASK_HDR                    |     1 |       |     1   (0)| 00:00:01 |
|* 69 |           TABLE ACCESS BY INDEX ROWID     | INVN_NEED_TYPE                 |    14 |   154 |     1   (0)| 00:00:01 |
|* 70 |            INDEX RANGE SCAN               | FK_INT_TO_INT_MASTER           |     1 |       |     1   (0)| 00:00:01 |
|  71 |          NESTED LOOPS                     |                                |       |       |            |          |
|  72 |           NESTED LOOPS                    |                                |    15 |  1725 |     9   (0)| 00:00:01 |
|  73 |            TABLE ACCESS BY INDEX ROWID    | CARTON_DTL                     |    18 |  1566 |     4   (0)| 00:00:01 |
|* 74 |             INDEX RANGE SCAN              | CARTON_DTL_IND_1               |    18 |       |     1   (0)| 00:00:01 |
|* 75 |            INDEX UNIQUE SCAN              | PK_CARTON_HDR                  |     1 |       |     1   (0)| 00:00:01 |
|* 76 |           TABLE ACCESS BY INDEX ROWID     | CARTON_HDR                     |     1 |    28 |     1   (0)| 00:00:01 |
|  77 |          NESTED LOOPS SEMI                |                                |    15 |  2085 |    12   (0)| 00:00:01 |
|  78 |           NESTED LOOPS                    |                                |    15 |  1725 |     8   (0)| 00:00:01 |
|* 79 |            TABLE ACCESS BY INDEX ROWID    | CARTON_DTL                     |    15 |  1305 |     4   (0)| 00:00:01 |
|* 80 |             INDEX RANGE SCAN              | CARTON_DTL_IND_1               |    18 |       |     1   (0)| 00:00:01 |
|* 81 |            TABLE ACCESS BY INDEX ROWID    | CARTON_HDR                     |     1 |    28 |     1   (0)| 00:00:01 |
|* 82 |             INDEX UNIQUE SCAN             | PK_CARTON_HDR                  |     1 |       |     1   (0)| 00:00:01 |
|* 83 |           INDEX RANGE SCAN                | ALLOC_INVN_DTL_IND_5           | 91590 |  2146K|     1   (0)| 00:00:01 |
|  84 |          NESTED LOOPS SEMI                |                                |     1 |   120 |    15   (0)| 00:00:01 |
|  85 |           NESTED LOOPS                    |                                |    15 |  1725 |     8   (0)| 00:00:01 |
|* 86 |            TABLE ACCESS BY INDEX ROWID    | CARTON_DTL                     |    15 |  1305 |     4   (0)| 00:00:01 |
|* 87 |             INDEX RANGE SCAN              | CARTON_DTL_IND_1               |    18 |       |     1   (0)| 00:00:01 |
|* 88 |            TABLE ACCESS BY INDEX ROWID    | CARTON_HDR                     |     1 |    28 |     1   (0)| 00:00:01 |
|* 89 |             INDEX UNIQUE SCAN             | PK_CARTON_HDR                  |     1 |       |     1   (0)| 00:00:01 |
|* 90 |           TABLE ACCESS BY INDEX ROWID     | TASK_DTL                       |     1 |     5 |     1   (0)| 00:00:01 |
|* 91 |            INDEX RANGE SCAN               | TASK_DTL_IND_4                 |     1 |       |     1   (0)| 00:00:01 |
|* 92 |          TABLE ACCESS BY INDEX ROWID      | TRANS_INVN                     |     1 |    69 |     1   (0)| 00:00:01 |
|* 93 |           INDEX RANGE SCAN                | FK_TRANS_INVN_TO_ITEM_MASTER   |     1 |       |     1   (0)| 00:00:01 |
|  94 |          NESTED LOOPS                     |                                |       |       |            |          |
|  95 |           NESTED LOOPS                    |                                |     1 |    86 |     2   (0)| 00:00:01 |
|* 96 |            TABLE ACCESS BY INDEX ROWID    | TRANS_INVN                     |     1 |    69 |     1   (0)| 00:00:01 |
|* 97 |             INDEX RANGE SCAN              | FK_TRANS_INVN_TO_ITEM_MASTER   |     1 |       |     1   (0)| 00:00:01 |
|* 98 |            INDEX UNIQUE SCAN              | PK_SYS_CODE                    |     1 |       |     1   (0)| 00:00:01 |
|* 99 |           TABLE ACCESS BY INDEX ROWID     | SYS_CODE                       |     1 |    17 |     1   (0)| 00:00:01 |
|*100 |          FILTER                           |                                |       |       |            |          |
| 101 |           NESTED LOOPS                    |                                |       |       |            |          |
| 102 |            NESTED LOOPS                   |                                |     1 |    65 |     2   (0)| 00:00:01 |
|*103 |             TABLE ACCESS BY INDEX ROWID   | PICK_LOCN_DTL                  |     1 |    46 |     1   (0)| 00:00:01 |
|*104 |              INDEX RANGE SCAN             | PICK_LOCN_DTL_IND_1            |     1 |       |     1   (0)| 00:00:01 |
|*105 |             INDEX UNIQUE SCAN             | PK_LOCN_HDR                    |     1 |       |     1   (0)| 00:00:01 |
|*106 |            TABLE ACCESS BY INDEX ROWID    | LOCN_HDR                       |     1 |    19 |     1   (0)| 00:00:01 |
| 107 |           SORT UNIQUE                     |                                |     2 |    54 |     6  (67)| 00:00:01 |
| 108 |            UNION-ALL                      |                                |       |       |            |          |
| 109 |             NESTED LOOPS                  |                                |     1 |    29 |     2   (0)| 00:00:01 |
|*110 |              TABLE ACCESS BY INDEX ROWID  | SYS_CODE_TYPE                  |     1 |     8 |     1   (0)| 00:00:01 |
|*111 |               INDEX UNIQUE SCAN           | PK_SYS_CODE_TYPE               |     1 |       |     1   (0)| 00:00:01 |
|*112 |              TABLE ACCESS BY INDEX ROWID  | WHSE_SYS_CODE                  |     1 |    21 |     1   (0)| 00:00:01 |
|*113 |               INDEX UNIQUE SCAN           | PK_WHSE_SYS_CODE               |     1 |       |     1   (0)| 00:00:01 |
| 114 |             NESTED LOOPS                  |                                |     1 |    25 |     2   (0)| 00:00:01 |
|*115 |              TABLE ACCESS BY INDEX ROWID  | SYS_CODE_TYPE                  |     1 |     8 |     1   (0)| 00:00:01 |
|*116 |               INDEX UNIQUE SCAN           | PK_SYS_CODE_TYPE               |     1 |       |     1   (0)| 00:00:01 |
|*117 |              TABLE ACCESS BY INDEX ROWID  | SYS_CODE                       |     1 |    17 |     1   (0)| 00:00:01 |
|*118 |               INDEX UNIQUE SCAN           | PK_SYS_CODE                    |     1 |       |     1   (0)| 00:00:01 |
| 119 |          NESTED LOOPS                     |                                |       |       |            |          |
| 120 |           NESTED LOOPS                    |                                |     1 |   105 |     4   (0)| 00:00:01 |
| 121 |            NESTED LOOPS                   |                                |     3 |   132 |     3   (0)| 00:00:01 |
| 122 |             TABLE ACCESS BY INDEX ROWID   | PKT_DTL                        |     4 |   116 |     2   (0)| 00:00:01 |
|*123 |              INDEX RANGE SCAN             | PKT_DTL_IND_6                  |     4 |       |     1   (0)| 00:00:01 |
| 124 |             TABLE ACCESS BY INDEX ROWID   | PKT_HDR                        |     1 |    15 |     1   (0)| 00:00:01 |
|*125 |              INDEX UNIQUE SCAN            | PK_PKT_HDR                     |     1 |       |     1   (0)| 00:00:01 |
|*126 |            INDEX RANGE SCAN               | PKT_DTL_SKU_INVN_IND_1         |     1 |       |     1   (0)| 00:00:01 |
|*127 |           TABLE ACCESS BY INDEX ROWID     | PKT_DTL_SKU_INVN               |     1 |    61 |     1   (0)| 00:00:01 |
| 128 |          NESTED LOOPS                     |                                |       |       |            |          |
| 129 |           NESTED LOOPS                    |                                |     1 |   222 |     2   (0)| 00:00:01 |
| 130 |            TABLE ACCESS BY INDEX ROWID    | STORE_DISTRO                   |     1 |   162 |     1   (0)| 00:00:01 |
|*131 |             INDEX RANGE SCAN              | STORE_DISTRO_IND_10            |     1 |       |     1   (0)| 00:00:01 |
|*132 |            INDEX RANGE SCAN               | FK_STORE_DISTRO_SKU_INVN_TO_SD |     1 |       |     1   (0)| 00:00:01 |
|*133 |           TABLE ACCESS BY INDEX ROWID     | STORE_DISTRO_SKU_INVN          |     1 |    60 |     1   (0)| 00:00:01 |
|*134 |          TABLE ACCESS BY INDEX ROWID      | ALLOC_INVN_DTL                 |     1 |    54 |     1   (0)| 00:00:01 |
|*135 |           INDEX RANGE SCAN                | FK_ALLOC_INVN_DTL_TO_ITEM_MAST |     2 |       |     1   (0)| 00:00:01 |
| 136 |          NESTED LOOPS                     |                                |       |       |            |          |
| 137 |           NESTED LOOPS                    |                                |     1 |    71 |     2   (0)| 00:00:01 |
|*138 |            TABLE ACCESS BY INDEX ROWID    | ALLOC_INVN_DTL                 |     1 |    57 |     1   (0)| 00:00:01 |
|*139 |             INDEX RANGE SCAN              | FK_ALLOC_INVN_DTL_TO_ITEM_MAST |     2 |       |     1   (0)| 00:00:01 |
|*140 |            INDEX UNIQUE SCAN              | PK_INVN_NEED_TYPE              |     1 |       |     1   (0)| 00:00:01 |
|*141 |           TABLE ACCESS BY INDEX ROWID     | INVN_NEED_TYPE                 |     1 |    14 |     1   (0)| 00:00:01 |
| 142 |          NESTED LOOPS                     |                                |       |       |            |          |
| 143 |           NESTED LOOPS                    |                                |     1 |    72 |     2   (0)| 00:00:01 |
|*144 |            TABLE ACCESS BY INDEX ROWID    | TASK_DTL                       |     1 |    55 |     1   (0)| 00:00:01 |
|*145 |             INDEX RANGE SCAN              | FK_TASK_DTL_TO_ITEM_MASTER     |     2 |       |     1   (0)| 00:00:01 |
|*146 |            INDEX UNIQUE SCAN              | PK_TASK_HDR                    |     1 |       |     1   (0)| 00:00:01 |
|*147 |           TABLE ACCESS BY INDEX ROWID     | TASK_HDR                       |     1 |    17 |     1   (0)| 00:00:01 |
| 148 |          NESTED LOOPS SEMI                |                                |     1 |    89 |     3   (0)| 00:00:01 |
| 149 |           NESTED LOOPS                    |                                |     1 |    75 |     2   (0)| 00:00:01 |
|*150 |            TABLE ACCESS BY INDEX ROWID    | TASK_DTL                       |     1 |    58 |     1   (0)| 00:00:01 |
|*151 |             INDEX RANGE SCAN              | FK_TASK_DTL_TO_ITEM_MASTER     |     2 |       |     1   (0)| 00:00:01 |
|*152 |            TABLE ACCESS BY INDEX ROWID    | TASK_HDR                       |     1 |    17 |     1   (0)| 00:00:01 |
|*153 |             INDEX UNIQUE SCAN             | PK_TASK_HDR                    |     1 |       |     1   (0)| 00:00:01 |
|*154 |           TABLE ACCESS BY INDEX ROWID     | INVN_NEED_TYPE                 |    14 |   196 |     1   (0)| 00:00:01 |
|*155 |            INDEX RANGE SCAN               | FK_INT_TO_INT_MASTER           |     1 |       |     1   (0)| 00:00:01 |
| 156 |          NESTED LOOPS                     |                                |       |       |            |          |
| 157 |           NESTED LOOPS                    |                                |     1 |   101 |     2   (0)| 00:00:01 |
|*158 |            TABLE ACCESS BY INDEX ROWID    | ASN_DTL                        |     1 |    83 |     1   (0)| 00:00:01 |
|*159 |             INDEX RANGE SCAN              | ASN_DTL_IND_1                  |     3 |       |     1   (0)| 00:00:01 |
|*160 |            INDEX UNIQUE SCAN              | PK_ASN_HDR                     |     1 |       |     1   (0)| 00:00:01 |
|*161 |           TABLE ACCESS BY INDEX ROWID     | ASN_HDR                        |     1 |    18 |     1   (0)| 00:00:01 |
| 162 |          NESTED LOOPS                     |                                |       |       |            |          |
| 163 |           NESTED LOOPS                    |                                |     1 |   195 |     2   (0)| 00:00:01 |
| 164 |            TABLE ACCESS BY INDEX ROWID    | WORK_ORD_HDR                   |     1 |    60 |     1   (0)| 00:00:01 |
|*165 |             INDEX RANGE SCAN              | WORK_ORD_HDR_IND_1             |     1 |       |     1   (0)| 00:00:01 |
|*166 |            INDEX RANGE SCAN               | FK_WORK_ORD_DTL_TO_ITEM_MASTER |     1 |       |     1   (0)| 00:00:01 |
|*167 |           TABLE ACCESS BY INDEX ROWID     | WORK_ORD_DTL                   |     1 |   135 |     1   (0)| 00:00:01 |
| 168 |   VIEW PUSHED PREDICATE                   |                                |     2 |     8 |     9  (12)| 00:00:01 |
| 169 |    NESTED LOOPS                           |                                |     2 |   178 |     9  (12)| 00:00:01 |
|*170 |     VIEW                                  | index$_join$_011               |   559 | 31304 |     8  (13)| 00:00:01 |
|*171 |      HASH JOIN                            |                                |       |       |            |          |
|*172 |       INDEX RANGE SCAN                    | PKT_HDR_INTRNL_IND_3           |   559 | 31304 |     1   (0)| 00:00:01 |
| 173 |       INDEX FAST FULL SCAN                | PK_PKT_HDR_INTRNL              |   559 | 31304 |     6   (0)| 00:00:01 |
|*174 |     INDEX RANGE SCAN                      | PKT_DTL_IND_7                  |     1 |    33 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------
------------------------ AWR top 5 ---------------------------

DB CPU   12,130   39.87   
library cache lock 19 3,974 209179 13.06 Concurrency 
db file sequential read 32,955 78 2 0.26 User I/O 
log file sync 24,401 35 1 0.12 Commit 
enq: TX - row lock contention 27 30 1114 0.10 Application 
Edited by: 937853 on Jan 16, 2013 9:41 AM
  • 1. Re: GATHER_STALE_STATS_JOB issue.
    TSharma-Oracle Guru
    Currently Being Moderated
    This explain plan above is ver normal for this job. The job is doing what it is supposed to do . if there is more work to do so , it will more time. But it should be stopped after he maintenance time.
    Can you check what is the maintenance window scheduled? If it is running after the maintenance window means you are hitting some bug.
  • 2. Re: GATHER_STALE_STATS_JOB issue.
    Mark D Powell Guru
    Currently Being Moderated
    What kind of memory management are you using? Oracle attempting to move chunks of memory around in the SGA can cause the Library Cache waits. There are numerous notes available at support related to this event. The first note below covers debugging while the second is about a specific problem.


    Note: 444560.1 Troubleshooting Library Cache: Lock, Pin and Load Lock

    Note: 742599.1 High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity

    HTH -- Mark D Powell --
  • 3. Re: GATHER_STALE_STATS_JOB issue.
    940856 Newbie
    Currently Being Moderated
    Sorry for being late on my response. Also, would like to thank you for suggesting oracle notes. We using automatic memory management here. One thing, I am concerned here is that from last 2 days , it giving us the trouble and it got fixed automatically , once the GATHER_STALE_STATS_JOB is stopped. I am sure, this job had not caused the issue , but there must be something else, which might be making the slowness to occur.
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------------
    hi_shared_memory_address             integer     0
    memory_max_target                    big integer 4G
    memory_target                        big integer 4G
    shared_memory_address                integer     0
  • 4. Re: GATHER_STALE_STATS_JOB issue.
    940856 Newbie
    Currently Being Moderated
    Thanks for the inputs , Mate !. Earlier , it used to run in no time , less then a minute and from last 2 days , it giving us the trouble. Also, the explain , which you claim is normal. do you think , it's because of Gather Stale Stats or something else here.

    Regards
  • 5. Re: GATHER_STALE_STATS_JOB issue.
    Mark D Powell Guru
    Currently Being Moderated
    The gather stats task should run in the overnight and weekend window so unless someone launched a gather stats task during the day it should not be your problem. That is unless you are seeing the issue at night and it goes away once the stats job terminated for the day.

    You should be able to look at the memory management views to see if the note from Oracle about automatic memory managment granule migration being a potential cause of the waits applies. If it looks like you have issues in this area you could set reasonable minimum pool sizes to prevent Oracle from overdoing the memory migrations and see if that helps.

    Naturally you should also look at the query plan and try to determine if it is the same plan the query would have been using last week. Check the tables/indexes to see when the statistics were last updated for the object and note if any were just changed while the others were not. Sometimes a change to one objects effects what the CBO expects to find in relation to how many rows in one object correspond to how many qualifying rows in another and you need the objects to have statistics collected at the same time.

    A big question is, is the problem database wide or actually limited to a specific query or application process.

    HTH -- Mark D Powell --

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points