一. 手工创建sql profile的方法
因为sql tuning的sql profile不能锁定执行计划，只是提供更准确的信息以生成更准确的执行计划，当统计信息改变时，sql的执行计划也会变化。所以就需要手工创建sql profile的方法来锁定指定计划。
目的：
a. 锁定或者说是稳定sql执行计划
b. 再不能修改应用的sql的情况下，来改变或者说是强制使sql使用我们指定的sql的执行计划，即使原始的sql包含了Hints.
方法：DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程，可以像outlines一样，稳定sql的执行计划。
二. 手工创建sql profile测试
1. 删除之前生成的sql profile，同时恢复T1表的统计信息中的表行数
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_015afaf3c7f80000');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats('TEST','T1',numrows=>49953);
PL/SQL procedure successfully completed.
2. 手工创建sql profile
SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints:=sys.sqlprof_attr('USE_NL([email protected]$1 [email protected]$1)','INDEX([email protected]$1)');
5 dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
6 v_hints,'SQLPROFILE_NAME1',force_match=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
3. 执行相应的sql
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
2 ;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 100K| 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2498 | 100K| 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2498 | 100K| 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 74940 | 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 "SQLPROFILE_NAME1" used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
310 consistent gets
1 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的执行计划已经采用了之前创建的sql profile
4. 修改表的统计信息，查看sql的执行计划
SQL> exec dbms_stats.set_table_stats('TEST','T1',numrows=>5000000);
PL/SQL procedure successfully completed.
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250K| 9M| 500K (1)| 01:40:03 |
| 1 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:40:03 |
| 2 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 107 (35)| 00:00:02 |
|* 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 "SQLPROFILE_NAME1" used for this statement
Statistics
----------------------------------------------------------
1 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
可以看到，oracle优化器评估表T1经过like条件过滤后返回的行数虽然很大，但是这里执行计划依然与之前一致，采用range scan+nested loop join。
三. 对现有的sql稳定其执行计划
1. 通过v$sql_plan找出稳定执行计划的hints
对于复杂的sql，很难手工构造hints，同时手工构造的hints不一定能够保证sql的执行计划能够稳定。10g之后，v$sql_plan中包括了sql语句的outline数据，也就是稳定执行计划的hints.
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2fvukamj6u7b7, child number 0
-------------------------------------
select t1.*,t2.owner from t1,t2 where t1.object_name
like '%T1%' and t1.object_id=t2.object_id
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 500K(100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:40:03 |
| 2 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 107 (35)| 00:00:02 |
|* 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 |
---------------------------------------------------------------------------------------
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
END_OUTLINE_DATA
*/
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
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
- SQL profile SQLPROFILE_NAME1 used for this statement
47 rows selected.
上面所显示的"Outline Data"即是我们稳定SQL执行计划需要的hints(甚至可以将这些hints直接写到sql中)。对需要稳定执行计划的SQL，就可以将Hints与SQL文本一起创建一个SQL profile。就要用到coe_xfr_sql_profile.sql脚本，这个脚本可以从 shared pool、awr中提取执行SQL ID的out line data并创建SQL profile。脚本来自于"SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]"
SQL> select /*+ proftest1 */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
从v$sql里面可以找出刚才执行的sql_id为3wymxrtstfstk。
2. 测试coe_xfr_sql_profile.sql
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 5czwj3q9sp5v1 --输入sql_id
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
2959412835 .584
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 2959412835 --输入正确的，需要稳定的执行计划的hash value
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "5czwj3q9sp5v1"
PLAN_HASH_VALUE: "2959412835"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql
on TARGET system in order to create a custom SQL Profile
with plan 2959412835 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
执行完之后会生成另一个脚本，运行这个脚本就会创建出稳定执行计划所需要的sql profile，sql profile的名字为coe+sql_id+plan_hash_value。这个脚本里面force match为false，可以手工修改脚本将其改为true，同时也可以根据医院来修改脚本的其他内容。
SQL>host
[[email protected] scripts]$ ls
coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql coe_xfr_sql_profile.log coe_xfr_sql_profile.sql
执行生成的脚本即可绑定指定的执行计划
SQL>@coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql 11.4.4.4 2017/03/31 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM [email protected]
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID 5czwj3q9sp5v1 based on plan hash
SQL>REM value 2959412835.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_5czwj3q9sp5v1_2959412835');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[select /*+ proftest1 */ t1.*,t2.owner
15 from t1,t2
16 ]');
17 wa(q'[ where t1.object_name like '%T1%'
18 and t1.object_id=t2]');
19 wa(q'[.object_id]');
20 DBMS_LOB.CLOSE(sql_txt);
21 h := SYS.SQLPROF_ATTR(
22 q'[BEGIN_OUTLINE_DATA]',
23 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
24 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
25 q'[DB_VERSION('11.2.0.4')]',
26 q'[ALL_ROWS]',
27 q'[OUTLINE_LEAF(@"SEL$1")]',
28 q'[FULL(@"SEL$1" "T2"@"SEL$1")]',
29 q'[FULL(@"SEL$1" "T1"@"SEL$1")]',
30 q'[LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")]',
31 q'[USE_HASH(@"SEL$1" "T1"@"SEL$1")]',
32 q'[END_OUTLINE_DATA]');
33 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
34 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
35 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
36 sql_text => sql_txt,
37 profile => h,
38 name => 'coe_5czwj3q9sp5v1_2959412835',
39 description => 'coe 5czwj3q9sp5v1 2959412835 '||:signature||' '||:signaturef||'',
40 category => 'DEFAULT',
41 validate => TRUE,
42 replace => TRUE,
43 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
44 DBMS_LOB.FREETEMPORARY(sql_txt);
45 END;
46 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
6058051510930011685
SIGNATUREF
---------------------
2283478425026279467
... manual custom SQL Profile has been created --sql profile创建成功
COE_XFR_SQL_PROFILE_5czwj3q9sp5v1_2959412835 completed
---
四. 再不能修改sql的情况下改变并固定sql的执行计划，即是原始sql使用了hints
在sql语句及其执行计划有问题，或者sql使用了错误的hints(比如)导致sql性能差，但是应用不能修改或者暂时无法修改sql的情况下，可以使用sql profile来稳定sql的执行计划。一般情况下不建议调整统计信息，因为比较复杂，不稳定可靠，影响面比较广(可能会为影响其他访问此对象的sql)
实现方法：
step1：取得原始sql的文本(sql_id)
step2：构造一个与原始sql在逻辑，结构上完全相同的sql。这里强制逻辑上和结构上相同，sql解析的用户名、sql中引用对象的用户名设置是一些predicate条件都可以不同。当然与原始sql一样最省事。
step3：执行构造的sql,取得构造的sql的outline data
step4：使用原始sql的文本和构造的sql的outline data创建sql profile
1. 执行原始sql，取得sql_id（2u5zxb296223v）
SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250K| 9M| | 1325 (3)| 00:00:16 |
|* 1 | HASH JOIN | | 250K| 9M| 1952K| 1325 (3)| 00:00:16 |
| 2 | TABLE ACCESS FULL| T2 | 86645 | 930K| | 345 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| T1 | 250K| 7324K| | 107 (35)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
1482 consistent gets
1722 physical reads
0 redo size
2205 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
2. 执行修改后的sql（dums8csjskpxn），使其按照我们想要的执行计划执行。
SQL>select /*+ modify_sql index(t1) use_nl(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250K| 9M| 500K (1)| 01:40:03 |
| 1 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:40:03 |
| 2 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 107 (35)| 00:00:02 |
|* 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
260 physical reads
0 redo size
2163 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
3. 使用coe_xfr_sql_profile.sql脚本来提取构造的sql的outline data
SQL> @coe_xfr_sql_profile.sql
Enter value for 1: dums8csjskpxn
PLAN_HASH_VALUE: "1022743391"
取得生成的创建sql profile的脚本，其中的outline data为：
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T1"@"SEL$1")]',
q'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',
q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
4. 针对原始sql使用coe_xfr_sql_profile.sql，手工修改生成的sql脚本，将其中的outline data替换为构造sql得到的那一段outline，同时将force_match从false改为true
SQL> @coe_xfr_sql_profile.sql
Enter value for 1: 2u5zxb296223v
Enter value for 2: 2959412835
修改生成的sql脚本coe_xfr_sql_profile_2u5zxb296223v_2959412835.sql并执行
SQL> @coe_xfr_sql_profile_2u5zxb296223v_2959412835.sql
PL/SQL procedure successfully completed.
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_2u5zxb296223v_2959412835 completed
5. 执行原始sql，查看执行情况
SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T2%'
4 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 | | 250K| 9M| 500K (1)| 01:
40:03 |
| 1 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:
40:03 |
| 2 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:
40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 107 (35)| 00:
00:02 |
|* 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 "coe_2u5zxb296223v_2959412835" used for this statement --可以看到执行计划已经采用了创建的sql profile
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
306 consistent gets
261 physical reads
0 redo size
2976 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
![SQL性能调优--去除索引函数修改时间判断逻辑[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)