[20230518]建立索引导致的性能问题3.txt
--//生产系统遭遇建立索引导致的性能问题,建立的sql profile里面包含索引名提示,很少见,改索引名导致sql profile失效,
--//当然我遇到的情况有一点点不同,建立新索引,然后旧索引设置不可见(相当于改名),今天测试看看修改sql profile的内容是否可行。
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t1 as select rownum id1 ,rownum id2 ,rownum id3 ,lpad(rownum,10,'a') vc from dual connect by level<=1e4;
Table created.
--//建立函数索引,包括一个常量0.
SCOTT@test01p> 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.
SCOTT@test01p> @ ind2 t1
Display indexes where table or index name matches t1...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
----------- ---------- ---------- ---- --------------- ----
SCOTT T1 IX_T1_ID2 1 ID2
2 SYS_NC00005$
IX_T1_ID3 1 ID3
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
----------- ---------- ---------- ---------- ---- ------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SCOTT T1 IX_T1_ID2 FBI NORMAL NO VALID NO N 2 24 10000 10000 39 2023-05-16 21:17:53 1 VISIBLE
T1 IX_T1_ID3 NORMAL NO VALID NO N 2 21 10000 10000 39 2023-05-16 21:17:53 1 VISIBLE
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 /*+ &&1 */ vc from t1 where id2 = :v_id2 or id3 = :v_id3 ;
SCOTT@test01p> @ tt1.txt test
VC
--------------------
aaaaaaaa42
SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 94as7gsx9b1rq, child number 0
-------------------------------------
select /*+ test */ 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=94as7gsx9b1rq,注意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> @ spsw 94as7gsx9b1rq 0 94as7gsx9b1rq 0 '' true
PL/SQL procedure successfully completed.
=========================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 94as7gsx9b1rq')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 94as7gsx9b1rq',attribute_name=>'STATUS',value=>'DISABLED')
=========================================================================================================================
--//验证看看.输出略,可以发现已经使用sql profile.
SCOTT@test01p> @ spext 94as7gsx9b1rq
HINT NAME
--------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 94as7gsx9b1rq
OPTIMIZER_FEATURES_ENABLE('12.2.0.1') switch tuning 94as7gsx9b1rq
DB_VERSION('12.2.0.1') switch tuning 94as7gsx9b1rq
ALL_ROWS switch tuning 94as7gsx9b1rq
OUTLINE_LEAF(@"SEL$1") switch tuning 94as7gsx9b1rq
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3"))) switch tuning 94as7gsx9b1rq
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1") switch tuning 94as7gsx9b1rq
7 rows selected.
--//注意下划线内容,包含索引名。
3.继续:
SCOTT@test01p> alter index IX_T1_ID2 rename to I_T1_ID2;
Index altered.
--//改名索引。
SCOTT@test01p> @ tt1.txt test
VC
--------------------
aaaaaaaa42
SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 94as7gsx9b1rq, child number 0
-------------------------------------
select /*+ test */ 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 94as7gsx9b1rq 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
--//执行计划已经是全表扫描。sql profile失效。
SCOTT@test01p> select * from SYS.sqlobj$data where SIGNATURE ='17052051993701408290'
2 @pr
==============================
SIGNATURE : 17052051993701408290
CATEGORY : DEFAULT
OBJ_TYPE : 1
PLAN_ID : 0
COMP_DATA :
<outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.2.0.1')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLI
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3")))]]></hint><hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")]]></hint></outline_data>
SPARE1 :
SPARE2 :
PL/SQL procedure successfully completed.
--//修改内容在COMP_DATA字段，类型clob类型，我的修改不会超过4000字符限制。
SCOTT@test01p> column c200 format a200
SCOTT@test01p> select COMP_DATA c200 from SYS.sqlobj$data where SIGNATURE ='17052051993701408290';
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.2.0.1')]]></hint><hint><![CDATA[ALL_ROWS]]
></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3")))]]></hint><hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1"
"T1"@"SEL$1")]]></hint></outline_data>
--//将"IX_T1_ID2" 换成 ("T1"."ID2")
--//以sys用户执行，利用q'转义，分隔符要使用比较特殊的^F(ctr+v ctrl+f).
update SYS.sqlobj$data
set COMP_DATA=
q'<outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.2.0.1')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3")))]]></hint><hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")]]></hint></outline_data>'
where SIGNATURE ='17052051993701408290';
commit;
SCOTT@test01p> @ spext 94as7gsx9b1rq
HINT NAME
----------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 94as7gsx9b1rq
OPTIMIZER_FEATURES_ENABLE('12.2.0.1') switch tuning 94as7gsx9b1rq
DB_VERSION('12.2.0.1') switch tuning 94as7gsx9b1rq
ALL_ROWS switch tuning 94as7gsx9b1rq
OUTLINE_LEAF(@"SEL$1") switch tuning 94as7gsx9b1rq
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3"))) switch tuning 94as7gsx9b1rq
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1") switch tuning 94as7gsx9b1rq
7 rows selected.
SCOTT@test01p> @ tt1.txt test
VC
--------------------
aaaaaaaa42
SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 94as7gsx9b1rq, child number 0
-------------------------------------
select /*+ test */ vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 4254410754
--------------------------------------------------------------------------------------------------
| 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 | I_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 "I_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 94as7gsx9b1rq 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
--//现在sql profile起作用，这样修改有一定风险，仅仅测试看看这样是否可行。
3.收尾:
SCOTT@test01p> execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 94as7gsx9b1rq')
PL/SQL procedure successfully completed.
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;