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

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

• Все о кешировании 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, который мало кому-нибудь будет нужен, но мне пришлось наваять вчера :)

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

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

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

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

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

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

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

Дальше »

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

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

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

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

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

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

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

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

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

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

Дальше »

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

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

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

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

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

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

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

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

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

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

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