[20221008]sql profile优化失效问题.txt
[20221008]sql profile优化失效问题.txt
--//生产系统一条sql语句存在性能问题,使用sql profile优化固定执行计划,再次出现问题,以前也遇到类似的问题,做一个记录.
1.环境:
SYS@192.168.100.235:1521/orcl> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.分析sql_id=3rhg88u6qnt7h
SYS@192.168.100.235:1521/orcl> @ sql_id 3rhg88u6qnt7h
SELECT "Extent1"."ID" AS "ID"
...
, "Extent1"."ORDER_USER_NAME" AS "ORDER_USER_NAME"
FROM "LIS"."LIS_LOG_INFECTION" "Extent1"
WHERE ((("Extent1"."TENANTID" = :DynamicFilterPara m_000001) )
AND (("Extent1"."PAT_ID" = :p__linq__0) OR (("Extent1"."PAT_ID" IS NULL) AND (:p__linq__0 IS NULL)))
AND (("Extent1"."PAT_BARCODE" = :p__linq__1) OR (("Extent1"."PAT_BARCODE" IS NULL) AND (:p__linq__1 IS NULL))))
AND (ROWNUM <= (1) );
--//查看绑定变量的值:
SYS@192.168.100.235:1521/orcl> @ bind_cap_awr 3rhg88u6qnt7h ''
@bind_cap_awr sql_id [column]
no rows selected
SNAP_ID INSTANCE_NUMBER SQL_ID WAS LAST_CAPTURED NAME POSITION MAX_LENGTH DATATYPE_STRING VALUE_STRING
------- --------------- ------------- --- ------------------- -------------------------- -------- ---------- --------------- ------------
15162 1 3rhg88u6qnt7h YES 2022-08-21 08:23:54 :DYNAMICFILTERPARAM_000001 1 22 NUMBER 1
1 :P__LINQ__0 2 128 NVARCHAR2(128) 60715862
1 :P__LINQ__1 4 32 NVARCHAR2(32) 60715862
15163 1 3rhg88u6qnt7h YES 2022-08-21 09:32:51 :DYNAMICFILTERPARAM_000001 1 22 NUMBER 1
1 :P__LINQ__0 2 128 NVARCHAR2(128) 04175454
1 :P__LINQ__1 4 32 NVARCHAR2(32) 04175454
...
--//带入的2个绑定变量值内容一样。
--//居然使用NVARCHAR2数据类型的参数,开始以为又是隐式转换问题，仔细查看不是，相关表字段定义的类型也是NVARCHAR2类型，不过
--//又是一个混合NVARCHAR2,VARCHAR2类型的应用程序，好在表字段开发已经定义为NVARCHAR2类型，如果带入参数是varchar2，转换发
--//生在带入参数端，没有出现隐式转换。
--//我估计正是开发在使用绑定变量字符类型上的混乱，我查看数据库应用表字典的定义，几乎全部字符类型字段都是NVARCHAR2类型。
SYS@192.168.100.235:1521/orcl> @ descz lis.LIS_LOG_INFECTION "column_name in ('PAT_ID','PAT_BARCODE')"
eXtended describe of lis.LIS_LOG_INFECTION
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- --------------- ---------------
LIS LIS_LOG_INFECTION 1425300 2022-07-16 06:02:18 6 PAT_ID NVARCHAR2(36) 1283712 .00000077899 291 1
1424609 2022-07-16 06:02:18 7 PAT_BARCODE NVARCHAR2(72) 1409664 .00000070939 982 1
--//descz.sql脚本不支持字段类型NARCHAR2的Low_value，High_value的显示，有机会完善descz.sql脚本。
--//PAT_ID,PAT_BARCODE的索引也建立了，而且2个字段都存在NULL，我本来想加入not null约束限制查询，这样可以取消一些查询条件
--//限制,实际情况不允许。
--//执行计划如下：
Plan hash value: 2640574480
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11042 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| LIS_LOG_INFECTION | 1 | 247 | 11042 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / Extent1@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :DYNAMICFILTERPARAM_000001 (NUMBER): 1
2 - :P__LINQ__0 (NVARCHAR2(30), CSID=2000): '90495940'
4 - :P__LINQ__1 (NVARCHAR2(30), CSID=2000): '90495940'
--//开发上想实现的是输入2个条件来查询。
--//注意前面SQL语句括号的位置，开发写的使用括号太多了，有点乱。单独拿出其中1个，删除一些括号：
("Extent1"."PAT_ID" = :p__linq__0
OR
("Extent1"."PAT_ID" IS NULL AND :p__linq__0 IS NULL))
--//开发的想法是如果:p__linq__0 没有输入(相当于NULL)，查询条件变成了"Extent1"."PAT_ID" IS NULL。
--// 如果:p__linq__0 有输入，查询条件变成了"Extent1"."PAT_ID" = :p__linq__0
--//开发本来意思输入两个查询参数或者输入其中1个可以显示查询，分成4个情况：
1. :p__linq__0 没有输入 :p__linq__1 没有输入 , 查询条件变成了"Extent1"."PAT_ID" IS NULL and "Extent1"."PAT_BARCODE" IS NULL
2. :p__linq__0 有输入 :p__linq__1 没有输入 , 查询条件变成了"Extent1"."PAT_ID" = :p__linq__0 and "Extent1"."PAT_BARCODE" IS NULL
3. :p__linq__0 没有输入 :p__linq__1 有输入 , 查询条件变成了"Extent1"."PAT_ID" IS NULL and "Extent1"."PAT_BARCODE" = :p__linq__1.
4. :p__linq__0 有输入 :p__linq__1 有输入 , 查询条件变成了"Extent1"."PAT_ID" = :p__linq__0 and "Extent1"."PAT_BARCODE" = :p__linq__1.
--//真心建议开发不要这样写sql语句，至少oracle优化引擎无法很好的优化这类语句。
--//我正好看了程序里面大量sql语句,发现许多类似风格的sql语句,真心再次建议开发不要写这些垃圾的代码,逻辑对的,但是oracle的优
--//化器基本选择都是全部扫描.
3.使用sql profile:
--//生成执行脚本,执行如下：
SYS@192.168.100.235:1521/orcl> @ b9d 3rhg88u6qnt7h 0
--//加入如下提示/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(&&1)) */,执行测试脚本：
SYS@192.168.100.235:1521/orcl> @ 3rhg88u6qnt7h.sql9_0 7
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6svzht02nz53p, child number 0
-------------------------------------
SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7)) */ "Extent1"."ID" AS
"ID", "Extent1"."TENANTID" AS "TENANTID", "Extent1"."INFECTION_TYPE" AS
"INFECTION_TYPE", "Extent1"."PAT_TYPE_ID" AS "PAT_TYPE_ID",
"Extent1"."PAT_TYPE_NAME" AS "PAT_TYPE_NAME", "Extent1"."PAT_ID" AS
"PAT_ID", "Extent1"."PAT_BARCODE" AS "PAT_BARCODE",
"Extent1"."VISIT_NO" AS "VISIT_NO", "Extent1"."PAT_NAME" AS "PAT_NAME",
"Extent1"."CHECKUP_CODE" AS "CHECKUP_CODE", "Extent1"."DEPART_CODE" AS
"DEPART_CODE", "Extent1"."AREA_CODE" AS "AREA_CODE",
"Extent1"."AREA_NAME" AS "AREA_NAME", "Extent1"."DEPART_NAME" AS
"DEPART_NAME", "Extent1"."BED" AS "BED", "Extent1"."PY" AS "PY",
"Extent1"."PAT_SEX" AS "PAT_SEX", "Extent1"."ORDER_PAT_AGE" AS
"ORDER_PAT_AGE", "Extent1"."PAT_AGE" AS "PAT_AGE",
"Extent1"."PAT_BIRTHDAY" AS "PAT_BIRTHDAY", "Extent1"."AGE_UNIT" AS
"AGE_UNIT", "Extent1"."REPORT_AGE" AS "REPORT_AGE",
"Extent1"."SAMPLE_CODE" AS "SAMPLE_CODE", "Extent1"."SAMPLE_TYPE_NAME"
AS "SAMPLE_TYPE_NAME", "Extent1"."OFFICE_ID"
Plan hash value: 2357843953
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 15471 (100)| | 1 |00:00:00.02 | 4 | 1 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.02 | 4 | 1 |
| 2 | CONCATENATION | | 1 | | | | | 1 |00:00:00.02 | 4 | 1 |
|* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 |
|* 4 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 |
|* 5 | TABLE ACCESS FULL | LIS_LOG_INFECTION | 0 | 1 | 259 | 15467 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 6 | FILTER | | 1 | | | | | 1 |00:00:00.02 | 4 | 1 |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_LOG_INFECTION | 1 | 1 | 259 | 4 (0)| 00:00:01 | 1 |00:00:00.02 | 4 | 1 |
|* 8 | INDEX RANGE SCAN | IX_LIS_LOG_INFECTION_PAT_BARCO | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.02 | 3 | 1 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$1_1 / Extent1@SEL$1
7 - SEL$1_2 / Extent1@SEL$1_2
8 - SEL$1_2 / Extent1@SEL$1_2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_1")
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7))
OUTLINE_LEAF(@"SEL$1_2")
FULL(@"SEL$1_1" "Extent1"@"SEL$1")
INDEX_RS_ASC(@"SEL$1_2" "Extent1"@"SEL$1_2" ("LIS_LOG_INFECTION"."PAT_BARCODE"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "Extent1"@"SEL$1_2")
PQ_FILTER(@"SEL$1" SERIAL)
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 1
2 - :2 (NVARCHAR2(30), CSID=2000): '91144085'
4 - (NVARCHAR2(30), CSID=2000): '91144085'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
3 - filter(ROWNUM<=1)
4 - filter(:P__LINQ__1 IS NULL)
5 - filter(("Extent1"."PAT_BARCODE" IS NULL AND ("Extent1"."PAT_ID"=:P__LINQ__0 OR ("Extent1"."PAT_ID" IS NULL AND :P__LINQ__0 IS NULL)) AND
"Extent1"."TENANTID"=:DYNAMICFILTERPARAM_000001))
6 - filter(ROWNUM<=1)
7 - filter((("Extent1"."PAT_ID"=:P__LINQ__0 OR ("Extent1"."PAT_ID" IS NULL AND :P__LINQ__0 IS NULL)) AND "Extent1"."TENANTID"=:DYNAMICFILTERPARAM_000001 AND
(LNNVL("Extent1"."PAT_BARCODE" IS NULL) OR LNNVL(:P__LINQ__1 IS NULL))))
8 - access("Extent1"."PAT_BARCODE"=:P__LINQ__1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1
- USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7))
90 rows selected.
--//注意: 我选择使用PAT_BARCODE,主要原因是它选择性比PAT_ID要好.
--//另外id=4,filter(:P__LINQ__1 IS NULL),因为两个绑定变量都有赋值，全表扫描不会发生。
--//如果执行如下，使用pat_id索引，有可能输出存在不一致的情况，因为代码里面有一个限制条件ROWNUM <= (1)。
@ 3rhg88u6qnt7h.sql9d_0 1
--//结果不贴出了。
--//剩下的优化就简单了，使用sql profile优化。
--//我只所以写下来真心建议开发不要写这样的代码或者这样所谓的技巧。oracle优化器目前还没有这么智能，无法再拆解第2个或，形
--//成4个分支条件。
--//导致其中1个分支一定选择全表扫描，好在上面实际的应用两个变量都有赋值，另外一个全表扫描被短路了，不会执行。
--//实际上真实的生产环境不会两个都不输入的情况。这样查询条件变成了：
"Extent1"."PAT_ID" IS NULL and "Extent1"."PAT_BARCODE" IS NULL
4.使用sql profile出现问题:
SYS@192.168.100.235:1521/orcl> @ spsw 6svzht02nz53p 0 3rhg88u6qnt7h 1 '' true
PL/SQL procedure successfully completed.
--//再次查看执行计划发现无法正常使用我建立的提示.结果我不贴出了.
SYS@192.168.100.235:1521/orcl> @ spext 3rhg88u6qnt7h
HINT NAME
--------------------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 3rhg88u6qnt7h
OPTIMIZER_FEATURES_ENABLE('19.1.0') switch tuning 3rhg88u6qnt7h
DB_VERSION('19.1.0') switch tuning 3rhg88u6qnt7h
ALL_ROWS switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1") switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1_1") switch tuning 3rhg88u6qnt7h
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7)) switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1_2") switch tuning 3rhg88u6qnt7h
FULL(@"SEL$1_1" "Extent1"@"SEL$1") switch tuning 3rhg88u6qnt7h
INDEX_RS_ASC(@"SEL$1_2" "Extent1"@"SEL$1_2" ("LIS_LOG_INFECTION"."PAT_BARCODE")) switch tuning 3rhg88u6qnt7h
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "Extent1"@"SEL$1_2") switch tuning 3rhg88u6qnt7h
PQ_FILTER(@"SEL$1" SERIAL) switch tuning 3rhg88u6qnt7h
12 rows selected.
--//尝试使用以上全部提示执行sql语句,发现没有问题略.
--//我手工尝试使用sql profile,我发现2种情况,原来的语句可以使用正常执行.
1.删除 OUTLINE_LEAF(@"SEL$1") 提示.
2.加入BEGIN_OUTLINE_DATA,END_OUTLINE_DATA 提示,OK.
--//手工编写脚本,贴出删除 OUTLINE_LEAF(@"SEL$1") 提示的情况.
DECLARE
ar_profile_hints SYS.sqlprof_attr;
cl_sql_text CLOB;
BEGIN
SELECT SQL_FULLTEXT
INTO cl_sql_text
FROM -- replace with dba_hist_sqltext
-- if required for AWR based
-- execution
v$sql
-- sys.dba_hist_sqltext
WHERE sql_id = '&&3' AND child_number = &&4;
-- plan_hash_value = &&2;
DBMS_SQLTUNE.import_sql_profile (sql_text => cl_sql_text,
profile => sqlprof_attr(
'IGNORE_OPTIM_EMBEDDED_HINTS',
q'[OPTIMIZER_FEATURES_ENABLE('19.1.0')]',
q'[DB_VERSION('19.1.0')]',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1_1")',
'USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7))',
'OUTLINE_LEAF(@"SEL$1_2")',
'FULL(@"SEL$1_1" "Extent1"@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1_2" "Extent1"@"SEL$1_2" ("LIS_LOG_INFECTION"."PAT_BARCODE"))',
'BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "Extent1"@"SEL$1_2")',
'PQ_FILTER(@"SEL$1" SERIAL)'
),
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;
/
SYS@192.168.100.235:1521/orcl> @ spext 3rhg88u6qnt7h
HINT NAME
-------------------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 3rhg88u6qnt7h
OPTIMIZER_FEATURES_ENABLE('19.1.0') switch tuning 3rhg88u6qnt7h
DB_VERSION('19.1.0') switch tuning 3rhg88u6qnt7h
ALL_ROWS switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1_1") switch tuning 3rhg88u6qnt7h
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7)) switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1_2") switch tuning 3rhg88u6qnt7h
FULL(@"SEL$1_1" "Extent1"@"SEL$1") switch tuning 3rhg88u6qnt7h
INDEX_RS_ASC(@"SEL$1_2" "Extent1"@"SEL$1_2" ("LIS_LOG_INFECTION"."PAT_BARCODE")) switch tuning 3rhg88u6qnt7h
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "Extent1"@"SEL$1_2") switch tuning 3rhg88u6qnt7h
PQ_FILTER(@"SEL$1" SERIAL) switch tuning 3rhg88u6qnt7h
11 rows selected.
--//很奇怪,以前使用这种sql profile的交换模式一直没有问题的.
--//最终我采用sql patch的方式完成优化:
SYS@192.168.100.235:1521/orcl> @ sqlpatch 3rhg88u6qnt7h 'USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7))'
input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_3rhg88u6qnt7h');
display sql path message , run @spext 3rhg88u6qnt7h
SYS@192.168.100.235:1521/orcl> @ spext 3rhg88u6qnt7h
HINT NAME
--------------------------------------- ------------------------------
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7)) sqlpatch_3rhg88u6qnt7h
5.收尾:
--//修改sqlobj$auxdata,created 字段秒后有值的情况。 toad 下查看sql profile的界面存在一点点小问题,要清除
--//sqlobj$auxdata.created字段 秒后面的值.
--//update (select * from sqlobj$auxdata where created<>to_char(created,'yyyy-mm-dd hh24:mi:ss')) set created=to_char(created,'yyyy-mm-dd hh24:mi:ss');
--//update (select * from sqlobj$auxdata) set created=to_char(created,'yyyy-mm-dd hh24:mi:ss');
--//commit ;
--//主要目的便于使用toad管理.
6.附上执行脚本:
$ 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$sql
-- sys.dba_hist_sqltext
WHERE sql_id = '&&3' AND child_number = &&4;
-- 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;
/
$ 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;
$ cat sqlpatch.sql
prompt
prompt input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
prompt drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_&1');;
prompt display sql path message , run @spext &1
prompt
define noprint='noprint'
set term off
col tpt_version_old &noprint new_value _tpt_version_old
col tpt_version_new &noprint new_value _tpt_version_new
col tpt_noprint &noprint new_value _tpt_noprint
WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old
,CASE WHEN v > 11 THEN '' ELSE '--' END tpt_version_new
FROM version;
set term on
declare
v_sql CLOB;
patch_name VARCHAR2 (100);
begin
select sql_fulltext into v_sql from v$sql where sql_id='&1' and rownum=1;
&&_tpt_version_old sys.dbms_sqldiag_internal.i_create_patch(
&&_tpt_version_old sql_text => v_sql,
&&_tpt_version_old hint_text => '&2',
&&_tpt_version_old name => 'sqlpatch_&1');
&&_tpt_version_new patch_name :=
&&_tpt_version_new sys.DBMS_SQLDIAG.create_sql_patch
&&_tpt_version_new (
&&_tpt_version_new sql_text => v_sql
&&_tpt_version_new ,hint_text => '&2'
&&_tpt_version_new ,name => 'sqlpatch_&1'
&&_tpt_version_new );
end;
/
![]( "轻灵划译")
--//生产系统一条sql语句存在性能问题,使用sql profile优化固定执行计划,再次出现问题,以前也遇到类似的问题,做一个记录.
1.环境:
SYS@192.168.100.235:1521/orcl> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.分析sql_id=3rhg88u6qnt7h
SYS@192.168.100.235:1521/orcl> @ sql_id 3rhg88u6qnt7h
SELECT "Extent1"."ID" AS "ID"
...
, "Extent1"."ORDER_USER_NAME" AS "ORDER_USER_NAME"
FROM "LIS"."LIS_LOG_INFECTION" "Extent1"
WHERE ((("Extent1"."TENANTID" = :DynamicFilterPara m_000001) )
AND (("Extent1"."PAT_ID" = :p__linq__0) OR (("Extent1"."PAT_ID" IS NULL) AND (:p__linq__0 IS NULL)))
AND (("Extent1"."PAT_BARCODE" = :p__linq__1) OR (("Extent1"."PAT_BARCODE" IS NULL) AND (:p__linq__1 IS NULL))))
AND (ROWNUM <= (1) );
--//查看绑定变量的值:
SYS@192.168.100.235:1521/orcl> @ bind_cap_awr 3rhg88u6qnt7h ''
@bind_cap_awr sql_id [column]
no rows selected
SNAP_ID INSTANCE_NUMBER SQL_ID WAS LAST_CAPTURED NAME POSITION MAX_LENGTH DATATYPE_STRING VALUE_STRING
------- --------------- ------------- --- ------------------- -------------------------- -------- ---------- --------------- ------------
15162 1 3rhg88u6qnt7h YES 2022-08-21 08:23:54 :DYNAMICFILTERPARAM_000001 1 22 NUMBER 1
1 :P__LINQ__0 2 128 NVARCHAR2(128) 60715862
1 :P__LINQ__1 4 32 NVARCHAR2(32) 60715862
15163 1 3rhg88u6qnt7h YES 2022-08-21 09:32:51 :DYNAMICFILTERPARAM_000001 1 22 NUMBER 1
1 :P__LINQ__0 2 128 NVARCHAR2(128) 04175454
1 :P__LINQ__1 4 32 NVARCHAR2(32) 04175454
...
--//带入的2个绑定变量值内容一样。
--//居然使用NVARCHAR2数据类型的参数,开始以为又是隐式转换问题，仔细查看不是，相关表字段定义的类型也是NVARCHAR2类型，不过
--//又是一个混合NVARCHAR2,VARCHAR2类型的应用程序，好在表字段开发已经定义为NVARCHAR2类型，如果带入参数是varchar2，转换发
--//生在带入参数端，没有出现隐式转换。
--//我估计正是开发在使用绑定变量字符类型上的混乱，我查看数据库应用表字典的定义，几乎全部字符类型字段都是NVARCHAR2类型。
SYS@192.168.100.235:1521/orcl> @ descz lis.LIS_LOG_INFECTION "column_name in ('PAT_ID','PAT_BARCODE')"
eXtended describe of lis.LIS_LOG_INFECTION
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- --------------- ---------------
LIS LIS_LOG_INFECTION 1425300 2022-07-16 06:02:18 6 PAT_ID NVARCHAR2(36) 1283712 .00000077899 291 1
1424609 2022-07-16 06:02:18 7 PAT_BARCODE NVARCHAR2(72) 1409664 .00000070939 982 1
--//descz.sql脚本不支持字段类型NARCHAR2的Low_value，High_value的显示，有机会完善descz.sql脚本。
--//PAT_ID,PAT_BARCODE的索引也建立了，而且2个字段都存在NULL，我本来想加入not null约束限制查询，这样可以取消一些查询条件
--//限制,实际情况不允许。
--//执行计划如下：
Plan hash value: 2640574480
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11042 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| LIS_LOG_INFECTION | 1 | 247 | 11042 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / Extent1@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :DYNAMICFILTERPARAM_000001 (NUMBER): 1
2 - :P__LINQ__0 (NVARCHAR2(30), CSID=2000): '90495940'
4 - :P__LINQ__1 (NVARCHAR2(30), CSID=2000): '90495940'
--//开发上想实现的是输入2个条件来查询。
--//注意前面SQL语句括号的位置，开发写的使用括号太多了，有点乱。单独拿出其中1个，删除一些括号：
("Extent1"."PAT_ID" = :p__linq__0
OR
("Extent1"."PAT_ID" IS NULL AND :p__linq__0 IS NULL))
--//开发的想法是如果:p__linq__0 没有输入(相当于NULL)，查询条件变成了"Extent1"."PAT_ID" IS NULL。
--// 如果:p__linq__0 有输入，查询条件变成了"Extent1"."PAT_ID" = :p__linq__0
--//开发本来意思输入两个查询参数或者输入其中1个可以显示查询，分成4个情况：
1. :p__linq__0 没有输入 :p__linq__1 没有输入 , 查询条件变成了"Extent1"."PAT_ID" IS NULL and "Extent1"."PAT_BARCODE" IS NULL
2. :p__linq__0 有输入 :p__linq__1 没有输入 , 查询条件变成了"Extent1"."PAT_ID" = :p__linq__0 and "Extent1"."PAT_BARCODE" IS NULL
3. :p__linq__0 没有输入 :p__linq__1 有输入 , 查询条件变成了"Extent1"."PAT_ID" IS NULL and "Extent1"."PAT_BARCODE" = :p__linq__1.
4. :p__linq__0 有输入 :p__linq__1 有输入 , 查询条件变成了"Extent1"."PAT_ID" = :p__linq__0 and "Extent1"."PAT_BARCODE" = :p__linq__1.
--//真心建议开发不要这样写sql语句，至少oracle优化引擎无法很好的优化这类语句。
--//我正好看了程序里面大量sql语句,发现许多类似风格的sql语句,真心再次建议开发不要写这些垃圾的代码,逻辑对的,但是oracle的优
--//化器基本选择都是全部扫描.
3.使用sql profile:
--//生成执行脚本,执行如下：
SYS@192.168.100.235:1521/orcl> @ b9d 3rhg88u6qnt7h 0
--//加入如下提示/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(&&1)) */,执行测试脚本：
SYS@192.168.100.235:1521/orcl> @ 3rhg88u6qnt7h.sql9_0 7
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6svzht02nz53p, child number 0
-------------------------------------
SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7)) */ "Extent1"."ID" AS
"ID", "Extent1"."TENANTID" AS "TENANTID", "Extent1"."INFECTION_TYPE" AS
"INFECTION_TYPE", "Extent1"."PAT_TYPE_ID" AS "PAT_TYPE_ID",
"Extent1"."PAT_TYPE_NAME" AS "PAT_TYPE_NAME", "Extent1"."PAT_ID" AS
"PAT_ID", "Extent1"."PAT_BARCODE" AS "PAT_BARCODE",
"Extent1"."VISIT_NO" AS "VISIT_NO", "Extent1"."PAT_NAME" AS "PAT_NAME",
"Extent1"."CHECKUP_CODE" AS "CHECKUP_CODE", "Extent1"."DEPART_CODE" AS
"DEPART_CODE", "Extent1"."AREA_CODE" AS "AREA_CODE",
"Extent1"."AREA_NAME" AS "AREA_NAME", "Extent1"."DEPART_NAME" AS
"DEPART_NAME", "Extent1"."BED" AS "BED", "Extent1"."PY" AS "PY",
"Extent1"."PAT_SEX" AS "PAT_SEX", "Extent1"."ORDER_PAT_AGE" AS
"ORDER_PAT_AGE", "Extent1"."PAT_AGE" AS "PAT_AGE",
"Extent1"."PAT_BIRTHDAY" AS "PAT_BIRTHDAY", "Extent1"."AGE_UNIT" AS
"AGE_UNIT", "Extent1"."REPORT_AGE" AS "REPORT_AGE",
"Extent1"."SAMPLE_CODE" AS "SAMPLE_CODE", "Extent1"."SAMPLE_TYPE_NAME"
AS "SAMPLE_TYPE_NAME", "Extent1"."OFFICE_ID"
Plan hash value: 2357843953
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 15471 (100)| | 1 |00:00:00.02 | 4 | 1 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.02 | 4 | 1 |
| 2 | CONCATENATION | | 1 | | | | | 1 |00:00:00.02 | 4 | 1 |
|* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 |
|* 4 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 |
|* 5 | TABLE ACCESS FULL | LIS_LOG_INFECTION | 0 | 1 | 259 | 15467 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 6 | FILTER | | 1 | | | | | 1 |00:00:00.02 | 4 | 1 |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_LOG_INFECTION | 1 | 1 | 259 | 4 (0)| 00:00:01 | 1 |00:00:00.02 | 4 | 1 |
|* 8 | INDEX RANGE SCAN | IX_LIS_LOG_INFECTION_PAT_BARCO | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.02 | 3 | 1 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$1_1 / Extent1@SEL$1
7 - SEL$1_2 / Extent1@SEL$1_2
8 - SEL$1_2 / Extent1@SEL$1_2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_1")
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7))
OUTLINE_LEAF(@"SEL$1_2")
FULL(@"SEL$1_1" "Extent1"@"SEL$1")
INDEX_RS_ASC(@"SEL$1_2" "Extent1"@"SEL$1_2" ("LIS_LOG_INFECTION"."PAT_BARCODE"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "Extent1"@"SEL$1_2")
PQ_FILTER(@"SEL$1" SERIAL)
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 1
2 - :2 (NVARCHAR2(30), CSID=2000): '91144085'
4 - (NVARCHAR2(30), CSID=2000): '91144085'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
3 - filter(ROWNUM<=1)
4 - filter(:P__LINQ__1 IS NULL)
5 - filter(("Extent1"."PAT_BARCODE" IS NULL AND ("Extent1"."PAT_ID"=:P__LINQ__0 OR ("Extent1"."PAT_ID" IS NULL AND :P__LINQ__0 IS NULL)) AND
"Extent1"."TENANTID"=:DYNAMICFILTERPARAM_000001))
6 - filter(ROWNUM<=1)
7 - filter((("Extent1"."PAT_ID"=:P__LINQ__0 OR ("Extent1"."PAT_ID" IS NULL AND :P__LINQ__0 IS NULL)) AND "Extent1"."TENANTID"=:DYNAMICFILTERPARAM_000001 AND
(LNNVL("Extent1"."PAT_BARCODE" IS NULL) OR LNNVL(:P__LINQ__1 IS NULL))))
8 - access("Extent1"."PAT_BARCODE"=:P__LINQ__1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1
- USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7))
90 rows selected.
--//注意: 我选择使用PAT_BARCODE,主要原因是它选择性比PAT_ID要好.
--//另外id=4,filter(:P__LINQ__1 IS NULL),因为两个绑定变量都有赋值，全表扫描不会发生。
--//如果执行如下，使用pat_id索引，有可能输出存在不一致的情况，因为代码里面有一个限制条件ROWNUM <= (1)。
@ 3rhg88u6qnt7h.sql9d_0 1
--//结果不贴出了。
--//剩下的优化就简单了，使用sql profile优化。
--//我只所以写下来真心建议开发不要写这样的代码或者这样所谓的技巧。oracle优化器目前还没有这么智能，无法再拆解第2个或，形
--//成4个分支条件。
--//导致其中1个分支一定选择全表扫描，好在上面实际的应用两个变量都有赋值，另外一个全表扫描被短路了，不会执行。
--//实际上真实的生产环境不会两个都不输入的情况。这样查询条件变成了：
"Extent1"."PAT_ID" IS NULL and "Extent1"."PAT_BARCODE" IS NULL
4.使用sql profile出现问题:
SYS@192.168.100.235:1521/orcl> @ spsw 6svzht02nz53p 0 3rhg88u6qnt7h 1 '' true
PL/SQL procedure successfully completed.
--//再次查看执行计划发现无法正常使用我建立的提示.结果我不贴出了.
SYS@192.168.100.235:1521/orcl> @ spext 3rhg88u6qnt7h
HINT NAME
--------------------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 3rhg88u6qnt7h
OPTIMIZER_FEATURES_ENABLE('19.1.0') switch tuning 3rhg88u6qnt7h
DB_VERSION('19.1.0') switch tuning 3rhg88u6qnt7h
ALL_ROWS switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1") switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1_1") switch tuning 3rhg88u6qnt7h
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7)) switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1_2") switch tuning 3rhg88u6qnt7h
FULL(@"SEL$1_1" "Extent1"@"SEL$1") switch tuning 3rhg88u6qnt7h
INDEX_RS_ASC(@"SEL$1_2" "Extent1"@"SEL$1_2" ("LIS_LOG_INFECTION"."PAT_BARCODE")) switch tuning 3rhg88u6qnt7h
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "Extent1"@"SEL$1_2") switch tuning 3rhg88u6qnt7h
PQ_FILTER(@"SEL$1" SERIAL) switch tuning 3rhg88u6qnt7h
12 rows selected.
--//尝试使用以上全部提示执行sql语句,发现没有问题略.
--//我手工尝试使用sql profile,我发现2种情况,原来的语句可以使用正常执行.
1.删除 OUTLINE_LEAF(@"SEL$1") 提示.
2.加入BEGIN_OUTLINE_DATA,END_OUTLINE_DATA 提示,OK.
--//手工编写脚本,贴出删除 OUTLINE_LEAF(@"SEL$1") 提示的情况.
DECLARE
ar_profile_hints SYS.sqlprof_attr;
cl_sql_text CLOB;
BEGIN
SELECT SQL_FULLTEXT
INTO cl_sql_text
FROM -- replace with dba_hist_sqltext
-- if required for AWR based
-- execution
v$sql
-- sys.dba_hist_sqltext
WHERE sql_id = '&&3' AND child_number = &&4;
-- plan_hash_value = &&2;
DBMS_SQLTUNE.import_sql_profile (sql_text => cl_sql_text,
profile => sqlprof_attr(
'IGNORE_OPTIM_EMBEDDED_HINTS',
q'[OPTIMIZER_FEATURES_ENABLE('19.1.0')]',
q'[DB_VERSION('19.1.0')]',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1_1")',
'USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7))',
'OUTLINE_LEAF(@"SEL$1_2")',
'FULL(@"SEL$1_1" "Extent1"@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1_2" "Extent1"@"SEL$1_2" ("LIS_LOG_INFECTION"."PAT_BARCODE"))',
'BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "Extent1"@"SEL$1_2")',
'PQ_FILTER(@"SEL$1" SERIAL)'
),
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;
/
SYS@192.168.100.235:1521/orcl> @ spext 3rhg88u6qnt7h
HINT NAME
-------------------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 3rhg88u6qnt7h
OPTIMIZER_FEATURES_ENABLE('19.1.0') switch tuning 3rhg88u6qnt7h
DB_VERSION('19.1.0') switch tuning 3rhg88u6qnt7h
ALL_ROWS switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1_1") switch tuning 3rhg88u6qnt7h
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7)) switch tuning 3rhg88u6qnt7h
OUTLINE_LEAF(@"SEL$1_2") switch tuning 3rhg88u6qnt7h
FULL(@"SEL$1_1" "Extent1"@"SEL$1") switch tuning 3rhg88u6qnt7h
INDEX_RS_ASC(@"SEL$1_2" "Extent1"@"SEL$1_2" ("LIS_LOG_INFECTION"."PAT_BARCODE")) switch tuning 3rhg88u6qnt7h
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "Extent1"@"SEL$1_2") switch tuning 3rhg88u6qnt7h
PQ_FILTER(@"SEL$1" SERIAL) switch tuning 3rhg88u6qnt7h
11 rows selected.
--//很奇怪,以前使用这种sql profile的交换模式一直没有问题的.
--//最终我采用sql patch的方式完成优化:
SYS@192.168.100.235:1521/orcl> @ sqlpatch 3rhg88u6qnt7h 'USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7))'
input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_3rhg88u6qnt7h');
display sql path message , run @spext 3rhg88u6qnt7h
SYS@192.168.100.235:1521/orcl> @ spext 3rhg88u6qnt7h
HINT NAME
--------------------------------------- ------------------------------
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(7)) sqlpatch_3rhg88u6qnt7h
5.收尾:
--//修改sqlobj$auxdata,created 字段秒后有值的情况。 toad 下查看sql profile的界面存在一点点小问题,要清除
--//sqlobj$auxdata.created字段 秒后面的值.
--//update (select * from sqlobj$auxdata where created<>to_char(created,'yyyy-mm-dd hh24:mi:ss')) set created=to_char(created,'yyyy-mm-dd hh24:mi:ss');
--//update (select * from sqlobj$auxdata) set created=to_char(created,'yyyy-mm-dd hh24:mi:ss');
--//commit ;
--//主要目的便于使用toad管理.
6.附上执行脚本:
$ 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$sql
-- sys.dba_hist_sqltext
WHERE sql_id = '&&3' AND child_number = &&4;
-- 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;
/
$ 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;
$ cat sqlpatch.sql
prompt
prompt input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
prompt drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_&1');;
prompt display sql path message , run @spext &1
prompt
define noprint='noprint'
set term off
col tpt_version_old &noprint new_value _tpt_version_old
col tpt_version_new &noprint new_value _tpt_version_new
col tpt_noprint &noprint new_value _tpt_noprint
WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old
,CASE WHEN v > 11 THEN '' ELSE '--' END tpt_version_new
FROM version;
set term on
declare
v_sql CLOB;
patch_name VARCHAR2 (100);
begin
select sql_fulltext into v_sql from v$sql where sql_id='&1' and rownum=1;
&&_tpt_version_old sys.dbms_sqldiag_internal.i_create_patch(
&&_tpt_version_old sql_text => v_sql,
&&_tpt_version_old hint_text => '&2',
&&_tpt_version_old name => 'sqlpatch_&1');
&&_tpt_version_new patch_name :=
&&_tpt_version_new sys.DBMS_SQLDIAG.create_sql_patch
&&_tpt_version_new (
&&_tpt_version_new sql_text => v_sql
&&_tpt_version_new ,hint_text => '&2'
&&_tpt_version_new ,name => 'sqlpatch_&1'
&&_tpt_version_new );
end;
/
