Query transformation in action

 Общи  Коментарите са изключени за Query transformation in action
Апр. 102013
 

Не знам защо, ама много се кефя като видя да действат разни дребни детайли в огромната и сложна машина Oracle. Сега ще ви разкажа за един такъв случай. Може да бъде поучителен, интересен или скучен – зависи от това дали си имате работа с oracle и дали сте dba или developer 🙂

Всичко започна със супер спешния мейл от един Development manager от високо ниво. Новоразработената функционалност ХYZ се бави (12-13 сек), и са идентифицирали проблема в конкретна заявка към БД:

select .... from SOME_TABLE where IP = :1 and ACTIVE_DATE > current_timestamp - :2;

Не ми трябваше много време да видя, че се случва full table scan на таблицата, която има милиони редове. Няма индекс нито по IP, нито по ACTIVE_DATE. Да, разбира се цялата таблица отдавна е почервеняла в паметта и няма physical IO, но дори LIO отнема време.

Без много надежда попитах дали не могат да използват някоя от вече индексираните колони. Ами не, не може. За тази функционалност имаме само тези предикати.

Ами ок, ще правим тогава индекс по (IP, ACTIVE_DATE). Направих индекса на една от DEV базите и помолих да тестват. Оказа се, че няма ефект. Ъ.

Реших да сръчкам Oracle да се взема в ръце. Може ли такава простотия! Да му направя аз перфектния индекс, а той въобще да не го вземе предвид! Пуснах му един Тuning advisor да се съвземе. Каква беше изненадата ми, когато и Tuning advisor не откри индекса! Но пък ми подсказа къде е заровено кучето:

The predicate TO_NUMBER(„IP“)=:B1 used at line ID 1 of the execution plan contains an implicit data type conversion on indexed column „IP“. This implicit data type conversion prevents the optimizer from selecting indices on table „SOME_TABLE“

Евала, добре са го написали. Като врял и кипял (и бивш девелопър) веднага се усетих какво е станало. Язък, след толкова години развития на IDE-та и фреймуорци, грешките са си същите.

Проблема е, че колонката в таблицата е тип VARCHAR2. Bind-а обаче го подаваме като NUMBER. Ако няма bind, това веднага се хваща само с поглед върху заявката. Но в случая трябваше да гледам във V$SQL_BIND_CAPTURE. Или да накарам някой девелопър да си го погледне – и да го оправи.

Тук искам да отворя една малка скоба относно ползването на графични инструменти като OEM CC. Хубави са, и ти дават много удобства, но в момента леко ме подведоха. Същата тази информация можех да си я извадя по-рано ако не ме беше домързяло да пусна един dbms_xplan.display_cursor. Ето какво показва той:

SQL_ID  436wr79chvhqx, child number 0
-------------------------------------
select ...
  from SOME_TABLE 
 where IP = :1
   and ACTIVE_DATE > current_timestamp - :2
 
Plan hash value: 2330990216
 
----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |       |       | 12528 (100)|          |
|*  1 |  TABLE ACCESS FULL| SOME_TABLE         |     1 |    87 | 12528   (2)| 00:02:31 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter((TO_NUMBER("IP")=:1 AND 
              "ACTIVE_DATE">CURRENT_TIMESTAMP(6)-:2))

И така идваме до същината на този пост. Оправиха си кода девелъпърите, започнаха да подават IP-то като стринг. Индекса захапа и плана стана следния:

SQL_ID  4ktc94z4ypdpu, child number 0
-------------------------------------
select ...
  from SOME_TABLE 
 where IP = :1
   and ACTIVE_DATE > current_timestamp - :2
 
Plan hash value: 3569887601
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| SOME_TABLE            |     1 |    87 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_SOME_TABLE_IP_TIME  |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("IP"=:1 AND "ACTIVE_DATE">CURRENT_TIMESTAMP(6)-:2 AND 
              "ACTIVE_DATE" IS NOT NULL)

Забелязахте ли промяната в предикатите? Появи се AND "ACTIVE_DATE" IS NOT NULL. Това optimizer-а сами си го е измислил!

От една страна, в b-tree индексите не се слагат null стойностите. За да използваме индекс, ние трябва да сме сигурни, че не ни трябват евентуалните редове с null в съответната колонка. Т.е. Oracle трябва да е сигурен, че потребителя, подал заявката, не търси тези данни. От друга страна оптимизатора знае, че няма дата, по-малка от null – или по-голяма. За това си позволява да забучи един измислен предикат във where клаузата, който му отваря пътя към използване на индекса.

Да, нещо дребно, ама ме кефи 🙂

 Posted by at 6:51