Том Кайт: о разделении, расщеплении и удалении

2007 г.

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

“Oracle Magazine/Русское издание”

Источник: журнал 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 могла бы оказывать в высшей степени позитивное воздействие на использование разделяемого пула.

Расщепление большой таблицы

Вопрос. Я хотел бы распределить диапазон значений по сбалансированным наборам. Я исходил из того, что для этого может быть полезной одна из аналитических функций, и решил изучить их более подробно. Я ставил перед собой вопрос: "Как "разбить" упорядоченный список значений на диапазоны, а затем перечислить первое и последнее значение каждого диапазона"? Например:

Range        Start        End
------       ------       -----
0            1            1000
1            1001         2000
2            2001         3000

Ответ. Это нечто подобное тому, что я часто реализовываю, называя это "самодельным параллелизмом" (do-it-yourself parallelism). Идея заключается в том, чтобы разбить большую таблицу на некоторое число неперекрывающихся диапазонов, запустив несколько параллельных процессов.

Это просто сделать, используя встроенную функцию NTILE(). Если нужно разбить представление словаря данных ALL_OBJECTS на восемь неперекрывающихся диапазонов с приблизительно одинаковым числом строк в каждом, можно использовать следующий код:

SQL> select min(object_id) min,
  2         max(object_id) max,
  3         count(*) cnt,
  4         nt
  5    from
  6  (
  7  select object_id,
  8         ntile(8) over
  9        (order by object_id) nt
 10    from all_objects
 11  )
 12  group by nt;
 
     MIN       MAX      CNT     NT
   -----     -----    -----    ----
       2      6811     6005       1
    6812     13117     6005      2
   13118     19122     6005      3
   19123     25127     6005      4
   25128     31132     6005      5
   31133     37142     6004      6
   37143     44620     6004      7
   44621     98225     6004      8
 
8 rows selected.

Много раз я повторял на сайте asktom.oracle.com: аналитика – это круто. Аналитика – лучшая вещь, появившаяся в языке SQL после введения ключевого слова SELECT.

Проблема с каскадным удалением

Вопрос. Допустим, у меня есть две таблицы, T1 (родительская) и T2 (дочерняя), которая связана с родительской таблицей внешним ключом с предложением каскадного удаления: REFERENCES T1(X,Y,Z...) ON DELETE CASCADE. Строки могут удаляться из таблицы T1, в этом случае из таблицы T2 также удаляются дочерние строки. Точно так же строки могут удаляться непосредственно из таблицы T2, но без соответствующего удаления родительских строк из таблицы T1.

Я хочу предотвратить возникновение второй ситуации. То есть, я хочу гарантировать, что выполняются только удаления из родительской таблицы (в результате которых удаляются и дочерние записи), а отдельные удаления из дочерней таблицы не выполняются. Проблема заключается в том, что сначала всегда выполняются удаления из дочерней таблицы, а затем – из родительской. Так, если я пытаюсь добиться этого с помощью триггера на дочерней таблице, но это не помогает, поскольку во время выполнения операции удаления из дочерней таблицы, нет никакого способа узнать, будет ли затем следовать удаление из родительской таблицы. Я предпочел бы простое решение (их, наверное, много), потому что экзотические, нестандартные решения могут оказаться у нас нереализуемыми.

Следующие шаги:

СПРАШИВАЙТЕ Тома
Том Кайт, вице-президент корпорации Oracle, отвечает на наиболее трудные вопросы, связанные с технологией баз данных Oracle. Наиболее яркие материалы этого форума публикуются в данной колонке.
ЧИТАЙТЕ более подробно о
совместном использовании курсоров
Oracle Database Performance Tuning Guide
аналитических функциях
Oracle Database SQL Reference ЧИТАЙТЕ еще Тома
Expert Oracle: 9i and 10g Programming Techniques and Solutions

Ответ. Для меня это просто защита данных. Вы не хотите удалять строки непосредственно из дочерней таблицы, следовательно, никому не предоставляйте объектную привилегию DELETE на эту таблицу. Это можно сделать легко:

SQL> create table p
  2  ( x int primary key );
Table created.

SQL> create table c
  2  ( x references p
  3      on delete cascade );
Table created.

SQL> insert into p
  2  values ( 1 );
1 row created.

SQL> insert into c
  2  values ( 1 );
1 row created.

SQL> grant select, delete
  2  on p to scott;
Grant succeeded.

SQL> grant select
  2  on c to scott;
Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> delete from ops$tkyte.c;
delete from ops$tkyte.c
                      *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> delete from ops$tkyte.p;
1 row deleted.

SQL> select * from ops$tkyte.c;
no rows selected

Вы можете сказать: "Да, но администратор базы данных может войти в систему и удалить эти строки...". Я же скажу: "Да, но администратор базы данных может обойти все что угодно, установленное вами, – все что угодно, ну и что"? Владелец схемы может сделать то же самое; поэтому вы просто защищаете данные, чтобы никто (на уровне приложений) не мог удалить их.

Есть ли способы сделать это с помощью триггеров? Да, но писать их довольно сложно (и их также можно обмануть).
Средства защиты делают это чисто.

Фактически, если вы использовали язык PL/SQL и никогда не предоставляли никаких объектных привилегий INSERT/UPDATE/DELETE, подумайте, насколько "безопасно" вы могли сделать это!

Ведущий данной колонки Том Кайт (Tom Kyte) работает в корпорации Oracle с 1993 года. Кайт – вице-президент Oracle, возглавляющий группу Oracle Public Sector, он автор книг "Expert Oracle: 9i and 10g Programming Techniques and Solutions" (Apress, 2005), "Effective Oracle by Design" (Oracle Press, 2003) и "Expert One on One: Oracle" (Apress, 2003) ( Прим. пер. Имеется русский перевод: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Книга 2. Расширение возможностей и защита. – ДиаСофт.).