Import CBO statistics for particular tables

On some occasions, we found a query having problem and suspect the execution plan has changed due to new stats. We thus want to test if we restore to previous CBO stats will help. However we may only have exported previous CBO statistics at schema level, can we import CBO statistics for the particular tables involved using the stats table obtained at schema level? I did a test and find the anwer is 'Yes'.

Test steps / observations:

1. Current analyze time for the table TAB_OT

col owner format a20
col table_name format a20
select owner, table_name, num_rows, last_analyzed from dba_tables where table_name='TAB_OT';
 
OWNER                TABLE_NAME             NUM_ROWS LAST_ANAL
-------------------- -------------------- ---------- ---------
SCH_RO              TAB_OT           449849200 21-FEB-09

2. DBMS_STATS.IMPORT_TABLE_STATS definition:

PROCEDURE IMPORT_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 STATTAB                        VARCHAR2                IN
 STATID                         VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT

3. Import table states

begin
   DBMS_STATS.IMPORT_TABLE_STATS (
     'SCH_RO',
     'TAB_OT',
      NULL,
     'SCH_RO_STATS_20090209',
      NULL,
      TRUE,
      'OPS$ORACLE',
      TRUE
);
end;
/ 

Note: SCH_RO_STATS_20090209 is a stats table for the schema SCH_RO

4. Check analyze time again:

 
OWNER             TABLE_NAME          NUM_ROWS LAST_ANAL
------------------ ----------------- ---------- ---------
SCH_RO              TAB_OT           432460407 07-FEB-09

 
OWNER                INDEX_NAME               TABLE_NAME           LAST_ANAL
-------------------- ------------------------ -------------------- ---------
SCH_RO              TAB_OT_PK                 TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX2                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX4                TAB_OT          08-FEB-09
SCH_RO              TAB_OT_IX20               TAB_OT
SCH_RO              TAB_OT_IX5                TAB_OT          08-FEB-09
SCH_RO              TAB_OT_FK8                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX3                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_FK4                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_FK1                TAB_OT          07-FEB-09
 
9 rows selected.

5. Command to export schema stats
(1) Create stats tab
begin
DBMS_STATS.CREATE_STAT_TABLE (
'STATTABOWNER',
'SCH_RO_STATS_20090302'
);
end;
/


(2) Export stats
begin
DBMS_STATS.EXPORT_SCHEMA_STATS (
'SCH_RO',
'SCH_RO_STATS_20090302' ,
NULL,
'STATABOWNER');
end;
/

Adjust Oracle sequence to use odd and even number respectively on a Production and DR database

Our production databases are replicated to DR databases through SharePlex replcation. Recently, We have a need to enable two-way replication. i.e. in addition to replication from production to DR, we also need DR to prodcution. I was thus assigned a task to adjust the sequences in production to use odd number and sequences in DR to use even number.

The sequneces in production and DR are out-of-sync currently, as we don't replicate sequence from production to DR. That means if on production a sequence's last number is 1000, whereas on DR, the number could be 1.

Testing for a while, I found that when generating the DDL for a sequence through DBMS_METADATA package, the number following the 'start with' clause is the last number of DBA_SEQUENCES view.

For example:

SQL> select sequence_name, last_number from dba_sequences
where sequence_owner=user;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
TEST2_SEQ                              107
TEST3_SEQ                              107
TEST_SEQ                               175


SQL> ;
  1* SELECT DBMS_METADATA.GET_DDL(upper('&OBJTYPE'), upper('&OBJNAME') , upper('&OWNER'))  ddl_string from dual
SQL> /
Enter value for objtype: sequence
Enter value for objname: TEST_SEQ
Enter value for owner: abc

   CREATE SEQUENCE  "ABC"."TEST_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27
 INCREMENT BY 98 START WITH 175 CACHE 20 NO
ORDER  NOCYCLE ;

I thus developed the following plan:

