Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 restart в 11g и как с ним бороться  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2957
 		Привожу просто результат, без комментариев.
Повторяется в 11.1 и 11.2.

+ test
drop table t;
create table t pctfree 10 as select level id, 111 num from dual connect by level < 100000;
set timing on
set autot on stat
update t set id = -id;
commit;
update t set id = -id;
commit;
create trigger trgt 
before update on t
begin
  null;
end;
/
update t set id = -id;
commit;
update t set id = -id;
commit;
drop trigger trgt;
update t set id = -id;
commit;
update t set id = -id;
commit;


+ result
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table t;

Table dropped.

SQL> create table t pctfree 10 as select level id, 111 num from dual connect by level < 100000;

Table created.

SQL> set timing on
SQL> set autot on stat
SQL> update t set id = -id;

99999 rows updated.

Elapsed: 00:00:04.89

Statistics
----------------------------------------------------------
         30  recursive calls
     456461  db block gets
        652  consistent gets
        193  physical reads
   81213276  redo size
        542  bytes sent via SQL*Net to client
        464  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      99999  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> update t set id = -id;

99999 rows updated.

Elapsed: 00:00:03.12

Statistics
----------------------------------------------------------
         18  recursive calls
     237855  db block gets
        426  consistent gets
          0  physical reads
   50064276  redo size
        546  bytes sent via SQL*Net to client
        464  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      99999  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> create trigger trgt
  2  before update on t
  3  begin
  4    null;
  5  end;
  6  /

Trigger created.

Elapsed: 00:00:00.00
SQL> update t set id = -id;

99999 rows updated.

Elapsed: 00:00:00.71

Statistics
----------------------------------------------------------
         26  recursive calls
     102448  db block gets
        293  consistent gets
          0  physical reads
   25250572  redo size
        547  bytes sent via SQL*Net to client
        464  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      99999  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> update t set id = -id;

99999 rows updated.

Elapsed: 00:00:02.23

Statistics
----------------------------------------------------------
         18  recursive calls
     102542  db block gets
        205  consistent gets
          0  physical reads
   25251816  redo size
        547  bytes sent via SQL*Net to client
        464  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      99999  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> drop trigger trgt;

Trigger dropped.

Elapsed: 00:00:00.01
SQL> update t set id = -id;

99999 rows updated.

Elapsed: 00:00:03.81

Statistics
----------------------------------------------------------
         55  recursive calls
     434886  db block gets
        653  consistent gets
          0  physical reads
   78168676  redo size
        547  bytes sent via SQL*Net to client
        464  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
      99999  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> update t set id = -id;

99999 rows updated.

Elapsed: 00:00:03.03

Statistics
----------------------------------------------------------
        144  recursive calls
     220412  db block gets
        471  consistent gets
          0  physical reads
   47582460  redo size
        549  bytes sent via SQL*Net to client
        464  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      99999  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
12 апр 11, 01:08    [10504056]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
сосед-акцессник
Guest 		Вопрос на понимание:
О "рестарте" оператора обычно говорят, когда он выполняется на фоне конкуренции.
Здесь, по видимости, все происходит в одном сеансе.
Какие основания есть для применения термина "рестарт" в данном случае?
12 апр 11, 04:18    [10504154]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
Владимир Бегун
Member

Откуда: Redwood Shores, CA USA
Сообщений: 1707
сосед-акцессник
происходит в одном сеансе
тут простой, старый, пример.
12 апр 11, 04:27    [10504157]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
сосед-акцессник
Guest 		:)
т.е. все-таки утверждается, что Set ID=-ID здесь значения не имеет.
Зато имеет значение именно пустой триггер.
Какой кошмар.

кстати, планируется ли торжественное празднование 10-летнего юбилея темы в следующем году?
12 апр 11, 05:16    [10504162]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
Владимир Бегун
Member

Откуда: Redwood Shores, CA USA
Сообщений: 1707
сосед-акцессник
:) т.е. все-таки утверждается, что Set ID=-ID здесь значения не имеет.
угу.
сосед-акцессник
Зато имеет значение именно пустой триггер.
это тоже не имеет. дело undo, почти такое же как восток... тонкое :)
сосед-акцессник
Какой кошмар.
не говори...
сосед-акцессник
кстати, планируется ли торжественное празднование 10-летнего юбилея темы в следующем году?
О, это да -- любой каприз за ваши деньги! :-) можно будет отпраздновать. Действительно, 10-лет... а через 2 года 15 с того самого момента, когда я тоже удивился :) старые грабли -- новые лбы :)
12 апр 11, 05:22    [10504165]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
Владимир Бегун
Member

