performance comparision

From: UXDBA <unixdba73_at_googlemail.com>
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 ) :

  1. Mark the rows to be deleted. i.e. Update one column, which is made exclusivley for archiving/deletion
  2. delete the marked rows in a batches
  3. 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 Received on Thu Jan 28 2010 - 07:16:08 CST

Original text of this message