1. On Prod, run script seq_odd.sql to change seq number to odd and increment by to 2
2. On prod, run script seq_ddl_gen.sql to generate sequence ddl script: seq_ddl.sql
3. SCP seq_ddl.sql to DR server
4. On DR, run script seq_drop_gen.sql and then seq_drop.sql to drop sequence
5. On DR, run script seq_ddl.sql generated in step 2 to re-create all sequences
6. On DR, run script seq_incr1_gen.sql, seq_incr1.sql to modify sequence increment by to 1;
7. On DR, run script seq_nextval_gen.sql, seq_nextval.sql to make the currval of sequences to be even number,
8. on DR, run script seq_incr2_gen.sql, seq_incr2.sql to modify sequence increment by to 2;

The above scripts are simple execept for seq_odd.sql, I wrote PL/SQL code for this:

---- seq_odd.sql -----
declare
  seqown varchar2(30);
  seqname varchar2(30);
  sqlstmt varchar2(1000);
  cval  number;
  incr  number;
  cursor  seq_cur is
      select sequence_owner, sequence_name
        from dba_sequences
        where sequence_owner is [some_condition];
begin
  open seq_cur;
  loop
    fetch seq_cur into seqown, seqname;
    exit when seq_cur%notfound;
    sqlstmt := 'select '  seqown  '.'  seqname  '.nextval  from dual';
    execute immediate sqlstmt into cval;

    if ( mod(cval, 2) = 0 )
    then
-- ensure the current val is odd number
-- first change increment by 1
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 1';
       execute immediate sqlstmt;

       sqlstmt := 'select '  seqown  '.'  seqname  '.nextval  from dual';
       execute immediate sqlstmt into cval;
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 2';
       execute immediate sqlstmt;
    else
-- already an odd number
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 2';
       execute immediate sqlstmt;
    end if;
  end loop;
  close seq_cur;

-- follwing code is to verify ----
---- end of seq_odd.sql --------

DBA_MVIEW_REFRESH_TIMES bug in 9i

We set up a materialized view replication for a table from a 9i database to a 10g database. According to the following Oracle doc:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#45084

You can also query the DBA_MVIEW_REFRESH_TIMES view at a master site or master materialized view site to obtain the last refresh times for each materialized view. Administrators can use this information to monitor materialized view activity and coordinate changes to materialized view sites if a master table or master materialized view needs to be dropped, altered, or relocated.

It is clearly said that we can query DBA_MVIEW_REFRESH_TIMES to see the last fresh time of materialized views on the master database. However I saw nothing by querying this viev in our case. I am sure that the materialized view is registed, as comfirmed by the following query:

select owner, name, mview_site, can_use_log, updatable, refresh_method from
dba_registered_mviews;

On the mview site, we can clearly see the last fast refresh time from dba_mviews.

To solve the puzzle, I opened a SR with Oracle. It turns out this is a bug:

There is a known issue in this view DBA_MVIEW_REFRESH_TIMES.
The Note # 290270.1 and Bug # 3557488 describes about this issue.
This issue is fixed in 10g as part of enhancement. So, cannot be back ported directly.

Applying two anti-join conditions separately speeds up a job

I was asked to create a temporary table on a 9.2.0.8 reporting database from the following sql:

select ot.TID
from sch_a.OT ot
where ot.TT_ID='C'
  and not exists (select 1 from sch_a.OT t
                  where ot.BID=t.BID and t.TT_ID='I')
   and not exists (select 1 from sch_b.BM bm
                  where ot.BID=bm.BID);

The execution plan looks like:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4640609 Card=360807 Bytes=5772912)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=360807 Bytes=5772912)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'OT' (Cost=8 Card=1 Bytes=9)
   4    3       INDEX (RANGE SCAN) OF 'OT_IX3' (NON-UNIQUE) (Cost=4 Card=4)
   5    1     INDEX (UNIQUE SCAN) OF 'BM_PK' (UNIQUE) (Cost=3 Card=1 Bytes=7)

The first attempt to run this sql returned "snapshot too old" error after 21 hours.

Finally I took a two-step approach to accomplish the task. I am not sure it is the best way, however, task was accomplished in less than 5 hours.


