Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Июнь 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : производительность оператора UPDATE


Информационный Канал Subscribe.Ru

Выпуск 38

Производительность оператора UPDATE

Этот выпуск посвящен обсуждению производительности достаточно сложного оператора UPDATE с вложенными. По мотивам очередного ответа Тома Кайта на вопрос, заданный в июне 2003 года.

Кстати, выпуск этот, в каком-то смысле, юбилейный. Прошел год с момента выхода первого выпуска рассылки. Это было 6 июня 2002 года. С удовольствием приму поздравления и пожелания по этому поводу ;)

Как повысить производительность этого оператора UPDATE в версии 8.1.7?

Привет, Том!

Я использую следующий оператор update для таблицы t1:

 
update t1 
set (amount, enter_date) = 
   (select amount, trunc(enter_date) 
    from t2 
    where t2.id = t1.id 
    and t2.code in (select code from t3 where t3.sign = 1) 
    and t2.enter_date = (select max(enter_date) from t2 
                         where t2.id = t1.id 
                         and t2.code in (select code from t3 
                                       where t3.sign = 1)));

В таблице t1 - около 10000 строк, а в таблице t2 - около 2 миллионов. Я пытаюсь получить в таблице t1 информацию о сумме и дате последней банковской транзакции для всех идентификаторов из таблицы t2 (таблицы транзакций). Таблица t3 (код транзакции) определяет, какие транзакции следует учитывать.

Этот оператор update выполняется достаточно долго. Что бы ты порекомендовал для повышения производительности этого оператора update?

Ответ Тома Кайта

Ну, этот оператор update, при наличии соответствующих индексов и использовании стоимостного оптимизатора, должен работать "сравнительно быстро". На моей персоналке он работает менеее 2 минут - разве это долго?

В любом случае, я сымитировал вашу ситуацию так:

 
create table t1 ( id int, amount int, enter_date date ); 
 
insert into t1 
select rownum, object_id, sysdate 
  from all_objects big_table 
 where rownum <= 100000; 
 
alter table t1 add constraint t1_pk primary key(id); 
 
analyze table t1 compute statistics 
for table 
for all indexes 
for all indexed columns; 
 
create table t2 ( id int, amount int, enter_date date, code int ); 
begin 
    for i in 1 .. 20 
    loop 
        insert /*+ APPEND */ into t2 
        select id, amount-i, enter_date-i, mod(i,2) 
          from t1; 
        commit; 
    end loop; 
end; 
/ 
 
create index t2_idx on t2(id,enter_date); 
 
analyze table t2 compute statistics 
for table 
for all indexes 
for all indexed columns; 
 
create table t3 ( code int, sign int primary key); 
insert into t3 
select rownum, rownum 
  from all_users; 
 
analyze table t3 compute statistics 
for table 
for all indexes 
for all indexed columns;

Итак, у меня тоже 100000 строк, 2000000 строк и отдельная таблица для поиска кода транзакции. Я создал следующее представление:

 
create or replace view v 
as 
select rid, amount, trunc(enter_date) enter_date 
  from ( select t1.rowid rid, t2.amount, t2.enter_date, 
                max(t2.enter_date) over (partition by t2.id) max_enter_date 
           from t1, (select amount, enter_date, id 
                       from t2 
                      where code in (select code from t3 where sign = 1)) t2 
          where t1.id = t2.id (+)) 
 where (enter_date = max_enter_date) 
    or (enter_date is null and max_enter_date is null) 
/

И попробовал выполнить изменение процедурно:

 
declare 
    type ridArray is table of rowid index by binary_integer; 
    type numArray is table of number index by binary_integer; 
    type datArray is table of date index by binary_integer; 
    l_rid ridArray; 
    l_amt numArray; 
    l_ed  datArray; 
    l_array_size number := 100; 
    l_cnt        number := 0; 
    cursor c is select rid, amount, enter_date from v; 
begin 
    open c; 
    loop 
        fetch c bulk collect 
         into l_rid, l_amt, l_ed 
        limit l_array_size; 
        forall i in 1 .. l_rid.count 
            update t1 set amount = l_amt(i), enter_date = l_ed(i) 
             where rowid = l_rid(i); 
        l_cnt := l_cnt + l_rid.count; 
        dbms_application_info.set_client_info( 'updated ' || l_cnt || ' rows' ); 
        exit when c%notfound; 
    end loop; 
    close c; 
end; 
/

Затем попробовал сделать то же самое одним оператором update:

 
update t1 
set (amount, enter_date) = 
   (select amount, trunc(enter_date) 
    from t2 
    where t2.id = t1.id 
    and t2.code in (select code from t3 where t3.sign = 1) 
    and t2.enter_date = (select max(enter_date) from t2 
                         where t2.id = t1.id 
                         and t2.code in (select code from t3 
                                       where t3.sign = 1)));

