XT&R blackboard

Все, что за 2013-й год накопилось неопубликованного

2014-01-15T01:02:00.001+03:00

Все не хватает времени закросспостить, поэтому просто проведу инвентаризацию :)
Список ссылок на неопубликованное из моего второго блога с короткими пояснениями:

Все о кешировании deterministic функций: http://orasql.org/category/oracle/deterministic-functions/
В этом цикле я рассказываю о том, как устроен механизм их кеширования и сравниваю с механизмом кеширования скалярных подзапросов, а также немного о том, как оптимизируются циклы в PL/SQL.
Пример контроля "direct path reads" при фул сканах с помощью хинтов или профилей с INDEX_STATS/TABLE_STATS: adaptive serial direct path reads
Про нежелательный inlist iterator по составным индексам: http://orasql.org/tag/inlist-iterator/
Различные трюки для SQL*Plus: http://orasql.org/category/oracle/sqlplus/
Просто парочка общеизвестных ошибок, но часто пропускаемых по невнимательности: a-couple-of-well-known-and-often-forgotten-things-for-plsql-developers
Как избавиться от многократного вызова функции из-за протолкнутых предикатов не изменяя кода: http://orasql.org/2013/06/10/too-many-function-executions/
Всякая всячина о внутренностях 12с: inline функции, наконец-то разрешенный lateral, extended varchars, identities и defaults: http://orasql.org/category/oracle/12c/
Трюк с переопределением объекта используемого в "чужой" вьюхе, на примере получения списка всех таблиц непривилигированным пользователем: http://orasql.org/2014/01/14/a-little-trick-with-redefining-any-object-in-a-view-from-another-schema/

Ну и напоследок минискриптик-набросочек для получения биндов из файла трассировки по заданному sql_id в xml или json, который мало кому-нибудь будет нужен, но мне пришлось наваять вчера :)

зы. Надеюсь, я когда-нибудь все это оформлю нормально...

Пара простых примеров по вчерашней дискуссии после семинара

2013-05-15T00:36:00.000+03:00

1. Пример, показывающий короткие clob'ы с "enable storage in row" будут передаваться точно так же как и обычные:

SQL> create table xt_clob(c clob) lob(c) store as securefile(enable storage in row);

Table created.

SQL> insert into xt_clob select lpad('x',100) from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> set autot trace stat
SQL> select * from xt_clob;

10 rows selected.


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         24  consistent gets
          5  physical reads
          0  redo size
       6748  bytes sent via SQL*Net to client
       8585  bytes received via SQL*Net from client
         12  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

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

SQL> drop table xt_curr purge;

Table dropped.

SQL> create table xt_curr as select level a,level b from dual connect by level<=1e4;

Table created.

SQL> set autot trace stat;
SQL> update xt_curr set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
         50  recursive calls
        130  db block gets
         52  consistent gets
         36  physical reads
     872008  redo size
        684  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> roll;
Rollback complete.
SQL> set autot off;
SQL> create or replace trigger tr_xt_curr before update on xt_curr for each row
  2  begin
  3    if :new.a !=:old.a and :new.b != :old.b then
  4      :new.b := :new.b/(:old.b - :new.b);
  5    end if;
  6  end;
  7  /

Trigger created.

SQL> set autot trace stat;
SQL> update xt_curr set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
          8  recursive calls
      20376  db block gets
         54  consistent gets
         18  physical reads
    4411636  redo size
        684  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> roll;
Rollback complete.
SQL> set autot off echo off;

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

2013-02-10T23:57:00.000+03:00

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

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

2012-10-31T02:11:00.000+03:00

Пару месяцев назад я обнаружил, что в трассировке сортировки в 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 его точно не было.

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

Включаем трассировку с 10-м уровнем:

alter session set events '10032 trace name context forever, level 10';

Кусок из трейс файла:

soropn: opened (new) sort, sordef 0x7fc4679e2550, flags 0x802
        maxkey 25, nflds 12, nkflds 1

*** 2012-10-30 23:14:34.678
----- Current SQL Statement for this session (sql_id=3ktacv9r56b51) -----
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-opifch2<-opifch<-opiodr<-rpidrus<-skgmstack<-rpiswu2<-rpidrv<-rpifch<-kqllod<-kglobld<-kglobpn<-kglpim<-kglpin<-kglgob<-kgldpo0<-qcdlgpo<-qcsRslvPLSQLInvoc1<-qcsRslvPLSQLInvoc<-qcsRslvName<-qcsridn<-qcsraic<-qcspqbDescendents
<-qcspqb<-kkmdrv<-opiSem<-opiDeferredSem<-opitca<-kksFullTypeCheck<-rpiswu2<-kksLoadChild<-kxsGetRuntimeLock<-kksfbc<-kkspsc0<-kksParseCursor<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-__libc_start_main
<-_start        End of abridged call stack trace.

*** 2012-10-30 23:14:35.328
soreod: sorp 0x7fc4679e2550
---- Sort Parameters ------------------------------
sort_area_size                    65536
sort_area_retained_size           65536
sort_multiblock_read_count        1
max intermediate merge width      3

Английская версия блога - orasql.org

2012-10-15T21:04:00.001+03:00

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

1. Удобная трассировка:
Очень удобно создать отдельный сервис и включить на нем трассировку: теперь чтобы оттрассировать приложение или обращения с дблинка достаточно подключиться к на нужный сервис. Код доступен по ссылке

2. Rollback вместо commit'a на дблинке:
При коммите после select * from tab@dblink на самом деле коммит будет только на локальном инстансе, а на самом дблинке будет роллбэк. При этом они оба будут "read-only". Кроме того, если после коммита/роллбэка мы будем еще вызывать коммиты/роллбэки на ремоут не будет ни коммита, ни роллбэка, однако, при отключении от локального инстансе(то есть просто при дисконнекте) на ремоут придет read-only commit. А как увидеть и каким же образом определяется, что будет коммит или роллбэк на ремоуте, непонятно...

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

2012-09-21T01:58:00.000+03:00

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

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

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

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

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

Пусть есть табличка с индексом на поле а:

create table xt_test(a not null,b not null,c)
as
select 
    length(object_name)
   ,nvl(object_id,0)
   ,o.OBJECT_NAME 
from dba_objects o;
create index ix_test_a on xt_test(a);
DB11G/XTENDER> select i.index_name
  2        ,i.distinct_keys,i.num_rows
  3        ,i.blevel,i.leaf_blocks
  4        ,i.avg_leaf_blocks_per_key,i.avg_data_blocks_per_key
  5  from user_indexes i where i.table_name='XT_TEST';

INDEX_NAME  DISTINCT_KEYS  NUM_ROWS   BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
----------- ------------- --------- -------- ----------- ----------------------- -----------------------
IX_TEST_A              30     69230        1         135                       4                     191

1 row selected.

drop table xt_test purge;
create table xt_test(a not null,b not null,c)
as
select 
    length(object_name)
   ,nvl(object_id,0)
   ,o.OBJECT_NAME 
from dba_objects o 
;
create index ix_test_a on xt_test(a);
begin
  dbms_stats.gather_table_stats( ''
                                ,'XT_TEST'
                                ,estimate_percent=>100
                                ,cascade=>true
                                ,method_opt => 'for all indexed columns size auto'
                               ); 
end;
/

select i.index_name
      ,i.distinct_keys,i.num_rows
      ,i.blevel,i.leaf_blocks
      ,i.avg_leaf_blocks_per_key,i.avg_data_blocks_per_key 
from user_indexes i where i.table_name='XT_TEST';

Распределение значений у этого поля очень неравномерное:

A	COUNT(*)
1	11
2	20
3	59
4	92
5	178
6	251
7	521
9	570
10	636
8	640
11	962
12	970
13	1151
15	1363
14	1544
16	1692
18	2021
17	2023
19	2550
20	2606
21	3050
22	3171
23	3395
24	3472
29	3527
27	3596
26	3698
28	4130
25	4268
30	17063
Всего	69230

Стандартный запрос c distinct очень неудачен - в индексе всего 30 distinct_keys, а прочитать придется 135 блоков!
С IFS:

DB11G/XTENDER> select/*+ INDEX(xt_test) */ distinct a from xt_test;

30 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3405466263

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    30 |    90 |   140   (3)| 00:00:02 |
|   1 |  SORT UNIQUE NOSORT|           |    30 |    90 |   140   (3)| 00:00:02 |
|   2 |   INDEX FULL SCAN  | IX_TEST_A | 69230 |   202K|   137   (1)| 00:00:02 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        138  consistent gets
          0  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         30  rows processed

C IFFS:

DB11G/XTENDER> select distinct a from xt_test;

30 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 4206828362

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |    30 |    90 |    42  (10)| 00:00:01 |
|   1 |  HASH UNIQUE          |           |    30 |    90 |    42  (10)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 69230 |   202K|    38   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        143  consistent gets
          0  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         30  rows processed

А ведь можно же было бы идти по дереву заходя только в нужные блоки, а не по всем листовым блокам! Однако, Oracle сам по себе такое не осилит и тут как раз придется его выкрутиться: у Oracle помимо IFS(min/max) есть и IRS(min/max) хорошо работающий с диапазонами и границами, и с помощью рекурсивного запроса, можно заставить его читать только нужное!

