Oracle深度学习笔记SQL基线

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 in dex t_idx on t(object_ name);In dex created.收集统计信息：tpccTOADDB> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);PL/SQL procedure successfully complete

5、d.tpccTOADDB> select /*test_01*/object_id,object_type from t where object_ name ='DUAL：OBJECT_ID OBJECT_TYPE142 TABLE143 SYNONYM查找所执行SQL的IDtpccTOADDB> select sql_id,sql_text from v$sql where sql_text like 'select /*test_O1*/%'SQL_IDSQL_TEXT farn s9fxz7dumselect /*test_01*/object_id

6、,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 加载 sqld 对应的 SQL的执行计

7、戈U。 declarex pls_ in teger;beginx := dbms_sp m.lo ad_pla ns_from_cursor_cache(sql_id => 'farn s9fxz7dum'); en d;/再查看创建的基线如下:tpccTOADDB> select sql_handle,plan_name from dba_sql_plan_baselines where sql_text like 'select /*test_01*/%'SQL_HANDLEPLAN_NAMESQL_718c094fd7a20a4dSQL_PL

8、AN_733099zbu42kda0b930be如此就为需要创建计划基线的SQL创建了计划基线用sql文本来创建计划基线如下tpccTOADDB>select object_id from t where object_name = 'DUAL'OBJECT_ID142143declarex pls_ in teger;beginx := dbms_sp m.lo ad_pla ns_from_cursor_cache(attribute_ name => 'SQL_TEXT', attribute_value => 'select o

9、bject_id from t%');dbms_output.put_li ne(x);en d;/PL/SQL procedure successfully completed.查看如下：tpccTOADDB> select sql_handle,plan_name from dba_sql_plan_baselines where sql_text like 'select object_id from t%'SQL_HANDLEPLAN_NAMESQL_b7598beb2a522d9dSQL_PLAN_bfqcbxcp54bcxa0b930be显示一条SQL

10、的执行计划我们可以使用 dbms_xplan.display_sql_plan_baseline 来完成，如下: tpccTOADDB> select sql_ha ndlefrom dba_sql_pla n_baseli neswhere plan_name = 'SQL_PLAN_bfqcbxcp54bcxa0b930be'/SQL_HANDLESQL_b7598beb2a522d9dtpccTOADDB> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'

11、SQL_b7598beb2a522d9d');PLAN_TABLE_OUTPUTSQL han die: SQL_b7598beb2a522d9dSQL text: select object_id from t where object_ name = 'DUAL'Plan name: SQL_PLAN_bfqcbxcp54bcxa0b930be Pla n id: 2696491198En abled: YES Fixed: NO Accepted: YES Origi n: MANUAL-LOADPla n rows: From dict ionaryPlan h

12、ash value: 767293772| Id | Operati on| 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 In formatio n (ide ntified by operati on id):2 - access("O

13、BJECT_NAME"='DUAL')26 rows selected.也可以输入plan_name，或是两者都输入。还有一个参数为format，来用决定所显示计划的详细信息，其值可以为baice，typical或all。4. 创建一个AWR基线通过如下命令来实现。begindbms_workload_repository.create_baseli ne( start_s nap_id => xxxxx, end_sn ap_id => yyyy,baseli ne_n ame => 'temp_baseli ne_n ame');

14、en d;/快照点，我们可以使用dba_hist_snapshot视图查找如下：tpccTOADDB> select sn ap_id from dba_hist_s napshot;SNAP_ID92938295767786838485888994738081879091747523 rows selected.然后执行如下，创建 AWR基线：begindbms_workload_repository.create_baseli ne( start_s nap_id =>91, en d_s nap_id => 92,baseli ne_n ame => 't

15、emp_baseli ne_n ame'); en d;/PL/SQL procedure successfully completed.创建一个SQL调优集。begindbms_sqlt un e.create_sqlset( sqlset_ name => 'temp_sqlset', description => 'sql tune set from awr');en d;/PL/SQL procedure successfully completed.SQL调优集。从awr基线中找到占较高资源的查询来填充declarebase_cur

16、 dbms_sqlt un e.sqlset_cursor;beginope n base_cur forselect value(x)from table(dbms_sqlt un e.select_workload_repository('temp_baseli ne_n ame', null, null,'elapsed_time',n ull, null, null,15) x;dbms_sqlt une.lo ad_sqlset(sqlset_ name => 'temp_sqlset',populate_cursor =>

17、 base_cur);en d;/PL/SQL procedure successfully completed.为SQL调优集中的每一个查询SQL创建计划基线。declarex pls_ in teger;beginx := dbms_sp m.lo ad_pla ns_from_sqlset(sqlset_ name => 'temp_sqlset');en d;/PL/SQL procedure successfully completed.这样就完成了调优集中查询SQL的计划基线的创建。5. SQL基线综合性使用很多的时候，优化器只选择一种执行计划，我们尝试使用h

18、i nt改变这个执行计划，且执行效率有很较大的提升。但是通常是不能够改变产品系统里面的SQL代码的。在这种情况下，会用到 SQL计划基线，在不改变 SQL的情况下让优化器选择我们加个hi nt后的执行计划。使用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_n

19、ame);In dex created.tpccTOADDB> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);PL/SQL procedure successfully completed.variable n ame varchar2(30) exec :n ame := 'DUAL'PL/SQL procedure successfully completed.执行SQLn ame = :n ame;COUNT(*)2常看刚执行SQL的SQL_IDtpccTOADDB>

20、 Select sqld, child_number from v$sql where sql_text like 'select count(*) from t where object_ name = :n ame'SQL_IDCHILD_NUMBERastw879f241950显示执行计划：tpccTOADDB> select * from table(dbms_xpla n.display_cursor(sql_id=>'astw879f24195');PLAN_TABLE_OUTPUTSQL_ID astw879f24195, child

21、number 0 select coun t(*) from t where object_ name = :n amePlan hash value: 293504097| Id | Operatio n | Name | Rows | Bytes | Cost (%CPU)| Time |0 | SELECT STATEMENT| | 3 (100)|1 | SORT AGGREGATE | 1 |25 | |50 |3(0)| 00:00:01 |*2 | INDEX RANGE SCAN| T_IDX |2 |Predicate In formatio n (ide ntified b

22、y operati on id):2 - access("OBJECT_NAME"=:NAME)19 rows selected.带HINT执行如下：tpccTOADDB> select /*+full(t)*/ cou nt(*) from t where object_ name = :n ame;COUNT(*)2查看执行计划的 SQL_ID如下：tpccTOADDB> select sql_id, child_number from v$sql where sql_text like 'select /*+full(t)*/ coun t(*)

23、from t where object_ name = :n ame：SQL_IDCHILD_NUMBER9rsq8360s27zu0查看执行计划：tpccTOADDB> select * from table(dbms_xpla n.display_cursor(sql_id=>'9rsq8360s27zu');PLAN_TABLE_OUTPUTSQL_ID 9rsq8360s27zu, child number 0 select /*+full(t)*/ coun t(*) from t where object_ name = :n amePlan hash

24、value: 2966233522| Id | Operatio n | Name | Rows | Bytes | Cost (%CPU)| Time|0 | SELECT STATEMENT |422 (100)|1 |SORT AGGREGATE | 1 |25 |*2 |TABLE ACCESS FULL| T | 2 |50 |422(1)| 00:00:01|Predicate In formatio n (ide ntified by operati on id):2 - filter("OBJECT_NAME"=:NAME)19 rows selected.

25、然后改变第一个执行SQL (未带HINT)的执行计划，让其走使用hint后的 SQL“ 9rsq8360s27z的执行计划，也就是让它走全表扫描创建两条SQL计划基线tpccTOADDB> Select sql_id,hash_value,child_ nu mber fromv$sql where sql_text like 'selectcoun t(*) from t where object_ name = :n ame'SQL_IDHASH_VALUE CHILD_NUMBERastw879f24195pccTOADDB> sel

26、ect sqld, hash_value,child_number from v$sql where sql_text like 'select /*+full(t)*/ coun t(*) from t where object_ name = :n ame'SQL_IDHASH_VALUE CHILD_NUMBER9rsq8360s27zu 21727231940创建SQL基线如下：declarex pls_ in teger;beginx := dbms_sp m.lo ad_pla ns_from_cursor_cache( sql_id => 'astw

27、879f24195', plan_hash_value => '293504097');dbms_output.put_li ne(x);en d;/第二条：declarex pls_ in teger;beginx := dbms_sp m.lo ad_pla ns_from_cursor_cache( sql_id => '9rsq8360s27zu', plan_hash_value => '2966233522');dbms_output.put_li ne(x);en d;/查看基线：pla n_n ame,

28、en abled, fixed, sql_ha ndle,sql_text from dba_sql_pla n_baseli neswhere 1 = 1 and sql_text like '%co un t(*) from t where object_ name = :n ame%'PLAN_NAMEENA FIXSQL_HANDLESQL_TEXTSQL_PLAN_7x3fcur5rkfgm3fdbb376YES NOSQL_7e8dccd5cb7939f3select /*+full(t)*/ coun t(*) from t where object_ name

29、= :n ameSQL_PLAN_4d8jvstgfrn4vded8ae2fYES NOSQL_46a23bc65eebd09bselect coun t(*) from t where object_ name = :n ame查看一下每个计划基线里的执行计划 table(dbms_xplan.display_sql_plan_baseline(sql_handle=>' SQL_7e8dccd5cb7939f3')SQL>selectfromPLAN_TABLE_OUTPUTSQL han dle: SQL_7e8dccd5cb7939f3SQL text: s

30、elect /*+full(t)*/ coun t(*) from t where object_ name = :n amePlan name: SQL_PLAN_7x3fcur5rkfgm3fdbb376Pla n id: 1071362934En abled: YES Fixed: NO Accepted: YES Origi n: MANUAL-LOAD Pla n rows: From dict ionaryPlan hash value: 2966233522| Id | Operati on| Name | Rows | Bytes | Cost (%CPU)| Time|0 |

31、 SELECT STATEMENT |422 (100)|1 | SORT AGGREGATE | 1 |25 |0(0)|*2 | TABLE ACCESS FULL| T | 2 |50 |422(1)| 00:00:01 |Predicate In formatio n (ide ntified by operati on id):2 - filter("OBJECT_NAME"=:NAME)26 rows selected.fromidle>select*table(dbms_xplan.display_sql_plan_baseline(sql_handle

32、=>' SQL_46a23bc65eebd09b');PLAN_TABLE_OUTPUTSQL han dle: SQL_46a23bc65eebd09bSQL text: select coun t(*) from t where object_ name = :n amePlan name: SQL_PLAN_4d8jvstgfrn4vded8ae2fPla n id: 3738742319En abled: YES Fixed: NO Accepted: YES Origi n: MANUAL-LOAD Pla n rows: From dict ionaryPla

33、n hash value: 293504097| Id | Operatio n | Name | Rows | Bytes | Cost (%CPU)| Time |0 | SELECT STATEMENT| | 3 (100)|1 | SORT AGGREGATE | 1 |25 | 0(0)|(0)| 00:00:01 |*2 | INDEX RANGE SCAN| T_IDX |2 |50 |3Predicate In formatio n (ide ntified by operati on id):2 - access("OBJECT_NAME"=:NAME)2

34、6 rows selected.改变的SQL信息SQL计划基把加hint后生成的SQL ID与Plan Hash Value (全表扫描)加载到走索引生成的 线当中。declarex pls_ in teger;beginx := dbms_sp m.lo ad_pla ns_from_cursor_cache(sql_id => '9rsq8360s27zu',plan_hash_value => '2966233522',sql_ha ndle => 'SQL_46a23bc65eebd09b');dbms_output.p

35、ut_li ne(x);en d;/PL/SQL procedure successfully completed.其中 SQL_46a23bc65eebd09b 是走索引的 SQL HANDLE 而 sqld => '9rsq8360s27zu',plan_hash_value => '2966233522',是走全表扫描的相关索引。执行之后原先走索引的SQL会走全表。这样的话SQL Han die为“ SQL_46a23bc65eebd09b勺计划基线有两份，因为刚才加载又生成了一份。 要把原来走索引的那个Pan Baseli ne删除或禁用掉，

36、保留全表扫描的那一个。查看如下：tpccTOADDB>select*table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_46a23bc65eebd09b');PLAN_TABLE_OUTPUT需fromSQL handle: SQL_46a23bc65eebd09bSQL text: select count(*) from t where object_name = :namePlan name: SQL_PLAN_4d8jvstgfrn4v3fdbb376 Plan id: 1071362934

37、Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD Plan rows: From dictionaryPlan hash value: 2966233522| Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time| 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | |*2 | TABLE ACCESS FULL| T | 2 |422 (100)|25 |0(0)|50 |422(1)| 00:00:01 |Pr

38、edicate Information (identified by operation id):2 - filter("OBJECT_NAME"=:NAME)Plan name: SQL_PLAN_4d8jvstgfrn4vded8ae2f Plan id: 3738742319Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD Plan rows: From dictionaryPlan hash value: 293504097| Id | Operatio n | Name | Rows | Bytes

39、| Cost (%CPU)| Time|0 | SELECT STATEMENT| | 3 (100)|1 | SORT AGGREGATE | 1 |25 |0(0)|(0)| 00:00:01 |*2 | INDEX RANGE SCAN| T_IDX |2 |50 |3Predicate In formatio n (ide ntified by operati on id):2 - access("OBJECT_NAME"=:NAME) 47 rows selected.删除原先的基线程序如下：declarex pls_ in teger;beginx := dbm

40、s_spm.drop_sql_pla n_baseli ne(plan_name => 'SQL_PLAN_4d8jvstgfrn4vded8ae2f,sql_ha ndle => 'SQL_46a23bc65eebd09b'end;/);执行查看tpccTOADDB> select coun t(*) from t where object_ name = :n ame;COUNT(*)Executi on PlanPlan hash value: 29662335222| Id | Operati on| Name | Rows | Bytes |

41、 Cost (%CPU)| Time|0 | SELECT STATEMENT | 1 |25 |422|1 | SORT AGGREGATE | | 1 |25 |(1)| 00:00:01 | |*2 | TABLE ACCESS FULL| T | 2 |50 |422(1)| 00:00:01 |Predicate In formatio n (ide ntified by operati on id):2 - filter("OBJECT_NAME"=:NAME)Note-SQL plan basel ine "SQL_PLAN_4d8jvstgfrn4

42、v3fdbb376" used for this stateme ntStatistics7 recursive calls0 db block gets1519 con siste nt gets0physical reads0redo size542 bytes sen t via SQL*Net to clie nt551bytes received via SQL*Netfrom clie nt2SQL*Net roun dtrips to/fromclie nt0sorts (memory)0sorts (disk)1rows processed走的是SQL基线了。6.基线维护查看基线一个查询已经有计划基线。然后新加了一个这个查询可以使用的索引。优化器确实这个查询现在有成本更低的新执行计划可用。并将这个新执行以未被接受的状态加入到计划历史中。通过SQL调优顾问给出的建议或者通过查询dba_sql_plan_baselines视图发现了新的计划。tpccTOADDB> selectpla n_n ame,sql_ha ndle,e nabled,accepted,optimizer_costfromdba_sql_plan_baselines where sql_