Oracle深度学习笔记SQL基线

　26.Oracle 深度学习笔记 ——SQL 基线 1. SQL 计划基线

　SQL 计划基线可以理解是存储提纲的一个改进版本，不仅和存储提纲有许多相同的特性，而且也和存储提纲一样被设计用来提供稳定的执行计划，以防执行环境和对象统计信息的改变对执行计划产生影响。此外，和存储提纲类似，也可以在不修改语句的情况下调优应用程序。

　维持执行计划的稳定性是 SQL 计划基线唯一被提及的用途．

　 SQL 计划基线是一个与 SQL 语句相关联的对象，它被设计用来影响查询优化器产生执行计划时的决定。具体地讲，SQL 计划基线主要是一个提示的集合。基本上，SQL 计划基线就是用来迫使查询优化器为一条给定的 SQL 语句产生个特定的、稳定的执行计划。

　有多种方法可用来捕获 SQL 计划基线。基本上，它们都是由数据库引擎自动创建或数据库管理员手动创建。

　2. 基线自动捕获

　当动态初始化参数 optimizer_use_sql_plan_baselines 设置为 true 的时候，查询优化器将自动创建一个新的 SQL 计划基线。这个初始化参数被默认设置为 FALSE，可以在系统级和会话级修改它。当自动捕获开启后，查询优化器为每条重复执行过（就是至少执行过两次）的SQL 语句存储一个新的 SQL 计划基线。为此．它会将每条 SQL 语句的签名插入一个日志中，以便于管理。

　这意味着当一条 SQL 语句第一次执行的时候，仅把它的签名插入日志。然后，当第二次执行相同的语句的时候，如果不存在与此语句相对应的 SQL 计划基线，就新建一个并存储起来。如果与 SQL 语句相对应的 SQL 计划基线已经存在，查询优化器仍然会对比当前的执行计划和基于此 SQL 计划基线的执行计划。如果它们不匹配，那么这个描述当前执行计划的新的 SQL 计划基线将被存储。然而就像你在前面见到的，不能直接使用当前的执行计划。查询优化器被强制使用在 SQL 计划基线的辅助下产生的执行计划。

　计划基线用来指导优化器始终选择某一个执行计划。通过计划基线，可以将执行计划存储在数据库的表中并进行管理。计划基线由一个或多个已经被接受到的 SQL 查询执行计划组成。运行一个查询，且该查询已经存在计划基线，优化器就会优先考虑计划基线中的执行计划。

　3. 测试 用 用 SQL_ID 创建基线 tpcc@TOADDB> create table t as select * from all_objects;

　Table created. tpcc@TOADDB> create index t_idx on t(object_name); Index created. 收集统计信息：

　tpcc@TOADDB> exec dbms_stats.gather_table_stats(user,"t",cascade=>true); PL/SQL procedure successfully completed. tpcc@TOADDB> select /*test_01*/object_id,object_type from t where object_name ="DUAL";

　OBJECT_ID OBJECT_TYPE ---------- -----------------------

　142 TABLE

　143 SYNONYM 查找所执行 SQL 的 ID tpcc@TOADDB> select sql_id,sql_text from v$sql where sql_text like "select /*test_01*/%"; SQL_ID ------------- SQL_TEXT ---------------------------------------------------------------------------------------------------- farns9fxz7dum select /*test_01*/object_id,object_type from t where object_name ="DUAL" SQL 计划基线的管理，需要拥有 administer sql management object 权限。

　查看已有基线：

　SQL>select 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 => "farns9fxz7dum");

　 end;

　 / 再查看创建的基线如下：

　tpcc@TOADDB> select sql_handle,plan_name from dba_sql_plan_baselines where sql_text like "select /*test_01*/%"; SQL_HANDLE ---------------------------------------------------------------------------------------------------- PLAN_NAME ---------------------------------------------------------------------------------------------------- SQL_718c094fd7a20a4d SQL_PLAN_733099zbu42kda0b930be 如此就为需要创建计划基线的 SQL 创建了计划基线

