"Нас Атакуют!" Изобличи козни лукавого, запрети диаволу
Изменяемые 'адаптивные' планы выполнения SQL запросов в Оракл 12c
Новая версия Oracle database 12c предлагает разработчикам значительно улучшенный "умный" SQL оптимизатор. Множество доработок и новых возможностей позволяют правильно использовать статистику, оценивать действительное число возвращаемых строк и учитывать фактическое распределение данных в столбцах.
При этом все предыдущие версии SQL оптимизатора, основанного на относительной стоимости операций, периодически ошибались, позволяя первому запросу "пойти в разнос" за счёт выбора неудачного плана. Последующие выполнения того же SQL могли пройти более успешно если оптимизатор мог учесть допущенные ошибки. Но первое выполнение всегда могло быть отработано неоптимальным образом, что было особенно ощутимо в системах хранилищ данных, где зачастую каждый запрос уникален и выполняется однократно.
В предлагаемой заметке я привожу пример использования одной из важных принципиально новых возможностей Оракла 12c изменять SQL план первого запроса "на лету" непосредственно во время его исполнения, приводя к значительному сокращению времени обработки данных. Так называемые "адаптивные" планы могут буквально спасти ситуацию, позволяя правильно и эффективно выполнить даже уникальный неповторяющийся SQL.
Прежде чем мы продолжим, я хотел бы привести строки из Евангелия:
......................... == Книга пророка Исаии == ............................
=== Глава 12, Стих 1 ===
1 И скажешь в тот день: славлю Тебя, Господи; Ты гневался на меня, но
отвратил гнев Твой и утешил меня.
2 Вот, Бог - спасение мое: уповаю на Него и не боюсь; ибо Господь - сила моя,
и пение мое - Господь; и Он был мне во спасение.
3 И в радости будете почерпать воду из источников спасения,
4 и скажете в тот день: славьте Господа, призывайте имя Его; возвещайте
в народах дела Его; напоминайте, что велико имя Его;
5 пойте Господу, ибо Он соделал великое, - да знают это по всей земле.
Лично для вас благая весть - Единородный Сын Божий Иисус Христос любит вас, Он взошёл на крест за ваши грехи, был распят и на третий день воскрес, сел одесную Бога и открыл нам дорогу в Царствие Небесное.
Мы все живём в те самые последние времена, когда брат идёт войной на брата, сердца людские ожесточились и каждый обороняет своё, не внимая голосу рассудка и зову сердца. Сатана запутал нас в бесконечном лабиринте жадности и мирских похотей - и, казалось бы, нет выхода из этого тупика.
Но ведь каждому из нас открыта дорога к спасению от этих окружающих нас повседневных бед - уповай на Господа, прославляй имя Его и ищи в Нём силы для борьбы со злом. Такое вот простое и незатейливое, казалось бы, решение. И тем не менее, уже много тысяч лет Господь спасает взывающих к Нему. И сегодня, на очередном витке нашего с вами безумия именуемого "историей", нам как никогда нужно Божие прощение и милость. Ибо наши страшные душевные раны могут быть омыты только той самой водой из источников спасения - остальным же уготована жажда на пустынной дороге самолюбия и зла, ведущей прямо в ад.
Задумайтесь над этими словами, серьёзно задумайтесь - выбор между спасительной верой и осуждающим для ада неверием неизбежен, и вполне возможно, дорогой брат, что именно сейчас решается ваша судьба - и не на год или десятилетие, а навсегда.
Покайтесь, примите Иисуса как вашего Спасителя, ибо наступают последние времена и время близко - стоит Судья у ворот.
Пожалуйста, в своих каждодневных трудах, какими бы занятыми вы себе ни казались - находите время для Бога, Его заповедей и Библии.
На главной странице этого сайта вы найдете программу для чтения Библии в командной строке - буду очень рад если программа окажется полезной. Пожалуйста, читайте Библию, на экране или в печатном виде - вы будете искренне удивлены как много там сказано лично про вас и ваши обстоятельства.
Вернёмся к нашим техническим деталям.
В заметке рассматриваются несколько видов запросов, приводящих к генерации изменяемого плана:
Прочитать подробно об изменяемых планах ("adaptive plan") можно в документации Оракла 12 - смотрите "Concept" и "SQL Tuning Guide".
Я же скажу вкратце так - при первом "разборе" SQL выражения оптимизатор начинает строить план его выполнения, опираясь на имеющиеся данные о количестве записей и числе уникальных значений в каждой колонке таблиц, участвующих в запросе. При определённых обстоятельствах оптимизатор замечает "неясность" в эффективности способа "сведения" таблиц - и вот в этот момент в план вставляется "ветвление" - кусок кода, выполняющийся позже, уже вместе с самим запросом. На данный момент код "ветвления" может сгенерировать только "Hash Join" или "Nested Loops", но я уверен - вскоре появятся новые варианты.
Непосредственно во время первого выполнения запроса при достижении "ветвления" процесс, исполняющий запрос по динамическому плану, оценивает количество уже обработанных фактически строк и сравнивает с числом строк, "предсказанных" планом. Если число отличается не сильно - ветвление просто игнорируется (и фактически выбрасывается, так что последующие выполнения SQL по этому адаптивному плану уже не будут изменяться - т.е. план превращается из адаптивного в обычный "фиксированный") и запрос "сводит" таблицы способом, предложенным в плане изначально.
Если же разница в числе строк велика - код "ветвления" выполняется и генерирует другой способ сведения таблиц - например, если изначально оптимизатор предсказал всего 20 строк в каждой таблице и решил использовать "Nested Loops", то теперь, когда вместо 20 строк мы уже обработали 2 тысячи, становится ясным что NL будет работать долго и нудно - и это же самое первое выполнение запроса "на лету" переходит на "Hash Join". И таким же образом, код ветвления удаляется из плана, превращая изменяемый план в его фиксированный эквивалент, но теперь уже содержащий HJ вместо NL.
Такой подход позволяет выполнить запрос оптимально с первой попытки, даже при неточностях в собранной статистике. При этом код ветвления либо не выполняется вообще, либо выполняется только один раз, сводя на нет возможные потери производительности при обработке последующих запросов по этому же плану.
"Внутренние" служебные запросы самой БД
Сам по себе, Оракл является одним из основных пользователей адаптивных планов. Сбор статистики, Automated Workload Repository и прочие "maintenance jobs" извлекают значительную выгоду из этой новой особенности продукта. Распознать запросы, выполненные по адаптивному плану, очень легко используя колонку V$SQL.IS_RESOLVED_ADAPTIVE_PLAN:
.......... == Второе послание к Тимофею святого апостола Павла == .............. === Глава 4, Стих 22 === 22 Господь Иисус Христос со духом твоим. Благодать с вами. Аминь. (b+/b-, c+/c-, +/-, *) > [oracle@localhost ~]$ sqlplus SQL*Plus: Release 12.1.0.1.0 Production ... SQL> l 1 select sql_id, IS_REOPTIMIZABLE, IS_RESOLVED_ADAPTIVE_PLAN, child_number 2 from v$sql 3 where IS_RESOLVED_ADAPTIVE_PLAN is not null 4* order by sql_id, child_number 15:27:36 SQL> / SQL_ID I I CHILD_NUMBER ------------- - - ------------ ... fuqs31uwjmbkz N N 0 fuqs31uwjmbkz N Y 1 ...
Мы видим, что запрос с sql_id "fuqs31uwjmbkz" был выполнен дважды - первый раз это происходило по "начальному" плану, который был "адаптирован" и в конце второго выполнения уже превратился в "фиксированный" план. Колонка IS_RESOLVED_ADAPTIVE_PLAN говорит нам именно о том, что код ветвления присутствовал в начальном плане, был выполнен и удалён.
Всё же возникает сомнение - а действительно ли план выполнения был адаптивным и вообще, не являются ли две строки выше просто двумя записями обработки одного и того же плана разными сессиями?
Замена Hash Join на Nested Loops
Я скажу сразу - изменения в версии 12c, связанные с "адаптивными" запросами и статистикой настолько кардинальны, что во многих случаях ответ просто неясен :-) Методом проб и ошибок, выполнением простых запросов на известных наборах данных получается добраться до реальной картины происходящего - но это требует усилий и времени. Попробуем посмотреть внимательнее:
....................... == Книга пророка Аввакума == ........................... === Глава 2, Стих 19 === 18 Что за польза от истукана, сделанного художником, этого литого лжеучителя, хотя ваятель, делая немые кумиры, полагается на свое произведение? 19 Горе тому, кто говорит дереву: "встань!" и бессловесному камню: "пробудись!" Научит ли он чему-нибудь? Вот, он обложен золотом и серебром, но дыхания в нем нет. (b+/b-, c+/c-, +/-, *) > [oracle@localhost ~]$ sqlplus SQL*Plus: Release 12.1.0.1.0 Production ... 15:45:52 SQL> select IS_REOPTIMIZABLE, IS_RESOLVED_ADAPTIVE_PLAN, child_number, 15:46:12 2 EXECUTIONS, fetches, END_OF_FETCH_COUNT, LAST_ACTIVE_TIME, PLAN_HASH_VALUE 15:46:46 3 from v$sql 15:46:49 4 where sql_id = 'fuqs31uwjmbkz'; I I CHILD_NUMBER EXECUTIONS FETCHES END_OF_FETCH_COUNT LAST_ACTIVE_TIME PLAN_HASH_VALUE - - ------------ ---------- ---------- ------------------ ----------------- --------------- N N 0 0 0 0 17-03-14 14:00;32 4259802813 N Y 1 1 2 1 17-03-14 14:00;32 4259802813 2 rows selected.
Совпадение времени окончания выполнения и хэшей планов показывает нам, что наше предположение оказалось верным - мы говорим об адаптивном плане. Что же именно делал SQL запрос 'fuqs31uwjmbkz' и как менялся "адаптивный" план? Посмотрим (и я заранее извиняюсь за выступающий за поля текст плана):
......................... == Книга пророка Ионы == .............................
=== Глава 2, Стих 10 ===
9 Чтущие суетных и ложных _богов_ оставили Милосердаго своего,
10 а я гласом хвалы принесу Тебе жертву; что обещал, исполню: у Господа
спасение!
(b+/b-, c+/c-, +/-, *) >
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production
...
SQL> l
1* select * from table(dbms_xplan.display_cursor('fuqs31uwjmbkz', 0, 'ALL +ADAPTIVE +NOTE'))
15:37:04 SQL> /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID fuqs31uwjmbkz, child number 0
-------------------------------------
SELECT count(*) as cnt , a.SQL_ID, a.CON_DBID,
a.CURRENT_OBJ#, sum(case when a.event_id in (:e0, :e1, :e2,
:e3, :e4, :e5, :e6, :e7) then 1 else 0 end) as full_scan FROM
WRH$_ACTIVE_SESSION_HISTORY a , WRH$_EVENT_NAME en WHERE a.dbid
= :dbid AND a.instance_number = :inst AND a.snap_id > :bid AND
a.snap_id <= :eid AND a.sql_id in (:sqlid0, :sqlid1, :sqlid2) AND
en.dbid = :dbid AND en.event_id = a.event_id AND en.wait_class
in ('Cluster', 'User I/O') AND a.CURRENT_OBJ# > 0 AND a.sql_id is
NOT NULL AND a.session_type = 1 GROUP BY a.SQL_ID, a.CON_DBID,
a.CURRENT_OBJ# HAVING count(*) >= :rowcnt ORDER BY count(*) DESC,
a.CURRENT_OBJ# ASC
Plan hash value: 4259802813
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 58 | 7 (29)| 00:00:01 | | |
| * 2 | FILTER | | | | | | | |
| 3 | HASH GROUP BY | | 1 | 58 | 7 (29)| 00:00:01 | | |
| * 4 | FILTER | | | | | | | |
|- * 5 | HASH JOIN | | 1 | 58 | 5 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 58 | 5 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 58 | 5 (0)| 00:00:01 | | |
|- 8 | STATISTICS COLLECTOR | | | | | | | |
| 9 | PARTITION RANGE ITERATOR | | 1 | 37 | 3 (0)| 00:00:01 | KEY | KEY |
| * 10 | TABLE ACCESS FULL | WRH$_ACTIVE_SESSION_HISTORY | 1 | 37 | 3 (0)| 00:00:01 | KEY | KEY |
| * 11 | INDEX RANGE SCAN | WRH$_EVENT_NAME_PK | 1 | | 1 (0)| 00:00:01 | | |
| * 12 | TABLE ACCESS BY INDEX ROWID| WRH$_EVENT_NAME | 1 | 21 | 2 (0)| 00:00:01 | | |
|- * 13 | TABLE ACCESS FULL | WRH$_EVENT_NAME | 1 | 21 | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
...
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Elapsed: 00:00:00.06
15:37:04 SQL>
План для "child 1" будет таким же самым. Действия в плане, помеченные дефисом, не выполнялись, именно как результат работы кода ветвления (обозначенного на плане "STATISTICS COLLECTOR", который тоже был помечен "-" после первого обращения и не является активным для последующих сессий).
В этом запросе процесс, выполняющий план с хэшем 4259802813, решил не использовать Hash Join и полное сканирование таблицы WRH$_EVENT_NAME, вместо этого был использован индекс WRH$_EVENT_NAME_PK с последующим Nested Loops Join. Также заметьте наличие Bind Variables - без них план вероятнее всего не был бы адаптивным и колонка v$sql.IS_RESOLVED_ADAPTIVE_PLAN содержала бы NULL.
Замена Nested Loops на Hash Join
Теперь посмотрим на обратное действие - похожий запрос от того же модуля AWR, только выполнялся он 4 раза, и все разы использовал адаптивный план, в котором вместо NL использовался HJ и полное сканирование таблицы WRH$_EVENT_NAME. Даты и количество END_OF_FETCH_COUNT показывают нам, что Child 0 был принят к обработке, но не закончил выполнения. Child 1 был полностью выполнен 4 раза.
.............. == Послание к Галатам святого апостола Павла == .................
=== Глава 6, Стих 18 ===
18 Благодать Господа нашего Иисуса Христа со духом вашим, братия. Аминь.
(b+/b-, c+/c-, +/-, *) >
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production
...
16:04:16 SQL> l
1 select IS_REOPTIMIZABLE, IS_RESOLVED_ADAPTIVE_PLAN, child_number,
2 EXECUTIONS, fetches, END_OF_FETCH_COUNT, FIRST_LOAD_TIME,
3 LAST_ACTIVE_TIME, full_PLAN_HASH_VALUE,
4 PLAN_HASH_VALUE, hash_value, invalidations
5 from v$sql
6* where sql_id = '6cp74g22fzahf'
16:04:19 SQL> /
I I CHILD_NUMBER EXECUTIONS FETCHES END_OF_FETCH_COUNT FIRST_LOAD_TIME LAST_ACTIVE_TIME
- - ------------ ---------- ---------- ------------------ --------------- -----------------
FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE HASH_VALUE INVALIDATIONS
-------------------- --------------- ---------- -------------
N N 0 0 0 0 2014-03-17/13:0 17-03-14 13:00;30
0:30
3348789387 3902652512 2230299150 0
N Y 1 4 529 4 2014-03-17/13:0 17-03-14 16:00;42
0:30
3348789387 3902652512 2230299150 0
2 rows selected.
Elapsed: 00:00:00.00
16:04:21 SQL>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 6cp74g22fzahf, child number 0
-------------------------------------
WITH ash as (SELECT ash.event_id, sum(decode(ash.session_type, 1, 1,
0)) as cnt_fg, count(*) as cnt_total FROM
WRH$_ACTIVE_SESSION_HISTORY ash WHERE ash.dbid = :dbid AND
ash.instance_number = :inst AND ash.snap_id > :bid AND ash.snap_id <=
:eid AND ash.wait_time = 0 GROUP BY ash.event_id ), evts as
(SELECT event_id, sum(w_cnt) as w_cnt, sum(to_cnt) as to_cnt,
sum(w_time) as w_time ,sum(w_cnt_fg) as w_cnt_fg, sum(to_cnt_fg) as
to_cnt_fg, sum(w_time_fg) as w_time_fg FROM (SELECT es.event_id as
event_id, es.total_waits - nvl(bs.total_waits,0) as w_cnt,
es.TOTAL_TIMEOUTS - nvl(bs.TOTAL_TIMEOUTS,0) as to_cnt,
es.TIME_WAITED_MICRO - nvl(bs.TIME_WAITED_MICRO,0) as w_time
,es.total_waits_fg - nvl(bs.total_waits_fg,0) as w_cnt_fg,
es.TOTAL_TIMEOUTS_FG - nvl(bs.TOTAL_TIMEOUTS_FG,0) as to_cnt_fg,
es.TIME_WAITED_MICRO_FG - nvl(bs.TIME_WAITED_MICRO_FG,0) as w_time_fg
FROM WRH$_SYSTEM_EVENT es , WRH$_SYSTEM_EVENT bs WHERE es.dbid =
:dbid AND es.instance_number = :i
Plan hash value: 3902652512
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21 (100)| | | |
| 1 | SORT ORDER BY | | 97 | 19982 | 21 (15)| 00:00:01 | | |
| * 2 | FILTER | | | | | | | |
| * 3 | HASH JOIN RIGHT OUTER | | 97 | 19982 | 20 (10)| 00:00:01 | | |
| 4 | FIXED TABLE FULL | X$KEHEVTMAP | 105 | 1155 | 0 (0)| | | |
| * 5 | HASH JOIN RIGHT OUTER | | 97 | 18915 | 20 (10)| 00:00:01 | | |
| 6 | VIEW | | 2 | 66 | 4 (25)| 00:00:01 | | |
| 7 | HASH GROUP BY | | 2 | 58 | 4 (25)| 00:00:01 | | |
| * 8 | FILTER | | | | | | | |
| 9 | PARTITION RANGE ITERATOR | | 2 | 58 | 3 (0)| 00:00:01 | KEY | KEY |
| * 10 | TABLE ACCESS FULL | WRH$_ACTIVE_SESSION_HISTORY | 2 | 58 | 3 (0)| 00:00:01 | KEY | KEY |
| * 11 | HASH JOIN | | 97 | 15714 | 16 (7)| 00:00:01 | | |
| * 12 | FIXED TABLE FULL | X$KEHECLMAP | 12 | 156 | 0 (0)| | | |
| * 13 | HASH JOIN | | 105 | 15645 | 16 (7)| 00:00:01 | | |
|- 14 | NESTED LOOPS | | 105 | 15645 | 16 (7)| 00:00:01 | | |
|- 15 | NESTED LOOPS | | | | | | | |
|- 16 | STATISTICS COLLECTOR | | | | | | | |
| 17 | VIEW | | 105 | 8925 | 9 (12)| 00:00:01 | | |
| 18 | HASH GROUP BY | | 105 | 8925 | 9 (12)| 00:00:01 | | |
| 19 | VIEW | | 270 | 22950 | 8 (0)| 00:00:01 | | |
| 20 | UNION-ALL | | | | | | | |
| * 21 | HASH JOIN OUTER | | 165 | 14190 | 8 (0)| 00:00:01 | | |
|- 22 | NESTED LOOPS OUTER | | 165 | 14190 | 8 (0)| 00:00:01 | | |
|- 23 | STATISTICS COLLECTOR | | | | | | | |
| 24 | PARTITION RANGE SINGLE | | 90 | 3870 | 4 (0)| 00:00:01 | KEY | KEY |
| 25 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| WRH$_SYSTEM_EVENT | 90 | 3870 | 4 (0)| 00:00:01 | KEY | KEY |
| * 26 | INDEX RANGE SCAN | WRH$_SYSTEM_EVENT_PK | 46 | | 2 (0)| 00:00:01 | KEY | KEY |
|- 27 | PARTITION RANGE SINGLE | | 2 | 86 | 4 (0)| 00:00:01 | KEY | KEY |
|- 28 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | WRH$_SYSTEM_EVENT | 2 | 86 | 4 (0)| 00:00:01 | KEY | KEY |
|- * 29 | INDEX RANGE SCAN | WRH$_SYSTEM_EVENT_PK | 46 | | 2 (0)| 00:00:01 | KEY | KEY |
| 30 | PARTITION RANGE SINGLE | | 90 | 3870 | 4 (0)| 00:00:01 | KEY | KEY |
| 31 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | WRH$_SYSTEM_EVENT | 90 | 3870 | 4 (0)| 00:00:01 | KEY | KEY |
| * 32 | INDEX RANGE SCAN | WRH$_SYSTEM_EVENT_PK | 46 | | 2 (0)| 00:00:01 | KEY | KEY |
| 33 | FIXED TABLE FULL | X$KEHEVTMAP | 105 | 735 | 0 (0)| | | |
|- * 34 | INDEX RANGE SCAN | WRH$_EVENT_NAME_PK | | | | | | |
|- * 35 | TABLE ACCESS BY INDEX ROWID | WRH$_EVENT_NAME | 1 | 64 | 7 (0)| 00:00:01 | | |
| * 36 | TABLE ACCESS FULL | WRH$_EVENT_NAME | 1501 | 96064 | 7 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Обратите внимание, что "код ветвления" был вставлен в этот план дважды.
Простой пример с двумя таблицами пользователя
Настало время оценить полезность изменяемых планов для нас, пользователей, на наших собственных данных. Сгенерируем простой набор в виде таблиц клиентов и их визитов к нам. Распределение записей по клиентам специально подобрано так, чтобы оптимизатор не мог быть уверен в количестве строк.
Создание набора данных пользователя
Используя приведенные ниже команды, создайте в своей базе подобные объекты в какой-нибудь из схем. Используйте "локального" пользователя с минимальным набором привилегий в одной из "подключаемых" PDB баз. Как я говорил в своей заметке про PDB/CDB, для удобства подключения установите переменную окружения среды TWO_TASK.
........................ == Книга пророка Иоиля == ............................
=== Глава 2, Стих 21 ===
20 И пришедшего от севера удалю от вас, и изгоню в землю безводную и пустую,
переднее полчище его - в море восточное, а заднее - в море западное, и
пойдет от него зловоние, и поднимется от него смрад, так как он много
наделал _зла._
21 Не бойся, земля: радуйся и веселись, ибо Господь велик, чтобы совершить
это.
(b+/b-, c+/c-, +/-, *) >
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production
...
11:53:00 SQL> create table customer (id number, name varchar2(100));
Table created.
Elapsed: 00:00:00.05
11:53:47 SQL>
11:54:18 SQL> create table visits (cust_id number, visit_date date);
Table created.
Elapsed: 00:00:00.01
11:55:01 SQL>
11:55:31 SQL> insert into customer values (1, 'ClientA');
1 row created.
Elapsed: 00:00:00.09
11:55:43 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
11:55:46 SQL>
11:55:54 SQL> insert into visits values (1, sysdate);
1 row created.
Elapsed: 00:00:00.03
11:56:09 SQL> /
1 row created.
Elapsed: 00:00:00.00
11:56:16 SQL> /
1 row created.
Elapsed: 00:00:00.01
11:56:26 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
11:56:29 SQL>
-- Добавим данных для создания неравномерного распределения.
12:37:35 SQL> insert into customer values(2, 'Client B');
1 row created.
Elapsed: 00:00:00.00
12:37:57 SQL> insert into customer values(3, 'Client C');
1 row created.
Elapsed: 00:00:00.01
12:38:12 SQL> insert into customer values(4, 'Client D');
1 row created.
Elapsed: 00:00:00.00
12:38:22 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
12:38:24 SQL>
12:38:42 SQL> insert into visits select 2, VISIT_DATE from visits;
3 rows created.
Elapsed: 00:00:00.01
12:39:19 SQL> c/2/3
1* insert into visits select 3, VISIT_DATE from visits
12:39:24 SQL> /
6 rows created.
Elapsed: 00:00:00.02
12:39:26 SQL> c/3/4
1* insert into visits select 4, VISIT_DATE from visits
12:39:30 SQL> /
12 rows created.
Elapsed: 00:00:00.01
12:39:31 SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
12:39:35 SQL>
12:39:48 SQL> select count(*) from customer;
COUNT(*)
----------
4
1 row selected.
Elapsed: 00:00:00.01
12:39:58 SQL> select count(*) from visits;
COUNT(*)
----------
24
1 row selected.
Elapsed: 00:00:00.02
12:40:09 SQL>
-- Теперь "раздуем" таблицу, копируя её саму в себя:
insert into visits select * from visits;
...
13:48:46 SQL> select count(*) from visits;
COUNT(*)
----------
25165824
1 row selected.
Elapsed: 00:00:00.28
13:48:59 SQL>
-- Создадим отдельного клиента с минимумом записей
10:32:21 SQL> insert into customer values (0, 'Small Client');
1 row created.
Elapsed: 00:00:00.00
10:32:57 SQL> insert into visits values (0, sysdate);
1 row created.
Elapsed: 00:00:00.01
10:33:19 SQL> /
1 row created.
Elapsed: 00:00:00.00
10:33:20 SQL> /
1 row created.
Elapsed: 00:00:00.00
10:33:20 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
10:33:22 SQL>
-- Создадим необходимые связи между таблицами
12:02:40 SQL> alter table customer add constraint PK_CUS_ID primary key (id);
Table altered.
Elapsed: 00:00:00.09
12:03:05 SQL> alter table visits add constraint FK_VIS_CUS -
> foreign key (cust_id) references customer(id);
Table altered.
Elapsed: 00:00:00.01
12:03:12 SQL>
create index IDX_VIS_CUSID on visits(cust_id) nologging;
Index created.
Elapsed: 00:00:00.08
-- Соберём статистику "по умолчанию" для всех объектов схемы
10:10:24 SQL> exec dbms_stats.gather_schema_stats(user);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.68
10:28:02 SQL>
10:28:53 SQL> l
1 select table_name, degree, last_analyzed, num_rows
2* from user_tables
10:28:55 SQL> /
10:34:16 SQL> select table_name, degree, last_analyzed, num_rows
10:34:25 2 from user_tables;
TABLE_NAME DEGREE LAST_ANALYZED NUM_ROWS
-------------------- ---------- ----------------- ----------
VISITS 1 18-03-14 10:34:16 25165827
CUSTOMER 1 18-03-14 10:34:14 5
2 rows selected.
Elapsed: 00:00:00.01
-- Which customer has most records?
1 select cust_id, count(*)
2 from visits
3 group by cust_id
4* order by 2
10:35:37 SQL> /
CUST_ID COUNT(*)
---------- ----------
0 3
2 3145728
1 3145728
3 6291456
4 12582912
5 rows selected.
Elapsed: 00:00:00.66
10:35:40 SQL>
После сбора статистики Оракл построил для нас частотную гистограмму для колонки CUST_ID таблицы VISITS:
.......... == Первое соборное послание святого апостола Иоанна == .............. === Глава 2, Стих 22 === 22 Кто лжец, если не тот, кто отвергает, что Иисус есть Христос? Это антихрист, отвергающий Отца и Сына. (b+/b-, c+/c-, +/-, *) > [oracle@localhost ~]$ sqlplus SQL*Plus: Release 12.1.0.1.0 Production ... SQL> l 1 select TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE, 2 ENDPOINT_REPEAT_COUNT from user_histograms 3* order by TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER 10:38:29 SQL> / TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_REPEAT_COUNT -------------------- ---------- --------------- -------------- --------------------- CUSTOMER ID 0 0 0 CUSTOMER ID 1 4 0 CUSTOMER NAME 0 3.5008E+35 0 CUSTOMER NAME 1 4.3318E+35 0 VISITS CUST_ID 3 0 0 VISITS CUST_ID 3145731 1 0 VISITS CUST_ID 6291459 2 0 VISITS CUST_ID 12582915 3 0 VISITS CUST_ID 25165827 4 0 VISITS VISIT_DATE 0 2456734.5 0 VISITS VISIT_DATE 1 2456735.44 0 11 rows selected. Elapsed: 00:00:00.08 10:38:31 SQL> 1 select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM 2 from user_tab_col_statistics 3* where HISTOGRAM != 'NONE' 10:47:38 SQL> / TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM -------------------- ---------- ------------ ----------- --------------- VISITS CUST_ID 5 5 FREQUENCY 1 row selected. Elapsed: 00:00:00.48 10:47:39 SQL>
Существуют параметры, определяющие эффект адаптивных планов, по умолчанию оптимизатор запросов будет их создавать и использовать:
12:23:06 SQL> show parameter adaptive NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_adaptive_features boolean TRUE optimizer_adaptive_reporting_only boolean FALSE parallel_adaptive_multi_user boolean TRUE 12:30:02 SQL>
Итак, данные созданы - пора перейти к запросу.
Запрос пользователя и начальный план его выполнения
Запрос очень простой и предсказуемый, посмотрим какой план Оракл нам предложит до его исполнения:
1 explain plan for
2 select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date
3 from customer c, visits v
4 where c.id = v.cust_id
5 and c.id > :custid
6 and v.visit_date < sysdate -1
7* order by v.visit_date
11:55:21 SQL> /
Explained.
Elapsed: 00:00:00.03
11:55:21 SQL>
11:55:22 SQL>
11:55:22 SQL>
11:55:22 SQL>
11:55:22 SQL> select * from table(dbms_xplan.display(format=>'ADAPTIVE NOTE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3793304633
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 6 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 24 | 6 (0)| 00:00:01 |
|- * 2 | HASH JOIN | | 1 | 24 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1 | 24 | 6 (0)| 00:00:01 |
|- 5 | STATISTICS COLLECTOR | | | | | |
| * 6 | TABLE ACCESS FULL | VISITS | 1 | 11 | 5 (0)| 00:00:01 |
| * 7 | INDEX UNIQUE SCAN | PK_CUS_ID | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 13 | 1 (0)| 00:00:01 |
|- 9 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 1 | 13 | 1 (0)| 00:00:01 |
|- * 10 | INDEX RANGE SCAN | PK_CUS_ID | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."ID"="V"."CUST_ID")
6 - filter("V"."CUST_ID">TO_NUMBER(:CUSTID) AND "V"."VISIT_DATE"TO_NUMBER(:CUSTID))
10 - access("C"."ID">TO_NUMBER(:CUSTID))
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
30 rows selected.
Elapsed: 00:00:00.17
За исключением создания адаптивного плана, ничего выдающегося. Оптимизатор решил не использовать Hash Join ввиду малого числа ожидаемых строк (всего одна).
Hint "gather_plan_statistics" нам понадобится во всех тестах, наравне с другим полезным хинтом "monitor". Почитайте в документации про них. Не забудьте проверить ваше лицензирование вообще, в том числе если используете "management pack".
Запускаем запрос и проверяем действительный план выполнения
Вы наверняка помните, что "explain plan" более не является полностью надежной утилитой - он может показать одно, а на самом деле запрос будет выполнен иначе. Запустим SQL и после его завершения проверим план по "sql_id".
11:56:01 SQL> print :custid
CUSTID
----------
2
11:58:21 SQL>
1 select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date
2 from customer c, visits v
3 where c.id = v.cust_id
4 and c.id > :custid
5 and v.visit_date < sysdate -1
6* order by v.visit_date
11:58:53 SQL> /
no rows selected
Elapsed: 00:00:00.01
11:58:53 SQL>
Итак, оптимизатор был неправ - наш запрос не вернул строк вообще. Определим sql_id запроса и проверим как же он был выполнен.
1 select LAST_ACTIVE_TIME, sql_id, sql_text, child_number, IS_REOPTIMIZABLE,
2 IS_RESOLVED_ADAPTIVE_PLAN, fetches, executions, end_of_fetch_count,
3 rows_processed, plan_hash_value from v$sql
4* where sql_text like '%Test1%'
12:00:55 SQL> /
LAST_ACTIVE_TIME SQL_ID
----------------- -------------
SQL_TEXT
----------------------------------------------------------------------------------------
CHILD_NUMBER I I FETCHES EXECUTIONS END_OF_FETCH_COUNT ROWS_PROCESSED PLAN_HASH_VALUE
------------ - - ---------- ---------- ------------------ -------------- ---------------
24-03-14 11:58:53 7spzkn428fjdu
select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date from customer c, visits v
where c.id = v.cust_id and c.id > :custid and
v.visit_date < sysdate -1 order by v.visit_date
0 N Y 1 1 1 0 865602956
SQL> select * from table(dbms_xplan.display_cursor('7spzkn428fjdu', 0, -
> 'ADAPTIVE NOTE ALLSTATS'))
13:45:52 SQL> /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID 7spzkn428fjdu, child number 0
-------------------------------------
select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date from
customer c, visits v where c.id = v.cust_id and c.id > :custid and
v.visit_date < sysdate -1 order by v.visit_date
Plan hash value: 865602956
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 2 |
| 1 | SORT ORDER BY | | 1 | 1 | 0 |00:00:00.01 | 2 |
|- * 2 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 2 |
| 3 | NESTED LOOPS | | 1 | | 0 |00:00:00.01 | 2 |
| 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 2 |
|- 5 | STATISTICS COLLECTOR | | 1 | | 0 |00:00:00.01 | 2 |
| * 6 | TABLE ACCESS BY INDEX ROWID BATCHED| VISITS | 1 | 1 | 0 |00:00:00.01 | 2 |
| * 7 | INDEX RANGE SCAN | IDX_VIS_CUSID | 1 | 2 | 0 |00:00:00.01 | 2 |
| * 8 | INDEX UNIQUE SCAN | PK_CUS_ID | 0 | 1 | 0 |00:00:00.01 | 0 |
| 9 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 0 | 1 | 0 |00:00:00.01 | 0 |
|- 10 | TABLE ACCESS BY INDEX ROWID BATCHED | CUSTOMER | 0 | 1 | 0 |00:00:00.01 | 0 |
|- * 11 | INDEX RANGE SCAN | PK_CUS_ID | 0 | 1 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."ID"="V"."CUST_ID")
6 - filter("V"."VISIT_DATE":CUSTID)
8 - access("C"."ID"="V"."CUST_ID")
filter("C"."ID">:CUSTID)
11 - access("C"."ID">:CUSTID)
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Итак, при выполнении использовался слегка другой план (сравните hash плана в предыдущем подразделе и этого) - оптимизатор решил использовать индексы для обеих таблиц. Но этот план тоже был адаптивным, при этом оптимизатор не был уверен надо ли делать Hash Join или Nested Loops до тех пор, пока не начал получать (отсутствующие) результаты.
Обратили ли вы внимание на привязку запроса к текущей дате? С тем же самым значением переменной :custid (равному 2) через 24 часа этот же запрос вернёт миллионы строк и оптимизатор решит "переключиться" во время исполнения на Hash Join. Правда, при этом необходимо очистить library cache или дождаться естественного устаревания и исчезновения уже ставшего постоянным плана с хэшем 865602956 (об этом свидетельствует IS_RESOLVED_ADAPTIVE_PLAN=Y в результате запроса вверху). Так что, по-прежнему, ещё есть возможность "обмануть" оптимизатор. Надо отметить, что другие средства оптимизации могут заставить Оракл сгенерировать новый фиксированный план даже при уже имеющемся адаптивном плане (например, обновление или устаревание статистики) - но в нашем случае привязки к дате вполне вероятна ситуация, когда миллионы строк будут возвращаться через Nested Loops. Я оставляю этот случай для вас в качестве упражнения.
Скрипт для тестирования
Используйте этот скрипт для тестирования разных условий и планов. Результаты должны показать, что внесение потенциальной "неясности" сразу же приводит к генерации изменяемого плана. Соответственно, ясно выраженные условия, при наличии гистограммы и правильной статистики, приводят к использованию простого фиксированного постоянного плана с самого начала.
var custid number exec :custid:=2; print :custid; explain plan for select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date from customer c, visits v where c.id = v.cust_id -- Откомментируйте следующие строки по одной --and c.id = :custid -- fixed plan --and c.id > :custid -- adaptive plan --and v.visit_date < sysdate -1 -- adaptive plan --and v.visit_date = :custid -- fixed plan order by v.visit_date / select * from table(dbms_xplan.display(format=>'ADAPTIVE NOTE')) / rollback /
В конце тестирования, измените степень параллельного выполнения на таблицах и повторите тесты в приведенном выше скрипте. Изменятся ли полученные планы?
12:05:11 SQL> alter table VISITS parallel 8; Table altered. Elapsed: 00:00:00.01 12:05:19 SQL> alter table CUSTOMER parallel 8; Table altered.
Смогли ли вы добиться адаптивных планов при параллельном выполнении запросов?
Моё тестирование показало, что в текущей версии Оракла 12.1.0.1 адаптивные планы используются только когда возможна ошибка оптимизации. Они используются почти всегда при наличии условий с неравенством и однопоточного выполнения запроса. В случае параллельного выполнения адаптивные планы почти никогда не генерируются. Также для создания изменяемых планов используйте PDB, подключаясь к ней как локальный пользователь - во многих случаях оптимизатор не генерирует адаптивные планы при подключении к контейнеру CDB$ROOT или не-CDB базе данных.
Пример, использующий схему "OE"
Поставляемая с Оракл схема Order Entry (загрузите и установите "Oracle Database 12c Release 1 Examples") тоже генерирует адаптивные планы. Используем простой запрос:
SQL> l 1 explain plan for 2 select product_name 3 from oe.order_items o, oe.product_information p 4 where o.unit_price = 15 5 and o.quantity >1 6* and p.product_id = o.product_id 09:49:55 SQL>
Какой план нам "предскажет" оптимизатор до выполнения запроса?
1* select * from table(dbms_xplan.display(format=>'ADAPTIVE NOTE ALLSTATS'))
09:50:54 SQL> /
PLAN_TABLE_OUTPUT
------------------------------
Plan hash value: 1553478007
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 |
| * 1 | HASH JOIN | | 13 |
|- 2 | NESTED LOOPS | | |
|- 3 | NESTED LOOPS | | 13 |
|- 4 | STATISTICS COLLECTOR | | |
| * 5 | TABLE ACCESS FULL | ORDER_ITEMS | 13 |
|- * 6 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | |
|- 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 |
| 8 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 288 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1)
6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
29 rows selected.
Elapsed: 00:00:00.04
09:50:54 SQL>
Выполним запрос:
1 select product_name 2 from oe.order_items o, oe.product_information p 3 where o.unit_price = 15 4 and o.quantity >1 5* and p.product_id = o.product_id 09:52:17 SQL> / PRODUCT_NAME -------------------------------------------------- ScrewsScrews Screws Screws Screws Screws Screws Screws Screws Screws Screws Screws Screws 13 rows selected. Elapsed: 00:00:00.01
Похоже, оптимизатор угадал число возвращаемых строк (13) - а это значит, что изначальная версия адаптивного плана окажется приемлемой, план не должен измениться, код ветвления не будет выполнен ни разу и план просто превратится в постоянный в конце первого запуска запроса. Проверим это:
SQL_ID 8bwz2v2542zm4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ product_name from oe.order_items
o, oe.product_information p where o.unit_price = 15 and o.quantity >1
and p.product_id = o.product_id
Plan hash value: 1553478007
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:00:00.01 | 24 | | | |
| * 1 | HASH JOIN | | 1 | 13 | 13 |00:00:00.01 | 24 | 2061K| 2061K| 1/0/0|
|- 2 | NESTED LOOPS | | 1 | | 13 |00:00:00.01 | 7 | | | |
|- 3 | NESTED LOOPS | | 1 | 13 | 13 |00:00:00.01 | 7 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | 13 |00:00:00.01 | 7 | | | |
| * 5 | TABLE ACCESS FULL | ORDER_ITEMS | 1 | 13 | 13 |00:00:00.01 | 7 | | | |
|- * 6 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 0 | | 0 |00:00:00.01 | 0 | | | |
|- 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 8 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 288 | 288 |00:00:00.01 | 17 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Всё оказалось именно так - даже хэш плана до и после выполнения остался неизменным. Что ещё раз показывает исключительную важность правильной статистики для SQL оптимизатора версии Oracle 12c. Кстати, статистика теперь тоже стала адаптивной, как и планы - но это уже другая история.
Спасибо что зашли,
Будьте благословенны!
Денис
27 Марта 2014 года.