DB11G/XTENDER> with t_unique( a ) as (
  2                select min(t1.a)
  3                from xt_test t1
  4                union all
  5                select (select min(t1.a) from xt_test t1 where t1.a>t.a)
  6                from t_unique t
  7                where a is not null
  8  )
  9  select * from t_unique where a is not null;

30 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2791305641

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |           |     2 |    26 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                                     |           |     2 |    26 |     4   (0)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|           |       |       |            |          |
|   3 |    SORT AGGREGATE                         |           |     1 |     3 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)             | IX_TEST_A |     1 |     3 |     2   (0)| 00:00:01 |
|   5 |    SORT AGGREGATE                         |           |     1 |     3 |            |          |
|   6 |     FIRST ROW                             |           |     1 |     3 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN (MIN/MAX)           | IX_TEST_A |     1 |     3 |     2   (0)| 00:00:01 |
|*  8 |    RECURSIVE WITH PUMP                    |           |       |       |            |          |
-------------------------------------------------------------------------------------------------------

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

   1 - filter("A" IS NOT NULL)
   7 - access("T1"."A">:B1)
   8 - filter("A" IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         36  consistent gets
          0  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         32  sorts (memory)
          0  sorts (disk)
         30  rows processed

Разница очевидна: 36 consistent gets на 30 значений, вместо 135. И это очень маленькая табличка, а на миллионах и миллиардах записей разница будет еще более огромной!
Поясню алгоритм:

В первой части union all (3-4 строки плана) мы указываем с чего начать рекурсию, а конкретно выбираем минимальное(первое) значение из индекса.
Затем с помощью IRS(min/max) (7-6-5 строки плана) выбираем первое значение большее выбранного на предыдущем этапе
Повторяем рекурсию пока что-то находим

Переходим к следующему:

2. Топ N записей для каждого значению ключа

Теперь вооруженные легким получением каждого начального значения, мы легко можем получить Топ для каждого из них, но для этого придется воспользоваться либо недокументированным Lateral() с включением соответствующего ивента, либо более простым и стандартным table(). Проблема остается только в том, что воспользоваться инлайн вью с row_number/rownum не получится, т.к. предикат с верхнего уровня не просунется, и придется воспользоваться простым ограничением по count stop key(по rownum) c обязательным доступом по IRS descending (order by там в общем лишний, но дополнительно уменьшает стоимость чтения IRS descending который нам и нужен для неявной сортировки) c подсказкой index_desc, чтобы прибить намертво, иначе сортировка может слететь:

DB11G/XTENDER> with t_unique( a ) as (
  2                select min(t1.a)
  3                from xt_test t1
  4                union all
  5                select (select min(t1.a) from xt_test t1 where t1.a>t.a)
  6                from t_unique t
  7                where a is not null
  8  )
  9  select/*+ use_nl(rids tt) */ *
 10  from t_unique v
 11      ,table(
 12            cast(
 13                 multiset(
 14                          select/*+ index_desc(tt ix_xt_test_ab) */ tt.rowid rid
 15                          from xt_test tt
 16                          where tt.a=v.a
 17                            and rownum<=5
 18                          order by tt.b desc
 19                         )
 20                 as sys.odcivarchar2list
 21                )
 22            ) rids
 23      ,xt_test tt
 24  where tt.rowid=rids.column_value
 25  order by tt.a,tt.b desc;

150 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4085270117

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |    11M|   506M|       |   149K  (1)| 00:29:54 |
|   1 |  SORT ORDER BY                               |               |    11M|   506M|   649M|   149K  (1)| 00:29:54 |
|   2 |   NESTED LOOPS                               |               |    11M|   506M|       | 16402   (1)| 00:03:17 |
|   3 |    NESTED LOOPS                              |               | 16336 |   239K|       |    60   (0)| 00:00:01 |
|   4 |     VIEW                                     |               |     2 |    26 |       |     4   (0)| 00:00:01 |
|   5 |      UNION ALL (RECURSIVE WITH) BREADTH FIRST|               |       |       |       |         |             |
|   6 |       SORT AGGREGATE                         |               |     1 |     3 |       |         |             |
|   7 |        INDEX FULL SCAN (MIN/MAX)             | IX_TEST_A     |     1 |     3 |       |     2   (0)| 00:00:01 |
|   8 |       SORT AGGREGATE                         |               |     1 |     3 |       |         |             |
|   9 |        FIRST ROW                             |               |     1 |     3 |       |     2   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN (MIN/MAX)           | IX_TEST_A     |     1 |     3 |       |     2   (0)| 00:00:01 |
|* 11 |       RECURSIVE WITH PUMP                    |               |       |       |       |         |             |
|  12 |     COLLECTION ITERATOR SUBQUERY FETCH       |               |  8168 | 16336 |       |    28   (0)| 00:00:01 |
|* 13 |      COUNT STOPKEY                           |               |       |       |       |         |             |
|* 14 |       INDEX RANGE SCAN DESCENDING            | IX_XT_TEST_AB |  2308 | 64624 |       |     8   (0)| 00:00:01 |
|* 15 |    TABLE ACCESS BY USER ROWID                | XT_TEST       |   692 | 22144 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

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

  10 - access("T1"."A">:B1)
  11 - filter("A" IS NOT NULL)
  13 - filter(ROWNUM<=5)
  14 - access("TT"."A"=:B1)
  15 - access(CHARTOROWID(VALUE(KOKBF$)))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        166  consistent gets
          0  physical reads
          0  redo size
       7523  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
        150  rows processed

Аналогично можно через lateral:

alter session set events '22829 trace name context forever';
with t_unique( a ) as (
              select min(t1.a)
              from xt_test t1
              union all
              select (select min(t1.a) from xt_test t1 where t1.a>t.a)
              from t_unique t
              where a is not null
)
select/*+ use_nl(rids tt) */ *
from t_unique v
    ,lateral(
              select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
              from xt_test tt
              where tt.a=v.a
                and rownum<=5
              order by tt.a, b desc
     ) r
order by r.a,r.b desc

В целом, конечно, можно было обойтись и без опасной сортировки, а взять и воспользоваться xmltable вместо table с передачей параметра сразу во внутренний подзапрос, но это чуток потяжелее чем обычный table.

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

2012-09-14T01:13:00.001+03:00

Сегодня нужно было ускорить большую разовую выгрузку(вообще был "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.

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

2012-08-23T22:20:00.000+03:00

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

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

2012-08-11T00:42:00.000+03:00

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

2012-08-10T23:41:00.000+03:00

Все, конечно, знают про 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 с вычисляемой заменяемой частью

2012-08-08T23:42:00.000+03:00

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

When oracle invalidates result_cache function results without any changes in objects on which depends

2012-07-30T23:25:00.001+03:00

On our production servers we have simple function with result_cache, like this:

create or replace function f_rc(p_id number) return number result_cache
is
  ret number;
begin
  select t.val into ret from rc_table t where t.id=p_id;
  return ret;
exception 
  when no_data_found then 
     return null;
end;
/

And its results frequently invalidates without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:
1. "select for update" from this table with commit;
2. deletion of unrelated rows from parent table if there is unindexed foreign key with "on delete cascade".
I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases for this i will show below.
But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with "select for update", it still does not stop invalidating.
In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and "commits"?

drop function f_rc;
drop table rc_table purge;
drop table rc_parent purge;


create table rc_parent(id primary key, val) as
select level,level from dual connect by level<=10;

create table rc_table(id primary key,parent,val) as 
select level,level,level from dual connect by level<=10;

create index ix_rc_table_parent on rc_table(parent);
alter table rc_table add constraint fk_parent foreign key(parent) references rc_parent(id);

create or replace function f_rc(p_id number) return number result_cache
is
  ret number;
begin
  select t.val into ret from rc_table t where t.id=p_id;
  dbms_output.put_line('fired');
  return ret;
exception when no_data_found then return null;
end;
/

-- first execution:
DB11G/XTENDER> select f_rc(1) from dual;

   F_RC(1)
----------
         1

1 row selected.

fired
-- checking result_cache 
DB11G/XTENDER> /

   F_RC(1)
----------
         1

1 row selected.
-- execute in another session: select * from rc_table for update; 
-- and check again:
DB11G/XTENDER> /

   F_RC(1)
----------
         1

1 row selected.
-- result valid:
DB11G/XTENDER> col name form a50
DB11G/XTENDER> select id,type,status,name,namespace,creation_timestamp,scn from v$result_cache_objects;

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40643967
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967
         1 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:31:35   40643967

3 rows selected.
DB11G/XTENDER> save rc_objects
Created file rc_objects.sql
-- rollback in another session after "select for update" and check again:
-- result still valid:
DB11G/XTENDER> /

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40643967
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967
         1 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:31:35   40643967

3 rows selected.
DB11G/XTENDER> select f_rc(1) from dual;

   F_RC(1)
----------
         1

1 row selected.

DB11G/XTENDER> save test replace
Wrote file test.sql
-- again "select * from rc_table for update;" in another session but now with "commit;"
-- check again:
DB11G/XTENDER> @rc_objects


        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644109
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967
         1 Result     Invalid   "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:31:35   40643967

3 rows selected.
-- scn changed! and result is invalid now!

-- getting new result
DB11G/XTENDER> @test

   F_RC(1)
----------
         1

1 row selected.

fired
-- execute in another session: insert into rc_parent values(-1,-1); 
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644109            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            1          0
         3 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          0

-- new result is valid
-- execute in another session: rollback; 
DB11G/XTENDER> @test

   F_RC(1)
----------
         1

DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644109            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            1          0
         3 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          1

-- again insert in another session, but now with commit: 
--    insert into rc_parent values(-1,-1); 
--    commit;
-- still valid:
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644109            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            1          0
         3 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          1

-- in another session: delete from rc_parent where id=-1; rollback;
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644109            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            1          0
         3 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          2

-- in another session: delete from rc_parent where id=-1; commit;
-- still valid:
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644109            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            1          0
         3 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          2

-- now without index:
DB11G/XTENDER> drop index ix_rc_table_parent;

Index dropped.

DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644745            0          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            0          0
         3 Result     Invalid   "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          3

4 rows selected.

DB11G/XTENDER> @test

   F_RC(1)
----------
         1

1 row selected.

fired
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644745            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            1          0
         4 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:51:27   40644759            2          0
         3 Result     Invalid   "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          3
-- again insert with commit in another session
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644745            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            1          0
         4 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:51:27   40644759            2          0
         3 Result     Invalid   "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          3
-- again delete with commit:
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:31:35   40644745            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:31:35   40643967            1          0
         4 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:51:27   40644759            2          0
         3 Result     Invalid   "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:41:53   40644522            2          3
-- now with "on delete cascade":
DB11G/XTENDER> alter table rc_table drop constraint fk_parent;

Table altered.
DB11G/XTENDER> alter table rc_table add constraint fk_parent foreign key(parent) references rc_parent(id) on delete cascade;

Table altered.
DB11G/XTENDER> exec dbms_result_cache.Flush;

PL/SQL procedure successfully completed.
DB11G/XTENDER> @test

   F_RC(1)
----------
         1

1 row selected.

fired
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:58:53   40645010            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:58:53   40645010            1          0
         1 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:58:53   40645010            2          0
-- again in another session: insert into rc_parent values(-1,-1);commit;delete from rc_parent where id=-1;commit;
-- checking:
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 01:58:53   40645202            0          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 01:58:53   40645010            0          0
         1 Result     Invalid   "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 01:58:53   40645010            2          0
-- Result is invalid now!
DB11G/XTENDER> -- now with index:
DB11G/XTENDER> create index ix_rc_table_parent on rc_table(parent);

Index created.

DB11G/XTENDER> exec dbms_result_cache.Flush;

PL/SQL procedure successfully completed.

DB11G/XTENDER> @test

   F_RC(1)
----------
         1

1 row selected.

fired
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 02:23:13   40654418            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 02:23:13   40654418            1          0
         1 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 02:23:13   40654418            2          0

3 rows selected.
-- again in another session: insert into rc_parent values(-1,-1);commit;delete from rc_parent where id=-1;commit;
DB11G/XTENDER> @rc_objects

        ID TYPE       STATUS    NAME                                               NAMES CREATION_TIMESTAMP     SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
         2 Dependency Published XTENDER.RC_TABLE                                         29.07.2012 02:23:13   40654418            1          0
         0 Dependency Published XTENDER.F_RC                                             29.07.2012 02:23:13   40654418            1          0
         1 Result     Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1     PLSQL 29.07.2012 02:23:13   40654418            2          0

3 rows selected.
-- Result still is valid.

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

2012-07-21T00:48:00.000+03:00

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

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 фактически изменяет одно и то же, то "лишнее" перезаписывается.

Ура! Сын родился!

2012-07-14T00:37:00.002+03:00

Теперь у нас полная семья - и доча и сын :)
Уррррррррраааааа! :)

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

