Базы данных Oracle - статьи

         

Том Кайт: о разделении, расщеплении и удалении (On Sharing, Splitting, and Deleting, By Tom Kyte)


Источник: журнал Oracle Magazine, January-February 2006

(http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html).

Том Кайт рассматривает совместное использование курсоров, расщепление больших таблиц и надежное удаление строк.

Вопрос. Я немного озадачен вашей фразой: "Если вы будете использовать параметр CURSOR_SHARING=SIMILAR, вы можете уменьшить количество сгенерированных планов выполнения, а, с другой стороны, вы можете иметь точно такое же количество планов".

Что влияет на количество сгенерированных планов? Я думал, что если в параметре CURSOR_SHARING установить значение SIMILAR, то оптимизатор заменит все литералы на переменные связывания:
SYS_B _? – это то, что мы видим в нашем сервере базы данных.

Ответ. Параметр CURSOR_SHARING используется в сервере Oracle Database для управления "автоматическим связыванием" в SQL-операторах. Сервер может в запросе SELECT * FROM TABLE WHERE COL = 'литерал' заменить 'литерал' на переменную связывания, и предикат будет выглядеть так: WHERE COL = :"SYS_B_0". Это позволяет многократно использовать сгенерированный план выполнения запроса, что, возможно, приведет к лучшему использованию разделяемого пула и уменьшению полных разборов (hard parses), выполняемых системой. Параметр CURSOR_SHARING может иметь одно из трех значений:

  • EXACT (точное соответствие): это – установка по умолчанию. Это значение запрещает перезапись запросов, чтобы они использовали переменные связывания;
  • FORCE (безусловное): это значение разрешает перезапись запросов, которая заменяет все литералы на переменные связывания, и создание плана выполнения "на все случаи жизни" – один план для перезаписанного запроса. Через минуту я покажу, что это означает.
  • SIMILAR (аналогичные операторы): это значение разрешает перезапись запросов, которая заменяет все литералы на переменные связывания, но для разных комбинаций переменных связывания могут генерироваться разные планы выполнения. Использование параметра CURSOR_SHARING=SIMILAR может или не может уменьшать количество сгенерированных планов, поскольку могут генерироваться множественные планы, которые можно увидеть в разделяемом пуле.

  • Давайте сначала рассмотрим, что будет происходить при выполнении одинакового набора запросов с этими тремя значениями параметра. Запросы будут простыми: SELECT * FROM DUAL WHERE DUMMY = <что-то>, где вместо <что-то> будут использоваться литералы 'A' и 'B'. Затем я, используя динамическое представление производительности V$SQL, посмотрю в разделяемом пуле, сколько для каждого запроса было создано курсоров. На листинге 1 показана установка трех значений параметра CURSOR_SHARING, выполнение операторов SELECT и содержимое столбца SQL_TEXT представления V$SQL, показывающее фактические SQL-операторы, которые были использованы в запросах.

    SQL> alter session set cursor_sharing=exact; Session altered.

    SQL> select * from dual CS_EXACT where dummy = 'A'; no rows selected

    SQL> select * from dual CS_EXACT where dummy = 'B'; no rows selected

    SQL> alter session set cursor_sharing=force; Session altered.

    SQL> select * from dual CS_FORCE where dummy = 'A'; no rows selected

    SQL> select * from dual CS_FORCE where dummy = 'B'; no rows selected

    SQL> alter session set cursor_sharing=similar; Session altered.



    SQL> select * from dual CS_SIMILAR where dummy = 'A'; no rows selected

    SQL> select * from dual CS_SIMILAR where dummy = 'B'; no rows selected

    SQL> select sql_text 2 from v$sql 3 where sql_text like 'select * from dual CS% where dummy = %' 4 order by sql_text;

    SQL_TEXT --------------------------------------------------------- select * from dual CS_EXACT where dummy = 'A' select * from dual CS_EXACT where dummy = 'B' select * from dual CS_FORCE where dummy = :"SYS_B_0" select * from dual CS_SIMILAR where dummy = :"SYS_B_0"

    Листинг 1. Значения параметра CURSOR_SHARING: EXACT, FORCE и SIMILAR.

    Как видно на листинге 1, с установленным параметром CURSOR_SHARING=EXACT (устанавливается по умолчанию) для каждого уникального SQL-оператора, который я выполняю, в представлении V$SQL создается новая запись – выполняется полный разбор оператора и для него создается новый план выполнения. В разделяемом пуле могут находиться сотни и тысячи очень похожих запросов, которые отличаются только литералами, используемыми в SQL-операторах. Это означает, что в приложении не используются переменные связывания, и это также означает, что сервер базы данных вынужден выполнять полный разбор практически каждого запроса, который, в свою очередь, не только потребляет много процессорного времени, но также приводит и к уменьшению масштабируемости. Сервер не может одновременно выполнять полный разбор сотен и тысяч SQL-операторов, поэтому приложение приостанавливается, ожидая доступа к разделяемому пулу. Один из главных факторов уменьшения масштабируемости в сервере базы данных – не использование переменных связывания. Это было причиной появления а сервере Oracle8i Release 2 (8.1.6) параметра CURSOR_SHARING=FORCE.

    С установленным параметром CURSOR_SHARING=FORCE сервер базы данных, как видно на листинге 1, сгенерировал в разделяемом пуле только один разделяемый запрос, заменив 'A' и 'B' на :"SYS_B_0" и сделав курсор совместно используемым многими сеансами, которым он потребовался бы. Вообще говоря, все сеансы многократно использовали бы только один план запроса. Это превратило бы полный разбор в частичный разбор (soft parse), который будет потреблять меньше ресурсов и одновременно увеличивать масштабируемость системы, обеспечивая больший параллелизм, поскольку частичному разбору по сравнению с полным разбором требуется меньше "защелкивать" разделяемый пул (использовать определенный тип блокирования).

    Тем не менее, рассматривая пример на листинге 1, вы можете предположить, что установка значений FORCE и SIMILAR приводит к одному и тому же результату, поскольку генерируются одинаковые планы. Итак, какое же различие между этими двумя установками? Для того чтобы показать это различие, мне потребуется другой пример, но сначала я расскажу об этом. Когда в параметре CURSOR_SHARING установлено значение SIMILAR, сервер Oracle Database заменяет все литералы на переменные связывания (так же как и при установке значения FORCE), но в этом случае сервер делает еще одну вещь – он смотрит на каждый заменяемый литерал и задается вопросом: "Могут ли различные значения этой переменной связывания приводить к генерации разных планов?" Например, использование предиката WHERE X=6 подразумевает, что желательно выполнять полный просмотр, а использование предиката WHERE X=5 подразумевает, что желательно выполнять просмотр диапазона по индексу, сервер базы данных распознает такие ситуации и генерирует разные планы. В случае разных планов переменная связывания помечается как ненадежная (unsafe) и ее значение добавляется к сигнатуре этого запроса, так что для повторного использования этого курсора необходимо не только иметь похожие SQL-операторы, но и такое же значение этой конкретной переменной связывания.

    Именно поэтому установка значения SIMILAR может, а, с другой стороны, не может уменьшать количество сгенерированных планов, которые можно увидеть в разделяемом пуле. Чтобы показать это, я создам таблицу с некоторыми данными, имеющими очень асимметричное распределение, так что, когда я выполняю запрос с предикатом WHERE ID=1, сервер Oracle Database захочет использовать индекс столбца ID, а когда я выполняю запрос с предикатом WHERE ID=99, сервер Oracle Database не захочет использовать индекс. На листинге 2 показано создание таблицы с данными, имеющими асимметричное распределение, индекса, а также планы выполнения запросов к этим данным.



    SQL> create table t 2 as 3 select decode(rownum,1,1,99) ID, 4 all_objects.* 5 from all_objects 6 / Table created.

    SQL> create index t_idx on t (id); Index created.

    SQL> begin 2 dbms_stats.gather_table_stats 3 ( ownname => USER, 4 tabname => 'T', 5 method_opt => ' for all indexed columns size 254', 6 cascade => TRUE 7 ); 8 end; 9 / PL/SQL procedure successfully completed.

    SQL> set autotrace traceonly explain SQL> select * from t where id=1;

    Execution Plan ---------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=96) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=96) 2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)

    SQL> select * from t where id=99;

    Execution Plan --------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=197 Card=48028 Bytes=4610688) 1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=197 Card=48028 Bytes=4610688)

    Листинг 2: Создание таблицы с данными, имеющими асимметричное распределение, и индекса, а также планы выполнения запросов к этим данным.

    Таблица T, показанная на листинге 2, содержит столбец ID, данные которого имеют очень асимметричное распределение – большинство значений равно 99, и только одна запись содержит значение, равное 1. После того, как я создал индекс и собрал статистику по таблице (с генерацией гистограмм по индексированному столбцу, поэтому оптимизатор знает, что данные имеют асимметричное распределение), я вижу, что оптимизатор предпочитает просмотр диапазона по индексу, когда ID=1, и полный просмотр, когда ID=99.

    Теперь, давайте выполним запросы к этой таблице с различным предикатами ID=, используя сначала установку CURSOR_SHARING=FORCE, а затем EXACT, как показано на листинге 3 (я уже знаю, чего следует ожидать от установки CURSOR_SHARING=EXACT – для каждого уникального значения столбца ID будет генерироваться отдельный план).



    SQL> alter session set cursor_sharing=force; Session altered. SQL> select * from t CS_FORCE where id = 1; 1 row selected. SQL> select * from t CS_FORCE where id = 50; no rows selected SQL> select * from t CS_FORCE where id = 99; 48029 rows selected. SQL> select * from t CS_FORCE where id = 1; 1 row selected. SQL> select * from t CS_FORCE where id = 50; no rows selected SQL> select * from t CS_FORCE where id = 99; 48029 rows selected. SQL> alter session set cursor_sharing=similar; Session altered. SQL> select * from t CS_SIMILAR where id = 1; 1 row selected. SQL> select * from t CS_SIMILAR where id = 50; no rows selected SQL> select * from t CS_SIMILAR where id = 99; 48029 rows selected. SQL> select * from t CS_SIMILAR where id = 1; 1 row selected. SQL> select * from t CS_SIMILAR where id = 50; no rows selected SQL> select * from t CS_SIMILAR where id = 99; 48029 rows selected. SQL> select sql_text 2 from v$sql 3 where sql_text like 'select * from t CS% where id = %' 4 order by sql_text; SQL_TEXT ------------------------------------------------ select * from t CS_FORCE where id = :"SYS_B_0" select * from t CS_SIMILAR where id = :"SYS_B_0" select * from t CS_SIMILAR where id = :"SYS_B_0" select * from t CS_SIMILAR where id = :"SYS_B_0"

    Листинг 3: Значения FORCE, SIMILAR и данные с асимметричным распределением.

    Как видно на листинге 3, когда установлено CURSOR_SHARING=FORCE, генерируется один и только один план выполнения. Это, фактически, план "на все случаи жизни", и в этом случае в нем используется просмотр диапазона по индексу (поскольку оптимизатор для генерации плана обязательно должен был использовать переменную связывания, а в первом разобранном запросе использовался предикат ID=1).

    Однако, как видно на листинге 3, когда установлено CURSOR_SHARING=SIMILAR, генерируются три плана, поскольку оптимизатор обнаружил, что для поиска по значениям столбца ID используются различные значения этого столбца, могущие приводить к генерации различных планов (эту информацию дает ему статистика, сбор которой показан на листинге 2). Следовательно, фактическое значение переменной связывания было добавлено к сигнатуре этого плана запроса, и только запрос с точно такой же сигнатурой мог снова использовать этот план. В этом состояла цель выполнения каждого из запросов два раза – показать, что возможно повторное использование курсора. В представлении V$SQL нет шести запросов, есть только четыре. При установке CURSOR_SHARING=SIMILAR повторное использование курсора не гарантируется намеренно.

    Итак, означает ли это, что при установке CURSOR_SHARING=SIMILAR для любого уникального набора литералов будет генерироваться новый план? Нет, я уже показывал на листинге 1 пример с таблицей DUAL, когда использовались предложения WHERE DUMMY='A' и WHERE DUMMY='B'. Новый план генерируется только тогда, когда подстановка переменной связывания считается ненадежной. Используя пример из листинга 2, когда выполнялось только ненадежное связывание по столбцу ID, я выполню запрос по этому столбцу и по некоторому другому столбцу, но не буду изменять в предикате значение столбца ID, то увижу повторное использование курсора, как это показано на листинге 4.



    SQL> alter session set cursor_sharing=similar; Session altered.

    SQL> select * from t CS_SIMILAR where id=1 and object_id=1; no rows selected SQL> select * from t CS_SIMILAR where id=1 and object_id=2; no rows selected SQL> select * from t CS_SIMILAR where id=1 and object_id=3; no rows selected

    SQL> select sql_text 2 from v$sql 3 where sql_text like 'select * from t CS% where id = % and object_id=%' 4 order by sql_text;

    SQL_TEXT ------------------------------------------------------------------------- select * from t CS_SIMILAR where id = :"SYS_B_0" and object_id=:"SYS_B_1"

    Листинг 4: Установка CURSOR_SHARING=SIMILAR.

    Как показано на этом листинге, я изменял литералы в предикатах со столбцом OBJECT_ID, но не со столбцом ID. Оптимизатор распознал, что значения столбца OBJECT_ID надежны, и ему для разных значений этого столбца в предикатах не нужно генерировать разные планы, поэтому он не добавлял к сигнатуре курсора значения этого столбца. Только тогда, когда в предикате используются разные значения столбца ID, будут генерироваться новые планы.

    Итак, это показывает, что установка CURSOR_SHARING=SIMILAR может уменьшать количество записей, которые вы видите в разделяемом пуле. Если бы это приложение должно было изменять литералы в предикатах со столбцом ID и использовало бы сотни и тысячи уникальных значений, установка CURSOR_SHARING=SIMILAR не оказывала бы существенного влияния на использование разделяемого пула. С другой стороны, если бы приложение использовало в предикатах со столбцом ID только два значения, то установка CURSOR_SHARING=SIMILAR могла бы оказывать в высшей степени позитивное воздействие на использование разделяемого пула.


    Содержание раздела