　用 用 sql 文本来创建计划基线 如下 tpcc@TOADDB>select object_id from t where object_name = "DUAL";

　OBJECT_ID ----------

　142

　143 declare

　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. 查看如下：

　tpcc@TOADDB> select sql_handle,plan_name from dba_sql_plan_baselines where sql_text like "select object_id from t%"; SQL_HANDLE ---------------------------------------------------------------------------------------------------- PLAN_NAME ---------------------------------------------------------------------------------------------------- SQL_b7598beb2a522d9d SQL_PLAN_bfqcbxcp54bcxa0b930be 条 显示一条 SQL 的执行计划 我们可以使用 dbms_xplan.display_sql_plan_baseline 来完成，如下：

　tpcc@TOADDB> select sql_handle

　 from dba_sql_plan_baselines

　 where plan_name = "SQL_PLAN_bfqcbxcp54bcxa0b930be"

　 / SQL_HANDLE ---------------------------------------------------------------------------------------------------- SQL_b7598beb2a522d9d tpcc@TOADDB> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => "SQL_b7598beb2a522d9d")); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------

　-------------------------------------------------------------------------------- SQL handle: SQL_b7598beb2a522d9d SQL text: select object_id from t where object_name = "DUAL" -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_bfqcbxcp54bcxa0b930be

　 Plan id: 2696491198 Enabled: YES

　Fixed: NO Accepted: YES

　 Origin: MANUAL-LOAD Plan rows: From dictionary -------------------------------------------------------------------------------- Plan hash value: 767293772 --------------------------------------------------------------------------------------------- | 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 |

　 |

　 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,

　baseline_name => "temp_baseline_name"); end; / 快照点，我们可以使用 dba_hist_snapshot 视图查找 如下：

　tpcc@TOADDB> select snap_id from dba_hist_snapshot;

　SNAP_ID ----------

　78

　79

　 92

　93

　82

　95

　76

　77

　86

　83

　84

　85

　88

　89

　94

　73

　80

　81

　87

　90

　91

　74

　75 23 rows selected. 然后执行如下，创建 AWR 基线：

　begin

　dbms_workload_repository.create_baseline( start_snap_id =>91, end_snap_id => 92, baseline_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; begin

　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 创建计划基线。

　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 后的执行计划。使用 SQL 计划基线是一个不错的选择。

　测试如下：

　准备表 如下：

　tpcc@TOADDB>

　drop table t purge; Table dropped. tpcc@TOADDB> create table t as select * from all_objects; Table created. tpcc@TOADDB>

　create index t_idx on t(object_name); Index created. tpcc@TOADDB> exec dbms_stats.gather_table_stats(user,"t",cascade=>true); PL/SQL procedure successfully completed. variable name varchar2(30) exec :name := "DUAL"

　PL/SQL procedure successfully completed. 行 执行 SQL

　tpcc@TOADDB> select count(*) from t where object_name = :name;

　 COUNT(*) ----------

　 2 常看刚执行 SQL 的 SQL_ID tpcc@TOADDB> Select sql_id, child_number from

　v$sql

　where sql_text like "select count(*) from t where object_name = :name"; SQL_ID

　 CHILD_NUMBER ------------- ------------ astw879f24195

　 0 显示执行计划：

　tpcc@TOADDB> select * from table(dbms_xplan.display_cursor(sql_id=>"astw879f24195")); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID astw879f24195, child number 0 ------------------------------------- select count(*) from t where object_name = :name Plan hash value: 293504097 --------------------------------------------------------------------------- | Id

　| Operation

　 | Name

　| Rows

　| Bytes | Cost (%CPU)| 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 执行 如下：

　tpcc@TOADDB> select /*+full(t)*/ count(*) from t where object_name = :name;

　 COUNT(*) ----------

　 2 查看执行计划的 SQL_ID 如下：

　tpcc@TOADDB> 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 查看执行计划：

　tpcc@TOADDB> select * from table(dbms_xplan.display_cursor(sql_id=>"9rsq8360s27zu")); PLAN_TABLE_OUTPUT ---------...