2012-06-30T01:03:00.000+03:00

Проблема эта в общем-то старая, но только сейчас благодаря книге "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.

А эта fixed таблица и есть в v$lock, это я многократно видел в планах c ней:

DB11G/XTENDER> explain plan for select * from v$lock;

Explained.

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

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

Plan hash value: 3074737110

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |   131 |     1 (100)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     1 |   131 |     1 (100)| 00:00:01 |
|*  2 |   HASH JOIN              |                 |     1 |    98 |     1 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FULL      | X$KSUSE         |     1 |    30 |     0   (0)| 00:00:01 |
|   4 |    VIEW                  | GV$_LOCK        |    10 |   680 |     0   (0)| 00:00:01 |
|   5 |     UNION-ALL            |                 |       |       |            |          |
|*  6 |      FILTER              |                 |       |       |            |          |
|   7 |       VIEW               | GV$_LOCK1       |     2 |   136 |     0   (0)| 00:00:01 |
|   8 |        UNION-ALL         |                 |       |       |            |          |
|*  9 |         FIXED TABLE FULL | X$KDNSSF        |     1 |    94 |     0   (0)| 00:00:01 |
|* 10 |         FIXED TABLE FULL | X$KSQEQ         |     1 |    94 |     0   (0)| 00:00:01 |
|* 11 |      FIXED TABLE FULL    | X$KTADM         |     1 |    94 |     0   (0)| 00:00:01 |
|* 12 |      FIXED TABLE FULL    | X$KTATRFIL      |     1 |    94 |     0   (0)| 00:00:01 |
|* 13 |      FIXED TABLE FULL    | X$KTATRFSL      |     1 |    94 |     0   (0)| 00:00:01 |
|* 14 |      FIXED TABLE FULL    | X$KTATL         |     1 |    94 |     0   (0)| 00:00:01 |
|* 15 |      FIXED TABLE FULL    | X$KTSTUSC       |     1 |    94 |     0   (0)| 00:00:01 |
|* 16 |      FIXED TABLE FULL    | X$KTSTUSS       |     1 |    94 |     0   (0)| 00:00:01 |
|* 17 |      FIXED TABLE FULL    | X$KTSTUSG       |     1 |    94 |     0   (0)| 00:00:01 |
|* 18 |      FIXED TABLE FULL    | X$KTCXB         |     1 |    94 |     0   (0)| 00:00:01 |
|* 19 |   FIXED TABLE FIXED INDEX| X$KSQRS (ind:1) |     1 |    33 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Теперь уже зная корень проблемы, остается только узнать можно ли избавиться от лишних обращений к X$KTADM, что интуитивно кажется возможным учитывая, что она часть "union all".
То есть надо было "разобрать" этот v$lock, чтобы получить полный текст запроса. Для этого я сначала получил текст запроса через трассировку 10053:

