mardi 23 décembre 2014

Work with massive number of rows



1] Speed DML on Huge number of rows

1.1 Insert throughput Rate

I created these few commands to get the Insert Rate on my 10.2.0.5 AIX Power VI / NetApp :

set timing on
drop table adb.table01_1 ;
select count(1) from adb.table01 ;

-- create rate direct
create table adb.table01_1 as select * from adb.table01 ;
select blocks , bytes/1024/1024 Sz_MB from dba_segments where owner = 'OWNER' and segment_name = 'AM_TAB_01ION_1' ;

-- insert rate conventional
delete adb.table01_1 ;
insert into adb.table01_1 select * from adb.table01 ;
select blocks , bytes/1024/1024 Sz_MB from dba_segments where owner = 'OWNER' and segment_name = 'AM_TAB_01ION_1' ;

-- insert rate direct
truncate table adb.table01_1 ;
insert /*+ APPEND */ into adb.table01_1 select * from adb.table01 ;
select blocks , bytes/1024/1024 Sz_MB from dba_segments where owner = 'OWNER' and segment_name = 'AM_TAB_01ION_1' ;

drop table adb.table01_1 ;


Table dropped.

  COUNT(1)
----------
    690621

Table created.
Elapsed: 00:00:25:67 -- create rate Direct = 26 562 rows / sec [ 1.6 Million rows / min ]


    BLOCKS      SZ_MB
---------- ----------
     62464        488


690621 rows deleted.
Elapsed: 00:00:25:87

690621 rows created.
Elapsed: 00:01:03:04 -- insert rate Conventional = 10 962 rows / sec [ 0.6 Million rows / min ]


    BLOCKS      SZ_MB
---------- ----------
     62848        491

Table truncated.
Elapsed: 00:00:01:41

690621 rows created. -- insert rate Direct = 27 624 rows / sec [ 1.65 Million rows / min ]
Elapsed: 00:00:24:59

    BLOCKS      SZ_MB
---------- ----------
     62464        488

Table dropped.
Elapsed: 00:00:00:05

Conclusion : I get a not-Direct DML Insert rate of around 10 K rows per Second ; and twice more performance when using Direct SQL.

1.2 Delete massive number of rows

First, the best method, then the case when we can’t shutdown access to the target table.

1.2.1 Faster massive number of rows Delete with INSERT / RENAME

See following chapter 7] for a complete test-case.

1.2.2 Fast Delete massive number of rows with FORALL loop resumable code

We remove 25 Millions rows,
-       more 2/3rd of a table
-       and can’t afford to stop the access on the table :
Since we can’t shutdown the application, I propose this PL/SQL to fast Delete/Commit every 10 000 Rows. This can be interrupted and resumed without any arm.

We can follow the state and throughput in V$SESSION.

  • Pre-requisite :

select count(code_arr) from TABLE_01 where code_arr not in (select code_arr from t_transaction) and heure <to_date('01/08/2013', 'dd/MM/yyyy') and id_societe=7 -- 25402611 rows out of
select count(code_arr) from TABLE_01 -- 58394895 rows

  • Bulk Delete 25 Millions of rows

$ cat del_table_01_forall.sql

set timing on
DECLARE
l_code_arr      dbms_sql.NUMBER_table;

CURSOR c_table_01 is
select code_arr from table_01 where code_arr not in (select code_arr from t_transaction) and heure <to_date('01/08/2013', 'dd/MM/yyyy') and id_societe=7 ;

-- Main program

/* Count the number of DML during the execution */
v_count NUMBER;
Limit NUMBER;
Start_date  date;

BEGIN
execute immediate 'alter session set cursor_sharing = FORCE' ;

/* Variables initialization */
Limit := 10000;
v_count := 0;
Start_date  := sysdate;

  OPEN c_table_01 ;
  LOOP
    FETCH c_table_01 BULK COLLECT INTO l_code_arr LIMIT Limit;

    FORALL i in 1 .. l_code_arr.COUNT
      DELETE TABLE_01
      WHERE code_arr =  l_code_arr(i) ;

        COMMIT;
        v_count := v_count + 1;
        dbms_application_info.set_module( 'num. rows deleted : '||to_char(v_count*Limit) , null );
        dbms_application_info.set_client_info( trunc((v_count*Limit/((sysdate-Start_date)*24*60*60)),2)||' Trx/s' );
    EXIT WHEN c_table_01%NOTFOUND;
  END LOOP;
  CLOSE c_table_01 ;
  COMMIT;

