SQL Tuning Advisor(STA)到底做了什么(四)
软解析
--字面量与绑定变量
select * from scott.emp where ename='SCOTT'; --SCOTT即是字面量
select * from scott.emp where ename=:b1; --b1即是绑定变量
--上面的查询结果为no，也就是说此时使用的是缺省值false.
--前面我们完成tuning后生成的report中提到了建议接受一个profile以及remove提示ordered
--那我们看看接受sql profile之后Oracle到底干了什么
--从下面的查询可知,sql profile告诉优化器忽略该SQL语句中的提示，这就是他做的事情
hr@CNMMBO> SELECT attr_val
2 FROM sys.sqlprof$ p, sys.sqlprof$attr a
3 WHERE p.sp_name = 'hr_profile' AND p.signature = a.signature AND p.category = a.category;
ATTR_VAL
--------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
3、测试接受SQL profile后的情形
[sql]
--对于OLTP的情形，大部分SQL语句使用了绑定变量，下面来测试一下绑定变量是否影响sql profile的运用
--以及测试SQL语句在使用大小写，添加或移除空格的情形时sql profile是否被使用到
hr@CNMMBO> variable bnd number; --定义绑定变量并赋值
hr@CNMMBO> exec :bnd:=166
PL/SQL procedure successfully completed.
hr@CNMMBO> set autot trace exp;
hr@CNMMBO> SELECT /*+ ORDERED */
2 *
3 FROM employees e, locations l, departments d
4 WHERE e.department_id = d.department_id AND l.location_id =
5 d.location_id AND e.employee_id < :bnd;
Execution Plan
----------------------------------------------------------
Plan hash value: 685643925
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 685 | 9 (23)| 00:00:01 |
|* 1 | HASH JOIN | | 5 | 685 | 9 (23)| 00:00:01 |
| 2 | MERGE JOIN | | 5 | 440 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 5 | 340 | 3 (34)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 340 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 2 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
7 - access("E"."EMPLOYEE_ID"
-->
Note
-----
- SQL profile "hr_profile" used for this statement --->这个提示表明sql profile已经被使用
--下面我们给绑定变量重新赋值，以及使用小写来替换原来的SQL语句，并且移除一些空格，看看profile是否依旧会被使用
hr@CNMMBO> exec :bnd:=133;
PL/SQL procedure successfully completed.
hr@CNMMBO> select /*+ ORDERED */ * from employees e, locations l, departments d
2 where e.department_id = d.department_id AND l.location_id = d.location_id
3 and e.employee_id < :bnd;
Execution Plan
---------------------------------------
| 评论 |
|
|