SELECT "S"."INST_ID" "INST_ID",
       "L"."LADDR" "ADDR",
       "L"."KADDR" "KADDR",
       "S"."KSUSENUM" "SID",
       "R"."KSQRSIDT" "TYPE",
       "R"."KSQRSID1" "ID1",
       "R"."KSQRSID2" "ID2",
       "L"."LMODE" "LMODE",
       "L"."REQUEST" "REQUEST",
       "L"."CTIME" "CTIME",
       DECODE("L"."LMODE", 0, 0, "L"."BLOCK") "BLOCK"
  FROM (SELECT "GV$_LOCK"."LADDR"   "LADDR",
               "GV$_LOCK"."KADDR"   "KADDR",
               "GV$_LOCK"."SADDR"   "SADDR",
               "GV$_LOCK"."RADDR"   "RADDR",
               "GV$_LOCK"."LMODE"   "LMODE",
               "GV$_LOCK"."REQUEST" "REQUEST",
               "GV$_LOCK"."CTIME"   "CTIME",
               "GV$_LOCK"."BLOCK"   "BLOCK"
          FROM ( (SELECT USERENV('INSTANCE') "INST_ID",
                        "V$_LOCK1"."LADDR" "LADDR",
                        "V$_LOCK1"."KADDR" "KADDR",
                        "V$_LOCK1"."SADDR" "SADDR",
                        "V$_LOCK1"."RADDR" "RADDR",
                        "V$_LOCK1"."LMODE" "LMODE",
                        "V$_LOCK1"."REQUEST" "REQUEST",
                        "V$_LOCK1"."CTIME" "CTIME",
                        "V$_LOCK1"."BLOCK" "BLOCK"
                   FROM (SELECT "GV$_LOCK1"."LADDR"   "LADDR",
                                "GV$_LOCK1"."KADDR"   "KADDR",
                                "GV$_LOCK1"."SADDR"   "SADDR",
                                "GV$_LOCK1"."RADDR"   "RADDR",
                                "GV$_LOCK1"."LMODE"   "LMODE",
                                "GV$_LOCK1"."REQUEST" "REQUEST",
                                "GV$_LOCK1"."CTIME"   "CTIME",
                                "GV$_LOCK1"."BLOCK"   "BLOCK"
                           FROM ((SELECT "X$KDNSSF"."INST_ID"   "INST_ID",
                                         "X$KDNSSF"."ADDR"      "LADDR",
                                         "X$KDNSSF"."KSQLKADR"  "KADDR",
                                         "X$KDNSSF"."KSQLKSES"  "SADDR",
                                         "X$KDNSSF"."KSQLKRES"  "RADDR",
                                         "X$KDNSSF"."KSQLKMOD"  "LMODE",
                                         "X$KDNSSF"."KSQLKREQ"  "REQUEST",
                                         "X$KDNSSF"."KSQLKCTIM" "CTIME",
                                         "X$KDNSSF"."KSQLKLBLK" "BLOCK"
                                    FROM SYS."X$KDNSSF" "X$KDNSSF"
                                   WHERE BITAND("X$KDNSSF"."KSSOBFLG", 1) <> 0
                                     AND ("X$KDNSSF"."KSQLKMOD" <> 0 OR
                                          "X$KDNSSF"."KSQLKREQ" <> 0)) 
                                 UNION ALL
                                 (SELECT "X$KSQEQ"."INST_ID"   "INST_ID",
                                         "X$KSQEQ"."ADDR"      "LADDR",
                                         "X$KSQEQ"."KSQLKADR"  "KADDR",
                                         "X$KSQEQ"."KSQLKSES"  "SADDR",
                                         "X$KSQEQ"."KSQLKRES"  "RADDR",
                                         "X$KSQEQ"."KSQLKMOD"  "LMODE",
                                         "X$KSQEQ"."KSQLKREQ"  "REQUEST",
                                         "X$KSQEQ"."KSQLKCTIM" "CTIME",
                                         "X$KSQEQ"."KSQLKLBLK" "BLOCK"
                                    FROM SYS."X$KSQEQ" "X$KSQEQ"
                                   WHERE BITAND("X$KSQEQ"."KSSOBFLG", 1) <> 0
                                     AND ("X$KSQEQ"."KSQLKMOD" <> 0 OR
                                          "X$KSQEQ"."KSQLKREQ" <> 0))
                                ) "GV$_LOCK1"
                          WHERE "GV$_LOCK1"."INST_ID" = USERENV('INSTANCE')
                         ) "V$_LOCK1"
                  )
                UNION ALL 
                  (SELECT "X$KTADM"."INST_ID"   "INST_ID",
                          "X$KTADM"."ADDR"      "LADDR",
                          "X$KTADM"."KSQLKADR"  "KADDR",
                          "X$KTADM"."KSQLKSES"  "SADDR",
                          "X$KTADM"."KSQLKRES"  "RADDR",
                          "X$KTADM"."KSQLKMOD"  "LMODE",
                          "X$KTADM"."KSQLKREQ"  "REQUEST",
                          "X$KTADM"."KSQLKCTIM" "CTIME",
                          "X$KTADM"."KSQLKLBLK" "BLOCK"
                     FROM SYS."X$KTADM" "X$KTADM" /*** 1 ***/
                    WHERE BITAND("X$KTADM"."KSSOBFLG", 1) <> 0
                      AND ("X$KTADM"."KSQLKMOD" <> 0 OR
                           "X$KTADM"."KSQLKREQ" <> 0)
                  ) 
                UNION ALL
                  (SELECT "X$KTATRFIL"."INST_ID"   "INST_ID",
                          "X$KTATRFIL"."ADDR"      "LADDR",
                          "X$KTATRFIL"."KSQLKADR"  "KADDR",
                          "X$KTATRFIL"."KSQLKSES"  "SADDR",
                          "X$KTATRFIL"."KSQLKRES"  "RADDR",
                          "X$KTATRFIL"."KSQLKMOD"  "LMODE",
                          "X$KTATRFIL"."KSQLKREQ"  "REQUEST",
                          "X$KTATRFIL"."KSQLKCTIM" "CTIME",
                          "X$KTATRFIL"."KSQLKLBLK" "BLOCK"
                     FROM SYS."X$KTATRFIL" "X$KTATRFIL"
                    WHERE BITAND("X$KTATRFIL"."KSSOBFLG", 1) <> 0
                      AND ("X$KTATRFIL"."KSQLKMOD" <> 0 OR
                           "X$KTATRFIL"."KSQLKREQ" <> 0)
                  ) 
                UNION ALL
                (SELECT "X$KTATRFSL"."INST_ID"   "INST_ID",
                        "X$KTATRFSL"."ADDR"      "LADDR",
                        "X$KTATRFSL"."KSQLKADR"  "KADDR",
                        "X$KTATRFSL"."KSQLKSES"  "SADDR",
                        "X$KTATRFSL"."KSQLKRES"  "RADDR",
                        "X$KTATRFSL"."KSQLKMOD"  "LMODE",
                        "X$KTATRFSL"."KSQLKREQ"  "REQUEST",
                        "X$KTATRFSL"."KSQLKCTIM" "CTIME",
                        "X$KTATRFSL"."KSQLKLBLK" "BLOCK"
                   FROM SYS."X$KTATRFSL" "X$KTATRFSL"
                  WHERE BITAND("X$KTATRFSL"."KSSOBFLG", 1) <> 0
                    AND ("X$KTATRFSL"."KSQLKMOD" <> 0 OR
                         "X$KTATRFSL"."KSQLKREQ" <> 0)) 
                UNION ALL
                (SELECT "X$KTATL"."INST_ID"   "INST_ID",
                        "X$KTATL"."ADDR"      "LADDR",
                        "X$KTATL"."KSQLKADR"  "KADDR",
                        "X$KTATL"."KSQLKSES"  "SADDR",
                        "X$KTATL"."KSQLKRES"  "RADDR",
                        "X$KTATL"."KSQLKMOD"  "LMODE",
                        "X$KTATL"."KSQLKREQ"  "REQUEST",
                        "X$KTATL"."KSQLKCTIM" "CTIME",
                        "X$KTATL"."KSQLKLBLK" "BLOCK"
                   FROM SYS."X$KTATL" "X$KTATL"
                  WHERE BITAND("X$KTATL"."KSSOBFLG", 1) <> 0
                    AND ("X$KTATL"."KSQLKMOD" <> 0 OR
                         "X$KTATL"."KSQLKREQ" <> 0)) 
                UNION ALL
                (SELECT "X$KTSTUSC"."INST_ID"   "INST_ID",
                        "X$KTSTUSC"."ADDR"      "LADDR",
                        "X$KTSTUSC"."KSQLKADR"  "KADDR",
                        "X$KTSTUSC"."KSQLKSES"  "SADDR",
                        "X$KTSTUSC"."KSQLKRES"  "RADDR",
                        "X$KTSTUSC"."KSQLKMOD"  "LMODE",
                        "X$KTSTUSC"."KSQLKREQ"  "REQUEST",
                        "X$KTSTUSC"."KSQLKCTIM" "CTIME",
                        "X$KTSTUSC"."KSQLKLBLK" "BLOCK"
                   FROM SYS."X$KTSTUSC" "X$KTSTUSC"
                  WHERE BITAND("X$KTSTUSC"."KSSOBFLG", 1) <> 0
                    AND ("X$KTSTUSC"."KSQLKMOD" <> 0 OR
                         "X$KTSTUSC"."KSQLKREQ" <> 0)) 
                UNION ALL
                (SELECT "X$KTSTUSS"."INST_ID"   "INST_ID",
                        "X$KTSTUSS"."ADDR"      "LADDR",
                        "X$KTSTUSS"."KSQLKADR"  "KADDR",
                        "X$KTSTUSS"."KSQLKSES"  "SADDR",
                        "X$KTSTUSS"."KSQLKRES"  "RADDR",
                        "X$KTSTUSS"."KSQLKMOD"  "LMODE",
                        "X$KTSTUSS"."KSQLKREQ"  "REQUEST",
                        "X$KTSTUSS"."KSQLKCTIM" "CTIME",
                        "X$KTSTUSS"."KSQLKLBLK" "BLOCK"
                   FROM SYS."X$KTSTUSS" "X$KTSTUSS"
                  WHERE BITAND("X$KTSTUSS"."KSSOBFLG", 1) <> 0
                    AND ("X$KTSTUSS"."KSQLKMOD" <> 0 OR
                         "X$KTSTUSS"."KSQLKREQ" <> 0)) 
                UNION ALL
                (SELECT "X$KTSTUSG"."INST_ID"   "INST_ID",
                        "X$KTSTUSG"."ADDR"      "LADDR",
                        "X$KTSTUSG"."KSQLKADR"  "KADDR",
                        "X$KTSTUSG"."KSQLKSES"  "SADDR",
                        "X$KTSTUSG"."KSQLKRES"  "RADDR",
                        "X$KTSTUSG"."KSQLKMOD"  "LMODE",
                        "X$KTSTUSG"."KSQLKREQ"  "REQUEST",
                        "X$KTSTUSG"."KSQLKCTIM" "CTIME",
                        "X$KTSTUSG"."KSQLKLBLK" "BLOCK"
                   FROM SYS."X$KTSTUSG" "X$KTSTUSG"
                  WHERE BITAND("X$KTSTUSG"."KSSOBFLG", 1) <> 0
                    AND ("X$KTSTUSG"."KSQLKMOD" <> 0 OR
                         "X$KTSTUSG"."KSQLKREQ" <> 0)) 
                UNION ALL
                (SELECT "X$KTCXB"."INST_ID"   "INST_ID",
                        "X$KTCXB"."KTCXBXBA"  "LADDR",
                        "X$KTCXB"."KTCXBLKP"  "KADDR",
                        "X$KTCXB"."KSQLKSES"  "SADDR",
                        "X$KTCXB"."KSQLKRES"  "RADDR",
                        "X$KTCXB"."KSQLKMOD"  "LMODE",
                        "X$KTCXB"."KSQLKREQ"  "REQUEST",
                        "X$KTCXB"."KSQLKCTIM" "CTIME",
                        "X$KTCXB"."KSQLKLBLK" "BLOCK"
                   FROM SYS."X$KTCXB" "X$KTCXB"
                  WHERE BITAND("X$KTCXB"."KSSPAFLG", 1) <> 0
                    AND ("X$KTCXB"."KSQLKMOD" <> 0 OR
                         "X$KTCXB"."KSQLKREQ" <> 0))) "GV$_LOCK"
         WHERE "GV$_LOCK"."INST_ID" = USERENV('INSTANCE')
      ) "L",
       SYS."X$KSUSE" "S",
       SYS."X$KSQRS" "R"
 WHERE "L"."SADDR" = "S"."ADDR"
   AND "L"."RADDR" = "R"."ADDR"

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

