Блог

Главная - Вопросы по программированию - Массовый сбор выборки PL SQL не завершается

Массовый сбор выборки PL SQL не завершается

#sql #oracle #plsql #procedure #plsqldeveloper

#sql #Oracle #plsql #процедура #plsqldeveloper

Вопрос:

Я выполнил эту процедуру для массового удаления данных (35 миллионов записей). Можете ли вы понять, почему эта процедура pl / sql выполняется без завершения, а строки не удаляются?

 create or replace procedure clear_logs
as 
 CURSOR c_logstodel IS SELECT * FROM test where id=23;
 TYPE typ_log is table of test%ROWTYPE;   
 v_log_del typ_log;
BEGIN
OPEN c_logstodel;
LOOP   
FETCH c_logstodel BULK COLLECT INTO v_log_del LIMIT 5000;   
EXIT WHEN c_logstodel%NOTFOUND;
    FORALL i IN v_log_del.FIRST..v_log_del.LAST   
    DELETE FROM test WHERE id =v_log_del(i).id;   
    COMMIT;
END LOOP;
CLOSE c_logstodel;
END clear_logs;

Комментарии:

1. Вероятно, процедура выполняется очень медленно. Проверьте, что делает выполняющийся сеанс, пока вы ожидаете его завершения, используйте v $session, чтобы увидеть, что он делает в данный момент, v $session_event, чтобы увидеть, где он потратил время на ожидание (блокировки? Физический ввод-вывод?), dbms_xplan.display_cursor, чтобы увидеть, как он пытается выполнить ваш SQL (если он не просто ожидает блокировок). 35 миллионов строк — это слишком много, чтобы ожидать быстрого удаления, особенно в цикле с фиксациями

2. @AndrewSayer, вы имеете в виду, что 35 миллионов строк в одной транзакции будут быстрее? (Обычно я бы фиксировал для каждых 50 000 строк.)

3. Один оператор, вероятно, будет быстрее. Очевидно, что есть преимущества для его пакетной обработки, например, одновременной блокировки только нескольких строк, но если их стоит удалить, они, вероятно, не должны использоваться в любом случае. В любом случае отправной точкой должно быть выяснение того, куда уходит ваше время, ознакомьтесь с тем, на что я ранее указывал вам, если ваш сеанс ожидает другого сеанса, тогда никакое перекодирование не поможет — вам нужен другой сеанс, чтобы уйти с пути.

4. Удаление обходится дорого. Если 35 миллионов записей составляют значительную часть вашей таблицы, вам может быть лучше создать временную таблицу, скопировать строки, которые вы хотите сохранить, обрезать исходную таблицу, а затем скопировать строки обратно в исходную таблицу.

5. Измените EXIT WHEN c_logstodel%NOTFOUND на EXIT WHEN v_log_del.count = 0; , чтобы не пропустить некоторые строки в последнем пакете. Хотя я не думаю, что это связано с вашей проблемой производительности.

Ответ №1:

Добавление rowid вместо имени столбца, exit when v_delete_data.count = 0; вместо EXIT WHEN c_logstodel%NOTFOUND; и изменение лимита блока до 50 000 позволило скрипту очистить 35 миллионов строк за 15 минут

 create or replace procedure clear_logs
as 
 CURSOR c_logstodel IS SELECT  rowid FROM test where id=23;
 TYPE typ_log is table of rowid index by binary_integer;
 v_log_del typ_log;
BEGIN
OPEN c_logstodel;
LOOP   
FETCH c_logstodel BULK COLLECT INTO v_log_del LIMIT 50000;   
 exit when v_log_del.count = 0;
    FORALL i IN v_log_del.FIRST..v_log_del.LAST   
    DELETE FROM test WHERE  rowid =v_log_del(i);  
    exit when v_log_del.count = 0;
    COMMIT;
END LOOP;
COMMIT;
CLOSE c_logstodel;
END clear_logs;

Ответ №2:

Во-первых, при использовании МАССОВОГО СБОРА LIMIT X значение %NOTFOUND приобретает несколько неожиданное значение. В этом случае %NOTFOUND фактически означает, что Oracle не удалось получить X строк. (Я предполагаю, что технически вы всегда получаете следующий 1, и в нем говорится, что он не смог заполнить буфер 1 строки.) Просто переместите ВЫХОД, КОГДА %NOTFOUND после FORALL . Но на самом деле нет причин извлекать данные, а затем удалять извлеченные строки. Хотя один оператор будет значительно быстрее, 35 миллионов строк потребуют значительного пространства для отката. Существует решение для захоронения.
Хотя обычно не используемые операторы Delate генерируют rownum, как и selects . Это значение может быть использовано пользователем для ограничения количества обрабатываемых строк. Поэтому, чтобы разбить на заданный размер фиксации, просто ограничьте число строк при удалении:

 create or replace procedure clear_logs
as 
    k_max_rows_per_interation constant integer := 50000;
begin
    loop   
        delete 
          from test 
         where id=23
           and rownum <= k_max_rows_per_interation;
        exit when sql%rowcount < k_max_rows_per_interation;
        commit;
    end loop;
    commit;
end;

Как указывает @Stilgar, удаления являются дорогостоящими, то есть медленными, поэтому их решение может быть лучше. Но это имеет то преимущество, что оно по существу не выводит таблицу из строя полностью во время операции. ПРИМЕЧАНИЕ: я склонен использовать гораздо больший размер интервала фиксации, обычно около 400 000-300 000 строк. Я предлагаю вам поговорить с вашим администратором базы данных, чтобы узнать, каким, по их мнению, должно быть это ограничение. Помните, что их работа заключается в правильном определении размера пространства отката для типичных операций. Если это нормально в вашей работе, они должны установить его правильно. Если вы можете получить пространство для отката для 35 миллионов удалений, то это самое быстрое, что вы получите.