[20230517]建立索引導致的性能問題2.txt

来源:https://www.cnblogs.com/lfree/archive/2023/05/28/17438783.html
-Advertisement-
Play Games

# 索引 索引用於幫助快速過濾或查找數據。 目前 Doris 主要支持兩類索引： • 內建的智能索引：包括首碼索引和 ZoneMap 索引。 • 用戶創建的二級索引：包括 Bloom Filter 索引 和 Bitmap倒排索引。 其中 ZoneMap 索引是在列存格式上，對每一列自動維護的索引信息 ...


[20230517]建立索引導致的性能問題2.txt

--//生產系統遭遇建立索引導致的性能問題,建立的sql profile裡面包含索引名提示,很少見,改索引名導致sql profile失效,
--//當然我遇到的情況有一點點不同,建立新索引,然後舊索引設置不可見(相當於改名),具體看下麵的測試環境模擬.

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

SCOTT@test01p> create table t1 as select rownum id1 ,rownum id2 ,rownum id3 ,lpad(rownum,10,'a') vc from dual connect by level<=1e4;
Table created.

--//建立函數索引,包括一個常量0.
SCOTT@test01p> create index ix_t1_id2 on t1(id2,0);
Index created.
--//註:ix_t1_id1 索引後面加入一個常量0,變成函數索引.

SCOTT@test01p> create index ix_t1_id3  on t1(id3);
Index created.

