Re: performance comparision

From: UXDBA <unixdba73_at_googlemail.com>
Date: Thu, 28 Jan 2010 09:56:41 -0800 (PST)
Message-ID: <d59f3a9d-42dd-42be-a203-255965fe509a_at_l19g2000yqb.googlegroups.com>



On Jan 28, 1:16�pm, UXDBA <unixdb..._at_googlemail.com> wrote:
> 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 ) :
>
> a) Mark the rows to be deleted. i.e. Update one column, which is made
> exclusivley for
> archiving/deletion
> b) delete the marked rows in a �batches
> b) Use Online redfifnition to reclaim the storage space and lower the
> HWM.
>
> Further details:
>
> 1) Table T1 has no clobs ( columns are varchar, number, date types)
> 2) T1 has "objid" column as primary key NUMBER datatype
> 3) "dev" column is type NUMBER
> 4) dev column is indexed normal B*Tree index.
> 5) 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

Thanks Joel

T1 count(*) - 6469204

and size is just 850 MB.

Regards Received on Thu Jan 28 2010 - 11:56:41 CST

Original text of this message