Online Contents

Oracle Q&A‎ > ‎제 1 차‎ > ‎

02. index & db file sequential read

--------------------------------------------------------------------------------
-- @name: index db file sequential read
-- @author: dion cho
-- @note: test case for the impact of the index key order
--------------------------------------------------------------------------------

set pages 10000
set lines 200
set timing off
set trimspool on


select * from v$version where rownum = 1;


---------------------------------------------------------------------------
-- step 1: create objects

-- create tables

-- table for ordered( + max) insertion
drop table t_ordered purge;

create table t_ordered (
c1 number, 
c2 char(100)
);


-- table for random insertion
drop table t_random purge;

create table t_random (
c1 number, 
c2 char(100)
);


-- table for hybrid(ordered + median) insertion
drop table t_hybrid purge;

create table t_hybrid (
c1 number, 
c2 char(100)
);


-- create indexes
create index t_ordered_n1 on t_ordered(c1, c2);

create index t_random_n1 on t_random(c1, c2);

create index t_hybrid_n1 on t_hybrid(c1, c2);



--------------------------------------------------------------------------
-- step2: ordered insertion vs. random insertion


----------------------------------------------------------
-- create data
set timing on


insert into t_ordered
select
level, 
level
from
dual
connect by level <= 100000
order by
1, 2
;


insert into t_random
select
level, 
level
from
dual
connect by level <= 100000
order by
dbms_random.random
;


insert into t_hybrid
select
level, 
level
from
dual
connect by level <= 100000
order by
dbms_random.random
;

-- gather statistics 
exec dbms_stats.gather_index_stats(user, 'T_ORDERED_N1');

exec dbms_stats.gather_index_stats(user, 'T_RANDOM_N1');

exec dbms_stats.gather_index_stats(user, 'T_HYBRID_N1');


-- clustering factor and size
select
index_name,
clustering_factor,
leaf_blocks,
num_rows
from
dba_indexes
where
owner = user
and index_name in ('T_ORDERED_N1', 'T_RANDOM_N1')
order by
2 desc
;


-- compare index tree dump
spool trace1.txt

-- alter session set events 'immediate trace name treedump level <data_object_id_of_index>';

select * from table(tpack.index_tree_dump(user, 't_ordered_n1'));

spool off

spool trace2.txt

select * from table(tpack.index_tree_dump(user, 't_random_n1'));

spool off

@diff trace1.txt trace2.txt


----------------------------------------------------------
-- tpack to compare V$SESSTAT
exec tpack.begin_session_snapshot;

alter system flush buffer_cache;

-- insert ordered max values
insert into t_ordered
select
level+100001, 
level
from
dual
connect by level <= 10000
order by
1, 2
;

exec tpack.add_session_snapshot;

alter system flush buffer_cache;

-- totally random values
insert into t_random
select
level+50001, 
level
from
dual
connect by level <= 10000
order by
dbms_random.random
;

exec tpack.add_session_snapshot;

alter system flush buffer_cache;

-- ordered medium values
insert into t_hybrid
select
level+50001, 
level
from
dual
connect by level <= 10000
order by
1, 2
;

exec tpack.add_session_snapshot;


spool report1.txt

col type format a10
col item format a30
col deltas format a30

select
type,
item,
deltas 
from
table(tpack.session_snapshot_report)
;

spool off

@ed report1.txt


--------------------------------------------------------------
-- compare 10046 trace(level 8)

alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

insert into t_ordered
select
level+200001, 
level
from
dual
connect by level <= 10000
order by
1, 2
;

exec tpack.end_diag_trace(userenv('sid'), 10046);

spool trace1.txt
select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));
spool off



alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

insert into t_random
select
level+50001, 
level
from
dual
connect by level <= 10000
order by
dbms_random.random
;

exec tpack.end_diag_trace(userenv('sid'), 10046);

spool trace2.txt
select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));
spool off


alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

insert into t_hybrid
select
level+50001, 
level
from
dual
connect by level <= 10000
order by
1, 2
;

exec tpack.end_diag_trace(userenv('sid'), 10046);

spool trace3.txt
select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));
spool off


@diff trace1.txt trace2.txt

@diff trace2.txt trace3.txt


-- monitor "db file sequnetial read" event through active session history
col owner format a10
col object_name format a30
col object_type format a10

select
o.owner,
o.object_name, 
o.object_type,
count(*) as hit_cnt
from
v$active_session_history h,
v$bh b,
dba_objects o
where
h.session_id = userenv('sid')
and h.event = 'db file sequential read'
and h.p1 = b.file#
and h.p2 = b.block#
and b.objd = o.data_object_id
and b.status = 'xcur'
group by 
o.owner,
o.object_name,
object_type
order by 
4 desc
;


Comments