SCOTT@test01p> @gts t1 '' ''
Gather Table Statistics for table t1...
exec dbms_stats.gather_table_stats('SCOTT', 'T1', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
if lock table t1, add force=>true.
press ctrl+c cancel, enter continue...
PL/SQL procedure successfully completed.

2.測試:
$ cat tt1.txt
set term off
variable v_id2 number;
variable v_id3 number;
exec :v_id2 := 42;
exec :v_id3 := 42;
set term on
select vc from t1 where id2 = :v_id2 or id3 = :v_id3 ;

SCOTT@test01p> @ tt1.txt
VC
--------------------
aaaaaaaa42

SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  73jvxn4mk2mgw, child number 0
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 563811631
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |        |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      2 |    38 |     4  (25)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |           |        |       |            |          |
|   3 |    BITMAP OR                        |           |        |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |           |        |       |            |          |
|   5 |      SORT ORDER BY                  |           |        |       |            |          |
|*  6 |       INDEX RANGE SCAN              | IX_T1_ID2 |        |       |     2   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS   |           |        |       |            |          |
|*  8 |      INDEX RANGE SCAN               | IX_T1_ID3 |        |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 42
   2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("ID2"=:V_ID2)
       filter("ID2"=:V_ID2)
   8 - access("ID3"=:V_ID3)

Note
-----
   - 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
59 rows selected.
--//記下sql_id=73jvxn4mk2mgw,註意outline部分BITMAP_TREE那行，出現IX_T1_ID2。另外ID＝5出現1次sort order by。
--//我在http://blog.itpub.net/267265/viewspace-2952012/ => [20230512]優化的困惑19.txt 有解析。
--//使用sql profile穩定執行計劃.

SCOTT@test01p> @ spsw 73jvxn4mk2mgw 0 73jvxn4mk2mgw 0 '' true
PL/SQL procedure successfully completed.

=================================================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 73jvxn4mk2mgw')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 73jvxn4mk2mgw',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
--//驗證看看.輸出略,可以發現已經使用sql profile.

SCOTT@test01p> @ spext 73jvxn4mk2mgw
HINT                                                                   NAME
---------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS                                            switch tuning 73jvxn4mk2mgw
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')                                  switch tuning 73jvxn4mk2mgw
DB_VERSION('12.2.0.1')                                                 switch tuning 73jvxn4mk2mgw
ALL_ROWS                                                               switch tuning 73jvxn4mk2mgw
OUTLINE_LEAF(@"SEL$1")                                                 switch tuning 73jvxn4mk2mgw
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3")))  switch tuning 73jvxn4mk2mgw
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")                     switch tuning 73jvxn4mk2mgw
--//註意下劃線內容,包含索引名。

3.繼續:
--//普通索引
SCOTT@test01p> create index i_t1_id2 on t1(id2);
Index created.

SCOTT@test01p> ALTER INDEX ix_t1_id2 INVISIBLE;
Index altered.
--//設置ix_t1_id2 不可見。

SCOTT@test01p> @tt1.txt
VC
--------------------
aaaaaaaa42

SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  73jvxn4mk2mgw, child number 1
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |    14 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |      2 |    38 |    14   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 42
   2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID2"=:V_ID2 OR "ID3"=:V_ID3))
Note
-----
   - SQL profile switch tuning 73jvxn4mk2mgw 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
50 rows selected.
--//雖然note提示使用sql profile，但是實際上sql profile已經失效,12c這裡設置不好,19c以上有Hint Report,提示一些提示無效。執
--//行計劃變成了全表掃描。
--//可是我還建立普通索引啊，為什麼不用呢？

--//改名索引：
--//函數索引,並且還是不可見。
SCOTT@test01p> ALTER INDEX ix_t1_id2 rename to iy_t1_id2;
Index altered.

--//普通索引變成了ix_t1_id2.
SCOTT@test01p> ALTER INDEX i_t1_id2 rename to ix_t1_id2;
Index altered.

--//再次執行tt1.txt,執行計劃如下:
SCOTT@test01p> @ tt1.txt
VC
--------------------
aaaaaaaa42

SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  73jvxn4mk2mgw, child number 0
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 2540130847
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      2 |    38 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |           |        |       |            |          |
|   3 |    BITMAP OR                        |           |        |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |           |        |       |            |          |
|*  5 |      INDEX RANGE SCAN               | IX_T1_ID2 |        |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS   |           |        |       |            |          |
|*  7 |      INDEX RANGE SCAN               | IX_T1_ID3 |        |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3")))
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 42
   2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("ID2"=:V_ID2)
   7 - access("ID3"=:V_ID3)

Note
-----
   - SQL profile switch tuning 73jvxn4mk2mgw 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
--//註意下劃線，現在提示如下,並且註意沒有sort order by。而且索引提示是欄位而不是索引名.
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3")))
--//原來如下
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IX_T1_ID2" 2 ("T1"."ID3")))

--//可以發現當使用函數索引時，outline裡面是寫死的IX_T1_ID2，而普通索引沒有這個問題。
--//而我建立的sql profile裡面寫死了索引名。
--//一旦我改名索引就導致對應的sql profile失效。

SCOTT@test01p> ALTER INDEX ix_t1_id2 rename to iz_t1_id2;
Index altered.

--//再次執行tt1.txt,執行計劃變成全表掃描:
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |    14 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |      2 |    38 |    14   (0)| 00:00:01 |
---------------------------------------------------------------------------

--//刪除sql profile：
SCOTT@test01p> execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 73jvxn4mk2mgw')
PL/SQL procedure successfully completed.

SCOTT@test01p> @ tt1.txt
VC
--------------------
aaaaaaaa42

SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  73jvxn4mk2mgw, child number 0
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 3294346658
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      2 |    38 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |           |        |       |            |          |
|   3 |    BITMAP OR                        |           |        |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |           |        |       |            |          |
|*  5 |      INDEX RANGE SCAN               | IZ_T1_ID2 |        |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS   |           |        |       |            |          |
|*  7 |      INDEX RANGE SCAN               | IX_T1_ID3 |        |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 42
   2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("ID2"=:V_ID2)
   7 - access("ID3"=:V_ID3)
Note
-----
   - 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
57 rows selected.

--//重新建立sql profile：
SCOTT@test01p> @ spsw 73jvxn4mk2mgw 0 73jvxn4mk2mgw 0 '' true
PL/SQL procedure successfully completed.
=================================================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 73jvxn4mk2mgw')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 73jvxn4mk2mgw',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
--//驗證略。執行計劃outline部分如下：
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

SCOTT@test01p> @ spext 73jvxn4mk2mgw
HINT                                                                   NAME
---------------------------------------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS                                            switch tuning 73jvxn4mk2mgw
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')                                  switch tuning 73jvxn4mk2mgw
DB_VERSION('12.2.0.1')                                                 switch tuning 73jvxn4mk2mgw
ALL_ROWS                                                               switch tuning 73jvxn4mk2mgw
OUTLINE_LEAF(@"SEL$1")                                                 switch tuning 73jvxn4mk2mgw
BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 ("T1"."ID2") 2 ("T1"."ID3"))) switch tuning 73jvxn4mk2mgw
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")                     switch tuning 73jvxn4mk2mgw
7 rows selected.

SCOTT@test01p> @ ind2 t1
Display indexes where table or index name matches t1...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME  DSC
----------- ---------- ---------- ---- ------------ ----
SCOTT       T1         IX_T1_ID3     1 ID3
                       IY_T1_ID2     1 ID2
                                     2 SYS_NC00005$
                       IZ_T1_ID2     1 ID2

INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE    UNIQ STATUS               PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT
----------- ---------- ---------- ---------- ---- -------------------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SCOTT       T1         IX_T1_ID3  NORMAL     NO   VALID                NO   N     2         21         10000      10000         39 2023-05-16 20:30:03 1      VISIBLE
            T1         IY_T1_ID2  FBI NORMAL NO   VALID                NO   N     2         24         10000      10000         39 2023-05-16 20:29:40 1      INVISIBLE
            T1         IZ_T1_ID2  NORMAL     NO   VALID                NO   N     2         21         10000      10000         39 2023-05-16 20:44:09 1      VISIBLE