END;
/

 $ nohup sqlplus system/pswir@TARG_DB @del_table_01_forall.sql &
[1] 29454


select module , client_info from v$session where sid = 628
module                                  client_info
num. rows deleted : 220000        2075.47 Trx/s

ü  Conclusion

Throughput rate was over 2000 DEL / Sec.
Program Status is shown in V$session :
select module , client_info from v$session where sid = 368


2] Archive those old Data

We are using Datapump, and we want to delete more than 80 % of the table rows.

2.1 Estimate Space Gain

Using business rules criteria where date_transaction < '01/01/2013'

ü  Tables should be analyzed prior to run this


-- gain
select 'select  '''||table_name||''' "TABLE", '''||num_rows||' rows'' "Num_Rows" , '''||ceil(blocks*8192/1024/1024)||' MB'' "Total Size" ,
count(1) "Archived_Num_Rows" ,
ceil( '||ceil(blocks*8192/1024/1024)||'*(count(1)/'||num_rows||') ) "Gain_MB" from '||owner||'.'||table_name||'
where DATE_TRANSACTION < ''01/01/2013'' UNION '
from dba_tables where owner = 'APP_OWNER' and table_name like 'HISTO%' and num_rows != 0 order by num_rows desc


Remove last UNION and execute


select  'HISTO_PERS' "TABLE", '50064506 rows' "Num_Rows" , '10845 MB' "Total Size" ,
count(1) "Archived_Num_Rows" ,
ceil( 10845*(count(1)/50064506) ) "Gain_MB" from APP_OWNER.HISTO_PERS
where DATE_TRANSACTION < '01/01/2013' UNION
…
where DATE_TRANSACTION < '01/01/2013' UNION
select  'HISTO_PE_FICHIER' "TABLE", '5 rows' "Num_Rows" , '1 MB' "Total Size" ,
count(1) "Archived_Num_Rows" ,
ceil( 1*(count(1)/5) ) "Gain_MB" from APP_OWNER.HISTO_PE_FICHIER
where DATE_TRANSACTION < '01/01/2013'


TABLE
Num_Rows
Total Size
Archived_Num_Rows
Gain_MB
HISTO_TAB_01
25355054 rows
3832 MB
15876990
2400
HISTO_TAB_02
15992 rows
2 MB
10532
2
HISTO_TAB_03
7 rows
1 MB
4
1
HISTO_TAB_04
10927577 rows
1381 MB
7314861
925
HISTO_TAB_05
10604 rows
2 MB
0
0
HISTO_TAB_06
18657 rows
8 MB
0
0
HISTO_TAB_07
5232314 rows
522 MB
3165938
316
HISTO_TAB_08
22866424 rows
4529 MB
14344782
2842
HISTO_TAB_09
2121484 rows
217 MB
1000101
103
HISTO_TAB_10
2136 rows
1 MB
992
1
HISTO_TAB_11
10919238 rows
1574 MB
6898810
995
HISTO_TAB_12
214960 rows
25 MB
125350
15
HISTO_TAB_13
10102 rows
2 MB
4606
1
HISTO_TAB_14
50064506 rows
10845 MB
31747388
6878
HISTO_TAB_14_TAB_05
8653 rows
3 MB
0
0
HISTO_TAB_14_TMP
58 rows
77 MB
0
0
HISTO_TAB_15
323248 rows
171 MB
0
0
HISTO_TAB_16
5 rows
1 MB
0
0
HISTO_TAB_17
10914958 rows
1695 MB
6898105
1072
HISTO_TAB_18
214 rows
1 MB
0
0
HISTO_TAB_19
11218 rows
2 MB
7904
2
HISTO_TAB_20
16870508 rows
2290 MB
10315364
1401
HISTO_TAB_21
13689026 rows
1829 MB
8668626
1159
HISTO_TAB_22
219285 rows
21 MB
135321
13

