1. Toy模板网首页
  2. > Toy博客

[20230517]建立索引导致的性能问题2.txt

8月前 作者：lfree 分类：Toy博客 阅读(34) 违法举报

这篇具有很好参考价值的文章主要介绍了[20230517]建立索引导致的性能问题2.txt。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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

文章来源地址https://www.toymoban.com/news/detail-462421.html

到了这里，关于[20230517]建立索引导致的性能问题2.txt的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • 解决Oracle SQL语句性能问题——合理使用索引

    ​​​​​​1. 合理使用索引 索引对关系库SQL调优来说，其重要性怎么强调也不会过分。为何这么说呢？因为对SQL调优来讲，最终目的就是通过减少SQL语句对系统资源的消耗来达到优化的目的，而索引又是缩减SQL语句资源消耗的最主要手段。当然，你也可以说，还可以通过

    2024年02月08日
    浏览(33)
  • 什么原因导致百度百科建立一直审核不通过？

    什么原因导致百度百科建立一直审核不通过？

    百科词条对网络营销实在是太重要了，不管是个人还是企业想在网上开展业务，都必要建立百科词条。自己动手编辑百科词条，搞个几十次也审核不过的情况比比皆是。 为什么百度百科总是审核不通过？百度官方发表过声明表示百度百科词条是人人都可以编辑的，并且都是免

    2024年02月19日
    浏览(33)
  • BoostCompass(建立正排索引和倒排索引模块)

    BoostCompass(建立正排索引和倒排索引模块)

    这个模块我们定义了一个名为 Index 的C++类，用于构建和维护一个文档索引系统。该系统采用单例模式确保只有一个索引实例，并使用正排索引和倒排索引来快速检索文档。正排索引存储了文档的基本信息，如标题、内容和URL，而倒排索引则根据将文档分组。类中提供了

    2024年04月13日
    浏览(34)
  • 【数据处理】建立数据库索引并定时重建索引

    【数据处理】建立数据库索引并定时重建索引

    给表 建立索引能加速查询 （我的习惯是给经常查询的列建立索引，如果经常查询的是id列，我会给将id设置为主键），长时间查询后会变慢（具体原因目前不清楚），公司前辈说 定期重建索引就可以解决问题 ，我就在 Microsoft Sql Server Management Studio 里设置了定时“自动重建索

    2024年01月25日
    浏览(37)
  • 如何建立含有逻辑删除字段的唯一索引

    如何建立含有逻辑删除字段的唯一索引

    业务场景 分析 解决 总结 在实际工作当中，遇到一个场景，就是在用户注册时，名字要全局唯一，当然，我们是可以对用户进行删除的，你会怎么去做？ 一般来说，我们可以在用户注册请求时，进行查库校验，看看名字是否已经存在，如果存在就抛异常给提示；否则，就落

    2023年04月15日
    浏览(38)
  • 索引：索引知识重复习，什么是索引、索引的类型、建立索引及【最左匹配原则】、Explain查看sql的执行计划

    索引：索引知识重复习，什么是索引、索引的类型、建立索引及【最左匹配原则】、Explain查看sql的执行计划

    开干 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的

    2023年04月09日
    浏览(28)
  • mongdb 存在重复字段值，建立唯一索引失败

    mongdb 存在重复字段值，建立唯一索引失败

    1. 插入两条相同的数据 2. 创建索引 3. 报错

    2024年02月07日
    浏览(42)
  • mysql的datetime字段建立索引并比较大小

    mysql的datetime字段建立索引并比较大小

    最近测试库查询一个表的数据，需要用到唯一的一个日期类型字段作为 where 的子查询（查询当天的数据），就正常写了个这样的 SQL，具体的表名我就不写了： 其中字段的值样本如下： 我知道我写的这条 SQL 即使在 create_time 这个列有索引的情况下也不会走索引，但是执行了以

    2023年04月08日
    浏览(40)

  • MySQL会导致索引失效的情况与解决索引失效的方法

    什么情况会导致索引失效 索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些： 1.使用 SELECT * 进行查询; 2.创建了组合索引，但查询条件未准守最左匹配原则; 3.在索引列上进行计算、函数、类型转换等操作; 4.以 % 开头的 LIKE 查询比如 like \\\'%abc\\\'; ; 5.查

    2023年04月08日
    浏览(38)
  • 【数据库】哪些操作会导致索引失效

    【数据库】哪些操作会导致索引失效

    🍎 个人博客： 个人主页 🏆 个人专栏： 数据库 ⛳️   功不唐捐，玉汝于成 目录 前言 正文 结语  我的其他博客   在数据库管理中，索引的有效性对于查询性能至关重要。然而，索引可能会因为各种操作而失效，从而影响到数据库的性能和稳定性。了解导致索引失效的常

    2024年02月19日
    浏览(33)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包