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