[20221130]最佳化備庫dg遇到的問題2.txt

lfree發表於2022-12-02

[20221130]最佳化備庫dg遇到的問題2.txt

--//生產系統一些語句執行慢,開發或者同事移動到備庫執行,我發現實際這些語句大部分都是由於選擇錯誤的索引導致的情況.
--//我想透過sql profile在主庫來穩定執行計劃,這樣備庫也可以使用sql profile選擇合理的執行計劃.但是我遇到一些問題,做一些記
--//錄.

1.環境:
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
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.問題語句:
SYS@192.168.100.237:1521/orcldg> @ dpc bu48z014njcg4 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bu48z014njcg4, child number 0
-------------------------------------
 select   case a.hosp_id when 141 then
1 when 181 then 3 when 201 then 2 end  hosp_code,a.id
test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode
  a.pat_barcode pat_id,''INPATIENT_ID,''
CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age
,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLO
    a.depart_name ,a.area_code ,a.area_name
,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS,
a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,''
    ''_STATUS_NAME,'' SAMPLING_POSITION,
    '' SAMPLE_CHARGE,'''' TEST_ORDER,
    _COUNT,''WORKLOAD, a.test_user_name, a.print_user_name,
a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name
,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,
   a.audit_user_name ,
to_char( a.audit_time,'yyyy-mm-dd hh
Plan hash value: 86349049
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                        |        |       |    13 (100)|          |       |       |          |
|   1 |  SORT ORDER BY                           |                        |      1 |   368 |            |          | 52224 | 52224 |47104  (0)|
|   2 |   HASH UNIQUE                            |                        |      1 |   368 |    13   (8)| 00:00:01 |   714K|   714K| 1390K (0)|
|*  3 |    FILTER                                |                        |        |       |            |          |       |       |          |
|   4 |     NESTED LOOPS                         |                        |      1 |   368 |    12   (0)| 00:00:01 |       |       |          |
|   5 |      NESTED LOOPS OUTER                  |                        |      1 |   362 |     9   (0)| 00:00:01 |       |       |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST               |      1 |   281 |     6   (0)| 00:00:01 |       |       |          |
|*  7 |        INDEX RANGE SCAN                  | IX_LIS_TEST_ORDER_TIME |      3 |       |     3   (0)| 00:00:01 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| LIS_PROMPT             |      1 |    81 |     3   (0)| 00:00:01 |       |       |          |
|*  9 |        INDEX RANGE SCAN                  | IX_LIS_PROMPT_TEST_ID  |      1 |       |     2   (0)| 00:00:01 |       |       |          |
|* 10 |      INDEX RANGE SCAN                    | PK_LIS_RESULT          |     10 |    60 |     3   (0)| 00:00:01 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$4439918E
   6 - SEL$4439918E / A@SEL$2
   7 - SEL$4439918E / A@SEL$2
   8 - SEL$4439918E / C@SEL$3
   9 - SEL$4439918E / C@SEL$3
  10 - SEL$4439918E / B@SEL$2
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (CHAR(30), CSID=852): '91237433'
   2 - :2 (CHAR(30), CSID=852): '452127198107122110'
   3 - (CHAR(30), CSID=852): '2022-11-18'
   4 - (CHAR(30), CSID=852): '2022-11-24'
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(TO_DATE(:STR_DTTO,'yyyy-MM-dd')>=TO_DATE(:STR_DTFROM,'yyyy-MM-dd'))
   6 - filter((("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH) OR "A"."PAT_ID"=SYS_OP_C2C(:STR_MZHM)) AND "A"."STATE">=60))
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   7 - access("A"."ORDER_TIME">=TO_DATE(:STR_DTFROM,'yyyy-MM-dd') AND "A"."ORDER_TIME"<=TO_DATE(:STR_DTTO,'yyyy-MM-dd'))
   9 - access("A"."ID"="C"."TEST_ID")
  10 - access("A"."ID"="B"."ID")

--//選擇錯誤的索引IX_LIS_TEST_ORDER_TIME,應該選擇IDENTITY_ID以及PAT_ID索引,首先這些索引是存在的.
--//但是我首先遇到奇怪的問題.

3.生成執行指令碼:
SYS@192.168.100.237:1521/orcldg> @ b9  bu48z014njcg4 0
variable STR_MZHM VARCHAR2(32)
variable STR_SFZH VARCHAR2(128)
variable STR_DTFROM VARCHAR2(32)
variable STR_DTTO VARCHAR2(32)
begin
:STR_MZHM := '91209282';
:STR_SFZH := '450802198909083618';
:STR_DTFROM := '2022-11-07';
:STR_DTTO := '2022-11-29';
null;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=FINDREPORT;
alter session set statistics_level=all;

select distinct re.*
 from (

select   case a.hosp_id when 141 then 1 when 181 then 3 when 201 then 2 end  hosp_code,a.id test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode ,a.pat_type_name ,
 a.pat_barcode pat_id,''INPATIENT_ID,'' CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age ,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLOODTYPE_RH,a.depart_code ,
   a.depart_name ,a.area_code ,a.area_name ,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS,
 a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,'' SAMPLE_STATUS_NAME,'' SAMPLING_POSITION,

   '' SAMPLING_POSITION_NAME,'' TEST_ORDER,
   '' SAMPLE_CHARGE,'' ITEM_COUNT,''WORKLOAD, a.test_user_name, a.print_user_name,

 a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,
 a.sign_user_name ,
 ''INSPECTION_PERSON,
  a.audit_user_name ,  to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON,
a.print_time ,''PRINT_COUNT,''EXPERT_RULE,''CHARGE_STATE,''INSPECTION_STATE,''RERUN_STATE,''READ_STATE,
dbms_lob.substr(VERDICT)  order_remark,
''REMARK_NAME,''ID_CARD,''CONFIRM_PERSON,''CONFIRM_TIME,''PATIENT_TYPE_OLD,''INSTRUMENT,''MOBILENO,''SEND_STATE,''SEND_DATE,''PATIENT_BIRTHDAY,a.report_type ,a.id RESID,
case when a.state=90 then '已報告' when a.state=60 then '正在檢驗' when a.state=100 then '已列印' else '' end INSPECTIONSTATE,''YCBS,''runow

from lis_test a
inner join lis_result b on   a.id=b.id
left join lis_prompt c on  a.id=c.test_id
where (  a.pat_id= :str_mzhm or a.identity_id= :str_sfzh  )
and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd')
and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd')
and a.state >=60
)re order by re.ORDER_TIME;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;

--//而當我執行生成的指令碼時,我發現裡面記錄的sql_id不是bu48z014njcg4,而是76wprdhujxj54,怎麼可能不同呢?
--//首先我不可能在備庫執行spsw.sql指令碼來交換執行計劃來穩定提高效能,因為要在read write模式,我必須在主庫執行.
--//而sql_id=bu48z014njcg4的語句並不存在主庫,主庫執行後僅僅存在sql_id=76wprdhujxj54的語句.
--//這樣我在主庫執行
--//@ spsw good_sql_id 0 76wprdhujxj54 0 '' true 是否到備庫sql_id=bu48z014njcg4可以應用.
--//注:最後我的測試可以.只要最後一個引數(第6個引數)為true.

--//我執行的指令碼b9.sql在這個語句中僅僅過濾掉裡面\r字元,使用replace替換chr(13)=>''.看來以後給修改指令碼程式碼改成替換為空格!!

SYS@192.168.100.237:1521/orcldg> select lengthb(to_char(sql_fulltext)),length(SQL_FULLTEXT), ora_hash(sql_fulltext),sql_id  from v$sqlarea where sql_id in ('bu48z014njcg4','76wprdhujxj54');
LENGTHB(TO_CHAR(SQL_FULLTEXT)) LENGTH(SQL_FULLTEXT) ORA_HASH(SQL_FULLTEXT) SQL_ID
------------------------------ -------------------- ---------------------- -------------
                          1816                 1806             3132263513 bu48z014njcg4
                          1777                 1767             2094332972 76wprdhujxj54
--//注:應該使用lengthb函式檢視長度,我開始在這裡犯錯.因為語句裡面有漢字.10個漢字多了10個長度.
--//1816-1777 = 39.兩組存在39個字元的差距.

--//在toad下執行:
select length(SQL_FULLTEXT), ora_hash(sql_fulltext),sql_id ,sql_fulltext from v$sqlarea where sql_id in ('bu48z014njcg4','76wprdhujxj54');

--//在toad下分別取出sql_fulltext輸出,分別對應的儲存為d1.bu48z014njcg4,d2.76wprdhujxj54檔案.
$ ls -l d1.bu48z014njcg4 d2.76wprdhujxj54
-rw-r--r-- 1 oracle oinstall 1790 2022-11-30 10:16:32 d1.bu48z014njcg4
-rw-r--r-- 1 oracle oinstall 1778 2022-11-30 10:16:52 d2.76wprdhujxj54
--//d1.bu48z014njcg4,d2.76wprdhujxj54有12個字元上的差異.
--//1816-1790 = 26,這26個字元應該是\r字元.

$ diff  d1.bu48z014njcg4 d2.76wprdhujxj54 | cat -Ev
8,9c8,9$
<  $
<    '' SAMPLING_POSITION_NAME,'' TEST_ORDER, $
---$
> $
>    '' SAMPLING_POSITION_NAME,'' TEST_ORDER,$
11,12c11,12$
<    $
<  a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time , $
---$
> $
>  a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,$
14,15c14,15$
<  ''INSPECTION_PERSON, $
<   a.audit_user_name ,  to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON, $
---$
>  ''INSPECTION_PERSON,$
>   a.audit_user_name ,  to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON,$
21,22c21,22$
< from lis_test a $
< inner join lis_result b on   a.id=b.id $
---$
> from lis_test a$
> inner join lis_result b on   a.id=b.id$
25,26c25,26$
< and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd') $
< and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd') $
---$
> and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd')$
> and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd')$
--//看上去一樣的,cat -E可以在結尾加入$,實際上有一些行結尾處有一些行包含空格.也就是差異的12個字元實際上空格.換一句話將
--//b9.sql指令碼的輸出時結尾的空格丟失了.

--//我的預設設定:
--//set trimout on;   -- 去除標準輸出每行的行尾空格(預設為off)
--//set trimspool on; -- 去除spool輸出結果中每行的結尾空格(預設為off)
SYS@192.168.100.237:1521/orcldg> show trimspool
trimspool ON
SYS@192.168.100.237:1521/orcldg> show trimout
trimout ON
--//我測試了set trimout off好像也不行呢?.另外寫blog說明問題.

--//如果加入-b引數,兩者一致.  
--// -b     Ignore changes in amount of white space
--// -w     Ignore white space when comparing lines.
$ diff -b d1.bu48z014njcg4 d2.76wprdhujxj54
$ diff -w d1.bu48z014njcg4 d2.76wprdhujxj54

$ wc d1.bu48z014njcg4 d2.76wprdhujxj54
  28  133 1790 d1.bu48z014njcg4
  28  133 1778 d2.76wprdhujxj54
  56  266 3568 total

--//我仔細在toad下看了sql_fulltext的dump發現bu48z014njcg4每行結尾有\r字元.這樣
--//1816-1790 = 26,應該多27個才對啊(最好1行不算).怎麼差1個字元呢?
--//仔細看76wprdhujxj54,lengthb輸出的長度是1777,d2.76wprdhujxj54的檔案大小1778,也是差1,這樣就很好理解了,因為儲存檔案
--//時最後一行結尾存在\n字元,多1個是正常的.
--//這樣就完成對上了,我的b9指令碼輸出過濾chr(13)字元(\r),並且因為sqlplus的輸出少了12個空格.
--//27  chr(13) + 12 空格 = 39.
--//1816-1777 = 39.這樣正好能對上了.

--//不過即使我修改生成的指令碼加入\r字元在sqlplus結尾,在sqlplus執行的sql_id還是76wprdhujxj54.
--//順便做一個記錄vim下在結尾加入^M的方法,打入:'a,'bs/$/\^M/
--//前面的\不能缺少,不然文字僅僅全部多一個空行.
--//^M 輸入 ctrl+q ctrl+m (for windwos) 或者 ctrl+v ctrl+m ( for linux)

--//不過最後我發現我自己多慮了,實際上在主庫執行如下:
--//先執行bu48z014njcg4.sql9_0 => sql_id=76wprdhujxj54
--//加入提示:  BITMAP_TREE(@"SEL$4439918E" "A"@"SEL$2" OR(1 2 "IX_LIS_TEST_IDENTITY_ID" 1 ("LIS_TEST"."PAT_ID")))
--//再執行bu48z014njcg4.sql9_0 -> sql_id=86uxv50rvtb3x

SYS@192.168.100.235:1521/orcl> @ spsw 86uxv50rvtb3x 0 76wprdhujxj54 0 '' true
PL/SQL procedure successfully completed.
--//主要第6個引數等於true,就沒有問題.

SYS@192.168.100.235:1521/orcl> 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');
1 row updated.

SYS@192.168.100.235:1521/orcl> commit ;
Commit complete.
--//為了在toad下維護sql profile.

--//然後在備庫執行或者檢視sql_id=bu48z014njcg4的執行計劃,會使用對應的sql profile.
SYS@192.168.100.237:1521/orcldg> @ dpc bu48z014njcg4 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bu48z014njcg4, child number 1
-------------------------------------
 select   case a.hosp_id when 141 then
1 when 181 then 3 when 201 then 2 end  hosp_code,a.id
test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode
  a.pat_barcode pat_id,''INPATIENT_ID,''
CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age
,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLO
    a.depart_name ,a.area_code ,a.area_name
,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS,
a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,''
    ''_STATUS_NAME,'' SAMPLING_POSITION,
    '' SAMPLE_CHARGE,'''' TEST_ORDER,
    _COUNT,''WORKLOAD, a.test_user_name, a.print_user_name,
