[20230518]建立索引导致的性能问题3.txt

来源：这里教程网时间：2026-03-03 18:50:03 作者：

[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;