[20230804]12c SPM学习2.txt

[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