a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name
,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,
   a.audit_user_name ,
to_char( a.audit_time,'yyyy-mm-dd hh

Plan hash value: 518087822

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |        |       |    21 (100)|          |       |       |          |
|   1 |  SORT ORDER BY                           |                         |      1 |   368 |    21  (15)| 00:00:01 | 74752 | 74752 |65536  (0)|
|   2 |   HASH UNIQUE                            |                         |      1 |   368 |    20  (10)| 00:00:01 |   715K|   715K| 1388K (0)|
|*  3 |    FILTER                                |                         |        |       |            |          |       |       |          |
|   4 |     NESTED LOOPS                         |                         |      1 |   368 |    19   (6)| 00:00:01 |       |       |          |
|   5 |      NESTED LOOPS OUTER                  |                         |      1 |   362 |    16   (7)| 00:00:01 |       |       |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST                |      1 |   281 |    13   (8)| 00:00:01 |       |       |          |
|   7 |        BITMAP CONVERSION TO ROWIDS       |                         |        |       |            |          |       |       |          |
|   8 |         BITMAP OR                        |                         |        |       |            |          |       |       |          |
|   9 |          BITMAP CONVERSION FROM ROWIDS   |                         |        |       |            |          |       |       |          |
|  10 |           SORT ORDER BY                  |                         |        |       |            |          | 73728 | 73728 |          |
|* 11 |            INDEX RANGE SCAN              | IX_LIS_TEST_IDENTITY_ID |        |       |     4   (0)| 00:00:01 |       |       |          |
|  12 |          BITMAP CONVERSION FROM ROWIDS   |                         |        |       |            |          |       |       |          |
|* 13 |           INDEX RANGE SCAN               | IX_LIS_TEST_PAT_ID      |        |       |     3   (0)| 00:00:01 |       |       |          |
|  14 |       TABLE ACCESS BY INDEX ROWID BATCHED| LIS_PROMPT              |      1 |    81 |     3   (0)| 00:00:01 |       |       |          |
|* 15 |        INDEX RANGE SCAN                  | IX_LIS_PROMPT_TEST_ID   |      1 |       |     2   (0)| 00:00:01 |       |       |          |
|* 16 |      INDEX RANGE SCAN                    | PK_LIS_RESULT           |     10 |    60 |     3   (0)| 00:00:01 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$4439918E
   6 - SEL$4439918E / A@SEL$2
  14 - SEL$4439918E / C@SEL$3
  15 - SEL$4439918E / C@SEL$3
  16 - SEL$4439918E / B@SEL$2

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (CHAR(30), CSID=852): '91240481'
   2 - :2 (CHAR(30), CSID=852): '452128198512081523'
   3 - (CHAR(30), CSID=852): '2022-11-19'
   4 - (CHAR(30), CSID=852): '2022-12-01'

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(TO_DATE(:STR_DTTO,'yyyy-MM-dd')>=TO_DATE(:STR_DTFROM,'yyyy-MM-dd'))
   6 - filter(("A"."ORDER_TIME">=TO_DATE(:STR_DTFROM,'yyyy-MM-dd') AND "A"."ORDER_TIME"<=TO_DATE(:STR_DTTO,'yyyy-MM-dd') AND
              "A"."STATE">=60))
  11 - access("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH))
       filter("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH))
  13 - access("A"."PAT_ID"=SYS_OP_C2C(:STR_MZHM))
  15 - access("A"."ID"="C"."TEST_ID")
  16 - access("A"."ID"="B"."ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 17 (U - Unused (1))
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  ALL_ROWS
           -  DB_VERSION('19.1.0')
           -  IGNORE_OPTIM_EMBEDDED_HINTS
           -  OPTIMIZER_FEATURES_ENABLE('19.1.0')

   0 -  SEL$2
           -  MERGE(@"SEL$2" >"SEL$006708EA")

   0 -  SEL$98196233
           -  ANSI_REARCH(@"SEL$3")
           -  MERGE(@"SEL$98196233" >"SEL$1")

   0 -  SEL$C97233AF
           -  MERGE(@"SEL$C97233AF" >"SEL$E6E74641")

   1 -  SEL$4439918E
           -  ANSI_REARCH(@"SEL$4")
           -  LEADING(@"SEL$4439918E" "A"@"SEL$2" "C"@"SEL$3" "B"@"SEL$2")

   6 -  SEL$4439918E / A@SEL$2
         U -  BITMAP_TREE(@"SEL$4439918E" "A"@"SEL$2" OR(1 2 "IX_LIS_TEST_IDENTITY_ID" 1 ("LIS_TEST"."PAT_ID")))
           -  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$4439918E" "A"@"SEL$2")

  14 -  SEL$4439918E / C@SEL$3
           -  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$4439918E" "C"@"SEL$3")
           -  INDEX_RS_ASC(@"SEL$4439918E" "C"@"SEL$3" ("LIS_PROMPT"."TEST_ID"))
           -  USE_NL(@"SEL$4439918E" "C"@"SEL$3")

  16 -  SEL$4439918E / B@SEL$2
           -  INDEX(@"SEL$4439918E" "B"@"SEL$2" ("LIS_RESULT"."ID" "LIS_RESULT"."ITEM_ID"))
           -  USE_NL(@"SEL$4439918E" "B"@"SEL$2")

Note
-----
   - SQL profile switch tuning 76wprdhujxj54 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
116 rows selected.

3.附上spsw.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;
/

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2926312/,如需轉載,請註明出處,否則將追究法律責任。

相關文章