deleting from table causes fragmentation

One of our application groups periodically deletes data, which are older than 10 days, from some tables. This periodic action splits the table blocks to a wide range of datafile blocks. This causes performance problems on daily data loading and daytime reporting. As the first action plan gather statistics for trusted analysis.

begin
dbms_stats.gather_table_stats(
    ownname=>'PQ',
    tabname=>'GPRS_CELL_GPRS',
    estimate_percent=>33,
    degree=>2,
    cascade=>true);
end;

You can see the space fragmentation from the following. Currently the tables consumes nearly 3Gb 's of datafile usage. But if you calculate the average row length with the number of table rows, datafile usage should be 328Mb.

select ceil((blocks*8)/1024) as table_mb from dba_tables 
    where table_name='GPRS_CELLGPRS' and owner='PQ'
/*
TABLE_MB
2926
*/
select ceil((avg_row_len*num_rows)/1024/1024) as table_mb from dba_tables 
    where table_name='GPRS_CELLGPRS' and owner='PQ'
/*
TABLE_MB
328
*/ 

After this finding, following procedure is set to run periodically to delete 10 days older data from the table. After the delete operation table is moved for defragmentation. Table indexes should also be rebuilded. After the move and rebuild operation statistics should also be gathered. Keep in mind that table move and index rebuild operations blocks user access.

create or replace procedure avea_sysmon.P_TABLE_MAINTENANCE
  (g_table_name varchar2, g_owner varchar2) AUTHID CURRENT_USER as 
  
  cursor c1 is
    select * from sys.dba_indexes where table_owner=g_owner and table_name=g_table_name;

  r1 c1%rowtype;  
begin
    
    execute immediate 'delete from PQ.GPRS_TRAFGPRS3 where ' || 
                      'to_number(substr(CDATE,1,6)) < to_char(sysdate-15,''YYMMDD'')';
    commit; 
    execute immediate 'alter table ' || g_owner || '.' || g_table_name || ' move';
    
    open c1;    
    loop
      fetch c1 into r1;
      exit when c1%NOTFOUND;  
      execute immediate 'alter index ' || r1.table_owner || '.' || 
                         r1.index_name || '  rebuild';      
    end loop;
    
    dbms_stats.gather_table_stats(ownname=>g_owner,tabname=>g_table_name,
                                  estimate_percent=>33,cascade=>true);
    
end P_TABLE_MAINTENANCE;

FOLLOW UP: (23.11.2010) With Oracle10g there is a new way of reorganising your table segments. The best thing is there is not any unusable indexes anymore.

select owner, table_name, 
  trunc((avg_row_len*num_rows)/1024/1024) as ROWS_MB, 
  trunc((blocks*8192)/1024/1024) as TOTAL_MB 
  from dba_tab_statistics where table_name = 'WIZ_CUSTOMER_MAILBOX';
--OWNER, TABLE_ANME, ROWS_MB, TOTAL_MB
--PROD_DBA,WIZ_CUSTOMER_MAILBOX,19982,29790

alter table PROD_DBA.WIZ_CUSTOMER_MAILBOX enable row movement;
alter table PROD_DBA.WIZ_CUSTOMER_MAILBOX shrink space compact;

select owner, table_name, 
  trunc((avg_row_len*num_rows)/1024/1024) as ROWS_MB, 
  trunc((blocks*8192)/1024/1024) as TOTAL_MB 
  from dba_tab_statistics where table_name = 'WIZ_CUSTOMER_MAILBOX';
--OWNER, TABLE_ANME, ROWS_MB, TOTAL_MB
--PROD_DBA,WIZ_CUSTOMER_MAILBOX,19982,26790

A Security Hardening Approach for userproof databases

What if you have some confusion about some of your database users and want to trace the users or even be notified about their actions in the database or just want to warn them about probable mistakes can be made by db users. Oracle Database software has some solutions about likely cases. Auditing is one of the most used option with tracing. DDL triggers can also be used. There are also some solutions at the opsys level.

1- Logon Triggers to Trace Users
Oracle database has logon triggers to run on any users login to the database. DBA can use this feature to set a trace file for the logged in user. tkprof utility can be used to examine users activity.

CREATE OR REPLACE TRIGGER SYS.TRG_SESSION_LOGON after logon on database
declare
  s_username varchar2(20);
  s_sessid varchar2(20);
  s_stamp varchar2(20);
begin

  -- obtain sid of the session
  select distinct(substr(sid,1,20)) into s_sessid from v$mystat;

  -- obtain username of the session
  select username into s_username from v$session where sid=s_sessid;

  -- create a timestamp to uniquely identify the trace file
  select to_char(sysdate,'DDMMYYYY_HH24MISS') into s_stamp from dual;

  -- set the tracefile size to unlimited. careful about that (!)
  execute immediate 'alter session set max_dump_file_size=unlimited';

  -- define an identifier to the tracefile to distinguish the trace files
  execute immediate 'alter session set tracefile_identifier=' || s_username || 
                    '_' || s_sessid || '_' || s_stamp;

  -- i will take 10046 trace level 8 to see the waits 
  -- (check timed_statistics init parameter!)
  execute immediate 'alter session set events ''10046 trace name context forever, level 8''';

  -- finally start the trace
  execute immediate 'alter session set sql_trace=true';
  
  exception
    when others then
      raise;
    
end TRG_LOGON;

