連結： http://blog.itpub.net/28602568/viewspace-1378912/

註釋：

前段時間，有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

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

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

| 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最佳化篇分類目錄。將固定連線加入收藏夾。

Oracle 透過rowid秒優SQL

相關文章