2.2 Export Old Data [ expdp with QUERY clause ]

This is an INCLUDE :TABLE only export :


$ time expdp system SCHEMAS=APP_OWNER DUMPFILE=<BASE>_DP_DIR:BASE.APP_OWNER.ARCH.HISTO.dp.dmp LOGFILE=<BASE>_DP_DIR:expdp_BASE.APP_OWNER.ARCH.HISTO.dp.log PARALLEL=2 INCLUDE=TABLE:"\"LIKE 'HISTO_%'\"" QUERY="\"where date_transaction < '01/01/2013'\""


Import them if needed to another DB.

2.3 Remove Rows

A method would be to :

-       Get row count and size
-       Disable Index [oracle disable index for maintenance ].
-       Delete Rows < '01/01/2013'
-       Enable row_movement , Shrink , Disable row_movement
-       Rebuild Index
-       Gather Stats
-       Get row count and size

A much faster method, when we can afford downtime :

-       Have a good backup
-       Get row count and size
-       Select rows we want to keep in a SAVE Table : Direct-(Nologging)-Insert [ CTAS ] > '31/12/2012'
-       Truncate Original Table
-       Copy back the rows from SAVE to Original Table
-       Get row count and size

Statistics on target tables should be up to date ; if necessary, change DBMS_STATS.AUTO_SAMPLE_SIZE to 100 for small tables

2.3.1 Version where There are NO INDEX on target tables


-- delete old rows ( were exported before )
--       Have a good backup
--       Get row count and size
--       Select rows we want to keep in a SAVE Table : Direct-(Nologging)-Insert [ CTAS ]
--       Truncate Original Table
--       Copy back the rows from SAVE to Original Table
--       Get row count and size After

set serveroutput on
-- exec DBMS_STATS.GATHER_TABLE_STATS ( 'APP_OWNER','HISTO_ACT',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE)

-- create or replace procedure DEL_SHR AS

declare

  v_sql  VARCHAR2(1024);
  v_res  VARCHAR2(1024);
  v_res_2  VARCHAR2(1024);
  v_sz_MB_before integer ;
  v_sz_MB_after integer ;
  v_redo_MB_before integer ;
  v_redo_MB_after integer ;

BEGIN
DBMS_OUTPUT.ENABLE(1000000);       -- Clear DBMS_OUTPUT buffer.

 for i in ( select a.owner , a.table_name from dba_tables a
where a.owner='APP_OWNER' and a.table_name like 'HISTO%' and a.num_rows != 0
and not exists ( select '1' from  dba_indexes b where a.owner=b.owner and a.table_name = b.table_name )
order by a.num_rows desc )

 loop

-- 1/ info before
         select ceil(value/1024/1024) into v_redo_MB_before from v$mystat natural join v$statname where name = 'redo size' ;
         EXECUTE IMMEDIATE 'select ''''||ceil(blocks*8192/1024/1024)||'''' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_sz_MB_before ;
--    DBMS_OUTPUT.PUT_LINE(' select '' ''||table_name||'' - ''||num_rows||'' rows - ''||ceil(blocks*8192/1024/1024)||'' MB'' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' ) ;
         EXECUTE IMMEDIATE 'select to_char(sysdate, ''YYYY-MM-DD HH24:MI:SS'')||'' Before ''||table_name||'' - ''||num_rows||'' rows - ''||ceil(blocks*8192/1024/1024)||'' MB'' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_res ;
    DBMS_OUTPUT.PUT_LINE( v_res ) ;

