[20221008]sql profile優化失效問題.txt
--//生產系統一條sql陳述句存在性能問題,使用sql profile優化固定執行計劃,再次出現問題,以前也遇到類似的問題,做一個記錄.
1.環境:
[email protected]: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
[email protected]: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) );
--//查看系結變數的值:
[email protected]: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型別，
[email protected]: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:
--//生成執行腳本,執行如下：
[email protected]:1521/orcl> @ b9d 3rhg88u6qnt7h 0
--//加入如下提示/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(&&1)) */,執行測驗腳本：
[email protected]: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出現問題:
[email protected]:1521/orcl> @ spsw 6svzht02nz53p 0 3rhg88u6qnt7h 1 '' true
PL/SQL procedure successfully completed.
--//再次查看執行計劃發現無法正常使用我建立的提示.結果我不貼出了.
[email protected]: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 = https://www.cnblogs.com/lfree/p/&&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;
/
[email protected]: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的方式完成優化:
[email protected]: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
[email protected]: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 = https://www.cnblogs.com/lfree/p/&&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;
/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/513128.html
標籤：Oracle
上一篇：day09-2視圖和用戶權限