SQL Tuning Advisor(STA)到底做了什么(二)
s | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 85 | 11645 | 103 (1)| 00:00:02 |
|* 1 | HASH JOIN | | 85 | 11645 | 103 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1973 | 225K| 99 (0)| 00:00:02 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 86 | 5848 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 86 | | 1 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 23 | 1127 | 96 (0)| 00:00:02 |
| 7 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND
"L"."LOCATION_ID"="D"."LOCATION_ID")
5 - access("E"."EMPLOYEE_ID"<:BND)
2- Using SQL Profile
--------------------
Plan hash value: 2153960720
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 85 | 11645 | 10 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 85 | 11645 | 10 (20)| 00:00:01 |
| 2 | MERGE JOIN | | 27 | 1863 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_LOCATION_IX | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 23 | 1127 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 86 | 5848 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 86 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
filter("L"."LOCATION_ID"="D"."LOCATION_ID")
8 - access("E"."EMPLOYEE_ID"<:BND)
-------------------------------------------------------------------------------
-->上面的advisor report中得到了两个findings，一个是建议我们接受profile，一个建议我们移出ordered hint，因为它导致了笛卡尔集
2、根据优化建议接受SQL profile
[sql]
-->根据上面的advisor，接下来我们accept这个profile，唯一不同的增加了一个名字，如果不指定名字，
系统会自动生成一个
hr@CNMMBO> execute dbms_sqltune.accept_sql_profile(task_name => 'hr_query',replace=> TRUE,name=>'hr_profile');
PL/SQL procedure successfully completed.
--所有生成的SQL profile的相关信息都存放在数据字典dba_sql_profiles中
hr@CNMMBO> select name,category,signature,sql_text,created,type,status,force_
| 评论 |
|
|