深度学习笔记SQL基线.docx

《深度学习笔记SQL基线.docx》由会员分享，可在线阅读，更多相关《深度学习笔记SQL基线.docx（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、26.Oracle深度学习笔记SQL基线1. SQL计划基线 SQL计划基线可以理解是存储提纲的一个改进版本，不仅和存储提纲有许多相同的特性，而且也和存储提纲一样被设计用来提供稳定的执行计划，以防执行环境和对象统计信息的改变对执行计划产生影响。此外，和存储提纲类似，也可以在不修改语句的情况下调优应用程序。维持执行计划的稳定性是SQL 计划基线唯一被提及的用途 SQL计划基线是一个与SQL语句相关联的对象，它被设计用来影响查询优化器产生执行计划时的决定。具体地讲，SQL计划基线主要是一个提示的集合。基本上，SQL计划基线就是用来迫使查询优化器为一条给定的SQL语句产生个特定的、稳定的执行计划。

2、有多种方法可用来捕获SQL计划基线。基本上，它们都是由数据库引擎自动创建或数据库管理员手动创建。2. 基线自动捕获 当动态初始化参数optimizer_use_sql_plan_baselines设置为true的时候，查询优化器将自动创建一个新的SQL计划基线。这个初始化参数被默认设置为FALSE，可以在系统级和会话级修改它。当自动捕获开启后，查询优化器为每条重复执行过（就是至少执行过两次）的SQL 语句存储一个新的SQL 计划基线。为此它会将每条SQL 语句的签名插入一个日志中，以便于管理。 这意味着当一条SQL语句第一次执行的时候，仅把它的签名插入日志。然后，当第二次执行相同的语句的时候，

3、如果不存在与此语句相对应的SQL计划基线，就新建一个并存储起来。如果与SQL语句相对应的SQL计划基线已经存在，查询优化器仍然会对比当前的执行计划和基于此SQL计划基线的执行计划。如果它们不匹配，那么这个描述当前执行计划的新的SQL计划基线将被存储。然而就像你在前面见到的，不能直接使用当前的执行计划。查询优化器被强制使用在SQL 计划基线的辅助下产生的执行计划。计划基线用来指导优化器始终选择某一个执行计划。通过计划基线，可以将执行计划存储在数据库的表中并进行管理。计划基线由一个或多个已经被接受到的SQL查询执行计划组成。运行一个查询，且该查询已经存在计划基线，优化器就会优先考虑计划基线中的执行

4、计划。3. 测试用SQL_ID创建基线tpccTOADDB create table t as select * from all_objects;Table created.tpccTOADDB create index t_idx on t(object_name);Index created.收集统计信息：tpccTOADDB exec dbms_stats.gather_table_stats(user,t,cascade=true);PL/SQL procedure successfully completed.tpccTOADDB select /*test_01*/object_

5、id,object_type from t where object_name =DUAL; OBJECT_ID OBJECT_TYPE- - 142 TABLE 143 SYNONYM查找所执行SQL的IDtpccTOADDB select sql_id,sql_text from v$sql where sql_text like select /*test_01*/%;SQL_ID-SQL_TEXT-farns9fxz7dumselect /*test_01*/object_id,object_type from t where object_name =DUALSQL计划基线的管理，需

6、要拥有administer sql management object权限。查看已有基线：SQLselect sql_handle,plan_name from dba_sql_plan_baselines where sql_text like select /*test_01*/%;no rows selected使用dbms_spm.load_plans_from_cursor_cache加载sql_id对应的SQL的执行计划。declare x pls_integer; begin x := dbms_spm.load_plans_from_cursor_cache(sql_id =

7、farns9fxz7dum); end; /再查看创建的基线如下：tpccTOADDB select sql_handle,plan_name from dba_sql_plan_baselines where sql_text like select /*test_01*/%;SQL_HANDLE-PLAN_NAME-SQL_718c094fd7a20a4dSQL_PLAN_zbu42kda0b930be如此就为需要创建计划基线的SQL创建了计划基线用sql文本来创建计划基线如下tpccTOADDBselect object_id from t where object_name = DUA

8、L; OBJECT_ID- 142 143declare x pls_integer; begin x := dbms_spm.load_plans_from_cursor_cache( attribute_name = SQL_TEXT, attribute_value = select object_id from t%); dbms_output.put_line(x); end; /PL/SQL procedure successfully completed.查看如下：tpccTOADDB select sql_handle,plan_name from dba_sql_plan_b