1. create a temp table "tempa" by applying the first anti-join condition using outer-join





create table tempa nologging
as
  select ot.TID, ot.BID
    from sch_a.OT ot, sch_b.BM bm
   where ot.BID = bm.BID (+)
     and bm.rowid is null
     and ot.TT_ID = 'C'
   ;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1035475 Card=144322676 Bytes=4329680280)
   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=144322676 Bytes=2309162816)
   4    2       INDEX (FAST FULL SCAN) OF 'BM_PK' (UNIQUE) (Cost=168790 Card=338111450 Bytes=4733560300)

2. Apply second anti-join condition using outer-join

select tmp.TID
 from  tempa tmp,
      ( select BID
        from  sch_a.OT t
       where t.TT_ID= 'I'
      ) a
 where tmp.BID = a.BID(+)
  and a.rowid is null;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=733333 Card=361891 Bytes=15199422)
   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'TEMPA' (Cost=75 Card=226290 Bytes=5883540)
   4    2       TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=144322676 Bytes=2309162816)

  ------ 

Look at the exection plans in the two-step approach, there are FTS and index fast full scan operations. One good thing about these operations is they appear in the v$session_longops view. The estimation of the exection time they need from this view is usually close to reality.

Some notes about tuning a sql

From my limited experience, the following methods are used or can be used to tune a sql

1. Obtain optimizer execution plan by autotrace and check consistent gets from the autotrace.

2. Obtain real run-time execution plan:
- Fire the sql
- Obtain the hash value, address or sql_id (in 10g) of the sql from v$sql
- Generate the execution plan from v$sql_plan
- Kill the session

3. Judge if the exection plan is reasonable,
- check all the indexes of tables involved
- check table column stats if needed
- check table stats if needed
- check v$session_longops to see the time estimation for available operations.
- join order, join type

4. 10046 event trace/TKPROF

5. 10053 event trace

6. Oracle SQLT tool

7. OEM SQL tunning advisor

8. SQL Profile

Burleson has a page that provides overview of SQL tuning: Oracle tuning - Tune individual SQL statements

Offline data files may need recovery after bringing up database

Due to I/O error, one of our production databases was down last week. In the alert log we can see the following error, for example:

 Thu Mar 19 20:23:28 2009
 KCF: write/open error block=0x96146 online=1
      file=150 [filename].dbf
      error=27063 txt: 'SVR4 Error: 6: No such device or address
 Additional information: -1
 Additional information: 16384'
 Automatic datafile offline due to write error on
 file 150: [filename].dbf

The database was brought up automatically by cluster software possibly. Anyway it was up though I am not sure how. Later on, application recieved error and called DBA for help. We found that there were files in 'recover' status:

select file# from 
v$datafile where status='RECOVER'; 

     FILE#
----------
        49
       144
       146
       148
       150
       172

Those files were further identified by:

select file_name from dba_data_files where file_id in (49,144,146,148,150,172) ;

We recovered them one by one and brought them online by following commands:

alter database recover datafile ;

alter database datafile online;

Learned from this incident, I reinforced the following understanding about Oracle behavior:

Oracle will do instance recovery if necessary when the database is brought up, however, if the data files are offline before the crash, after database is up, they will still be offline and may need to be manually recovered and brought online.

Filtering first or constructing the hierarchical tree first?

A SQL with "start with" and "connect by" looks like:

select level, a.*
from t a
where a.ntid =2
start with a.vp_bid =9 or a.vp_bid is null
connect by prior a.bid= a.vp_bid;

My doubt is that Oracle will first apply the predicate: ntid=2 , or Oracle will
apply the "start with ... connect by" first, then do the filtiering? My test shows the latter is true


The rows in the table:

SQL> select * from t;

       BID       NTID     VP_BID
---------- ---------- ----------
         1          2
         2          2
         3          2          1
         4          2        100
         5          2          2
         6          7          2
         7          7          9
        10         10          9
        11         10         10
        12          2         10
        30         30         30

11 rows selected.

If filtering first, what we should see:

