[20230517]建立索引导致的性能问题2.txt
--//生产系统遭遇建立索引导致的性能问题,建立的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.
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> @ tt1.txt
VC
--------------------
aaaaaaaa42
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> @ spsw 73jvxn4mk2mgw 0 73jvxn4mk2mgw 0 '' true
PL/SQL procedure successfully completed.
=================================================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 73jvxn4mk2mgw')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 73jvxn4mk2mgw',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
--//验证看看.输出略,可以发现已经使用sql profile.
SCOTT@test01p> @ spext 73jvxn4mk2mgw
HINT NAME
---------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 73jvxn4mk2mgw
OPTIMIZER_FEATURES_ENABLE('12.2.0.1') switch tuning 73jvxn4mk2mgw
DB_VERSION('12.2.0.1') switch tuning 73jvxn4mk2mgw
ALL_ROWS switch tuning 73jvxn4mk2mgw
OUTLINE_LEAF(@"SEL$1") switch tuning 73jvxn4mk2mgw
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3"))) switch tuning 73jvxn4mk2mgw
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1") switch tuning 73jvxn4mk2mgw
--//注意下划线内容,包含索引名。
3.继续:
--//普通索引
SCOTT@test01p> create index i_t1_id2 on t1(id2);
Index created.
SCOTT@test01p> ALTER INDEX ix_t1_id2 INVISIBLE;
Index altered.
--//设置ix_t1_id2 不可见。
SCOTT@test01p> @tt1.txt
VC
--------------------
aaaaaaaa42
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.
--//再次执行tt1.txt,执行计划如下:
SCOTT@test01p> @ tt1.txt
VC
--------------------
aaaaaaaa42
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")))
--//可以发现当使用函数索引时，outline里面是写死的IX_T1_ID2，而普通索引没有这个问题。
--//而我建立的sql profile里面写死了索引名。
--//一旦我改名索引就导致对应的sql profile失效。
SCOTT@test01p> ALTER INDEX ix_t1_id2 rename to iz_t1_id2;
Index altered.
--//再次执行tt1.txt,执行计划变成全表扫描:
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 |
---------------------------------------------------------------------------
--//删除sql profile：
SCOTT@test01p> execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 73jvxn4mk2mgw')
PL/SQL procedure successfully completed.
SCOTT@test01p> @ tt1.txt
VC
--------------------
aaaaaaaa42
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: 3294346658
--------------------------------------------------------------------------------------------------
| 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 | IZ_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
-----
- 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
57 rows selected.
--//重新建立sql profile：
SCOTT@test01p> @ spsw 73jvxn4mk2mgw 0 73jvxn4mk2mgw 0 '' true
PL/SQL procedure successfully completed.
=================================================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 73jvxn4mk2mgw')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 73jvxn4mk2mgw',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
--//验证略。执行计划outline部分如下：
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
*/
SCOTT@test01p> @ spext 73jvxn4mk2mgw
HINT NAME
---------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 73jvxn4mk2mgw
OPTIMIZER_FEATURES_ENABLE('12.2.0.1') switch tuning 73jvxn4mk2mgw
DB_VERSION('12.2.0.1') switch tuning 73jvxn4mk2mgw
ALL_ROWS switch tuning 73jvxn4mk2mgw
OUTLINE_LEAF(@"SEL$1") switch tuning 73jvxn4mk2mgw
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3"))) switch tuning 73jvxn4mk2mgw
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1") switch tuning 73jvxn4mk2mgw
7 rows selected.
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_ID3 1 ID3
IY_T1_ID2 1 ID2
2 SYS_NC00005$
IZ_T1_ID2 1 ID2
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_ID3 NORMAL NO VALID NO N 2 21 10000 10000 39 2023-05-16 20:30:03 1 VISIBLE
T1 IY_T1_ID2 FBI NORMAL NO VALID NO N 2 24 10000 10000 39 2023-05-16 20:29:40 1 INVISIBLE
T1 IZ_T1_ID2 NORMAL NO VALID NO N 2 21 10000 10000 39 2023-05-16 20:44:09 1 VISIBLE
--//当前IY_T1_ID2(函数索引)INVISIBLE。IZ_T1_ID2(普通索引),VISIBLE.
SCOTT@test01p> ALTER INDEX iy_t1_id2 VISIBLE;
Index altered.
SCOTT@test01p> ALTER INDEX iz_t1_id2 inVISIBLE;
Index altered.
SCOTT@test01p> @ tt1.txt
VC
--------------------
aaaaaaaa42
1 row selected.
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有效.
3.总结：
--//我仅仅想通过这个例子提醒自己如果通过类似交换方式稳定执行计划时，注意生成的outline部分，
--//里面一些提示会不会出现写死的情况。这样一个改名可能就导致sql profile失效。
--//收尾:
SCOTT@test01p> execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 73jvxn4mk2mgw')
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;