Откуда: Redwood Shores, CA USA
Сообщений: 1707
это тоже не имеет
... но может работать как костыль. :)
12 апр 11, 05:24    [10504167]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2957
 		Тут как-то по-жеще ситуация, одно дело restart немного увеличивающий redo, другое - увеличивающий его в разы. И, так как костыль с триггером работает, получается это баг.
12 апр 11, 08:31    [10504303]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2957
 		Что-то это совсем жуткий баг. В 11g update неиндексированного столбца почти всегда приводит к перезапуску(а иногда и не одному) всего update, что в разы замедляет работу. Ситуацию "спасает" триггер :)

+ test
drop table t;

create table t 
(
  id number primary key, 
  gr number not null,
  val varchar2(30),
  col_for_upd varchar2(10)
);
insert into t 
select level, mod(level, 4), lpad('x',30,'x'), lpad('y',10,'y') 
from dual connect by level < 2000000;
create index idx_t_gr on t (gr);

set timing on

update /*+ gather_plan_statistics */ t set col_for_upd = col_for_upd where gr = 2;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
commit;

create trigger trgt 
before update on t
begin
  null;
end;
/

update /*+ gather_plan_statistics */ t set col_for_upd = col_for_upd where gr = 2;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
commit;


+ результат
SQL> update /*+ gather_plan_statistics */ t set col_for_upd = col_for_upd where gr = 2;
500000 rows updated.

Elapsed: 00:00:19.39

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

...

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |      3 |        |      0 |00:00:17.41 |     998K|   7596 |
|   1 |  UPDATE            | T    |      3 |        |      0 |00:00:17.41 |     998K|   7596 |
|*  2 |   TABLE ACCESS FULL| T    |      3 |    530K|   1079K|00:00:03.04 |   25324 |   8728 |
----------------------------------------------------------------------------------------------

...


SQL> create trigger trgt
  2  before update on t
  3  begin
  4    null;
  5  end;
  6  /

Trigger created.

SQL> update /*+ gather_plan_statistics */ t set col_for_upd = col_for_upd where gr = 2;

500000 rows updated.

Elapsed: 00:00:08.60

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

...

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |      1 |        |      0 |00:00:08.59 |     537K|    390 |
|   1 |  UPDATE            | T    |      1 |        |      0 |00:00:08.59 |     537K|    390 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    530K|    500K|00:00:00.51 |   25324 |    390 |
----------------------------------------------------------------------------------------------

...


P.S. На металинке ничего подходящего не нашёл.
14 апр 11, 01:15    [10516081]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
Владимир Бегун
Member

Откуда: Redwood Shores, CA USA
Сообщений: 1707
_Nikotin
...
Я планирую обсудить проблемы рестартов после своего отпуска, скорее всего через неделю (неделя 25 апреля). Если что-то прояснится и будет смысл это представить здесь (исключая пункты из моего профиля), я это сделаю.
15 апр 11, 22:28    [10526768]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
сосед-акцессник
Guest 		сегодня по случаю добрался до 11го оракла и провел тест из стартового поста с малой модификацией.
Результат:

+
SQL> create table t initrans 8 as select level id, 111 num from dual connect by level < 100000;

Таблица создана.

Затрач.время: 00:00:00.12
SQL> update t set id = -id;

99999 строк обновлено.

Затрач.время: 00:00:01.09

Статистика
----------------------------------------------------------
12 recursive calls
102522 db block gets
272 consistent gets
196 physical reads
26027484 redo size
678 bytes sent via SQL*Net to client
474 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99999 rows processed

SQL> commit;

Фиксация обновлений завершена.

Затрач.время: 00:00:00.00
SQL> update t set id = -id;

99999 строк обновлено.

Затрач.время: 00:00:01.32

Статистика
----------------------------------------------------------
0 recursive calls
102605 db block gets
200 consistent gets
0 physical reads
26049412 redo size
678 bytes sent via SQL*Net to client
474 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99999 rows processed

SQL> commit;

Фиксация обновлений завершена.

Затрач.время: 00:00:00.00
SQL> create trigger trgt
2 before update on t
3 begin
4 null;
5 end;
6 /

Триггер создан.

Затрач.время: 00:00:00.01
SQL> update t set id = -id;

99999 строк обновлено.

Затрач.время: 00:00:01.09

Статистика
----------------------------------------------------------
8 recursive calls
102529 db block gets
267 consistent gets
0 physical reads
26049120 redo size
678 bytes sent via SQL*Net to client
474 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99999 rows processed

SQL> commit;

Фиксация обновлений завершена.

Затрач.время: 00:00:00.00
SQL> update t set id = -id;

99999 строк обновлено.

Затрач.время: 00:00:01.14

