主頁 > 資料庫 > [20230517]建立索引導致的性能問題2.txt

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

2023-05-29 09:18:08 資料庫

[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=https://www.cnblogs.com/lfree/p/>'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=https://www.cnblogs.com/lfree/p/>'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 = https://www.cnblogs.com/lfree/p/&&2;

   DBMS_SQLTUNE.import_sql_profile (sql_text      => cl_sql_text,
                                    profile       => ar_profile_hints,
                                    category      => '&&5',
                                    DESCRIPTION   => 'switch &&1 => &&3',
                                    name          => 'switch tuning &&3' -- use force_match => true
                                                                         -- to use CURSOR_SHARING=SIMILAR
                                                                         -- behaviour, i.e. match even with
                                                                         -- differing literals
                                    ,
                                    force_match   => &&6);
END;
/

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=https://www.cnblogs.com/lfree/p/>'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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/553675.html

標籤：Oracle

上一篇：快速上手kettle(二)小試牛刀

下一篇：返回列表

標籤雲
其他(159897) Python(38178) JavaScript(25460) Java(18141) C(15232) 區塊鏈(8268) C＃(7972) AI(7469) 爪哇(7425) MySQL(7214) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5873) 数组(5741) R(5409) Linux(5343) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4577) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2434) ASP.NET(2403) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) .NET技术(1977) 功能(1967) Web開發(1951) HtmlCss(1949) C++(1924) python-3.x(1918) 弹簧靴(1913) xml(1889) PostgreSQL(1878) .NETCore(1862) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • [20230517]建立索引導致的性能問題2.txt

    [20230517]建立索引導致的性能問題2.txt--//生產系統遭遇建立索引導致的性能問題,建立的sql profile里面包含索引名提示,很少見,改索引名導致sql profile失效,--//當然我遇到的情況有一點點不同,建立新索引,然后舊索引設定不可見(相當于改名),具體看下面的測驗環境模 ......

    uj5u.com 2023-05-29 09:18:08 more
  • 快速上手kettle(二)小試牛刀

    [TOC](快速上手kettle二 小試牛刀) ### 一 、前言 上一期中大概介紹了下kettle，并已經把kettle下載安裝完了。 這一期我們就來簡單體驗下kettle怎么進行資料轉換的。 ### 二 、兩個小目標 我們這里呢就以兩個小案例來體驗下kettle - 將csv檔案通過kettle ......

    uj5u.com 2023-05-29 09:16:51 more
  • Doris(三) -- 索引

    # 索引 索參考于幫助快速過濾或查找資料。 目前 Doris 主要支持兩類索引： ? 內建的智能索引：包括前綴索引和 ZoneMap 索引。 ? 用戶創建的二級索引：包括 Bloom Filter 索引 和 Bitmap倒排索引。 其中 ZoneMap 索引是在列存格式上，對每一列自動維護的索引資訊 ......

    uj5u.com 2023-05-29 09:16:10 more
  • Doris(二) -- 基本概念和資料表模型

    # 欄位型別 | 資料型別 | 位元組 | 范圍 | | | | | |TINYINT|1 位元組|-2^7 + 1 ~ 2^7 - 1| |SMALLINT|2 位元組|-2^15 + 1 ~ 2^15 - 1| |INT|4 位元組|-2^31 + 1 ~ 2^31 - 1| |BIGINT|8 位元組| ......

    uj5u.com 2023-05-28 08:37:01 more
  • GaussDB(DWS)條件運算式函式回傳錯誤結果集排查

    摘要：條件運算式函式中出現結果集不一致問題，我們首先要考慮是否入參資料型別不一致導致出參不一致。 本文分享自華為云社區《GaussDB(DWS)條件運算式函式回傳錯誤結果集排查》，作者：yd_211369925 。 （一）案例背景 客戶使用greatest獲取并回傳引數串列中值最大的運算式的值，子查 ......

    uj5u.com 2023-05-28 08:31:42 more
  • GaussDB(DWS)條件運算式函式回傳錯誤結果集排查

    摘要：條件運算式函式中出現結果集不一致問題，我們首先要考慮是否入參資料型別不一致導致出參不一致。 本文分享自華為云社區《GaussDB(DWS)條件運算式函式回傳錯誤結果集排查》，作者：yd_211369925 。 （一）案例背景 客戶使用greatest獲取并回傳引數串列中值最大的運算式的值，子查 ......

    uj5u.com 2023-05-28 08:30:40 more
  • Doris(二) -- 基本概念和資料表模型

    # 欄位型別 | 資料型別 | 位元組 | 范圍 | | | | | |TINYINT|1 位元組|-2^7 + 1 ~ 2^7 - 1| |SMALLINT|2 位元組|-2^15 + 1 ~ 2^15 - 1| |INT|4 位元組|-2^31 + 1 ~ 2^31 - 1| |BIGINT|8 位元組| ......

    uj5u.com 2023-05-28 08:30:04 more
  • SQL進階教程讀后總結與感想

    ![](https://img2023.cnblogs.com/blog/3076680/202305/3076680-20230516151258933-1445766867.png) # 1. 基本資訊 SQL進階教程 [日]MICK 人民郵電出版社,2017年11月出版，**1版** ## 1 ......

    uj5u.com 2023-05-27 08:07:21 more
  • Doris(一) -- 簡介和安裝

    ## Doris 簡介 ### Doris 概述 Apache Doris 由百度大資料部研發 (之前叫百度 Palo，2018 年貢獻到 Apache 社區后，更名為 Doris), 在百度內部，有超過 200 個產品線在使用，部署機器超過 1000 臺，單一業務最大可達到上百 TB。 Apach ......

    uj5u.com 2023-05-27 08:07:12 more
  • 一文教會你用Apache SeaTunnel Zeta離線把資料從MySQL同步到Star

    我們介紹一下SeaTunnel支持的第一個同步場景：離線批量同步。顧名思意，離線批量同步需要用戶定義好SeaTunnel JobConfig，選擇批處理模式，作業啟動后開始同步資料，當資料同步完成后作業完成退出。 ......

    uj5u.com 2023-05-27 08:06:59 more

Copyright © 2010-2020 有解無憂