一. 手工建立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=)