SQL优化--用各种hints优化一条SQL

最新推荐文章于 2024-03-09 16:25:58 发布

congche1987

最新推荐文章于 2024-03-09 16:25:58 发布

阅读量655

点赞数 1

文章标签： java

oracle 10g R2

加各种hints优化一条SQL：

Select Count(*) From t_Ho_Order_Statistics --2032946

Select Count(*) From t_Ho_Order_Info --2032946

其他都是小的维度表

统计信息已经检查过了，差不多10天前的（不过我10天前跑过这个SQL,出来的执行计划一样），

这里，这里就把注意力集中在两个大表连接的问题上，

后面的谓词过滤，这个SQL是程序里面拼出来的，每次谓词过滤条件可能不同。

=========== =========== =========== =========== =========== =========== =========== ===========

--<1>

下面这条SQL要运行5分钟左右，出来的结果为0

原SQL和执行计划

SQL> explain plan for

2 Select Count(*)

3 From (Select o.Order_Id,

4 o.Order_Version,

5 o.Guest_Name,

6 o.Order_Status_Id,

7 o.Order_Flow_Id,

8 o.Order_Occupt_Type_Id,

9 o.Pymt_Means_Id,

10 o.Contact_Name,

11 o.Reserv_Time,

12 h.Hotel_Name,

13 h.Address,

14 Arrival_Time,

15 Checkout_Time,

16 o.Use_Rule,

17 o.Guaranty_Type_Id,

18 Gt.Guaranty_Type_Desc,

19 o.Hbeorder_Id,

20 o.Order_Occupt_Type_Id Producttype,

21 o.Order_Type_Rule,

22 o.Hotel_Belong

23 From t_Ho_Order_Info o

24 Left Join t_Sd_Guaranty_Type Gt

25 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id

26 Inner Join t_Hp_Hotel h

27 On o.Hotel_Id = h.Hotel_Id

28 Inner Join t_Ho_Order_Statistics Os

29 On Os.Order_Id = o.Order_Id

30 Inner Join t_s_Employee e

31 On e.Employee_Id = o.Employee_Id

32 Where 1 = 1

33 And o.Guest_Name Like '%王明%'

34 And o.Reserv_Time >=

35 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')

36 And o.Reserv_Time <=

37 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0

38 ;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1815111547

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

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

| 0 | SELECT STATEMENT | | 1 | 49 | 11175 (38)| 00:02:15 |

| 1 | SORT AGGREGATE | | 1 | 49 | | |

| 2 | NESTED LOOPS OUTER | | 3430 | 164K| 11175 (38)| 00:02:15 |

| 3 | NESTED LOOPS | | 3430 | 154K| 11174 (38)| 00:02:15 |

| 4 | INDEX FAST FULL SCAN | HOBASEINFO_STATINFO_FK | 2016K| 11M| 1609 (7)| 00:00:20 |

|* 5 | TABLE ACCESS BY INDEX ROWID| T_HO_ORDER_INFO | 1 | 40 | 1 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | PK_T_HO_ORDER_INFO | 1 | | 1 (0)| 00:00:01 |

|* 7 | INDEX UNIQUE SCAN | PK_T_SD_GUARANTY_TYPE | 1 | 3 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

5 - filter("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND

"O"."GUEST_NAME" LIKE U'%\738B\634D\660E%' AND "O"."RESERV_TIME"<=TO_DATE('2012-05-24 19:00:00',

'yyyy-mm-dd hh24:mi:ss') AND "O"."EMPLOYEE_ID" IS NOT NULL)

6 - access("OS"."ORDER_ID"="O"."ORDER_ID")

7 - access("O"."GUARANTY_TYPE_ID"="GT"."GUARANTY_TYPE_ID"(+))

23 rows selected

注意：HOBASEINFO_STATINFO_FK为表t_Ho_Order_Statistics上的索引，问题比较明显，

嵌套循环驱动表出来了2064384行，下面虽然是通过索引唯一扫描访问t_ho_order_info

但是这个操作的次数实在是太多了，肯定慢。

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

=========== =========== =========== =========== =========== =========== =========== ===========

--<2>

还是让它走嵌套循环，换个驱动表，48秒左右跑完

SQL> explain plan for

2 Select Count(*)

3 From (Select /*+ leading(o,Os) use_nl(Os,o) */

4 o.Order_Id,

5 o.Order_Version,

6 o.Guest_Name,

7 o.Order_Status_Id,

8 o.Order_Flow_Id,

9 o.Order_Occupt_Type_Id,

10 o.Pymt_Means_Id,

11 o.Contact_Name,

12 o.Reserv_Time,

13 h.Hotel_Name,

14 h.Address,

15 Arrival_Time,

16 Checkout_Time,

17 o.Use_Rule,

18 o.Guaranty_Type_Id,

19 Gt.Guaranty_Type_Desc,

20 o.Hbeorder_Id,

21 o.Order_Occupt_Type_Id Producttype,

22 o.Order_Type_Rule,

23 o.Hotel_Belong

24 From t_Ho_Order_Info o

25 Left Join t_Sd_Guaranty_Type Gt

26 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id

27 Inner Join t_Hp_Hotel h

28 On o.Hotel_Id = h.Hotel_Id

29 Inner Join t_Ho_Order_Statistics Os

30 On Os.Order_Id = o.Order_Id

31 Inner Join t_s_Employee e

32 On e.Employee_Id = o.Employee_Id

33 Where 1 = 1

34 And o.Guest_Name Like '%王明%'

35 And o.Reserv_Time >=

36 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')

37 And o.Reserv_Time <=

38 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0

39 ;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 3583136125

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

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

| 0 | SELECT STATEMENT | | 1 | 49 | 19342 (4)| 00:03:53 |

| 1 | SORT AGGREGATE | | 1 | 49 | | |

| 2 | NESTED LOOPS OUTER | | 3430 | 164K| 19342 (4)| 00:03:53 |

| 3 | NESTED LOOPS | | 3430 | 154K| 19341 (4)| 00:03:53 |

|* 4 | TABLE ACCESS FULL| T_HO_ORDER_INFO | 2962 | 115K| 17262 (5)| 00:03:28 |

|* 5 | INDEX RANGE SCAN | HOBASEINFO_STATINFO_FK | 1 | 6 | 1 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | PK_T_SD_GUARANTY_TYPE | 1 | 3 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00', 'yyyy-mm-dd

hh24:mi:ss') AND "O"."GUEST_NAME" LIKE U'%\738B\634D\660E%' AND

"O"."RESERV_TIME"<=TO_DATE('2012-05-24 19:00:00', 'yyyy-mm-dd hh24:mi:ss') AND

"O"."EMPLOYEE_ID" IS NOT NULL)

5 - access("OS"."ORDER_ID"="O"."ORDER_ID")

6 - access("O"."GUARANTY_TYPE_ID"="GT"."GUARANTY_TYPE_ID"(+))

23 rows selected

=========== =========== =========== =========== =========== =========== =========== ===========

--<3>

通过加hints让其走hash,差不多40秒左右跑完

SQL> explain plan for

2 Select Count(*)

3 From (Select /*+ use_hash(Os,o) */

4 o.Order_Id,

5 o.Order_Version,

6 o.Guest_Name,

7 o.Order_Status_Id,

8 o.Order_Flow_Id,

9 o.Order_Occupt_Type_Id,

10 o.Pymt_Means_Id,

11 o.Contact_Name,

12 o.Reserv_Time,

13 h.Hotel_Name,

14 h.Address,

15 Arrival_Time,

16 Checkout_Time,

17 o.Use_Rule,

18 o.Guaranty_Type_Id,

19 Gt.Guaranty_Type_Desc,

20 o.Hbeorder_Id,

21 o.Order_Occupt_Type_Id Producttype,

22 o.Order_Type_Rule,

23 o.Hotel_Belong

24 From t_Ho_Order_Info o

25 Inner Join t_Sd_Guaranty_Type Gt

26 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id

27 Inner Join t_Hp_Hotel h

28 On o.Hotel_Id = h.Hotel_Id

29 Inner Join t_Ho_Order_Statistics Os

30 On Os.Order_Id = o.Order_Id

31 Inner Join t_s_Employee e

32 On e.Employee_Id = o.Employee_Id

33 Where 1 = 1

34 And o.Guest_Name Like '%王明%'

35 And o.Reserv_Time >=

36 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')

37 And o.Reserv_Time <=

38 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0

39 ;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 3811067636

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

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

| 0 | SELECT STATEMENT | | 1 | 46 | 18941 (5)| 00:03:48 |

| 1 | SORT AGGREGATE | | 1 | 46 | | |

|* 2 | HASH JOIN | | 3430 | 154K| 18941 (5)| 00:03:48 |

|* 3 | TABLE ACCESS FULL | T_HO_ORDER_INFO | 2962 | 115K| 17262 (5)| 00:03:28 |

| 4 | INDEX FAST FULL SCAN| HOBASEINFO_STATINFO_FK | 2016K| 11M| 1609 (7)| 00:00:20 |

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

Predicate Information (identified by operation id):

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

2 - access("OS"."ORDER_ID"="O"."ORDER_ID")

3 - filter("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

AND "O"."GUEST_NAME" LIKE U'%\738B\634D\660E%' AND

"O"."RESERV_TIME"<=TO_DATE('2012-05-24 19:00:00', 'yyyy-mm-dd hh24:mi:ss') AND

"O"."EMPLOYEE_ID" IS NOT NULL AND "O"."GUARANTY_TYPE_ID" IS NOT NULL)

20 rows selected

-----

注意：这里将hints的写法单单改成/*+ leading(o,OS)*/，直接加leading,它也选择了走hash,且执行计划和上面

一模一样。

=========== =========== =========== =========== =========== =========== =========== ===========

--<4>

直接加rule,12秒出结果

SQL> explain plan for

2 Select /*+ RULE */ Count(*)

3 From (Select o.Order_Id,

4 o.Order_Version,

5 o.Guest_Name,

6 o.Order_Status_Id,

7 o.Order_Flow_Id,

8 o.Order_Occupt_Type_Id,

9 o.Pymt_Means_Id,

10 o.Contact_Name,

11 o.Reserv_Time,

12 h.Hotel_Name,

13 h.Address,

14 Arrival_Time,

15 Checkout_Time,

16 o.Use_Rule,

17 o.Guaranty_Type_Id,

18 Gt.Guaranty_Type_Desc,

19 o.Hbeorder_Id,

20 o.Order_Occupt_Type_Id Producttype,

21 o.Order_Type_Rule,

22 o.Hotel_Belong

23 From t_Ho_Order_Info o

24 Inner Join t_Sd_Guaranty_Type Gt

25 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id

26 Inner Join t_Hp_Hotel h

27 On o.Hotel_Id = h.Hotel_Id

28 Inner Join t_Ho_Order_Statistics Os

29 On Os.Order_Id = o.Order_Id

30 Inner Join t_s_Employee e

31 On e.Employee_Id = o.Employee_Id

32 Where 1 = 1

33 And o.Guest_Name Like '%王明%'

34 And o.Reserv_Time >=

35 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')

36 And o.Reserv_Time <=

37 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0

38 ;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 4158354473

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | NESTED LOOPS | |

|* 3 | TABLE ACCESS BY INDEX ROWID| T_HO_ORDER_INFO |

|* 4 | INDEX RANGE SCAN | IDX_RESERV_TIME |

|* 5 | INDEX RANGE SCAN | HOBASEINFO_STATINFO_FK |

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

Predicate Information (identified by operation id):

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

3 - filter("O"."GUARANTY_TYPE_ID" IS NOT NULL AND "O"."EMPLOYEE_ID"

IS NOT NULL AND "O"."GUEST_NAME" LIKE U'%\738B\634D\660E%')

4 - access("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00',

'yyyy-mm-dd hh24:mi:ss') AND "O"."RESERV_TIME"<=TO_DATE('2012-05-24

19:00:00', 'yyyy-mm-dd hh24:mi:ss'))

5 - access("OS"."ORDER_ID"="O"."ORDER_ID")

Note

-----

- rule based optimizer used (consider using cbo)

26 rows selected

=========== =========== =========== =========== =========== =========== =========== ===========

注意：加了rule之后，是通过索引范围扫描访问了t_ho_order_info,Reserv_Time列上

的索引IDX_RESERV_TIME起效果了？关注一下这个索引。

--先前原始SQL走t_ho_order_info上面的PK_T_HO_ORDER_INFO，是因为嵌套循环可以通过等值条件使用索引，而非使用到了谓词过滤条件上面的索引列

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

Select c.Table_Name 表名,

a.Index_Name 索引名,

a.index_type 索引类型,

b.Column_Name 列名,

a.Distinct_Keys 列中不同的值,

a.Clustering_Factor As 集群因子,

a.Last_Analyzed As 索引上次统计信息收集时间,

c.Last_Analyzed As 表上次统计信息收集时间

From User_Indexes a, User_Ind_Columns b, User_Tables c

Where a.Index_Name = b.Index_Name

And a.Table_Name = c.Table_Name

And a.Index_Name = Upper('IDX_RESERV_TIME');

表名索引名索引类型列名列中不同的值集群因子索引上次统计信息收集时间表上次统计信息收集时间

T_HO_ORDER_INFO IDX_RESERV_TIME NORMAL RESERV_TIME 1763955 1699935 2012/3/28 17:17:05 2012/3/28 17:16:21

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

analyze index IDX_RESERV_TIME validate Structure

select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN

2064512 39225728 31495 598405

碎片貌似有点多

在线rebuild一下

alter index IDX_RESERV_TIME rebuild Online

analyze index IDX_RESERV_TIME validate Structure

select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN

2033136 38629584 0 0

exec dbms_stats.gather_index_stats('gc','IDX_RESERV_TIME');

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

将索引做了调整后，发现原SQL仍然是原来那种效果，没有改进

为什么没有使用IDX_RESERV_TIME？查看上面的索引状态，跟集群因子有较大关系

=========== =========== =========== =========== =========== =========== =========== ===========

--<5>

根据rule的思路，我们直接强制走索引IDX_RESERV_TIME，0.3秒出结果

SQL> explain plan for

2 Select Count(*)

3 From (Select /*+ index(o IDX_RESERV_TIME)*/

4 o.Order_Id,

5 o.Order_Version,

6 o.Guest_Name,

7 o.Order_Status_Id,

8 o.Order_Flow_Id,

9 o.Order_Occupt_Type_Id,

10 o.Pymt_Means_Id,

11 o.Contact_Name,

12 o.Reserv_Time,

13 h.Hotel_Name,

14 h.Address,

15 Arrival_Time,

16 Checkout_Time,

17 o.Use_Rule,

18 o.Guaranty_Type_Id,

19 Gt.Guaranty_Type_Desc,

20 o.Hbeorder_Id,

21 o.Order_Occupt_Type_Id Producttype,

22 o.Order_Type_Rule,

23 o.Hotel_Belong

24 From t_Ho_Order_Info o

25 Left Join t_Sd_Guaranty_Type Gt

26 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id

27 Inner Join t_Hp_Hotel h

28 On o.Hotel_Id = h.Hotel_Id

29 Inner Join t_Ho_Order_Statistics Os

30 On Os.Order_Id = o.Order_Id

31 Inner Join t_s_Employee e

32 On e.Employee_Id = o.Employee_Id

33 Where 1 = 1

34 And o.Guest_Name Like '%王明%'

35 And o.Reserv_Time >=

36 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')

37 And o.Reserv_Time <=

38 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0

39 ;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 3515346792

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

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

| 0 | SELECT STATEMENT | | 1 | 49 | 19878 (2)| 00:03:59 |

| 1 | SORT AGGREGATE | | 1 | 49 | | |

|* 2 | HASH JOIN | | 3430 | 164K| 19878 (2)| 00:03:59 |

| 3 | NESTED LOOPS OUTER | | 2962 | 124K| 18199 (1)| 00:03:39 |

|* 4 | TABLE ACCESS BY INDEX ROWID| T_HO_ORDER_INFO | 2962 | 115K| 18198 (1)| 00:03:39 |

|* 5 | INDEX RANGE SCAN | IDX_RESERV_TIME | 63952 | | 62 (4)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | PK_T_SD_GUARANTY_TYPE | 1 | 3 | 1 (0)| 00:00:01 |

| 7 | INDEX FAST FULL SCAN | HOBASEINFO_STATINFO_FK | 2016K| 11M| 1609 (7)| 00:00:20 |

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

Predicate Information (identified by operation id):

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

2 - access("OS"."ORDER_ID"="O"."ORDER_ID")

4 - filter("O"."GUEST_NAME" LIKE U'%\738B\634D\660E%' AND "O"."EMPLOYEE_ID" IS NOT NULL)

5 - access("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND

"O"."RESERV_TIME"<=TO_DATE('2012-05-24 19:00:00', 'yyyy-mm-dd hh24:mi:ss'))

6 - access("O"."GUARANTY_TYPE_ID"="GT"."GUARANTY_TYPE_ID"(+))

23 rows selected

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

转载于:http://blog.itpub.net/10678398/viewspace-721283/

congche1987

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
SQL优化--用各种hints优化一条SQL

oracle 10g R2加各种hints优化一条SQL：Select Count(*) From t_Ho_Order_Statistics --2032946Select ...
复制链接

扫一扫