/-------------------------- V$LOCK
select  ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK 
from GV$LOCK 
where inst_id = USERENV('Instance')
/-------------------------- GV$LOCK
select s.inst_id                             INST_ID
      ,l.laddr                               ADDR   
      ,l.kaddr                               KADDR
      ,s.ksusenum                            SID
      ,r.ksqrsidt                            TYPE
      ,r.ksqrsid1                            ID1
      ,r.ksqrsid2                            ID2
      ,l.lmode                               LMODE
      ,l.request                             REQUEST
      ,l.ctime                               CTIME
      ,decode(l.lmode, 0, 0, l.block)        BLOCK
from v$_lock l, x$ksuse s, x$ksqrs r 
where l.saddr=s.addr and l.raddr=r.addr
/--------------------      V$_LOCK  -----------------------------------------
select  LADDR , KADDR , SADDR , RADDR , LMODE , REQUEST , CTIME , BLOCK 
from GV$_LOCK 
where inst_id = USERENV('Instance')
/--------------------      GV$_LOCK  -----------------------------------------
select USERENV('Instance') inst_id,laddr,kaddr,saddr,raddr,lmode,request,ctime,  block 
from v$_lock1 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktadm /**** 1 *****/
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktatrfil 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktatrfsl 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktatl 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktstusc 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktstuss 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktstusg 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,ktcxbxba,ktcxblkp,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktcxb 
where bitand(ksspaflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
/------------------ V$_LOCK1
select  LADDR , KADDR , SADDR , RADDR , LMODE , REQUEST , CTIME , BLOCK 
from GV$_LOCK1 
where inst_id = USERENV('Instance')
/------------------ GV$_LOCK1
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim, ksqlklblk 
from x$kdnssf 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim, ksqlklblk 
from x$ksqeq 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)

/-------------------------- V$LOCK
select  ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK 
from GV$LOCK 
where inst_id = USERENV('Instance')
/-------------------------- GV$LOCK
select s.inst_id                             INST_ID  
      ,l.laddr                               ADDR    
      ,l.kaddr                               KADDR
      ,s.ksusenum                            SID
      ,r.ksqrsidt                            TYPE
      ,r.ksqrsid1                            ID1
      ,r.ksqrsid2                            ID2
      ,l.lmode                               LMODE
      ,l.request                             REQUEST                      
      ,l.ctime                               CTIME
      ,decode(l.lmode, 0, 0, l.block)        BLOCK
from v$_lock l, x$ksuse s, x$ksqrs r  
where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr)
/--------------------      V$_LOCK  -----------------------------------------
select  LADDR , KADDR , SADDR , RADDR , LMODE , REQUEST , CTIME , BLOCK 
from GV$_LOCK 
where inst_id = USERENV('Instance')
/--------------------      GV$_LOCK  -----------------------------------------
select USERENV('Instance') inst_id,laddr,kaddr,saddr,raddr,lmode,request,ctime,  block 
from v$_lock1 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktadm 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktatrfil 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktatrfsl 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktatl 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktstusc 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktstuss 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktstusg 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,ktcxbxba,ktcxblkp,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
from x$ktcxb 
where bitand(ksspaflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)  
/------------------ V$_LOCK1
select  LADDR , KADDR , SADDR , RADDR , LMODE , REQUEST , CTIME , BLOCK 
from GV$_LOCK1 
where inst_id = USERENV('Instance')
/------------------ GV$_LOCK1
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim, ksqlklblk 
from x$kdnssf 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
    union all 
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim, ksqlklblk 
from x$ksqeq 
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)

Кстати, как можно заметить есть разница в gv$lock между версиями и ее нужно учесть(на 11.2.0.2 я еще не смотрел - позже подправлю):
в 11.2.0.3 предикат l.raddr=r.addr изменился на concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr).
В моей ситуации из v$lock запрашивались только конкретные блокировки, причем основными из них были пользовательские, т.е. с типом 'UL' - user locks. Поэтому разобрав код, нужно было получить какие блокировки возвращает каждый конкретный блок union all. Для этого я создал модифицированный GV$LOCK:

create or replace view xt_gv$_lock as
with XT_GV$_LOCK as (
      select 1 sq
             ,USERENV('Instance') inst_id,laddr,kaddr,saddr,raddr,lmode,request,ctime,  block 
      from v$_lock1 
          union all 
      select 2
             ,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
      from x$ktadm 
      where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
          union all 
      select 3
             ,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
      from x$ktatrfil 
      where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
          union all 
      select 4
             ,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
      from x$ktatrfsl 
      where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
          union all 
      select 5
             ,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
      from x$ktatl 
      where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
          union all 
      select 6
             ,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
      from x$ktstusc 
      where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
          union all 
      select 7
             ,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
      from x$ktstuss 
      where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
          union all 
      select 8
             ,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
      from x$ktstusg 
      where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0) 
          union all 
      select 9
             ,inst_id,ktcxbxba,ktcxblkp,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk 
      from x$ktcxb 
      where bitand(ksspaflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
)
select l.sq 
      ,s.inst_id                             INST_ID  
      ,l.laddr                               ADDR    
      ,l.kaddr                               KADDR
      ,s.ksusenum                            SID
      ,r.ksqrsidt                            TYPE
      ,r.ksqrsid1                            ID1
      ,r.ksqrsid2                            ID2
      ,l.lmode                               LMODE
      ,l.request                             REQUEST                      
      ,l.ctime                               CTIME
      ,decode(l.lmode,0,0,l.block)           BLOCK
from XT_GV$_LOCK l, x$ksuse s, x$ksqrs r  
where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr)
/
create or replace public synonym xt_gv$lock for xt_gv$_lock
/
grant select on xt_gv$lock to public
/

Теперь можем получить на нагруженной базе соответствие типов блокировок ~ конкретному блоку:

with t as (select distinct sq,type from xt_gv$lock l)
select sq
      ,listagg(t.TYPE,',') within group(order by t.type)
from t
group by sq

Где sq - это номер блока union all.
Или просто получить номер блока фильтром по нужному типу блокировки. Так, например, 'UL' будут в первом блоке и теперь, чтобы без проблем их выбирать можно запрашивать из этой новой вьюхи с добавлением предиката sq=1, чтобы не мучать зря другие блоки.

select * 
from xt_gv$lock l
where l.type='UL' 
and l.sq=1 -- первый блок, где ul и бывают

В моем нагрузочном тесте в условиях конкуренции модифицированный запрос по user locks не только полностью решил проблему с латчами, да еще и приблизительно в 200 раз ускорил выполнение запроса и существенно снял нагрузку с cpu.

Файлы скриптов:

Для 10.2 - 11.2.0.1: xt_gv$_lock_11_2_0_1.sql
Для 11.2.0.3: xt_gv$_lock_11_2_0_3.sql

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

2012-06-27T02:42:00.002+03:00

Увидев новые пару строк на 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
и дополнительно у Тома

В две строки

2012-06-18T22:42:00.000+03:00

dbms_random в параллели

2012-06-13T21:08:00.002+03:00

В документации к dbms_random сказано:

It will automatically initialize with the date, user ID, and process ID if no explicit initialization is performed.

Из данной фразы не очевидно, какой "process id" будет использоваться для инициализации в случае параллельного выполнения, поэтому приведу наглядный пример, показывающий независимость генератора dbms_random от "process id" слейва, то есть генерацию одинаковых значений в параллели:

with
 t  as ( select/*+ materialize */ level n from dual connect by level<=4000)
,t1 as (
         select--+ materialize parallel(t 4)
            dbms_random.string('x',4)
            ||';'
            ||(select sid||';'||process||';'||pid
               from v$session, v$process
               where sid=sys_context('USERENV','SID')
                 and PADDR=ADDR
                 and n>0
              ) f
         from t
)
,t2 as (
         select 
            t1.f
           ,count(*) over(partition by regexp_substr(f,'^[^;]+')) cnt
         from t1
)
select f
      ,regexp_substr(f,'[^;]+') rnd
      ,regexp_substr(f,'[^;]+',1,2) sid
      ,regexp_substr(f,'[^;]+',1,3) process
      ,regexp_substr(f,'[^;]+',1,4) pid
from t2 
where cnt>1
order by f

Результат:

F	RND	SID	PROCESS	PID
AARV;130;5472;30	AARV	130	5472	30
AARV;68;2228;29	AARV	68	2228	29
AC2R;130;5472;30	AC2R	130	5472	30
AC2R;68;2228;29	AC2R	68	2228	29
AC8O;130;5472;30	AC8O	130	5472	30
AC8O;68;2228;29	AC8O	68	2228	29
AKVZ;130;5472;30	AKVZ	130	5472	30
AKVZ;68;2228;29	AKVZ	68	2228	29
ALTQ;130;5472;30	ALTQ	130	5472	30
ALTQ;68;2228;29	ALTQ	68	2228	29
...	...	...	...	...

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

2012-05-18T00:50:00.001+03:00

Эта заметка посвящена известному факту о низкой производительности обработки исключений.
Да, действительно, обработка исключений достаточно медленна, однако, не стоит сразу же пытаться избегать их везде, где только можно, да еще и любыми способами. Например, часто вижу как их пытаются избегать даже в случаях поиска по первичному ключу 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

2012-04-28T02:33:00.086+03:00

Многие знают о том, что 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

Появление buffer sort в oracle 11.2 в select for update

2012-04-21T02:32:00.003+03:00

Начиная с 11.2.0.1 появилась новая строка "buffer sort" в планах с for update.
Пример:

DB11G/XTENDER> explain plan for
  2  select *
  3  from t_for_update
  4  where
  5    id=1
  6    and dt between date'2012-01-01'
  7               and date'2012-01-02'
  8  for update;

Explained.
DB11G/XTENDER> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3273240857
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  FOR UPDATE        |                 |       |       |            |          |
|   2 |   BUFFER SORT      |                 |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IX_T_FOR_UPDATE |     1 |    11 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"=1 AND "DT">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "DT"<=TO_DATE(' 2012-01-02 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

О причинах вкратце рассказывается тут. Цитата оттуда:

"buffer sort" is an operation to sort the data in private memory (sort area). However, there isn't any sort operation require in our query. We could understand that Oracle just adopt the mechanism to avoid read data from buffer cache when fetching data. Not only will it decrease the CR number, but also reduce latch requests.

Правда тут кое-что неверно: Джонатан Льюис поясняет, что операция buffer sort в таких случаях не включает сортировок. Вот вывод трассировки 10032 для for update с 32 записями:

---- Sort Statistics ------------------------------
Input records                             32
Output records                            32
Total number of comparisons performed     0
Total amount of memory used               2048
Uses version 1 sort

В 10053 трассировке видно, что стоимостной оптимизатор не анализирует планы без buffer sort'a, поэтому механизм прошит жестко и отключить его можно хинтом /*+ opt_param( 'optimizer_features_enable' '11.1.0.7' ) */
или если так не работает, то optimizer_features_enable('11.1.0.7'). Версию, естественно, ставить можно любую ниже 11.2.0.1

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

2012-04-21T00:32:00.000+03:00

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

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

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

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

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

Вариант 1. Поиск записей по вхождению поля в заданную коллекцию

Рассмотрим сначала первый вариант - поиск где поле входит в коллекцию.
Текст примера:

--drop table member_of_test1 purge;

create table member_of_test1(n primary key)
as
select level from dual connect by level<1e5
/
begin
  dbms_stats.gather_table_stats(user,'member_of_test1',cascade => true);
end;
/
explain plan for 
select * from member_of_test1 
where member_of_test1.n member of cast (:a as number_table)
/
select * from table(dbms_xplan.display)
/
explain plan for 
select * from member_of_test1 
where member_of_test1.n in (select column_value from table(cast (:a as number_table)))
/
select * from table(dbms_xplan.display)
/

План с member of:

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

Plan hash value: 3157173230

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |  5000 | 25000 |    49   (7)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MEMBER_OF_TEST1 |  5000 | 25000 |    49   (7)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("MEMBER_OF_TEST1"."N"MEMBER OFCAST(:A AS "NUMBER_TABLE") )

План с "in (select * from table(:collection))":

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

Plan hash value: 3898737298

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |   255 |  1785 |    30   (4)| 00:00:01 |
|   1 |  NESTED LOOPS                       |              |   255 |  1785 |    30   (4)| 00:00:01 |
|   2 |   SORT UNIQUE                       |              |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH|              |  8168 | 16336 |    29   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN                 | SYS_C0024557 |     1 |     5 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   4 - access("MEMBER_OF_TEST1"."N"=VALUE(KOKBF$))

Разница очевидна: full table scan против index unique scan'a! Кроме того, в варианте с "n in (select * from table(:collection))" можно было бы еще и получить concatenation при использовании хинта precompute_subquery.

Вариант 2. Поиск по вложенной таблице

Cоздадим тестовую таблицу с nested table. sys.ku$_ObjNumSet - это table of number.
Кстати, с 11.2 в nested table уже автоматически создается индекс для nested_table_id, поэтому следовать совету из 11.1 "Design Considerations for Nested Tables" уже не нужно, если цель только быстро доставать чайлдов из nested table от родителя.
Но т.к. мы хотим проверить поиск именно от nested table к родительской, то создадим свой

create table nt_test
(
  id integer
 ,nt sys.ku$_ObjNumSet
 ,primary key(id) using index( create unique index pk_nt_test on nt_test(id) )
)
nested table nt 
store as nt_table(
   (
    column_value not null
   ,constraint pk_nt_table primary key(nested_table_id,column_value)
   )
)
/

На каждую запись из родительской вставим три во вложенной:

insert into nt_test 
select level
      ,sys.ku$_ObjNumSet(level,level+1,level+2)
from dual
connect by level<=1e4
/

еще создадим индекс, который по идее помог бы нам в поиске сначала по значениям в nested table:

create index ix_nt_table_good on nt_table(column_value,nested_table_id);

Т.к. мы знаем, что в родительской таблице автоматически создается индекс на поле связи с дочерней таблицей(nested_table_id), то попробуем его получить:

col column_name format a30

select i.index_name,ic.TABLE_NAME ,ic.COLUMN_POSITION,ic.COLUMN_NAME 
from user_indexes i
    ,user_ind_columns ic
where 
    i.table_name='NT_TEST'
and i.index_name=ic.INDEX_NAME;

Вы увидите, что название этого столбца якобы соответствует названию nested table, которое было в ddl создания таблицы. Однако стоит только попытаться создать индекс с ним, и мы поймем что такого просто нет. Зато можем его получить из dba_cons_columns и сохранить, чтобы затем создать индекс с ним:

column column_name new_val col_name;

select  c.constraint_name,c.constraint_type,c.table_name
       ,c.index_name
       ,cc.position
       ,cc.column_name
from user_constraints c
    ,user_cons_columns cc
where 
    c.owner=user
and c.table_name='NT_TEST'
and c.constraint_type='U'
and cc.owner=c.owner
and cc.constraint_name=c.constraint_name
;
-- создадим теперь полный индекс с этим полем связи:
create unique index ix_nt_test_parent on nt_test (&col_name, id);

Соберем статистику:

begin
-- параметр cascade не включает сбор по вложенной таблице
  dbms_stats.gather_table_stats( user
                                ,'NT_TEST'
                                ,estimate_percent => 10
                                ,method_opt => 'FOR ALL COLUMNS SIZE 1'
                                ,cascade => true
                                );
-- поэтому по ней тоже надо собирать:
  dbms_stats.gather_table_stats( user
                                ,'NT_TABLE'
                                ,estimate_percent => 10
                                ,method_opt => 'FOR ALL COLUMNS SIZE 1'
                                ,cascade => true
                                );
end;

Теперь наконец посмотрим какие планы получаются:

-- План с member of:
explain plan for
select *
from nt_test t
where :n1 member of(nt);

select * from table(dbms_xplan.display);
----------------------------------
-- План с in (select * from table(...))
explain plan for
select *
from nt_test t
where :n1 in (select * from table(nt));

select * from table(dbms_xplan.display);

Member of:

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

Plan hash value: 3175250264

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  9918 |   203K|    15   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN  | PK_NT_TABLE |     3 |    63 |     2   (0)| 00:00:01 |
|*  2 |  FILTER            |             |       |       |            |          |
|   3 |   TABLE ACCESS FULL| NT_TEST     |  9918 |   203K|    13   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN | PK_NT_TABLE |     3 |    63 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - access("NESTED_TABLE_ID"=:B1)
   2 - filter(:N1MEMBER OF"NT")
   4 - access("NESTED_TABLE_ID"=:B1)

:value in (select * from table(nt)):

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

Plan hash value: 3039725448

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     3 |   126 |     5   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN | PK_NT_TABLE       |     3 |    63 |     2   (0)| 00:00:01 |
|   2 |  NESTED LOOPS     |                   |     3 |   126 |     5   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| IX_NT_TABLE_GOOD  |     3 |    63 |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN| IX_NT_TEST_PARENT |     1 |    21 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("NESTED_TABLE_ID"=:B1)
   3 - access("NT_TABLE"."COLUMN_VALUE"=TO_NUMBER(:N1))
   4 - access("NT_TABLE"."NESTED_TABLE_ID"="SYS_NC0000200003$")

Как видно, в варианте ":value in (select * from table(nt))" поиск происходит сразу по вложенной таблице с Index range scan и access по значению, в отличие от "member of", где сначала фулсканится родительская таблица и от нее уже по полю связи(NESTED_TABLE_ID) происходит lookup к вложенной таблице с фильтром по значению.