9、aselines where sql_text like select object_id from t%;SQL_HANDLE-PLAN_NAME-SQL_b7598beb2a522d9dSQL_PLAN_bfqcbxcp54bcxa0b930be显示一条SQL的执行计划我们可以使用dbms_xplan.display_sql_plan_baseline来完成，如下：tpccTOADDB select sql_handle from dba_sql_plan_baselines where plan_name = SQL_PLAN_bfqcbxcp54bcxa0b930be /SQL_HAN

10、DLE-SQL_b7598beb2a522d9dtpccTOADDB select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle = SQL_b7598beb2a522d9d);PLAN_TABLE_OUTPUT-SQL handle: SQL_b7598beb2a522d9dSQL text: select object_id from t where object_name = DUAL-Plan name: SQL_PLAN_bfqcbxcp54bcxa0b930be Plan id: Enabled: YES

11、Fixed: NOAccepted: YES Origin: MANUAL-LOADPlan rows: From dictionary-Plan hash value: -| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | | | 4 (100)| | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 2 | 60 | 4 (0)| 00:00:01 |* 2 | INDEX RANGE SCAN | T_IDX | 2 | |

12、3 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 2 - access(OBJECT_NAME=DUAL)26 rows selected.也可以输入plan_name，或是两者都输入。还有一个参数为format，来用决定所显示计划的详细信息，其值可以为baice，typical或all。4. 创建一个AWR基线通过如下命令来实现。begin dbms_workload_repository.create_baseline(start_snap_id = xxxxx,end_snap_id = yyyy

13、,baseline_name = temp_baseline_name);end;/快照点，我们可以使用dba_hist_snapshot视图查找如下：tpccTOADDB select snap_id from dba_hist_snapshot; SNAP_ID-787992938295767786838485888994738081879091747523 rows selected.然后执行如下，创建AWR基线：begin dbms_workload_repository.create_baseline(start_snap_id =91,end_snap_id = 92,baseli

14、ne_name = temp_baseline_name);end;/PL/SQL procedure successfully completed.创建一个SQL调优集。begin dbms_sqltune.create_sqlset(sqlset_name = temp_sqlset,description = sql tune set from awr);end;/PL/SQL procedure successfully completed.从awr基线中找到占较高资源的查询来填充SQL调优集。declare base_cur dbms_sqltune.sqlset_cursor;be

