一. 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 join
3. 查看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)| Tim
e |
--------------------------------------------------------------------------------
-------
| 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_771
Tuning Task Owner : TEST
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/23/2017 19:31:14
Completed at : 03/23/2017 19:31:21
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : 4zbqykx89yc8v
SQL 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)| Tim
e |
--------------------------------------------------------------------------------
-------
| 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)| Tim
e |
--------------------------------------------------------------------------------
-------
| 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 statement
Statistics
----------------------------------------------------------
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
7. 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)| Tim
e |
--------------------------------------------------------------------------------
-------
| 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 statement
Statistics
----------------------------------------------------------
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 profile
8. 查询生成的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 YES
sql 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 traceonly
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 | | 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 statement
Statistics
----------------------------------------------------------
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起作用了,但是并没有锁定执行计划。