[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;
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2917491/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181119]使用sql profile優化問題.txtSQL優化
- [20220324]toad與sql profile使用問題.txtSQL
- sql最佳化：使用sql profile最佳化sql語句SQL
- 使用SQL Profile進行SQL最佳化案例SQL
- SQL最佳化問題SQL
- cursor_sharing=force導致sql profile部分hint失效SQL
- css失效問題CSS
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- sql profileSQL
- MyBatis order by失效問題MyBatis
- 外掛失效問題
- 對sql語句的最佳化問題SQL
- sql profile使用SQL
- [20221101]tmux使用問題copy和paste失效.txtUXAST
- [20130109]SPM與sql profile.txtSQL
- ORACLE profile 最佳化配置Oracle
- [20190221]sql patch 問題.txtSQL
- SQL最佳化案例-從執行計劃定位SQL問題（三）SQL
- [20131122]跟蹤sql profile的操作.txtSQL
- 探究 position-sticky 失效問題
- kubernetesgraceperiod失效問題排查
- .gitignore 失效問題解決Git
- ORACLE SQL PROFILE使用OracleSQL
- SQL PROFILE 測試SQL
- sql profile的使用SQL
- [20230110]sql profile run standby database.txtSQLDatabase
- [20180301]sql profile 非繫結變數.txtSQL變數
- [20151201]備份遷移sql profile.txtSQL
- SQL最佳化 | MySQL問題處理案例分享三則MySql
- sql tuning task和sql profileSQL
- spring data mongo @Field 失效問題SpringGo
- IOS margin-bottom失效問題iOS
- 如何解決快取失效問題快取
- JDBC連線MySQL失效的問題JDBCMySql
- 再談量化策略失效的問題
- [20150611]優化sql遇到問題.txt優化SQL
- SQL Server profile使用技巧SQLServer
- SQL Profile 實驗03SQL