Вариант 3. "Member of" в PL/SQL

В PL/SQL особого смысла сравнивать, конечно, нет, но на всякий случай покажу, что использование member of вполне оправданно - по производительности лишь чуть-чуть проигрывает варианту с собственной функцией с компиляцией в native и plsql_optimize_level=3, зато универсальна и без лишних зависимостей. Код примера:

alter session set PLSQL_CODE_TYPE=NATIVE PLSQL_OPTIMIZE_LEVEL=3
/
create or replace function member_of2( p_element    integer
                                      ,p_collection sys.ku$_ObjNumSet
                                     ) 
                                     return boolean 
is
begin
  if p_element is null or p_collection is null then
    return null;
  else
    for i in p_collection.first..p_collection.last loop
      if p_element = p_collection(i) then
        return true;
      end if;
    end loop;
  end if;
exception when value_error then
  return false;
end;
/
declare
  a       sys.ku$_ObjNumSet:=sys.ku$_ObjNumSet();
  cnt     integer := 20000;
  b       integer := 0;
  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,'9999.99')||' '||msg);
    l_timer:=dbms_utility.get_time;
  end;
  
begin
  a.extend(cnt);
  for i in 1..cnt loop
    a(i):=i;
  end loop;

  print('Start');

  b:=0;  
  for i in 1..cnt loop
    if i member of a then
      b:=b+1;
    end if;
  end loop;
  print('1 finished. b='||b);
  
  b:=0;
  for i in 1..cnt loop
    if member_of2(i,a) then
      b:=b+1;
    end if;
  end loop;
  print('2 finished. b='||b);
end;
/

Результат:
Member of - 8.75 сек.

Собственная функция - 8.59.

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

2012-04-18T02:21:00.018+03:00

Очередная задачка показалась мне интересной: 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)

2012-04-08T19:31:00.000+03:00

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

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

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

Включение PL/Scope

Чтобы использовать PL/Scope, необходимо сначала настроить компилятор на анализ идентификаторов во время компиляции. Вы можете сделать это, изменив значение параметра plscope_settings на уровне сеанса:
ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'

Существуют два возможных значения: IDENTIFIERS:ALL и IDENTIFIERS:NONE. По умолчанию - IDENTIFIERS:NONE. Вы можете увидеть значение plscope_settings для каждой программной единицы с помощью запроса к [ALL/USER/DBA]_PLSQL_OBJECT_SETTINGS (прим переводчика: также как и другие полезные вещи, включая уровень pl/sql оптимизации и тд)

select name
      ,type
      ,plscope_settings
from USER_PLSQL_OBJECT_SETTINGS

Описание USER_PLSQL_OBJECT_SETTINGS:

NAME	VARCHAR2(30)	Название объекта
TYPE	VARCHAR2(12)	Тип объекта(Type,package,package body,procedure,function...)
PLSQL_OPTIMIZE_LEVEL	NUMBER	Уровень оптимизации(10g: 0-2, 11g: 0-3)
PLSQL_CODE_TYPE	VARCHAR2(4000)	Тип компиляции: Interpreted/Native
PLSQL_DEBUG	VARCHAR2(4000)	Включен ли debug
PLSQL_WARNINGS	VARCHAR2(4000)	Включен ли вывод предупреждений при компиляции.Кстати, "любимый" Кайтовский "when others" без raise обрел свое личное предупреждение-PLW-06009, а если установить параметр в 'ERRORS:ALL', то объект не будет скомпилирован пока не избавитесь от всех предупреждений
NLS_LENGTH_SEMANTICS	VARCHAR2(4000)	Параметр сравнения длины строковых переменных: байты/символы
PLSQL_CCFLAGS	VARCHAR2(4000)	Переменные условной компиляции
PLSCOPE_SETTINGS	VARCHAR2(4000)	IDENTIFIERS:ALL/ IDENTIFIERS:NONE

У этого параметра есть два возможных значения: IDENTIFIERS:ALL и IDENTIFIERS:NONE. По умолчанию - IDENTIFIERS:NONE - сбор идентификаторов отключен.

Представление ALL_IDENTIFIERS

При компиляции объекта с включенным PL/Scope собирается информация обо всех идентификаторах используемых в объекте, которая доступна через представление ALL_IDENTIFIERS.

Описание ALL_IDENTIFIERS:

Столбец	Значение
OWNER	Владелец объекта, содержащего идентификатор
NAME	Имя идентификатора
TYPE	Тип идентификатора, например: FORALL OUT (out аргумент), CONSTANT, PACKAGE, or RECORD
SIGNATURE	Уникальная строка для каждого идентификатора сквозная для всех объектов, для однозначного определения из идентификаторов с одинаковыми именами(прим. переводчика: далее сигнатура)
OBJECT_NAME	Имя объекта
OBJECT_TYPE	Тип объекта, например: PACKAGE, TRIGGER, или PROCEDURE
USAGE	Тип использования(объявление или присваивание)
USAGE_ID	Порядковый номер вхождения идентификатора внутри объекта
USAGE_CONTEXT_ID	Внешний ключ на родительский USAGE_ID; (например, контекст определения переменной - это имя подпрограммы, в которой эта переменная определена)
LINE	Порядковый номер строки вхождения переменной
COL	Порядковый номер символа с начала строки места положения переменной

Таблица 1: Описание ALL_IDENTIFIERS

Вы можете получить информацию по определенному объекту следующим запросом:

SELECT *
  FROM all_identifiers ai
 WHERE ai.owner = USER 
   AND ai.object_type = '<program_type>' 
   AND ai.object_name = '<program_name>'
ORDER BY line

Отслеживание использования переменной с помощью PL/Scope

PL/Scope сохраняет детальную информацию о каждом идентификаторе используемом в вашем коде. Каждая строка в ALL_IDENTIFIERS относится к определенной строке определенного объекта. Тип использования указывается в столбце USAGES:

ASSIGNMENT - присваивание - значение переменной в данном месте может измениться, то есть переменная находится либо в левой части оператора присваивания, либо находится в запросе в блоке INTO, либо передается OUT/IN OUT параметром.
CALL - вызов функции, процедуры или sql-запроса.
DECLARATION - объявление идентификатора.
REFERENCE - обращение. Означает, что идентификатор используется без изменения значения, например: вызов исключения, передача идентификатора как IN или IN OUT параметр подпрограммы или списке USING блока EXECUTE IMMEDIATE, и использование идентификатора в определении %TYPE.
DEFINITION - определение: говорит компилятору как использовать или использовать объявленный ранее идентификатор. Следующие типы будут иметь строку с DEFINITION в ALL_IDENTIFIERS: FUNCTION, OBJECT, PACKAGE, PROCEDURE, TRIGGER, и EXCEPTION.

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

SELECT ai.object_name
     , ai.object_type
     , ai.name variable_name
     , ai.name context_name
  FROM all_identifiers ai
 WHERE ai.owner = USER AND 
       ai.TYPE = 'VARIABLE' AND 
       ai.usage = 'DECLARATION'
ORDER BY ai.object_name, 
ai.object_type, ai.usage_id

Использование usage_id для отображения иерархии идентификаторов

Пакет может содержать несколько подпрограмм, которые также могут содержать один или несколько параметров. Вы можете использовать PL/Scope, чтобы показать эту иерархию. Покажем на примере пакета, представленного в Листинге 1.

Листинг 1: Код пакета plscope_demo

CREATE OR REPLACE PACKAGE plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER
                         , param2 IN employees.last_name%TYPE
                          );
END plscope_demo;
/
CREATE OR REPLACE PACKAGE BODY plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER
                         , param2 IN employees.last_name%TYPE
                          )
   IS
      c_no_such   CONSTANT NUMBER := 100;
      l_local_variable     NUMBER;
   BEGIN
      IF param1_in > l_local_variable
      THEN
         DBMS_OUTPUT.put_line (param2);
      ELSE
         DBMS_OUTPUT.put_line (c_no_such);
      END IF;
   END my_procedure;
END plscope_demo;
/

Вы можете выполнить следующий иерархический запрос(Листинг 2), чтобы показать родителя строки в ALL_IDENTIFIERS в столбце usage_context_id.

Листинг 2: Запрос отображения иерархии идентификаторов

WITH plscope_hierarchy
        AS (SELECT line
                 , col
                 , name
                 , TYPE
                 , usage
                 , usage_id
                 , usage_context_id
              FROM all_identifiers
             WHERE     owner = USER
                   AND object_name = 'PLSCOPE_DEMO'
                   AND object_type = 'PACKAGE BODY')
SELECT    LPAD (' ', 3 * (LEVEL - 1))
       || TYPE
       || ' '
       || name
       || ' ('
       || usage
       || ')'
          identifier_hierarchy
  FROM plscope_hierarchy
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER SIBLINGS BY line, col
/

Результат запроса:

