[20230804]12c SPM学习2.txt

--//前一段时间建立一个索引导致性能问题,参考链接:[20230517]建立索引导致的性能问题2.txt
--//当时我使用sql profile,在我的实际工作中更加喜欢使用sql profile,而不喜欢SQL Plan Baseline,可能于我先学习sql profile有
--//关.或者我认为sql profile更加简单,实际上两种方式存在不同管理方式.

--//我当时的情况非常特殊,就是我sql profile里面的信息写死了使用了索引名,我想测试SQL Plan Baseline呢?
[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 /*+ &&1 */ vc from t1 where id2 = :v_id2 or id3 = :v_id3 ;

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

SCOTT@test01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  88fg1sd3a8xqt, child number 0
-------------------------------------
select /*+ test1 */ 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=88fg1sd3a8xqt,注意outline部分BITMAP_TREE那行，出现IX_T1_ID2。另外ID＝5出现1次sort order by。
--//我在http://blog.itpub.net/267265/viewspace-2952012/ => [20230512]优化的困惑19.txt 有解析。

--//使用SQL Plan Baseline稳定执行计划,首先加载到sql plan baseline中。
SCOTT@test01p> exec dbms_output.put_line( dbms_spm.load_plans_from_cursor_cache('88fg1sd3a8xqt') )
PL/SQL procedure successfully completed.

variable v_basenum     number;
variable v_sql_handle  varchar2(30);
variable v_plan_name   varchar2(30);
exec :v_sql_handle  := 'SQL_1896abbc2fc905f4'
exec :v_plan_name   := 'SQL_PLAN_1j5pbrhrwk1gn7809c0d5'

SCOTT@test01p> select sql_handle,plan_name,created,enabled,accepted,fixed,autopurge,reproduced,origin,signature from dba_sql_plan_baselines where sql_handle=:v_sql_handle;
SQL_HANDLE           PLAN_NAME                      CREATED                    ENA ACC FIX AUT REP ORIGIN                                      SIGNATURE
-------------------- ------------------------------ -------------------------- --- --- --- --- --- ----------------------------- -----------------------
SQL_1896abbc2fc905f4 SQL_PLAN_1j5pbrhrwk1gn7809c0d5 2023-08-04 20:29:09.000000 YES YES NO  YES YES MANUAL-LOAD-FROM-CURSOR-CACHE     1771792328160773620

--//改名索引：
SCOTT@test01p> alter index ix_t1_id2 rename to i_t1_id2;
Index altered.

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

SCOTT@test01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  88fg1sd3a8xqt, child number 0
-------------------------------------
select /*+ test1 */ vc from t1 where id2 = :v_id2 or id3 = :v_id3
Plan hash value: 4254410754
--------------------------------------------------------------------------------------------------
| 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              | I_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 "I_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
--//还好没有出现性能问题。
--//这个测试就没有出现前一次测试的情况。

SCOTT@test01p> select sql_handle,plan_name,created,enabled,accepted,fixed,autopurge,reproduced,origin,signature from dba_sql_plan_baselines where sql_handle=:v_sql_handle;
SQL_HANDLE           PLAN_NAME                      CREATED                    ENA ACC FIX AUT REP ORIGIN                                      SIGNATURE
-------------------- ------------------------------ -------------------------- --- --- --- --- --- ----------------------------- -----------------------
SQL_1896abbc2fc905f4 SQL_PLAN_1j5pbrhrwk1gn7809c0d5 2023-08-04 20:29:09.000000 YES YES NO  YES YES MANUAL-LOAD-FROM-CURSOR-CACHE     1771792328160773620
SQL_1896abbc2fc905f4 SQL_PLAN_1j5pbrhrwk1gn78d5b082 2023-08-04 20:32:26.000000 YES NO  NO  YES YES AUTO-CAPTURE                      1771792328160773620