Fake baseline

Техника Fake Baseline заключается в следующем: cоздать желаемый план выполнения для одного запроса и перенести план выполнения на другой запрос, используя процедуру dbms_spm.load_plans_from_cursor_cache

--Предварительный шаг
drop table t1;
alter system flush shared_pool;

--Создадим тестовую таблицу на 1 млн строк, индексы, собираем статистику
create table t1 as
select mod(rownum, 5) c1,
       mod(rownum, 100) c2,
       case mod(rownum, 20)
         when 0 then
          'aaa'
         when 10 then
          'bbb'
         when 5 then
          'ccc'
         else
          dbms_random.string(opt => 'l', len => 3)
       end c3,
       dbms_random.string(opt => 'a', len => 10) c4
  from dual
connect by level <= 1e6;

create index t1_c1_с2_idx on t1(c1,c2);
create index t1_c3_c1_idx on t1(c3,c1);

begin
  dbms_stats.gather_table_stats(user, 'T1');
end;
/

-- Оригинальный план работает по индексу t1_c3_c1_idx, но мы хотим чтобы использовался t1_c1_с2_idx
explain plan for select c4 from t1 t where c1 =:1 and c3 in (:2, :3);
select * from table(dbms_xplan.display);

Plan hash value: 840289599
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |    23 |   414 |    24   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                     |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1           |    23 |   414 |    24   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_C3_C1_IDX |    23 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access(("C3"=:2 OR "C3"=:3) AND "C1"=TO_NUMBER(:1))

-- Доработанный план использует требуемый нам индекс t1_c1_с2_idx
explain plan for select /*+ index(t t1_c1_с2_idx) */ c4 from t1 t where c1 =:1 and c3 in (:2, :3);
select * from table(dbms_xplan.display);

Plan hash value: 571715440
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    23 |   414 | 71927   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1           |    23 |   414 | 71927   (1)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN                  | T1_C1_С2_IDX |   200K|       |   472   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C3"=:2 OR "C3"=:3)
   2 - access("C1"=TO_NUMBER(:1))

-- Выполним запросы для получения их sql_id 
begin
  execute immediate 'select c4 from t1 t where c1 =:1 and c3 in (:2, :3)' using 0, 'aaa', 'bbb';
end;
/

begin
  execute immediate 'select /*+ index(t t1_c1_с2_idx) */ c4 from t1 t where c1 =:1 and c3 in (:2, :3)' using 0, 'aaa', 'bbb';
end;
/

select sql_text, sql_id, plan_hash_value, child_number, executions, sql_plan_baseline
  from v$sql s
 where sql_text like '%from t1 t where c1 =:1 and c3 in (:2, :3)%'
   and not regexp_like(sql_text, 'v\$sql|explain|execute', 'i');

SQL_TEXT                       SQL_ID          PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS SQL_PLAN_BASELINE
------------------------------ --------------- --------------- ------------ ---------- -----------------
select c4 from t1 t where c1 = 5x745mn4sj9m2         840289599            0          1
:1 and c3 in (:2, :3)

select /*+ index(t t1_c1_с2_id 65jdnxycwrs71         571715440            0          1
x) */ c4 from t1 t where c1 =:
1 and c3 in (:2, :3)

Используем sql_id и child_number оригинального запроса для получения текса запроса в clob, затем используем sql_id и plan_hash_value доработанного запроса для того чтобы ассоциировать новый план с оригинальным запросом, сохранив результат в SQL baseline

-- Создаем SQL baseline для оригинального запроса с планом от доработанного
declare
  v_sql_text clob;
  v_plans    pls_integer;
begin
  select sql_fulltext
    into v_sql_text
    from v$sql
   where sql_id = '&original_sql_id'
     and child_number = 0;

  v_plans := dbms_spm.load_plans_from_cursor_cache(sql_id          => '&hinted_sql_id',
                                                   plan_hash_value => '&hinted_plan_hash_value',
                                                   sql_text        => v_sql_text,
                                                   fixed           => 'NO',
                                                   enabled         => 'YES');

  dbms_output.put_line('Plans Loaded: ' || v_plans);

end;
/

Plans Loaded: 1

-- Проверяем что оригинальный запрос использует желаемый план с индексом t1_c1_с2_idx, в Note наличие сообщения об использовании baseline
explain plan for select c4 from t1 t where c1 =:1 and c3 in (:2, :3);
select * from table(dbms_xplan.display);

Plan hash value: 571715440
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    23 |   414 | 71927   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1           |    23 |   414 | 71927   (1)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN                  | T1_C1_С2_IDX |   200K|       |   472   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C3"=:2 OR "C3"=:3)
   2 - access("C1"=TO_NUMBER(:1))
 
Note
-----
   - SQL plan baseline "SQL_PLAN_2p70zf9h31ydg4162c407" used for this statement

-- Проверяем статистику реального выполнения 

alter session set "_rowsource_execution_statistics" = true;

declare
  type c4_tab_type is table of t1.c4%type index by pls_integer;
  v1 c4_tab_type;
begin
  for i in 1 .. 10 loop
    execute immediate 'select c4 from t1 t where c1 =:1 and c3 in (:2, :3)' bulk collect into v1 using 0, 'aaa', 'bbb';
  end loop;