--//當前IY_T1_ID2(函數索引)INVISIBLE。IZ_T1_ID2(普通索引),VISIBLE.
SCOTT@test01p> ALTER INDEX iy_t1_id2 VISIBLE;
Index altered.

SCOTT@test01p> ALTER INDEX iz_t1_id2 inVISIBLE;
Index altered.

SCOTT@test01p> @ tt1.txt
VC
--------------------
aaaaaaaa42

1 row selected.

SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  73jvxn4mk2mgw, child number 0
-------------------------------------
select vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 713367141
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |        |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      2 |    38 |     4  (25)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |           |        |       |            |          |
|   3 |    BITMAP OR                        |           |        |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |           |        |       |            |          |
|   5 |      SORT ORDER BY                  |           |        |       |            |          |
--//出現SORT ORDER BY，說明使用函數索引。
|*  6 |       INDEX RANGE SCAN              | IY_T1_ID2 |        |       |     2   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS   |           |        |       |            |          |
|*  8 |      INDEX RANGE SCAN               | IX_T1_ID3 |        |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T1"@"SEL$1" OR(1 1 "IY_T1_ID2" 2 ("T1"."ID3"))) --//再次出現索引名。
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 42
   2 - :2 (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("ID2"=:V_ID2)
       filter("ID2"=:V_ID2)
   8 - access("ID3"=:V_ID3)

Note
-----
   - SQL profile switch tuning 73jvxn4mk2mgw 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
60 rows selected.
--//但是sql profile有效.

3.總結：
--//我僅僅想通過這個例子提醒自己如果通過類似交換方式穩定執行計劃時，註意生成的outline部分，
--//裡面一些提示會不會出現寫死的情況。這樣一個改名可能就導致sql profile失效。

--//收尾:
SCOTT@test01p> execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 73jvxn4mk2mgw')
PL/SQL procedure successfully completed.

4.附上spsw.sql和spext.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;
/

prompt =================================================================================================================================================
prompt if drop or alter sql profile ,run :
prompt execute dbms_sqltune.drop_sql_profile(name => 'switch tuning &&3')
prompt execute dbms_sqltune.alter_sql_profile(name => 'switch tuning &&3',attribute_name=>'STATUS',value=>'DISABLED')
prompt =================================================================================================================================================
prompt
prompt

$ 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;


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Unity的IPreprocessBuild：深入解析與實用案例
    # Unity IPreprocessBuild Unity IPreprocessBuild是Unity引擎中的一個非常有用的功能，它可以讓開發者在構建項目時自動執行一些操作。這個功能可以幫助開發者提高工作效率，減少手動操作的時間和錯誤率。在本文中我們將介紹Unity IPreprocessBui ...
  • Unity中的PostProcessBuild：深入解析與實用案例
    # Unity中的PostProcessBuild：深入解析與實用案例 在Unity游戲開發中，我們經常需要在構建完成後對生成的應用程式進行一些額外的處理。這時，我們可以使用Unity提供的`PostProcessBuild`功能。本文將詳細介紹Unity中的`PostProcessBuild`方法 ...
  • Unity中的InitializeOnLoad特性：深入解析與實踐
    # Unity中的InitializeOnLoad特性：深入解析與實踐 在Unity開發過程中，我們經常需要在編輯器啟動時或腳本重新編譯後執行一些操作，例如初始化數據、註冊事件等。這時，我們可以使用`InitializeOnLoad`特性來實現這一需求。本文將詳細介紹`InitializeOnLoa ...
  • 電賽控制類PID演算法實現
    **一、什麼是PID** 學過自動控制原理的對PID並不陌生，PID控制是對偏差信號e(t)進行比例、積分和微分運算變換後形成的一種控制規律。PID 演算法的一般形式： ![](https://img2023.cnblogs.com/blog/2776504/202305/2776504-202305 ...
  • 協程概述講解
    # 協程 > 線程分為用戶級線程，內核級線程和輕量級線程。Linux中使用的是輕量級線程，而協程雖然是運行線上程之上，但是是run在用戶空間。並且協程和線程一樣，擁有自己的調度器、cpu的上下文切換等。 > > 協程在我個人看來是一種用戶級線程； > > - 這是因為對於cpu有上下文的切換，而且是 ...
  • 《痞子衡嵌入式半月刊》 第 77 期
    # 痞子衡嵌入式半月刊： 第 77 期 ![](http://henjay724.com/image/cnblogs/pzh_mcu_bi_weekly.PNG) 這裡分享嵌入式領域有用有趣的項目/工具以及一些熱點新聞，農曆年分二十四節氣，希望在每個交節之日準時發佈一期。 本期刊是開源項目（GitH ...
  • 【原創】淺談EtherCAT主站EOE(上)-EOE網路
    Ethernet over EtherCAT (EoE) 是一種通信協議，它允許通過網路連接訪問從站設備的參數，以便配置和診斷從站設備。EoE 允許將常見的互聯網協議（如 HTTP、FTP 等）的數據插入到 EtherCAT 協議數據中，而不影響 EtherCAT 過程數據 ，那ETherCAT主站... ...
  • Oracle 死鎖與慢查詢總結
    ## 查看死鎖 ```sql SELECT s.sid "會話ID", s.lockwait "等待鎖", s.event "等待的資源/事件", -- 最近等待或正在等待的資源/事件 DECODE(lo.locked_mode, 0, '尚未獲得鎖', 1, NULL, 2, '行共用鎖', 3, ...
一周排行
    -Advertisement-
    Play Games
  • UOS 開啟 VisualStudio 遠程調試 .NET 應用之旅
    前言 在我們開發過程中基本上不可或缺的用到一些敏感機密數據，比如SQL伺服器的連接串或者是OAuth2的Secret等，這些敏感數據在代碼中是不太安全的，我們不應該在源代碼中存儲密碼和其他的敏感數據，一種推薦的方式是通過Asp.Net Core的機密管理器。 機密管理器 在 ASP.NET Core ...
  • C# - 能否讓 SortedSet.RemoveWhere 內傳入的委托非同步執行
    新改進提供的Taurus Rpc 功能，可以簡化微服務間的調用，同時可以不用再手動輸出模塊名稱，或調用路徑，包括負載均衡，這一切，由框架實現並提供了。新的Taurus Rpc 功能，將使得服務間的調用，更加輕鬆、簡約、高效。 ...
  • 順序棧的介面程式
    順序棧的介面程式 目錄順序棧的介面程式頭文件創建順序棧入棧出棧利用棧將10進位轉16進位數驗證 頭文件 #include <stdio.h> #include <stdbool.h> #include <stdlib.h> 創建順序棧 // 指的是順序棧中的元素的數據類型，用戶可以根據需要進行修改 ...
  • mORMot2 獲取數據集1
    前言 整理這個官方翻譯的系列，原因是網上大部分的 tomcat 版本比較舊，此版本為 v11 最新的版本。 開源項目 從零手寫實現 tomcat minicat 別稱【嗅虎】心有猛虎，輕嗅薔薇。 系列文章 web server apache tomcat11-01-官方文檔入門介紹 web serv ...
  • 萬字長文總結與剖析C語言關鍵字 -- <<C語言深度解剖>>
    C總結與剖析:關鍵字篇 -- <<C語言深度解剖>> 目錄C總結與剖析:關鍵字篇 -- <<C語言深度解剖>>程式的本質:二進位文件變數1.變數:記憶體上的某個位置開闢的空間2.變數的初始化3.為什麼要有變數4.局部變數與全局變數5.變數的大小由類型決定6.任何一個變數,記憶體賦值都是從低地址開始往高地 ...
  • 【Flink入門修煉】2-3 Flink Checkpoint 原理機制
    如果讓你來做一個有狀態流式應用的故障恢復，你會如何來做呢？ 單機和多機會遇到什麼不同的問題？ Flink Checkpoint 是做什麼用的？原理是什麼？ ...
  • C++ 多級繼承與多重繼承：代碼組織與靈活性的平衡
    C++ 多級繼承 多級繼承是一種面向對象編程（OOP）特性，允許一個類從多個基類繼承屬性和方法。它使代碼更易於組織和維護，並促進代碼重用。 多級繼承的語法 在 C++ 中，使用 : 符號來指定繼承關係。多級繼承的語法如下： class DerivedClass : public BaseClass1 ...
  • 圖文結合手把手教你創建SpringCloud項目
    前言 什麼是SpringCloud？ Spring Cloud 是一系列框架的有序集合，它利用 Spring Boot 的開發便利性簡化了分散式系統的開發，比如服務註冊、服務發現、網關、路由、鏈路追蹤等。Spring Cloud 並不是重覆造輪子，而是將市面上開發得比較好的模塊集成進去，進行封裝，從 ...
  • 類模板的常見用法
    class_template 類模板和函數模板的定義和使用類似，我們已經進行了介紹。有時，有兩個或多個類，其功能是相同的，僅僅是數據類型不同。類模板用於實現類所需數據的類型參數化 template<class NameType, class AgeType> class Person { publi ...
  • system-v IPC共用記憶體通信
    目錄system v IPC簡介共用記憶體需要用到的函數介面shmget函數--獲取對象IDshmat函數--獲得映射空間shmctl函數--釋放資源共用記憶體實現思路註意 system v IPC簡介 消息隊列、共用記憶體和信號量統稱為system v IPC(進程間通信機制)，V是羅馬數字5，是UNI ...
所有分類