PACKAGE PLSCOPE_DEMO (DEFINITION)
   PROCEDURE MY_PROCEDURE (DEFINITION)
      FORMAL IN PARAM1_IN (DECLARATION)
         SUBTYPE INTEGER (REFERENCE)
      FORMAL IN PARAM2 (DECLARATION)
      CONSTANT C_NO_SUCH (DECLARATION)
         CONSTANT C_NO_SUCH (ASSIGNMENT)
         NUMBER DATATYPE NUMBER (REFERENCE)
      VARIABLE L_LOCAL_VARIABLE (DECLARATION)
         NUMBER DATATYPE NUMBER (REFERENCE)
      FORMAL IN PARAM1_IN (REFERENCE)
      VARIABLE L_LOCAL_VARIABLE (REFERENCE)

Использование Signature для отличия идентификаторов с одинаковыми именами

Всегда можно найти вхождение строки в исходном коде поиском по представлению ALL_SOURCE. Также возможно использовать одно название переменной в нескольких различных элементах в вашем коде. К примеру, вы можете использовать в подпрограмме переменную с таким же названием:

PROCEDURE plscope_demo_proc
IS
  plscope_demo_proc   NUMBER;
BEGIN
  DECLARE
    plscope_demo_proc   EXCEPTION;
  BEGIN
    RAISE plscope_demo_proc;
  END;

  plscope_demo_proc := 1;
END plscope_demo_proc;

Это очень запутанный, но это, безусловно, валидный код, и будет очень тяжело различить использование различных идентификаторов с этим имени в поиске по ALL_SOURCE.

PL/Scope упрощает эту задачу, добавляя столбец SIGNATURE в представлении ALL_IDENTIFIERS. Каждый идентификатор имеет собственную сигнатуру - 32-байтную строку уникальную как внутри данной программной единицы (далее - юнит), так и вне ее (в отличие от USAGE_ID уникального только внутри своего юнита)

Листинг 3: Идентификаторы с одним именем

SELECT line
     , TYPE
     , usage
     , signature
  FROM all_identifiers
 WHERE     owner = USER
       AND object_name = 'PLSCOPE_DEMO_PROC'
       AND name = 'PLSCOPE_DEMO_PROC'
ORDER BY line

LINE  TYPE       USAGE        SIGNATURE                        
1     PROCEDURE  DEFINITION   51B3B5C5404AE8307DA49F42E0279915 
1     PROCEDURE  DECLARATION  51B3B5C5404AE8307DA49F42E0279915 
3     VARIABLE   DECLARATION  021B597943C0F31AD3938ACDAAF276F3 
6     EXCEPTION  DECLARATION  98E0183501FB350439CA44E3E511F60C 
8     EXCEPTION  REFERENCE    98E0183501FB350439CA44E3E511F60C 
11    VARIABLE   ASSIGNMENT   021B597943C0F31AD3938ACDAAF276F3

Заметьте, что каждая сигнатура появляется в выводе дважды. Для самой процедуры - DEFINITION и DECLARATION. Для переменных, констант, исключений и т.п. обязательно сначала будут строки с DECLARATION и лишь потом строки с такой же сигнатурой, показывающие строки кода в которых эти идентификаторы используются.

С помощью сигнатур можно легко отфильтровать только те строки кода, в которых используется конкретный искомый идентификатор, несмотря на многочисленные идентификаторы с тем же именем. Например, в Листинге 4 запрашиваются все присваивания и обращения к переменной PLSCOPE_DEMO_PROC .

Листинг 4: Запрос всех присваиваний и обращений к переменной PLSCOPE_DEMO_PROC

SELECT usg.line
     , usg.TYPE
     , usg.usage
  FROM all_identifiers dcl, 
      all_identifiers usg
 WHERE     
    dcl.owner = USER
 AND dcl.object_name = 'PLSCOPE_DEMO_PROC'
 AND dcl.name = 'PLSCOPE_DEMO_PROC'
 and dcl.usage = 'DECLARATION'
 and dcl.type = 'VARIABLE'
 and usg.signature = dcl.signature
 and usg.usage <> 'DECLARATION'
ORDER BY line

Давайте теперь рассмотрим для чего еще можно использовать PL/Scope:

Проверка соглашений именования объектов и переменных
Определение нарушений рекомендаций "Best practices"

Проверка соглашений именования объектов и переменных

С помощью PL/Scope легко и просто различить типы идентификаторов (переменные, константы, параметры и т.д.), поэтому можно также проверять не нарушают ли они правила именования для своего типа.

К примеру, я следую следующим соглашениям при именовании параметров:
IN параметры заканчивается постфиксом "_in"
OUT параметры - _out
IN OUT параметры - _io

Чтобы проверить следует ли юнит данным правилам, я посмотрю в строки с usage = declaration и типом FORMAL IN, FORMAL OUT или FORMAL IN OUT.

Предположим, есть пакет с описанием, представленным в Листинге 5.

Листинг 5: Создание пакета plscope_demo

CREATE OR REPLACE PACKAGE plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER, param2 IN DATE);

   FUNCTION my_function (param1    IN INTEGER
                       , in_param2 IN DATE
                       , param3_in IN employees.last_name%TYPE
                        )
      RETURN VARCHAR2;
END plscope_demo;

Выполнив запрос из Листинга 6, мы можем найти нарушения правил именования параметров в данном пакете.

Листинг 6:Поиск нарушений правил именования

SELECT prog.name subprogram, parm.name parameter
  FROM all_identifiers parm, all_identifiers prog
 WHERE     parm.owner = USER
       AND parm.object_name = 'PLSCOPE_DEMO'
       AND parm.object_type = 'PACKAGE'
       AND prog.owner = parm.owner
       AND prog.object_name = parm.object_name
       AND prog.object_type = parm.object_type
       AND parm.usage_context_id = prog.usage_id
       AND parm.TYPE IN ('FORMAL IN', 'FORMAL IN OUT', 'FORMAL OUT')
       AND parm.usage = 'DECLARATION'
       AND ( (parm.TYPE = 'FORMAL IN'
              AND LOWER (parm.name) NOT LIKE '%\_in' ESCAPE '\')
            OR (parm.TYPE = 'FORMAL OUT'
                AND LOWER (parm.name) NOT LIKE '%\_out' ESCAPE '\')
            OR (parm.TYPE = 'FORMAL IN OUT'
                AND LOWER (parm.name) NOT LIKE '%\_io' ESCAPE '\'))
ORDER BY prog.name, parm.name

Обратите внимание на использование usage_context_id для поиска подпрограммы-"владельца" параметра.

Определение нарушений рекомендаций "Best practices"

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

Переменные декларированные в спецификации пакета. Это опасно тем, что любой пользователь с правом execute на пакет может читать и менять содержимое этих переменных напрямую.

Объявленные, но не используемые в коде исключения. Разработчики могут объявить собственные исключения в блоке и, если они не используются, то от них желательно избавиться.(Это же, кстати, относится и к неиспользуемым переменным)

Чтобы проверить объявленные переменные в спецификации пакета, мы должны их сначала найти. Это довольно просто:

SELECT object_name, name, line
  FROM all_identifiers ai
 WHERE ai.owner = USER
     AND ai.TYPE = 'VARIABLE'
     AND ai.usage = 'DECLARATION'
     AND ai.object_type = 'PACKAGE'

Чтобы проверить объявленные, но не вызываемые исключения, нужно сначала понять какие значения USAGE возможны для исключений. Рассмотрим следующую процедуру:

PROCEDURE plscope_demo_proc
IS
   e_bad_data   EXCEPTION;
   PRAGMA EXCEPTION_INIT (
                e_bad_data, -20900);
BEGIN
   RAISE e_bad_data;
EXCEPTION
   WHEN e_bad_data
   THEN
      log_error ();
END plscope_demo_proc;

Посмотрим, что покажет PL/Scope об идентификаторе "e_bad_data":

SELECT line
     , TYPE
     , usage
  FROM all_identifiers
 WHERE owner = USER
   AND object_name = 
              'PLSCOPE_DEMO_PROC'
   AND name = 'E_BAD_DATA'
ORDER BY line
/

LINE  TYPE       USAGE
-----  ------------  ---------------
3     EXCEPTION  DECLARATION 
4     EXCEPTION  ASSIGNMENT  
6     EXCEPTION  REFERENCE   
8     EXCEPTION  REFERENCE

Из этого можно сделать вывод, что EXCEPTION_INIT отображается как assignment - присваивание именованному исключению кода ошибки, а RAISE и WHEN - как references - использование.
Исходя из этого, мы можем найти все исключения, которые объявлены, но используются в коде:

Листинг 7:Запрос всех объявленных, но неиспользуемых исключений

WITH subprograms_with_exception
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers has_exc
             WHERE     has_exc.owner = USER
                   AND has_exc.usage = 'DECLARATION'
                   AND has_exc.TYPE = 'EXCEPTION'),
     subprograms_with_raise_handle
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers with_rh
             WHERE     with_rh.owner = USER
                   AND with_rh.usage = 'REFERENCE'
                   AND with_rh.TYPE = 'EXCEPTION')
SELECT *
  FROM subprograms_with_exception
MINUS
SELECT *
  FROM subprograms_with_raise_handle

Я реализовал многие из показанных в статье запросов в одном пакете доступном на http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165565.zip

Стивен Фейерштейн (steven.feuerstein@quest.com)

Оригинал статьи:http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165471.html

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

2012-03-22T22:53:00.006+03:00

Сегодня очень удивился заметив, что вызов "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