Friday, March 27, 2009

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;
/

Thursday, March 26, 2009

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.

Monday, March 23, 2009

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.

Wednesday, March 18, 2009

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.

Monday, March 16, 2009

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.

Sunday, March 15, 2009

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 ----------

Saturday, March 14, 2009

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 %

Tuesday, March 03, 2009

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