[20221008]sql profile优化失效问题.txt

来源：这里教程网时间：2026-03-03 17:57:38 作者：

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

编辑推荐：

返回列表

[20221008]sql profile优化失效问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推