SCOTT@test01p> create table t1 as select rownum id1 ,rownum id2 ,rownum id3 ,lpad(rownum,10,'a') vc from dual connect by level create index ix_t1_id2 on t1(id2,0);
Index created.
--//注:ix_t1_id1 索引后面加入一个常量0,变成函数索引.
SCOTT@test01p> create index ix_t1_id3 on t1(id3);
Index created.
SCOTT@test01p> @gts t1 '' ''
Gather Table Statistics for table t1...
exec dbms_stats.gather_table_stats('SCOTT', 'T1', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
if lock table t1, add force=>true.
press ctrl+c cancel, enter continue...
PL/SQL procedure successfully completed.
2.测试:
$ cat tt1.txt
set term off
variable v_id2 number;
variable v_id3 number;
exec :v_id2 := 42;
exec :v_id3 := 42;
set term on
select vc from t1 where id2 = :v_id2 or id3 = :v_id3 ;
SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 73jvxn4mk2mgw, child number 0
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 563811631
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2 | 38 | 4 (25)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | IX_T1_ID2 | | | 2 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 8 | INDEX RANGE SCAN | IX_T1_ID3 | | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3")))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 42
2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("ID2"=:V_ID2)
filter("ID2"=:V_ID2)
8 - access("ID3"=:V_ID3)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
59 rows selected.
--//记下sql_id=73jvxn4mk2mgw,注意outline部分BITMAP_TREE那行，出现IX_T1_ID2。另外ID＝5出现1次sort order by。
--//我在http://blog.itpub.net/267265/viewspace-2952012/ => [20230512]优化的困惑19.txt 有解析。
--//使用sql profile稳定执行计划.
SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 73jvxn4mk2mgw, child number 1
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 38 | 14 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 42
2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ID2"=:V_ID2 OR "ID3"=:V_ID3))
Note
-----
- SQL profile switch tuning 73jvxn4mk2mgw used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
50 rows selected.
--//虽然note提示使用sql profile，但是实际上sql profile已经失效,12c这里设置不好,19c以上有Hint Report,提示一些提示无效。执
--//行计划变成了全表扫描。
--//可是我还建立普通索引啊，为什么不用呢？
--//改名索引：
--//函数索引,并且还是不可见。
SCOTT@test01p> ALTER INDEX ix_t1_id2 rename to iy_t1_id2;
Index altered.
--//普通索引变成了ix_t1_id2.
SCOTT@test01p> ALTER INDEX i_t1_id2 rename to ix_t1_id2;
Index altered.
SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 73jvxn4mk2mgw, child number 0
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 2540130847
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2 | 38 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 5 | INDEX RANGE SCAN | IX_T1_ID2 | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 7 | INDEX RANGE SCAN | IX_T1_ID3 | | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3")))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 42
2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID2"=:V_ID2)
7 - access("ID3"=:V_ID3)
Note
-----
- SQL profile switch tuning 73jvxn4mk2mgw used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//注意下划线，现在提示如下,并且注意没有sort order by。而且索引提示是字段而不是索引名.
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3")))
--//原来如下
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3")))
SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 73jvxn4mk2mgw, child number 0
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 713367141
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2 | 38 | 4 (25)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 5 | SORT ORDER BY | | | | | |
--//出现SORT ORDER BY，说明使用函数索引。
|* 6 | INDEX RANGE SCAN | IY_T1_ID2 | | | 2 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 8 | INDEX RANGE SCAN | IX_T1_ID3 | | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IY_T1_ID2" 2 ("T1"."ID3"))) --//再次出现索引名。
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 42
2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("ID2"=:V_ID2)
filter("ID2"=:V_ID2)
8 - access("ID3"=:V_ID3)
Note
-----
- SQL profile switch tuning 73jvxn4mk2mgw used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
60 rows selected.
--//但是sql profile有效.
4.附上spsw.sql和spext.sql脚本：
$ cat spsw.sql
-- @create_profile_from_shared_pool c2trqja6wh561 0 TEST true
-- @spsw good_sql_id 0 bad_sql_id 0 test true
-- @spsw good_sql_id 0 bad_sql_id 0 '' true
DECLARE
ar_profile_hints SYS.sqlprof_attr;
cl_sql_text CLOB;
BEGIN
SELECT EXTRACTVALUE (VALUE (d), '/hint') AS outline_hints
BULK COLLECT INTO ar_profile_hints
FROM XMLTABLE (
'/*/outline_data/hint'
PASSING (SELECT xmltype (other_xml) AS xmlval
FROM v$sql_plan
WHERE sql_id = '&&1'
AND child_number = &&2
AND other_xml IS NOT NULL)) d;
SELECT SQL_FULLTEXT
INTO cl_sql_text
FROM -- replace with dba_hist_sqltext
-- if required for AWR based
-- execution
v$sqlarea
-- sys.dba_hist_sqltext
WHERE sql_id = '&&3'and rownum=1;
-- plan_hash_value = &&2;
DBMS_SQLTUNE.import_sql_profile (sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&5',
DESCRIPTION => 'switch &&1 => &&3',
name => 'switch tuning &&3' -- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
,
force_match => &&6);
END;
/
prompt =================================================================================================================================================
prompt if drop or alter sql profile ,run :
prompt execute dbms_sqltune.drop_sql_profile(name => 'switch tuning &&3')
prompt execute dbms_sqltune.alter_sql_profile(name => 'switch tuning &&3',attribute_name=>'STATUS',value=>'DISABLED')
prompt =================================================================================================================================================
prompt
prompt
$ cat spext.sql
column hint format a200
column name format a30
SELECT EXTRACTVALUE (VALUE (h), '.') AS hint,so.name
FROM SYS.sqlobj$data od
,SYS.sqlobj$ so
,TABLE
(
XMLSEQUENCE
(
EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint')
)
) h
WHERE ( so.NAME in ( 'profile &&1', 'tuning &&1','switch tuning &&1') or lower(so.name) like lower('%&&1%'))
AND so.signature = od.signature
AND so.CATEGORY = od.CATEGORY
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;