SQL ProfilesPart I.docx
《SQL ProfilesPart I.docx》由会员分享，可在线阅读，更多相关《SQL ProfilesPart I.docx（20页珍藏版）》请在冰豆网上搜索。
SQLProfilesPartI
SQLProfiles-PartI
在这里向大家介绍SQLProfiles，就是希望能够了解Oracle数据库的这一功能。
SQLProfiles可以说是Outlines的进化。
Outlines能够实现的功能SQLProfiles也完全能够实现，而SQLProfiles具有Outlines不具备的优化，个人认为最重要的有2点：
∙SQLProfiles更容易生成、更改和控制。
∙SQLProfiles在对SQL语句的支持上做得更好，也就是适用范围更广。
关于这2方面的优点，我后面会详细地阐述。
现在我在使用Outlines的场合，均使用SQLProfiles来替代。
有一次准备对1条SQL语句使用Outline进行执行计划的稳定，结果使用Outline之后，系统出现大量的librarycachelatch的争用，不得不关闭Outline的使用，但是改用SQLProfiles不再有这个问题。
这或许是个BUG，不过既然能用SQLProfiles代替，也就没再深入去研究这个问题。
使用SQLProfiles无非是两个目的：
∙锁定或者说是稳定执行计划。
∙在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。
那么SQLProfile到底是什么？
在我看来，SQLProfile就是为某一SQL语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划。
这些说法显得比较枯燥，还是来看看下面的测试。
首先建2个测试表：
viewplaincopytoclipboardprint?
1.SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
2.
3.表已创建。
4.
5.SQL> create table t2 as select * from dba_objects;
6.
7.表已创建。
8.
9.SQL> create 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> exec dbms_stats.gather_table_stats（user,'t2',cascade=>true,method_opt=>'for all columns size 1'）;
18.
19.PL/SQL 过程已成功完成。
然后看看下面这一条SQL：
1.SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
2.
3.已选择29行。
4.
5.执行计划
6.----------------------------------------------------------
7.Plan hash value:
1838229974
8.
9.---------------------------------------------------------------------------
10.| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
11.---------------------------------------------------------------------------
12.| 0 | SELECT STATEMENT | | 2498 | 99920 | 219 （4）| 00:
00:
03 |
13.|* 1 | HASH JOIN | | 2498 | 99920 | 219 （4）| 00:
00:
03 |
14.|* 2 | TABLE ACCESS FULL| T1 | 2498 | 72442 | 59 （6）| 00:
00:
01 |
15.| 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159
（2）| 00:
00:
02 |
16.---------------------------------------------------------------------------
17.
18.Predicate Information （identified by operation id）:
19.---------------------------------------------------
20.
21. 1 - access（"T1"."OBJECT_ID"="T2"."OBJECT_ID"）
22. 2 - filter（"T1"."OBJECT_NAME" LIKE '%T1%'）
23.
24.统计信息
25.----------------------------------------------------------
26. 0 recursive calls
27. 0 db block gets
28. 932 consistent gets
29. 0 physical reads
30. 0 redo size
31. 1352 bytes sent via SQL*Net to client
32. 385 bytes received via SQL*Net from client
33. 2 SQL*Net roundtrips to/from client
34. 0 sorts （memory）
35. 0 sorts （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的成本：
1.SQL> explain plan for select /*+ use_nl（t1 t2） index（t2） */ t1.*,t2.owner
2. from t1,t2
3. where t1.object_name like '%T1%'
4. and t1.object_id=t2.object_id;
5.
6.已解释。
7.
8.SQL> @showplan
9.
10.PLAN_TABLE_OUTPUT
11.--------------------------------------------------------------------------------------
12.Plan hash value:
3787413387
13.--------------------------------------------------------------------------------------
14.| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
15.--------------------------------------------------------------------------------------
16.| 0 | SELECT STATEMENT | | 2498 | 99920 | 5061
（1）| 00:
01:
01 |
17.| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 （0）| 00:
00:
01 |
18.| 2 | NESTED LOOPS | | 2498 | 99920 | 5061
（1）| 00:
01:
01 |
19.|* 3 | TABLE ACCESS FULL | T1 | 2498 | 72442 | 59 （6）| 00:
00:
01 |
20.|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 （0）| 00:
00:
01 |
21.--------------------------------------------------------------------------------------
22.Predicate Information （identified by operation id）:
23.---------------------------------------------------
24. 3 - filter（"T1"."OBJECT_NAME" LIKE '%T1%'）
25. 4 - access（"T1"."OBJECT_ID"="T2"."OBJECT_ID"）
从执行计划可以看到Oracle优化器评估的成本为5061，远远高于原来的219。
但是实际的逻辑读是多少呢？
1.统计信息
2.----------------------------------------------------------
3. 0 recursive calls
4. 0 db block gets
5. 290 consistent gets
6. 0 physical reads
7. 0 redo size
8. 1352 bytes sent via SQL*Net to client
9. 385 bytes received via SQL*Net from client
10. 2 SQL*Net roundtrips to/from client
11. 0 sorts （memory）
12. 0 sorts （disk）
13. 29 rows processed
加了HINT之后实际的逻辑读只有290，低于原始SQL的932。
所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nestloop的成本，最终也就选择了不是最优的执行计划。
下面我们用Oracle的SQLTuningAdvisor来尝试这条SQL：
1.SQL> var tuning_task varchar2（100）;
2.SQL> DECLARE
3. 2 l_sql_id v$session.prev_sql_id%TYPE;
4. 3 l_tuning_task VARCHAR2（30）;
5. 4 BEGIN
6. 5 l_sql_id:
='4zbqykx89yc8v';
7. 6 l_tuning_task :
= dbms_sqltune.create_tuning_task（sql_id => l_sql_id）;
8. 7 :
tuning_task:
=l_tuning_task;
9. 8 dbms_sqltune.execute_tuning_task（l_tuning_task）;
10. 9 dbms_output.put_line（l_tuning_task）;
11. 10 END;
12. 11 /
13.任务_74
14.
15.PL/SQL 过程已成功完成。
16.
17.SQL> print tuning_task;
18.
19.TUNING_TASK
20.---------------------------------------------------------------------------------------------------------
21.任务_74
22.
23.SQL> SELECT dbms_sqltune.report_tuning_task（:
tuning_task） FROM dual;
24.
25.DBMS_SQLTUNE.REPORT_TUNING_TASK（:
TUNING_TASK）
26.--------------------------------------------------------------------------------
27.GENERAL INFORMATION SECTION
28.-------------------------------------------------------------------------------
29.Tuning Task Name :
任务_74
30.Tuning Task Owner :
TEST1
31.Scope :
COMPREHENSIVE
32.Time Limit（seconds） :
1800
33.Completion Status :
COMPLETED
34.Started at :
12/15/2010 09:
56:
02
35.Completed at :
12/15/2010 09:
56:
03
36.Number of SQL Profile Findings :
1
37.
38.-------------------------------------------------------------------------------
39.Schema Name:
TEST1
40.SQL ID :
4zbqykx89yc8v
41.SQL Text :
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
42. and t1.object_id=t2.object_id
43.
44.-------------------------------------------------------------------------------
45.FINDINGS SECTION （1 finding）
46.-------------------------------------------------------------------------------
47.
48.1- SQL Profile Finding （see explain plans section below）
49.--------------------------------------------------------
50. 为此语句找到了性能
51.
52. Recommendation （estimated benefit:
46.62%）
53. ------------------------------------------
54. -考虑接受推荐的 SQL
55. executedbms_sqltune.accept_sql_profile（task_name => '任务_74', replace =
56. TRUE）;
57.
58.-------------------------------------------------------------------------------
59.EXPLAIN PLANS SECTION
60.-------------------------------------------------------------------------------
61.
62.1- Original With Adjusted Cost
63.------------------------------
64.Plan hash value:
1838229974
65.
66.---------------------------------------------------------------------------
67.| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
68.---------------------------------------------------------------------------
69.| 0 | SELECT STATEMENT | | 29 | 1160 | 219 （4）| 00:
00:
03 |
70.|* 1 | HASH JOIN | | 29 | 1160 | 219 （4）| 00:
00:
03 |
71.|* 2 | TABLE ACCESS FULL| T1 | 29 | 841 | 59 （6）| 00:
00:
01 |
72.| 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159
（2）| 00:
00:
02 |
73.---------------------------------------------------------------------------
74.
75.Predicate Information （identified by operation id）:
76.---------------------------------------------------
77.
78. 1 - access（"T1"."OBJECT_ID"="T2"."OBJECT_ID"）
79. 2 - filter（"T1"."OBJECT_NAME" LIKE '%T1%'）
80.
81.2- Using SQL Profile
82.--------------------
83.Plan hash value:
3787413387
84.
85.--------------------------------------------------------------------------------------
86.| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
87.--------------------------------------------------------------------------------------
88.| 0 | SELECT STATEMENT | | 29 | 1160 | 117 （3）| 00:
00:
02 |
89.| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 （0）| 00:
00:
01 |
90.| 2 | NESTED LOOPS | | 29 | 1160 | 117 （3）| 00:
00:
02
91. |
92.|* 3 | TABLE ACCESS FULL | T1 | 29 | 841 | 59 （6）| 00
升级会员 