SQL> select level, a.* from
  2     ( select * from t where ntid=2) a
  3  start with a.vp_bid =9 or a.vp_bid is null
  4     connect by prior a.bid= a.vp_bid;

     LEVEL        BID       NTID     VP_BID
---------- ---------- ---------- ----------
         1          1          2
         2          3          2          1
         1          2          2
         2          5          2          2

If applying the hierarchical condition first, then the filtering, what we should see:

SQL> select * from (
  2     select level,  a.*
  3      from t a
  4      start with a.vp_bid =9 or a.vp_bid is null
  5     connect by prior a.bid= a.vp_bid
  6  )
  7  where ntid=2;

     LEVEL        BID       NTID     VP_BID
---------- ---------- ---------- ----------
         2         12          2         10
         1          1          2
         2          3          2          1
         1          2          2
         2          5          2          2

What we see from the original SQL confirms that Oracle applys the hierarchical condition first:

SQL> select level,  a.*
  2   from t a
  3   where a.ntid =2
  4   start with a.vp_bid =9 or a.vp_bid is null
  5  connect by prior a.bid= a.vp_bid;

     LEVEL        BID       NTID     VP_BID
---------- ---------- ---------- ----------
         2         12          2         10
         1          1          2
         2          3          2          1
         1          2          2
         2          5          2          2

Without the predicate, we should see:

SQL> select level,  a.*
  2       from t a
  3    start with a.vp_bid =9 or a.vp_bid is null
  4  connect by prior a.bid= a.vp_bid;

     LEVEL        BID       NTID     VP_BID
---------- ---------- ---------- ----------
         1          7          7          9
         1         10         10          9
         2         11         10         10
         2         12          2         10
         1          1          2
         2          3          2          1
         1          2          2
         2          5          2          2
         2          6          7          2

9 rows selected.

Doubts about UNION operation

I have some doubts about 'union' operation. I know that when two tables union together, duplicate rows will be removed in the result set. My doubts are that if there are duplicated rows in the same table, will Oracle remove those rows? I thus did a simple test. The answer is YES.

My test:

SQL> select * from t;

A
----------
1
2
3
4
5
2

6 rows selected.

Note: I have duplicated rows in table t;

SQL> select * from t1;

A
----------
5
3
8


Union all - Oracle preserves all the rows from table t and t1 as expected

SQL> select * from t1
2 union all
3 select * from t;

A
----------
5
3
8
1
2
3
4
5
2

9 rows selected.


Union - Note: Oracle removes duplicated rows from two tables as well as from the same table:

SQL> select * from t1
2 union
3 select * from t;

A
----------
1
2
3
4
5
8

6 rows selected.

Generate statspack report for the most recent interval quickly

Sometime we need to generate most recent statspack report to understand the problem that have just happened. The script to generate statspack report is spreport.sql, usually we execute this script in SQL* Plus as follows:

SQL> @?/rdbms/admin/spreport

We then need to interactively input start and end snap ids and report name. It is not difficult to modify this script to accept these input variables at command line. For example I have a script called spreport_ds.sql located in my SQLPATH, which can be used in the following way:

SQL> @spreport_ds.sql

The relevent modifications in my spreprot_ds.sql script are shown as follows:

prompt
-- prompt Specify the Begin and End Snapshot Ids
-- prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- prompt Begin Snapshot Id specified: &&begin_snap
-- prompt
-- prompt End   Snapshot Id specified: &&end_snap
prompt

define begin_snap = &1
define end_snap = &2

--
--  Set up the snapshot-related binds, and additional instance info

set termout off;

variable bid        number;
variable eid        number;
begin
  :bid       :=  &&begin_snap;
  :eid       :=  &&end_snap;
end;
/

prompt
-- prompt Specify the Report Name
-- prompt ~~~~~~~~~~~~~~~~~~~~~~~
-- prompt The default report file name is &dflt_name..  To use this name,
-- prompt press  to continue, otherwise enter an alternative.

set heading off;
column report_name new_value report_name noprint;
-- select 'Using the report name '  nvl('&&report_name','&dflt_name')
--     , nvl('&&report_name','&dflt_name') report_name
--   from sys.dual;

