Новые посты на orasql.org

К сожалению, опять пока не успел сделать версию на русском поэтому пока просто ссылки с кратким описанием:
1. Сравнение механизмов кэширования deterministic функций и scalar subquery caching
2. Краткий пример того, что материализация subquery factoring может быть рассмотрена CBO(без хинта) только если есть хоть один предикат, даже бессмысленный 1=1
3. Моя версия Кайтовского runstats

“Abridged” call stack в трассировке "dump sort statistics"(event 10032)

Пару месяцев назад я обнаружил, что в трассировке сортировки в 11.2.0.3 появился short call stack и даже уже советовал на форуме как более простой oradebug short_stack, хотя, честно говоря, из-за тотального недостатка свободного времени до сих пор не разбирался в отличиях. Я даже не знаю с 11.2.0.2 или 11.2.0.3 он появился, т.к. все время забываю это проверить, когда есть под рукой 11.2.0.2, но в 11.2.0.1 его точно не было.

Под катом пример:

Дальше »

Удивительная оптимизация получения distinct values из индекса, а также TopN для каждого

Несколько дней назад на форуме задали, как изначально показалось, старый, скучный, вдоль и поперек изъезженный вопрос:

Есть лента новостей. Все новости разделены на 10 категорий(Политика, спорт, авто, недвижимость и тд).
Надо 1 запросом для каждой категории выбрать 4 новости.
Получается если перебрать результат - сразу идет 4 новости о политике, затем 4 новости о спорте и тд.

Однако, задача стояла - сделать это оптимально, а стандартное решение с обычным TopN через row_number никак оптимальным не назвать, особенно в случае больших таблиц, относительно небольшого количества категорий и неравномерного распределения или просто общей низкой селективности. И вот после нескольких более-менее хороших вариантов, подглядев в решение с PostgreSQL(правда я в нем не стал сильно разбираться, достаточно было увидеть рекурсию, min и предикат), получился отличный вариант.

Но обо всем по порядку:

1. Получение distinct значений из индекса

Дальше »

Включение parallel для index range scan и создания констрейнтов

Сегодня нужно было ускорить большую разовую выгрузку(вообще был "insert/*+append*/ select", но это не суть важно в данном контексте): большая не секционированная таблица, достаточно хорошая селективность по индексу( ~1.2%), больше 95% времени идет на lookup в таблицу из индекса. Один только индекс размером 44ГБ...

Изначальный запрос был вида:

select t_big.*
from t_big
where t_big.a=:a

Естественно, сразу захотелось распараллелить, но index range scan не идет в параллели, поэтому пришлось прибегнуть к небольшому ухищрению, которое позволило ускорить выполнение более чем в 13 раз при установке DOP в 16!

Метод прост: т.к.вычитка из индекса достаточно быстрая, то просто читаем и материализуем rowid через IRS, и затем уже в параллели идем к таблице через TABLE ACCESS BY USER ROWID.

Окончательный запрос:

with rids as(
            select--+ materialize
               rowid rid
            from t_big t1
            where t1.a=:a
)
select/*+ use_nl(rids t_big) */ t_big.*
from t_big, rids
where t_big.rowid=rids.rid

Создание констрейнтов в параллели:
По умолчанию при создании констрейнтов они валидируются не распараллеливаясь, что существенно замедляет процесс. Особенно учитывая, что обычно это происходит в технологические перерывы и ресурсов полно.. Решается же проблема просто: создавать их нужно с enable novalidate, и лишь затем отдельно включать enable validate:

alter table t1 
   add constraint fk_t1
   foreign key(a)
   references t2(a)
   enable novalidate;
alter table t1 
   modify constraint fk_t1
   enable validate;

UPD: Исправил на enable novalidate, т.к. как правильно заметил Тимур c disable не будет работать с unique и foreign key, а будет только для check constraints.

О рекурсивных сессиях

Отличная статья от Танела Поддера: http://tech.e2sn.com/oracle/oracle-internals-and-architecture/recursive-sessions-and-ora-00018-maximum-number-of-sessions-exceeded

Трассировка Event 10046, уровни 16,32,64

Christian Antognini опубликовал полный список уровней для 10046-й трассы.
Утащу к себе, пока не запомню :)

Repost: Christian Antognini. "Event 10046 – Full List of Levels"

Extended SQL trace (a.k.a. debugging event 10046 at a level higher than 1) is one of the key features provided by Oracle to troubleshoot applications using Oracle Database. For many years the available levels were always the same (4, 8 and 12). In fact, since I wrote my first paper about it in May 2000 and the release of 11g nothing changed.

With 11g, as I described in this post, new levels (16 and 32) were introduced.

More recently, with the introduction of the fix for bug 8328200, a new one was added to the list (64).

So, I thought it was time to publish the current list of available levels…

Level	Description
0	The debugging event is disabled.
1	The debugging event is enabled. For each processed database call, the following information is given: SQL statement, response time, service time, number of processed rows, number of logical reads, number of physical reads and writes, execution plan, and little additional information. Up to 10.2 an execution plan is written to the trace file only when the cursor it is associated with is closed. The execution statistics associated to it are values aggregated over all executions. As of 11.1 an execution plan is written to the trace file only after the first execution of every cursor. The execution statistics associated to it are the ones of the first execution only.
4	As in level 1, with additional information about bind variables. Mainly, the data type, its precision, and the value used for each execution.
8	As in level 1, plus detailed information about wait time. For each wait experienced during the processing, the following information is given: the name of the wait event, the duration, and a few additional parameters identifying the resource that has been waited for.
16	As in level 1, plus the execution plans information is written to the trace file for each execution. Available as of 11.1 only.
32	As in level 1, but without the execution plans information. Available as of 11.1 only.
64	As in level 1, plus the execution plans information might be written for executions following the first one. The condition is that, since the last write of execution plans information, a particular cursor consumed at least one additional minute of DB time. This level is interesting in two cases. First, when the information about the first execution is not enough for analysing a specific issue. Second, when the overhead of writing the information about every execution (level 16) is too high. Generally available as of 11.2.0.2 only.

In addition to the levels described in the previous table, you can also combine the levels 4 and 8 with every other level greater than 1. For example:

• Level 12 (4 + 8): simultaneously enable level 4 and level 8.
• Level 28 (4 + 8 + 16): simultaneously enable level 4, level 8 and level 16.
• Level 68 (4 + 64): simultaneously enable level 4 and level 64.

If you are using dbms_monitor or dbms_session for enabling extended SQL trace, here is the mapping between the levels and the parameters:

• Level 4: waits=FALSE, binds=TRUE, plan_stat=’first_execution’
• Level 8: waits=TRUE, binds=FALSE, plan_stat=’first_execution’
• Level 16: waits=FALSE, binds=FALSE, plan_stat=’all_executions’
• Level 32: waits=FALSE, binds=FALSE, plan_stat=’never’
• Level 64: not available yet

As you can see from the previous list, it is not possible to enable level 64 through dbms_monitor and dbms_session. Hence, statements like the following ones or ORADEBUG have to be used:

alter session set events '10046 trace name context forever, level 64'

alter session set events 'sql_trace wait=false, bind=false, plan_stat=adaptive'

I really hope that this limitation will be removed very soon.

Ссылка на онлайн unwrapper в документации Oracle

Все, конечно, знают про unwrapper'ы и многие даже пробовали свой написать (и я в том числе, после после презентации Pete Finnigan'а :), но тем не менее забавно, что Oracle в документации к 11.2 прямо-таки "громогласно" заявляет о том, что ничего unwrap от просмотра не защищает, но еще и прямую ссылку дает на online unwrapper :)

11.2 PL/SQL Source Text Wrapping:

Note:

Wrapping text does not prevent anyone from displaying it with a utility such as:

http://www.codecheck.info/UnwrapIt/

For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator's Guide.

И для сравнения прогресс документации от версии к версии о возможности unwrap'а:

• В 9.2 все скрывает надежно, кроме литералов и названий переменных, таблиц, колонок:

  String literals, number literals, and names of variables, tables, and columns remain in plain text within the wrapped file. Wrapping a procedure helps to hide the algorithm and prevent reverse-engineering, but it is not a way to hide passwords or table names that you want to be secret.

• А в 10.2 уже защищено лишь от бóльшего количества пользователей, но все-таки затрудняет реверс-инжиниринг!

  Although wrapping a compilation unit helps to hide the algorithm and makes reverse-engineering difficult, Oracle Corporation does not recommend it as a secure method for hiding passwords or table names. Obfuscating a PL/SQL unit prevents most users from examining the source code, but might not stop all attempts.

