[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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類