end;
/

select sql_text, sql_id, plan_hash_value, child_number, executions, sql_plan_baseline
  from v$sql s
 where sql_text like '%from t1 t where c1 =:1 and c3 in (:2, :3)%'
   and not regexp_like(sql_text, 'v\$sql|explain|execute', 'i');

SQL_TEXT                                 SQL_ID          PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS SQL_PLAN_BASELINE
---------------------------------------- --------------- --------------- ------------ ---------- ------------------------------
select c4 from t1 t where c1 =:1 and c3  5x745mn4sj9m2         571715440            0         10 SQL_PLAN_2p70zf9h31ydg4162c407
in (:2, :3)


select /*+ index(t t1_c1_с2_idx) */ c4 f 65jdnxycwrs71         571715440            0          1
rom t1 t where c1 =:1 and c3 in (:2, :3)

select * from table(dbms_xplan.display_cursor('5x745mn4sj9m2',null,'ALL ALLSTATS LAST'));

SQL_ID  5x745mn4sj9m2, child number 0
-------------------------------------
select c4 from t1 t where c1 =:1 and c3 in (:2, :3)
 
Plan hash value: 571715440
 
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |       | 71927 (100)|          |    100K|00:00:00.06 |   71888 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1           |      1 |     23 |   414 | 71927   (1)| 00:00:03 |    100K|00:00:00.06 |   71888 |
|*  2 |   INDEX RANGE SCAN                  | T1_C1_С2_IDX |      1 |    200K|       |   472   (1)| 00:00:01 |    200K|00:00:00.02 |     448 |
----------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("C3"=:2 OR "C3"=:3))
   2 - access("C1"=:1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "C4"[VARCHAR2,4000]
   2 - "T".ROWID[ROWID,10]
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 6
---------------------------------------------------------------------------
 
   0 -  STATEMENT
           -  ALL_ROWS
           -  DB_VERSION('19.1.0')
           -  IGNORE_OPTIM_EMBEDDED_HINTS
           -  OPTIMIZER_FEATURES_ENABLE('19.1.0')
 
   1 -  SEL$1 / T@SEL$1
           -  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
           -  INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T1"."C1" "T1"."C2"))
 
Note
-----
   - SQL plan baseline SQL_PLAN_2p70zf9h31ydg4162c407 used for this statement

Дополнительно

--Просмотр baseline
select s.sql_handle,
       s.plan_name,
       s.enabled,
       s.accepted,
       s.fixed,
       s.optimizer_cost,
       s.created,
       s.last_modified,
       s.last_executed,
       s.sql_text
  from dba_sql_plan_baselines s;

-- Просмотр плана из baseline
select s.sql_id, sql_handle -- получаем sql_handle
  from dba_sql_plan_baselines b, v$sql s
 where S. EXACT_MATCHING_SIGNATURE = B.SIGNATURE
   and S.SQL_PLAN_BASELINE = B.PLAN_NAME;

-- Просмотр все планов имеющихся в baseline для определенного sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle => 'SQL_2a9c1f726030f9af',format => 'ALL'));

-- Просмотр конкретного плана из baseline
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name => 'SQL_PLAN_2p70zf9h31ydg4162c407'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name => 'SQL_PLAN_2p70zf9h31ydg4162c407', format => 'OUTLINE'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name => 'SQL_PLAN_2p70zf9h31ydg4162c407', format => 'ADVANCED'));

-- Удаление baseline
declare
 v1 pls_integer;
begin
  v1 := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_2a9c1f726030f9af');
end;
/

Можно ли использовать один baseline для разных запросов?

Можно, если запросы с одинаковой сигнатурой

Baseline, profile и sql patch используют "SQL signature":

A numeric hash value computed using a SQL statement text that has been normalized for case insensitivity and white space. It uniquely identifies a SQL statement. The database uses this signature as a key to maintain SQL management objects such as SQL profiles, SQL plan baselines, and SQL patches.

Что не то же самое, что и sql_id, который вычисляется другой хэш функцией. Исходя из определения сигнатуры любая модификация запроса по составу полей и т. д. меняет ее. Поэтому никак нельзя применить baseline или profile сразу к нескольким разным запросам. Но запросы с одинаковой сигнатурой могут использовать один и тот же baseline или profile.

*ACCEPT_SQL_PROFILE с опцией force_match => TRUE

Можно заранее проверить сигнатуру текста запроса:

-- Получить сигнатуру из текста запроса
select dbms_sqltune.sqltext_to_signature ('select * from dual') as signature from dual;

select dbms_sqltune.sqltext_to_signature(sql_text    => 'select с1 from t1 where c2 = 10',
                                         force_match => 1) as signature
  from dual;

select dbms_sqltune.sqltext_to_signature(sql_text    => 'select с1 from t1 where c2 = 100',
                                         force_match => 1) as signature
  from dual;

-- Получить sql_id из текста запроса
select dbms_sql_translator.sql_id('select c4 from t1 t where c1 =:1 and c3 in (:2, :3)') from dual;

Fake baseline - denis-kol4ev/OraDBA GitHub Wiki

Fake baseline

⚠️ GitHub.com Fallback ⚠️

Fake baseline - denis-kol4ev/OraDBA GitHub Wiki

Fake baseline

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️