Data generator

Yesterday I had fallen to new issue with bad performance of "reusable code". It was some piece of code seemed to be very elegant from programmer point of view.
Complicated select statement contained the some kind of "data generator" as inline view like following:

select add_months(:p_begin_date, level - 1) dt
  from dual
connect by level < months_between(:p_end_date, :p_begin_date) + 2

This subquery aimed to generate a list of month between two dates which are specified as bind variables.
As a result, the complex query was failed with "ORA-01652: unable to extend temp segment by string in tablespace string" approximately at 13 step of plan (see below)

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                                     |                     |      1 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   2 |   NESTED LOOPS OUTER                        |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   3 |    NESTED LOOPS                             |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  4 |     HASH JOIN                               |                     |      1 |      1 |      0 |00:00:00.01 |       0 |   740K|   740K|          |
|   5 |      VIEW                                   |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |       SORT GROUP BY                         |                     |      1 |      1 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|*  7 |        HASH JOIN OUTER                      |                     |      1 |      1 |      0 |00:00:00.01 |       0 |   742K|   742K|          |
|   8 |         VIEW                                |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   9 |          NESTED LOOPS                       |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  10 |           NESTED LOOPS                      |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 11 |            HASH JOIN                        |                     |      1 |      1 |      0 |00:00:00.01 |       0 |   723K|   723K|          |
|* 12 |             HASH JOIN                       |                     |      1 |      1 |      0 |00:00:00.01 |       0 |   776K|   776K|          |
|* 13 |              HASH JOIN                      |                     |      1 |    524 |    301M|04:06:15.61 |     257 |    82M|  5277K|  143M (0)|
|  14 |               MERGE JOIN CARTESIAN          |                     |      1 |  39178 |   1781K|00:00:00.04 |     161 |       |       |          |
|  15 |                VIEW                         |                     |      1 |      1 |     48 |00:00:00.01 |       0 |       |       |          |
|  16 |                 CONNECT BY WITHOUT FILTERING|                     |      1 |        |     48 |00:00:00.01 |       0 |       |       |          |
|  17 |                  FAST DUAL                  |                     |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|  18 |                BUFFER SORT                  |                     |     48 |  39178 |   1781K|00:00:00.04 |     161 |  1753K|   641K| 1558K (0)|
|  19 |                 INDEX FAST FULL SCAN        | XIF_TCCP_SET1       |      1 |  39178 |  37124 |00:00:00.01 |     161 |       |       |          |
|  20 |               TABLE ACCESS FULL             | TMP_TAR_DATES_DAY   |      1 |  41996 |  16274 |00:00:00.07 |      96 |       |       |          |
|  21 |              INLIST ITERATOR                |                     |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  22 |               TABLE ACCESS BY INDEX ROWID   | TMP_CALC_PROWS      |      0 |  92152 |      0 |00:00:00.01 |       0 |       |       |          |
|* 23 |                INDEX RANGE SCAN             | XIF_TCPR_SET2       |      0 |   1750 |      0 |00:00:00.01 |       0 |       |       |          |
|* 24 |             TABLE ACCESS FULL               | TMP_PTO_CALC        |      0 |  19983 |      0 |00:00:00.01 |       0 |       |       |          |
|* 25 |            TABLE ACCESS BY INDEX ROWID      | TMP_TAR_DATES       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 26 |             INDEX RANGE SCAN                | XIF_TTD_TMP_SET1    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  27 |           TABLE ACCESS BY INDEX ROWID       | TMP_PRT_CALC_PARAMS |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 28 |            INDEX RANGE SCAN                 | XIF_TPCP_SET1       |      0 |    449 |      0 |00:00:00.01 |       0 |       |       |          |
|* 29 |         TABLE ACCESS FULL                   | TMP_CALC_IDX_ROWS   |      0 |   2166 |      0 |00:00:00.01 |       0 |       |       |          |
|* 30 |      TABLE ACCESS FULL                      | TMP_PTO_CALC        |      0 |  19983 |      0 |00:00:00.01 |       0 |       |       |          |
|* 31 |     TABLE ACCESS BY INDEX ROWID             | ERRD                |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 32 |      INDEX RANGE SCAN                       | XIF1661_ERRD        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  33 |    TABLE ACCESS BY INDEX ROWID              | MPMNT               |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 34 |     INDEX UNIQUE SCAN                       | XPK_PMONTH          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------

The main cause of problem is very bad cardinality estimating for "data generator".

SQL> explain plan for
  2  select add_months(:p_begin_date, level - 1) dt
  3    from dual
  4  connect by level < months_between(:p_end_date, :p_begin_date) + 2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

CBO will decide to assume the single row cardinality for such subquery without any hint. But it is wrong for most combinations of values which are in use.
My recommendation was to add the CARDINALITY hint with enough high value, 100 for example. It will cover most of cases at this time and in future, when the distance will rise between P_BEGIN_DATE and P_END_DATE.

Reusable program code

I had finished the first stage of highly intensive performance tuning project yesterday. I had found the problem that is very illustrative for statement about "poor effectiveness of universal program code". In my case it was reusable code of "individual calculations" that have been reused in "mass calculations". This is mistaken itself. But another effect had took place in the run time. It was happened that "individual calculation" had started first and sql query plans were parsed with little volume of data. Notice: global temporary tables is used for most queries in this task.

For example, one of query plans was obtained with

select * from table(dbms_xplan.display_cursor('&1',null,'LAST ALLSTATS'));

Its run time statistics confirms the fact that last start was executed as "individual calculation"

Plan hash value: 645901738
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                    |                    |      1 |      1 |    172 |00:00:00.01 |      16 |   766K|   766K|          |
|   2 |   MERGE JOIN CARTESIAN        |                    |      1 |     12 |    228 |00:00:00.01 |      10 |       |       |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| PORDER             |      1 |      1 |     12 |00:00:00.01 |       7 |       |       |          |
|   4 |     NESTED LOOPS              |                    |      1 |      1 |     14 |00:00:00.01 |       6 |       |       |          |
|*  5 |      TABLE ACCESS FULL        | TMP_PNF_TO_CALC    |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|*  6 |      INDEX RANGE SCAN         | IDX$$_0EFF0001     |      1 |      2 |     12 |00:00:00.01 |       3 |       |       |          |
|   7 |    BUFFER SORT                |                    |     12 |     12 |    228 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   8 |     TABLE ACCESS FULL         | TMP_TAR_DATES      |      1 |     12 |     19 |00:00:00.01 |       3 |       |       |          |
|*  9 |   TABLE ACCESS FULL           | TMP_CALC_P_ROWS    |      1 |    178 |    287 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

But after I have examined overall run time statistics for this query with

select * from table(dbms_xplan.display_cursor('&1',null,'ALL ALLSTATS'));

it became clear that plan for "mass calculation" exactly match the plan for "individual calculation". In other words it was reused.