define report_name = &3
spool &report_name;
set heading on;
prompt

With this modified script in place, I created another script called sp_mrti.sql to generate statspack report for the most recent interval:

----- script: sp_mrti.sql -----------------
rem sp_mrti.sql
rem  -- generate a statspack report for the  most recent time interval
rem
rem  dependent:  spreport_ds.sql
rem

col eid new_val eid
col sid new_val sid
select max(snap_id)  eid,
       max(snap_id) - 1 sid
from perfstat.stats$snapshot
;

col stime new_val stime
col etime new_val etime

select  to_char(snap_time, 'YYYYMMDD_HH24MI') stime
from perfstat.stats$snapshot
where snap_id = &sid;


select  to_char(snap_time, 'HH24MI') etime
from perfstat.stats$snapshot
where snap_id = &eid;

col instname new_val instname
select instance_name instname from v$instance;


@spreport_ds &sid &eid  &instname._&stime._&etime..txt

----- end of script: sp_mrti.sql ----------

Materialized View Log Overhead

To understand the overhead of materialized view log, I did a test by comparing the logical reads between the cases of presence and absence of materialized view log. I found that Oracle needs to do 14.6% more work in the case of materialized view log.

In the test, I firstly created a table called big_table in the scott schema from dba_objects with 1,000,000 rows. Then in the case of existing of materialized view log, I created a materialized view log on the table scott.big_table. Finally, I executed some update/delete/insert statments against this table with and without materialized view log.

The test script is as follows:

 

alter session set tracefile_identifier = mvlog;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

-- update

begin
for rec in (select id from scott.big_table where rownum <=100000 )
loop
   update scott.big_table set status=lower(status) where id=rec.id;
   commit;
end loop;
end;
/

-- insert
declare
 maxid number;
begin
   select max(id) into maxid from scott.big_table;
   for rec in (select * from scott.big_table where rownum <=100000 )
   loop
      maxid := maxid +1;
      insert into scott.big_table values (
           maxid
          ,rec.OWNER
          ,rec.OBJECT_NAME
          ,rec.SUBOBJECT_NAME
          ,rec.OBJECT_ID
          ,rec.DATA_OBJECT_ID
          ,rec.OBJECT_TYPE
          ,sysdate
          ,sysdate
          ,rec.TIMESTAMP
          ,rec.STATUS
          ,rec.TEMPORARY
          ,rec.GENERATED
          ,rec.SECONDARY
      );
      commit;
   end loop;
end;
/

---  delete 100,000 rows

begin
for rec in (select id from scott.big_table where rownum <=100000 )
loop
   delete from scott.big_table  where id=rec.id;
   commit;
end loop;
end;
/
exit;

Below is the TKPROF analysis result summary:

SQL Statement: 
              select id from scott.big_table where rownum <=100000"
MVLOG         query + current = 200266
NO MVLOG      query + current = 200266


SQL Statement: 
              select * from scott.big_table where rownum <=100000
MVLOG         query + current = 100109
NO MVLOG      query + current = 100133


SQL Statement: 
              DELETE from scott.big_table where id=:b1
MVLOG         query + current = 812628
NO MVLOG      query + current = 811684



SQL Statement: 
             INSERT into scott.big_table values (:b12 ,:b11 ,:b10 ,:b9  ,:b8  
             ,:b7 ,:b6 ,sysdate ,sysdate ,:b5  ,:b4 ,:b3 ,:b2 ,:b1  )
MVLOG         query + current = 516815
NO MVLOG      query + current = 515709


SQL Statement: 
              UPDATE scott.big_table set status=lower(status)
MVLOG         query + current = 503087
NO MVLOG      query + current = 501878

SQL Statement: 
              INSERT /*+ IDX(0) */ INTO "SCOTT"."MLOG$_BIG_TABLE" 
              (dmltype$$,old_new$$, snaptime$$,change_vector$$,"ID") VALUES 
              (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:1)
MVLOG         query + current = 309375


