Преамбула: когда-то давно, лет пять назад, я стоял у истоков одной большой информационной системы, был ее архитектором. Сейчас занимаюсь поддержкой и развитем этой системы. В основе выбранной мною архитектуры лежало понятие изолированных подсистем, каждая подсистема находится в отдельной схеме базы данных. Экземпляр системы состоит из набора схем, имеющих дозированный доступ друг к другу. Кроме того, информационная система является распределенной, т. е. происходит репликация данных между узлами. При тестировании, чтобы иметь возможность развернуть несколько экземпляров системы в одной физической базе, было принято решение использовать для имен схем префиксы, а для обращения подсистем друг к другу - приватные синонимы. Система пошла в "production", и вот, уже более чем через 5 лет эксплуатации, в центральном ее узле назрела проблема - количество синонимов приближается к 100 тысячам. Таже проблема, кстати, и в девелоперской базе.
Амбула: недавно устанавливал обновления приложений центральной базы. Процесс очень затянулся. Одним из основных потребителей времени оказался SQL*Loader. Дело в том, что в процессе обновления грузилось очень много мелких файлов с помощью указанной утилиты. Собственно сама загрузка происходила быстро, но перед загрузкой, SQL*Loader обращался к представлению ALL_SYNONYMS. На это, в моем случае, уходило порядка 30 секунд.
План соответствующего запроса имеет угрожающий вид:
SQL_ID 41zw01wh8s59a, child number 0
SELECT TABLE_NAME, NVL(TABLE_OWNER, ''), NVL(DB_LINK, '') FROM SYS.ALL_SYNONYMS WHERE SYNONYM_NAME = :1 AND OWNER = :2
Plan hash value: 3788280054
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | VIEW | ALL_SYNONYMS | 1 | 2 | 1 |00:00:30.26 | 4601 | | | |
| 2 | SORT UNIQUE | | 1 | 2 | 1 |00:00:30.26 | 4601 | 2048 | 2048 | 2048 (0)|
| 3 | UNION-ALL | | 1 | | 1 |00:00:30.26 | 4601 | | | |
|* 4 | FILTER | | 1 | | 1 |00:00:00.01 | 8 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 8 | | | |
| 6 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 8 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 10 | INDEX RANGE SCAN | I_OBJ2 | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | SYN$ | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 12 | INDEX UNIQUE SCAN | I_SYN1 | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 13 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | | |
|* 14 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 15 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 16 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 18 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 20 | INDEX RANGE SCAN | I_OBJ2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 21 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 22 | FIXED TABLE FULL | X$KZSRO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 23 | FIXED TABLE FULL | X$KZSPR | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 24 | NESTED LOOPS | | 1 | 1 | 0 |00:00:30.26 | 4593 | | | |
|* 25 | HASH JOIN | | 1 | 1 | 0 |00:00:30.26 | 4593 | 700K| 700K| |
| 26 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 28 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 30 | INDEX RANGE SCAN | I_OBJ2 | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 31 | VIEW | _ALL_SYNONYMS_TREE | 1 | 4098 | 32 |00:00:30.26 | 4588 | | | |
|* 32 | CONNECT BY WITH FILTERING | | 1 | | 32 |00:00:30.26 | 4588 | 6144 | 6144 | 6144 (0)|
|* 33 | FILTER | | 1 | | 32 |00:00:15.28 | 2544 | | | |
| 34 | COUNT | | 1 | | 38 |00:00:15.28 | 2044 | | | |
|* 35 | HASH JOIN | | 1 | 4098 | 38 |00:00:15.28 | 2044 | 1034K| 1034K| |
| 36 | TABLE ACCESS FULL | USER$ | 1 | 335 | 335 |00:00:00.01 | 14 | | | |
|* 37 | HASH JOIN | | 1 | 4103 | 38 |00:00:15.28 | 2030 | 1033K| 870K| |
|* 38 | HASH JOIN | | 1 | 4103 | 38 |00:00:14.67 | 1154 | 819K| 819K| |
| 39 | TABLE ACCESS FULL | USER$ | 1 | 335 | 335 |00:00:00.01 | 14 | | | |
|* 40 | HASH JOIN | | 1 | 1376K| 3378K|00:00:03.80 | 1140 | 6425K| 2078K| 9700K (0)|
| 41 | TABLE ACCESS FULL | SYN$ | 1 | 94997 | 95067 |00:00:00.10 | 264 | | | |
|* 42 | TABLE ACCESS FULL | OBJ$ | 1 | 95290 | 95069 |00:00:00.19 | 876 | | | |
| 43 | TABLE ACCESS FULL | OBJ$ | 1 | 143K| 143K|00:00:00.14 | 876 | | | |
|* 44 | FILTER | | 37 | | 32 |00:00:00.01 | 500 | | | |
| 45 | NESTED LOOPS | | 37 | 1 | 61 |00:00:00.01 | 500 | | | |
| 46 | NESTED LOOPS | | 37 | 1 | 169 |00:00:00.01 | 295 | | | |
| 47 | NESTED LOOPS | | 37 | 1 | 36 |00:00:00.01 | 182 | | | |
|* 48 | TABLE ACCESS BY INDEX ROWID| SYN$ | 37 | 1 | 36 |00:00:00.01 | 110 | | | |
|* 49 | INDEX UNIQUE SCAN | I_SYN1 | 37 | 1 | 36 |00:00:00.01 | 74 | | | |
| 50 | TABLE ACCESS BY INDEX ROWID| USER$ | 36 | 335 | 36 |00:00:00.01 | 72 | | | |
|* 51 | INDEX UNIQUE SCAN | I_USER1 | 36 | 1 | 36 |00:00:00.01 | 36 | | | |
| 52 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 36 | 1 | 169 |00:00:00.01 | 113 | | | |
|* 53 | INDEX RANGE SCAN | I_OBJ2 | 36 | 1 | 169 |00:00:00.01 | 73 | | | |
|* 54 | INDEX RANGE SCAN | I_OBJAUTH1 | 169 | 1 | 61 |00:00:00.01 | 205 | | | |
|* 55 | FIXED TABLE FULL | X$KZSRO | 24 | 1 | 1 |00:00:00.01 | 0 | | | |
|* 56 | HASH JOIN | | 1 | | 0 |00:00:14.97 | 2044 | 1035K| 872K| |
| 57 | CONNECT BY PUMP | | 1 | | 32 |00:00:00.01 | 0 | | | |
| 58 | COUNT | | 1 | | 38 |00:00:14.97 | 2044 | | | |
|* 59 | HASH JOIN | | 1 | 4098 | 38 |00:00:14.97 | 2044 | 1034K| 1034K| |
| 60 | TABLE ACCESS FULL | USER$ | 1 | 335 | 335 |00:00:00.01 | 14 | | | |
|* 61 | HASH JOIN | | 1 | 4103 | 38 |00:00:14.97 | 2030 | 1033K| 870K| |
|* 62 | HASH JOIN | | 1 | 4103 | 38 |00:00:14.41 | 1154 | 819K| 819K| |
| 63 | TABLE ACCESS FULL | USER$ | 1 | 335 | 335 |00:00:00.01 | 14 | | | |
|* 64 | HASH JOIN | | 1 | 1376K| 3378K|00:00:03.77 | 1140 | 6425K| 2078K| 9700K (0)|
| 65 | TABLE ACCESS FULL | SYN$ | 1 | 94997 | 95067 |00:00:00.10 | 264 | | | |
|* 66 | TABLE ACCESS FULL | OBJ$ | 1 | 95290 | 95069 |00:00:00.10 | 876 | | | |
| 67 | TABLE ACCESS FULL | OBJ$ | 1 | 143K| 143K|00:00:00.14 | 876 | | | |
| 68 | TABLE ACCESS BY INDEX ROWID | SYN$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 69 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Тяжелый случай, тем более, что выбор средств для настройки запросов к представлениям словаря данных весьма узок.
Решением, достаточно эффективным, стало следующее: поскольку процесс обновления эксклюзивно использовал базу данных, у меня была возможность совершенно безопасно выполнить следующие команды:
alter system set sort_area_size=10000000 deferred;
alter system set workarea_size_policy=manual;
Моей целью было воздействовать на выделение ресурсов для частей плана: строки 35-43 и строки 59-67. Указанные части плана являются примерами так называемых "right-deep tree" [1], особенностью которых является то, что в отличие от "left-deep tree", где используется не более двух active work areas, для "правых деревьев" на протяжении практически всего исполнения количество work areas, которые выделяются и участвуют в "probe", равно количеству соединений.
Таким образом, я достаточно сильно ускорил процесс обновления. Для будущих обновлений, включу в скрипты переключение в manual режим параметра workarea_size_policy в начале установки и переключения обратно в конце установки.
Тестирование на девелоперской базе аналогичного запроса показывает следующие результаты:
devdb> sho parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
devdb> variable a1 varchar2(30)
devdb> exec :a1:='...'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
devdb> variable a2 varchar2(30)
devdb> exec :a2:='...'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
devdb> SELECT TABLE_NAME, NVL(TABLE_OWNER, ''), NVL(DB_LINK, '') FROM SYS.ALL_SYNONYMS WHERE SYNONYM_NAME = :a1 AND OWNER = :a2;
...
1 row selected.
Elapsed: 00:00:26.37
devdb> alter session set workarea_size_policy=manual;
System altered.
Elapsed: 00:00:00.26
devdb> alter session set sort_area_size=1000000;
Session altered.
Elapsed: 00:00:00.00
devdb> SELECT TABLE_NAME, ... FROM SYS.ALL_SYNONYMS WHERE SYNONYM_NAME = :a1 AND OWNER = :a2;
...
1 row selected.
Elapsed: 00:00:27.17
devdb> alter session set sort_area_size=5000000;
Session altered.
Elapsed: 00:00:00.00devdb> SELECT TABLE_NAME, ... FROM SYS.ALL_SYNONYMS WHERE SYNONYM_NAME = :a1 AND OWNER = :a2;
...
1 row selected.
Elapsed: 00:00:03.54
И наверное, уже назрело время переходить на публичные синонимы.
Литература:
1. Christian Antognini "Troubleshooting Oracle Performance".
29 Май 2009 г.
Представления словаря данных
Автор:
YAP
на
10:45
0
коммент.
Ссылки на это сообщение
Ярлыки: HASH JOIN
1 Январь 2009 г.
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results
В описании sample_clause для select statement имеется такое предупреждение:
Caution:
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.
Чем это может "грозить"?
Всем известный dbms_stats широко использует sample_clause при вычислении статистических показателей. При использовании параметров сбора статистики по умолчанию возможны аномалии:
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT'));
3 end;
4 /
DBMS_STATS.AUTO_SAMPLE_SIZE
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.DELETE_TABLE_STATS('CS','WK_REQUEST_PARS')
PL/SQL procedure successfully completed.
Вот например, собранные в таких условиях некоторые показатели:
SQL> select COLUMN_NAME,NUM_DISTINCT,NUM_NULLS from ALL_TAB_COL_STATISTICS where
2 owner='CS' and table_name='WK_REQUEST_PARS' and COLUMN_NAME='WOP_NUMID';
COLUMN_NAME NUM_DISTINCT NUM_NULLS
------------------------------ ------------ ----------
WOP_NUMID 1633 0
Как выяснилось, такое значение для NUM_DISTINCT - крайне нереалистично. И соответственно, план одного из популярных запросов далек от совершенства:
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID | WK_REQUEST_PARS | 1 | 1 | 0 |00:00:55.75 | 99643 | 11607 |
| 2 | NESTED LOOPS | | 1 | 1 | 63144 |00:00:30.33 | 92490 | 6769 |
| 3 | NESTED LOOPS | | 1 | 1 | 31572 |00:00:08.30 | 29242 | 2455 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 1 | 6 |00:00:00.01 | 12 | 0 |
| 5 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID| OORG | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 7 | INDEX UNIQUE SCAN | XPK_ORG_ID | 1 | 1 | 1 |00:00:00.01 | 1 | 0 |
|* 8 | TABLE ACCESS BY INDEX ROWID| DOCF_DV | 1 | 1 | 1 |00:00:00.01 | 5 | 0 |
|* 9 | INDEX RANGE SCAN | XI_DIC_DIDI | 1 | 6 | 6 |00:00:00.01 | 3 | 0 |
| 10 | BUFFER SORT | | 1 | 1 | 6 |00:00:00.01 | 5 | 0 |
|* 11 | TABLE ACCESS BY INDEX ROWID| DOCF_DV | 1 | 1 | 6 |00:00:00.01 | 5 | 0 |
|* 12 | INDEX RANGE SCAN | XI_DIC_DIDI | 1 | 6 | 6 |00:00:00.01 | 2 | 0 |
|* 13 | TABLE ACCESS BY INDEX ROWID | WK_REQUESTS | 6 | 29 | 31572 |00:00:08.25 | 29230 | 2455 |
|* 14 | INDEX RANGE SCAN | XIF1122_WK_REQUESTS | 6 | 3428 | 191K|00:00:01.02 | 388 | 62 |
|* 15 | INDEX RANGE SCAN | IDX_WOP_WOR | 31572 | 8 | 31571 |00:00:21.83 | 63248 | 4314 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("WK_REQUEST_PARS"."WOP_NUMID"='6453768762' AND "WK_REQUEST_PARS"."WOP_ST"='R' AND
"WK_REQUEST_PARS"."WOP_IS_SPECIAL"='T'))
7 - access("ORG_ID"=768590)
8 - filter("DIC_VALUE"='R')
9 - access("DIC_DIDI"=12002)
11 - filter(("DIC_VALUE"='A' OR "DIC_VALUE"='C' OR "DIC_VALUE"='F' OR "DIC_VALUE"='H' OR "DIC_VALUE"='I' OR "DIC_VALUE"='K' OR
"DIC_VALUE"='P'))
12 - access("DIC_DIDI"=12001)
13 - filter("WK_REQUESTS"."WOR_REQ_TP"="DIC_VALUE")
14 - access("WK_REQUESTS"."WOR_ORG"=768590)
15 - access("WK_REQUEST_PARS"."WOP_WOR"="WK_REQUESTS"."WOR_ID")
Однако, если сделать так:
exec dbms_stats.gather_table_stats('CS','WK_REQUEST_PARS',cascade=>true,estimate_percent=>100)
то в результате:
SQL> select COLUMN_NAME,NUM_DISTINCT,NUM_NULLS from ALL_TAB_COL_STATISTICS where
2 owner='CS' and table_name='WK_REQUEST_PARS' and COLUMN_NAME='WOP_NUMID';
COLUMN_NAME NUM_DISTINCT NUM_NULLS
------------------------------ ------------ ----------
WOP_NUMID 628654 48
и план того же запроса:
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 |
| 2 | MERGE JOIN CARTESIAN | | 1 | 1 | 0 |00:00:00.01 | 14 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 6 |00:00:00.01 | 10 |
| 4 | NESTED LOOPS | | 1 | 1 | 6 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS BY INDEX ROWID| OORG | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 6 | INDEX UNIQUE SCAN | XPK_ORG_ID | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 7 | TABLE ACCESS BY INDEX ROWID| DOCF_DV | 1 | 1 | 6 |00:00:00.01 | 5 |
|* 8 | INDEX RANGE SCAN | XI_DIC_DIDI | 1 | 6 | 6 |00:00:00.01 | 2 |
| 9 | BUFFER SORT | | 6 | 1 | 6 |00:00:00.01 | 3 |
|* 10 | TABLE ACCESS BY INDEX ROWID| DOCF_DV | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 11 | INDEX RANGE SCAN | XI_DIC_DIDI | 1 | 6 | 6 |00:00:00.01 | 2 |
| 12 | BUFFER SORT | | 6 | 1 | 0 |00:00:00.01 | 4 |
|* 13 | TABLE ACCESS BY INDEX ROWID | WK_REQUEST_PARS | 1 | 1 | 0 |00:00:00.01 | 4 |
|* 14 | INDEX RANGE SCAN | IDX_WRP_NUMID | 1 | 22 | 1 |00:00:00.01 | 3 |
|* 15 | TABLE ACCESS BY INDEX ROWID | WK_REQUESTS | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 16 | INDEX UNIQUE SCAN | PK_WK_REQUESTS | 0 | 1 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("ORG_ID"=768590)
7 - filter(("DIC_VALUE"='A' OR "DIC_VALUE"='C' OR "DIC_VALUE"='F' OR "DIC_VALUE"='H' OR "DIC_VALUE"='I' OR "DIC_VALUE"='K' OR
"DIC_VALUE"='P'))
8 - access("DIC_DIDI"=12001)
10 - filter("DIC_VALUE"='R')
11 - access("DIC_DIDI"=12002)
13 - filter(("WK_REQUEST_PARS"."WOP_ST"='R' AND "WK_REQUEST_PARS"."WOP_IS_SPECIAL"='T'))
14 - access("WK_REQUEST_PARS"."WOP_NUMID"='6453768762')
15 - filter(("WK_REQUESTS"."WOR_ORG"=768590 AND "WK_REQUESTS"."WOR_REQ_TP"="DIC_VALUE"))
16 - access("WK_REQUEST_PARS"."WOP_WOR"="WK_REQUESTS"."WOR_ID")
Разница разительна!
Как сообщил мне один из разработчиков, на начальных стадиях заполнения таблицы WK_REQUEST_PARS существовала ошибка в прикладном коде, в связи с которой возникло множество дубликатов для значения поля WOP_NUMID. Видимо это оказало фатальное влияние на работу sample_clause.
Автор:
YAP
на
20:47
0
коммент.
Ссылки на это сообщение
Ярлыки: Sampling
28 Декабрь 2008 г.
Древовидные справочники в качестве таблиц-размерностей или анализ “что-если” применительно к “TUNING BY CARDINALITY FEEDBACK METHOD”
Недавно консультировал коллегу по настройке запроса. Суть решаемой запросом задачи состояла в следующем: таблица фактов (далее facts) связана с таблицей размерности (далее dic$territory), а та в свою очередь, являлась древовидным справочником административно-территориального устройства. Интересующий моего коллегу запрос должен был выдавать некоторые суммарные значения по таблице facts, отфильтрованной с помощью таблицы dic$territory. Из таблицы facts отбирались строки, ссылающиеся на некоторую часть дерева справочника dic$territory, корень необходимой для фильтрации части дерева указывался параметром :code.
Запрос имеел вид аналогичный приведенному ниже:
select sum(human_cnt) from facts
where territory_id in (select territory_id
from dic$territory
start with territory_code = :code
connect by prior territory_id = territory_parent);
Первый подход к анализу запроса (explain plan) показал - прогнозируемая кардинальность фильтрующего подзапроса (шаг 4) определяется крайне неточно. Следует отметить, что реальная кардинальность подзапроса зависит от значения параметра, указывающего на корневой узел начала перебора дерева, и может изменяться начиная от нескольких единиц для узлов дерева близких к листовым, до нескольких тысяч для узлов дерева близких к корню.
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 39 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | FACTS | 9 | 81 | 12 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 27 | 594 | 39 (3)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 3 | 39 | 2 (0)| 00:00:01 |
| 5 | HASH UNIQUE | | 3 | 30 | | |
|* 6 | CONNECT BY WITH FILTERING | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY | 1 | 26 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_TERR_CODE | 1 | | 1 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | | | | |
| 10 | CONNECT BY PUMP | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY | 3 | 30 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_TERR_PARENT | 3 | | 1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_FACTS_TERR | 9 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("TERRITORY_PARENT"=PRIOR "TERRITORY_ID")
8 - access("TERRITORY_CODE"=:CODE)
12 - access("TERRITORY_PARENT"=PRIOR "TERRITORY_ID")
13 - access("TERRITORY_ID"="$nso_col_1")
Интересно было бы понять, каким образом вычисляется кардинальность фильтрующего подзапроса. В файле трассировки 10053 имеется следующий фрагмент:
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#2): TERRITORY_PARENT(NUMBER)
AvgLen: 5.00 NDV: 12110 Nulls: 23 Density: 8.2576e-005 Min: 0 Max: 42164
Table: DIC$TERRITORY Alias: DIC$TERRITORY
Card: Original: 42188 Rounded: 3 Computed: 3.48 Non Adjusted: 3.48
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
на основе которого можно сделать вывод, что кардинальность подзапроса есть не что иное как кардинальность выборки из таблицы dic$territory, отфильтрованой предикатом territory_parent=:id.
Статистика времени выполнения этого запроса при значении параметра :code, указывающим на «близкий» к корню дерева узел, подтверждает, некорректность прогноза кардинальности подзапроса (шаг 4):
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.55 | 28858 |
| 2 | TABLE ACCESS BY INDEX ROWID | FACTS | 1 | 9 | 21051 |00:00:00.43 | 28858 |
| 3 | NESTED LOOPS | | 1 | 27 | 21051 |00:00:00.32 | 7807 |
| 4 | VIEW | VW_NSO_1 | 1 | 3 | 2339 |00:00:00.15 | 3081 |
| 5 | HASH UNIQUE | | 1 | 3 | 2339 |00:00:00.14 | 3081 |
|* 6 | CONNECT BY WITH FILTERING | | 1 | | 2339 |00:00:00.13 | 3081 |
| 7 | TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 8 | INDEX RANGE SCAN | IDX_TERR_CODE | 1 | 1 | 1 |00:00:00.01 | 2 |
| 9 | NESTED LOOPS | | 4 | | 2338 |00:00:00.11 | 3078 |
| 10 | CONNECT BY PUMP | | 4 | | 2339 |00:00:00.01 | 0 |
| 11 | TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY | 2339 | 3 | 2338 |00:00:00.07 | 3078 |
|* 12 | INDEX RANGE SCAN | IDX_TERR_PARENT | 2339 | 3 | 2338 |00:00:00.03 | 2353 |
|* 13 | INDEX RANGE SCAN | IDX_FACTS_TERR | 2339 | 9 | 21051 |00:00:00.06 | 4726 |
----------------------------------------------------------------------------------------------------------------
Оценив на глазок, можно сказать, что способ соединения (шаг 3) не очень подходит для такой кардинальности, так же как и способ доступа к таблице facts (шаги 13, 2).
Учитывая тот факт, что кардинальность подзапроса оценивается оптимизатором настолько грубо, не остается ничего другого, как воспользоваться подсказкой cardinality со значением параметра равным значению колонки A-Rows для шага 4.
select /*+gather_plan_statistics cardinality(@qb1 2339)*/
sum(human_cnt) from facts
where territory_id in (select /*+QB_NAME(qb1)*/ territory_id
from dic$territory
start with territory_code = :code
connect by prior territory_id = territory_parent);
Статистика времени выполнения с уточненной кардинальностью (для того же параметра :code):
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:03.42 | 4064 |
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 21212 | 21051 |00:00:03.38 | 4064 |
| 3 | VIEW | VW_NSO_1 | 1 | 2339 | 2339 |00:00:00.14 | 3081 |
|* 4 | CONNECT BY WITH FILTERING | | 1 | | 2339 |00:00:00.13 | 3081 |
| 5 | TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | IDX_TERR_CODE | 1 | 1 | 1 |00:00:00.01 | 2 |
| 7 | NESTED LOOPS | | 4 | | 2338 |00:00:00.11 | 3078 |
| 8 | CONNECT BY PUMP | | 4 | | 2339 |00:00:00.01 | 0 |
| 9 | TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY | 2339 | 3 | 2338 |00:00:00.07 | 3078 |
|* 10 | INDEX RANGE SCAN | IDX_TERR_PARENT | 2339 | 3 | 2338 |00:00:00.03 | 2353 |
| 11 | TABLE ACCESS FULL | FACTS | 1 | 381K| 379K|00:00:00.76 | 983 |
--------------------------------------------------------------------------------------------------------------
В результате LIO уменьшился в 7 раз. Для настройки реального запроса такой метод корректирования кардинальности не претендует на универсальность, однако позволяет легко воздействовать на форму плана и оценивать его эффективность.
В моем случае знание формы более эффективного плана позволило найти решение реальной проблемы, а именно, была создана дополнительная таблица, в которой хранилась «развертка» дерева, эта новая таблица была использована в фильтрующем подзапросе вместо первоначальной древовидной. Её использование позволило оптимизатору получать корректные значения прогнозов кардинальности для большинства используемых значений параметра :code.
Кроме того, на основе проведенной работы выкристализовалась идея использовать подсказку cardinality для осуществления «что-если» анализа в рамках метода «...by cardinality feedback...». Алгоритм может быть примерно следующим:
1) выявить шаг плана, с ошибочным прогнозом кардинальности, используя для этого статистику времени исполнения;
2) подсказкой cardinality скорректировать кардинальность;
3) перезапустить запрос, если еще остаются шаги плана с некорректным прогнозом кардинальности, перейти к шагу 1), иначе перейти к следующему шагу;
4) оценить эффективность плана, при положительном ответе — найти способо помочь оптимизатору находить такой же план, но без использования подсказки(ок) cardinality.
Фактически, такая последовательность действий дает возможность понять — какой план можно получить в ситуации, когда оптимизатор имеет точную информацию о кардинальности, станет ли такой план достаточно эффективным. Ведь в результате анализа может также выяснится, что сама задача, которую решает настраиваемый запрос, требует переформулирования. Кстати, окончательным решением моего коллеги по настройке запроса стало именно переформулирование задачи.
Автор:
YAP
на
20:46
0
коммент.
Ссылки на это сообщение
Ярлыки: cardinaliry feedback
16 Ноябрь 2008 г.
Работаем с BLOB
Недавно принимал участие в решении проблемы низкой производительности PL\SQL приложения.
Анализ файла трассировки 10046 навел на мысль о том, что в основном код работает с BLOB-данными. Разработчики подтвердили это предположение. Соответствующий фрагмент отчета tkprof приведен ниже:
declare
a blob;
begin
a:=PrintActiveContragent(7720);
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.03 10.50 1521 4766 2600 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 10.50 1521 4766 2600 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 1521 0.05 10.38
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 5.86 5.86
********************************************************************************
Остальные рекурсивные вызовы не приводятся ввиду исчезающе малой ресурсоемкости.
Из приведенного видно, что все время потрачено на direct path read, изучение исходного файла трасировки показало, что это были чтения BLOB-поля, причем со слов разработчиков - читалось значение BLOB только из одной строки таблицы. Кроме того, разработчики сообщили, что размер BLOB в той строке примерно 200К.
Очевидно, что происходят множественные чтения BLOB, которые фактически, осущесвтляются непосредственно с диска. Проверив некоторые свойства BLOB этой таблицы, выяснилось среди прочего, что CACHE=NO.
Что ж, напрашивается рекомендация - воспользоваться возможностью кеширования BLOB, используя стейтмент подобный этому: alter table sometable modify lob (blob_field) (cache reads). После такого изменения скорость работы проблемного приложения улучшилась на 3 порядка, в общем можно было бы останоиться, но у некоторых наверняка возникнут сомнения относительно такого включения кеширования, ведь оно фактически может повлиять и на другие приложения, вытесняя из буферного кеша их данные.
Поскольку я достаточно редко имею дело с BLOB-ами, решил их немного поизучать, заодно попытаться сформулировать возможные альтернативные решения вышеприведенной проблемы.
Тестовые данные. Работаю с Oracle 10.2.0.1 (XE), размер блока 8192 байта.
Таблица: create table t_blob (id number,blob_data blob);
Проверю некоторые ее свойства
SQL> select table_name,column_name,chunk,cache from user_lobs;
TABLE_NAME COLUMN_NAME CHUNK CACHE
------------------------------ -------------------- ---------- ----------
T_BLOB BLOB_DATA 8192 NO
Создаю одну строку со значением в поле BLOB длиной 10000 байт.
SQL> declare
2 l_data blob;
3 l_str raw(1000);
4 begin
5 insert into t_blob values (1,empty_blob());
6 select blob_data into l_data from t_blob where id=1;
7 dbms_lob.open(l_data,dbms_lob.lob_readwrite);
8 for i in 1..10 loop
9 l_str:=utl_raw.CAST_TO_RAW(rpad('z',1000-i,'z')||rpad('x',i,'x'));
10 dbms_lob.writeappend(l_data,1000,l_str);
11 end loop;
12 dbms_lob.close(l_data);
13 commit;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> select id,dbms_lob.getlength(blob_data) from t_blob;
ID DBMS_LOB.GETLENGTH(BLOB_DATA)
---------- -----------------------------
1 10000
Создаю процедуру, которая читает BLOB поле фрагментами по 1000 байт, в некотором смысле, эмуляция работы кода, который изучался в начале статьи. После каждого чтения проверяется значение статистики с номером 120 для текущей сессии
SQL> select name from v$statname where statistic#=120;
NAME
------------------------------------------------------
physical reads direct (lob)
Процедура:
SQL> create or replace procedure proc2
2 is
3 function GetStat(st# number) return number
4 is
5 l_res number;
6 begin
7 select value into l_res from v$mystat x where x.statistic#=st#;
8 return l_res;
9 end;
10 procedure a1 (p1 in out blob)
11 as
12 l_cnt1 number;
13 l_cnt2 number;
14 ll raw(1000);
15 am binary_integer := 1000;
16 begin
17 dbms_output.put_line('Size: '||dbms_lob.getlength(p1));
18 for i in 1..trunc(dbms_lob.getlength(p1)/1000) loop
19 l_cnt1:=getstat(120);
20 dbms_lob.read(p1,am,1+(i-1)*am,ll);
21 l_cnt2:=getstat(120);
22 dbms_output.put_line('I/O#'||i||' reads: '||(l_cnt2-l_cnt1));
23 end loop;
24 end;
25 begin
26 for i in (select blob_data from t_blob where id=1 order by id) loop
27 a1(i.blob_data);
28 end loop;
29 end;
30 /
Procedure created.
Сравненние работы с полем BLOB, не кешированными (изначально) и кешированным:
SQL> alter session set events '10046 trace name context forever, level 8';
Session altered.
SQL> begin proc2; end;
2 /
Size: 10000
I/O#1 reads: 1
I/O#2 reads: 1
I/O#3 reads: 1
I/O#4 reads: 1
I/O#5 reads: 1
I/O#6 reads: 1
I/O#7 reads: 1
I/O#8 reads: 1
I/O#9 reads: 2
I/O#10 reads: 1
PL/SQL procedure successfully completed.
SQL> alter table t_blob modify lob (blob_data) (cache reads);
Table altered.
SQL> begin PROC2; end;
2 /
Size: 10000
I/O#1 reads: 0
I/O#2 reads: 0
I/O#3 reads: 0
I/O#4 reads: 0
I/O#5 reads: 0
I/O#6 reads: 0
I/O#7 reads: 0
I/O#8 reads: 0
I/O#9 reads: 0
I/O#10 reads: 0
PL/SQL procedure successfully completed.
Исходя из статистик сессии, следуюет, что каждое обращение к некешированному BLOB приводит к запросу на дисковый ввод-вывод. Это же подтверждают данные трассировки сессии. Видно, что значение BLOB хранится в двух блоках, чтение 9-го фрагмента (длиной 1000 байт) фактически затрагивает оба блока.
WAIT #3: nam='direct path read' ela= 23758 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520891323
WAIT #3: nam='direct path read' ela= 271 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520895284
WAIT #3: nam='direct path read' ela= 249 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520897971
WAIT #3: nam='direct path read' ela= 264 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520900695
WAIT #3: nam='direct path read' ela= 262 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520903592
WAIT #3: nam='direct path read' ela= 235 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520906394
WAIT #3: nam='direct path read' ela= 222 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520909032
WAIT #3: nam='direct path read' ela= 235 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520911710
WAIT #3: nam='direct path read' ela= 205 file number=1 first dba=55426 block cnt=1 obj#=16099 tim=107520914372
WAIT #3: nam='direct path read' ela= 14 file number=1 first dba=55428 block cnt=1 obj#=16099 tim=107520914681
WAIT #3: nam='direct path read' ela= 239 file number=1 first dba=55428 block cnt=1 obj#=16099 tim=107520917641
Некешированный читаестя через "direct path read", кешированный соответственно через "db file sequential read".
Профиль для некешированного BLOB:
begin proc2; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.04 11 11 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.04 11 11 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 11 0.02 0.02
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
Профиль для кешированного BLOB
begin PROC2; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.06 2 11 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.06 2 11 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.03 0.05
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
Первый итог: любое чтение поля BLOB - приводит к LIO, если поле BLOB не кешируется, то еще и к физическому вводу-выводу. Следовательно, при кодировании алгоритмов, читающих BLOB - поля, более выиграшным будет подход, минимизирующий количество чтений, что есть закономерным. Фактически необходимо преследовать те же цели, что и при оптимизации SQL.
Что же делать если по каким-либо причинам изменение alter table ... modify lob (...) (cache reads) является неприемлемым?
Можно попробовать использовать временный BLOB, причем, кешированный. Сравнивать его, естественно, есть смысл с кешированным BLOB-полем таблицы. В качестве измерительного инструмента - runstats.
SQL> declare
2 l_blob blob;
3 l_blob1 blob;
4 procedure a1 (p1 in out blob)
5 as
6 ll raw(1000);
7 am binary_integer := 1000;
8 begin
9 for i in 1..trunc(dbms_lob.getlength(p1)/1000) loop
10 dbms_lob.read(p1,am,1+(i-1)*am,ll);
11 end loop;
12 end;
13 begin
14 dbms_lob.createtemporary(l_blob1,true);
15 select blob_data into l_blob from t_blob where id=1;
16 DBMS_LOB.COPY (l_blob1,l_blob,dbms_lob.getlength(l_blob));
17 runStats_pkg.rs_start;
18 for j in 1..1000 loop a1(l_blob); end loop;
19 runStats_pkg.rs_middle;
20 for j in 1..1000 loop a1(l_blob1); end loop; --Temporary BLOB
21 runStats_pkg.rs_stop;
22 dbms_lob.freetemporary(l_blob1);
23 end;
24 /
Run1 ran in 31 hsecs
Run2 ran in 10 hsecs
run 1 ran in 310% of the time
Name Run1 Run2 Diff
STAT...active txn count during 1 0 -1
STAT...consistent gets - exami 1 0 -1
LATCH.library cache lock alloc 0 1 1
STAT...redo entries 8 9 1
STAT...cleanout - number of kt 1 0 -1
LATCH.redo allocation 10 9 -1
STAT...calls to kcmgcs 4 5 1
LATCH.library cache lock 6 8 2
LATCH.library cache pin 6 8 2
LATCH.resmgr:resource group CP 4 2 -2
LATCH.redo writing 2 0 -2
LATCH.cache buffers lru chain 8 5 -3
LATCH.object queue header oper 8 5 -3
STAT...recursive cpu usage 5 2 -3
LATCH.messages 4 0 -4
STAT...enqueue releases 5 1 -4
STAT...db block changes 45 40 -5
STAT...consistent changes 36 31 -5
STAT...enqueue requests 6 1 -5
STAT...parse time elapsed 8 1 -7
LATCH.channel operations paren 8 0 -8
LATCH.enqueue hash chains 10 2 -8
LATCH.enqueues 10 2 -8
STAT...db block gets from cach 38 27 -11
STAT...db block gets 38 27 -11
STAT...session logical reads 11,044 11,033 -11
STAT...undo change vector size 2,008 2,024 16
LATCH.checkpoint queue latch 16 0 -16
STAT...redo size 2,688 2,672 -16
STAT...CPU used by this sessio 33 11 -22
STAT...workarea memory allocat 33 11 -22
STAT...Elapsed Time 50 21 -29
LATCH.cache buffers chains 22,186 22,136 -50
LATCH.SQL memory manager worka 73 6 -67
LATCH.simulator hash latch 4 9,002 8,998
LATCH.simulator lru latch 1 9,002 9,001
STAT...calls to get snapshot s 33,002 10,001 -23,001
STAT...session uga memory 65,560 15,072 -50,488
STAT...session uga memory max 261,964 65,560 -196,404
STAT...session pga memory max 262,144 65,536 -196,608
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
22,380 40,212 17,832 55.66%
Как говорится, 3:1 в пользу временного! Хотя есть странности: например некоторые типы защелок "simulator hash latch" и "simulator lru latch" достаточно активно себя проявили у "победителя". Кроме того, использование памяти сильно отличается. Можно предположить, что для доступа к данным временного BLOB используется другой "code path", признаками которого, возможно, являются защелки типа "simulator...".
Подытоживая, можно сказать следующее: использование временного кешированного BLOB решает с одной стороны проблему вымывания буферного кеша, а с другой стороны является еще и менее ресурсоемким способом обработки.
Автор:
YAP
на
20:12
0
коммент.
Ссылки на это сообщение
Ярлыки: LOB
28 Октябрь 2008 г.
Неудачные значения при bind peeking.
Проводя ежедневный осмотр моей боевой БД, обнаружил долгоиграющий запрос. На 3 запуска более 80000 секунд времени исполняния (по данным v$sql). Естественно, не дожидаясь отклика "благодарных" пользователей начал исследовать это явление.
Промышленная база версии 10.2.0.4.
Для иллюстрации буду использовать вот такие тестовые данные:
create table t1 as select * from all_objects;
create table t2 as select * from all_objects;
create index t1_i1 on t1(owner,object_type);
create index t2_i1 on t2(owner,object_type);
exec dbms_stats.gather_table_stats(null,'t1',cascade=>true,method_opt=>'for all indexed columns size 254')
exec dbms_stats.gather_table_stats(null,'t2',cascade=>true,method_opt=>'for all indexed columns size 254')
переменные:
variable p_owner varchar2(30)
variable p_type varchar2(20)
запрос:
select /*+gather_plan_statistics*/ count(1)
from t1,t2 where t1.owner=t2.owner and t1.object_type=t2.object_type
and t1.owner=:p_owner and t1.object_type=:p_type;
А теперь, собственно, ход исследования. Предполагается, что запрос уже кем-то где-то в приложении запущен, и моя задача найти и проанализировать информацию о нём.
Сначала получаю план из разделяемого пула:
SQL> select * from table(dbms_xplan.display_cursor('gmf7sa93m8tk4',null,'LAST ALLSTATS'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID gmf7sa93m8tk4, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ count(1) from t1,t2 where t1.owner=t2.owner and
t1.object_type=t2.object_type and t1.owner=:p_owner and t1.object_type=:p_type
Plan hash value: 1472244417
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:04.28 | 8 | | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 1 | 516K|00:00:03.10 | 8 | | | |
|* 3 | INDEX RANGE SCAN | T2_I1 | 1 | 1 | 719 |00:00:00.01 | 4 | | | |
| 4 | BUFFER SORT | | 719 | 1 | 516K|00:00:01.04 | 4 | 73728 | 73728 | |
|* 5 | INDEX RANGE SCAN | T1_I1 | 1 | 1 | 719 |00:00:00.01 | 4 | | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."OWNER"=:P_OWNER AND "T2"."OBJECT_TYPE"=:P_TYPE)
5 - access("T1"."OWNER"=:P_OWNER AND "T1"."OBJECT_TYPE"=:P_TYPE)
По статистике выполнения видно, что как минимум, тип соединения не подходит для данных, которые обрабатываются (результирующая кардинальность на шаге 2 плана крайне отличается от прогнозируемой).
Первая гипотеза - неужели статистика устарела? Хотя автоматический сбор статистики не отключал...
Пробую получить план "непосредтсвенно от CBO":
SQL> explain plan for
2 select /*+gather_plan_statistics*/ count(1)
3 from t1,t2 where t1.owner=t2.owner and t1.object_type=t2.object_type
4 and t1.owner=:p_owner and t1.object_type=:p_type;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1849716936
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (34)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | HASH JOIN | | 11125 | 325K| 3 (34)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| T2_I1 | 103 | 1545 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| T1_I1 | 108 | 1620 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OWNER"="T2"."OWNER" AND
"T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE")
3 - access("T2"."OWNER"=:P_OWNER AND "T2"."OBJECT_TYPE"=:P_TYPE)
4 - access("T1"."OWNER"=:P_OWNER AND "T1"."OBJECT_TYPE"=:P_TYPE)
Результат несколько озадачивает - совершенно нормальный план (способ доступа и тип соединения). Почему же в разделяемом пуле нечто совершенно другое? В общем, отбрасываю версию об устаревшей статистике.
После недолгих раздумий начинаю вспоминать недавно прочитанное у Джонатана Льюса (см. ссылку внизу). Возникает вторая гипотеза - влияние bind peeking.
Проверяю текущие значения переменных привязки:
SQL> select last_captured,name,datatype_string,value_string from v$sql_bind_capture
2 where sql_id='gmf7sa93m8tk4';
LAST_CAPTURED NAME DATATYPE_STRING
------------------- ------------------------------ ---------------
VALUE_STRING
---------------------------------------------------------------------
28/10/2008 21:27:37 :P_OWNER VARCHAR2(32)
SYS
28/10/2008 21:27:37 :P_TYPE VARCHAR2(32)
TABLE
Строю план, заменяя переменные литералами с соответствующими текущим значениями:
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1962103783
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 11 (46)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | HASH JOIN | | 678K| 19M| 11 (46)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| T1_I1 | 812 | 12180 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| T2_I1 | 835 | 12525 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OWNER"="T2"."OWNER" AND
"T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE")
3 - access("T1"."OWNER"='SYS' AND "T1"."OBJECT_TYPE"='TABLE')
4 - access("T2"."OWNER"='SYS' AND "T2"."OBJECT_TYPE"='TABLE')
Очевидно, что запрос из разделяемого пула разбирался при других значениях переменных. Проверяю что было при разборе (в следующем отчете среди прочего выводятся значения переменных привязки, которые использовались при разборе запроса):
SQL> select * from table(dbms_xplan.display_cursor('gmf7sa93m8tk4',null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID gmf7sa93m8tk4, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ count(1) from t1,t2 where
t1.owner=t2.owner and t1.object_type=t2.object_type and
t1.owner=:p_owner and t1.object_type=:p_type
Plan hash value: 1472244417
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T2_I1 | 1 | 15 | 1 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 15 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 1 | 15 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :P_OWNER (VARCHAR2(30), CSID=171): 'SCOTT'
2 - :P_TYPE (VARCHAR2(30), CSID=171): 'TABLE'
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."OWNER"=:P_OWNER AND "T2"."OBJECT_TYPE"=:P_TYPE)
5 - access("T1"."OWNER"=:P_OWNER AND "T1"."OBJECT_TYPE"=:P_TYPE)
Наконец, перепроверяю вторую гипотезу:
SQL> explain plan for
2 select /*+gather_plan_statistics*/ count(1)
3 from t1,t2 where t1.owner=t2.owner and t1.object_type=t2.object_type
4 and t1.owner='SCOTT' and t1.object_type='TABLE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1472244417
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T2_I1 | 1 | 15 | 1 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 15 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 1 | 15 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."OWNER"='SCOTT' AND "T2"."OBJECT_TYPE"='TABLE')
5 - access("T1"."OWNER"='SCOTT' AND "T1"."OBJECT_TYPE"='TABLE')
Так и есть, разбор происходил при не очень удачных значениях переменных привязки.
Возникает законный вопрос: как бороться с этим? Изящное решение проблемы находися в следующей версии - Oracle 11g. Я имею ввиду новшество "Intelligent Cursor Sharing".
Там аналогичная ситуация выглядела бы вот так:
SQL> begin
2 :p_owner:='SCOTT'; :p_type:='SEQUENCE';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select /*+gather_plan_statistics*/ count(1)
2 from t1,t2 where t1.owner=t2.owner and t1.object_type=t2.object_type
3 and t1.owner=:p_owner and t1.object_type=:p_type;
COUNT(1)
----------
0
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gmf7sa93m8tk4, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ count(1) from t1,t2 where
t1.owner=t2.owner and t1.object_type=t2.object_type and
t1.owner=:p_owner and t1.object_type=:p_type
Plan hash value: 2988039147
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 1 | 0 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | T1_I1 | 1 | 1 | 0 |00:00:00.01 | 2 | | | |
| 4 | BUFFER SORT | | 0 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 5 | INDEX RANGE SCAN | T2_I1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OWNER"=:P_OWNER AND "T1"."OBJECT_TYPE"=:P_TYPE)
5 - access("T2"."OWNER"=:P_OWNER AND "T2"."OBJECT_TYPE"=:P_TYPE)
...
SQL> begin
2 :p_owner:='SYS'; :p_type:='TABLE';
3 end;
4 /
SQL> select /*+gather_plan_statistics*/ count(1)
2 from t1,t2 where t1.owner=t2.owner and t1.object_type=t2.object_type
3 and t1.owner=:p_owner and t1.object_type=:p_type;
COUNT(1)
----------
872356
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last +peeked_binds'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID gmf7sa93m8tk4, child number 1
-------------------------------------
select /*+gather_plan_statistics*/ count(1) from t1,t2 where
t1.owner=t2.owner and t1.object_type=t2.object_type and
t1.owner=:p_owner and t1.object_type=:p_type
Plan hash value: 1849716936
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.43 | 10 | | | |
|* 2 | HASH JOIN | | 1 | 1824K| 872K|00:00:00.88 | 10 | 909K| 909K| 361K (0)|
|* 3 | INDEX RANGE SCAN| T2_I1 | 1 | 1321 | 934 |00:00:00.01 | 5 | | | |
|* 4 | INDEX RANGE SCAN| T1_I1 | 1 | 1381 | 934 |00:00:00.01 | 5 | | | |
-----------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=171): 'SYS'
2 - (VARCHAR2(30), CSID=171): 'TABLE'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OWNER"="T2"."OWNER" AND "T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE")
3 - access("T2"."OWNER"=:P_OWNER AND "T2"."OBJECT_TYPE"=:P_TYPE)
4 - access("T1"."OWNER"=:P_OWNER AND "T1"."OBJECT_TYPE"=:P_TYPE)
После изменения значений переменных произошел повторный разбор, в итоге новый child cursor и новый, а главное - подходящий план.
А для используемой мною версии Oracle Database 10.2.0.4, простых решений вероятно не существует. Можно например вытеснять из разделяемого пула выбранный разобранный запрос (dbms_shared_pool.purge). Возможно для некоторых случаев подойдет принудительный (не автоматический) периодичесикий сбор статистики без гистограмм, с последующей фиксацией (dbms_stats.lock_table_stats).
Ссылки:
Jonathan Lewis "Bind Capture"
Troubleshooting Oracle Performance by Christian Antognini
Автор:
YAP
на
22:06
0
коммент.
Ссылки на это сообщение
Ярлыки: bind peeking
25 Июнь 2008 г.
Предикат из чек-констрейнта
Еще одна, очевидно, ошибка, связанная со сгенеренными предикатами.
drop table ch1;
create table ch1 (f1 number(1) constraint ch1_ck_f1 check (f1 in ('1','2','3')));
insert into ch1 values (1);
insert into ch1 values (2);
insert into ch1 values (3);
insert into ch1 values (4);
commit;
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'ch1')
select * from ch1 where f1=1;
explain plan for
select * from ch1 where f1=1;
select * from table(dbms_xplan.display);
Как то так вышло у моего коллеги, что для поля с типом NUMBER был создан чек-констрейнт ...in ('1','2','3').
Оптимизатор сгенерил фактически лишний и не корректный предикат из чек-констрейнта.
В итоге результат таков:
SQL> select * from ch1 where f1=1;
no rows selected
SQL> explain plan for
2 select * from ch1 where f1=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3341507894
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| CH1 | 1 | 3 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("F1"=1)
а ежели еще вот так
alter table ch1 disable constraint ch1_ck_f1;
select * from ch1 where f1=1;
explain plan for
select * from ch1 where f1=1;
select * from table(dbms_xplan.display);
то
SQL> alter table ch1 disable constraint ch1_ck_f1;
Table altered.
SQL> select * from ch1 where f1=1;
F1
----------
1
SQL> delete from plan_table;
3 rows deleted.
SQL> explain plan for
2 select * from ch1 where f1=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2138608289
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CH1 | 1 | 3 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F1"=1)
Проверял на следующих версиях: 9.2.0.4, 10.2.0.1, 10.2.0.4, 11.1.0.6 - проявлялось только на 10.2.0.1.
Автор:
YAP
на
14:42
1 коммент.
Ссылки на это сообщение
Ярлыки: check constraint predicate
Tuning by Cardinality Feedback в действии
В свое время, статья Tuning by Cardinality Feedback произвеля на меня неизгладимое впечатление.
И вот теперь случилось применить ее в одном интересном случае. Вот такой план имел место быть на промышленной площадке.
Сразу же бросается в глаза серъезное расхождение между прогнозом оптимизатора и статистикой времени исполнения. Первая стадия анализа привела к фрагменту плана, в котором, как мне кажется, находится ключевая ошибка оптимизатора в оценке кардинальности:
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows |
-------------------------------------------------------------------------------------------------------------------------------------------------------
...
|* 17 | HASH JOIN ANTI | | 1 | 1 | 54 | | 7 (15)| 00:00:01 | 18 |
| 18 | NESTED LOOPS | | 1 | 1 | 42 | | 2 (0)| 00:00:01 | 21 |
|* 19 | TABLE ACCESS BY USER ROWID | IKIS_USERS_ATTR | 1 | 1 | 25 | | 1 (0)| 00:00:01 | 1 |
|* 20 | INDEX FULL SCAN | PK_IKIS_ROLE | 1 | 1 | 17 | | 1 (0)| 00:00:01 | 21 |
| 21 | VIEW | VW_NSO_3 | 1 | 12 | 144 | | 4 (0)| 00:00:01 | 3 |
| 22 | NESTED LOOPS | | 1 | 12 | 600 | | 4 (0)| 00:00:01 | 3 |
|* 23 | TABLE ACCESS FULL | IKIS_RESOURCE | 1 | 9 | 162 | | 3 (0)| 00:00:01 | 4 |
|* 24 | INDEX RANGE SCAN | PK_RSRC2ROLE | 4 | 1 | 32 | | 1 (0)| 00:00:01 | 3 |
...
все остальное лавинообразно преумножало ошибку, сделанную на 20 шаге плана.
Интересный предикат был сгенерен для этого 20 шага:
20 - filter(("IKIS_SUBSYS_UTIL"."GETINSTANCEPREF"()||"RS"."IRL_NAME"<>'PUBLIC' AND "IKIS_SUBSYS_UTIL"."GETINSTANCEPREF"()||"RS"."IRL_NAME"<>'_NEXT_USER'))
Собственно, данный предикат сгенерен на основе where clause представления dba_roles, которое участвует в данном запросе.
Далее простой test case, имитирующий данную ситуацию:
drop table r1;
drop table r2;
create table r1 as select rownum id,x.* from all_objects x where rownum<=1000;
update r1 set object_name='SSDR' where mod(id,276)=0;
update r1 set object_name='GSDR' where mod(id,876)=0;
create table r2 as select * from r1 where mod(object_id,2)=0;
update r1 set object_name='XXX'||substr(object_name,1,25);
update r2 set object_name=substr(object_name,1,25);
commit;
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'r1')
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'r2')
create or replace view v1
as
select * from r1
where object_name<>'XXXSSDR' and object_name<>'XXXGSDR' and object_type='INDEX';
explain plan for
select count(1)
from v1,r2
where v1.object_name='XXX'||r2.object_name;
и ожидаемый, аналогичный полученному на пром-системе, результат с ошибкой оценки кардинальности для таблицы R2:
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
|* 2 | HASH JOIN | | 1 | 40 | 9 (12)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| R2 | 1 | 15 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| R1 | 111 | 2775 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='XXX'||"R2"."OBJECT_NAME")
3 - filter('XXX'||"R2"."OBJECT_NAME"<>'XXXSSDR' AND
'XXX'||"R2"."OBJECT_NAME"<>'XXXGSDR')
4 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_NAME"<>'XXXSSDR' AND
"OBJECT_NAME"<>'XXXGSDR')
Очевидно, что ситуация в следующем: оптимизатор генерит предикат и крайне неверно оценивает его селективность.
Собственно workaround:
1) каким-либо образом заставить оптимизатор не генерить предикат, для этого можно использовать хинт NO_MERGE или NO_QUERY_TRANSFORMATION. В таком случае план будет имеет вид:
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 32 | | |
|* 2 | HASH JOIN | | 57 | 1824 | 9 (12)| 00:00:01 |
| 3 | VIEW | V1 | 111 | 1887 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| R1 | 111 | 2775 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | R2 | 501 | 7515 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V1"."OBJECT_NAME"='XXX'||"R2"."OBJECT_NAME")
4 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_NAME"<>'XXXSSDR' AND
"OBJECT_NAME"<>'XXXGSDR')
План исходного запроса с хинтом NO_QUERY_TRANSFORMATION тяжеловат, но по времени исполнения все таки существенно лучше.
2) пока искал другие пути обнаружил, что это явление - ошибка в коде оптимизатора.
Проверял следующие версии 9.2.0.4 SE Linux, 10.2.0.1 SE/EE Linux,
10.2.0.1 SE Windows, 11.1.0.6 EE Windows.
Этот эффект проявился исключиетльно на 10.2.0.1 SE Windows (такая же версия была и в продукционной системе, где собственно проблема и была обнаружена).
Автор:
YAP
на
11:14
0
коммент.
Ссылки на это сообщение
Ярлыки: cardinaliry feedback