-- 2/ work
    DBMS_OUTPUT.PUT_LINE( ' create table '||i.owner||'.'||i.table_name||'_S as select * from '||i.owner||'.'||i.table_name||' where DATE_TRANSACTION > ''31/12/2012'' ' ) ;
    EXECUTE IMMEDIATE 'create table '||i.owner||'.'||i.table_name||'_S as select * from '||i.owner||'.'||i.table_name||' where DATE_TRANSACTION > ''31/12/2012'' ' ;
    DBMS_OUTPUT.PUT_LINE( ' truncate table '||i.owner||'.'||i.table_name||' ' ) ;
    EXECUTE IMMEDIATE 'truncate table '||i.owner||'.'||i.table_name||' ' ;
    DBMS_OUTPUT.PUT_LINE( ' alter table '||i.owner||'.'||i.table_name||' NOLOGGING ' ) ;
    EXECUTE IMMEDIATE 'alter table '||i.owner||'.'||i.table_name||' NOLOGGING ' ;
    DBMS_OUTPUT.PUT_LINE( ' insert /*+ APPEND */ into '||i.owner||'.'||i.table_name||' select * from '||i.owner||'.'||i.table_name||'_S ' ) ;
    EXECUTE IMMEDIATE 'insert /*+ APPEND */ into '||i.owner||'.'||i.table_name||' select * from '||i.owner||'.'||i.table_name||'_S ' ;
         commit ;
         DBMS_OUTPUT.PUT_LINE( ' DBMS_STATS.GATHER_TABLE_STATS ( '''||i.owner||''','''||i.table_name||''',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,''FOR ALL INDEXED COLUMNS SIZE AUTO'',2,''ALL'',TRUE)' ) ;
         EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.GATHER_TABLE_STATS ( '''||i.owner||''','''||i.table_name||''',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,''FOR ALL INDEXED COLUMNS SIZE AUTO'',2,''ALL'',TRUE); END; ' ;
    DBMS_OUTPUT.PUT_LINE( ' alter table '||i.owner||'.'||i.table_name||' LOGGING ' ) ;
    EXECUTE IMMEDIATE 'alter table '||i.owner||'.'||i.table_name||' LOGGING ' ;
    DBMS_OUTPUT.PUT_LINE( ' drop table '||i.owner||'.'||i.table_name||'_S ' ) ;
    EXECUTE IMMEDIATE 'drop table '||i.owner||'.'||i.table_name||'_S ' ;

-- 3/ info after  
         EXECUTE IMMEDIATE 'select ''''||ceil(blocks*8192/1024/1024)||'''' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_sz_MB_after ;
         EXECUTE IMMEDIATE 'select to_char(sysdate, ''YYYY-MM-DD HH24:MI:SS'')||'' After ''||table_name||'' - ''||num_rows||'' rows - ''||ceil(blocks*8192/1024/1024)||'' MB'' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_res_2 ;
    DBMS_OUTPUT.PUT_LINE( v_res||' '||v_res_2||' GAIN = '||(v_sz_MB_before-v_sz_MB_after)||' MB GAIN_PCT = '||ceil(100*(v_sz_MB_before-v_sz_MB_after)/v_sz_MB_before)||' %' ) ;

         select ceil(value/1024/1024) into v_redo_MB_after from v$mystat natural join v$statname where name = 'redo size' ;
         DBMS_OUTPUT.PUT_LINE( 'Redo size generated: '||(v_redo_MB_after-v_redo_MB_before)||' MB' ) ;

 end loop;

END;
/



Run under SYSTEM user

2014-12-04 16:53:00 Before HISTO_ACT - 25609416 rows - 3914 MB
 create table APP_OWNER.HISTO_ACT_S as select * from APP_OWNER.HISTO_ACT where DATE_TRANSACTION > '31/12/2012'
 truncate table APP_OWNER.HISTO_ACT
 alter table APP_OWNER.HISTO_ACT NOLOGGING
 insert /*+ APPEND */ into APP_OWNER.HISTO_ACT select * from APP_OWNER.HISTO_ACT_S
 DBMS_STATS.GATHER_TABLE_STATS ( 'APP_OWNER','HISTO_ACT',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE)
 alter table APP_OWNER.HISTO_ACT LOGGING
 drop table APP_OWNER.HISTO_ACT_S
2014-12-04 16:53:00 Before HISTO_ACT - 25609416 rows - 3914 MB 2014-12-04 16:56:17 After HISTO_ACT - 9662204 rows - 1535 MB GAIN = 2379 MB GAIN_PCT = 61 %
Redo size generated: 1550 MB
PL/SQL procedure successfully completed.

Note 1 : Oracle log/undo generated at the session level


-- Total amount of redo / undo generated in bytes since the session started:
select 'redo: '||ceil(value/1024/1024)||' MB' from v$mystat natural join v$statname where name = 'redo size'
union
select 'undo: '||ceil(value/1024/1024)||' MB' from v$mystat natural join v$statname where name = 'undo change vector size';


Note 2 : Direct Insert LOGGING vs. NOLOGGING

LOGGING Redo size generated 3086 MB
NOLOGGING Redo size generated: 1550 MB

2.3.2 Other Version that takes of Tables With INDEX

We manage the index : disable and recreation to speed the whole process.


set serveroutput on
set timing on
set lines 150 pages 5000

DECLARE
  TYPE idxcurtyp IS REF CURSOR;
  usr_idxs idxcurtyp ;
  TYPE proplist IS TABLE OF dba_indexes.owner%TYPE;
  TYPE idxnlist IS TABLE OF dba_indexes.index_name%TYPE;
  prop  proplist ;
  idxn  idxnlist ;

  v_sql  VARCHAR2(1024);
  v_res  VARCHAR2(1024);
  v_res_2  VARCHAR2(1024);
  v_sz_MB_before integer ;
  v_sz_MB_after integer ;
  v_sz_Idx_MB_before integer ;
  v_sz_Idx_MB_after integer ;
  v_redo_MB_before integer ;
  v_redo_MB_after integer ;

BEGIN
DBMS_OUTPUT.ENABLE(1000000);       -- Clear DBMS_OUTPUT buffer.

DBMS_OUTPUT.PUT_LINE('START : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));

-- Select tables with Index -> every tables

for i in (
select a.owner , a.table_name from dba_tables a
where a.owner='APP' and a.table_name like 'HISTO%' and a.num_rows != 0 and table_name not in ('HISTO_T1')
-- and exists ( select '1' from  dba_indexes b where a.owner=b.owner and a.table_name = b.table_name )
order by a.num_rows desc
)
 loop

-- 1/ info before, including Index
DBMS_OUTPUT.PUT_LINE('INFO BEFORE : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));

  select ceil(value/1024/1024) into v_redo_MB_before from v$mystat natural join v$statname where name = 'redo size' ;
  EXECUTE IMMEDIATE 'select ''''||ceil(blocks*8192/1024/1024)||'''' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_sz_MB_before ;
  EXECUTE IMMEDIATE 'select sum(''''||ceil(blocks*8192/1024/1024)||'''') from dba_indexes a , dba_segments b where a.owner = '''||i.owner||''' and table_name = '''||i.table_name||''' and a.index_name = b.segment_name and a.owner = b.owner ' into v_sz_Idx_MB_before ;
  EXECUTE IMMEDIATE 'select to_char(sysdate, ''YYYY-MM-DD HH24:MI:SS'')||'' Before ''||table_name||'' - ''||num_rows||'' rows - ''||ceil(blocks*8192/1024/1024)||'' MB'' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_res ;

-- 2/ work
-- dbms_lock.sleep(20);
DBMS_OUTPUT.PUT_LINE('PREPARE : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));

    EXECUTE IMMEDIATE 'create table '||i.owner||'.'||i.table_name||'_S as select * from '||i.owner||'.'||i.table_name||' where DATE_TRANSACTION > ''31/12/2014'' ' ;
    EXECUTE IMMEDIATE 'truncate table '||i.owner||'.'||i.table_name||' ' ;
    EXECUTE IMMEDIATE 'alter table '||i.owner||'.'||i.table_name||' NOLOGGING ' ;

-- 2b/ index management before
   OPEN usr_idxs for select owner , index_name from dba_indexes where owner = ''||i.owner||'' and table_name = ''||i.table_name||'' order by 1 ;
   FETCH usr_idxs BULK COLLECT INTO prop , idxn ;
   CLOSE usr_idxs ;

    FOR j IN prop.FIRST .. prop.LAST
    LOOP
      v_sql:= 'ALTER INDEX '||prop(j)||'.'||idxn(j)||' UNUSABLE';
      DBMS_OUTPUT.PUT_LINE( v_sql ) ;
      EXECUTE IMMEDIATE v_sql;
    END LOOP;
-- index mngt before end
DBMS_OUTPUT.PUT_LINE('INSERT START : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
    EXECUTE IMMEDIATE 'insert /*+ APPEND */ into '||i.owner||'.'||i.table_name||' select * from '||i.owner||'.'||i.table_name||'_S ' ;
    commit ;
DBMS_OUTPUT.PUT_LINE('INSERT DONE : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));

-- 2c/ Index management after

DBMS_OUTPUT.PUT_LINE('MNGT AFTER IDX : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));

   OPEN usr_idxs for select owner , index_name from dba_indexes where owner = ''||i.owner||'' and table_name = ''||i.table_name||'' order by 1 ;
   FETCH usr_idxs BULK COLLECT INTO prop , idxn ;
   CLOSE usr_idxs ;

    FOR j IN prop.FIRST .. prop.LAST
    LOOP
      v_sql:= 'ALTER INDEX '||prop(j)||'.'||idxn(j)||' REBUILD ONLINE NOLOGGING';
      DBMS_OUTPUT.PUT_LINE( v_sql ) ;
      EXECUTE IMMEDIATE v_sql;
    END LOOP;

DBMS_OUTPUT.PUT_LINE('MNGT AFTER TABLE : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
    EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.GATHER_TABLE_STATS ( '''||i.owner||''','''||i.table_name||''',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,''FOR ALL INDEXED COLUMNS SIZE AUTO'',2,''ALL'',TRUE); END; ' ;
    EXECUTE IMMEDIATE 'alter table '||i.owner||'.'||i.table_name||' LOGGING ' ;
    EXECUTE IMMEDIATE 'drop table '||i.owner||'.'||i.table_name||'_S ' ;

-- 3/ info after
DBMS_OUTPUT.PUT_LINE('INFO AFTER : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));

    EXECUTE IMMEDIATE 'select ''''||ceil(blocks*8192/1024/1024)||'''' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_sz_MB_after ;
    EXECUTE IMMEDIATE 'select to_char(sysdate, ''YYYY-MM-DD HH24:MI:SS'')||'' After ''||table_name||'' - ''||num_rows||'' rows - ''||ceil(blocks*8192/1024/1024)||'' MB'' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_res_2 ;
    DBMS_OUTPUT.PUT_LINE( v_res||' '||v_res_2||' GAIN = '||(v_sz_MB_before-v_sz_MB_after)||' MB GAIN_PCT = '||ceil(100*(v_sz_MB_before-v_sz_MB_after)/v_sz_MB_before)||' %' ) ;

    EXECUTE IMMEDIATE 'select sum(''''||ceil(blocks*8192/1024/1024)||'''') from dba_indexes a , dba_segments b where a.owner = '''||i.owner||''' and table_name = '''||i.table_name||''' and a.index_name = b.segment_name and a.owner = b.owner ' into v_sz_Idx_MB_after ;
    EXECUTE IMMEDIATE 'select to_char(sysdate, ''YYYY-MM-DD HH24:MI:SS'')||'' Before ''||table_name||'' - ''||num_rows||'' rows - ''||ceil(blocks*8192/1024/1024)||'' MB'' from dba_tables where owner = '''||i.owner||''' and table_name = '''||i.table_name||''' ' into v_res ;
    DBMS_OUTPUT.PUT_LINE( 'Idx_Size Before = '||v_sz_Idx_MB_before||' MB ; After = '||v_sz_Idx_MB_after||' MB - GAIN = '||(v_sz_Idx_MB_before-v_sz_Idx_MB_after)||' MB GAIN_PCT = '||ceil(100*(v_sz_Idx_MB_before-v_sz_Idx_MB_after)/v_sz_Idx_MB_before)||' %' ) ;

    select ceil(value/1024/1024) into v_redo_MB_after from v$mystat natural join v$statname where name = 'redo size' ;
    DBMS_OUTPUT.PUT_LINE( 'Redo size generated: '||(v_redo_MB_after-v_redo_MB_before)||' MB' ) ;

 end loop;

END;
/


Run under SYSTEM user

$ sqlplus system @delete_histo_2015_idx.sql

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 28 09:02:08 2015
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ALTER INDEX APP.HP_DTNAI UNUSABLE
ALTER INDEX APP.HP_MATRICULE UNUSABLE
ALTER INDEX APP.HP_CODE UNUSABLE
ALTER INDEX APP.HP_NOMN UNUSABLE
ALTER INDEX APP.HP_PREN UNUSABLE
ALTER INDEX APP.HP_NOM UNUSABLE
ALTER INDEX APP.HP_NUMDEMRSA UNUSABLE
ALTER INDEX APP.HP_NIR UNUSABLE
ALTER INDEX APP.HP_DTNAI REBUILD ONLINE NOLOGGING
ALTER INDEX APP.HP_MATRICULE REBUILD ONLINE NOLOGGING
ALTER INDEX APP.HP_CODE REBUILD ONLINE NOLOGGING
ALTER INDEX APP.HP_NOMN REBUILD ONLINE NOLOGGING
ALTER INDEX APP.HP_PREN REBUILD ONLINE NOLOGGING
ALTER INDEX APP.HP_NOM REBUILD ONLINE NOLOGGING
ALTER INDEX APP.HP_NUMDEMRSA REBUILD ONLINE NOLOGGING
ALTER INDEX APP.HP_NIR REBUILD ONLINE NOLOGGING
2015-01-28 09:02:18 Before HISTO_PERS - 21099061 rows - 4911 MB 2015-01-28 09:48:10 After HISTO_PERS - 10778832 rows - 2515 MB GAIN = 2396 MB GAIN_PCT = 49 %
Idx_Size Before = 3890 MB ; After = 1989 MB - GAIN = 1901 MB GAIN_PCT = 49 %
Redo size generated: 35281 MB

ALTER INDEX APP_OWNER.HPED_ID_IDX1 UNUSABLE
ALTER INDEX APP_OWNER.HPED_ID_IDX1 REBUILD ONLINE NOLOGGING
2014-12-08 14:42:19 Before HISTO_TAB_15 - 325018 rows - 180 MB 2014-12-08 14:42:23 After HISTO_TAB_15 - 65559 rows - 37 MB GAIN = 143 MB GAIN_PCT = 80 %
Idx_Size Before = 9 MB ; After = 2 MB - GAIN = 7 MB GAIN_PCT = 78 %
Redo size generated: 43 MB
PL/SQL procedure successfully completed.
Elapsed: 00:46:32.62


Another execution :


START : 14:25:55
INFO BEFORE : 14:25:55
PREPARE : 14:25:57
ALTER INDEX APP.TAB1_ID_IDX1 UNUSABLE
INSERT START : 14:26:25
INSERT DONE : 14:26:38
MNGT AFTER IDX : 14:26:38
ALTER INDEX APP.TAB1_ID_IDX1 REBUILD ONLINE NOLOGGING
MNGT AFTER TABLE : 14:26:46
INFO AFTER : 14:26:58
2016-02-12 14:25:57 Before HISTO_TAB1 - 1216885 rows - 648 MB 2016-02-12
14:26:58 After HISTO_TAB1 - 823069 rows - 447 MB GAIN = 201 MB GAIN_PCT = 32 %
Idx_Size Before = 33 MB ; After = 22 MB - GAIN = 11 MB GAIN_PCT = 34 %
Redo size generated: 449 MB

PL/SQL procedure successfully completed.

START : 14:31:46
INFO BEFORE : 14:31:46
PREPARE : 14:31:48
ALTER INDEX APP.HP_DTNAI UNUSABLE
…
ALTER INDEX APP.HP_NIR UNUSABLE
INSERT START : 14:34:49
INSERT DONE : 14:44:45
MNGT AFTER IDX : 14:44:45
ALTER INDEX APP.HP_DTNAI REBUILD ONLINE NOLOGGING
…
ALTER INDEX APP.HP_NIR REBUILD ONLINE NOLOGGING
MNGT AFTER TABLE : 14:58:42
INFO AFTER : 15:00:43
2016-02-12 14:31:48 Before HISTO_PERS - 21698308 rows - 4921 MB 2016-02-12 15:00:43 After HISTO_PERS - 11675711 rows - 2731 MB GAIN = 2190 MB GAIN_PCT = 45 %
Idx_Size Before = 4009 MB ; After = 2152 MB - GAIN = 1857 MB GAIN_PCT = 47 %
Redo size generated: 8867 MB



Publié par orACsd à 02:33
Libellés :