The total logical reads
   MVLOG : 2442240
NO MVLOG : 2129630

Overhead:  (2442240 - 2129630 ) / 2129630 = 14.6 %

Materialized View Log Space Requirement

To understand how much space is needed for materialized view logs, I did a test in a 9i database on my PC.

After this test, I can make this point: the size of materialized view is proportional to the number of rows that are changed by DML statments: insert, update and delete.

Here is the summary of the size of the mvlog segment in my test:

size of MLOG$_BIG_TABLE segment
--------------------------------

Condition                          size_M
---------                          -------
At beginning                       0.0625
After update 1 col for 100,000 rows     7
After insert 100,000 rows              13
After delete 100,000 rows              19
After update 2 col for 100,000 rows    25

The following are more detailed steps and observations:

1. A table called big_table is created in scott schema from dba_objects:

select count(*) from scott.big_table;

COUNT(*)
----------
1000000

2. Create the materialized view log and measure the size of the segments

create materialized view log on scott.big_table tablespace mviewlog;


select owner, segment_name, segment_type, bytes/1024/1024 size_m from dba_segments
where segment_name like '%BIG_TABLE%'
and owner='SCOTT';

OWNER         SEGMENT_NAME             SEGMENT_TYPE           SIZE_M
------------ ------------------------- ------------------ ----------
SCOTT         BIG_TABLE                TABLE                     120
SCOTT         MLOG$_BIG_TABLE          TABLE                   .0625

SCOTT         BIG_TABLE_PK             INDEX                      17

3. Update a column for 100,100 rows

begin
  for rec in (select id from scott.big_table where rownum <=100000 ) 
  loop   
    update scott.big_table set status=lower(status)
    where id=rec.id;   
    commit;
  end loop;
end;
/ 

OWNER       SEGMENT_NAME            SEGMENT_TYPE   SIZE_M
----------- ----------------------- ------------- -------
SCOTT       BIG_TABLE               TABLE             120
SCOTT       MLOG$_BIG_TABLE         TABLE               7
SCOTT       BIG_TABLE_PK            INDEX              17

4. insert 100,000 rows

declare
  maxid number;
begin
   select max(id) into maxid from scott.big_table;
   for rec in (select * from scott.big_table where rownum <=100000 )    
   loop      
     maxid := maxid +1;      
     insert into scott.big_table values (
            maxid          
           ,rec.OWNER   
           ,rec.OBJECT_NAME  
           ,rec.SUBOBJECT_NAME   
           ,rec.OBJECT_ID    
           ,rec.DATA_OBJECT_ID         
           ,rec.OBJECT_TYPE      
           ,sysdate        
           ,sysdate                  
           ,rec.TIMESTAMP                      
           ,rec.STATUS                         
           ,rec.TEMPORARY                      
           ,rec.GENERATED                      
           ,rec.SECONDARY                  
     );      
     commit;   
   end loop;
end;
/ 

OWNER          SEGMENT_NAME       SEGMENT_TYPE       SIZE_M
-------------- ------------------ -------------- ----------
SCOTT          BIG_TABLE          TABLE                 128
SCOTT          MLOG$_BIG_TABLE    TABLE                  13
SCOTT          BIG_TABLE_PK       INDEX                  20 

5. Delete 100,000 rows

begin
  for rec in (select id from scott.big_table where rownum <=100000 )
  loop   
    delete from scott.big_table  where id=rec.id;   
    commit;
  end loop;
end;
/ 

OWNER        SEGMENT_NAME      SEGMENT_TYPE       SIZE_M
------------- ----------------- -------------- ----------
SCOTT         BIG_TABLE         TABLE                 128
SCOTT         MLOG$_BIG_TABLE   TABLE                  19
SCOTT         BIG_TABLE_PK      INDEX                  20 

6. Update two colunms for 100,000 rows

begin
  for rec in (select id from scott.big_table where rownum <=100000 ) 
  loop   
    update scott.big_table set status=upper(status),
                               created=sysdate - 1
    where id=rec.id;   
    commit;
  end loop;
