我們正在對表“application_audit”中的應用程式進行審核。
我正在嘗試撰寫存盤程序以從該表中洗掉我們不再需要的記錄。
到目前為止，我已經撰寫了下面的存盤程序，但我發現當要洗掉的行數超過 100k 時需要花費很多時間。
您能幫我實作并行會話或優化以下存盤程序中的洗掉查詢以加快執行速度。
在生產中，該表在任何給定時間點至少有 500 萬行，據我所知，如果我們每天執行這個存盤程序，那么至少有 10 萬條記錄要洗掉。
在下面的查詢中，COMPONENT_NAME='REQUESTPURGE' 意味著對于那個特定的請求號清除已經發生，并且我們的活動資料庫實體中沒有該請求號的請求資料，因此“application_audit”表中具有該請求號的所有記錄都符合條件洗掉。
存盤程序：
create or replace PROCEDURE APPLICATION_AUDIT_PURGE_RECORD
IS
purgewait number := 30;
BEGIN
DBMS_OUTPUT.PUT_LINE('Application audit purge started with purge wait value as '||purgewait||' days');
delete from application_audit where id in (select id from application_audit where request_number in (select request_number from application_audit where COMPONENT_NAME='REQUESTPURGE' and trunc(timestamp) < trunc(sysdate - purgewait)));
END APPLICATION_AUDIT_PURGE_RECORD;
桌子：
CREATE TABLE "APPLICATION_AUDIT" (
"ID" NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
"MESSAGE_TYPE" VARCHAR2(64 CHAR),
"COMPONENT_NAME" VARCHAR2(64 CHAR),
"USERNAME" VARCHAR2(32 CHAR),
"TIMESTAMP" TIMESTAMP (6) WITH TIME ZONE NOT NULL,
"REQUEST_NUMBER" VARCHAR2(64 CHAR),
"MODULE_NAME" VARCHAR2(256 CHAR),
"PROCESS_NAME" VARCHAR2(256 CHAR),
"VERSION" VARCHAR2(64 CHAR),
"TASK" VARCHAR2(64 CHAR),
"ERROR_CODE" VARCHAR2(256 CHAR),
"ERROR_MESSAGE" VARCHAR2(4000 CHAR),
"MESSAGE" VARCHAR2(4000 CHAR)
)
Edit1：更改存盤程序中的洗掉陳述句并使用索引顯著減少了執行時間。
更新了存盤程序中的洗掉陳述句：
DELETE FROM APPLICATION_AUDIT WHERE REQUEST_NUMBER IN (SELECT APPLICATION_AUDIT.REQUEST_NUMBER FROM APPLICATION_AUDIT WHERE APPLICATION_AUDIT.REQUEST_NUMBER != 'null' AND APPLICATION_AUDIT.MESSAGE_TYPE='INFO' AND APPLICATION_AUDIT.COMPONENT_NAME='REQUESTPURGE' AND APPLICATION_AUDIT.TASK='DeleteRequest' AND TRUNC(APPLICATION_AUDIT.TIMESTAMP) < TRUNC(SYSDATE - v_reqnumpurgewait));
DELETE FROM APPLICATION_AUDIT WHERE REQUEST_NUMBER = 'null' AND TRUNC(APPLICATION_AUDIT.TIMESTAMP) < TRUNC(SYSDATE - v_purgewait);
索引創建查詢：
CREATE INDEX APPLICATION_AUDIT_IDX1 ON APPLICATION_AUDIT (COMPONENT_NAME, TIMESTAMP, (NVL(REQUEST_NUMBER,'null')));
CREATE INDEX APPLICATION_AUDIT_IDX2 ON APPLICATION_AUDIT (NVL(REQUEST_NUMBER,'null'));
uj5u.com熱心網友回復：
我認為找到一行就足以component_name = 'REQUESTPURGE'洗掉具有相同請求號的所有行。這意味著單獨的 component_name 并不能告訴我們是否洗掉一行。否則我會建議在這里使用表磁區。
照原樣，我能想到的只是提供適當的索引。不過，首先，您的查詢可以簡化為：
delete from application_audit
where request_number in
(
select request_number
from application_audit
where component_name = 'REQUESTPURGE'
and timestamp < trunc(sysdate - purgewait)
);
我為此宣告建議的索引：
create index idx1 on application_audit (component_name, timestamp, request_number);
create index idx2 on application_audit (request_number);
uj5u.com熱心網友回復：
洗掉 500 萬條記錄不應該那么耗時。
話雖如此，您可以嘗試向 DELETE 陳述句添加并行提示。
首先啟用
ALTER SESSION ENABLE PARALLEL DML;
如果這沒有幫助，您可以查看：
禁用表上的索引
但是，當然，在洗掉運行時，任何需要和使用這些索引的查詢都會變慢。因此，您只是在為（很多）其他人交易一個緩慢的陳述。之后你必須重建它們，這將花費（可能很長）時間。
您可以通過 SQL 或 rowid 查看分塊
如果這些都沒有足夠的幫助，您可能需要尋找更激進的解決方案。
比如把想要保存的資料保存在臨時表中。然后洗掉當前表并重命名臨時表。例如：
create table tmp as select ...data you want to keep... from old_tab;
drop old_tab;
rename tmp to old_tab;
-- run grants, indexes etc. that were on the original table
...
但是您需要中斷才能執行此操作。
我建議通過解釋計劃或跟蹤來追蹤瓶頸首先發生的位置，因為如果 500 萬次洗掉需要很長時間，這聽起來像你有一個潛在的問題
uj5u.com熱心網友回復：
我認為您的 DELETE 查詢可能被過度簡化為 -
DELETE FROM application_audit
WHERE COMPONENT_NAME = 'REQUESTPURGE'
AND TRUNC(timestamp) < TRUNC(SYSDATE - purgewait);
您也可以嘗試在 COMPONENT_NAME 列上建立索引。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/461930.html
上一篇：將資料從資料庫加載到字串