Статистика
----------------------------------------------------------
0 recursive calls
102605 db block gets
200 consistent gets
0 physical reads
26054944 redo size
678 bytes sent via SQL*Net to client
474 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99999 rows processed

SQL> commit;

Фиксация обновлений завершена.

Затрач.время: 00:00:00.01
SQL> drop table t purge;

Таблица удалена.

Затрач.время: 00:00:00.03
SQL> spool off


Примечания такие:
1) 8 - это просто тык.
На первом прогоне было 100, понизил до 8 и этот результат снял.
Эксперимент понижающий до воспроизведения исходного поведения не проводился ввиду спешки (база чужая).
По этой же причине не снят баннер. Но, чес-слово - там была кака-то из последних 11G R2 под линухом.

2) все-таки веры в то, что в первоначальной постановке речь идет именно о рестарте "в смысле Кайта", у меня так и нет.
15 апр 11, 22:29    [10526776]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2957
сосед-акцессник
сегодня по случаю добрался до 11го оракла и провел тест из стартового поста с малой модификацией.
Результат:

...

Примечания такие:
1) 8 - это просто тык.
На первом прогоне было 100, понизил до 8 и этот результат снял.
Эксперимент понижающий до воспроизведения исходного поведения не проводился ввиду спешки (база чужая).
По этой же причине не снят баннер. Но, чес-слово - там была кака-то из последних 11G R2 под линухом.

Попробовал у себя (11.2) - ни 8 ни 100 никак не влиюят на redo.

сосед-акцессник
2) все-таки веры в то, что в первоначальной постановке речь идет именно о рестарте "в смысле Кайта", у меня так и нет.

Что имеется в виду под "в смысле Кайта" ?

Триггерами это не поймать - потому как пропадает эффект, но можно, например, посмотреть на результат

alter session set events '10014 trace name context forever; name savepoints';

*** 2011-04-17 18:54:41.464
*** SESSION ID:(131.36159) 2011-04-17 18:54:41.464
*** CLIENT ID:() 2011-04-17 18:54:41.464
*** SERVICE NAME:() 2011-04-17 18:54:41.464
*** MODULE NAME:(SQL*Plus) 2011-04-17 18:54:41.464
*** ACTION NAME:() 2011-04-17 18:54:41.464
 
NO SAVEPOINT FOR CURRENT PROCESS
ROLL BACK TO SAVE POINT - xid: 0x0003.000.000053be
ROLL BACK TO SAVE POINT - xid: 0x0003.000.000053be

*** 2011-04-17 18:54:42.275
ROLL BACK TO SAVE POINT - xid: 0x0003.000.000053be
17 апр 11, 19:04    [10530420]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
сосед-акцессник
Guest
_Nikotin
Что имеется в виду под "в смысле Кайта" ?

Хе-хе, вопрос оказался "интересный"
По наивности, в момент объявления "смысла по Кайту", я полагал, что он определен в этих трех постах:
http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html

http://tkyte.blogspot.com/2005/08/part-ii-seeing-restart.html

http://tkyte.blogspot.com/2005/09/part-iii-why-is-restart-important-to.html

В них совмещены публичная попытка теоретического объяснения - как устроен мир рестарта, с практическим руководством - как по виду запроса или триггера определить - может ли в такой ситуации он произойти.

Под смыслом понималась достаточно тщательно выписанная там теоретическая моделька.
Характеристики модельки:
1) Рестрарт происходит на случайной строке, среди попадающих под изменение
2) рестарт стейтмента всегда и обязательно полный, т.к. иначе последствия апдейта непредсказуемы. Этот момент центральный и критичный, определяющий существо модельки Кайта.
3) третьего прохода (повторного рестарта) не бывает

В качестве следствия из них рискну предложить такое грубое утверждение:
Если размер редо для оператора без рестрарта принять за 1, то размер редо для оператора, испытавшего рестарт представляет собой случайную величину > 1 и <=2

В обсуждаемых примерах смущает то, что речь идет о коэффициенте "строго 2"
Это должно означать, что утверждение проваливается всегда на "последней строке", то есть обязательно доходит до конца, прежде чем полностью перезапуститься. Что требует объяснения или поиска иного механизма удвоения редо.

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

Возвращяюсь к "смыслу по Кайту".
Оказалось, что здесь
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2599480800346313755

он наотрез отказался от обсуждения темы в модельных терминах. Тем самым, косвенно дезавуировав заявленный в "трех постах" смысл.
Дело свелось к такому примерно выражению: так оно устроено или не так - это не тот вопрос на котором надо концентрироваться. Надо знать только то, что "оно" вообще есть.
В контексте вопроса "что делать с before-триггерами" это имеет практическое значение.
В конексте же вопроса "что делать размером redo", такая позиция, на мой взгляд, делает бессмысленным применением термина "рестарт".