end;
/  

OWNER        SEGMENT_NAME         SEGMENT_TYPE       SIZE_M
------------ -------------------- -------------- ----------
SCOTT        BIG_TABLE            TABLE                 128
SCOTT        MLOG$_BIG_TABLE      TABLE                  25
SCOTT        BIG_TABLE_PK         INDEX                  20

Discovering the I/O pattern of a production database through mining STATSPACK data

1. Total I/O in every hour during weekdays

Total I/O is defined as the number of physical reads plus the number of physical writes. They can be obtained throught the following two scripts, respectively.

----  Sample Script to obtain physical reads ---------
select
             to_char(sn.snap_time, 'YYYY-MM-DD HH24') start_time,
             sum(b.value - a.value)  val
        from perfstat.stats$sysstat a,
             perfstat.stats$sysstat b,
             perfstat.stats$snapshot sn
        where sn.snap_id = a.snap_id
        and   a.snap_id = b.snap_id -1
        and   a.statistic# = 42
        and   b.statistic# = 42
          and snap_time >= to_date('2009-01-26', 'YYYY-MM-DD')
          and snap_time < to_date('2009-01-31', 'YYYY-MM-DD')
        group by to_char(sn.snap_time, 'YYYY-MM-DD HH24')
;
----  END of Sample Script to obtain physical reads ---------

----  Sample Script to obtain physical writes---------

select
             to_char(sn.snap_time, 'YYYY-MM-DD HH24') start_time,
             sum(b.value - a.value)  val
        from perfstat.stats$sysstat a,
             perfstat.stats$sysstat b,
             perfstat.stats$snapshot sn
        where sn.snap_id = a.snap_id
        and   a.snap_id = b.snap_id -1
        and   a.statistic# = 46
        and   b.statistic# = 46
          and snap_time >= to_date('2009-01-26', 'YYYY-MM-DD')
          and snap_time < to_date('2009-01-31', 'YYYY-MM-DD')
        group by to_char(sn.snap_time, 'YYYY-MM-DD HH24')
;
----  END of Sample Script to obtain physical writes---------

2. Average Time Per Read (ATPR) in every hour during weekdays

In the Statspack report, there is a section called "Tablespace I/O Statistics", along with the "File I/O section" they can be used to identify whether I/O is especially slow or there are an exceptional number of I/Os on any specific data file or tablespaces. There is a field called "Av Rd (ms)", representing average time per read in milli-second. Generally speaking, 20ms - 40ms reads may be considered slow for single block reads. In this post, I used a script to count the number of tablespaces that have ATPR greater than 100ms.

----- Sample script to obtain the number of tablespaces
------ that have ATPR greater than 100ms
select mydate, sum(case when atpr_ms > 100 then 1 else 0 end)
from (
        select
             to_char(snap_time,'yyyy-mm-dd HH24') mydate,
             old.tsname,
             sum(new.phyrds-old.phyrds)   phy_rds,
             sum(new.phywrts-old.phywrts) phy_wrts,
             sum(new.readtim-old.readtim) read_tim,
             sum(new.writetim-old.writetim) write_tim,
             decode(sum(new.phyrds-old.phyrds), 0,0,
             sum(new.readtim-old.readtim) *10 / sum(new.phyrds-old.phyrds) ) atpr_ms
          from
             perfstat.stats$filestatxs old,
             perfstat.stats$filestatxs new,
             perfstat.stats$snapshot   sn
          where
             old.snap_id = sn.snap_id
          and
             old.filename = new.filename
          and
             new.snap_id = sn.snap_id + 1
          and
             (new.phyrds-old.phyrds) > 0
          and snap_time >= to_date('2009-01-26', 'YYYY-MM-DD')
          and snap_time < to_date('2009-01-31', 'YYYY-MM-DD')
          group by
             to_char(snap_time,'yyyy-mm-dd HH24'),
             old.tsname
        )
group by mydate
;

3. Grapth the I/O pattern:

Sample Graph - Total IO changes with every hour
Sample Graph - Average Time Per Read Count