20160519 cache_buffer_chains:latch

SQL> select count(*) from LRMS_OWNER.I_RESOURCE;

  COUNT(*)
----------
  36245811
 
SQL> SELECT * FROM dba_ind_columns  WHERE table_name='I_RESOURCE

INDEX_OWNER          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                    COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------- ------------- ----------- ----
LRMS_OWNER           IDX_I_RESOURCE_1               LRMS_OWNER                     I_RESOURCE                     RESOURCE_SMALL_TYPE                          1             4           4 ASC
LRMS_OWNER           IDX_I_RESOURCE_1               LRMS_OWNER                     I_RESOURCE                     RESOURCE_ID                                  2            20          20 ASC
LRMS_OWNER           IDX_I_RESOURCE_2               LRMS_OWNER                     I_RESOURCE                     AREA_ID                                      1             6           6 ASC
LRMS_OWNER           IDX_I_RESOURCE_3               LRMS_OWNER                     I_RESOURCE                     RES_INST_ID                                  1            22           0 ASC
LRMS_OWNER           IDX_I_RESOURCE_3               LRMS_OWNER                     I_RESOURCE                     RESOURCE_SMALL_TYPE                          2             4           4 ASC
LRMS_OWNER           PK_I_RESOURCE                  LRMS_OWNER                     I_RESOURCE                     RES_INST_ID                                  1            22           0 ASC
LRMS_OWNER           PK_I_RESOURCE                  LRMS_OWNER                     I_RESOURCE                     SEQ                                          2            22           0 ASC
LRMS_OWNER           UIDX_I_RESOURCE_4              LRMS_OWNER                     I_RESOURCE                     RES_INST_ID                                  1            22           0 ASC
LRMS_OWNER           UIDX_I_RESOURCE_4              LRMS_OWNER                     I_RESOURCE                     RESOURCE_TYPE                                2             4           4 ASC
LRMS_OWNER           UIDX_I_RESOURCE_4              LRMS_OWNER                     I_RESOURCE                     STS                                          3             4           4 ASC

10 rows selected.

create index LRMS_OWNER.IDX_I_RESOURCE_1 on LRMS_OWNER.I_RESOURCE (RESOURCE_SMALL_TYPE, RESOURCE_ID)
  local;
create index LRMS_OWNER.IDX_I_RESOURCE_2 on LRMS_OWNER.I_RESOURCE (AREA_ID)
  local;
create index LRMS_OWNER.IDX_I_RESOURCE_3 on LRMS_OWNER.I_RESOURCE (RES_INST_ID, RESOURCE_SMALL_TYPE)
  local;
create unique index LRMS_OWNER.UIDX_I_RESOURCE_4 on LRMS_OWNER.I_RESOURCE (RES_INST_ID, RESOURCE_TYPE, STS)
  tablespace TBSI_RMS_32M
  pctfree 60
  initrans 2
  maxtrans 255
  storage
  (
    initial 32M
    next 32M
    minextents 1
    maxextents unlimited
  );
 

有问题的执行计划
SQL_ID  7zjcuxfd8bf05, child number 0
-------------------------------------
SELECT *
  FROM I_RESOURCE T1
 WHERE T1.RESOURCE_SMALL_TYPE IN
       (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)
   AND T1.RES_INST_ID IN (SELECT T.RES_INST_ID
                            FROM I_RESOURCE T
                           WHERE T.RESOURCE_SMALL_TYPE = :12
                             AND T.RESOURCE_ID = :13
                             AND T.LATN_ID = :14
                             AND T.AREA_ID = :15)
   AND T1.STS = :16
   AND T1.LATN_ID = :17
   AND T1.AREA_ID = :18

 
Plan hash value: 864385991
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |       |       |     7 (100)|          |       |       |
|   1 |  NESTED LOOPS SEMI                  |                  |     1 |    94 |     7   (0)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE SINGLE            |                  |     1 |    61 |     4   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| I_RESOURCE       |     1 |    61 |     4   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | IDX_I_RESOURCE_2 |     1 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|   5 |   PARTITION RANGE SINGLE            |                  |     1 |    33 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |    TABLE ACCESS BY LOCAL INDEX ROWID| I_RESOURCE       |     1 |    33 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  7 |     INDEX RANGE SCAN                | IDX_I_RESOURCE_1 |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("T1"."LATN_ID"=:17 AND "T1"."STS"=:16 AND INTERNAL_FUNCTION("T1"."RESOURCE_SMALL_TYPE")))
   4 - access("T1"."AREA_ID"=:18)
   6 - filter(("T"."LATN_ID"=:14 AND "T"."AREA_ID"=:15 AND "T1"."RES_INST_ID"="T"."RES_INST_ID"))
   7 - access("T"."RESOURCE_SMALL_TYPE"=:12 AND "T"."RESOURCE_ID"=:13)
 

统计信息失效，重新收集一下表的信息，执行计划发生改变
调整完之后的执行计划
SQL_ID  7zjcuxfd8bf05, child number 2
-------------------------------------
SELECT *
  FROM I_RESOURCE T1
 WHERE T1.RESOURCE_SMALL_TYPE IN
       (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)
   AND T1.RES_INST_ID IN (SELECT T.RES_INST_ID
                            FROM I_RESOURCE T
                           WHERE T.RESOURCE_SMALL_TYPE = :12
                             AND T.RESOURCE_ID = :13
                             AND T.LATN_ID = :14
                             AND T.AREA_ID = :15)
   AND T1.STS = :16
   AND T1.LATN_ID = :17
   AND T1.AREA_ID = :18
 
Plan hash value: 8655894
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |       |       |    12 (100)|          |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID   | I_RESOURCE        |     1 |    61 |     7   (0)| 00:00:01 | ROW L | ROW L |
|   2 |   NESTED LOOPS                        |                   |     1 |    95 |    12   (9)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE                        |                   |     1 |    34 |     4   (0)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE SINGLE            |                   |     1 |    34 |     4   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |      TABLE ACCESS BY LOCAL INDEX ROWID| I_RESOURCE        |     1 |    34 |     4   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |       INDEX RANGE SCAN                | IDX_I_RESOURCE_1  |     1 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  7 |    INDEX RANGE SCAN                   | UIDX_I_RESOURCE_4 |     1 |       |     4   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("T1"."AREA_ID"=:18 AND "T1"."LATN_ID"=:17 AND INTERNAL_FUNCTION("T1"."RESOURCE_SMALL_TYPE")))
   5 - filter(("T"."AREA_ID"=:15 AND "T"."LATN_ID"=:14))
   6 - access("T"."RESOURCE_SMALL_TYPE"=:12 AND "T"."RESOURCE_ID"=:13)
   7 - access("T1"."RES_INST_ID"="T"."RES_INST_ID" AND "T1"."STS"=:16)
       filter("T1"."STS"=:16)
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26179376/viewspace-2103314/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26179376/viewspace-2103314/