Для PL/SQL-блока потребовалось:

 
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.02       0.03          0          0          1           0 
Execute      1     71.28      80.10      15610     106393     101914           1 
Fetch        0      0.00       0.00          0          0          0           0 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total        2     71.30      80.14      15610     106393     101915           1

около 80 секунд, а для update:

 
update t1 
set (amount, enter_date) = 
   (select amount, trunc(enter_date) 
    from t2 
    where t2.id = t1.id 
    and t2.code in (select code from t3 where t3.sign = 1) 
    and t2.enter_date = (select max(enter_date) from t2 
                         where t2.id = t1.id 
                         and t2.code in (select code from t3 
                                       where t3.sign = 1))) 
 
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.01          0          0          0           0 
Execute      1     62.90     108.38      13665    2728542     204504      100000 
Fetch        0      0.00       0.00          0          0          0           0 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total        2     62.90     108.39      13665    2728542     204504      100000

немного больше...

Так что, можете попробовать процедурный подход, но, при наличии соответствующих индексов, изменение 100000 строк не должно вызывать особых проблем.

Почему SQL выполняется медленнее, чем PL/SQL?

Том, что в этом примере делает его исключением из твоего простого правила: один SQL-оператор работает быстрее и использует меньше ресурсов, чем процедурное решение?

Ответ Тома Кайта

Дело в сути изменения: в нем используется много коррелированных подзапросов для поиска max enter_date и т.п. Это усложняет при оптимизации выбор любого другого плана кроме вложенных циклов (именно так выполняются коррелированные подзапросы).

Я бы хотел сделать следующее:

 
UPDATE(  
select * 
  from ( select t1.amount t1_amt, t1.enter_date t1_ed,  
                t2.amount t2_amt, t2.enter_date t2_ed, 
                max(t2.enter_date) over (partition by t2.id) max_enter_date 
           from t1, (select amount, enter_date, id 
                       from t2 
                      where code in (select code from t3 where sign = 1)) t2 
          where t1.id = t2.id (+)) 
 where (enter_date = max_enter_date) 
    or (enter_date is null and max_enter_date is null) 
) 
set t1_amt = t2_amt, t1_ed = t2_ed;

Но, поскольку нет защиты ключом (key preservation) и используются аналитические функции (что подразумевает "упорядочивание" подставляемого представления), так делать нельзя. Я хотел бы выбирать строки с помощью именно такого запроса, поскольку он выполняет множественные операции (что ускоряет работу при большом количестве строк), а не 100000 сложенных циклов...

Итак, чтобы "изменить результаты этого запроса", мне, к сожалению (в 8i), приходится использовать запрос + update. Выполняя множественные операции...

В версии 9i появился оператор MERGE:

 
merge into t1 
using ( 
select rid, amount, trunc(enter_date) enter_date 
  from ( select t1.rowid rid, t2.amount, t2.enter_date, 
                max(t2.enter_date) over (partition by t2.id) max_enter_date 
           from t1, (select amount, enter_date, id 
                       from t2 
                      where code in (select code from t3 where sign = 1)) t2 
          where t1.id = t2.id (+)) 
 where (enter_date = max_enter_date) 
    or (enter_date is null and max_enter_date is null) 
) t2 
on ( t1.rowid = t2.rid ) 
when matched then 
    update set t1.amount = t2.amount, t1.enter_date = t2.enter_date 
when not matched then 
    insert (id) values ( null ) 
 
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.01       0.01          0          0          0           0 
Execute      2     43.03      53.45      16486       7558     102809      100000 
Fetch        0      0.00       0.00          0          0          0           0 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total        3     43.05      53.46      16486       7558     102809      100000 
 
Misses in library cache during parse: 1 
Optimizer goal: CHOOSE 
Parsing user id: 137 
 
Rows     Row Source Operation 
-------  --------------------------------------------------- 
      2  MERGE  (cr=7558 r=16486 w=10807 time=53444549 us) 
 100000   VIEW  (cr=7531 r=16477 w=10807 time=49308344 us) 
 100000    HASH JOIN OUTER (cr=7531 r=16477 w=10807 time=48646484 us) 
 100000     VIEW  (cr=7187 r=16139 w=10807 time=46776991 us) 
1000000      WINDOW SORT (cr=7187 r=16139 w=10807 time=45026596 us) 
1000000       HASH JOIN OUTER (cr=7187 r=5331 w=0 time=27551523 us) 
 100000        INDEX FAST FULL SCAN OBJ#(37458) (cr=215 r=210 w=0 time=235332 us)(object id 37458) 
