Правилен индекс, грешен подход
Feb 8th, 2010 by yavor
Това ще влезе в категория quick checks. Предварително казвам, че (все още) не познавам добре системата, за която става дума.
Днес ми излезе интересен въпрос: една таблица има няколко десетки милиона реда, с данни от 8 години насам. Има колонка с дата, по която има индекс. Колонката няма хистограма. Когато пуснем заявката 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.
В случая решението беше да се затрият бъгливите данни. Но по принцип това е идеален пример за ползата от хистограмите.
), все повече оценявам нашите си “звезди” - примерно Юлиян Дончев. Owen Hughes също е почти местен с третото си идване, a и също е много добър лектор. Както и Пламен, когато реши да говори. Пък и всеки друг, решил да сподели опит или да покаже резултати от неговия research. (Иване, не се ослушвай)