PS
Постараюсь в ближайшие дни добраться до версии, на к-рой проводился эксперимент, снять баннер и, м.б. что-то еще сделать...
18 апр 11, 03:10    [10531440]     Ответить | Цитировать Сообщить модератору
 Re: restart в 11g и как с ним бороться  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2957
 		Понятие рестарта гораздо проще: если во время выполнения statement происходит внутренняя ошибка (вызывающая restart), то откатывается часть/все изменения и попытка повторяется. В некотороых ситуациях, если количество рестартов больше 5000, выпадает ORA-600, например 10428755.
В некоторых - нет:
+ test
CREATE SMALLFILE UNDO TABLESPACE UNDO_TEST DATAFILE 'F:\\oracle\DB\ORCL\UNDO_TEST.ora' SIZE 5M;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDO_TEST;
drop table t0;
create table t0 as select 0 n from dual;
drop table t1;
create table t1 as select lpad('x',4000,'x') x from dual;
create or replace trigger trg1
before update on t1
for each row
declare
  pragma autonomous_transaction;
begin
  update t0 set n = n + 1;
  commit;
end;
/
declare
  t0 timestamp;
  t1 timestamp;
  n  pls_integer;
begin
  for i in 1 .. 10 loop
    update t0 set n = 0;
    commit;
    t0 := systimestamp;
    for i in 1 .. 100 loop
      update t1 set x=x;
    end loop;
    t1 := systimestamp;
    commit;
    select n into n from t0;    
    dbms_output.put_line('n = '||rpad(n, 7, ' ')||' t1-t0 = '||substr(t1-t0, 15, 9));
  end loop;
end;
/
+ result
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

...

SQL> declare
  2    t0 timestamp;
  3    t1 timestamp;
  4    n  pls_integer;
  5  begin
  6    for i in 1 .. 10 loop
  7      update t0 set n = 0;
  8      commit;
  9      t0 := systimestamp;
 10      for i in 1 .. 100 loop
 11        update t1 set x=x;
 12      end loop;
 13      t1 := systimestamp;
 14      commit;
 15      select n into n from t0;
 16      dbms_output.put_line('n = '||rpad(n, 7, ' ')||' t1-t0 = '||substr(t1-t0, 15, 9));
 17    end loop;
 18  end;
 19  /
n = 112     t1-t0 = 00:00.015
n = 112     t1-t0 = 00:00.016
n = 103     t1-t0 = 00:00.015
n = 112     t1-t0 = 00:00.016
n = 112     t1-t0 = 00:00.063
n = 112     t1-t0 = 00:00.031
n = 131299  t1-t0 = 00:45.031
n = 123     t1-t0 = 00:00.078
n = 1684    t1-t0 = 00:00.610
n = 4978    t1-t0 = 00:01.875

PL/SQL procedure successfully completed.


То что описывает Кайт - restart, который происходит для обеспечения read/write consistency (немного странной consistency в случае statement типа update t set x = x + 1 когда restart не происходит).

сосед-акцессник
3) третьего прохода (повторного рестарта) не бывает

Бывает.

сосед-акцессник
В качестве следствия из них рискну предложить такое грубое утверждение:
Если размер редо для оператора без рестрарта принять за 1, то размер редо для оператора, испытавшего рестарт представляет собой случайную величину > 1 и <=2

Слишком грубое. Размер redo зависит не только от изменяемых строк. В обсуждаемых примерах redo был выбран просто как показатель того что "что-то не так".

сосед-акцессник
В обсуждаемых примерах смущает то, что речь идет о коэффициенте "строго 2"

Про "строго 2" не понял где такое говорилось.

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

Завязаны :) согласованность тут не при чем.

сосед-акцессник
Дело свелось к такому примерно выражению: так оно устроено или не так - это не тот вопрос на котором надо концентрироваться. Надо знать только то, что "оно" вообще есть.

В общем случае - да. Иногда можно попробовать получить "ведические знания" доступными средствами, ни с какой практической целью. Разве что попытаться спрогнозировать на их основе то или иное поведение.

сосед-акцессник
В контексте вопроса "что делать с before-триггерами" это имеет практическое значение.
В конексте же вопроса "что делать размером redo", такая позиция, на мой взгляд, делает бессмысленным применением термина "рестарт".

С одной стороны - да, то что делает оракл - делает прозрачно. Но существует проблема performance.
В итоге, мне больше чем размер redo понравился A-rows из display_cursor как индикатор подозрительности.
18 апр 11, 21:06    [10533473]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить