2009/12/02

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.

2009/11/28

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.

2009/11/19

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.

2009/10/09

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

2009/05/29

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

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

2009/01/01

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.

2008/12/28

Древовидные справочники в качестве таблиц-размерностей или анализ “что-если” применительно к “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.
Фактически, такая последовательность действий дает возможность понять — какой план можно получить в ситуации, когда оптимизатор имеет точную информацию о кардинальности, станет ли такой план достаточно эффективным. Ведь в результате анализа может также выяснится, что сама задача, которую решает настраиваемый запрос, требует переформулирования. Кстати, окончательным решением моего коллеги по настройке запроса стало именно переформулирование задачи.