архив
Том Кайт: о разделении, расщеплении и удалении
(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 (устанавливается по умолчанию) для каждого уникального 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)
Таблица 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, когда установлено 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"
Как показано на этом листинге, я изменял литералы в предикатах со столбцом 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.
Я хочу предотвратить возникновение второй ситуации. То есть, я хочу гарантировать, что выполняются только удаления из родительской таблицы (в результате которых удаляются и дочерние записи), а отдельные удаления из дочерней таблицы не выполняются. Проблема заключается в том, что сначала всегда выполняются удаления из дочерней таблицы, а затем – из родительской. Так, если я пытаюсь добиться этого с помощью триггера на дочерней таблице, но это не помогает, поскольку во время выполнения операции удаления из дочерней таблицы, нет никакого способа узнать, будет ли затем следовать удаление из родительской таблицы. Я предпочел бы простое решение (их, наверное, много), потому что экзотические, нестандартные решения могут оказаться у нас нереализуемыми.
|
СПРАШИВАЙТЕ Тома |
Ответ. Для меня это просто защита данных. Вы не хотите удалять строки непосредственно из дочерней таблицы, следовательно, никому не предоставляйте объектную привилегию 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. Расширение возможностей и защита. – ДиаСофт.).