1476 | 370 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 36 | 1476 | 370 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 36 | 1080 | 69 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 75308 | 808K| 300 (1)| 00:00:04 |
---------------------------------------------------------------------------
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 | 141 (1)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 36 | 1476 | 141 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 69 (2)| 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")
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 1
------
Plan Origin :Cursor Cache
Plan Hash Value :1022743391
Executions :12
Elapsed Time :0.022 sec
CPU Time :0.019 sec
Buffer Gets :318
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 5071 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 69 (2)| 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> exec dbms_sqltune.accept_sql_profile(task_name=>'任务_309',task_owner=>user,replace=>true,force_match=>true);
PL/SQL 过程已成功完成。
现在我们来看一下sql profile是否会其作用
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' AND T1.object_id=t2.object_id;
执行计划
-----------