SQL ProfilesPart IWord格式.docx

SQL ProfilesPart IWord格式.docx

《SQL ProfilesPart IWord格式.docx》由会员分享，可在线阅读，更多相关《SQL ProfilesPart IWord格式.docx（20页珍藏版）》请在冰豆网上搜索。

9.SQL>

index 

t2_idx 

on 

t2（object_id）;

10. 

11.索引已创建。

12. 

13.SQL>

exec 

dbms_stats.gather_table_stats（user,'

t1'

cascade=>

true,method_opt=>

'

for 

all 

columns 

size 

1'

）;

14. 

15.PL/SQL 

过程已成功完成。

16. 

17.SQL>

t2'

18. 

19.PL/SQL 

然后看看下面这一条SQL：

t1.*,t2.owner 

t1,t2 

t1.object_name 

like 

%T1%'

and 

t1.object_id=t2.object_id;

3.已选择29行。

5.执行计划 

6.---------------------------------------------------------- 

7.Plan 

hash 

value:

1838229974 

9.--------------------------------------------------------------------------- 

10.| 

Id 

| 

Operation 

Name 

Rows 

Bytes 

Cost 

（%CPU）| 

Time 

11.--------------------------------------------------------------------------- 

12.| 

0 

SELECT 

STATEMENT 

2498 

99920 

219 

（4）| 

00:

03 

13.|* 

1 

HASH 

JOIN 

14.|* 

2 

TABLE 

ACCESS 

FULL| 

T1 

72442 

59 

（6）| 

01 

15.| 

3 

T2 

49954 

536K| 

159 

（2）| 

02 

16.--------------------------------------------------------------------------- 

17. 

18.Predicate 

Information 

（identified 

by 

operation 

id）:

19.--------------------------------------------------- 

20. 

21. 

- 

access（"

T1"

."

OBJECT_ID"

="

T2"

） 

22. 

filter（"

OBJECT_NAME"

LIKE 

23. 

24.统计信息 

25.---------------------------------------------------------- 

26. 

recursive 

calls 

27. 

db 

block 

gets 

28. 

932 

consistent 

29. 

physical 

reads 

30. 

redo 

31. 

1352 

bytes 

sent 

via 

SQL*Net 

to 

client 

32. 

385 

received 

33. 

roundtrips 

to/from 

34. 

sorts 

（memory） 

35. 

（disk） 

36. 

29 

rows 

processed 

这里省略了SELECT出来的具体数据，但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。

首先从执行计划可以看到，这条SQL语句在2个表上都是全表扫描。

在第1个表T1上，有like‘%T1%’这样的条件，导致只能全表扫描，这没有问题。

但是第2个表，也是全表扫描，这里有没有问题呢？

或者说是有没有优化的余地，答案显然是肯定的。

这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1like‘%T1%’返回的结果行数为2498行，即T1表总行数的5%，如果2个表采用indexrangescan+nestedloop连接，oracle评估的成本会高于fulltablescan+hashjoin。

下面可以看到Oracle优化器评估的indexrange_scan+nestedloop的成本：

explain 

plan 

/*+ 

use_nl（t1 

t2） 

index（t2） 

*/ 

3. 

5. 

6.已解释。

7. 

8.SQL>

@showplan 

9. 

10.PLAN_TABLE_OUTPUT 

11.-------------------------------------------------------------------------------------- 

12.Plan 

3787413387 

13.-------------------------------------------------------------------------------------- 

14.| 

15.-------------------------------------------------------------------------------------- 

16.| 

5061 

（1）| 

01:

17.| 

BY 

INDEX 

ROWID| 

11 

（0）| 

18.| 

NESTED 

LOOPS 

19.|* 

FULL 

20.|* 

4 

RANGE 

SCAN 

T2_IDX 

21.-------------------------------------------------------------------------------------- 

22.Predicate 

23.--------------------------------------------------- 

24. 

25. 

从执行计划可以看到Oracle优化器评估的成本为5061，远远高于原来的219。

但是实际的逻辑读是多少呢？

1.统计信息 

2.---------------------------------------------------------- 

290 

11. 

13. 

加了HINT之后实际的逻辑读只有290，低于原始SQL的932。

所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nestloop的成本，最终也就选择了不是最优的执行计划。

下面我们用Oracle的SQLTuningAdvisor来尝试这条SQL：

var 

tuning_task 

varchar2（100）;

2.SQL>

DECLARE 

l_sql_id 

v$session.prev_sql_id%TYPE;

l_tuning_task 

VARCHAR2（30）;

BEGIN 

5 

l_sql_id:

='

4zbqykx89yc8v'

;

6 

:

= 

dbms_sqltune.create_tuning_task（sql_id 

=>

l_sql_id）;

7 

tuning_task:

=l_tuning_task;

8 

dbms_sqltune.execute_tuning_task（l_tuning_task）;

9 

dbms_output.put_line（l_tuning_task）;

10 

END;

/ 

13.任务_74 

print 

tuning_task;

19.TUNING_TASK 

20.--------------------------------------------------------------------------------------------------------- 

21.任务_74 

23.SQL>

dbms_sqltune.report_tuning_task（:

tuning_task） 

FROM 

dual;

25.DBMS_SQLTUNE.REPORT_TUNING_TASK（:

TUNING_TASK） 

26.-------------------------------------------------------------------------------- 

27.GENERAL 

INFORMATION 

SECTION 

28.------------------------------------------------------------------------------- 

29.Tuning 

Task 

任务_74 

30.Tuning 

Owner 

TEST1 

31.Scope 

COMPREHENSIVE 

32.Time 

Limit（seconds） 

1800 

33.Completion 

Status 

COMPLETED 

34.Started 

at 

12/15/2010 

09:

56:

35.Completed 

36.Number 

of 

SQL 

Profile 

Findings 

37. 

38.------------------------------------------------------------------------------- 

39.Schema 

Name:

40.SQL 

ID 

4zbqykx89yc8v 

41.SQL 

Text 

42. 

t1.object_id=t2.object_id 

43. 

44.------------------------------------------------------------------------------- 

45.FINDINGS 

（1 

finding） 

46.------------------------------------------------------------------------------- 

47. 

48.1- 

Finding 

（see 

plans 

section 

below） 

49.-------------------------------------------------------- 

50. 

为此语句找到了性能 

51. 

52. 

Recommendation 

（estimated 

benefit:

46.62%） 

53. 

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

54. 

-考虑接受推荐的 

55. 

executedbms_sqltune.accept_sql_profile（task_name 

任务_74'

replace 

56. 

TRUE）;

57. 

58.------------------------------------------------------------------------------- 

59.EXPLAIN 

PLANS 

60.------------------------------------------------------------------------------- 

61. 

62.1- 

Original 

With 

Adjusted 

63.------------------------------ 

64.Plan 

65. 

66.--------------------------------------------------------------------------- 

67.| 

68.--------------------------------------------------------------------------- 

69.| 

1160 

70.|* 

71.|* 

841 

72.| 

73.--------------------------------------------------------------------------- 

74. 

75.Predicate 

76.--------------------------------------------------- 

77. 

78. 

79. 

80. 

81.2- 

Using 

82.-------------------- 

83.Plan 

84. 

85.-------------------------------------------------------------------------------------- 

86.| 

87.-------------------------------------------------------------------------------------- 

88.| 

117 

（3）| 

89.| 

90.| 

91. 

92.|* 

00