Fake statistic - denis-kol4ev/OraDBA GitHub Wiki
Fake statistic
Заставить оптимизатор выбрать определённый индекс за счет подмены статистики. Например, оптимизатор выбирает индекс A, но мы хотим использовать индекс B. Добавить хинт в запрос нельзя, т.к. нет доступа к коду приложения или код запроса генерируется ORM. Применить тюнинг через sql patch / baseline / profile так же не представляется возможным т.к. сигнатура (SQL signature) генерируемых запросов меняется, но при этом сами запросы однотипные.
-- Создадим тестовую таблицу и индексы
alter user maint quota unlimited on users;
drop table maint.t1;
create table maint.t1 as select * from dba_objects;
select count(*) from maint.t1;
create index maint.t1_id_owner_idx on maint.t1 (object_id,owner);
create index maint.t1_name_id_idx on maint.t1 (object_name,object_id);
-- Проверка статистик индексов
set lines 120
col index_name format a20
col blevel format 999
col leaf_blocks format 999999999
col num_rows format 999999999
col distinct_keys format 999999999
col clustering_factor format 999999999
col degree format a6
col last_analyzed format a20
select i.index_name,
i.blevel,
i.leaf_blocks,
i.num_rows,
i.distinct_keys,
i.clustering_factor,
i.degree,
i.last_analyzed
from dba_indexes i
where table_name = 'T1' and owner='MAINT';
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR DEGREE LAST_ANALYZED
-------------------- ------ ----------- ---------- ------------- ----------------- ------ --------------------
T1_ID_OWNER_IDX 1 215 74766 74766 1683 1 27.02.2024 06:28:57
T1_NAME_ID_IDX 2 524 74766 74766 37836 1 27.02.2024 06:28:57
Проверка плана запроса, используется индекс t1_id_owner_idx, но нам нужен t1_name_id_idx
explain plan for
select object_id, owner, object_name, object_type, last_ddl_time
from maint.t1
where object_id = :1
and object_name = :2;
select * from table(dbms_xplan.display);
Plan hash value: 4177223468
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 63 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_ID_OWNER_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"=:2)
2 - access("OBJECT_ID"=TO_NUMBER(:1))
Подменим статистику по индексам. Опционально можем сохранить текущую статистику чтобы была возможность восстановления.
begin
dbms_stats.create_stat_table(ownname => 'MAINT',
stattab => 'STATTAB',
tblspace => 'USERS');
end;
/
begin
dbms_stats.export_index_stats(ownname => 'MAINT',
indname => 'T1_NAME_ID_IDX',
stattab => 'STATTAB',
statown => 'MAINT');
dbms_stats.export_index_stats(ownname => 'MAINT',
indname => 'T1_ID_OWNER_IDX',
stattab => 'STATTAB',
statown => 'MAINT');
end;
/
Занижаем стоимость индекса который хотим использовать и завышаем стоимость того, который хотим игнорировать.
begin
dbms_stats.delete_index_stats('MAINT', 'T1_NAME_ID_IDX');
dbms_stats.delete_index_stats('MAINT', 'T1_ID_OWNER_IDX');
dbms_stats.set_index_stats(ownname => 'MAINT',
indname => 'T1_NAME_ID_IDX',
no_invalidate => FALSE,
indlevel => 1,
numlblks => 10,
numrows => 100,
numdist => 100,
clstfct => 10);
dbms_stats.set_index_stats(ownname => 'MAINT',
indname => 'T1_ID_OWNER_IDX',
no_invalidate => FALSE,
indlevel => 5,
numlblks => 32000,
numrows => 10e6,
numdist => 1e6,
clstfct => 1e6);
end;
/
Повторно проверим статистику по индексам
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR DEGREE LAST_ANALYZED
-------------------- ------ ----------- ---------- ------------- ----------------- ------ --------------------
T1_ID_OWNER_IDX 5 32000 10000000 1000000 1000000 1 27.02.2024 06:43:13
T1_NAME_ID_IDX 1 10 100 100 10 1 27.02.2024 06:43:13
Блокируем статистику по таблице, каскадно блокируется статистика и по зависимым индексам. Считаем что распределение данных в таблице меняется незначительно и текущая статистика будет оптимальной.
begin
dbms_stats.lock_table_stats('MAINT','T1');
end;
/
Проверка плана, выбирается нужный индекс T1_NAME_ID_IDX
explain plan for
select object_id,owner,object_name,object_type, last_ddl_time
from maint.t1
where object_id = :1
and object_name = :2;
select * from table(dbms_xplan.display);
Plan hash value: 3386561775
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74766 | 4599K| 20 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 74766 | 4599K| 20 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_NAME_ID_IDX | 100 | | 10 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"=:2 AND "OBJECT_ID"=TO_NUMBER(:1))
Реальное выполнение запроса в sqlplus, под условие запроса данных не попадает, но смысл показать что использовался план с нужным нам индексом.
SQL> select /*+ GATHER_PLAN_STATISTICS */ object_id,owner,object_name,object_type, last_ddl_time
from maint.t1
where object_id = 400
and object_name = 'AAA';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 26z34nra20yf7, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */
object_id,owner,object_name,object_type, last_ddl_time from maint.t1
where object_id = 400 and object_name = 'AAA'
Plan hash value: 3386561775
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 0 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | T1_NAME_ID_IDX | 1 | 1 | 0 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='AAA' AND "OBJECT_ID"=400)
21 rows selected.
SQL>
Дополнительно
-- Собрать статистику на таблице с заблокированной статистикой можно с ключом force
begin
dbms_stats.gather_index_stats('MAINT', 'T1_NAME_ID_IDX', force => true);
end;
/
begin
dbms_stats.gather_table_stats(ownname => 'MAINT', tabname => 'T1', cascade =>true, force =>true);
end;
/
-- Разблокировать статистику по таблице
begin
dbms_stats.unlock_table_stats('MAINT','T1');
end;
/
-- Импортировать ранее сохраненную статистику по индексам
begin
dbms_stats.import_index_stats(ownname => 'MAINT',
indname => 'T1_NAME_ID_IDX',
stattab => 'STATTAB',
statown => 'MAINT');
dbms_stats.import_index_stats(ownname => 'MAINT',
indname => 'T1_ID_OWNER_IDX',
stattab => 'STATTAB',
statown => 'MAINT');
end;
/