SQL Tuning Advisor(STA)到底做了什么(五)
-------------------
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依旧有效
4、对比优化前后的I/O 开销
[sql]
a、先测试优化后的I/O cost
hr@CNMMBO> set autot trace stat;
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;
33 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
10 physical reads
0 redo size
5362 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
33 rows processed
--上面的Statistics表明优化后的consistent gets为14，physical reads为10
--将sql profile disable，则该profile不会生效
hr@CNMMBO> set autot off;
hr@CNMMBO> BEGIN
2 DBMS_SQLTUNE.alter_sql_profile (name => 'hr_profile', attribute_name => 'STATUS', VALUE => 'DISABLED');
3 END;
4 /
PL/SQL procedure successfully completed.
-->查看disable后的状态
hr@CNMMBO> select name,status from dba_sql_profiles where name='hr_profile';
NAME STATUS
------------------------------ --------
hr_profile DISABLED
hr@CNMMBO> print bnd;
BND
----------
133
--下面来查看disable后的统计信息，SQL语句会回到优化之前
hr@CNMMBO> set autot trace stat;
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
| 评论 |
|
|