Plan hash value: 645901738
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                    |                    |     84 |      1 |   404 |    11  (10)| 00:00:01 |   5680 |00:00:00.14 |    1201 |      0 |   766K|   766K|          |
|   2 |   MERGE JOIN CARTESIAN        |                    |     84 |     12 |   900 |     8   (0)| 00:00:01 |    191M|00:03:11.20 |   22759 |    282 |       |       |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| PORDER             |     84 |      1 |    27 |     4   (0)| 00:00:01 |   8995 |00:00:03.14 |   22424 |    282 |       |       |          |
|   4 |     NESTED LOOPS              |                    |     84 |      1 |    53 |     6   (0)| 00:00:01 |  19632 |00:00:03.04 |   22040 |    280 |       |       |          |
|*  5 |      TABLE ACCESS FULL        | TMP_PNF_TO_CALC    |     84 |      1 |    26 |     2   (0)| 00:00:01 |  10554 |00:00:00.03 |     308 |      0 |       |       |          |
|*  6 |      INDEX RANGE SCAN         | IDX$$_0EFF0001     |  10554 |      2 |       |     2   (0)| 00:00:01 |   8995 |00:00:02.96 |   21732 |    280 |       |       |          |
|   7 |    BUFFER SORT                |                    |   8995 |     12 |   264 |     4   (0)| 00:00:01 |    191M|00:00:00.07 |     335 |      0 |  2048 |  2048 |    66/0/0|
|   8 |     TABLE ACCESS FULL         | TMP_TAR_DATES      |     66 |     12 |   264 |     2   (0)| 00:00:01 |  23269 |00:00:00.01 |     335 |      0 |       |       |          |
|*  9 |   TABLE ACCESS FULL           | TMP_CALC_P_ROWS    |     65 |    178 | 58562 |     2   (0)| 00:00:01 |  12305 |00:00:00.03 |     313 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Notice, that last start of this query was failed with "ORA-01652: unable to extend temp segment by string in tablespace string", which had occurred approximately at step 2 of plan. Probably that`s why the run time statistics seems to be inconsistent. Especially between the second and the first steps of plan in "A-Rows" and "Buffers" columns.
For solving this problem I had recommended to create another copy of code of "individual calculations" and slightly change the text of sql queries, which was shared for both calculation approaches.
After implementing my recommendations, the new plan had appeared:

Plan hash value: 3979030168
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                  |                    |      1 |      4 |    128K|00:00:05.82 |     447K|       |       |          |
|   2 |   NESTED LOOPS                 |                    |      1 |      1 |    128K|00:00:04.15 |     318K|       |       |          |
|*  3 |    HASH JOIN                   |                    |      1 |      8 |   9200 |00:00:00.13 |     338 |  1308K|  1162K| 1820K (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID| PORDER             |      1 |      8 |   9200 |00:00:00.03 |     276 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | XIF1660_PORDER     |      1 |     39 |   9200 |00:00:00.01 |      47 |       |       |          |
|*  6 |     TABLE ACCESS FULL          | TMP_PNF_TO_CALC    |      1 |  11167 |  11167 |00:00:00.03 |      62 |       |       |          |
|*  7 |    TABLE ACCESS BY INDEX ROWID | TMP_CALC_P_ROWS    |   9200 |      1 |    128K|00:00:03.88 |     318K|       |       |          |
|*  8 |     INDEX RANGE SCAN           | XIF_TCPR_SET1      |   9200 |     26 |    302K|00:00:00.55 |   19333 |       |       |          |
|*  9 |   INDEX RANGE SCAN             | XIF_TTD_TMP_SET1   |    128K|      3 |    128K|00:00:01.50 |     128K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

I emphasize, that it is only the first stage of optimization with very low cost of implementation. It was allowed to speed up "mass calculation" about three times and reduce the tension of overall project.
Further steps will be a) investigation of each query from top query consumers but more likely b) redesign of whole "mass calculation" algorithm.

Parameter File Search Order in Oracle RAC

It is still not documented or rather poorly documented thing about "Parameter File Search Order in Oracle RAC". Even documentation release 11.2 continue to assert the following:

Oracle Database searches for your parameter file in a particular 
order depending on your platform.

On Linux and UNIX platforms, the search order is as follows:

  1. $ORACLE_HOME/dbs/spfilesid.ora
  2. $ORACLE_HOME/dbs/spfile.ora
  3. $ORACLE_HOME/dbs/initsid.ora

If you start your RAC database using srvctl or it is autostarted by clusterware during system boot, you may discover that sometimes database will seem to be started from another server parameter file than that, which must be in use according to Oracle Documentation, especially when you migrated your database from non-RAC configuration to RAC on same hardware.
In fact, which spfile will be used in such circumstance is determined by configuration information stored in OCR, namely:

oracle@srv1:~> srvctl config database -d iadb -a
cntn1 iadb1 /opt/oracle/product/10.2/db_1
cntn3 iadb2 /opt/oracle/product/10.2/db_1
DB_NAME: iadb
ORACLE_HOME: /opt/oracle/product/10.2/db_1
SPFILE: +DATA1/iadb/spfileiadb.ora
DOMAIN: null
DB_ROLE: PRIMARY
START_OPTIONS: null
POLICY:  AUTOMATIC
ENABLE FLAG: DB ENABLED

And if you start RAC database instance using sqlplus, than spfile will be used from places that specified in documentation.

DBMS_XPLAN.DISPLAY_CURSOR for RAC database.

I had the two-node Oracle RAC till recently. And it was sufficient to use the dbms_xplan.display_cursor function to examine the query execution plans with run-time statistics with connection to required instance.
However, recently my oracle databases have migrated onto new hardware with eight nodes. It is become inconveniently to use dbms_xplan.display_cursor for examination of all instances and all child cursors plans.
For this purposes I have written wrapper for dbms_xplan.display function:

create or replace function sys.gdisplay(statement_id varchar2,
                                        format       varchar2 default 'TYPICAL')
  return sys.dbms_xplan_type_table
  pipelined is
  l_line sys.dbms_xplan_type := sys.dbms_xplan_type(null);
begin
  for i in (select distinct inst_id, CHILD_NUMBER
              from gv$sql_plan_statistics_all
             where sql_id = statement_id
             order by 1, 2) loop
    l_line.plan_table_output := 'INSTANCE#=' || i.inst_id ||
                                '; CHILD_NUMBER=' || i.CHILD_NUMBER;
    pipe row(l_line);
    for j in (select *
                from table(dbms_xplan.display('gv$sql_plan_statistics_all',
                                              null,
                                              format,
                                              'inst_id=' || i.inst_id ||
                                              ' and sql_id=''' ||
                                              statement_id ||
                                              ''' and CHILD_NUMBER=' ||
                                              i.CHILD_NUMBER))) loop
      l_line.plan_table_output := j.plan_table_output;
      pipe row(l_line);
    end loop;
    l_line.plan_table_output := '================================================';
    pipe row(l_line);
  end loop;
  return;
end;

Now I can see execution plans and run-time statistics for certain query for all instances and child cursors without big effort.
But I was obliged to include the "sql_id=<...>" filter into filter_preds parameter, because if I use the statement_id parameter to point at certain cursor, I receive the error on my 10.2.0.4 SUSE 10 sp2 ia64 box:
ORA-12805: parallel query server died unexpectedly

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

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

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.