performance comparision
Date: Thu, 28 Jan 2010 05:16:08 -0800 (PST)
Message-ID: <e01eea25-edef-4569-8a76-2cb21f896367_at_f12g2000yqn.googlegroups.com>
All,
at Oracle 9.2.0.8
we are aiming to delete huge rows( old data ) from a table T1
Since we can not afford outage hence CTAS and other options are ruled out.
Method being opted are ( in Test env first ) :
- Mark the rows to be deleted. i.e. Update one column, which is made exclusivley for archiving/deletion
- delete the marked rows in a batches
- Use Online redfifnition to reclaim the storage space and lower the HWM.
- Table T1 has no clobs ( columns are varchar, number, date types)
- T1 has "objid" column as primary key NUMBER datatype
- "dev" column is type NUMBER
- dev column is indexed normal B*Tree index.
- Based on the bussiness rule for "unused data" we set "dev" column to 1. Default value of "dev" column is NULL
We use the following function to set the "dev" column to 1
f1) ...FN_Mark_for_Del
UPDATE T1
SET dev = 1
WHERE start_time < (SYSDATE - no_days) and
some_business_rule..........;
Then we use the following function to delete the rows.
f2) ...FN_del_T1
FUNCTION fn_delete_T1 (batch_size IN NUMBER, max_rows IN NUMBER)
RETURN BOOLEAN
AS
cnt NUMBER;
row_count NUMBER;
BEGIN
SAVEPOINT checkhere;
SELECT COUNT (objid)
INTO row_count
FROM T1
WHERE dev = 1;
cnt := 0;
DBMS_OUTPUT.put_line
( 'Total number of rows to be deleted from T1 ['
|| LEAST (row_count, max_rows)
|| '] in batches of ['
|| batch_size
|| '] rows'
);
DBMS_OUTPUT.put_line ( 'Starting deletion at ['
|| TO_CHAR (SYSDATE, 'dd-MM-yyyy
hh24:mi:ss')
|| ']'
);
WHILE (cnt < row_count AND (cnt + batch_size <= max_rows))
LOOP
DELETE FROM T1
WHERE dev = 1 AND ROWNUM <= batch_size;
cnt := cnt + SQL%ROWCOUNT;
COMMIT;
DBMS_OUTPUT.put_line ( 'Deleted ['
|| cnt
|| '] rows from T1'
);
END LOOP;
DBMS_OUTPUT.put_line ( 'End of deletion at ['
|| TO_CHAR (SYSDATE, 'dd-MM-yyyy
hh24:mi:ss')
|| ']'
);
DBMS_OUTPUT.put_line ('Deleted [' || cnt
|| '] rows from T1'
);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO checkhere;
DBMS_OUTPUT.put_line ( 'Error processing. Sql error code is
['
|| SQLCODE
|| ']'
);
RETURN FALSE;
END fn_delete_T1;
We used f1 to mark the data for deletion :
Table Total Count Marked for Del
T1 6469204 6177615
Attempted to delete the rows in a controlled manner in following
phases.
phase-1 : batch size 500, records deleted 1000000 --> Time taken
00:05:49.09 Undo Generated 1GB,
Redo 1.5 GB
phase-2 : batch size 1000,records deleted 1000000 --> Time taken
00:07:23.8 Undo Generated 1GB,
Redo 1.5 GB
phase-3 : batch size 250, records deleted 1000000 --> Time taken
00:29:59.9 Undo Generated 1GB,
Redo 1.5 GB
phase-4 : batch size 500, records deleted 1000000 --> Time taken
00:22:23.31 Undo Generated 1GB,
Redo 1.5 GB
Done above just to benchmark the batch_size and no_of_rows deleted ..
so that we can use those
figure in live.
Can see huge difference in time in phas-1 & phase-4 ( same batch size,
same number_of_rows
deleted).
excperts from Statspack report ( phase-1):
..
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 5,465,377.22
1,039,493.31
Logical reads: 66,886.79
12,721.60
Block changes: 44,316.35
8,428.80
Physical reads: 3,898.02
741.39
Physical writes: 1,498.68
285.04
User calls: 61.01
11.60
Parses: 31.51
5.99
Hard parses: 0.01
0.00
Sorts: 0.32
0.06
Logons: 0.00
0.00
Executes: 37.43
7.12
Transactions: 5.26
..
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 94.17 In-memory Sort %: 100.00
Library Hit %: 99.99 Soft Parse %: 99.98
Execute to Parse %: 15.82 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 120.00 % Non-Parse CPU: 99.94
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 32.99 33.00
% SQL with executions>1: 91.76 91.72
% Memory for SQL w/exec>1: 84.31 84.28
Top 5 Timed Events
% Total
Event Waits Time (s)Ela Time
-------------------------------------------- ------------ -----------
CPU time
205 39.03
log file parallel write 4,494
130 24.71
db file sequential read 1,511,549
124 23.65
global cache busy 60
45 8.62
write complete waits 47
7 1.29
-------------------------------------------------------------
...
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
25,865,837 1 25,865,837.0 99.7 196.90 340.57
2165703550
Module: SQL*Plus
DECLARE RetVal BOOLEAN; BATCH_SIZE NUMBER; MAX_ROWS NUMBER
; BEGIN BATCH_SIZE := NULL; MAX_ROWS := NULL; RetVal :=
USERNAME.PKG_ARCHIVE_DEL.fn_delete_T1
( 500, 1000000 ); COMMIT;
END;
25,842,514 2,000 12,921.3 99.6 193.00 336.26
3890394561
Module: SQL*Plus
DELETE FROM T1 WHERE DEV = 1 AND ROWNUM <= :B1
excperts from Statspack report ( phase-4):
...
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 1,438,322.45
992,682.66
Logical reads: 31,594.86
21,805.73
Block changes: 11,635.91
8,030.72
Physical reads: 16,059.68
11,083.86
Physical writes: 818.52
564.92
User calls: 61.15
42.21
Parses: 32.80
22.64
Hard parses: 0.00
0.00
Sorts: 0.37
0.25
Logons: 0.06
0.04
Executes: 34.67
23.93
Transactions: 1.45
..
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 49.17 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 100.00
Execute to Parse %: 5.41 Latch Hit %: 99.68
Parse CPU to Parse Elapsd %: 54.84 % Non-Parse CPU: 99.97
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 33.05 33.06
% SQL with executions>1: 91.44 91.42
% Memory for SQL w/exec>1: 84.07 84.05
Top 5 Timed Events
% Total
Event Waits Time (s)Ela Time
-------------------------------------------- ------------ -----------
CPU time
1,271 66.76
db file sequential read 23,891,385
472 24.78
log file parallel write 4,272
121 6.37
latch free 5,188
14 .71
DFS lock handle 2,646
9 .46
-------------------------------------------------------------
..
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
46,644,584 1 46,644,584.0 99.2 1244.23 1311.65
733080457
Module: SQL*Plus
DECLARE RetVal BOOLEAN; BATCH_SIZE NUMBER; MAX_ROWS NUMBER
; BEGIN BATCH_SIZE := NULL; MAX_ROWS := NULL; RetVal :=
USERNAME.PKG_ARCHIVE_DEL.fn_delete_T1
(500, 1000000 ); COMMIT;
END;
46,621,261 2,000 23,310.6 99.2 1240.47 1307.92
3890394561
Module: SQL*Plus
DELETE FROM T1 WHERE DEV = 1 AND ROWNUM <= :B1
Questions
q1) what for phase-4 waiting ? [ as it took time 4x longer than
phase-1 for the same number of
rows & get per execution are almost double in phase-4 compared to
phase-1 with poor buffer hit ratio ]
q2) is "delayed block cleanout" playing some role here ?
q3) function used (f1) and (f2) are efficient ? something can be
suggested here to improve
the efficiency?
q4) in functions no use of rowid or cursor.. do use of these will
help.?
q5) will indexing "dev" column as bitmap index will help? (In
production env this table T1 is
subject to huge insert/update.)
Regards Received on Thu Jan 28 2010 - 07:16:08 CST