• В 11.1 как-то скромно и скучно:

  Wrapping is not a secure method for hiding passwords or table names. Wrapping a PL/SQL unit prevents most users from examining the source code, but might not stop all of them.

Регулярные выражения Oracle с вычисляемой заменяемой частью

Интересная тема с возможными решениями двух стандартных задач:
1) sprintf в oracle в SQL
2) замена по регулярке с вычисляемой заменяемой частью(а ля модификатор "e" в perl)

Интересный вопрос от Валентина Никотина

Тестовая таблица:

create table tclob(c clob);

Что будет выведено кодом из нижеследующих блоков с rollback и без:

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1:='1';
  insert into tclob values(cl1) returning c into cl2;
  cl3:=cl2;
  dbms_lob.append(cl3,'2');
  select c into cl4 from tclob;
--  rollback;

  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '2');
  select c into cl4 from tclob;
--  rollback;

  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '2');
  dbms_lob.append(cl3, '3');
  select c into cl4 from tclob;
--  rollback;

  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '22');
  dbms_lob.append(cl3, '3');
  dbms_lob.append(cl2, '44');
  select c into cl4 from tclob;
--  rollback;
  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

Проверьте и на винде, и на солярке/линуксе :)

Ясно, что этот баг платформозависим и что дело в особенностях работы с памятью. Ответ кроется в том, что у cl3 и cl2 не синхронизированы длины, т.е. Oracle "забывает" изменить длины всех остальных переменных, указывающих на этот clob, а т.к. каждая операция изменения cl2/cl3 фактически изменяет одно и то же, то "лишнее" перезаписывается.

Большое количество latch free: dml allocation latch при конкурентных запросах к v$lock

Проблема эта в общем-то старая, но только сейчас благодаря книге "Oracle Core" Джонатана Льюиса удалось узнать в чем собственно проблема и справиться с ней.
Цитата из главы "Latches for lock":

If the enqueue resource is in place already, then pick a row from the relevant enqueue structure (x$ksqeq, et al.), but to do this you have to get the associated enqueue latch to stop other people from picking the same enqueue row at the same time. The latch you need to acquire depends on the specific type of enqueue you are using; for example, if you want a row from x$ksqeq you need to get the enqueue latch but for a row from x$ktadm you need to get the dml allocation latch. Drop this latch as soon as you have made the enqueue row safe.

Дальше »

Список и описание новых хинтов в Oracle 11.1 - 11.2.0.3(включая недокументированные)

Увидев новые пару строк на 11.2.0.3 в списке хинтов(V$SQL_HINT), решил свести их описания в одну удобную заметку, попутно попытавшись разобраться в них.
Описание до конца еще не закончено, постараюсь сделать это на днях.

Cписок хинтов добавленных в 11g

NAME	INVERSE	VERSION
FULL_OUTER_JOIN_TO_OUTER	NO_FULL_OUTER_JOIN_TO_OUTER	11.2.0.3
OUTER_JOIN_TO_ANTI	NO_OUTER_JOIN_TO_ANTI	11.2.0.3
TABLE_LOOKUP_BY_NL	NO_TABLE_LOOKUP_BY_NL	11.2.0.2
USE_HASH_GBY_FOR_PUSHDOWN	NO_USE_HASH_GBY_FOR_PUSHDOWN	11.2.0.2
XDB_FASTPATH_INSERT	NO_XDB_FASTPATH_INSERT	11.2.0.2
APPEND_VALUES	NOAPPEND	11.2.0.1
COALESCE_SQ	NO_COALESCE_SQ	11.2.0.1
CONNECT_BY_ELIM_DUPS	NO_CONNECT_BY_ELIM_DUPS	11.2.0.1
DST_UPGRADE_INSERT_CONV	NO_DST_UPGRADE_INSERT_CONV	11.2.0.1
EXPAND_TABLE	NO_EXPAND_TABLE	11.2.0.1
FACTORIZE_JOIN	NO_FACTORIZE_JOIN	11.2.0.1
NO_SUBSTRB_PAD		11.2.0.1
PLACE_DISTINCT	NO_PLACE_DISTINCT	11.2.0.1
STATEMENT_QUEUING	NO_STATEMENT_QUEUING	11.2.0.1
TRANSFORM_DISTINCT_AGG	NO_TRANSFORM_DISTINCT_AGG	11.2.0.1
XMLINDEX_SEL_IDX_TBL		11.2.0.1
BIND_AWARE	NO_BIND_AWARE	11.1.0.7
CHANGE_DUPKEY_ERROR_INDEX		11.1.0.7
IGNORE_ROW_ON_DUPKEY_INDEX		11.1.0.7
RETRY_ON_ROW_CHANGE		11.1.0.7
CHECK_ACL_REWRITE	NO_CHECK_ACL_REWRITE	11.1.0.6
COST_XML_QUERY_REWRITE	NO_COST_XML_QUERY_REWRITE	11.1.0.6
DB_VERSION		11.1.0.6
DOMAIN_INDEX_FILTER	NO_DOMAIN_INDEX_FILTER	11.1.0.6
INDEX_RS_ASC		11.1.0.6
INDEX_RS_DESC		11.1.0.6
MONITOR	NO_MONITOR	11.1.0.6
NLJ_BATCHING	NO_NLJ_BATCHING	11.1.0.6
NLJ_PREFETCH	NO_NLJ_PREFETCH	11.1.0.6
NO_LOAD		11.1.0.6
OUTER_JOIN_TO_INNER	NO_OUTER_JOIN_TO_INNER	11.1.0.6
PLACE_GROUP_BY	NO_PLACE_GROUP_BY	11.1.0.6
RESULT_CACHE	NO_RESULT_CACHE	11.1.0.6
SUBQUERY_PRUNING	NO_SUBQUERY_PRUNING	11.1.0.6
USE_INVISIBLE_INDEXES	NO_USE_INVISIBLE_INDEXES	11.1.0.6
USE_MERGE_CARTESIAN		11.1.0.6
XML_DML_RWT_STMT		11.1.0.6
XMLINDEX_REWRITE	NO_XMLINDEX_REWRITE	11.1.0.6
XMLINDEX_REWRITE_IN_SELECT	NO_XMLINDEX_REWRITE_IN_SELECT	11.1.0.6
CONNECT_BY_CB_WHR_ONLY	NO_CONNECT_BY_CB_WHR_ONLY	10.2.0.5

FULL_OUTER_JOIN_TO_OUTER / NO_FULL_OUTER_JOIN_TO_OUTER

SQL feature:	QKSFM_CBO
Class:	FULL_OUTER_JOIN_TO_OUTER
Version:	11.2.0.3
Документирован	нет

Включает/выключает механизм трансформации запроса из full outer join в left outer join, появившийся и работающий по умолчанию с 11.2.0.2.
Помимо самого хинта, появившегося в 11.2.0.3, управлять этим преобразованием можно начиная с 11.2.0.2 через fix control (bugno=9287401) и с помощью:

OPT_PARAM('_optimizer_full_outer_join_to_outer' 'false')

или alter session set "_optimizer_full_outer_join_to_outer"=true;

Пример:

create table mss_foj1(id primary key, a, constraint uq_foj1_a unique(a) using index) as select level id, level*2-1 a from dual connect by level<=1e5;
create table mss_foj2(id primary key, a, constraint uq_foj2_a unique(a) using index) as select level id, level*2 a from dual connect by level<=1e5;
begin
   dbms_stats.gather_table_stats(user,'mss_foj1',cascade=>true);
   dbms_stats.gather_table_stats(user,'mss_foj2',cascade=>true);
end;
/

>> explain plan for
  2  select
  3   *
  4  from mss_foj1 f1
  5       full outer join mss_foj2 f2
  6                  on f1.id=f2.id
  7  where f1.a=3;

Explained.

Elapsed: 00:00:00.03
 >> @xplan

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

Plan hash value: 2311935082

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |              |     1 |    20 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MSS_FOJ1     |     1 |    10 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | UQ_FOJ1_A    |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| MSS_FOJ2     |   100K|   976K|     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C0094323 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F1"."A"=3)
   5 - access("F1"."ID"="F2"."ID"(+))

18 rows selected.

Elapsed: 00:00:00.18

>> alter session set "_optimizer_full_outer_join_to_outer"=false;

Session altered.

Elapsed: 00:00:00.01
 >> explain plan for
  2  select
  3   *
  4  from mss_foj1 f1
  5       full outer join mss_foj2 f2
  6                  on f1.id=f2.id
  7  where f1.a=3;

Explained.

Elapsed: 00:00:00.04
 >> @xplan

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------

Plan hash value: 2034556344

------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |   100K|  5078K|       |  1684  (14)| 00:00:01 |
|*  1 |  VIEW                 | VW_FOJ_0 |   100K|  5078K|       |  1684  (14)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |   100K|  1953K|  2152K|  1684  (14)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | MSS_FOJ1 |   100K|   976K|       |   269  (22)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | MSS_FOJ2 |   100K|   976K|       |   268  (22)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("F1"."A"=3)
   2 - access("F1"."ID"="F2"."ID")

17 rows selected.

Elapsed: 00:00:01.03

OUTER_JOIN_TO_ANTI / NO_OUTER_JOIN_TO_ANTI

SQL feature:	QKSFM_CBO
Class:	OUTER_JOIN_TO_ANTI
Version:	11.2.0.3
Документирован	нет

Судя по названию и скрытому параметру _optimizer_outer_to_anti_enabled (Enable transformation of outer-join to anti-join if possible), введенному еще в 10.2, этот хинт отвечает за трансформацию outer join'ов к anti-join, но мне, к сожалению, не удалось воспроизвести это, и буду рад, если кто-нибудь поделится примером. Скажу сразу, что я пробовал только простые запросы полные аналоги классического anti-join'a.

TABLE_LOOKUP_BY_NL / NO_TABLE_LOOKUP_BY_NL

SQL feature:	QKSFM_TABLE_LOOKUP_BY_NL
Class:	TABLE_LOOKUP_BY_NL
Version:	11.2.0.2
Документирован	нет

С этим хинтом у меня тоже, к сожалению, разобраться не получилось. Из описания параметра "_optimizer_enable_table_lookup_by_nl" - consider table lookup by nl transformation. Кстати говоря, этот параметр в 11.2.0.1 по умолчанию был false, а с 11.2.0.2 - true.
В первую очередь я подумал, что он связан с новым вариантом nested loops появившемся в 11g, но у него есть свой хинт - NLJ_BATCHING. Второй мыслью было о том, что хинт отвечает за включение/выключение этапа анализа для возможности NLJ_BATCHING, но добавление хинта в запросы с nested loops планов не меняло(попозже попробую проанализировать трассировку 10053 с ним на различных nl - будет ли видно, что он добавляет/убирает). Третью мыслью было, что это относится к механизму table lookup prefetch, но тоже не получилось их как-то связать, т.к. у того тоже есть свое название - NLJ_PREFETCH.

В общем тоже прощу сообщить, если узнаете за что этот хинт отвечает.

USE_HASH_GBY_FOR_PUSHDOWN / NO_USE_HASH_GBY_FOR_PUSHDOWN

SQL feature:	USE_HASH_GBY_FOR_PUSHDOWN
Class:	USE_HASH_GBY_FOR_PUSHDOWN
Version:	11.2.0.2
Документирован	нет

И еще один хинт с которым не получилось разобраться: тоже судя по названию только догадываюсь о том, что этот хинт отвечает за использование hash group by для view pushed predicate, но сделать так, чтобы он хоть что-нибудь менял в 10053 мне не удалось. При этом, если моя догадка верна, непонятно где именно должен быть hash group by - по идее для hash group by задумывался use_hash_aggregation.
Правда он черезчур загадочный: например, даже при появлении его в плане в секции outline, в самом плане может быть sort group by:

ORCL/XTENDER> explain plan for
  2  with v_push as (
  3       select
  4          t_8k.c100
  5          ,count(*) s
  6       from  t_4k
  7            ,t_8k
  8       where t_4k.col_unique=t_8k.col_unique
  9       group by t_8k.c100
 10  )
 11  select
 12    *
 13  from
 14       t_1k
 15      ,v_push
 16  where t_1k.col_unique=v_push.c100(+)
 17        and t_1k.c3=5;

Explained.

Elapsed: 00:00:00.23
ORCL/XTENDER> @xplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------

Plan hash value: 438187520

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     3 |    96 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER             |              |     3 |    96 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | T_1K         |     3 |    51 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN             | IX_T_1K_C3   |     3 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE         |              |     1 |    15 |     2   (0)| 00:00:01 |
|   5 |    SORT GROUP BY                |              |     1 |    11 |     2   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                |              |   100 |  1100 |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| T_8K         |   100 |   700 |     2   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | IX_T_8K_C100 |   100 |       |     1   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN          | PK_T_4K      |     1 |     4 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2
   2 - SEL$2        / T_1K@SEL$2
   3 - SEL$2        / T_1K@SEL$2
   4 - SEL$9113C594 / V_PUSH@SEL$2
   5 - SEL$9113C594
   7 - SEL$9113C594 / T_8K@SEL$1
   8 - SEL$9113C594 / T_8K@SEL$1
   9 - SEL$9113C594 / T_4K@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$9113C594")
      USE_NL(@"SEL$9113C594" "T_4K"@"SEL$1")
      LEADING(@"SEL$9113C594" "T_8K"@"SEL$1" "T_4K"@"SEL$1")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------

      INDEX(@"SEL$9113C594" "T_4K"@"SEL$1" ("T_4K"."COL_UNIQUE"))
      INDEX_RS_ASC(@"SEL$9113C594" "T_8K"@"SEL$1" ("T_8K"."C100"))
      USE_NL(@"SEL$2" "V_PUSH"@"SEL$2")
      LEADING(@"SEL$2" "T_1K"@"SEL$2" "V_PUSH"@"SEL$2")
      NO_ACCESS(@"SEL$2" "V_PUSH"@"SEL$2")
      INDEX_RS_ASC(@"SEL$2" "T_1K"@"SEL$2" ("T_1K"."C3"))
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      PUSH_PRED(@"SEL$2" "V_PUSH"@"SEL$2" 1)
      OUTLINE_LEAF(@"SEL$9113C594")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T_1K"."C3"=5)
   8 - access("T_8K"."C100"="T_1K"."COL_UNIQUE")
   9 - access("T_4K"."COL_UNIQUE"="T_8K"."COL_UNIQUE")

XDB_FASTPATH_INSERT / NO_XDB_FASTPATH_INSERT

SQL feature:	QKSFM_ALL
Class:	XDB_FASTPATH_INSERT
Version:	11.2.0.2
Документирован	нет

Не документирован, но имеет свою ошибку:

ORA-19051: Cannot use fast path insert for this XMLType table

   Cause
   An attempt was made to insert using event 19049 into an XMLType table that does not support fast path insert.

   Action
   Unset event 19049 and try again.

APPEND_VALUES / NOAPPEND

SQL feature:	QKSFM_CBO
Class:	APPEND_VALUES
Version:	11.2.0.1
Документирован	да

Включает механизм direct path inserts аналогичный insert/*+ append*/ … select, но для insert into … values.

Пример:

forall i in c.first..c.last
insert/*+ APPEND_VALUES*/ values c(i);

Дополнительно: 1

COALESCE_SQ / NO_COALESCE_SQ

SQL feature:	QKSFM_COALESCE_SQ
Class:	COALESCE_SQ
Version:	11.2.0.1
Документирован	нет

Включает механизм удаления лишних join'ов c какой-либо таблицей(join elimination) в случаях подзапросов.

ORCL/XTENDER> explain plan for
  2  select * from mss_foj1 f1
  3  where
  4      exists(select * from mss_foj2 f2 where f2.id=f1.a)
  5  and exists(select * from mss_foj2 f2 where f2.id=f1.a);

Explained.

ORCL/XTENDER> @xplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------

Plan hash value: 4138783270

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              | 50000 |   732K|    71  (15)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |              | 50000 |   732K|    71  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MSS_FOJ1     |   100K|   976K|    62   (2)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C0014388 | 50000 |   244K|     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$82F4A621
   2 - SEL$82F4A621 / F1@SEL$1
   3 - SEL$82F4A621 / F2@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      COALESCE_SQ(@"SEL$2")
      COALESCE_SQ(@"SEL$3")
      ...
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F2"."ID"="F1"."A")

ORCL/XTENDER> explain plan for
  2  select/*+ NO_COALESCE_SQ(@SEL$2) NO_UNNEST(@SEL$2) */ * from mss_foj1 f1
  3  where
  4      exists(select * from mss_foj2 f2 where f2.id=f1.a)
  5  and exists(select * from mss_foj2 f2 where f2.id=f1.a)
  6  /

Explained.

Elapsed: 00:00:00.08
ORCL/XTENDER> @xplan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

Plan hash value: 2240918256

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     1 |    15 | 39164   (1)| 00:07:50 |
|*  1 |  FILTER             |              |       |       |            |          |
|   2 |   NESTED LOOPS SEMI |              | 50000 |   732K|    71  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| MSS_FOJ1     |   100K|   976K|    62   (2)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014388 | 50000 |   244K|     0   (0)| 00:00:01 |
|*  5 |   INDEX UNIQUE SCAN | SYS_C0014388 |     1 |     5 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST NO_COALESCE_SQ */ 0 FROM
              "MSS_FOJ2" "F2" WHERE "F2"."ID"=:B1))
   4 - access("F2"."ID"="F1"."A")
   5 - access("F2"."ID"=:B1)

Дополнительно:
1 2 3

CONNECT_BY_ELIM_DUPS / NO_CONNECT_BY_ELIM_DUPS

SQL feature:	QKSFM_ALL
Class:	CONNECT_BY_ELIM_DUPS
Version:	11.2.0.1
Документирован	нет

Включает механизм отсева дубликатов в иерархическом запросе(в строке плана с connect by появляется (UNIQUE), например "CONNECT BY NO FILTERING WITH SW (UNIQUE))", при выключенном просто "CONNECT BY NO FILTERING WITH START-WITH]".

select/*+ NO_CONNECT_BY_ELIM_DUPS */ distinct *
from t_connect_by
start with a=1
connect by prior a+1 = a

DST_UPGRADE_INSERT_CONV / NO_DST_UPGRADE_INSERT_CONV

SQL feature:	QKSFM_ALL
Class:	DST_UPGRADE_INSERT_CONV
Version:	11.2.0.1
Документирован	отчасти

Отвечает за работу внутренних операторов с конвертацией столбцов TIMESTAMP WITH TIMEZONE (TSTZ), которые ещё не были обновлены во время перехода на летнее время.

EXPAND_TABLE / NO_EXPAND_TABLE

SQL feature:	QKSFM_TABLE_EXPANSION
Class:	EXPAND_TABLE
Version:	11.2.0.1
Документирован	нет

В 11gR2 введен новый механизм, позволяющий использовать разные пути доступа к секциям в случаях, когда индексы в некоторых секциях изменены на unusable - это позволяет иметь отключать индексы на нагруженных dml секциях. Механизм хорошо описан тут. За включение механизма отвечает скрытый параметр _optimizer_table_expansion - consider table expansion transformation.

Пример:

create table t_expanded(part_key integer, field1 integer, field2 integer, val number)
partition by list(part_key)
(
    partition p1 values(1)
   ,partition p2 values(2)
   ,partition p3 values(3)
   ,partition p4 values(4)
   ,partition p5 values(5)
)
/
create index ix_t_expanded1 on t_expanded(field1, field2) local;
create index ix_t_expanded2 on t_expanded(field2) local;
alter index ix_t_expanded1 modify partition p1 unusable;
alter index ix_t_expanded2 modify partition p2 unusable;
/
insert/*+ APPEND */ into t_expanded
with t as (select level p from dual connect by level<=5)
    ,gen as (select level val from dual connect by level<=1e4)
select t.p
      ,decode(t.p,1,mod(val,3),mod(val,20))
      ,trunc(dbms_random.value(1,1000))
      ,dbms_random.normal
from t
    ,gen
/
select * from t_expanded te where te.field1=:a and te.field2=:b
/
begin 
   dbms_stats.gather_table_stats(null,'t_expanded',cascade=>true);
end;
/

>> explain plan for
  2  select/*+ NO_EXPAND_TABLE(TE) */
  3  *
  4  from t_expanded te
  5  where te.field1=:a
  6    and te.field2=:b;

Explained.

Elapsed: 00:00:00.03
 >> @advanced

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 2740702834

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     3 |    96 |   398  (16)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL|            |     3 |    96 |   398  (16)| 00:00:01 |     1 |     5 |
|*  2 |   TABLE ACCESS FULL| T_EXPANDED |     3 |    96 |   398  (16)| 00:00:01 |     1 |     5 |
-------------------------------------------------------------------------------------------------

>> explain plan for
  2  select *
  3  from t_expanded te
  4  where te.field1=:a
  5    and te.field2=:b;

Explained.

Elapsed: 00:00:00.04
>> @advanced

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 527277835

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |     3 |    96 |   242  (15)| 00:00:01 |       |       |
|   1 |  VIEW                                | VW_TE_2        |     6 |   312 |   242  (15)| 00:00:01 |       |       |
|   2 |   UNION-ALL                          |                |       |       |            |          |       |       |
|   3 |    PARTITION LIST INLIST             |                |     2 |    64 |   240  (15)| 00:00:01 |KEY(I) |KEY(I) |
|*  4 |     TABLE ACCESS FULL                | T_EXPANDED     |     2 |    64 |   240  (15)| 00:00:01 |KEY(I) |KEY(I) |
|   5 |    PARTITION LIST SINGLE             |                |     3 |    96 |     1   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_EXPANDED     |     3 |    96 |     1   (0)| 00:00:01 |     1 |     1 |
|*  7 |      INDEX RANGE SCAN                | IX_T_EXPANDED2 |    10 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   8 |    PARTITION LIST SINGLE             |                |     1 |    32 |     1   (0)| 00:00:01 |     2 |     2 |
|   9 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_EXPANDED     |     1 |    32 |     1   (0)| 00:00:01 |     2 |     2 |
|* 10 |      INDEX RANGE SCAN                | IX_T_EXPANDED1 |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------------------------------
Outline Data 
-------------
             
  /*+        
      BEGIN_OUTLINE_DATA
      ...
      EXPAND_TABLE(@"SEL$1" "TE"@"SEL$1")                                                                                                                                 
      ...
      END_OUTLINE_DATA                                                                                                                                                    
  */                                                                                                                                                                      
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   4 - filter("TE"."FIELD2"=TO_NUMBER(:B) AND "TE"."FIELD1"=TO_NUMBER(:A))                                                                                                
   6 - filter("TE"."FIELD1"=TO_NUMBER(:A))                                                                                                                                
   7 - access("TE"."FIELD2"=TO_NUMBER(:B))                                                                                                                                
  10 - access("TE"."FIELD1"=TO_NUMBER(:A) AND "TE"."FIELD2"=TO_NUMBER(:B))

FACTORIZE_JOIN / NO_FACTORIZE_JOIN

SQL feature:	QKSFM_JOINFAC
Class:	FACTORIZE_JOIN
Version:	11.2.0.1
Документирован	нет

В 11gR2 введен новый механизм, позволяющий объединять обращения к одной присоединяемой таблице по одним и тем же условиям в разных частях "union [all]" в один join на более позднем этапе после слияния других таблиц. Т.е. запрос вида:

select * from t1 join t2 on t1.a=t2.a where предикаты_по_таблице_t1_1
union all
select * from t1 join t2 on t1.a=t2.a where предикаты_по_таблице_t1_2

Будет трансформирован в

select * 
from t2 
   join (
      select * from t1 where предикаты_по_таблице_t1_1
      union all
      select * from t1 where предикаты_по_таблице_t1_2
   ) on t1.a=t2.a

DB11G/XTENDER> explain plan for
  2  select *
  3  from t_1k
  4      ,t_8k
  5  where t_1k.c10=t_8k.c100
  6    and t_1k.col_unique=:a
  7  union all
  8  select *
  9  from t_1k
 10      ,t_8k
 11  where t_1k.c10=t_8k.c100
 12    and t_1k.c1000=:b
 13  /

Explained.

Elapsed: 00:00:00.03
DB11G/XTENDER> @xplan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------

Plan hash value: 3121763019

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |   100K|  9384K|    16   (7)| 00:00:01 |
|*  1 |  HASH JOIN                     |                    |   100K|  9384K|    16   (7)| 00:00:01 |
|   2 |   VIEW                         | VW_JF_SET$154ACCB2 |  1001 | 78078 |     5   (0)| 00:00:01 |
|   3 |    UNION-ALL                   |                    |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_1K               |     1 |    17 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | PK_T_1K            |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL          | T_1K               |  1000 | 17000 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL            | T_8K               |  8000 |   140K|    10   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      FACTORIZE_JOIN(@"SET$1"("T_8K"@"SEL$1" "T_8K"@"SEL$2"))
      ...
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM_1"="T_8K"."C100")
   5 - access("T_1K"."COL_UNIQUE"=TO_NUMBER(:A))
   6 - filter("T_1K"."C1000"=TO_NUMBER(:B))

DB11G/XTENDER> explain plan for
  2  select--+ NO_FACTORIZE_JOIN(@SET$1)
  3    *
  4  from t_1k
  5      ,t_8k
  6  where t_1k.c10=t_8k.c100
  7    and t_1k.col_unique=:a
  8  union all
  9  select *
 10  from t_1k
 11      ,t_8k
 12  where t_1k.c10=t_8k.c100
 13    and t_1k.c1000=:b;

Explained.

Elapsed: 00:00:00.00
DB11G/XTENDER> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------

Plan hash value: 3677413949

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              | 80100 |  2737K|    18  (78)| 00:00:01 |
|   1 |  UNION-ALL                    |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |   100 |  3500 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_1K         |     1 |    17 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_T_1K      |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T_8K         |   100 |  1800 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IX_T_8K_C100 |   100 |       |     1   (0)| 00:00:01 |
|*  7 |   HASH JOIN                   |              | 80000 |  2734K|    14   (8)| 00:00:01 |
|*  8 |    TABLE ACCESS FULL          | T_1K         |  1000 | 17000 |     3   (0)| 00:00:01 |
|   9 |    TABLE ACCESS FULL          | T_8K         |  8000 |   140K|    10   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1
   2 - SEL$1
   3 - SEL$1 / T_1K@SEL$1
   4 - SEL$1 / T_1K@SEL$1
   5 - SEL$1 / T_8K@SEL$1
   6 - SEL$1 / T_8K@SEL$1
   7 - SEL$2
   8 - SEL$2 / T_1K@SEL$2
   9 - SEL$2 / T_8K@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "T_8K"@"SEL$1")
      LEADING(@"SEL$1" "T_1K"@"SEL$1" "T_8K"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T_8K"@"SEL$1" ("T_8K"."C100"))
      INDEX_RS_ASC(@"SEL$1" "T_1K"@"SEL$1" ("T_1K"."COL_UNIQUE"))
      USE_HASH(@"SEL$2" "T_8K"@"SEL$2")
      LEADING(@"SEL$2" "T_1K"@"SEL$2" "T_8K"@"SEL$2")
      FULL(@"SEL$2" "T_8K"@"SEL$2")
      FULL(@"SEL$2" "T_1K"@"SEL$2")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T_1K"."COL_UNIQUE"=TO_NUMBER(:A))
   6 - access("T_1K"."C10"="T_8K"."C100")
   7 - access("T_1K"."C10"="T_8K"."C100")
   8 - filter("T_1K"."C1000"=TO_NUMBER(:B))

Описание и пример от Тимура Ахмадеева.

NO_SUBSTRB_PAD

SQL feature:	QKSFM_EXECUTION
Class:	NO_SUBSTRB_PAD
Version:	11.2.0.1
Документирован	нет

Этот хинт используется dbms_stats в запросах сбора гистограмм. Он меняет поведение substrb - с этим хинтом результат substrb после получения подстроки по байтам будет еще обрезан по символам, т.е. остатки от мультибайтовых символов будут убраны:

SQL> select
  2     column_value
  3    ,dump(substrb(column_value,2,8)) "dump 2-8"
  4  from table(sys.odcivarchar2list('яяяяzz', 'zzzzяя','йййййй'));

COLUMN_VAL dump 2-8
---------- --------------------------------------------------
яяяяzz     Typ=1 Len=8: 32,209,143,209,143,209,143,122
zzzzяя     Typ=1 Len=7: 122,122,122,209,143,209,143
йййййй     Typ=1 Len=8: 32,208,185,208,185,208,185,32

3 rows selected.

Elapsed: 00:00:00.00
SQL> select--+ NO_SUBSTRB_PAD
  2     column_value
  3    ,dump(substrb(column_value,2,8)) "dump 2-8"
  4  from table(sys.odcivarchar2list('яяяяzz', 'zzzzяя','йййййй'));

COLUMN_VAL dump 2-8
---------- --------------------------------------------------
яяяяzz     Typ=1 Len=7: 209,143,209,143,209,143,122
zzzzяя     Typ=1 Len=7: 122,122,122,209,143,209,143
йййййй     Typ=1 Len=6: 208,185,208,185,208,185

PLACE_DISTINCT / NO_PLACE_DISTINCT

SQL feature:	QKSFM_DIST_PLCMT
Class:	PLACE_DISTINCT
Version:	11.2.0.1
Документирован	нет

В 11.2.0.1 введен новый механизм cbqt - перемещение кляузы distinct в запросах с джойнами с верхнего уровня в inner view, для уменьшения набора данных на наиболее ранних этапах.
При этом алиасы у этих внутренних представлений в плане будут начинаться с "VW_DTP_", в примере ниже это VW_DTP_7FEE568E.

Пример:

select
 distinct
  t1.c10,t2.c100
from (
       select c10,c100,c1000
       from t_1k
       where t_1k.col_unique <100
     ) t1
     ,(
       select c10,c100,c1000
       from t_8k
       where t_8k.col_unique in (select t_4k.c10 from t_4k where t_4k.c100=1)
     ) t2
where t1.c10=t2.c10

****************************************
 Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$658B16C2 (#1)
GBP: Checking validity of group-by placement for query block SEL$658B16C2 (#1)
GBP: Bypassed: Query has invalid constructs.
DP: Checking validity of distinct placement for query block SEL$658B16C2 (#1)

DP: Using search type: linear
DP: Considering distinct placement on query block SEL$658B16C2 (#1)
****************************************
DP: Starting iteration 1, state space = (2,3) : (0,0)
DP: Original query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "T_1K"."C10" "C10","T_8K"."C100" "C100" FROM "XTENDER"."T_4K" "T_4K","XTENDER"."T_1K" "T_1K","XTENDER"."T_8K" "T_8K" WHERE "T_1K"."C10"="T_8K"."C10" AND "T_1K"."COL_UNIQUE"<100 AND "T_8K"."COL_UNIQUE"="T_4K"."C10" AND "T_4K"."C100"=1
...
DP: Costing query block.
...
DP: Updated best state, Cost = 17.14
****************************************
DP: Starting iteration 2, state space = (2,3) : (1,0)
DP: Using DP transformation in this iteration.
...
DP: Transformed query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "VW_DTP_7FEE568E"."ITEM_1" "C10","T_8K"."C100" "C100" FROM  (SELECT DISTINCT "T_1K"."C10" "ITEM_1" FROM "XTENDER"."T_1K" "T_1K" WHERE "T_1K"."COL_UNIQUE"<100) "VW_DTP_7FEE568E","XTENDER"."T_4K" "T_4K","XTENDER"."T_8K" "T_8K" WHERE "VW_DTP_7FEE568E"."ITEM_1"="T_8K"."C10" AND "T_8K"."COL_UNIQUE"="T_4K"."C10" AND "T_4K"."C100"=1
...
DP: Costing query block.
...
DP: Not update best state, Cost = 18.14
****************************************
DP: Starting iteration 3, state space = (2,3) : (0,1)
DP: Using DP transformation in this iteration.
...
DP: Transformed query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "T_1K"."C10" "C10","T_8K"."C100" "C100" FROM  (SELECT DISTINCT "T_4K"."C10" "ITEM_1" FROM "XTENDER"."T_4K" "T_4K" WHERE "T_4K"."C100"=1) "VW_DTP_71E93533","XTENDER"."T_1K" "T_1K","XTENDER"."T_8K" "T_8K" WHERE "T_1K"."C10"="T_8K"."C10" AND "T_1K"."COL_UNIQUE"<100 AND "T_8K"."COL_UNIQUE"="VW_DTP_71E93533"."ITEM_1"
...
DP: Costing query block.
...
DP: Not update best state, Cost = 18.14
****************************************

****************************************
 Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$5428C7F1 (#1)
DP: Checking validity of distinct placement for query block SEL$5428C7F1 (#1)
DP: Bypassed: Query tables are not connected.

Дополнительно: пример от Тимура Ахмадеева.

PLACE_GROUP_BY / NO_PLACE_GROUP_BY

SQL feature:	QKSFM_PLACE_GROUP_BY
Class:	PLACE_GROUP_BY
Version:	11.1.0.6
Документирован	нет

Этот механизм аналогичен PLACE_DISTINCT(точнее, наоборот, т.к. PLACE_DISTINCT появился позже) и уже давно и хорошо описан: У Льюиса в презентации Riyaj Shamsudeen В планах эти inner-view легко заметить по префиксу "VW_GBC_", например VW_GBC_10.

TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG

SQL feature:	QKSFM_INDEX_RS_ASC
Class:	ACCESS
Version:	11.1.0.6
Документирован	нет

Тоже новый механизм трансформирования запроса c distinct для использования нового hash group aggregate, у этих вложенных представлений префиксы "VW_DAG_". Скрытый параметр - "_optimizer_distinct_agg_transform". Пример.

BIND_AWARE / NO_BIND_AWARE

SQL feature:	QKSFM_CURSOR_SHARING
Class:	BIND_AWARE
Version:	11.1.0.7
Документирован	нет

Это известнейший хинт включения/выключения bind aware cursor sharing(is_bind_aware в v$sql_shared_cursor). Подробнее Дополнительно А тут забавно прочесть, что в официальном блоге рекомендуют недокументированный хинт

INDEX_RS_ASC / INDEX_RS_DESC

SQL feature:	QKSFM_TRANSFORMATION
Class:	TRANSFORM_DISTINCT_AGG
Version:	11.2.0.1
Документирован	нет

Эти обычные хинты для index range scan [asc/desc] появились в общем-то давно, в аутлайнах профилей они в 10.2 уже были.

NLJ_BATCHING / NO_NLJ_BATCHING

SQL feature:	QKSFM_EXECUTION
Class:	ACCESS
Version:	11.1.0.6
Документирован	нет

Как я уже говорил в описании к хинту TABLE_LOOKUP_BY_NL в 11g появился новый вариант nested loops join и этот NLJ_BATCHING как раз и позволяет включать и выключать этот механизм. Дополнительно: Jeff's blog: Thoughts on NLJ batching Dion Cho: Batching NLJ optimization and ordering

NLJ_PREFETCH / NO_NLJ_PREFETCH

SQL feature:	QKSFM_EXECUTION
Class:	ACCESS
Version:	11.1.0.6
Документирован	нет

Отвечают за механизм nested loops table prefetch, появившийся в 9i, хорошо объяснено тут:

• Logical I/O - Evolution: Part 2 - 9i, 10g Prefetching
• Logical I/O Evolution - Part 3: 11g
• и дополнительно у Тома

О производительности обработки исключений

Эта заметка посвящена известному факту о низкой производительности обработки исключений.
Да, действительно, обработка исключений достаточно медленна, однако, не стоит сразу же пытаться избегать их везде, где только можно, да еще и любыми способами. Например, часто вижу как их пытаются избегать даже в случаях поиска по первичному ключу c минимальной вероятностью получения no_data_found.
В целом, стоит учитывать вероятную частоту возникновения исключений и overhead, добавляемый выбранным способом обхода исключений.

Поясню на примере, о котором говорил: пусть есть код, который возвращает поле из таблицы по pk и в случае, если такой записи нет, возвращает null.

create table t_test(a primary key, b)
as 
select level,level from dual connect by level<=1e5;

Создадим эталонную функцию для тестов:

create or replace function f1(p in number) return number
as
  res number;
begin
  select/*+ F1 */ b into res
  from t_test t
  where t.a=p;
  return res;
exception when no_data_found then
  return null;
end;

Как я вижу, наиболее часто в таких случаях пытаются обойти механизм исключений следующими способами:

create or replace function f2(p in number) return number
as
begin
  for rec in (select/*+ F2 */ b from t_test t where t.a=p) loop
    return rec.b;
  end loop;
  return null;
end;

Кстати, в случаях если у вас в курсоре не может быть больше одной записи, то не делайте так:

create or replace function f2(p in number) return number
as
  res number;
begin
  for rec in (select/*+ F2 */ b from t_test t where t.a=p) loop
    res:=rec.b;
  end loop;
  return res;
end;

Иначе будут попытки второй итерации, что вы сможете увидеть в профайлере.

create or replace function f3(p in number) return number
as
  res number;
begin
  select/*+ F3 */ min(b) into res
  from t_test t
  where t.a=p;
  return res;
end;

И предложу свой вариант для этого:

create or replace function f4(p in number) return number
as
  res number;
begin
  select/*+ F4 */ 
    (select b from t_test t where t.a=p)
    into res
  from dual;
  return res;
end;

И теперь проведем элементарный тест выполнив эти функции по тестовой табличке:

declare 
  v       integer;
  v_start integer:= 1;
  v_end   integer:= 100000;
    l_timer integer := dbms_utility.get_time;
    procedure print(msg varchar2) is
    begin
      dbms_output.put_line(to_char((dbms_utility.get_time-l_timer)/100,'9990.00')||' '||msg);
      l_timer:=dbms_utility.get_time;
    end;
    
begin
  print('start');
  for i in v_start..v_end loop
    v:=f1(i);
  end loop;
  print('1');
  for i in v_start..v_end loop
    v:=f2(i);
  end loop;
  print('2');
  for i in v_start..v_end loop
    v:=f3(i);
  end loop;
  print('3');
  for i in v_start..v_end loop
    v:=f4(i);
  end loop;
  print('4');
end;

В результате мы получим такое соотношение:

Вариант	Время(c)
1 вариант(c exception)	3.03
2 вариант(c циклом)	3.62
3 вариант(c min)	3.34
4 вариант(скалярный подзапрос)	3.10

Как видите, в случае если исключения не вызываются, то оригинальный запрос быстрее всего! Проверим, теперь с разными процентами исключений: исключения будут для запросов с i<=0, общее кол-во вызовов будет 100001, v_start и v_end буду менять парами: (-5000,95000),(10000,90000),(-50000,50000),(-90000,10000):

declare 
  v       integer;
  v_start integer:=-50000;
  v_end   integer:= 50000;
    l_timer integer := dbms_utility.get_time;
    procedure print(msg varchar2) is
    begin
      dbms_output.put_line(to_char((dbms_utility.get_time-l_timer)/100,'9990.00')||' '||msg);
      l_timer:=dbms_utility.get_time;
    end;
    
begin
  print('start');
  for i in v_start..v_end loop
    v:=f1(i);
  end loop;
  print('1');
  for i in v_start..v_end loop
    v:=f2(i);
  end loop;
  print('2');
  for i in v_start..v_end loop
    v:=f3(i);
  end loop;
  print('3');
  for i in v_start..v_end loop
    v:=f4(i);
  end loop;
  print('4');
end;

Итоговая таблица нескольких сравнений:

Вариант	0%	~5%	~10%	~50%	~90%
1 вариант(c exception)	3.04	3.12	3.16	3.82	4.51
2 вариант(c циклом)	3.18	3.21	3.20	3.51	3.85
3 вариант(c min)	3.37	3.34	3.29	3.25	3.18
4 вариант(скалярный подзапрос)	3.12	3.06	3.03	2.98	2.94

Какие можно сделать выводы:

• Как видите, для данной таблички 5% исключений - это своего рода переломная точка, когда стандартный вариант с exception начинает проигрывать варианту с подзапросом(если чуть точнее, то это было на ~4.5%), и ~10% другим двум вариантам
• Варианты с min и циклом в целом хуже варианта с подзапросом.
• Варианты с подзапросом и min ускоряются с увеличением количества "пустых" запросов.

Забавный факт о collect

Многие знают о том, что oracle при необходимости сам создает доменные типы, например, когда используют тип объявленный в пакете(до 11g их можно было увидеть в dba_objects с именем like 'PLSQL%').

Факт 1

Точно так же он поступает и при вызове агрегатной функции collect.

-- Сначала проверим есть ли такие типы 
DB11G/XTENDER> select t.type_name,t.type_name,t.typecode 
 2 from dba_types t 
 3 where t.type_name like 'SYSTP%';

no rows selected


-- Выполним запрос с collect
DB11G/XTENDER> select collect(level) from dual connect by level<=10;

COLLECT(LEVEL)
-------------------------------------------------------------------------

SYSTPZvGjVQTySRSjYVlHXyEE2Q==(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

1 row selected.


-- Проверим снова и увидим что появился новый тип SYSTP%
DB11G/XTENDER> select t.type_name,t.type_name,t.typecode 
 2 from dba_types t 
 3 where t.type_name like 'SYSTP%';

TYPE_NAME                      TYPE_NAME                      TYPECODE
------------------------------ ------------------------------ ------------
SYSTPZvGjVQTySRSjYVlHXyEE2Q==  SYSTPZvGjVQTySRSjYVlHXyEE2Q==  COLLECTION

И еще выберем данные по нему из sys.obj$ - это нам потом пригодится:

DB11G/XTENDER> select obj#,type#,ctime,mtime,stime,status
  2  from sys.obj$ o$
  3  where o$.name = 'SYSTPZvGjVQTySRSjYVlHXyEE2Q==';

  OBJ#  TYPE# CTIME               MTIME               STIME              
------ ------ ------------------- ------------------- -------------------
103600     10 28.04.2012 01:02:35 28.04.2012 01:02:35 28.04.2012 01:02:35

Это абсолютно закономерно - нельзя вернуть клиенту информацию не описав ее.

Факт №2

Теперь зная, что тип создается, интересно что потом будет с этим типом: будет ли он удален после фетча или после отключения клиента? Например, при удалении пакетного типа автоматически дропался и доменный тип, будет ли здесь аналогичное автоматическое удаление?

Согласно Bug 4033868: COLLECT FUNCTION LEAVES TEMPORARY SYS TYPES BEHIND этот баг решен в "11.0", но я тестирую на 11.2.0.1 и элементарная проверка после дисконнекта показала, что тип продолжает существовать до рестарта инстанса, однако на самом деле даже после этого он остается, но в dba_objects уже не выводится.

Я дропну его сам, чтобы лишний раз не рестартить инстанс - это абсолютно аналогично тому как oracle "удаляет" этот тип в 11.2:

DB11G/XTENDER> drop type "SYSTPZvGjVQTySRSjYVlHXyEE2Q==";

Type dropped.

А теперь проверим:

DB11G/XTENDER> select * from dba_types
   where type_name='SYSTPZvGjVQTySRSjYVlHXyEE2Q==';

no rows selected

Вроде удалили, однако посмотрим в sys.obj$:

DB11G/XTENDER> select obj#,type#,ctime,mtime,stime,status
  2  from sys.obj$ o$
  3  where o$.name = 'SYSTPZvGjVQTySRSjYVlHXyEE2Q==';

  OBJ#  TYPE# CTIME               MTIME               STIME              
------ ------ ------------------- ------------------- -------------------
103600     10 28.04.2012 01:02:35 28.04.2012 01:40:37 31.12.4712 23:59:59

Как видите, объект остался, но с type#=10 и с stime равным последней дате 4712 года, а раньше было type#=13 и stime=mtime=ctime, а в 10.2 при ручном дропе этого типа никаких записей не оставалось. Поясню соответствия полей из sys.obj$ и dba_objects, чтобы было понятней: obj# - object_id, type# ~ код типа, ctime,mtime,stime - created, last_ddl_time, timestamp соответственно. По коду представления dba_objects мы увидим, что type# = 10 - это якобы "NON-EXISTENT" и выводить его не нужно.

and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)

А установка даты в 31.12.4712 23:59:59 указывает на его неактуальность - уж слишком далекое будущее :)

Факт №3

Теперь перейдем к тому, что меня собственно заставило повозиться с collect: ошибки с параллельным выполнением collect (обсуждение на форуме с указанием и подробным описанием Bug 11906197).

Вообще collect довольно глючная штука, я и сам на это уже неоднократно нарывался, и на металинке много различных багов связанных с collect(например, "Bug 8912282: COLLECT+UNIQUE+ORDER DOES NOT REMOVE DUPLICATES", "Bug 6145841: ORA-600[KOLOGSF2] ON CAST(COLLECT(..)) CALL","Bug 11802848: CAST/COLLECT DOES NOT WORK IN VERSION 11.2.0.2 WITH TYPE SYS.DBMS_DEBUG_VC2COLL", "Bug 6996176: SELECT COLLECT DISTINCT GROUP BY STATEMENT RETURNS DUPLICATE VALUES").

create table test_parallel parallel 8 as 
select mod(level,8) a, level b 
from dual 
connect by level<=1000;
create index IX_TEST_PARALLEL on TEST_PARALLEL (A);

DB11G/XTENDER> select/*+ PARALLEL(2)*/ cast(collect(a) as number_table) from test_parallel ;
select/*+ PARALLEL(2)*/ cast(collect(a) as number_table) from test_parallel
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-21710: argument is expecting a valid memory address of an object

Elapsed: 00:00:00.12
DB11G/XTENDER> select cast(collect(b) as number_table) from test_parallel group by a;
select cast(collect(b) as number_table) from test_parallel group by a
*
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly

Elapsed: 00:00:17.57

А вот с медленным своим агрегатом таких ошибок нет. Стандартная дилемма: либо нестабильный, но быстрый collect, либо медленный свой агрегат...

create or replace type ncollect_type as object
(
 
  data            sys.ku$_objnumset,
  
  static function ODCIAggregateInitialize
    ( sctx in out ncollect_type )
    return number ,

  member function ODCIAggregateIterate
    ( self  in out ncollect_type ,
      val   in     number
    ) return number ,
    
  member function ODCIAggregateDelete
    (  self in out  ncollect_type, 
       val  in      number
    ) return number ,
  member function ODCIAggregateTerminate
    ( self        in  ncollect_type,
      returnval   out sys.ku$_objnumset,
      flags in number
    ) return number ,
    
  member function ODCIAggregateMerge
    ( self in out ncollect_type,
      ctx2 in     ncollect_type
    ) return number
)
/
create or replace type body ncollect_type is

  static function ODCIAggregateInitialize
  ( sctx in out ncollect_type )
  return number
  is
  begin
    sctx := ncollect_type( sys.ku$_objnumset()) ;
    return ODCIConst.Success ;
  end;

  member function ODCIAggregateIterate
  ( self  in out ncollect_type ,
    val   in     number
  ) return number
  is
  begin
    self.data:=self.data multiset union sys.ku$_objnumset(val);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateDelete
  (  self in out  ncollect_type, 
     val  in      number
  ) return number
  is
  begin
    self.data:=self.data multiset except sys.ku$_objnumset(val);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate
  ( self        in  ncollect_type ,
    returnval   out sys.ku$_objnumset ,
    flags       in  number
  ) return number
  is
  begin
    returnval:=self.data;
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateMerge
  ( self in out ncollect_type ,
    ctx2 in     ncollect_type
  ) return number
  is
  begin
    self.data := self.data multiset union ctx2.data;
    return ODCIConst.Success;
  end;
end;
/

И результаты:

Вариант	Время(сек)
select/*+ NO_PARALLEL*/ cast(collect(b) as number_table) from test_parallel group by a;	0.03
select/*+ NO_PARALLEL*/ ncollect(b) from test_parallel group by a	0.08
select ncollect(b) from test_parallel group by a;	0.07
select/*+ NO_PARALLEL*/ collect(a) from test_parallel;	0.02
select/*+ NO_PARALLEL*/ ncollect(a) from test_parallel	0.18
select ncollect(a) from test_parallel;	0.19
select/*+ NO_PARALLEL*/ collect(b) from test_parallel;	0.02
select/*+ NO_PARALLEL*/ ncollect(b) from test_parallel	0.18
select ncollect(b) from test_parallel;	0.06

Минусы использования "member of" с коллекциями и вложенными таблицами

Давно хотел написать о минусах использования "a member of b" перед обычными старыми вариантами вроде a in (select column_value from table(b)).
Резюме: "member of" в sql не умеет хорошо работать с индексами и использовать его желательно только в pl/sql - в этом случае по производительности практически одинаков с собственной функцией с циклом проверки(это покажу в самом конце).

Рассмотрим два варианта использования member of:

1. запрос с условием, где поле таблицы должно входить в коллекцию
2. запрос по таблице с вложенной таблицей и условием вхождения переменной во вложенную таблицу

И в первом и втором можно создать индексы, по которым отлично можно было бы искать необходимые поля с index range/unique scan, но в случае с member of ситуация гораздо хуже:

• В первом варианте можно добиться только index fast full scan
• Во втором еще хуже: будет сначала фулл скан по родительской таблице, от нее index range scan по вложенной с access только по полю nested_table_id - полю связи родительской с вложенной

Дальше »

Небольшой и опасный workaround для работы с gtt в pipelined функции с parallel_enable

Очередная задачка показалась мне интересной: Oracle очень плохо работает с временными таблицами(gtt - global temporary tables) в параллели.

Вообще у gtt в параллели в 11g куча ограничений, а до этого вообще запрещены:
10.2: Restrictions on Temporary Tables:

Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored. Specification of the parallel_clause returns an error.

11.1: Restrictions on Parallel DML
11.2: Restrictions on Parallel DML:

Parallel UPDATE, DELETE and MERGE are not supported for temporary tables.

Обновление:
Я был категорически не прав: в примере, который я привел ранее, данные gtt доступны только в одном слейве. Вообще доступность этих данных организуется каким-то хитрым способом, например, поглядите на данный пример:
«Показать код...»

drop table gtt_tab;
drop function test_parallel;

create global temporary table gtt_tab (i integer) on commit preserve rows parallel 8;
create or replace function test_parallel(p_cur sys_refcursor) return sys.ku$_objnumpairlist
    pipelined 
    parallel_enable (partition p_cur by any) 
    is
     l_row integer;
     l_cnt integer;
     l_sid integer;
    begin
      loop
        fetch p_cur into l_row;
        exit when p_cur%notfound;
        select count(*) into l_cnt from gtt_tab;
        l_sid := sys_context('USERENV','SID');
        pipe row (SYS.ku$_ObjNumPair(l_sid,l_cnt));
      end loop;  
    end;
/
insert into gtt_tab select level from dual connect by level<=10000
/
select
  num1 sid,num2 cnt,count(*)
from
   table(
     test_parallel(cursor(select /*+ parallel(t,8) */ * from gtt_tab t))
   ) tt
group by num1,num2
/

Результат:

 >> select
  2    num1 sid,num2 cnt,count(*)
  3  from
  4     table(
  5       test_parallel(cursor(select /*+ parallel(t,8) */ * from gtt_tab t))
  6     ) tt
  7  group by num1,num2
  8  ;

       SID        CNT   COUNT(*)
---------- ---------- ----------
      4956          0       2080
      2981          0       2640
       336          0       2640
      1328          0       2640

«Ошибочный пример с видимостью только в одном слейве:»

truncate table gtt_tab;
drop function test_parallel_pipe;
drop table j_cur purge;
drop table gtt_tab purge;
drop type t_tab_row;
drop type t_row;

create or replace type t_row is object (
       i integer,
       n integer);
       
create or replace type t_tab_row is table of t_row;

create table j_cur parallel 8 as 
select level l from dual connect by level<=10000;


create global temporary table gtt_tab (i integer) on commit preserve rows parallel 8;

create or replace function test_parallel_pipe(p_cur sys_refcursor) return t_tab_row 
    pipelined 
    parallel_enable (partition p_cur by any) 
    is
     v_row integer;
     v integer;
    begin
      loop
        fetch p_cur into v_row;
        exit when p_cur%notfound;
        for rec in (select count(*) v from gtt_tab) loop
           pipe row (t_row(v_row,rec.v));
        end loop;
      end loop;  
    end;
/
insert into gtt_tab values(1);
commit;
select 
   * 
from 
   table(
      test_parallel_pipe(
         cursor(
            select /*+ parallel(t,8) */ 
               t.l 
            from j_cur t
                ,(select 1 ignore from gtt_tab) t_ignore
         )
     )
   ) tt;

Анализ PL/SQL кода в Oracle 11g (Перевод "Zoom In on Your Code" By Steven Feuerstein)

Используйте PL/Scope для анализа вашего PL/SQL кода

С момента первого релиза PL/SQL, Oracle предоставляет набор представлений позволяющих разработчику получать информацию о PL/SQL объектах. Эти представления помогают нам понимать и анализировать наш код. Oracle Database 11g предоставляет еще более мощный аналитический инструмент - PL/Scope, собирающий информацию обо всех идентификаторах в вашем PL/SQL коде, которая затем доступна через представления словаря данных. Эти представления могут помочь отследить использование каждой переменной в определении, ссылках, вызовах, а также местоположение каждого вхождения переменной в исходном коде.

Благодаря PL/Scope разработчики могут значительно лучше и легче выполнять анализ кода. Некоторые полезные приемы его использования и будут рассмотрены в данной статье.

Дальше »

Сравнение скорости функций user vs sys_context('USERENV','SESSION_USER')

Сегодня очень удивился заметив, что вызов "username:=user;" и "select user into username from dual;" одинаковы по времени выполнения, в отличие от десятикратной разницы dt:=sysdate и select sysdate into dt from dual;. Оказалось все просто, стоило лишь поглядеть трассировку или поглядеть код пакета sys.standard: PL/SQL-ный USER содержит внутри рекурсивный запрос c вызовом sql-ного user: "select user from sys.dual".
Из кода standard:

function USER return varchar2 is
  c varchar2(255);
  begin
        select user into c from sys.dual;
        return c;
  end;

  -- Bug 1287775: back to calling ICD.
  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'SELECT SYSDATE FROM DUAL;' thing.  This allows us to do the
  -- SELECT from PL/SQL rather than having to do it from C (within the ICD.)
  function sysdate return date is
    d date;
  begin
    d := pessdt;
    return d;
  exception
    when ICD_UNABLE_TO_COMPUTE then
      select sysdate into d from sys.dual;
      return d;
  end;

  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'select sys_context(...) from dual;' thing.  This allows us to do
  -- the select from PL/SQL rather than having to do it from C (within the ICD.)
  function SYS_CONTEXT(namespace varchar2, attribute varchar2)
    return varchar2 is
  c varchar2(4000);
  BEGIN
    c := pessysctx2(namespace, attribute);
    return c;
  exception
    when ICD_UNABLE_TO_COMPUTE then
      select sys_context(namespace,attribute) into c from sys.dual;
      return c;
  end;

И самое главное: user в 10 раз с лишним медленнее, чем sys_context('USERENV','SESSION_USER')!
У user, видимо, все это время съедает переключение контекста, а исключение ICD_UNABLE_TO_COMPUTE в sys_context непроисходит(как и в случае с sysdate).

Я проверял на 10.2.0.4, 10.2.0.5, 11.2.0.1, 11.2.0.3 с plsql_optimization_level=2 результаты практически одинаковы, поэтому показываю только один:

DB11G/XTENDER> declare
  2    username varchar2(30);
  3  begin
  4    for i in 1..5e5 loop
  5      username:=user;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.86
DB11G/XTENDER> declare
  2    username varchar2(30);
  3  begin
  4    for i in 1..5e5 loop
  5      username:=sys_context('USERENV','SESSION_USER');
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.02

Проверка возможности открытия двух дефолтных сессий из java в oracle

Пока разбирал-удалял сейчас свой старый тестовый код на домашней 11.2, наткнулся на проверку возможности открытия двух параллельных коннектов из внутренней java процедуры для независимых транзакций(когда-то где-то кто-то про это спрашивал) и решил запостить - вдруг кому-нибудь пригодится.
Если вкратце, то - нельзя! Все они на самом деле в одной транзакции(естественно, если не выполнять внутри них автономных).

Дальше »

"Лишние" типы

Как же я не люблю, когда создают по 100-200 одинаковых типов с различным названием, а особенно ненавижу, когда когда они еще и бестолково называются и содержат в себе другие такие же дублирующие типы... Ну к чему создавать по двести различных типов коллекций - можно ведь создать набор наиболее частых и, прежде чем создавать новый, посмотреть какие уже есть. И уж тем более когда уже есть "predefined" типы, как, например, ku$_vcnt, который часто используют в примерах.

Таких типов достаточно много, вот еще ku$_ObjNumPairList:

CREATE OR REPLACE TYPE sys.ku$_ObjNumPairList IS TABLE OF ku$_ObjNumPair

CREATE OR REPLACE TYPE sys.ku$_ObjNumPair AS OBJECT (
 num1         NUMBER,
 num2         NUMBER
)

DB11G> select *
 2 from table(
 3            select
 4              cast(
 5                    collect(ku$_objnumpair(rownum,rownum))
 6                    as ku$_objnumpairlist
 7                  )
 8            from dual
 9            connect by rownum<=10
10            );

     NUM1      NUM2
---------- ----------
         1         1
         2         2
         3         3
         4         4
         5         5
         6         6
         7         7
         8         8
         9         9
        10         10

10 rows selected.

Elapsed: 00:00:00.02

Группировка и проверка условия внутри нее до первого удовлетворяющего

На очередной коротенький quiz, натолкнул такой вопрос:

Здравствуйте!
Столкнулся с запросом вида

SELECT   ...
   MAX
   (CASE
      WHEN <большое условие>
      THEN 1
      ELSE 0
   END) AS FLAG
   ...
FROM  <большая таблица>
GROUP BY ...

В котором, как вы видите, надо просто определить, есть ли в рамках "окна группировки" хотя бы одна строка удовлетворяющая <большое условие>.
Можно ли сделать так, чтобы после нахождения первой такой строки никаких вычислений в рамках окна больше не производилось?

На самом деле можно несколькими способами, но условие должно быть действительно тяжелое и никак иначе не решаемое, чтобы получить какой-то профит.

Например, я попробую с помощью создания функции с result_cache, но вместо "relies on" + "какая-то таблица для хранения значения" буду использовать пакетную переменную(можно, конечно, и sys_context) и функцию DBMS_RESULT_CACHE.INVALIDATE.

Дальше »