Представления словаря данных

Преамбула: когда-то давно, лет пять назад, я стоял у истоков одной большой информационной системы, был ее архитектором. Сейчас занимаюсь поддержкой и развитем этой системы. В основе выбранной мною архитектуры лежало понятие изолированных подсистем, каждая подсистема находится в отдельной схеме базы данных. Экземпляр системы состоит из набора схем, имеющих дозированный доступ друг к другу. Кроме того, информационная система является распределенной, т. е. происходит репликация данных между узлами. При тестировании, чтобы иметь возможность развернуть несколько экземпляров системы в одной физической базе, было принято решение использовать для имен схем префиксы, а для обращения подсистем друг к другу - приватные синонимы. Система пошла в "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.00
devdb> 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".