You can find the trace files from user_dump_dest parameter

SQL> show parameter user_dump_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      D:\ORACLEDB10G\ADMIN\ORCL\UDUMP

To examine the dump file tkprof utility can be used.

tkprof orcl_ora_5412_epeker_148_22052008_162752.trc tkprof.out

tkprof.out file.

2- Database Audit
To enable audit option, audit_trail init parameter should be set.

select name, value from v$parameter where name = 'audit_trail';
alter system set audit_trail='DB_EXTENTED' scope=spfile;
--After changing audit_trail parameter bounce the database.

audit select table, insert table, update table, delete table by epeker;
audit session by epeker;

Now login to the database with the audited user and make some ddl.

select * from EPEKER.T_TABLE_DBA_TABLES;
insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10;
commit;
select * from EPEKER.T_TABLE_DBA_TABLES;

All the audit information should be in the SYS.AUD$ table

select 
  SESSIONID, USERID, USERHOST,TERMINAL, OBJ$CREATOR, OBJ$NAME, NTIMESTAMP#, SCN, SQLTEXT 
from 
  SYS.AUD$
order by NTIMESTAMP# desc;

/*
SESSIONID  USERID     OBJ$CREATOR OBJ$NAME           NTIMESTAMP#           SCN    SQLTEXT
1249       EPEKER     SYS,        USER$              5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        TS$                5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        SEG$               5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        X$KSPPCV           5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        TAB$               5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        OBJ$               5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        DBA_TABLES         5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     SYS,        X$KSPPI            5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10 
1249       EPEKER     EPEKER,     T_TABLE_DBA_TABLES 5/23/2008 11:44:10 AM 935944 select * from T_TABLE_DBA_TABLES
1268       EPEKER                                    5/23/2008 12:01:56 PM       
*/

You can revoke the audit by noaudit command.

noaudit select table, insert table, update table, delete table by epeker;
noaudit session by epeker;

3- DDL Triggers
Oracle database has many different trigger options. You have just read about "logon triggers". There is also ddl triggers can be used to disable specific users from mistakenly running dangerous ddl statements. Any user wants to execute a ddl statment (create, truncate.. etc) can be logged and the user can also be warned by the administrator. The following trigger simply disables a user to drop and/or truncate database tables.

CREATE OR REPLACE TRIGGER SYS.TRG_DDL before ddl
ON DATABASE
declare
  s_sysevent varchar2(32);
  s_owner varchar2(32);
  s_objname varchar2(32);
  s_user varchar2(32);
begin

  select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, USER 
    into s_sysevent, s_owner, s_objname, s_user from dual;
    
  insert into epeker.t_trg_ddl_test values (ora_sysevent);
  if s_sysevent in ('DROP','TRUNCATE') then
    if s_user = 'EPEKER' then
      raise_application_error(-20010,'You are not authorized to drop a segment!.. ');
      null;
    end if;
  end if;
  
end TRG_DDL;

4- database privileges
Privileges is important (maybe the most important) checklist to be careful about. Privileges and Roles should be carefully assgined and also revoked from users to minimize the mistakenly made data loss in the databases.

5- opsys suggested tasks (alias, sudo, execute privileges, op.sys groups)
Beside database auditing tasks, some securtiy hardening can be made in operating system side. Aliases can be used as of direct op. sys. commands for auditing and preventing mistakes by end users or application users. Some commands need root privileges, this kind of commands can be run through sudo in unix. Application users should not be in dba group and Oracle Home and datafiles should be protected with op. sys. file permissions. Grid agent like management and monitoring tools can be used to audit or proactively solve the likely problems.

Table Monitoring worked well

I was logged in one of our forgotten databases to partition some tables and maintain dba routines. After all my work is done i decided to examine the tables which are not used any more.

First thing is to find if it worths to spend some time on it. I checked the sizes of the tables and found that one of the unused tables consume approximately 21Gb of space. That should be so valuable to drop this table on this database because of the problematic disk space usage.

select 
  segment_name, sum(bytes/1024/1024) as MB
from 
  dba_segments 
where 
  segment_name in ('WAP_STATREC','PAYFORME_TEMP','PAY4ME_TEMP') and 
  owner ='PQ'
group by 
  segment_name
order by MB desc;

SEGMENT_NAME    MB
------------    --
WAP_STATREC     21443
PAYFORME_TEMP   145
PAY4ME_TEMP     1

To obtain if these tables are actively used by developers or any application, i altered these tables to enable monitoring and logout the system.

alter table pq.wap_statrec monitoring
alter table pq.payforme_temp monitoring
alter table pq.pay4me_temp monitoring

Today, when i remembered what have i done for a few weeks earlier, i logged in the database and check the dba_tab_modifications management view to find if any modifications done to the tables.

select
  table_owner, table_name, inserts, updates, deletes, timestamp, truncated
from 
  sys.dba_tab_modifications 
where 
  table_name in ('WAP_STATREC','PAYFORME_TEMP','PAY4ME_TEMP');

TABLE_OWNER TABLE_NAME    INSERTS  UPDATES DELETES TIMESTAMP  TRUNCATED
----------- ----------    -------  ------- ------- ---------  ---------
PQ          PAYFORME_TEMP 10695476 0       0       5/7/2008   YES

As you can see only one of the tables is actively used by an application and the others are not used for 2 weeks. Now time to talk with the application operation or development group to drop their unused tables.