tag:blogger.com,1999:blog-47918725135653481252023-11-16T15:25:03.742+02:00XT&R blackboardJust another Oracle developer
Саян МалакшиновSayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.comBlogger63125tag:blogger.com,1999:blog-4791872513565348125.post-7714925442618155572014-01-15T01:02:00.001+03:002015-04-30T21:15:39.111+02:00Все, что за 2013-й год накопилось неопубликованногоВсе не хватает времени закросспостить, поэтому просто проведу инвентаризацию :) <br />
Список ссылок на неопубликованное из моего второго блога с короткими пояснениями:<br />
<ul><li>Все о кешировании deterministic функций: <a href="http://orasql.org/category/oracle/deterministic-functions/">http://orasql.org/category/oracle/deterministic-functions/</a><br />
В этом цикле я рассказываю о том, как устроен механизм их кеширования и сравниваю с механизмом кеширования скалярных подзапросов, а также немного о том, как оптимизируются циклы в PL/SQL.<br />
</li>
<li>Пример контроля "direct path reads" при фул сканах с помощью хинтов или профилей с INDEX_STATS/TABLE_STATS: <a href="http://orasql.org/category/oracle/adaptive-serial-direct-path-reads/">adaptive serial direct path reads</a><br />
</li>
<li>Про нежелательный inlist iterator по составным индексам: <a href="http://orasql.org/tag/inlist-iterator/">http://orasql.org/tag/inlist-iterator/</a><br />
</li>
<li>Различные трюки для SQL*Plus: <a href="http://orasql.org/category/oracle/sqlplus/">http://orasql.org/category/oracle/sqlplus/</a><br />
</li>
<li>Просто парочка общеизвестных ошибок, но часто пропускаемых по невнимательности: <a href="http://orasql.org/2013/05/28/a-couple-of-well-known-and-often-forgotten-things-for-plsql-developers/">a-couple-of-well-known-and-often-forgotten-things-for-plsql-developers</a><br />
</li>
<li>Как избавиться от многократного вызова функции из-за протолкнутых предикатов не изменяя кода: <a href="http://orasql.org/2013/06/10/too-many-function-executions/">http://orasql.org/2013/06/10/too-many-function-executions/</a><br />
</li>
<li>Всякая всячина о внутренностях 12с: inline функции, наконец-то разрешенный lateral, extended varchars, identities и defaults: <a href="http://orasql.org/category/oracle/12c/">http://orasql.org/category/oracle/12c/</a><br />
</li>
<li>Трюк с переопределением объекта используемого в "чужой" вьюхе, на примере получения списка всех таблиц непривилигированным пользователем: http://orasql.org/2014/01/14/a-little-trick-with-redefining-any-object-in-a-view-from-another-schema/<br />
</li>
</ul><br />
Ну и напоследок <a href="http://orasql.org/scripts/get_binds_from_trace_by_sqlid.pl">минискриптик-набросочек</a> для получения биндов из файла трассировки по заданному sql_id в xml или json, который мало кому-нибудь будет нужен, но мне пришлось наваять вчера :)<br />
<br />
зы. Надеюсь, я когда-нибудь все это оформлю нормально...Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-15717288367565798072013-05-15T00:36:00.000+03:002013-05-15T00:36:41.065+03:00Пара простых примеров по вчерашней дискуссии после семинара1. Пример, показывающий короткие clob'ы с "enable storage in row" будут передаваться точно так же как и обычные:<br />
<pre class="brush: sql">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
</pre>2. Пример, показывающий что строчный before триггер без всяких дополнительный манипуляций прекрасно увеличивает current'ы:<br />
<pre class="brush: sql">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;
</pre>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-84105731331245527612013-02-10T23:57:00.000+03:002013-02-10T23:57:58.358+03:00Новые посты на orasql.orgК сожалению, опять пока не успел сделать версию на русском поэтому пока просто ссылки с кратким описанием:<br />
1. <a href="http://orasql.org/2013/02/10/deterministic-function-vs-scalar-subquery-caching-part-1/">Сравнение механизмов кэширования deterministic функций и scalar subquery caching</a><br />
2. <a href="http://orasql.org/2013/02/09/materialization-in-subquery-factoring-without-hint-materialize-can-be-considered-only-when-exists-at-least-one-predicate/">Краткий пример того, что материализация subquery factoring может быть рассмотрена CBO(без хинта) только если есть хоть один предикат, даже бессмысленный 1=1</a><br />
3. <a href="http://orasql.org/2013/01/21/just-another-version-of-tom-kytes-runstats-runstats_pkg/">Моя версия Кайтовского runstats</a>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com2tag:blogger.com,1999:blog-4791872513565348125.post-18386354146981404932012-10-31T02:11:00.000+03:002012-10-31T02:15:51.509+03:00“Abridged” call stack в трассировке "dump sort statistics"(event 10032)Пару месяцев назад я обнаружил, что в трассировке сортировки в <b>11.2.0.3</b> появился <b>short call stack</b> и даже уже советовал на форуме как более простой <b>oradebug short_stack</b>, хотя, честно говоря, из-за тотального недостатка свободного времени до сих пор не разбирался в отличиях. Я даже не знаю с 11.2.0.2 или 11.2.0.3 он появился, т.к. все время забываю это проверить, когда есть под рукой 11.2.0.2, но в 11.2.0.1 его точно не было.<br />
<br />
Под катом пример:<br />
<a name='more'></a><br />
Включаем трассировку с 10-м уровнем:<br />
<pre class="brush: sql">alter session set events '10032 trace name context forever, level 10';
</pre>Кусок из трейс файла:<br />
<pre class="brush: sql">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
</pre>
Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-20941167210189122842012-10-15T21:04:00.001+03:002012-10-15T21:04:25.637+03:00Английская версия блога - orasql.orgЯ недавно открыл версию своего блога на английском и кое-что оттуда не успел запостить сюда, поэтому исправляюсь и вкратце резюмирую свои два поста:<br />
<br />
1. <a href="http://orasql.org/2012/10/12/easy-way-to-tracing/">Удобная трассировка</a>:<br />
Очень удобно создать отдельный сервис и включить на нем трассировку: теперь чтобы оттрассировать приложение или обращения с дблинка достаточно подключиться к на нужный сервис. Код доступен по ссылке<br />
<br />
2. <a href="http://orasql.org/2012/10/13/deceptive-commit-after-select-from-dblink/">Rollback вместо commit'a на дблинке</a>: <br />
При коммите после select * from tab@dblink на самом деле коммит будет только на локальном инстансе, а на самом дблинке будет роллбэк. При этом они оба будут "read-only". Кроме того, если после коммита/роллбэка мы будем еще вызывать коммиты/роллбэки на ремоут не будет ни коммита, ни роллбэка, однако, при отключении от локального инстансе(то есть просто при дисконнекте) на ремоут придет read-only commit. А как увидеть и каким же образом определяется, что будет коммит или роллбэк на ремоуте, непонятно...Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-5009095807434696822012-09-21T01:58:00.000+03:002012-09-22T03:29:14.307+03:00Удивительная оптимизация получения distinct values из индекса, а также TopN для каждогоНесколько дней назад на форуме задали, как изначально показалось, старый, скучный, вдоль и поперек изъезженный <a href="http://www.sql.ru/forum/actualthread.aspx?tid=969857">вопрос</a>:<br />
<blockquote>Есть лента новостей. Все новости разделены на 10 категорий(Политика, спорт, авто, недвижимость и тд).<br />
Надо 1 запросом для каждой категории выбрать 4 новости.<br />
Получается если перебрать результат - сразу идет 4 новости о политике, затем 4 новости о спорте и тд.</blockquote>Однако, задача стояла - сделать это оптимально, а стандартное решение с обычным TopN через row_number никак оптимальным не назвать, особенно в случае больших таблиц, относительно небольшого количества категорий и неравномерного распределения или просто общей низкой селективности. И вот после нескольких более-менее хороших вариантов, подглядев в решение с PostgreSQL(правда я в нем не стал сильно разбираться, достаточно было увидеть рекурсию, min и предикат), получился отличный вариант.<br />
<br />
Но обо всем по порядку:<br />
<br />
<b>1. Получение distinct значений из индекса</b><br />
<a name='more'></a><br />
Пусть есть табличка с индексом на поле а:<br />
<pre class="brush: sql">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.
</pre><div class="spoiler" title="Весь код создания таблицы"><pre class="brush: sql">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';
</pre></div><br />
Распределение значений у этого поля очень неравномерное:<br />
<div class="spoiler" title="Распределение"><table BORDER="1"><tr><th>A</TH><th>COUNT(*)</TH></TR>
<tr><td>1</TD><td>11</TD></TR>
<tr><td>2</TD><td>20</TD></TR>
<tr><td>3</TD><td>59</TD></TR>
<tr><td>4</TD><td>92</TD></TR>
<tr><td>5</TD><td>178</TD></TR>
<tr><td>6</TD><td>251</TD></TR>
<tr><td>7</TD><td>521</TD></TR>
<tr><td>9</TD><td>570</TD></TR>
<tr><td>10</TD><td>636</TD></TR>
<tr><td>8</TD><td>640</TD></TR>
<tr><td>11</TD><td>962</TD></TR>
<tr><td>12</TD><td>970</TD></TR>
<tr><td>13</TD><td>1151</TD></TR>
<tr><td>15</TD><td>1363</TD></TR>
<tr><td>14</TD><td>1544</TD></TR>
<tr><td>16</TD><td>1692</TD></TR>
<tr><td>18</TD><td>2021</TD></TR>
<tr><td>17</TD><td>2023</TD></TR>
<tr><td>19</TD><td>2550</TD></TR>
<tr><td>20</TD><td>2606</TD></TR>
<tr><td>21</TD><td>3050</TD></TR>
<tr><td>22</TD><td>3171</TD></TR>
<tr><td>23</TD><td>3395</TD></TR>
<tr><td>24</TD><td>3472</TD></TR>
<tr><td>29</TD><td>3527</TD></TR>
<tr><td>27</TD><td>3596</TD></TR>
<tr><td>26</TD><td>3698</TD></TR>
<tr><td>28</TD><td>4130</TD></TR>
<tr><td>25</TD><td>4268</TD></TR>
<tr><td>30</TD><td>17063</TD></TR>
<tr><td>Всего</TD><td>69230</TD></TR>
</TABLE></div>Стандартный запрос c distinct очень неудачен - в индексе всего 30 distinct_keys, а прочитать придется 135 блоков!<br />
С IFS:<br />
<pre class="brush: sql">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
</pre>C IFFS:<br />
<pre class="brush: sql">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
</pre>А ведь можно же было бы идти по дереву заходя только в нужные блоки, а не по всем листовым блокам! Однако, Oracle сам по себе такое не осилит и тут как раз придется его выкрутиться: у Oracle помимо IFS(min/max) есть и IRS(min/max) хорошо работающий с диапазонами и границами, и с помощью рекурсивного запроса, можно заставить его читать только нужное!<br />
<pre class="brush: sql">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
</pre>Разница очевидна: 36 consistent gets на 30 значений, вместо 135. И это очень маленькая табличка, а на миллионах и миллиардах записей разница будет еще более огромной!<br />
Поясню алгоритм: <br />
<ul><li>В первой части union all (3-4 строки плана) мы указываем с чего начать рекурсию, а конкретно выбираем минимальное(первое) значение из индекса.</li>
<li>Затем с помощью IRS(min/max) (7-6-5 строки плана) выбираем первое значение большее выбранного на предыдущем этапе</li>
<li>Повторяем рекурсию пока что-то находим</li>
</ul><br />
Переходим к следующему:<br />
<br />
<b>2. Топ N записей для каждого значению ключа</b><br />
<br />
Теперь вооруженные легким получением каждого начального значения, мы легко можем получить Топ для каждого из них, но для этого придется воспользоваться либо недокументированным Lateral() с включением соответствующего ивента, либо более простым и стандартным table(). Проблема остается только в том, что воспользоваться инлайн вью с row_number/rownum не получится, т.к. предикат с верхнего уровня не просунется, и придется воспользоваться простым ограничением по count stop key(по rownum) c обязательным доступом по IRS descending (order by там в общем лишний, но дополнительно уменьшает стоимость чтения IRS descending который нам и нужен для неявной сортировки) c подсказкой index_desc, чтобы прибить намертво, иначе сортировка может слететь:<br />
<pre class="brush: sql">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
</pre>
Аналогично можно через lateral:
<pre class="brush: sql">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
</pre>
В целом, конечно, можно было обойтись и без опасной сортировки, а взять и воспользоваться xmltable вместо table с передачей параметра сразу во внутренний подзапрос, но это чуток потяжелее чем обычный table.Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com1tag:blogger.com,1999:blog-4791872513565348125.post-5429247720809983612012-09-14T01:13:00.001+03:002012-09-29T14:57:10.549+03:00Включение parallel для index range scan и создания констрейнтовСегодня нужно было ускорить большую разовую выгрузку(вообще был "insert/*+append*/ select", но это не суть важно в данном контексте): большая не секционированная таблица, достаточно хорошая селективность по индексу( ~1.2%), больше 95% времени идет на lookup в таблицу из индекса. Один только индекс размером 44ГБ...<br />
<br />
Изначальный запрос был вида:<br />
<pre class="brush: sql">select t_big.*
from t_big
where t_big.a=:a
</pre>Естественно, сразу захотелось распараллелить, но index range scan не идет в параллели, поэтому пришлось прибегнуть к небольшому ухищрению, которое позволило ускорить выполнение более чем в 13 раз при установке DOP в 16!<br />
<br />
Метод прост: т.к.вычитка из индекса достаточно быстрая, то просто читаем и материализуем rowid через IRS, и затем уже в параллели идем к таблице через TABLE ACCESS BY USER ROWID.<br />
<br />
Окончательный запрос:<br />
<pre class="brush: sql">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
</pre><br />
<b>Создание констрейнтов в параллели:</b><br />
По умолчанию при создании констрейнтов они валидируются не распараллеливаясь, что существенно замедляет процесс. Особенно учитывая, что обычно это происходит в технологические перерывы и ресурсов полно.. Решается же проблема просто: создавать их нужно с <b><i>enable novalidate</i></b>, и лишь затем отдельно включать enable validate:<br />
<pre class="brush: sql">alter table t1
add constraint fk_t1
foreign key(a)
references t2(a)
enable novalidate;
alter table t1
modify constraint fk_t1
enable validate;
</pre><b>UPD:</b> Исправил на enable novalidate, т.к. как правильно заметил <a href="http://timurakhmadeev.wordpress.com/">Тимур</a> c disable не будет работать с unique и foreign key, а будет только для check constraints.Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com2tag:blogger.com,1999:blog-4791872513565348125.post-60145989723517635842012-08-23T22:20:00.000+03:002012-08-23T22:20:30.444+03:00О рекурсивных сессияхОтличная статья от Танела Поддера:
http://tech.e2sn.com/oracle/oracle-internals-and-architecture/recursive-sessions-and-ora-00018-maximum-number-of-sessions-exceededSayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-62195958425783642302012-08-11T00:42:00.000+03:002013-04-26T22:11:59.423+03:00Трассировка Event 10046, уровни 16,32,64Christian Antognini <a href="http://www.antognini.ch/2012/08/event-10046-full-list-of-levels/">опубликовал полный список уровней для 10046-й трассы</a>. <br />
Утащу к себе, пока не запомню :) <h2>Repost: Christian Antognini. "Event 10046 – Full List of Levels"</h2><p>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 <a title="Tracing Bind Variables and Waits" href="http://www.antognini.ch/papers/Tracing_Bind_Variables_and_Waits_20000507.pdf" target="_blank">my first paper</a> about it in May 2000 and the release of 11g nothing changed.</p><p>With 11g, as I described in <a title="11g New Feature in DBMS_MONITOR" href="http://www.antognini.ch/2009/02/11g-new-feature-in-dbms_monitor/" target="_blank">this post</a>, new levels (16 and 32) were introduced.</p><p>More recently, with the introduction of the <a title=" Bug 8328200 Misleading or excessive STAT# lines for SQL_TRACE / 10046 " href="https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?id=8328200.8" target="_blank">fix for bug 8328200</a>, a new one was added to the list (64).</p><p>So, I thought it was time to publish the current list of available levels…</p><table border="1"><tbody>
<tr><th><strong>Level</strong></th> <th><strong>Description</strong></th> </tr>
<tr> <td>0</td> <td>The debugging event is disabled.</td> </tr>
<tr> <td>1</td> <td>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.<br />
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.<br />
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. </td> </tr>
<tr> <td>4</td> <td>As in level 1, with additional information about bind variables. Mainly, the data type, its precision, and the value used for each execution.</td> </tr>
<tr> <td>8</td> <td>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.</td> </tr>
<tr> <td>16</td> <td>As in level 1, plus the execution plans information is written to the trace file for each execution. Available as of 11.1 only.</td> </tr>
<tr> <td>32</td> <td>As in level 1, but without the execution plans information. Available as of 11.1 only.</td> </tr>
<tr> <td>64</td> <td>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.</td> </tr>
</tbody></table><p>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:</p><ul><li>Level 12 (4 + 8): simultaneously enable level 4 and level 8.</li>
<li>Level 28 (4 + 8 + 16): simultaneously enable level 4, level 8 and level 16.</li>
<li>Level 68 (4 + 64): simultaneously enable level 4 and level 64.</li>
</ul><p>If you are using dbms_monitor or dbms_session for enabling extended SQL trace, here is the mapping between the levels and the parameters:</p><ul><li>Level 4: waits=FALSE, binds=TRUE, plan_stat=’first_execution’</li>
<li>Level 8: waits=TRUE, binds=FALSE, plan_stat=’first_execution’</li>
<li>Level 16: waits=FALSE, binds=FALSE, plan_stat=’all_executions’</li>
<li>Level 32: waits=FALSE, binds=FALSE, plan_stat=’never’</li>
<li>Level 64: not available yet</li>
</ul><p>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:</p><p><pre class="brush: sql">alter session set events '10046 trace name context forever, level 64'</pre></p><p><pre class="brush: sql">alter session set events 'sql_trace wait=false, bind=false, plan_stat=adaptive'</pre></p><p>I really hope that this limitation will be removed very soon.</p>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com2tag:blogger.com,1999:blog-4791872513565348125.post-826499938315064792012-08-10T23:41:00.000+03:002012-08-10T23:41:31.517+03:00Ссылка на онлайн unwrapper в документации Oracle<p>Все, конечно, знают про unwrapper'ы и многие даже пробовали свой написать (и я в том числе, после после презентации Pete Finnigan'а :), но тем не менее забавно, что Oracle в документации к 11.2 прямо-таки "громогласно" заявляет о том, что ничего unwrap от просмотра не защищает, но еще и прямую ссылку дает на online unwrapper :) </p>
<style type="text/css">
div.infoboxnote, div.infoboxnotewarn, div.infoboxnotealso {
margin-top: 4ex;
margin-right: 10%;
margin-left: 10%;
margin-bottom: 4ex;
padding: 0.25em;
border-top: 1pt solid gray;
border-bottom: 1pt solid gray;
font-family: Tahoma, sans-serif;
color: #222;
font-size: small;
display: block;
}
.IND p, .IND div, .IND table {
max-width: 70em;
}
.IND {
line-height: 1.5;
}
</style>
<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/wrap.htm"><b>11.2 PL/SQL Source Text Wrapping</b></a>:
<div class="infoboxnote">
<p class="notep1">Note:</p>
Wrapping text does not prevent anyone from displaying it with a utility such as:
<p><code><a href="http://www.codecheck.info/UnwrapIt/">http://www.codecheck.info/UnwrapIt/</a></code></p>
<p>For high-assurance security, use Oracle Database Vault, described in <a class="olink DVADM" href="http://docs.oracle.com/cd/E11882_01/appdev.112/server.112/e23090/toc.htm"><span class="italic">Oracle Database Vault Administrator's Guide</span></a>.</p>
</div>
И для сравнения прогресс документации от версии к версии о возможности unwrap'а:
<ul>
<li>В <a href="http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/c_wrap.htm#3209">9.2</a> все скрывает надежно, кроме литералов и названий переменных, таблиц, колонок:
<blockquote>
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.
</blockquote>
</li>
<li>А в <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/wrap.htm#sthref3172">10.2</a> уже защищено лишь от бóльшего количества пользователей, но все-таки затрудняет реверс-инжиниринг!
<blockquote>
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.
</blockquote>
</li>
<li>В <a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/wrap.htm#BEHGBJAA">11.1</a> как-то скромно и скучно:
<blockquote>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.
</blockquote>
</li>
</ul>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com3tag:blogger.com,1999:blog-4791872513565348125.post-82563163652496574332012-08-08T23:42:00.000+03:002012-08-08T23:42:08.572+03:00Регулярные выражения Oracle с вычисляемой заменяемой частью<a href="http://www.sql.ru/forum/actualthread.aspx?tid=960945">Интересная тема</a> с возможными решениями двух стандартных задач:<br />
1) sprintf в oracle в SQL<br />
2) замена по регулярке с вычисляемой заменяемой частью(а ля модификатор "e" в perl)Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-26950205711188594812012-07-30T23:25:00.001+03:002012-07-30T23:32:52.908+03:00When oracle invalidates result_cache function results without any changes in objects on which dependsOn our production servers we have simple function with result_cache, like this:<br />
<pre class="brush: sql">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;
/
</pre>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:<br />
1. "select for update" from this table with commit;<br />
2. deletion of unrelated rows from parent table if there is unindexed foreign key with "on delete cascade".<br />
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.<br />
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.<br />
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"?<br />
<a name='more'></a><br />
<div class="spoiler" title="tables and function"><pre class="brush: sql">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;
/
</pre>
</div><div class="spoiler" title="Test case with 'select for update'"><pre class="brush: sql">-- 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!
</pre></div><div class="spoiler" title="Unindexed fk"><pre class="brush: sql">-- 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.
</pre></div>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com3tag:blogger.com,1999:blog-4791872513565348125.post-30986744612912858272012-07-21T00:48:00.000+03:002012-07-21T00:48:06.937+03:00Интересный вопрос от Валентина НикотинаТестовая таблица:<br />
<pre class="brush: sql">create table tclob(c clob);
</pre><br />
Что будет выведено кодом из нижеследующих блоков с rollback и без:<br />
<div class="spoiler" title="Задача 1"><pre class="brush: sql">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;
/
</pre></div><br />
<div class="spoiler" title="Задача 2"><pre class="brush: sql">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;
/
</pre></div><br />
<div class="spoiler" title="Задача 3"><pre class="brush: sql">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;
/
</pre></div><br />
<div class="spoiler" title="Задача 4"><pre class="brush: sql">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;
/
</pre></div>Проверьте и на винде, и на солярке/линуксе :)<br />
<br />
<div class="spoiler" title="Объяснение">Ясно, что этот баг платформозависим и что дело в особенностях работы с памятью. Ответ кроется в том, что у cl3 и cl2 не синхронизированы длины, т.е. Oracle "забывает" изменить длины всех остальных переменных, указывающих на этот clob, а т.к. каждая операция изменения cl2/cl3 фактически изменяет одно и то же, то "лишнее" перезаписывается.<br />
</div>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com1tag:blogger.com,1999:blog-4791872513565348125.post-91957868455165422042012-07-14T00:37:00.002+03:002012-07-14T00:37:34.695+03:00Ура! Сын родился!Теперь у нас полная семья - и доча и сын :)<br />
Уррррррррраааааа! :)Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com1tag:blogger.com,1999:blog-4791872513565348125.post-79077573244117624102012-06-30T01:03:00.000+03:002012-10-04T22:36:56.024+03:00Большое количество latch free: dml allocation latch при конкурентных запросах к v$lockПроблема эта в общем-то старая, но только сейчас благодаря книге "Oracle Core" Джонатана Льюиса удалось узнать в чем собственно проблема и справиться с ней. <br />
Цитата из главы "<b>Latches for lock</b>":<br />
<blockquote><i><br />
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 <u>for a row from <b>x$ktadm</b> you need to get the dml allocation latch</u>. Drop this latch as soon as you have made the enqueue row safe. <br />
</i></blockquote><a name='more'></a><br />
А эта fixed таблица и есть в v$lock, это я многократно видел в планах c ней:<br />
<div class="spoiler" title="План"><pre class="brush: sql">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 |
--------------------------------------------------------------------------------------------
</pre></div>Теперь уже зная корень проблемы, остается только узнать можно ли избавиться от лишних обращений к X$KTADM, что интуитивно кажется возможным учитывая, что она часть "union all".<br />
То есть надо было "разобрать" этот v$lock, чтобы получить полный текст запроса. Для этого я сначала получил текст запроса через трассировку 10053:<br />
<div class="spoiler" title="Unparsed query"><pre class="brush: sql">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"
</pre></div>Но попытавшись начать форматировать после обработки бьютифаером, я бросил это дело поленившись и решил разобрать поступательно через v$fixed_view_definition. Так получилось гораздо лучше:<br />
<div class="spoiler" title="10.2.0.4-11.2.0.1"><pre class="brush: sql">/-------------------------- 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)
</pre></div><div class="spoiler" title="11.2.0.3"><pre class="brush: sql">/-------------------------- 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)
</pre></div>Кстати, как можно заметить есть разница в gv$lock между версиями и ее нужно учесть(на 11.2.0.2 я еще не смотрел - позже подправлю): <br />
в 11.2.0.3 предикат l.raddr=r.addr изменился на concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr).<br />
В моей ситуации из v$lock запрашивались только конкретные блокировки, причем основными из них были пользовательские, т.е. с типом 'UL' - user locks. Поэтому разобрав код, нужно было получить какие блокировки возвращает каждый конкретный блок union all. Для этого я создал модифицированный GV$LOCK:<br />
<div class="spoiler" title="xt_gv$_lock для 11.2.0.3"><pre class="brush: sql">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
/
</pre></div><br />
Теперь можем получить на нагруженной базе соответствие типов блокировок ~ конкретному блоку:<br />
<pre class="brush: sql">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
</pre>Где sq - это номер блока union all.<br />
Или просто получить номер блока фильтром по нужному типу блокировки. Так, например, 'UL' будут в первом блоке и теперь, чтобы без проблем их выбирать можно запрашивать из этой новой вьюхи с добавлением предиката sq=1, чтобы не мучать зря другие блоки. <br />
<pre class="brush: sql">select *
from xt_gv$lock l
where l.type='UL'
and l.sq=1 -- первый блок, где ul и бывают
</pre>В моем нагрузочном тесте в условиях конкуренции модифицированный запрос по user locks не только полностью решил проблему с латчами, да еще и приблизительно в 200 раз ускорил выполнение запроса и существенно снял нагрузку с cpu.<br />
<br />
Файлы скриптов:<br />
<ul><li>Для 10.2 - 11.2.0.1: <a href="/sql/vlock/xt_gv_lock_11_2_0_1.sql" target="_blank">xt_gv$_lock_11_2_0_1.sql</a></li>
<li>Для 11.2.0.3: <a href="/sql/vlock/xt_gv_lock_11_2_0_3.sql" target="_blank">xt_gv$_lock_11_2_0_3.sql</a></li>
</ul>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-69885210900049306062012-06-27T02:42:00.002+03:002012-11-14T00:04:51.291+03:00Список и описание новых хинтов в Oracle 11.1 - 11.2.0.3(включая недокументированные)Увидев новые пару строк на 11.2.0.3 в списке хинтов(V$SQL_HINT), решил свести их описания в одну удобную заметку, попутно попытавшись разобраться в них. <br />
Описание до конца еще не закончено, постараюсь сделать это на днях.<br />
<br />
<b>Cписок хинтов добавленных в 11g</b><!-- ></b>Cписок> и 10.2.0.5(описания ниже)--><br />
<table BORDER="1"><tr><th>NAME</TH><th>INVERSE</TH><th>VERSION</TH></TR>
<tr><td><a href="#FULL_OUTER_JOIN_TO_OUTER">FULL_OUTER_JOIN_TO_OUTER</a></TD><td>NO_FULL_OUTER_JOIN_TO_OUTER</TD><td>11.2.0.3</TD> </TR>
<tr><td><a href="#OUTER_JOIN_TO_ANTI">OUTER_JOIN_TO_ANTI</a></TD><td>NO_OUTER_JOIN_TO_ANTI</TD><td>11.2.0.3</TD></TR>
<tr><td><a href="#TABLE_LOOKUP_BY_NL">TABLE_LOOKUP_BY_NL</a></TD><td>NO_TABLE_LOOKUP_BY_NL</TD><td>11.2.0.2</TD></TR>
<tr><td><a href="#USE_HASH_GBY_FOR_PUSHDOWN">USE_HASH_GBY_FOR_PUSHDOWN</a></TD><td>NO_USE_HASH_GBY_FOR_PUSHDOWN</TD><td>11.2.0.2</TD></TR>
<tr><td><a href="#XDB_FASTPATH_INSERT">XDB_FASTPATH_INSERT</a></TD><td>NO_XDB_FASTPATH_INSERT</TD><td>11.2.0.2</TD></TR>
<tr><td><a href="#APPEND_VALUES">APPEND_VALUES</a></TD><td>NOAPPEND</TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#COALESCE_SQ">COALESCE_SQ</a></TD><td>NO_COALESCE_SQ</TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#CONNECT_BY_ELIM_DUPS">CONNECT_BY_ELIM_DUPS</a></TD><td>NO_CONNECT_BY_ELIM_DUPS</TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#DST_UPGRADE_INSERT_CONV">DST_UPGRADE_INSERT_CONV</a></TD><td>NO_DST_UPGRADE_INSERT_CONV</TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#EXPAND_TABLE">EXPAND_TABLE</a></TD><td>NO_EXPAND_TABLE</TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#FACTORIZE_JOIN">FACTORIZE_JOIN</a></TD><td>NO_FACTORIZE_JOIN</TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#NO_SUBSTRB_PAD">NO_SUBSTRB_PAD</a></TD><td> </TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#PLACE_DISTINCT">PLACE_DISTINCT</a></TD><td>NO_PLACE_DISTINCT</TD><td>11.2.0.1</TD></TR>
<tr><td><a href="http://books.google.ru/books?id=1Aqw6mrxz5AC&lpg=PA160&ots=T9sGosYLuH&dq=%22STATEMENT_QUEUING%22&hl=ru&pg=PA160#v=onepage&q=%22STATEMENT_QUEUING%22&f=false">STATEMENT_QUEUING</a></TD><td>NO_STATEMENT_QUEUING</TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#TRANSFORM_DISTINCT_AGG">TRANSFORM_DISTINCT_AGG</a></TD><td>NO_TRANSFORM_DISTINCT_AGG</TD><td>11.2.0.1</TD></TR>
<tr><td>XMLINDEX_SEL_IDX_TBL</TD><td> </TD><td>11.2.0.1</TD></TR>
<tr><td><a href="#BIND_AWARE">BIND_AWARE</a></TD><td>NO_BIND_AWARE</TD><td>11.1.0.7</TD></TR>
<tr><td><a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#CHDIFFJE">CHANGE_DUPKEY_ERROR_INDEX</a></TD><td> </TD><td>11.1.0.7</TD></TR>
<tr><td><a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#autoId17">IGNORE_ROW_ON_DUPKEY_INDEX</a></TD><td> </TD><td>11.1.0.7</TD></TR>
<tr><td><a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#autoId71">RETRY_ON_ROW_CHANGE</a></TD><td> </TD><td>11.1.0.7</TD></TR>
<tr><td>CHECK_ACL_REWRITE</TD><td>NO_CHECK_ACL_REWRITE</TD><td>11.1.0.6</TD></TR>
<tr><td>COST_XML_QUERY_REWRITE</TD><td>NO_COST_XML_QUERY_REWRITE</TD><td>11.1.0.6</TD></TR>
<tr><td>DB_VERSION</TD><td> </TD><td>11.1.0.6</TD></TR>
<tr><td>DOMAIN_INDEX_FILTER</TD><td>NO_DOMAIN_INDEX_FILTER</TD><td>11.1.0.6</TD></TR>
<tr><td><a href="INDEX_RS_ASC">INDEX_RS_ASC</a></TD><td> </TD><td>11.1.0.6</TD></TR>
<tr><td><a href="INDEX_RS_ASC">INDEX_RS_DESC</a></TD><td> </TD><td>11.1.0.6</TD></TR>
<tr><td><a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm#autoId27">MONITOR</a></TD><td>NO_MONITOR</TD><td>11.1.0.6</TD></TR>
<tr><td><a href="NLJ_BATCHING">NLJ_BATCHING</a></TD><td>NO_NLJ_BATCHING</TD><td>11.1.0.6</TD></TR>
<tr><td><a href="NLJ_PREFETCH">NLJ_PREFETCH</a></TD><td>NO_NLJ_PREFETCH</TD><td>11.1.0.6</TD></TR>
<tr><td>NO_LOAD</TD><td> </TD><td>11.1.0.6</TD></TR>
<tr><td>OUTER_JOIN_TO_INNER</TD><td>NO_OUTER_JOIN_TO_INNER</TD><td>11.1.0.6</TD></TR>
<tr><td><a href="#PLACE_GROUP_BY">PLACE_GROUP_BY</a></TD><td>NO_PLACE_GROUP_BY</TD><td>11.1.0.6</TD></TR>
<tr><td><a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm#BABIFIGC">RESULT_CACHE</a></TD><td>NO_RESULT_CACHE</TD><td>11.1.0.6</TD></TR>
<tr><td>SUBQUERY_PRUNING</TD><td>NO_SUBQUERY_PRUNING</TD><td>11.1.0.6</TD></TR>
<tr><td>USE_INVISIBLE_INDEXES</TD><td>NO_USE_INVISIBLE_INDEXES</TD><td>11.1.0.6</TD></TR>
<tr><td>USE_MERGE_CARTESIAN</TD><td> </TD><td>11.1.0.6</TD></TR>
<tr><td>XML_DML_RWT_STMT</TD><td> </TD><td>11.1.0.6</TD></TR>
<tr><td>XMLINDEX_REWRITE</TD><td>NO_XMLINDEX_REWRITE</TD><td>11.1.0.6</TD></TR>
<tr><td>XMLINDEX_REWRITE_IN_SELECT</TD><td>NO_XMLINDEX_REWRITE_IN_SELECT</TD><td>11.1.0.6</TD></TR>
<tr><td>CONNECT_BY_CB_WHR_ONLY</TD><td>NO_CONNECT_BY_CB_WHR_ONLY</TD><td>10.2.0.5</TD></TR>
<!--
<tr><td>GBY_PUSHDOWN</TD><td>NO_GBY_PUSHDOWN</TD><td>10.2.0.5</TD></TR>
--> </TABLE><a name="FULL_OUTER_JOIN_TO_OUTER"><h3>FULL_OUTER_JOIN_TO_OUTER / NO_FULL_OUTER_JOIN_TO_OUTER</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_CBO</td></tr>
<tr><td>Class:</td><td>FULL_OUTER_JOIN_TO_OUTER</td></tr>
<tr><td>Version:</td><td>11.2.0.3</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table><br />
Включает/выключает механизм трансформации запроса из full outer join в left outer join, появившийся и работающий по умолчанию с 11.2.0.2. <br />
Помимо самого хинта, появившегося в 11.2.0.3, управлять этим преобразованием можно начиная с 11.2.0.2 через fix control (bugno=9287401) и с помощью:<br />
<br />
<i>OPT_PARAM('_optimizer_full_outer_join_to_outer' 'false')</i><br />
<br />
или <i>alter session set "_optimizer_full_outer_join_to_outer"=true;</i><br />
<br />
<p />Пример:<br />
<div class="spoiler" title="Тестовые таблицы:"><pre class="brush: sql">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;
/
</pre></div><br />
<div class="spoiler" title="Пример трансформированного:"><pre class="brush: sql">>> 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
</pre></div><div class="spoiler" title="Нетрансформированного:"><pre class="brush: sql">>> 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
</pre></div><!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="OUTER_JOIN_TO_ANTI"><h3>OUTER_JOIN_TO_ANTI / NO_OUTER_JOIN_TO_ANTI</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_CBO</td></tr>
<tr><td>Class:</td><td>OUTER_JOIN_TO_ANTI</td></tr>
<tr><td>Version:</td><td>11.2.0.3</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Судя по названию и скрытому параметру _optimizer_outer_to_anti_enabled (Enable transformation of outer-join to anti-join if possible), введенному еще в 10.2, этот хинт отвечает за трансформацию outer join'ов к anti-join, но мне, к сожалению, не удалось воспроизвести это, и буду рад, если кто-нибудь поделится примером. Скажу сразу, что я пробовал только простые запросы полные аналоги классического anti-join'a.<br />
<!-- ---------------------------------------------------
-------------- 11.2.0.2 ------------------------
--------------------------------------------------- --><br />
<a name="TABLE_LOOKUP_BY_NL"><h3>TABLE_LOOKUP_BY_NL / NO_TABLE_LOOKUP_BY_NL</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_TABLE_LOOKUP_BY_NL</td></tr>
<tr><td>Class:</td><td>TABLE_LOOKUP_BY_NL</td></tr>
<tr><td>Version:</td><td>11.2.0.2</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>С этим хинтом у меня тоже, к сожалению, разобраться не получилось. Из описания параметра "_optimizer_enable_table_lookup_by_nl" - consider table lookup by nl transformation. Кстати говоря, этот параметр в 11.2.0.1 по умолчанию был false, а с 11.2.0.2 - true.<br />
В первую очередь я подумал, что он связан с <a href="http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#sthref955">новым вариантом nested loops</a> появившемся в 11g, но у него есть свой хинт - NLJ_BATCHING. Второй мыслью было о том, что хинт отвечает за включение/выключение этапа анализа для возможности NLJ_BATCHING, но добавление хинта в запросы с nested loops планов не меняло(попозже попробую проанализировать трассировку 10053 с ним на различных nl - будет ли видно, что он добавляет/убирает). Третью мыслью было, что это относится к механизму table lookup prefetch, но тоже не получилось их как-то связать, т.к. у того тоже есть свое название - NLJ_PREFETCH.<br />
<br />
В общем тоже прощу сообщить, если узнаете за что этот хинт отвечает.<br />
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="USE_HASH_GBY_FOR_PUSHDOWN"><h3>USE_HASH_GBY_FOR_PUSHDOWN / NO_USE_HASH_GBY_FOR_PUSHDOWN</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>USE_HASH_GBY_FOR_PUSHDOWN</td></tr>
<tr><td>Class:</td><td>USE_HASH_GBY_FOR_PUSHDOWN</td></tr>
<tr><td>Version:</td><td>11.2.0.2</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>И еще один хинт с которым не получилось разобраться: тоже судя по названию только догадываюсь о том, что этот хинт отвечает за использование hash group by для view pushed predicate, но сделать так, чтобы он хоть что-нибудь менял в 10053 мне не удалось. При этом, если моя догадка верна, непонятно где именно должен быть hash group by - по идее для hash group by задумывался use_hash_aggregation. <br />
Правда он черезчур загадочный: например, даже при появлении его в плане в секции outline, в самом плане может быть sort group by:<br />
<div class="spoiler" title="Пример появления use_hash_aggregation:"><pre class="brush: sql">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")
</pre></div><!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="XDB_FASTPATH_INSERT"><h3>XDB_FASTPATH_INSERT / NO_XDB_FASTPATH_INSERT</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_ALL</td></tr>
<tr><td>Class:</td><td>XDB_FASTPATH_INSERT</td></tr>
<tr><td>Version:</td><td>11.2.0.2</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Не документирован, но имеет свою ошибку:<br />
<pre class="brush: sql">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.
</pre><!-- ---------------------------------------------------
---------------------------------------------------
------------------ 11.2.0.1 --------------------
--------------------------------------------------- --><br />
<br />
<a name="APPEND_VALUES"><h3>APPEND_VALUES / NOAPPEND</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_CBO</td></tr>
<tr><td>Class:</td><td>APPEND_VALUES</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td><a href="http://docs.oracle.com/cd/E11882_01/server.112/e17118/sql_elements006.htm#SQLRF51109">да</a></td></tr>
</table><br />
Включает механизм direct path inserts аналогичный insert/*+ append*/ … select, но для insert into … values.<br />
<p />Пример:<br />
<pre class="brush: sql">forall i in c.first..c.last
insert/*+ APPEND_VALUES*/ values c(i);
</pre><br />
Дополнительно: <a href="http://www.oracle-base.com/articles/11g/append-values-hint-11gr2.php" title="Подробный пример">1</a><br />
<!-- --------------------------------------------------- --><br />
<a name="COALESCE_SQ"><h3>COALESCE_SQ / NO_COALESCE_SQ</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_COALESCE_SQ</td></tr>
<tr><td>Class:</td><td>COALESCE_SQ</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table><br />
<p>Включает механизм удаления лишних join'ов c какой-либо таблицей(join elimination) в случаях подзапросов.</p><div class="spoiler" title="Пример:"><pre class="brush: sql">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)
</pre></div>Дополнительно: <br />
<a href="http://optimizermagic.blogspot.com/2008/06/why-are-some-of-tables-in-my-query.html" title="Join elimination: Why are some of the tables in my query missing from the plan?">1</a> <a href="http://timurakhmadeev.wordpress.com/2010/01/18/coalesce_sq/" title="Подробный пример от Тимура Ахмадеева">2</a> <a href="http://oracle-randolf.blogspot.com/2012/03/coalesce-subquery-transformation.html" title="Пример от Randolf Geist">3</a><br />
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="CONNECT_BY_ELIM_DUPS"><h3>CONNECT_BY_ELIM_DUPS / NO_CONNECT_BY_ELIM_DUPS</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_ALL</td></tr>
<tr><td>Class:</td><td>CONNECT_BY_ELIM_DUPS</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table><p>Включает механизм отсева дубликатов в иерархическом запросе(в строке плана с connect by появляется (UNIQUE), например "CONNECT BY NO FILTERING WITH SW (UNIQUE))", при выключенном просто "CONNECT BY NO FILTERING WITH START-WITH]".</p><div class="spoiler" title="Пример:"><pre class="brush: sql">select/*+ NO_CONNECT_BY_ELIM_DUPS */ distinct *
from t_connect_by
start with a=1
connect by prior a+1 = a
</pre></div><br />
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="DST_UPGRADE_INSERT_CONV"><h3>DST_UPGRADE_INSERT_CONV / NO_DST_UPGRADE_INSERT_CONV</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_ALL</td></tr>
<tr><td>Class:</td><td>DST_UPGRADE_INSERT_CONV</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td><a href="http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams077.htm">отчасти</a></td></tr>
</table>Отвечает за работу внутренних операторов с конвертацией столбцов TIMESTAMP WITH TIMEZONE (TSTZ), которые ещё не были обновлены во время перехода на летнее время. <br />
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="EXPAND_TABLE"><h3>EXPAND_TABLE / NO_EXPAND_TABLE</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_TABLE_EXPANSION</td></tr>
<tr><td>Class:</td><td>EXPAND_TABLE</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>В 11gR2 введен новый механизм, позволяющий использовать разные пути доступа к секциям в случаях, когда индексы в некоторых секциях изменены на unusable - это позволяет иметь отключать индексы на нагруженных dml секциях. Механизм хорошо описан <a href="http://blogs.oracle.com/optimizer/entry/optimizer_transformations_table_expansion">тут</a>. За включение механизма отвечает скрытый параметр _optimizer_table_expansion - consider table expansion transformation.<br />
<br />
Пример:<br />
<div class="spoiler" title="Тестовые данные:"><pre class="brush: sql">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;
/
</pre></div><div class="spoiler" title="Без expand_table:"><pre class="brush: sql">>> 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 |
-------------------------------------------------------------------------------------------------
</pre></div><br />
<div class="spoiler" title="C expand_table:"><pre class="brush: sql">>> 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))
</pre></div><!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="FACTORIZE_JOIN"><h3>FACTORIZE_JOIN / NO_FACTORIZE_JOIN</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_JOINFAC</td></tr>
<tr><td>Class:</td><td>FACTORIZE_JOIN</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>В 11gR2 введен новый механизм, позволяющий объединять обращения к одной присоединяемой таблице по одним и тем же условиям в разных частях "union [all]" в один join на более позднем этапе после слияния других таблиц. Т.е. запрос вида:<br />
<pre class="brush: sql">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
</pre>Будет трансформирован в <br />
<pre class="brush: sql">select *
from t2
join (
select * from t1 where предикаты_по_таблице_t1_1
union all
select * from t1 where предикаты_по_таблице_t1_2
) on t1.a=t2.a
</pre><div class="spoiler" title="Пример c FACTORIZE_JOIN:"><pre class="brush: sql">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))
</pre></div><div class="spoiler" title="Пример с NO_FACTORIZE_JOIN:"><pre class="brush: sql">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))
</pre></div><a href="http://timurakhmadeev.wordpress.com/2009/12/01/join-factorization/">Описание и пример от Тимура Ахмадеева</a>.<br />
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="NO_SUBSTRB_PAD"><h3>NO_SUBSTRB_PAD</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_EXECUTION</td></tr>
<tr><td>Class:</td><td>NO_SUBSTRB_PAD</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Этот хинт используется dbms_stats в запросах сбора гистограмм. Он меняет поведение substrb - с этим хинтом результат substrb после получения подстроки по байтам будет еще обрезан по символам, т.е. остатки от мультибайтовых символов будут убраны:<br />
<br />
<div class="spoiler" title="Пример:"><pre class="brush: sql">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
</pre></div><!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- --><br />
<a name="PLACE_DISTINCT"><h3>PLACE_DISTINCT / NO_PLACE_DISTINCT</h3></a><br />
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_DIST_PLCMT</td></tr>
<tr><td>Class:</td><td>PLACE_DISTINCT</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>В 11.2.0.1 введен новый механизм cbqt - перемещение кляузы distinct в запросах с джойнами с верхнего уровня в inner view, для уменьшения набора данных на наиболее ранних этапах.<br />
При этом алиасы у этих внутренних представлений в плане будут начинаться с "VW_DTP_", в примере ниже это VW_DTP_7FEE568E.<br />
<br />
Пример:<br />
<div class="spoiler" title="Тестовый запрос:"><pre class="brush: sql">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
</pre>
</div><div class="spoiler" title="Отрывки из 10053 трассировки для него:"><pre class="brush: sql">****************************************
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
****************************************
</pre>
</div><div class="spoiler" title='Пример, показывающий что Distinct Placement не работает для "связанных" таблиц(то есть cross-joinы не подойдут)'><pre class="brush: sql">****************************************
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.
</pre></div>Дополнительно: <a href="http://timurakhmadeev.wordpress.com/2011/02/28/distinct-placement/">пример</a> от Тимура Ахмадеева.
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- -->
<a name="PLACE_GROUP_BY"><h3>PLACE_GROUP_BY / NO_PLACE_GROUP_BY</h3></a>
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_PLACE_GROUP_BY</td></tr>
<tr><td>Class:</td><td>PLACE_GROUP_BY</td></tr>
<tr><td>Version:</td><td>11.1.0.6</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Этот механизм аналогичен <a href="#PLACE_DISTINCT">PLACE_DISTINCT</a>(точнее, наоборот, т.к. PLACE_DISTINCT появился позже) и уже давно и хорошо описан:
<a href="http://jonathanlewis.wordpress.com/2008/12/21/group-by/">У Льюиса</a>
<a href="/docs/riyaj_cost_based_query_transformation.pdf">в презентации Riyaj Shamsudeen</a>
В планах эти inner-view легко заметить по префиксу "VW_GBC_", например VW_GBC_10.
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- -->
<a name="TRANSFORM_DISTINCT_AGG"><h3>TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG</h3></a>
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_INDEX_RS_ASC</td></tr>
<tr><td>Class:</td><td>ACCESS</td></tr>
<tr><td>Version:</td><td>11.1.0.6</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Тоже новый механизм трансформирования запроса c distinct для использования нового hash group aggregate, у этих вложенных представлений префиксы "VW_DAG_". Скрытый параметр - "_optimizer_distinct_agg_transform".
<a href="http://www.hellodba.com/reader.php?ID=183&lang=en">Пример</a>.
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- -->
<a name="BIND_AWARE"><h3>BIND_AWARE / NO_BIND_AWARE</h3></a>
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_CURSOR_SHARING</td></tr>
<tr><td>Class:</td><td>BIND_AWARE</td></tr>
<tr><td>Version:</td><td>11.1.0.7</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Это известнейший хинт включения/выключения bind aware cursor sharing(is_bind_aware в v$sql_shared_cursor).
<a href="https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1">Подробнее</a>
<a href="http://iusoltsev.wordpress.com/2011/11/14/bind-aware-cursor-sharing-cardinality-feedback-elements/">Дополнительно</a>
<a href="https://blogs.oracle.com/optimizer/entry/how_do_i_force_a">А тут забавно прочесть, что в официальном блоге рекомендуют недокументированный хинт</a>
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- -->
<a name="INDEX_RS_ASC"><h3>INDEX_RS_ASC / INDEX_RS_DESC</h3></a>
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_TRANSFORMATION</td></tr>
<tr><td>Class:</td><td>TRANSFORM_DISTINCT_AGG</td></tr>
<tr><td>Version:</td><td>11.2.0.1</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Эти обычные хинты для index range scan [asc/desc] появились в общем-то давно, в аутлайнах профилей они в 10.2 уже были.
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- -->
<a name="NLJ_BATCHING"><h3>NLJ_BATCHING / NO_NLJ_BATCHING</h3></a>
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_EXECUTION</td></tr>
<tr><td>Class:</td><td>ACCESS</td></tr>
<tr><td>Version:</td><td>11.1.0.6</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Как я уже говорил в описании к хинту <a href="#TABLE_LOOKUP_BY_NL">TABLE_LOOKUP_BY_NL</a> в 11g появился <a href="http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#sthref955">новый вариант nested loops join</a> и этот NLJ_BATCHING как раз и позволяет включать и выключать этот механизм.
Дополнительно:
<a href="http://jeffreylui.wordpress.com/2011/02/21/thoughts-on-nlj_batching/">Jeff's blog: Thoughts on NLJ batching</a>
<a href="http://dioncho.wordpress.com/2010/08/16/batching-nlj-optimization-and-ordering/">Dion Cho: Batching NLJ optimization and ordering</a>
<!-- ---------------------------------------------------
---------------------------------------------------
--------------------------------------------------- -->
<a name="NLJ_PREFETCH"><h3>NLJ_PREFETCH / NO_NLJ_PREFETCH</h3></a>
<table border="1"><tr><td>SQL feature:</td><td>QKSFM_EXECUTION</td></tr>
<tr><td>Class:</td><td>ACCESS</td></tr>
<tr><td>Version:</td><td>11.1.0.6</td></tr>
<tr><td>Документирован</td><td>нет</td></tr>
</table>Отвечают за механизм nested loops table prefetch, появившийся в 9i, хорошо объяснено тут:
<ul><li><a href="http://oracle-randolf.blogspot.com/2011/07/logical-io-evolution-part-2-9i-10g.html">Logical I/O - Evolution: Part 2 - 9i, 10g Prefetching</a></li>
<li><a href="http://oracle-randolf.blogspot.com/2011/08/logical-io-evolution-part-3-11g.html">Logical I/O Evolution - Part 3: 11g</a></li>
<li>и дополнительно <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:1591616885374501::::P11_QUESTION_ID:7110065183012">у Тома</a></li>
</ul>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-3785831554491141512012-06-18T22:42:00.000+03:002012-06-18T22:42:05.464+03:00В две строки<ol><li><a href="http://carlos-sierra.net/2012/06/05/sqltxplain-sqlt-11-4-4-6-is-now-available/">Обновился SQLTXPLAIN (SQLT)</a> </li>
<li><a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1366133.1">Новая версия "SQL Health-Check" скрипта</a> </li>
</ol>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-9959934481972498182012-06-13T21:08:00.002+03:002012-06-13T21:25:32.069+03:00dbms_random в параллелиВ документации к dbms_random <a href="http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_random.htm">сказано</a>: <blockquote>It will automatically initialize with the date, user ID, and process ID if no explicit initialization is performed.</blockquote>Из данной фразы не очевидно, какой "process id" будет использоваться для инициализации в случае параллельного выполнения, поэтому приведу наглядный пример, показывающий независимость генератора dbms_random от "process id" слейва, то есть генерацию одинаковых значений в параллели:<br />
<pre class="brush: sql">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
</pre><br />
Результат:<br />
<table BORDER="1"><tr><th>F</TH><th>RND</TH><th>SID</TH><th>PROCESS</TH><th>PID</TH></TR>
<tr><td>AARV;130;5472;30</TD><td>AARV</TD><td>130</TD><td>5472</TD><td>30</TD></TR>
<tr><td>AARV;68;2228;29</TD><td>AARV</TD><td>68</TD><td>2228</TD><td>29</TD></TR>
<tr><td>AC2R;130;5472;30</TD><td>AC2R</TD><td>130</TD><td>5472</TD><td>30</TD></TR>
<tr><td>AC2R;68;2228;29</TD><td>AC2R</TD><td>68</TD><td>2228</TD><td>29</TD></TR>
<tr><td>AC8O;130;5472;30</TD><td>AC8O</TD><td>130</TD><td>5472</TD><td>30</TD></TR>
<tr><td>AC8O;68;2228;29</TD><td>AC8O</TD><td>68</TD><td>2228</TD><td>29</TD></TR>
<tr><td>AKVZ;130;5472;30</TD><td>AKVZ</TD><td>130</TD><td>5472</TD><td>30</TD></TR>
<tr><td>AKVZ;68;2228;29</TD><td>AKVZ</TD><td>68</TD><td>2228</TD><td>29</TD></TR>
<tr><td>ALTQ;130;5472;30</TD><td>ALTQ</TD><td>130</TD><td>5472</TD><td>30</TD></TR>
<tr><td>ALTQ;68;2228;29</TD><td>ALTQ</TD><td>68</TD><td>2228</TD><td>29</TD></TR>
<tr><td>...</TD><td>...</TD><td>...</TD><td>...</TD><td>...</TD></TR>
</TABLE>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-50233104272031698782012-05-18T00:50:00.001+03:002012-05-18T00:51:54.258+03:00О производительности обработки исключенийЭта заметка посвящена известному факту о низкой производительности обработки исключений. <br />
Да, действительно, обработка исключений достаточно медленна, однако, не стоит сразу же пытаться избегать их везде, где только можно, да еще и любыми способами. Например, часто вижу как их пытаются избегать даже в случаях поиска по первичному ключу c минимальной вероятностью получения no_data_found. <br />
В целом, стоит учитывать вероятную частоту возникновения исключений и overhead, добавляемый выбранным способом обхода исключений.<br />
<br />
Поясню на примере, о котором говорил: пусть есть код, который возвращает поле из таблицы по pk и в случае, если такой записи нет, возвращает null. <br />
<div class="spoiler" title="Тестовая табличка:"><pre class="brush: sql">create table t_test(a primary key, b)
as
select level,level from dual connect by level<=1e5;
</pre>
</div>Создадим эталонную функцию для тестов:
<pre class="brush: sql">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;
</pre>Как я вижу, наиболее часто в таких случаях пытаются обойти механизм исключений следующими способами:
<div class="spoiler" title="Вариант 1"><pre class="brush: sql">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;
</pre></div>Кстати, в случаях если у вас в курсоре не может быть больше одной записи, то не делайте так:
<div class="spoiler"><pre class="brush: sql">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;
</pre></div>Иначе будут попытки второй итерации, что вы сможете увидеть в профайлере.
<div class="spoiler" title="Вариант 2"><pre class="brush: sql">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;
</pre></div>И предложу свой вариант для этого:
<div class="spoiler"><pre class="brush: sql">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;
</pre></div>И теперь проведем элементарный тест выполнив эти функции по тестовой табличке:
<pre class="brush: sql">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;
</pre>В результате мы получим такое соотношение:
<table border=1><tr><th>Вариант</th><th>Время(c)</th></tr>
<tr><td>1 вариант(c exception) </td><td>3.03</td></tr>
<tr><td>2 вариант(c циклом) </td><td>3.62</td></tr>
<tr><td>3 вариант(c min) </td><td>3.34</td></tr>
<tr><td>4 вариант(скалярный подзапрос)</td><td>3.10</td></tr>
</table>Как видите, в случае если исключения не вызываются, то оригинальный запрос быстрее всего!
Проверим, теперь с разными процентами исключений: исключения будут для запросов с i<=0, общее кол-во вызовов будет 100001, v_start и v_end буду менять парами: (-5000,95000),(10000,90000),(-50000,50000),(-90000,10000):
<div class="spoiler">
<pre class="brush: sql">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;
</pre></div>Итоговая таблица нескольких сравнений:
<table border=1><tr><th>Вариант </th><th>0% </th><th> ~5% </th><th>~10% </th><th>~50% </th><th>~90% </th></tr>
<tr><td>1 вариант(c exception) </td><td>3.04 </td><td>3.12 </td><td>3.16 </td><td>3.82 </td><td>4.51 </td></tr>
<tr><td>2 вариант(c циклом) </td><td>3.18 </td><td>3.21 </td><td>3.20 </td><td>3.51 </td><td>3.85 </td></tr>
<tr><td>3 вариант(c min) </td><td>3.37 </td><td>3.34 </td><td>3.29 </td><td>3.25 </td><td>3.18 </td></tr>
<tr><td>4 вариант(скалярный подзапрос)</td><td>3.12 </td><td>3.06 </td><td>3.03 </td><td>2.98 </td><td>2.94 </td></tr>
</table>Какие можно сделать выводы:
<ul><li>Как видите, для <u>данной</u> таблички 5% исключений - это своего рода переломная точка, когда стандартный вариант с exception начинает проигрывать варианту с подзапросом(если чуть точнее, то это было на ~4.5%), и ~10% другим двум вариантам</li>
<li>Варианты с min и циклом в целом хуже варианта с подзапросом.</li>
<li>Варианты с подзапросом и min ускоряются с увеличением количества "пустых" запросов.</li>
</ul>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-88386910706584924622012-04-28T02:33:00.086+03:002012-04-29T00:34:04.298+03:00Забавный факт о collectМногие знают о том, что oracle при необходимости сам создает доменные типы, например, когда используют тип объявленный в пакете(до 11g их можно было увидеть в dba_objects с именем like 'PLSQL%').<br />
<br />
<h2><b>Факт 1</b></h2><br />
Точно так же он поступает и при вызове агрегатной функции <a href="http://docs.oracle.com/cd/B13789_01/server.101/b10759/functions020.htm">collect</a>.<br />
<pre class="brush: sql">-- Сначала проверим есть ли такие типы
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
</pre>И еще выберем данные по нему из sys.obj$ - это нам потом пригодится:<br />
<pre class="brush: sql">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
</pre>Это абсолютно закономерно - нельзя вернуть клиенту информацию не описав ее. <br />
<br />
<h2><b>Факт №2</b></h2>Теперь зная, что тип создается, интересно что потом будет с этим типом: будет ли он удален после фетча или после отключения клиента? Например, при удалении пакетного типа автоматически дропался и доменный тип, будет ли здесь аналогичное автоматическое удаление?<br />
<br />
Согласно <b>Bug 4033868: COLLECT FUNCTION LEAVES TEMPORARY SYS TYPES BEHIND</b> этот баг решен в "11.0", но я тестирую на 11.2.0.1 и элементарная проверка после дисконнекта показала, что тип продолжает существовать до рестарта инстанса, однако на самом деле даже после этого он остается, но в dba_objects уже не выводится.<br />
<br />
Я дропну его сам, чтобы лишний раз не рестартить инстанс - это абсолютно аналогично тому как oracle "удаляет" этот тип в 11.2:<br />
<br />
<pre class="brush: sql">DB11G/XTENDER> drop type "SYSTPZvGjVQTySRSjYVlHXyEE2Q==";
Type dropped.
</pre>А теперь проверим:<br />
<pre class="brush: sql">DB11G/XTENDER> select * from dba_types
where type_name='SYSTPZvGjVQTySRSjYVlHXyEE2Q==';
no rows selected
</pre>Вроде удалили, однако посмотрим в sys.obj$:<br />
<pre class="brush: sql">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
</pre>Как видите, объект остался, но с 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" и выводить его не нужно.<br />
<pre class="brush: sql">and (o.type# not in (1 /* INDEX - handled below */,
10 /* NON-EXISTENT */)
</pre>А установка даты в 31.12.4712 23:59:59 указывает на его неактуальность - уж слишком далекое будущее :)<br />
<br />
<h2><b>Факт №3</b></h2>Теперь перейдем к тому, что меня собственно заставило повозиться с collect: ошибки с параллельным выполнением collect (<a href="http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=937491&msg=12482005">обсуждение на форуме с указанием и подробным описанием Bug 11906197</a>).<br />
<br />
Вообще collect довольно глючная штука, я и сам на это уже неоднократно <a href="http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=607879&msg=10137740">нарывался</a>, и на металинке много различных багов связанных с collect(например,<small> "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"</small>).<br />
<br />
<div class="spoiler" title="Тестовая табличка"><pre class="brush: sql">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);
</pre>
</div><div class="spoiler" title="Ошибки"><pre class="brush: sql">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
</pre></div>
А вот с медленным своим агрегатом таких ошибок нет. Стандартная дилемма: либо нестабильный, но быстрый collect, либо медленный свой агрегат...
<div class="spoiler" title="Пример агрегата"><pre class="brush: sql">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;
/
</pre></div>И результаты:
<table border=1><tr><th>Вариант</th><th>Время(сек)</th></tr>
<tr style="background-color:#AAA"> <td>select/*+ NO_PARALLEL*/ cast(collect(b) as number_table) from test_parallel group by a;</td> <td>0.03</td> </tr>
<tr style="background-color:#AAA"> <td>select/*+ NO_PARALLEL*/ ncollect(b) from test_parallel group by a</td> <td>0.08</td> </tr>
<tr style="background-color:#AAA"> <td>select ncollect(b) from test_parallel group by a;</td> <td>0.07</td> </tr>
<tr> <td>select/*+ NO_PARALLEL*/ collect(a) from test_parallel;</td> <td>0.02</td> </tr>
<tr> <td>select/*+ NO_PARALLEL*/ ncollect(a) from test_parallel</td> <td>0.18</td> </tr>
<tr> <td>select ncollect(a) from test_parallel;</td> <td>0.19</td> </tr>
<tr style="background-color:#AAA"> <td>select/*+ NO_PARALLEL*/ collect(b) from test_parallel;</td> <td>0.02</td> </tr>
<tr style="background-color:#AAA"> <td>select/*+ NO_PARALLEL*/ ncollect(b) from test_parallel</td> <td>0.18</td> </tr>
<tr style="background-color:#AAA"> <td>select ncollect(b) from test_parallel;</td> <td>0.06</td> </tr>
</table>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-81478903368467239112012-04-21T02:32:00.003+03:002012-04-21T02:35:33.932+03:00Появление buffer sort в oracle 11.2 в select for updateНачиная с 11.2.0.1 появилась новая строка "buffer sort" в планах с for update.<br />
Пример:<br />
<pre class="brush: sql">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'))
</pre>
О причинах вкратце рассказывается <a href="http://www.hellodba.com/reader.php?ID=177&lang=en">тут</a>.
Цитата оттуда:
<blockquote>"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.
</blockquote>Правда тут кое-что неверно: Джонатан Льюис <a href="http://jonathanlewis.wordpress.com/2006/12/17/buffer-sorts/">поясняет</a>, что операция buffer sort в таких случаях не включает сортировок. Вот вывод трассировки 10032 для for update с 32 записями:
<pre>---- Sort Statistics ------------------------------
Input records 32
Output records 32
Total number of comparisons performed 0
Total amount of memory used 2048
Uses version 1 sort
</pre>В 10053 трассировке видно, что стоимостной оптимизатор не анализирует планы без buffer sort'a, поэтому механизм прошит жестко и отключить его можно хинтом /*+ opt_param( 'optimizer_features_enable' '11.1.0.7' ) */<br />
или если так не работает, то optimizer_features_enable('11.1.0.7'). Версию, естественно, ставить можно любую ниже 11.2.0.1Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-86309079029709883552012-04-21T00:32:00.000+03:002012-11-24T10:49:29.362+03:00Минусы использования "member of" с коллекциями и вложенными таблицами<script type="text/javascript">
jQuery(document).ready(function(){
// Скрываем все спойлеры
jQuery('.spoiler-body').hide()
// по клику отключаем класс folded, включаем unfolded, затем для следующего
// элемента после блока .spoiler-head (т.е. .spoiler-body) показываем текст спойлера
jQuery('.spoiler-head').click(function(){
jQuery(this).toggleClass("folded").toggleClass("unfolded").next().toggle()
})
})
</script><br />
Давно хотел написать о минусах использования <b>"a member of b"</b> перед обычными старыми вариантами вроде <b>a in (select column_value from table(b))</b>.<br />
Резюме: <i>"member of"</i> в sql не умеет хорошо работать с индексами и использовать его желательно только в pl/sql - в этом случае по производительности практически одинаков с собственной функцией с циклом проверки(это покажу в самом конце).<br />
<br />
Рассмотрим два варианта использования member of:<ol><li> запрос с условием, где поле таблицы должно входить в коллекцию</li>
<li> запрос по таблице с вложенной таблицей и условием вхождения переменной во вложенную таблицу</li>
</ol><br />
И в первом и втором можно создать индексы, по которым отлично можно было бы искать необходимые поля с <i>index range/unique scan</i>, но в случае с <i>member of</i> ситуация гораздо хуже:<br />
<ul><li>В первом варианте можно добиться только <i>index fast full scan</i></li>
<li>Во втором еще хуже: будет сначала фулл скан по родительской таблице, от нее index range scan по вложенной с access только по полю <i>nested_table_id </i>- полю связи родительской с вложенной</li>
</ul><a name='more'></a><br />
<div class="spoiler" title="Подробнее..."><b><center><h2>Вариант 1. Поиск записей по вхождению поля в заданную коллекцию</b></center></h2>Рассмотрим сначала первый вариант - поиск где поле входит в коллекцию.<br />
Текст примера:<br />
<pre class="brush: sql">--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)
/
</pre>План с member of:<br />
<pre class="brush: sql">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") )
</pre>План с "in (select * from table(:collection))":<br />
<pre class="brush: sql">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$))
</pre>Разница очевидна: full table scan против index unique scan'a! Кроме того, в варианте с <i>"n in (select * from table(:collection))"</i> можно было бы еще и получить concatenation при использовании хинта <i>precompute_subquery</i>.<br />
<br />
<b><center><h2>Вариант 2. Поиск по вложенной таблице</b></center></h2><br />
Cоздадим тестовую таблицу с nested table. sys.ku$_ObjNumSet - это table of number.<br />
Кстати, с 11.2 в nested table уже автоматически создается индекс для <i>nested_table_id</i>, поэтому следовать совету из <a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjdes.htm#sthref829">11.1 "Design Considerations for Nested Tables"</a> уже <a href="http://docs.oracle.com/cd/E16338_01/appdev.112/e11822/adobjdes.htm#sthref805">не нужно</a>, если цель только быстро доставать чайлдов из nested table от родителя.<br />
Но т.к. мы хотим проверить поиск именно от nested table к родительской, то создадим свой<br />
<br />
<pre class="brush: sql">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)
)
)
/
</pre>На каждую запись из родительской вставим три во вложенной:<br />
<pre class="brush: sql">insert into nt_test
select level
,sys.ku$_ObjNumSet(level,level+1,level+2)
from dual
connect by level<=1e4
/
</pre>
еще создадим индекс, который по идее помог бы нам в поиске сначала по значениям в nested table:
<pre class="brush: sql">create index ix_nt_table_good on nt_table(column_value,nested_table_id);
</pre>Т.к. мы знаем, что в родительской таблице автоматически создается индекс на поле связи с дочерней таблицей(nested_table_id), то попробуем его получить:
<pre class="brush: sql">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;
</pre>Вы увидите, что название этого столбца якобы соответствует названию nested table, которое было в ddl создания таблицы. Однако стоит только попытаться создать индекс с ним, и мы поймем что такого просто нет.
Зато можем его получить из <i>dba_cons_columns </i>и сохранить, чтобы затем создать индекс с ним:
<pre class="brush: sql">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);
</pre>Соберем статистику:
<pre class="brush: sql">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;
</pre>Теперь наконец посмотрим какие планы получаются:
<pre class="brush: sql">-- План с 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);
</pre><b>Member of</b>:
<pre class="brush: sql">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)
</pre><b>:value in (select * from table(nt))</b>:
<pre class="brush: sql">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$")
</pre>Как видно, в варианте <i>":value in (select * from table(nt))"</i> поиск происходит сразу по вложенной таблице с <b>Index range scan</b> и <i>access </i>по значению, в отличие от <i>"member of"</i>, где сначала фулсканится родительская таблица и от нее уже по полю связи(NESTED_TABLE_ID) происходит lookup к вложенной таблице с фильтром по значению.
<b><center><h2>Вариант 3. "Member of" в PL/SQL</b></center></h2>В PL/SQL особого смысла сравнивать, конечно, нет, но на всякий случай покажу, что использование member of вполне оправданно - по производительности лишь чуть-чуть проигрывает варианту с собственной функцией с компиляцией в native и plsql_optimize_level=3, зато универсальна и без лишних зависимостей.
Код примера:
<pre class="brush: sql">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;
/
</pre><b>Результат:<ul><li>Member of - 8.75 сек.</li>
<li>Собственная функция - 8.59.</li>
</ul></b>
</div>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-8243377988895794522012-04-18T02:21:00.018+03:002012-04-19T21:58:55.952+03:00Небольшой и опасный workaround для работы с gtt в pipelined функции с parallel_enable<script type="text/javascript">
$(function() {
$('a#spoilerhead1').click(function() {
$('div#example1').toggle();
return false;
});
$('a#spoilerhead2').click(function() {
$('div#example2').toggle();
return false;
});
});
</script><br />
<a href="http://www.sql.ru/forum/actualthread.aspx?tid=934949">Очередная задачка</a> показалась мне интересной: Oracle очень плохо работает с временными таблицами(gtt - global temporary tables) в параллели.<br />
<p />Вообще у gtt в параллели в 11g куча ограничений, а до этого вообще запрещены:<br />
10.2: <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#sthref7490">Restrictions on Temporary Tables</a>:<br />
<blockquote>Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored. Specification of the parallel_clause returns an error.<br />
</blockquote>11.1: <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm#CACEJACE">Restrictions on Parallel DML</a><br />
11.2: <a href="http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_7002.htm#SQLRF54448">Restrictions on Parallel DML</a>:<br />
<blockquote>Parallel UPDATE, DELETE and MERGE are not supported for temporary tables.</blockquote><br />
<b>Обновление:</b><br />
Я был категорически не прав: в примере, который я привел ранее, данные gtt доступны только в одном слейве. Вообще доступность этих данных организуется каким-то хитрым способом, например, поглядите на данный пример:<br />
<a id="spoilerhead1">«Показать код...»</a><br />
<div id="example1" style="display: none;"><pre class="brush: sql" >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
/
</pre>
Результат:
<pre class=brush: sql"> >> 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
</pre></div>
<a id="spoilerhead2">«Ошибочный пример с видимостью только в одном слейве:»</a>
<div id="example2" style="display: none;"><pre class="brush: sql">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;
</pre>
</div>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-34523566527102163942012-04-08T19:31:00.000+03:002012-04-08T19:31:10.663+03:00Анализ PL/SQL кода в Oracle 11g (Перевод "Zoom In on Your Code" By Steven Feuerstein)<b>Используйте PL/Scope для анализа вашего PL/SQL кода</b><br />
<p />С момента первого релиза PL/SQL, Oracle предоставляет набор представлений позволяющих разработчику получать информацию о PL/SQL объектах. Эти представления помогают нам понимать и анализировать наш код. Oracle Database 11g предоставляет еще более мощный аналитический инструмент - PL/Scope, собирающий информацию обо всех идентификаторах в вашем PL/SQL коде, которая затем доступна через представления словаря данных. Эти представления могут помочь отследить использование каждой переменной в определении, ссылках, вызовах, а также местоположение каждого вхождения переменной в исходном коде.<br />
<br />
Благодаря PL/Scope разработчики могут значительно лучше и легче выполнять анализ кода. Некоторые полезные приемы его использования и будут рассмотрены в данной статье.<br />
<a name='more'></a><br />
<h3><b>Включение PL/Scope</b></h3><p />Чтобы использовать PL/Scope, необходимо сначала настроить компилятор на анализ идентификаторов во время компиляции. Вы можете сделать это, изменив значение параметра plscope_settings на уровне сеанса:<br />
<code>ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'</code><br />
<br />
Существуют два возможных значения: IDENTIFIERS:ALL и IDENTIFIERS:NONE. По умолчанию - IDENTIFIERS:NONE. Вы можете увидеть значение plscope_settings для каждой программной единицы с помощью запроса к [ALL/USER/DBA]_PLSQL_OBJECT_SETTINGS (прим переводчика: также как и другие полезные вещи, включая уровень pl/sql оптимизации и тд)<br />
<pre class="brush: sql">select name
,type
,plscope_settings
from USER_PLSQL_OBJECT_SETTINGS
</pre><i>Описание USER_PLSQL_OBJECT_SETTINGS</i>:<br />
<table border="1"><tr><td><br />
NAME </td><td>VARCHAR2(30)</td><td>Название объекта<br />
</td></tr>
<tr><td><br />
TYPE </td><td>VARCHAR2(12)</td><td>Тип объекта(Type,package,package body,procedure,function...)<br />
</td></tr>
<tr><td><br />
PLSQL_OPTIMIZE_LEVEL </td><td>NUMBER</td><td>Уровень оптимизации(<a href="http://docs.oracle.com/cd/B12037_01/server.101/b10755/initparams163.htm">10g: 0-2</a>, <a href="http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams189.htm">11g: 0-3</a>)<br />
</td></tr>
<tr><td><br />
PLSQL_CODE_TYPE </td><td>VARCHAR2(4000)</td><td>Тип компиляции: <a href="http://dsvolk.blogspot.com/2010/08/plsql-native-compilation.html">Interpreted/Native</a><br />
</td></tr>
<tr><td><br />
PLSQL_DEBUG </td><td>VARCHAR2(4000)</td><td>Включен ли <a href="http://docs.oracle.com/cd/E11882_01/server.112/e24448/initparams193.htm">debug</a><br />
</td></tr>
<tr><td><br />
PLSQL_WARNINGS </td><td>VARCHAR2(4000)</td><td><a href="http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams191.htm">Включен ли вывод предупреждений при компиляции</a>.Кстати, "любимый" Кайтовский "when others" без raise обрел свое личное предупреждение-PLW-06009, а если установить параметр в 'ERRORS:ALL', то объект не будет скомпилирован пока не избавитесь от всех предупреждений<br />
</td></tr>
<tr><td><br />
NLS_LENGTH_SEMANTICS </td><td>VARCHAR2(4000)</td><td><a href="http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams127.htm">Параметр сравнения длины строковых переменных: байты/символы</a><br />
</td></tr>
<tr><td><br />
PLSQL_CCFLAGS </td><td>VARCHAR2(4000)</td><td>Переменные <a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#BABIHIHF">условной компиляции</a><br />
</td></tr>
<tr><td><br />
PLSCOPE_SETTINGS </td><td>VARCHAR2(4000)</td><td>IDENTIFIERS:ALL/ IDENTIFIERS:NONE<br />
</table><br />
У этого параметра есть два возможных значения: IDENTIFIERS:ALL и IDENTIFIERS:NONE. По умолчанию - IDENTIFIERS:NONE - сбор идентификаторов отключен.<br />
<br />
<h3><b>Представление ALL_IDENTIFIERS</b></h3><p />При компиляции объекта с включенным PL/Scope собирается информация обо всех идентификаторах используемых в объекте, которая доступна через представление ALL_IDENTIFIERS.<br />
<br />
Описание ALL_IDENTIFIERS:<br />
<table hspace="5" width="100%" vspace="5" cellpadding="5" border="1" bgcolor="#dddddd" align="center"><tbody>
<tr><td><strong>Столбец</strong></td> <td><strong>Значение</strong></td> </tr>
<tr><td>OWNER</td> <td>Владелец объекта, содержащего идентификатор</td> </tr>
<tr><td>NAME</td> <td>Имя идентификатора</td> </tr>
<tr><td>TYPE</td> <td>Тип идентификатора, например: FORALL OUT (out аргумент), CONSTANT, PACKAGE, or RECORD</td> </tr>
<tr><td>SIGNATURE</td> <td>Уникальная строка для каждого идентификатора сквозная для всех объектов, для однозначного определения из идентификаторов с одинаковыми именами(прим. переводчика: далее сигнатура)</td> </tr>
<tr><td>OBJECT_NAME</td> <td>Имя объекта</td> </tr>
<tr><td>OBJECT_TYPE</td> <td>Тип объекта, например: PACKAGE, TRIGGER, или PROCEDURE</td> </tr>
<tr><td>USAGE</td> <td>Тип использования(объявление или присваивание)</td> </tr>
<tr><td>USAGE_ID</td> <td>Порядковый номер вхождения идентификатора внутри объекта</td> </tr>
<tr><td>USAGE_CONTEXT_ID</td> <td>Внешний ключ на родительский USAGE_ID; (например, контекст определения переменной - это имя подпрограммы, в которой эта переменная определена)</td> </tr>
<tr><td>LINE</td> <td>Порядковый номер строки вхождения переменной</td> </tr>
<tr><td>COL</td> <td>Порядковый номер символа с начала строки места положения переменной</td> </tr>
</tbody> </table>Таблица 1: Описание ALL_IDENTIFIERS<br />
<br />
<p/>Вы можете получить информацию по определенному объекту следующим запросом:<br />
<pre class="brush: sql">SELECT *
FROM all_identifiers ai
WHERE ai.owner = USER
AND ai.object_type = '<program_type>'
AND ai.object_name = '<program_name>'
ORDER BY line
</pre><br />
<h3><b>Отслеживание использования переменной с помощью PL/Scope</b></h3><p/>PL/Scope сохраняет детальную информацию о каждом идентификаторе используемом в вашем коде. Каждая строка в ALL_IDENTIFIERS относится к определенной строке определенного объекта. Тип использования указывается в столбце USAGES:<br />
<ul><li><b>ASSIGNMENT</b> - присваивание - значение переменной в данном месте может измениться, то есть переменная находится либо в левой части оператора присваивания, либо находится в запросе в блоке INTO, либо передается OUT/IN OUT параметром.<br />
</li>
<li><b>CALL</b> - вызов функции, процедуры или sql-запроса.<br />
</li>
<li><b>DECLARATION</b> - объявление идентификатора.<br />
</li>
<li><b>REFERENCE</b> - обращение. Означает, что идентификатор используется без изменения значения, например: вызов исключения, передача идентификатора как IN или IN OUT параметр подпрограммы или списке USING блока EXECUTE IMMEDIATE, и использование идентификатора в определении %TYPE.<br />
</li>
<li><b>DEFINITION</b> - определение: говорит компилятору как использовать или использовать объявленный ранее идентификатор. Следующие типы будут иметь строку с DEFINITION в ALL_IDENTIFIERS: FUNCTION, OBJECT, PACKAGE, PROCEDURE, TRIGGER, и EXCEPTION.<br />
</li>
</ul><br />
Если вы захотите увидеть все объявленные переменные в коде, вы можете выполнить следующий запрос:<br />
<pre class="brush: sql">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
</pre><br />
<h3><b>Использование usage_id для отображения иерархии идентификаторов</b></h3><br />
Пакет может содержать несколько подпрограмм, которые также могут содержать один или несколько параметров. Вы можете использовать PL/Scope, чтобы показать эту иерархию. Покажем на примере пакета, представленного в Листинге 1.<br />
<br />
<b>Листинг 1:</b> Код пакета plscope_demo<br />
<pre class="brush: sql">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;
/
</pre><br />
Вы можете выполнить следующий иерархический запрос(Листинг 2), чтобы показать родителя строки в ALL_IDENTIFIERS в столбце usage_context_id.<br />
<br />
<b>Листинг 2:</b> Запрос отображения иерархии идентификаторов<br />
<pre class="brush: sql">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
/
</pre>Результат запроса:<br />
<pre class="brush: sql">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)
</pre><br />
<h3><b>Использование Signature для отличия идентификаторов с одинаковыми именами</b></h3><br />
Всегда можно найти вхождение строки в исходном коде поиском по представлению ALL_SOURCE. Также возможно использовать одно название переменной в нескольких различных элементах в вашем коде. К примеру, вы можете использовать в подпрограмме переменную с таким же названием:<br />
<br />
<pre class="brush: sql">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;
</pre><br />
Это очень запутанный, но это, безусловно, валидный код, и будет очень тяжело различить использование различных идентификаторов с этим имени в поиске по ALL_SOURCE.<br />
<br />
PL/Scope упрощает эту задачу, добавляя столбец SIGNATURE в представлении ALL_IDENTIFIERS. Каждый идентификатор имеет собственную сигнатуру - 32-байтную строку уникальную как внутри данной программной единицы (далее - юнит), так и вне ее (в отличие от USAGE_ID уникального только внутри своего юнита)<br />
<br />
<b>Листинг 3:</b> Идентификаторы с одним именем<br />
<pre class="brush: sql">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
</pre><br />
Заметьте, что каждая сигнатура появляется в выводе дважды. Для самой процедуры - DEFINITION и DECLARATION. Для переменных, констант, исключений и т.п. обязательно сначала будут строки с DECLARATION и лишь потом строки с такой же сигнатурой, показывающие строки кода в которых эти идентификаторы используются.<br />
<br />
С помощью сигнатур можно легко отфильтровать только те строки кода, в которых используется конкретный искомый идентификатор, несмотря на многочисленные идентификаторы с тем же именем. Например, в Листинге 4 запрашиваются все присваивания и обращения к переменной PLSCOPE_DEMO_PROC .<br />
<br />
<b>Листинг 4:</b> Запрос всех присваиваний и обращений к переменной PLSCOPE_DEMO_PROC<br />
<pre class="brush: sql">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
</pre><br />
Давайте теперь рассмотрим для чего еще можно использовать PL/Scope:<br />
<ul><li>Проверка соглашений именования объектов и переменных</li>
<li>Определение нарушений рекомендаций "Best practices"</li>
</ul><br />
<h3><b>Проверка соглашений именования объектов и переменных</b></h3><br />
С помощью PL/Scope легко и просто различить типы идентификаторов (переменные, константы, параметры и т.д.), поэтому можно также проверять не нарушают ли они правила именования для своего типа.<br />
<p />К примеру, я следую следующим соглашениям при именовании параметров:<br />
IN параметры заканчивается постфиксом "_in"<br />
OUT параметры - _out<br />
IN OUT параметры - _io<br />
<br />
<p />Чтобы проверить следует ли юнит данным правилам, я посмотрю в строки с usage = declaration и типом FORMAL IN, FORMAL OUT или FORMAL IN OUT.<br />
<br />
<p />Предположим, есть пакет с описанием, представленным в Листинге 5.<br />
<br />
<br />
<br />
<b>Листинг 5:</b> Создание пакета plscope_demo<br />
<pre class="brush: sql">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;
</pre><br />
Выполнив запрос из Листинга 6, мы можем найти нарушения правил именования параметров в данном пакете.<br />
<br />
<br />
<br />
<b>Листинг 6:</b>Поиск нарушений правил именования<br />
<pre class="brush: sql">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
</pre><br />
Обратите внимание на использование usage_context_id для поиска подпрограммы-"владельца" параметра.<br />
<br />
<h3><b>Определение нарушений рекомендаций "Best practices"</b></h3><br />
В дополнение к предупреждениям при компиляции PL/Scope предлагает отличный способ реализации собственных проверок на нарушения рекомендаций. Ниже два сценария проверки, которые было бы тяжело реализовать простым сканированием исходного кода:<br />
<br />
<p /><b>Переменные декларированные в спецификации пакета.</b> Это опасно тем, что любой пользователь с правом execute на пакет может читать и менять содержимое этих переменных напрямую.<br />
<br />
<p /><b>Объявленные, но не используемые в коде исключения.</b> Разработчики могут объявить собственные исключения в блоке и, если они не используются, то от них желательно избавиться.(Это же, кстати, относится и к неиспользуемым переменным)<br />
<br />
Чтобы проверить объявленные переменные в спецификации пакета, мы должны их сначала найти. Это довольно просто:<br />
<br />
<pre class="brush: sql">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'
</pre><br />
Чтобы проверить объявленные, но не вызываемые исключения, нужно сначала понять какие значения USAGE возможны для исключений. Рассмотрим следующую процедуру:<br />
<pre class="brush: sql">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;
</pre>Посмотрим, что покажет PL/Scope об идентификаторе "e_bad_data":<br />
<pre class="brush: sql">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
</pre>Из этого можно сделать вывод, что EXCEPTION_INIT отображается как <i>assignment</i> - присваивание именованному исключению кода ошибки, а RAISE и WHEN - как <i>references</i> - использование. <br />
Исходя из этого, мы можем найти все исключения, которые объявлены, но используются в коде:<br />
<br />
<b>Листинг 7:</b>Запрос всех объявленных, но неиспользуемых исключений<br />
<pre class="brush: sql">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
</pre><br />
Я реализовал многие из показанных в статье запросов в одном пакете доступном на <a href="http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165565.zip">http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165565.zip</a><br />
<br />
<hr><b>Стивен Фейерштейн</b> (steven.feuerstein@quest.com)<br />
<br />
Оригинал статьи:<a href="http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165471.html">http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165471.html</a>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0tag:blogger.com,1999:blog-4791872513565348125.post-58822929956477969742012-03-22T22:53:00.006+03:002012-04-27T19:41:26.403+03:00Сравнение скорости функций user vs sys_context('USERENV','SESSION_USER')Сегодня очень удивился заметив, что вызов <i><b>"username:=user;"</b></i> и <i><b>"select user into username from dual;"</b></i> одинаковы по времени выполнения, в отличие от десятикратной разницы <i><b>dt:=sysdate</b></i> и <i><b>select sysdate into dt from dual;</b></i>. Оказалось все просто, стоило лишь поглядеть трассировку или поглядеть код пакета sys.standard: PL/SQL-ный <i>USER</i> содержит внутри рекурсивный запрос c вызовом sql-ного user: <b><i>"select user from sys.dual"</i></b>.<br />
Из кода <b>standard</b>:<br />
<pre class="brush: sql">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;
</pre><br />
И самое главное: user в 10 раз с лишним медленнее, чем sys_context('USERENV','SESSION_USER')!<br />
У user, видимо, все это время съедает переключение контекста, а исключение ICD_UNABLE_TO_COMPUTE в sys_context непроисходит(как и в случае с sysdate).<br />
<p>Я проверял на 10.2.0.4, 10.2.0.5, 11.2.0.1, 11.2.0.3 с plsql_optimization_level=2 результаты практически одинаковы, поэтому показываю только один:<br />
<pre class="brush: sql">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
</pre>Sayan Malakshinovhttp://www.blogger.com/profile/11087163803358489777noreply@blogger.com0