1000000        VIEW  (cr=6972 r=5121 w=0 time=18959603 us) 
1000000         HASH JOIN  (cr=6972 r=5121 w=0 time=15955318 us) 
      1          TABLE ACCESS BY INDEX ROWID OBJ#(37461) (cr=2 r=0 w=0 time=97 us) 
      1           INDEX UNIQUE SCAN OBJ#(37462) (cr=1 r=0 w=0 time=58 us)(object id 37462) 
2000000          TABLE ACCESS FULL OBJ#(37459) (cr=6970 r=5121 w=0 time=4275638 us) 
 100000     TABLE ACCESS FULL OBJ#(37457) (cr=344 r=338 w=0 time=376606 us)

Он делает то же самое, что и мой PL/SQL-код, но немного эффективнее.

Аналогичная проблема

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

Моя проблема в том, что первоначально не была учтена возможность рождения однополых двойняшек. В таблице есть столбцы CLIID, пол и дата рождения. Я просто хочу пометить возможные дублирующиеся строки (часть из них может быть связана с двойняшками, а часть - с опечатками при наборе) по каждому CLIID. В таблице - около 150000 записей, из которых около 10000 потенциально могут быть дублирующимися.

Я написал следующий оператор:

 
update testchild tcx set pd = (select "rank" from  
(select cliid,id,fname, rank() over (partition by cliid order by fname) "rank"  
from testchild tc2 where exists 
  (select * from testchild tc3 where tc3.cliid=tc2.cliid and cliid > 1000000000  
group by cliid having count(*) > 1)) x 
where x.id = tcx.id)    
 
Plan Table 
------------------------------------------------------------------------------------------ 
| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstart| Pstop | 
------------------------------------------------------------------------------------------ 
| UPDATE STATEMENT          |                    |   152K|  745K|    284 |       |       | 
|  UPDATE                   |TESTCHILD           |       |      |        |       |       | 
|   TABLE ACCESS FULL       |TESTCHILD           |   152K|  745K|    284 |       |       | 
|   VIEW                    |                    |     7K|  193K|    319 |       |       | 
|    WINDOW SORT            |                    |     7K|  149K|    319 |       |       | 
|     FILTER                |                    |       |      |        |       |       | 
|      TABLE ACCESS FULL    |TESTCHILD           |     7K|  149K|    284 |       |       | 
|      FILTER               |                    |       |      |        |       |       | 
|       SORT GROUP BY NOSORT|                    |     1 |    9 |      1 |       |       | 
|        FILTER             |                    |       |      |        |       |       | 
|         INDEX RANGE SCAN  |TC_CLIID            |     1 |    9 |      1 |       |       | 
------------------------------------------------------------------------------------------

Сам запрос выполняется около 6 секунд. Если я выполняю CREATE TABLE AS SELECT, а затем изменяю таблицу testchild на основе этой "временной" таблицы, требуется менее минуты. Добавление индекса по ID для временной таблицы особой роли не играет. А вот если выполнять представленный выше полный update... Он начался в 11 утра вчера, и сегодня в 9 утра еще выполняется. Количество consistent gets измеряется миллиардами. Все это работает в версии 9i на персоналке.

Что происходит, не подскажешь?

Ответ Тома Кайта

Проблема та же.

Этот 6-секундный запрос выполняется 152k раз (представленная статистика свидетельсвует, что изменять в таблице придется 152k строк) Представленный ранее прием позволит выполнять 6-секундный запрос блоком (IN BULK), а не построчно, и изменять его результаты.

Прекрасное решение

Я попробовал предложенное процедурное решение, и оно потребовало около 60 секунд. Аналитические функции - это замечательно.

У меня, однако, осталась одна проблема. Вместо создания представления я попытался создать курсор:

 
CURSOR last_payment_cur IS  
select rid, amount, trunc(enter_date) enter_date 
  from ( select t1.rowid rid, t2.amount, t2.enter_date, 
                max(t2.enter_date) over (partition by t2.id) max_enter_date 
           from t1, (select amount, enter_date, id 
                       from t2 
                      where code in (select code from t3 where sign = 1)) t2 
          where t1.id = t2.id (+)) 
 where (enter_date = max_enter_date) 
    or (enter_date is null and max_enter_date is null);

В результате было получено следующее сообщение об ошибке:

 
LINE/COL ERROR 
51/32    PLS-00103: Encountered the symbol "(" when expecting one of the  
following: , from

Но этот же запрос в окне SQL*Plus прекрасно работает. Почему нельзя создать курсор?

Ответ Тома Кайта

Я специально создал представление, увидев в вопросе упоминание версии 8.1.7

См. главу в моей книге, посвященную аналитическим функциям. Стр. 586

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2003 Oracle Corporation

В следующем выпуске

Кое-что об интерпретации результатов трассировки и применения утилиты TKPROF. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное