Oracle 通過rowid秒優SQL

君落塵發表於2015-02-28
OracleSQL


標題： Oracle 通過rowid秒優SQL

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]


註釋：
  前段時間，有2個SQL，每天訪問至少上千次,操作不頻繁時穩定在6s，頻繁時13s；
  加2個欄位的組合索引，查詢還是不走索引！這種情況通過什麼方式合理走索引？[不是hint] 
  

環境：

AIX
7.1
 >> CPU 64 (16C)
 >> Mem 64
Oracle 11.2.0.3.0
 >>
Mem 40G


表結構/資料情況 
 
*****  表cs_oi_confirm_1段大小 2213MB；新建表,不存在高水位等問題..
COLUMN_NAME
COLUMN_ID
DATA_TYPE
NULLABLE
NUM_DISTINCT
CON_OI_ID 1 NUMBER N 5182502
->主鍵
NATI_TASK_ID 2 NUMBER Y 51048
SUPP_PROT_ID 3 NUMBER Y 41684
BUSI_CUST_ID 4 NUMBER N 4756
ORDER_ID 5 NUMBER N 317152
EMP_ID 6 NUMBER Y 310560
OI_STAT 7 NUMBER N 5
PAY_WAY 8 NUMBER Y 3
PAY_FEES_WAY 9 NUMBER Y 3
INS_ACCU_NO 10 VARCHAR2 Y 0
PROT_DET_ID 11 NUMBER Y 176880
CHAR_COUN_TYPE 12 NUMBER Y 3
PROD_PRICE_SCHM_ID 13 NUMBER N 2050
PROD_SCHM_ID 14 NUMBER N 1380
PROD_ID 15 NUMBER N 50
EXECU_AREA_ID 16 NUMBER N 161
EXECU_SUPP_ID 17 NUMBER N 101
PROT_LOCAL_STAN_ID 18 NUMBER Y 268800
LOCAL_STAN_ID 19 NUMBER Y 2319
OI_OPE_CATEGORY 20 NUMBER N 7
DECL_WAGE 21 NUMBER Y 39912
QUANTITY 22 NUMBER N 2
PAY_BEGIN_YM 23 NUMBER Y 179
PAY_SUPP_PRICE 24 NUMBER N 138688
CUST_BEGIN_DATE 25 DATE N 253
CUST_END_DATE 26 DATE Y 139
CUST_PER_BASE 27 NUMBER Y 35264
CUST_COM_BASE 28 NUMBER Y 35936
NATI_BEGIN_DATE 29 DATE N 251
NATI_END_DATE 30 DATE Y 139
NATI_PER_BASE 31 NUMBER Y 35264
NATI_COM_BASE 32 NUMBER Y 35936
NATI_PUBLISH_REP_ID 33 NUMBER N 128
NATI_PUBLISH_TIME 34 DATE N 896064
NATI_PUBLISH_DESC 35 VARCHAR2 Y 2
NATI_PUBLISH_EDI_REP_ID 36 NUMBER Y 25
NATI_PUBLISH_EDI_TIME 37 DATE Y 3566
SUPP_BEGIN_DATE 38 DATE N 250
SUPP_END_DATE 39 DATE Y 129
SUPP_PER_BASE 40 NUMBER Y 35340
SUPP_COM_BASE 41 NUMBER Y 36012
SUPP_DECL_DATE 42 DATE Y 1747
SUPP_EDI_DESC 43 VARCHAR2 Y 9
SUPP_EDI_ADD_DESC 44 VARCHAR2 Y 345
SUPP_CONF_TIME 45 DATE Y 802432
NATI_CONF_TIME 46 DATE Y 772672
NATI_CONF_DESC 47 VARCHAR2 Y 0
NATI_ADD_DESC 48 VARCHAR2 Y 0
DECL_ADD_SUB_TYPE 49 NUMBER Y 0
PARENT_ID 50 NUMBER Y 5351
OI_DET_CRE_YM 51 NUMBER Y 44
CHARHZ 52 NUMBER N 3
BILLING_HZ 53 NUMBER N 3
IS_CALC 54 NUMBER Y 2
REF_BACK_SUPP_BEGIN_DATE 55 DATE Y 0
REF_BACK_SUPP_END_DATE 56 DATE Y 0
REF_BACK_SUPP_PER_BASE 57 NUMBER Y 0
REF_BACK_SUPP_COM_BASE 58 NUMBER Y 0
BUSI_BEGIN_DATE 59 DATE Y 254
BUSI_END_DATE 60 DATE Y 129
BUSI_PER_BASE 61 NUMBER Y 35284
BUSI_COM_BASE 62 NUMBER Y 35960
BUSI_CONF_REP_ID 63 NUMBER Y 345
BUSI_CONF_TIME 64 DATE Y 813696
BUSI_CONF_DESC 65 VARCHAR2 Y 0
CHAR_BEGIN_YM 66 NUMBER Y 2
CATEGORY 67 NUMBER N 3
SUPP_TASK_ID 68 NUMBER Y 99968
BUSI_TASK_ID 69 NUMBER Y 91584
OI_OPE_CATEGORY_BACKUP 70 NUMBER Y 3
BUSI_CIRC_STATE 71 NUMBER N 6
BUSI_CIRC_HANDLE_TYPE 72 NUMBER Y 3
RECV_NATI_OUT_TIME 73 DATE Y 569
RECV_NATI_OUT_USER_ROLE_ID 74 NUMBER Y 2
RECV_BUSI_INTO_TIME 75 DATE Y 1354
RECV_BUSI_INTO_USER_ROLE_ID 76 NUMBER Y 80
RECV_BUSI_DISPH_TIME 77 DATE Y 2071
RECV_BUSI_DISPH_USER_ROLE_ID 78 NUMBER Y 80
RECV_ES_COOK_TIME 79 DATE Y 11
RECV_ES_COOK_USER_ROLE_ID 80 NUMBER Y 9
RECV_BUSI_UPDATE_BEGIN_DATE 81 DATE Y 243
RECV_BUSI_UPDATE_END_DATE 82 DATE Y 137
RECV_BUSI_UPDATE_PER_BASE 83 NUMBER Y 14502
RECV_BUSI_UPDATE_COM_BASE 84 NUMBER Y 14667
IS_DEL 85 NUMBER N 2
IS_REF_BACK_HIS 86 NUMBER Y 1
ORD_SEND_SUPP_ID 87 NUMBER N 57
ORD_RECE_SUPP_ID 88 NUMBER N 101
OI_CRE_CATEGORY 89 NUMBER Y 0
LEVEL_ 90 NUMBER N 2
IS_SEND_BIG_WHOLE 91 NUMBER N 3
PER_PAY_PRICE 92 NUMBER Y 56080
COM_PAY_PRICE 93 NUMBER Y 112944
NATI_PUBLISH_BUR_ID 94 NUMBER N 155
NATI_PUBLISH_ADD_DESC 95 VARCHAR2 Y 0
PER_ATTACH 96 NUMBER Y 23
COM_ATTACH 97 NUMBER Y 31
PER_PERCENT 98 NUMBER Y 35
COM_PERCENT 99 NUMBER Y 71
BUSI_MNGR_APP_STAT 100 NUMBER Y 4
ORD_SEND_PROD_SCHM_ID 101 NUMBER N 1073
NATI_EDI_DESC 102 NUMBER Y 5
NATI_EDI_ADD_DESC 103 VARCHAR2 Y 20
NATI_REFU_EDI_DESC 104 NUMBER Y 6
NATI_REFU_EDI_ADD_DESC 105 VARCHAR2 Y 17
BAT_NUM 106 NUMBER Y 5469
ORD_RECE_PROD_PRIC_SCHM_ID 107 NUMBER N 2654
SEC_INV_SUBJECT 108 NUMBER N 75
BILLS_TYPE 109 NUMBER N 2
ACCOU_ITEM_ID 110 NUMBER N 4
CHANGE_PROT_USER_ROLE_ID 111 NUMBER Y 0
CHANGE_PROT_TIME 112 DATE Y 0
SEND_EVA_INFO_ID 113 NUMBER Y 315168
ORDER_CATE 114 NUMBER N 2
CON_ACCE_ID 115 NUMBER Y 9985
NATI_EDIT_COST_DESC 116 NUMBER Y 19
NATI_EDIT_COST_ADD_DESC 117 VARCHAR2 Y 496
SUPP_DECL_DATE_OLD 118 DATE Y 14
IS_BATCH 119 NUMBER Y 2
SOURCE_OI_ID 120 VARCHAR2 Y 1112064
HIS_RE_BA_NUMBER 121 VARCHAR2 Y 710
REAL_ORD_SEND_SUPP_ID 122 NUMBER Y 56
REAL_ORD_RECE_SUPP_ID 123 NUMBER Y 100
IS_EFFECT 124 NUMBER Y 2
parent_id    is not null 總數 15899  
source_oi_id is not null 總數 1113634


索引情況:
索引名字
大小(MB)
column_name
屬性
IDX_COC_SOURCE_PARENT_ID 25 SOURCE_OI_ID,PARENT_ID bree
PK_cs_oi_confirm_1 
96
CON_OI_ID
bree[unique]
 

SQL 1、 檢視原SQL 速度/執行計劃：


SQL>  select to_char(wm_concat(c.con_oi_id)) from cs_oi_confirm_1  c  where c.parent_id in (3141609 , 3182013) and c.supp_prot_id = 6002704 ;

TO_CHAR(WM_CONCAT(C.CON_OI_ID))
--------------------------------------------------------------------------------
3141614,3141603,3141602

Elapsed: 00:00:06.27

Execution Plan
----------------------------------------------------------
Plan hash value: 475458160
------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     1 |    14 | 76035   (2)|00:15:13 |
|   1 |  SORT AGGREGATE    |                     |     1 |    14 |            |         |
|*  2 |   TABLE ACCESS FULL| CS_OI_CONFIRM_1 |     1 |    14 | 76035   (2)|00:15:13 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C"."SUPP_PROT_ID"=6002704 AND ("C"."PARENT_ID"=3141609 OR
              "C"."PARENT_ID"=3182013))
Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
     276415  consistent gets
     276408  physical reads
          0  redo size
        570  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>  


分析優化方案：

    原sql執行6s全掃；我們上文提到，已加SOURCE_OI_ID,PARENT_ID的組合索引IDX_COC_SOURCE_PARENT_ID；
    ？我們分析下為啥沒有走索引
    **** 1 ）不加supp_prot_id=6002704，肯定會走主鍵索引和組合索引；
    **** 2 ）加上supp_prot_id=6002704，CBO 可能考慮（parent_id distinct key 佔整個表0.1%，supp_prot_id 回表，組合索引'後導列INDEX SKIP SCAN'等情況選擇了全掃）等情況選了全掃.
    **** 3 ）parent_id傳一個值 和supp_prot_id = 6002704 時會走索引.. [多值時 衡量全表多塊讀更快..]
    **    分析走索引是否合理？
    ****通過 parent_id is not null 總數= 15899 、distinct key = 5351 資訊 和確認業務傳值一般不到20個，走索引還是很合理的。
    ？怎麼叫他走索引呢    .... 避免不走索引的幾項 
    **** 1 ）避免supp_prot_id = 6002704回表而不走索引，可以由一個sql 改成2個sql實現，先找到唯一資料，再通過唯一資料查詢表 再做回表關聯；
    **** 2 ）唯一資料的選擇主鍵(con_oi_id) 或 rowid 唯一標示；
                 SQl1、select to_char(wm_concat( '''' || con_oi_id || '''' ))  from cs_oi_confirm_1 c where c.parent_id in ( 3141609 ,  3182013 );
                 SQl2、select to_char(wm_concat( '''' || rowid || ''''))  from cs_oi_confirm_1 c where c.parent_id in ( 3141609 ,  3182013 );
    **** 3 ）若用主鍵，sql1 需要主鍵和組合索引關聯，而用SQL2只用組合索引就可以實現； 


***最後優化的SQL如下：
步驟1、select to_char(wm_concat( '''' || rowid || ''''))  from cs_oi_confirm_1 c where c.parent_id in ( 3141609,3182013 );
步驟2、SELECT to_char(wm_concat(c.con_oi_id)) FROM cs_oi_confirm_1 c where rowid in (上結果集) and c.supp_prot_id = 6002704;


看下優化效果~


步驟1：
SQL> select to_char(wm_concat( '''' || rowid || ''''))  from cs_oi_confirm_1 c where c.parent_id in (3141609,  3182013);

TO_CHAR(WM_CONCAT(''''||ROWID||''''))
--------------------------------------------------------------------------------
'AAAm6DAACAACWJ8AAR','AAAm6DAACAACWCZAAO','AAAm6DAACAACVXuAAH','AAAm6DAACAACWFfAAB','AAAm6DAACAACWFfAAC','AAAm6DAACAACWFfAAD'


Elapsed: 00:00:00.13

Execution Plan
----------------------------------------------------------
Plan hash value: 3275894710
--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                     | Rows  | Bytes | Cost(%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                          |     1 |    14 |   875   (4)| 00:00:11 |
|   1 |  SORT AGGREGATE       |                          |     1 |    14 |      |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_COC_SOURCE_PARENT_ID |     6 |    84 |   875   (4)| 00:00:11 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C"."PARENT_ID"=3141609 OR "C"."PARENT_ID"=3182013)

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

步驟2：

SQL>  SELECT to_char(wm_concat(c.con_oi_id)) FROM cs_oi_confirm_1 c where rowid in ('AAAm6DAACAACWJ8AAR','AAAm6DAACAACWCZAAO','AAAm6DAACAACVXuAAH','AAAm6DAACAACWFfAAB', 'AAAm6DAACAACWFfAAC', 'AAAm6DAACAACWFfAAD') and c.supp_prot_id = 6002704;

TO_CHAR(WM_CONCAT(C.CON_OI_ID))
--------------------------------------------------------------------------------
3141614,3141603,3141602

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1353697632
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    24 |1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                     |     1 |    24 |        |          |
|   2 |   INLIST ITERATOR            |                     |       |       |        |          |
|*  3 |    TABLE ACCESS BY USER ROWID| CS_OI_CONFIRM_1 |     1 |    24 |1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("C"."SUPP_PROT_ID"=6002704)

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

SQL> 


效果總結:
     由原elapsed time (s)6.27s提升到0.13+0.01=0.14(簡單理解就是講全掃優化成組合索引掃描)，物理邏輯讀 可以看上面資訊。
    

SQL 2、 檢視原SQL 速度/執行計劃：


SQL> select *   from cs_oi_confirm_1 t where 1 = 1 AND INSTR(',' || T.SOURCE_OI_ID || ',', 1259941) > 0  and t.is_del = 0 and SOURCE_OI_ID is not null   order by t.PROD_ID, t.NATI_PUBLISH_TIME DESC;

------ 一條記錄...欄位有些多..頁面問題,,在此不做顯示

Elapsed: 00:00:09.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2928122185
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes |TempSpc| Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     | 27841 |  9923K|       | 79088   (3)| 00:15:50 |
|   1 |  SORT ORDER BY     |                     | 27841 |  9923K|    16M| 79088   (3)| 00:15:50 |
|*  2 |   TABLE ACCESS FULL| CS_OI_CONFIRM_1 | 27841 |  9923K|       | 76914   (3)| 00:15:23 |
-------------------------------------------------------------------------------------------------

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

   2 - filter("SOURCE_OI_ID" IS NOT NULL AND "T"."IS_DEL"=0 AND
              INSTR(','||"T"."SOURCE_OI_ID"||',','1259941')>0)

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


方案思路都和sql1 相同...

***最後優化的SQL如下：
步驟1、select /*count(1)*/to_char(wm_concat(rowid))  from cs_oi_confirm_1 t  where INSTR(',' || T.SOURCE_OI_ID || ',', 1259941) > 0 and T.SOURCE_OI_ID is not null ;
步驟2、SELECT count(1) FROM cs_oi_confirm_1 t where rowid in ('AAAm6DAACAACYp5AAF')  and t.is_del = 0 order by t.PROD_ID, t.NATI_PUBLISH_TIME DESC;


看下優化效果~


步驟1：
SQL> select /*count(1)*/to_char(wm_concat(rowid))  from cs_oi_confirm_1 t  where INSTR(',' || T.SOURCE_OI_ID || ',', 1259941) > 0 and T.SOURCE_OI_ID is not null --非必填項,不加宣告not null就不會走索引;
        /*and t.is_del = 0  order by t.PROD_ID, t.NATI_PUBLISH_TIME DESC*/

TO_CHAR(WM_CONCAT(ROWID))
---------------------------------------
AAAm6DAACAACYp5AAF

Elapsed: 00:00:00.46

Execution Plan
----------------------------------------------------------
Plan hash value: 3275894710

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                          |     1 |    15 |   875   (4)| 00:00:11 |
|   1 |  SORT AGGREGATE       |                          |     1 |    15 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_COC_SOURCE_PARENT_ID | 55682 |   815K|   875   (4)| 00:00:11 |
--------------------------------------------------------------------------------------------------

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

   2 - filter("T"."SOURCE_OI_ID" IS NOT NULL AND
              INSTR(','||"T"."SOURCE_OI_ID"||',','1259941')>0)


Statistics
----------------------------------------------------------
          1  recursive calls
         12  db block gets
       3228  consistent gets
          0  physical reads
          0  redo size
        559  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
步驟2：

SQL>  SELECT count(1) FROM cs_oi_confirm_1 t where rowid in ('AAAm6DAACAACYp5AAF')  and t.is_del = 0 order by t.PROD_ID, t.NATI_PUBLISH_TIME DESC;

  COUNT(1)
----------
         1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2781911543

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    15 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |                     |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS BY USER ROWID| CS_OI_CONFIRM_1 |     1 |    15 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."IS_DEL"=0)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          1  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> 

 


  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正...

此條目發表在   SQL、SQL優化篇  分類目錄。將固定連線加入收藏夾。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29819001/viewspace-1443579/，如需轉載，請註明出處，否則將追究法律責任。

相關文章