Sql profiles-->使用sql tuning生成sql profile
来源：互联网 发布：windows如何禁止更新 编辑：程序博客网 时间：2024/06/06 12:34
一. sqlprofile理解
sql profile可以为某一sql语句提供除了系统统计信息、对象(表和索引等)统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器选择更适合的执行计划。
使用sql profile的目的：
a. 锁定或者说是稳定执行计划
b. 在不能修改应用中的sql的情况下使sql语句按照执行的执行计划运行。
相较于outline，sql profile更容易生成、更改和控制，在对sql语句的支持上也做得更好，适用范围更广。
二. (测试)借助sql tuning advisor生成sql profile
1. 创建测试表，收集统计信息
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;表已创建。SQL> create table t2 as select * from dba_objects;表已创建。SQL> create index t2_idx on t2(object_id);索引已创建。SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');PL/SQL 过程已成功完成。SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');PL/SQL 过程已成功完成。
2. 执行测试sql，观察执行计划
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;36 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1838229974---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2500 | 100K| 415 (1)| 00:00:05 ||* 1 | HASH JOIN | | 2500 | 100K| 415 (1)| 00:00:05 ||* 2 | TABLE ACCESS FULL| T1 | 2500 | 75000 | 70 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| T2 | 86645 | 930K| 345 (1)| 00:00:05 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1484 consistent gets 1477 physical reads 0 redo size 2132 bytes sent via SQL*Net to client 545 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 36 rows processed可以看出，这条sql在两个表上都是全表扫描。在第一个表T1上，有 like '%T1%'这样的条件，导致只能全表扫描，这是ok的。但是第二个表也是全表扫描，为什么呢，重点在于id=1的那一列，oracle优化器评估T1 Like '%T1%'返回的结果为2500行，如果对第二个表采用nested loop+index range scan的方式，oracle评估的成本会高于full table scan+hash join3. 查看oraclee优化器评估的index range scan+nested loop的成本
SQL> explain plan for select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1022743391---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2500 | 100K| 5071 (1)| 00:01:01 || 1 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 || 2 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 ||* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 70 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL) 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")19 rows selected.可以看到，oracle优化器评估的成本为5071,远高于原来的415.4. 查看index range scan+nested loop实际的物理读
SQL> select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;36 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1022743391---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2500 | 100K| 5071 (1)| 00:01:01 || 1 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 || 2 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 ||* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 70 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL) 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 303 consistent gets 248 physical reads 0 redo size 2111 bytes sent via SQL*Net to client 545 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 36 rows processed加了hint之后，实际的逻辑读只有303，低于原始sql的1484。所以可以得出，由于oracle优化器过高的估计了T1表经过Like操作过滤后返回的行数，也就过高估计了nest loop的成本，导致最终选择了非最优的执行计划。5. 使用sql tuning advisor 来尝试优化这条sql
SQL> var tuning_task varchar2(100); SQL> DECLARE 2 l_sql_id v$session.prev_sql_id%TYPE; 3 l_tuning_task VARCHAR2(30); 4 BEGIN 5 l_sql_id:='4zbqykx89yc8v'; 6 l_tuning_task := 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; 11 /PL/SQL procedure successfully completed.SQL> print tuning_task; TUNING_TASK--------------------------------------------------------------------------------TASK_771查看sql tuning建议
SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name : TASK_771Tuning Task Owner : TESTWorkload Type : Single SQL StatementScope : COMPREHENSIVETime Limit(seconds): 1800Completion Status : COMPLETEDStarted at : 03/23/2017 19:31:14Completed at : 03/23/2017 19:31:21-------------------------------------------------------------------------------Schema Name: TESTSQL ID : 4zbqykx89yc8vSQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id-------------------------------------------------------------------------------FINDINGS SECTION (1 finding)-------------------------------------------------------------------------------1- SQL Profile Finding (see explain plans section below)-------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 79.89%) ------------------------------------------ - Consider accepting the recommended SQL profile. #考虑接受推荐的sql execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_771', task_owner => 'TEST', replace => TRUE); Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .089893 .03349 62.74 % CPU Time (s): .044293 .031795 28.21 % User I/O Time (s): .039801 0 100 % Buffer Gets: 1484 296 80.05 % Physical Read Requests: 19 0 100 % Physical Write Requests: 0 0 Physical Read Bytes: 901120 0 100 % Physical Write Bytes: 0 0 Rows Processed: 36 36 Fetches: 36 36 Executions: 1 1 Notes ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions.-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original With Adjusted Cost------------------------------Plan hash value: 1838229974---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 36 | 1476 | 415 (1)| 00:00:05 ||* 1 | HASH JOIN | | 36 | 1476 | 415 (1)| 00:00:05 ||* 2 | TABLE ACCESS FULL| T1 | 36 | 1080 | 70 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| T2 | 86645 | 930K| 345 (1)| 00:00:05 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)2- Using SQL Profile--------------------Plan hash value: 1022743391---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 36 | 1476 | 142 (0)| 00:00:02 || 1 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 || 2 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 ||* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 70 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL) 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")-------------------------------------------------------------------------------sql tuning advisor找到了理想的执行计划，T1表上经过Like过滤后返回的行数估为36，比较准确。6. accept sql profile
SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);PL/SQL procedure successfully completed.再次查看原sql的执行计划
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;36 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1022743391---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 36 | 1476 | 142 (0)| 00:00:02 || 1 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 || 2 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 ||* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 70 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL) 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Note----- - SQL profile "SYS_SQLPROF_015afaf3c7f80000" used for this statementStatistics---------------------------------------------------------- 0 recursive calls 0 db block gets 305 consistent gets 0 physical reads 0 redo size 2132 bytes sent via SQL*Net to client 545 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 36 rows processed7. sql profile不光适用于一样的sql，对其他类似的sql也能生效
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;57 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1022743391---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 36 | 1476 | 142 (0)| 00:00:02 || 1 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 || 2 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 ||* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 70 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS NOT NULL) 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Note----- - SQL profile "SYS_SQLPROF_015afaf3c7f80000" used for this statementStatistics---------------------------------------------------------- 1 recursive calls 0 db block gets 316 consistent gets 9 physical reads 0 redo size 2932 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 57 rows processed从执行计划的note信息也可以看出sql采用了刚才的sql profile8. 查询生成的sql profile
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles; NAME CATEGORY SIGNATURE TYPE STATUS FOR------------------------------ ------------------------------ ---------- ------- -------- ---SYS_SQLPROF_015afaf3c7f80000 DEFAULT 3.9607E+18 MANUAL ENABLED YESsql profile的实际上就是一些hints，与outlines没有本质上的区别，只是sql profle中的hint没有指定sql使用哪个索引，也没有指定表的连接方法和连接顺序。在此例中，sql profile只是指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数，即36/2500，即是告诉优化器,T1经过Like后返回的行数应为评估的36/2500。
所以sql profile不会锁定sql的执行计划，只是提供了更多，更准确的统计信息给优化器。
9. 改变T1表的统计信息，观察原sql的执行计划
SQL> exec dbms_stats.set_table_stats('TEST','T1',numrows=>5000000); PL/SQL procedure successfully completed.SQL> set autot traceonlySQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;36 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1838229974---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3600 | 144K| 452 (9)| 00:00:06 ||* 1 | HASH JOIN | | 3600 | 144K| 452 (9)| 00:00:06 ||* 2 | TABLE ACCESS FULL| T1 | 3600 | 105K| 107 (35)| 00:00:02 || 3 | TABLE ACCESS FULL| T2 | 86645 | 930K| 345 (1)| 00:00:05 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)Note----- - SQL profile "SYS_SQLPROF_015afaf3c7f80000" used for this statementStatistics---------------------------------------------------------- 0 recursive calls 0 db block gets 305 consistent gets 0 physical reads 0 redo size 2132 bytes sent via SQL*Net to client 545 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 36 rows processed这里优化器依然采用了sql profile，但根据新的统计信息评估的返回结果数为3600行。执行计划又变回了full scan+hash join。可以看到，虽然sql profile起作用了，但是并没有锁定执行计划。 0 0
- Sql profiles-->使用sql tuning生成sql profile
- SQL：addm+sql tuning advisor+sql profile
- 使用SQL Profile及SQL Tuning Advisor固定执行计划
- Sql profiles-->手工创建sql profile
- SQL Profiles-PartIII -- profile的控制
- SQL Profiles
- SQL PROFILES
- SQL Tuning
- SQL Tuning
- SQL Tuning
- SQL Tuning Advisor使用实例
- SQL Tuning Advisor使用实例
- SQL Tuning Advisor使用实例
- SQL Profile使用1
- sql profile使用
- Oracle SQL Profile使用
- 053-3 While tuning a SQLstatement, the SQL Tuning Advisor finds an existing SQL profile for
- MySQL SQL Tuning：Profile定位单条QUERY性能瓶颈
- 3-4进行反向迭代以及实现反向迭代
- c++ 容器vector的基本操作
- 给出每个站点之间的最短距离，求出最短路径，用unordered_map来实现，让你实现find_cheapest_transform函数
- Glide源码分析（一）——DiskLruCache磁盘缓存的实现
- 2013第四届蓝桥杯预赛试题本科c++前缀判断
- Sql profiles-->使用sql tuning生成sql profile
- LeetCode:535. Encode and Decode TinyURL
- iptables详解
- 3-5对迭代器做切片操作
- 带头结点的双向循环链表
- STl的set容器的总结
- 深入.net第四章
- 3-6在一个for语句中迭代多个可迭代对象
- java 文件操作（二）---Files和Path