15、gin open base_cur for select value(x) from table(dbms_sqltune.select_workload_repository(temp_baseline_name,null,null,elapsed_time,null,null,null,15) x; dbms_sqltune.load_sqlset(sqlset_name = temp_sqlset,populate_cursor = base_cur);end;/PL/SQL procedure successfully completed.为SQL调优集中的每一个查询SQL创建计划基线

16、。declare x pls_integer;begin x := dbms_spm.load_plans_from_sqlset(sqlset_name = temp_sqlset);end;/PL/SQL procedure successfully completed.这样就完成了调优集中查询SQL的计划基线的创建。5. SQL基线综合性使用很多的时候，优化器只选择一种执行计划，我们尝试使用hint改变这个执行计划，且执行效率有很较大的提升。但是通常是不能够改变产品系统里面的SQL代码的。在这种情况下，会用到SQL 计划基线，在不改变SQL的情况下让优化器选择我们加个hint后的执行计划

17、。使用SQL计划基线是一个不错的选择。测试如下：准备表如下：tpccTOADDB drop table t purge;Table dropped.tpccTOADDB create table t as select * from all_objects;Table created.tpccTOADDB create index t_idx on t(object_name);Index created.tpccTOADDB exec dbms_stats.gather_table_stats(user,t,cascade=true);PL/SQL procedure successfull

18、y completed.variable name varchar2(30)exec :name := DUALPL/SQL procedure successfully completed.执行SQLtpccTOADDB select count(*) from t where object_name = :name; COUNT(*)- 2常看刚执行SQL的SQL_IDtpccTOADDB Select sql_id, child_number from v$sql where sql_text like select count(*) from t where object_name =

19、 :name;SQL_ID CHILD_NUMBER- -astw879f24195 0显示执行计划：tpccTOADDB select * from table(dbms_xplan.display_cursor(sql_id=astw879f24195);PLAN_TABLE_OUTPUT-SQL_IDastw879f24195, child number 0-select count(*) from t where object_name = :namePlan hash value: -| Id | Operation | Name | Rows | Bytes | Cost (%CP

20、U)| Time |-| 0 | SELECT STATEMENT | | | |3 (100)| | 1 | SORT AGGREGATE | |1 | 25 | | |* 2 | INDEX RANGE SCAN| T_IDX |2 | 50 |3 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 2 - access(OBJECT_NAME=:NAME)19 rows selected.带HINT执行如下：tpccTOADDB select /*+full(t)*/ count(*) from t w

21、here object_name = :name; COUNT(*)- 2查看执行计划的SQL_ID如下：tpccTOADDB select sql_id, child_number from v$sql where sql_text like select /*+full(t)*/ count(*) from t where object_name = :name;SQL_ID CHILD_NUMBER- -9rsq8360s27zu 0查看执行计划：tpccTOADDB select * from table(dbms_xplan.display_cursor(sql_id=9rsq836

22、0s27zu);PLAN_TABLE_OUTPUT-SQL_ID9rsq8360s27zu, child number 0-select /*+full(t)*/ count(*) from t where object_name = :namePlan hash value: -| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | | | 422 (100)| | 1 | SORT AGGREGATE | |1 | 25 | | |* 2 | TABLE ACCESS F

23、ULL| T |2 | 50 | 422 (1)| 00:00:01 |-Predicate Information (identified by operation id):- 2 - filter(OBJECT_NAME=:NAME)19 rows selected.然后改变第一个执行SQL（未带HINT）的执行计划，让其走使用hint后的SQL“9rsq8360s27zu”的执行计划，也就是让它走全表扫描创建两条SQL计划基线tpccTOADDB Select sql_id,hash_value,child_number from v$sql where sql_text like se

24、lect count(*) from t where object_name = :name;SQL_ID HASH_VALUE CHILD_NUMBER- - -astw879f24195 0tpccTOADDB select sql_id, hash_value,child_number from v$sql where sql_text like select /*+full(t)*/ count(*) from t where object_name = :name;SQL_ID HASH_VALUE CHILD_NUMBER- - -9rsq8360s27zu 0创建SQL基线如下：

25、declare x pls_integer; begin x := dbms_spm.load_plans_from_cursor_cache( sql_id = astw879f24195, plan_hash_value = ); dbms_output.put_line(x); end; /第二条：declare x pls_integer; begin x := dbms_spm.load_plans_from_cursor_cache( sql_id = 9rsq8360s27zu, plan_hash_value = ); dbms_output.put_line(x); end;

26、 /查看基线：SQLselect plan_name, enabled, fixed, sql_handle,sql_text from dba_sql_plan_baselines where 1 = 1 and sql_text like %count(*) from t where object_name = :name%;PLAN_NAME-ENA FIX- -SQL_HANDLE-SQL_TEXT-SQL_PLAN_7x3fcur5rkfgm3fdbb376YES NOSQL_7e8dccd5cb7939f3select /*+full(t)*/ count(*) from t wh

27、ere object_name = :nameSQL_PLAN_4d8jvstgfrn4vded8ae2fYES NOSQL_46a23bc65eebd09bselect count(*) from t where object_name = :name查看一下每个计划基线里的执行计划SQL select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=SQL_7e8dccd5cb7939f3);PLAN_TABLE_OUTPUT-SQL handle: SQL_7e8dccd5cb7939f3SQL text: sel

28、ect /*+full(t)*/ count(*) from t where object_name = :name-Plan name: SQL_PLAN_7x3fcur5rkfgm3fdbb376 Plan id: Enabled: YES Fixed: NOAccepted: YES Origin: MANUAL-LOADPlan rows: From dictionary-Plan hash value: -| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | |

29、| 422 (100)| | 1 | SORT AGGREGATE | |1 | 25 |0 (0)| |* 2 | TABLE ACCESS FULL| T |2 | 50 | 422 (1)| 00:00:01 |-Predicate Information (identified by operation id):- 2 - filter(OBJECT_NAME=:NAME)26 rows selected.idle select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=SQL_46a23bc65eebd0

30、9b);PLAN_TABLE_OUTPUT-SQL handle: SQL_46a23bc65eebd09bSQL text: select count(*) from t where object_name = :name-Plan name: SQL_PLAN_4d8jvstgfrn4vded8ae2f Plan id: Enabled: YES Fixed: NOAccepted: YES Origin: MANUAL-LOADPlan rows: From dictionary-Plan hash value: -| Id | Operation | Name | Rows | Byt

31、es | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | | |3 (100)| | 1 | SORT AGGREGATE | |1 | 25 |0 (0)| |* 2 | INDEX RANGE SCAN| T_IDX |2 | 50 |3 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 2 - access(OBJECT_NAME=:NAME)26 rows selected.改变的SQL信息把加hint后生成的SQL ID与 Plan Hash Value（全表扫描）加载到走索引生成的SQL计划基线当中。declare x pls_integer;