Това ще влезе в категория quick checks. Предварително казвам, че (все още) не познавам добре системата, за която става дума.
Днес ми излезе интересен въпрос: една таблица има няколко десетки милиона реда, с данни от 8 години насам. Има (not null) колонка с дата, по която има индекс. Колонката няма хистограма. Когато пуснем заявката select count(1) from tab where date_col > sysdate-1, съвсем правилно, се изпозлва съответният индекс, т.е. не сме в тази скучна ситуация „Oracle не ми използва индекса“. Обаче се прави Index Fast Full Scan (wtf???), което естествено, дава незадоволителна производителност:
SQL> set autot traceonly explain
SQL> select count(1) from tab where date_col>SYSDATE-1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2096840410
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 10461 (7)| 00:02:06 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| I_TAB_DATE_COL | 5038K| 38M| 10461 (7)| 00:02:06 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DATE_COL">SYSDATE@!-1)
Според explain plan, използването на IFFS води до cost от ~10000, докато при hint INDEX_SS_ASC cost-а е 15000.
SQL> SELECT /*+INDEX_SS_ASC(t I_TAB_DATE_COL)*/ COUNT(1) FROM tab t WHERE date_col>SYSDATE-1;
Execution Plan
----------------------------------------------------------
Plan hash value: 547665643
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 15875 (1)| 00:03:11 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| I_TAB_DATE_COL | 5038K| 38M| 15875 (1)| 00:03:11 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATE_COL">SYSDATE@!-1)
При аномалии в cost, особено в елементарни заявки, веднага гледам статистиките, защото в 99.9999% от случаите, там се крие заека.
Статистиките си бяха ОК, събрани са скоро (предната вечер):
SQL> set autot off
SQL> select to_char(last_analyzed, 'DD.MM.YYYY HH24:MI:SS')
from dba_tab_statistics
where owner = 'OWNR'
and table_name = 'TAB';
TO_CHAR(LAST_ANALYZED,'DD.MM.Y
---------------------------------------------------------------------------
07.02.2010 22:03:58
Преди да задълбая в clustering factor и други подробности за индекса, погледнах какво точно пише за тази колонка, та оптимизатора решава, че ще вземе твърде много данни при range scan.
SQL> column low_value format a20
SQL> column high_value format a20
SQL> select low_value, high_value, density
from dba_tab_col_statistics
where owner = 'OWNR'
and table_name = 'TAB'
and column_name = 'DATE_COL';
LOW_VALUE HIGH_VALUE DENSITY
-------------------- -------------------- ----------
78660107173A0A 78710208121319 1.1388E-07
Опа… греда. Данните за low_value и high_value са в колонка тип long (ха-ха-ха). С малко търсене намерих в OTN как да видя датата в по-разбираем формат
SQL> set serveroutput on
DECLARE
rv RAW(32) := '78710208121319';
dt DATE := NULL;
BEGIN
dbms_stats.convert_raw_value(rv, dt);
dbms_output.put_line(TO_CHAR(dt, 'DD-MM-YYYY hh24:mi:ss'));
END;
/
08-02-2013 17:18:24
PL/SQL procedure successfully completed.
Грешката е вярна! Оказа се че има няколко бъгливи записа, които стоят години напред. Съответно предиката date_col>SYSDATE-1 връща 3 години данни. При липсата на хистограми оптимизатора няма как да знае, че ще върне няколко десетки/стотици записи – грубата сметка показва, че ще прерови данни за 3 години от общо налични 11 между low_value и high_value.
В случая решението беше да се затрият бъгливите данни. Но по принцип това е идеален пример за ползата от хистограмите.