Category Archives: Database

UNCOMMITTED DATA IN DATAFILES

In this post, I will demonstrate various scenarios in single instance where uncommitted data gets written to datafiles.

The database writer can write uncommitted info also to datafiles as a result of following activities:

Flush buffer cache
Before a logfile gets overwritten
Manual checkpoint
To free buffers
Tablespace offline
Tablespace read only
Tablespace begin backup

– SETUP –

– create tablespace test_dbwr

SQL>drop tablespace test_dbwr including contents and datafiles;
    create tablespace test_dbwr datafile '/u01/app/oracle/test_dbwr.dbf' size 100m;

– create table test_dbwr_tab

SQL>drop table test_dbwr_tab purge;
    create table test_dbwr_tab (txt char(100)) tablespace test_dbwr;

- insert a record with txt = test_init

SQL>insert into test_dbwr_tab values ('test_init');
    commit;
    alter system checkpoint;

– find out current checkpoint#

SQL>select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
——————
983019

– Switch logfile

SQL>alter system switch logfile;

Flush buffer cache

If buffer cache containing dirty blocks is flushed, dirty blocks containing uncomitted data are written to datafiles.

— Overview–

— find out current checkpoint#
— Update record in test_dbwr_tab to ‘test_dbwr’ and do not commit
— check that datafile does not contain string ‘test_dbwr’ as checkpoint has not taken place yet
— Flush buffer cache so that all the dirty blocks in the buffer cache are
flushed to disk by database writer
— Check that checkpoint# has been incremented
— check that datafile contains the string ‘test_dbwr’ as dirty blocks
have been flushed to disk

Implementation

– find out current checkpoint#

SQL>select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            983019

– Update record in test_dbwr_tab to ‘test_dbwr’ and do not commit

SQL>update test_dbwr_tab set txt = 'test_dbwr';

-- check that datafile does not contain string ‘test_dbwr’ as checkpoint has not taken place yet

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_dbwr

– Flush buffer cache so that all the dirty blocks in the buffer cache are flushed to disk by database writer

SQL>alter system flush buffer_cache;

– Check that checkpoint# has been incremented

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            983038

– check that datafile contains the string ‘test_dbwr’ as dirty blocks have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_dbwr
dtest_dbwr

————————————————————————

Before a logfile gets overwritten

A redo log cannot be overwritten until changes recorded in it are written to disk by DBWR. Hence, before a redo log containing uncommitted data is overwritten, uncommitted changes recorded in it are written to datafiles by DBWR.

– Overview –
— check the no. of logfile groups and the current logfile group
— Check current checkpoint#
— update record to string ‘test_log_switch’ in table test_dbwr_tab
— check that datafile does not contain string ‘test_log_switch’
as dirty buffers have not been written to disk yet
— Switch logfile so that curent redo log gets overwritten
— Check that checkpoint# has incremented
— check that datafile contains the string ‘test_log_switch’ as dirty buffers
have been flushed to disk

– Implementation –
— check the no. of logfile groups and the current logfile group

SQL>select group#, status from v$log ;
   GROUP# STATUS
---------- ----------------
         4 INACTIVE
         5 INACTIVE
         6 CURRENT

– Check current checkpoint#

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983038

– update record to string ‘test_log_switch’ in table test_dbwr_tab

SQL>update test_dbwr_tab set txt = 'test_log_switch';

– check that datafile does not contain string ‘test_log_switch’  as dirty buffers have not been written to disk yet

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_log_switch

– Switch logfile so that curent redo log gets overwritten

SQL>alter system switch logfile;
    /
    /
    /
    select group#, status from v$log ;

– Check that checkpoint# has incremented

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983337

– check that datafile contains the string ‘test_log_switch’ as dirty buffers
have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_log_switch
dtest_log_switch

————————————————————————–

Manual checkpoint

Whenever checkpoint is initiated manually, all the dirty blocks containing committed/uncommitted data in buffer cache are flushed to disk .

– Overview –
— Find out current checkpoint#
— Update the record to string ‘test_checkpoint’ in table test_dbwr_tab
— check that datafile does not contain string ‘test_checkpoint’
— Perform manual checkpoint so that all the dirty buffers get written
to disk
— Check that checkpoint# has incremented
— check that datafile contains the string ‘test_checkpoint’ as dirty buffers
have been flushed to disk

– Implementation –

– Find out current checkpoint#

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983337

– Update the record to string ‘test_checkpoint’ in table test_dbwr_tab

SQL>update test_dbwr_tab set txt = 'test_checkpoint';

– check that datafile does not contain string ‘test_checkpoint’

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_checkpoint

– Perform manual checkpoint so that all the dirty buffers get written to disk

SQL>alter system  checkpoint;

– Check that checkpoint# has incremented

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983842

– check that datafile contains the string ‘test_checkpoint’ as dirty buffers have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_checkpoint
dtest_checkpoint

——————————————————————

Tablespace offline

When a tablespace is taken offline, the  dirty  buffers containinig committed/uncommitted data belonging to it are flushed to disk.

– Overview –
— Find out current checkpoint#
— Find out current checkpoint# for datafile of test_dbwr tablespace
— Update the record to string ‘test_offline’ in table test_dbwr_tab
— check that datafile does not contain string ‘test_offline’
— Take tablespace test_dbwr offline
— Note that database checkpoint# does not change
— Note that checkpoint# for the datafile for test_dbwr tablespace has been incremented
— check that datafile contains the string ‘test_offline’ as dirty buffers
— have been flushed to disk
— Bring tablespace online —

– Implementation –

– Find out current checkpoint#

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983842

– Find out current checkpoint# for datafile of test_dbwr tablespace

SQL>select checkpoint_change# from V$datafile
    where upper(name) like '%TEST_DBWR%';

CHECKPOINT_CHANGE#
------------------
            983842

– Update the record to string ‘test_offline’ in table test_dbwr_tab

SQL>update test_dbwr_tab set txt = 'test_offline';

– check that datafile does not contain string ‘test_offline’

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_offline

– Take tablespace test_dbwr offline –

SQL>alter tablespace test_dbwr offline;

– Note that database checkpoint# does not change

SQL>select checkpoint_change# from V$database;
CHECKPOINT_CHANGE#
------------------
            983842

– Note that checkpoint# for the datafile for test_dbwr tablespace has been incremented

SQL>select checkpoint_change# from V$datafile
    where upper(name) like '%TEST_DBWR%';

CHECKPOINT_CHANGE#
------------------
            984338

– check that datafile contains the string ‘test_offline’ as dirty buffers – have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_offline
dtest_offline

– Bring tablespace online –

SQL>alter tablespace test_dbwr online;

————————————————————————–

Similarly, it can be demonstrated that uncommitted data gets written to datafiles whenever
– a tablespace is put in read only mode
– a tablespace is put in backup mode

In these cases also, database checkpoint# is not incremented but checkpoint# for the datafile of the tablespace gets incremented

To free buffers in buffer cache

When a server process is looking for free buffers and they are not available, dirty buffers containing uncommitted/committed data from cold end of LRU list  will be flushed to disk. The flushed buffers may not be the ones with oldest SCN.  Hence, in this case checkpoint# is not incremented.

– Overview –

— Find out current size of database buffer cache
— Decrease size of buffer cache to small value 4M
— check that buffer cache has been shrunk to 4M
— check checkpoint# for the database —
— Update the record to string ‘test_buffer’ in table test_dbwr_tab
— Issue a query on a large table so that dirty buffers in buffer cache have to be flushed to disk in order to free buffers
— check that datafile now contain the strings ‘test_buffer’ as dirty buffers have been written to disk
— Note that checkpoint # has not changed in this case although dirty buffers have been written to disk(LRU/TCH algorithm)
— Reset buffer cache to earlier size

Implementation

– Find out current size of database buffer cache

SQL> col component for a20
     select component, current_size/1024/1024 current_mb, min_size/1024/1024 MIN_MB
     from v$sga_dynamic_components
     where component = 'DEFAULT buffer cache';

COMPONENT            CURRENT_MB     MIN_MB
-------------------- ---------- ----------
DEFAULT buffer cache         28         28

– Decrease size of buffer cache to minimum value i.e. 4M

SQL>alter system set db_cache_size=4m;

– check that buffer cache has been shrunk to 4M

SQL> select component, current_size/1024/1024 current_mb, min_size/1024/1024 MIN_MB
     from v$sga_dynamic_components
     where component = 'DEFAULT buffer cache';

OMPONENT            CURRENT_MB     MIN_MB
-------------------- ---------- ----------
DEFAULT buffer cache          4          4

– check checkpoint# for the database –

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983842

– Update the record to string ‘test_buffer’ in table test_dbwr_tab

SQL>update test_dbwr_tab set txt = 'test_buffer';

– check that datafile does not contain string ‘test_buffer”

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_buffer

– Issue a query on a large table so that dirty buffers in buffer cache have to be flushed to disk in order to free buffers

SQL>select * from sh.sales;

– check that datafile now contain the strings ‘test_buffer’ as dirty buffers have been written to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_buffer
dtest_buffer

– Note that checkpoint # has not changed in this case although dirty buffers have been written to disk(LRU/TCH algorithm)

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983842

– Reset buffer cache to earlier size

SQL>alter system set db_cache_size=44m;
    select component, current_size/1024/1024 current_mb, min_size/1024/1024 MIN_MB
     from v$sga_dynamic_components
     where component = 'DEFAULT buffer cache';

– cleanup –

SQL>drop tablespace test_dbwr including contents and datafiles;

Summary:

– When uncommitted data is flushed to disk as a result of

  •      . flush buffer cache,
  •      . manual checkpoint or
  •      . to write changes in a redo log to be overwritten,

checkpoint# for the database is incremented.

– When uncommitted data belonging to a tablespace  is flushed to disk as a result of

  •        . tablespace offline,
  •        . tablespace read only or
  •        . tablespace begin backup

checkpoint# for the database is not incremented but checkpoint# for the datafile(s) belonging to the tablespace is incremented.

– When uncommitted data   is flushed to disk to free buffers in the buffer cache,    checkpoint# for the database is not incremented .

References:

Oracle Documentation

https://community.oracle.com/thread/886580
———————————————————————–

Related links:

Home

Database  Index

Consistent Reads In Oracle : Part-I
Consistent Reads In Oracle : Part-II
Flush Buffer Cache
Oracle Checkpoints
Undo And Redo In Oracle

 

ORA-12543: TNS:destination host unreachable error

I got this error while I was trying to connect to a remote database on a linux host.

Reason : Firewall was enabled on the target host.

I disabled firewall by issuing following commands :

root@target#service iptables status
            service iptables stop
            chkconfig iptables off

Now I could make the connection.

Hope it helps…

————————————————————————————————–

Related links:

Home

Database Index

Tuning Index

 

 

———————

MULTIBLOCK READS AND CACHED BLOCKS

 

A full scan operation on a table reads multiple blocks in one I/O as opposed to single block reads which read only one block in one I/O.  The number of blocks read in one multiblock I/O   can range anywhere from one to the number of blocks specified in the db_file_multiblock_read_count parameter. For example, if the parameter is set to 64 and there are 640 blocks in the table,  the least no. of I/O’s required  to get all the blocks = 640/64 = 10. The no. of I/O’s could be more than one due  to the following limitations on multiblock read calls:

– Multiblock reads cannot span extent boundaries i.e.
if db_file_multiblock_read_count = 64 and extent size = 8 blocks, a multiblock read can read a maximum of 8 blocks only in one I/O.

– If a requested block is already in the buffer cache, it  will not be read again as part of the multiblock read.  Oracle will simply read the blocks up to those not already in memory, then issue another read call that skips those blocks to read the rest.  For example,
let’s say db_file_multiblock_read_count = 64 and
the range of blocks to be read is between block number 1 and 64. If block no. 32 is already available in the buffer cache, first multiblock read  reads would be done for blocks 1 to 31 and subsequent read will read blocks from block 33 to 64.

• Multiblock reads cannot  exceed the operating system limit for multiblock read size

In an earlier post, I had  demonstrated that multiblock reads cannot span extent boundaries.
In this post, I will demonstrate that multiblock reads skip blocks which are already cached.

Overview:

– Db_block_size = 8k
– create table MBRC_CACHE with uniform extent size = 8
– Populate table with records for id = 1 to 60 such that each block contains records for one id only
– Create index on the table on id column
– Trace the following
. Read records for id = 2
. Perform full scan on the table

– Trace file shows that
to read records for id = 2
. Index segment header block is read
. Index leaf blocks are read
. Table block containing records for id = 2 is read

to perform FTS on the table
. Table segment header block is read
. Table block containing records for id =1 is read
. Table block containing records for id = 2 is skipped
. Remaining Table blocks containing records for id = 3 to 60 are read

Implementation:

>SQL> sho parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> sho parameter db_file_multi

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 8

SQL> sho parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS<

- create table MBRC_CACHE with uniform extent size = 8

SQL> drop table mbrc_cache purge;
        create table mbrc_cache (id number , text char(700));

- Populate table with records for id = 1 to 60 such that each block contains records for one id only

begin
for i in 1..60 loop
for j in 1..10 loop
insert into mbrc_cache values (i, 'txt'||j);
commit;
end loop;
commit;
end loop;
end;
/

– check that records for each id are placed in one block each

SQL>select id, dbms_rowid.rowid_block_number(rowid),count(*)
from mbrc_cache
group by id, dbms_rowid.rowid_block_number(rowid)
order by id, dbms_rowid.rowid_block_number(rowid);

ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
---------- ------------------------------------ ----------
1                                88809         10
2                                88810         10
3                                88811         10
4                                88812         10
5                                88813         10
6                                88814         10
7                                88815         10
8                                88816         10
9                                88817         10
10                                88818         10
11                                88819         10

...
56                                88872         10
57                                88873         10
58                                88874         10
59                                88875         10
60                                88876         10

- Create index on the table on id column and gather statistics

SQL>create index mbrc_cache_idx on mbrc_cache(id);

exec dbms_stats.gather_table_stats(USER, 'MBRC_CACHE', cascade=> true);

select table_name, num_rows, blocks from user_tables where table_name= 'MBRC_CACHE';

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
MBRC_CACHE                            600         60

– Find out object_id for mbrc_cache table and mbrc_)cache_idx index

SQL>col object_name for a30
select object_name, object_id from dba_objects where object_name like 'MBRC_CACHE%';

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
MBRC_CACHE                          75140
MBRC_CACHE_IDX                      75225

– Note that for table MBRC_CACHE, segment header is in block# 88808
— Note that for index MBRC_CACHE_IDX, segment header is in block# 88880

SQL> select segment_name, header_block from dba_segments where segment_name LIKE 'MBRC_CACHE%';

SEGMENT_NAME         HEADER_BLOCK
-------------------- ------------
MBRC_CACHE                  88808
MBRC_CACHE_IDX              88880

– Note that 8 blocks (88880 – 88888) are assigned to index mbrc_cache
— Block 88880 contains segment header
— Two index leaf blocks are located in block# 88881 and 88882

SQL> select SEGMENT_NAME, BLOCK_ID, BLOCKS from dba_extents where segment_name='MBRC_CACHE_IDX';

SEGMENT_NAME           BLOCK_ID     BLOCKS
——————– ———- ———-
MBRC_CACHE_IDX            88880          8

SQL> select index_name, leaf_blocks from user_indexes where index_name=’MBRC_CACHE_IDX';

INDEX_NAME                     LEAF_BLOCKS
—————————— ———–
MBRC_CACHE_IDX                           2

- Trace the following
. Read records for id = 2
. Perform full scan on the table

conn / as sysdba

alter system flush buffer_cache;
alter session set tracefile_identifier = 'mbrc';

set serveroutput off
exec dbms_monitor.session_trace_enable(waits=> true);
select * from mbrc_cache where id=2;
select * from mbrc_cache ;

declare
cursor c1_cur  is select * from mbrc_cache where id=2;
v1_cur mbrc_cache%rowtype;
cursor c2_cur  is select * from mbrc_cache;
v2_cur mbrc_cache%rowtype;
exec dbms_monitor.session_trace_disable();

begin
open c1_cur;
fetch c1_cur into v1_cur;

loop
fetch c1_cur into v1_cur;
exit when c1_cur%NOTFOUND;
end loop ;
close c1_cur;
end;
/
exec dbms_monitor.session_trace_disable();

SQL> col name for a15
col value for a60
select name, value from v$diag_info where upper(name) like '%TRACE F%';

NAME            VALUE
--------------- ------------------------------------------------------------
Default Trace F c:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_
ile             3828_mbrc.trc

– Read trace file

SQL> ho notepad c:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_3828_mbrc.trc

- Trace file (trimmed to show only read waits) shows that
to read records for id = 2
. Index segment header block is read
. Index leaf blocks are read
. Table block containing records for id = 2 is read

to perform FTS on the table
. Table segment header block is read
. Table block containing records for id =1 is read
. Table block containing records for id = 2 is skipped
. Remaining Table blocks containing records for id = 3 to 60 are read

=====================
PARSING IN CURSOR #1 len=39 dep=0 uid=0 oct=3 lid=0 tim=283016701180 hv=2279949904 ad=’7ff0da36078′ sqlid=’42bhbr63yajkh’
select * from mbrc_cache where id=2
END OF STMT
PARSE #1:c=0,e=1668,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3589681151,tim=283016701177
EXEC #1:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3589681151,tim=283016701337

– Read index mbrc_cache_idx (object_id  =75225 )
— Read two index leaf blocks  88881 and 88882 (as found earlier)

WAIT #1: nam=’db file sequential read’ ela= 1045 file#=1 block#=88881 blocks=1 obj#=75225 tim=283016702567
WAIT #1: nam=’db file sequential read’ ela= 20967 file#=1 block#=88882 blocks=1 obj#=75225 tim=283016723719

– Read table mbrc_cache (object_id = 75140)
— Read block 88810 contaning records for id = 2 (as found earlier)

WAIT #1: nam=’db file sequential read’ ela= 14929 file#=1 block#=88810 blocks=1 obj#=75140 tim=283016738901

WAIT #1: nam=’SQL*Net message from client’ ela= 72910 driver id=1111838976 #bytes=1 p3=0 obj#=75140 tim=283016812709
CLOSE #1:c=0,e=32,dep=0,type=0,tim=283016812906
=====================

PARSING IN CURSOR #2 len=29 dep=0 uid=0 oct=3 lid=0 tim=283016814397 hv=4285579899 ad=’7ff097aab18′ sqlid=’1qvcxnbzr1hmv’
select * from mbrc_cache
END OF STMT
PARSE #2:c=0,e=1425,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2256522414,tim=283016814394
EXEC #2:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2256522414,tim=283016814549

– During FTS, read segment header for table mbrc_cache (header block# = 88808 as found earlier)

WAIT #2: nam=’db file sequential read’ ela= 1843 file#=1 block#=88808 blocks=1 obj#=75140 tim=283016816604

– Read block 88809 containing rows for id = 1 ( as found earlier)

WAIT #2: nam=’db file sequential read’ ela= 384 file#=1 block#=88809 blocks=1 obj#=75140 tim=283016817170

– Skip block 88810 containing records for id=2 as that block has already been read into buffer
— Read 5 remaining blocks in the extent starting from block 88811

WAIT #2: nam=’db file scattered read’ ela= 22144 file#=1 block#=88811 blocks=5 obj#=75140 tim=283016947620

– Read subdsequent extents in units of 8 blocks

WAIT #2: nam=’db file scattered read’ ela= 302 file#=1 block#=88816 blocks=8 obj#=75140 tim=283017221664

WAIT #2: nam=’db file scattered read’ ela= 33 file#=1 block#=88824 blocks=8 obj#=75140 tim=283017573336

WAIT #2: nam=’db file scattered read’ ela= 17389 file#=1 block#=88832 blocks=8 obj#=75140 tim=283017918715

WAIT #2: nam=’db file scattered read’ ela= 496 file#=1 block#=88840 blocks=8 obj#=75140 tim=283018212031

WAIT #2: nam=’db file scattered read’ ela= 28 file#=1 block#=88848 blocks=8 obj#=75140 tim=283018477971

WAIT #2: nam=’db file scattered read’ ela= 31 file#=1 block#=88856 blocks=8 obj#=75140 tim=283018786018

– Read 5 blocks  from last extent
— Last block 88876 contains records for id = 60 (as found earlier)
— Read 4 blocks ( 88872 – 88876) containing data
— Read one more block to ensure that there are no more blocks containing data

WAIT #2: nam=’db file scattered read’ ela= 28 file#=1 block#=88872 blocks=5 obj#=75140 tim=283019045215

WAIT #2: nam=’SQL*Net message from client’ ela= 54378 driver id=1111838976 #bytes=1 p3=0 obj#=75140 tim=283019256210
CLOSE #2:c=0,e=25,dep=0,type=0,tim=283019260582

=====================

Conclusion:

– If a requested block is already in the buffer cache, it  will not be read again as part of the multiblock read.  Oracle will simply read the blocks up to those not already in memory, then issue another read call that skips those blocks to read the rest.

References:

Pro Oracle SQL by Karen Morton, Kerry Osborne,  Robyn Sands, Riyaz and Jared Still

Oracle Documentation

————————————————————————————————-

Related links:

Home

Tuning Index

Oracle always full scans tables smaller than DBFMRC:  A myth

DB_FILE_MULTIBLOCK_READ_COUNT And Extent Size

 

——————–

 

DB_FILE_MULTIBLOCK_READ_COUNT AND EXTENT SIZE

A full scan operation on a table reads multiple blocks in one I/O as opposed to single block reads which read only one block in one I/O. The number of blocks read in one multiblock I/O can range anywhere from one to the number of blocks specified in the db_file_multiblock_read_count parameter. For example, if the parameter is set to 64 and there are 640 blocks in the table, the least no. of I/O’s required to get all the blocks = 640/64 = 10.

The no. of I/O’s could be more than one due to the following limitations on multiblock read calls:

– Multiblock reads cannot span extent boundaries i.e.
if db_file_multiblock_read_count = 64 and extent size = 8 blocks, a multiblock read can read a maximum of 8 blocks only in one I/O.

– If a requested block is already in the buffer cache, it will not be read again as part of the multiblock read. Oracle will simply read the blocks up to those not already in memory, then issue another read call that skips those blocks to read the rest. For example,
let’s say db_file_multiblock_read_count = 64 and
the range of blocks to be read is between block number 1 and 64. If block no. 32 is already available in the buffer cache, first multiblock read reads would be done for blocks 1 to 31 and subsequent read will read blocks from block 33 to 64.

• Multiblock reads cannot exceed the operating system limit for multiblock read size

In this post, I will demonstrate that multiblock reads cannot span extent boundaries.

In my next post, I will demonstrate that multiblock reads skip the bocks which already cached.

 

Overview:

– Db_block_size = 8k
– Set db_file_multiblock_read_count = 64
– create two tables with identical data :
. table mbrc_ext_64k with uniform extent size = 8
. table mbrc_ext_512k with uniform extent size = 64
. table mbrc_ext_1024k with uniform extent size = 128

– Trace FTS on both the tables and check the trace file
– Trace file shows that
. I/O’s made to mbrc_ext_64k are of size 8 blocks (=extent size)
. I/O’s made to mbrc_ext_512k are of size 64 blocks(= db_file_multiblock_read_count )
. I/O’s made to mbrc_ext_1024k are of size 64 blocks(= db_file_multiblock_read_count )

Implementation:

– check that db_block_size = 8k

SQL> sho parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- -----------------
db_block_size integer 8192

— Set db_file_multiblock_read_count = 64

SQL> alter system set db_file_multiblock_read_count =64
sho parameter db_file_multi

NAME TYPE VALUE
------------------------------------ ----------- -----------------
db_file_multiblock_read_count integer 64

— check that optimizer_mode = all_rows

SQL> sho parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- -----------------
optimizer_mode string ALL_ROWS

— Create tablespaces
. tbs_64k – Uniform Extent size = 64K = 8 blocks
. tbs_512k – Uniform Extent size = 512K = 64 blocks
. tbs_1024k- Uniform Extent size = 512K = 128 blocks

SQL>drop tablespace tbs_64k including contents and datafiles;
create tablespace tbs_64k datafile 'C:\APP\mbrc8k.dbf' size 100m reuse autoextend on
extent management local uniform size 64k segment space management manual;

drop tablespace tbs_512k including contents and datafiles;
create tablespace tbs_512k datafile 'C:\APP\mbrc512k.dbf' size 100m reuse autoextend on
extent management local uniform size 512k segment space management manual;

drop tablespace tbs_1024k including contents and datafiles;
create tablespace tbs_1024k datafile 'C:\APP\mbrc1024k.dbf' size 100m reuse autoextend on
extent management local uniform size 1024k segment space management manual;

– create three tables with identical data :
. table mbrc_ext_64k in tbs_64k with uniform extent size = 8 blocks
. table mbrc_ext_512k in tbs_512k with uniform extent size = 64 blocks
. table mbrc_ext_1024k in tbs_1024k with uniform extent size = 128 blocks

drop table mbrc_ext_64k purge;
create table mbrc_ext_64k tablespace tbs_64k as select * from sh.sales where rownum<=50000;

drop table mbrc_ext_512k purge;
create table mbrc_ext_512k tablespace tbs_512k as select * from sh.sales where rownum<=50000;

drop table mbrc_ext_1024k purge;
create table mbrc_ext_1024k tablespace tbs_1024k as select * from sh.sales where rownum<=50000;

— Gather statistics

exec dbms_stats.gather_table_stats(USER, 'MBRC_EXT_64K', cascade=> true);
exec dbms_stats.gather_table_stats(USER, 'MBRC_EXT_512K', cascade=> true);
exec dbms_stats.gather_table_stats(USER, 'MBRC_EXT_1024K', cascade=> true);

— check that extent size for
. MBRC_EXT_64K = 8
. MBRC_EXT_512K = 64
. MBRC_EXT_512K = 128

SQL>col segment_name for a20
select segment_name, blocks, count(*)
from dba_extents
where segment_name like 'MBRC_EXT_%K'
group by segment_name, blocks;

SEGMENT_NAME BLOCKS COUNT(*)
-------------------- ---------- ----------
MBRC_EXT_1024K 128 2
MBRC_EXT_64K 8 31
MBRC_EXT_512K 64 4

— Find out object_id’s of the tables

SQL>col object_name for a30
select object_name, object_id from dba_objects where object_name like 'MBRC_EXT_%K%';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
MBRC_EXT_1024K 75139
MBRC_EXT_512K 75131
MBRC_EXT_64K 75130

— Enable tracing and per Full table scan of the tables

conn / as sysdba

alter system flush buffer_cache;
alter session set tracefile_identifier = 'mbrc';

set serveroutput off
exec dbms_monitor.session_trace_enable(waits=> true);

declare
cursor c1_cur is select * from mbrc_ext_64k;
cursor c2_cur is select * from mbrc_ext_512k;
cursor c3_cur is select * from mbrc_ext_1024k;
v1_cur mbrc_ext_64k%rowtype;
v2_cur mbrc_ext_512k%rowtype;
v3_cur mbrc_ext_1024k%rowtype;

begin
open c1_cur;
loop
fetch c1_cur into v1_cur;
exit when c1_cur%NOTFOUND;
end loop ;
close c1_cur;

open c2_cur;
loop
fetch c2_cur into v2_cur;
exit when c2_cur%NOTFOUND;
end loop ;
close c2_cur;

open c3_cur;
loop
fetch c3_cur into v3_cur;
exit when c3_cur%NOTFOUND;
end loop ;
close c3_cur;

end;
/
exec dbms_monitor.session_trace_disable();

— Find out name of trace file generated

SQL>col value for a80
select name, value from v$diag_info where upper(name) like '%TRACE F%';

NAME VALUE
-------------------- -------------------------------------------------------------------------
Default Trace File c:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_5476_mbrc.trc

— Read the trace file

SQL>ho notepad c:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_5476_mbrc.trc

=====================

In MBRC_EXT_64K (object_id = 75130)

Single block read identified by wait event ‘db file sequential read’
. Reads block# 128

— Let’s verify that block# 128 contains segment header

SQL> select segment_name, header_block from dba_segments where segment_name = 'MBRC_EXT_64k';

SEGMENT_NAME HEADER_BLOCK
-------------------- ------------
MBRC_EXT_64K 128

First multiblock read (identified by wait event ‘db file scattered read’)
.starting at block# 129 (Block next to segment header)
.No. of blocks read = 7 (blocks=7= remainig blocks in the extent)

Subsequent multiblock reads (identified by wait event ‘db file scattered read’)
. from block# 136 (129+7) onwards
. No. of blocks read = 8 (blocks = 8)

Last multiblock read (identified by wait event ‘db file scattered read’)
. starting at block# 368
. No. of blocks read = 2 (blocks = 2 = remaining blocks containing data)

Last block# read = 368 + 2 (in last wait, starting block = 368 , blocks read = 2)
= 370
First block# read (after segment header) = 129

Total no. of blocks scanned = 370 – 129
= 241

— Let’s verify that this no. matches the No. of blocks containing data as stored in data dictionary

SQL>select table_name, blocks from user_tables where table_name = 'MBRC_EXT_64K';

TABLE_NAME BLOCKS
------------------------------ ----------
MBRC_EXT_64K 241

=====================

PARSING IN CURSOR #2 len=26 dep=1 uid=0 oct=3 lid=0 tim=249360274955 hv=1652364071 ad=’7ff09780df8′ sqlid=’d3ubygdj7u4t7′
SELECT * FROM MBRC_EXT_64K
END OF STMT

PARSE #2:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3512697650,tim=249360274953
EXEC #2:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3512697650,tim=249360275088

WAIT #2: nam=’Disk file operations I/O’ ela= 211 FileOperation=2 fileno=7 filetype=2 obj#=75130 tim=249360275427

WAIT #2: nam=‘db file sequential read’ ela= 22023 file#=7 block#=128 blocks=1 obj#=75130 tim=249360297493

WAIT #2: nam=‘db file scattered read’ ela= 1305 file#=7 block#=129 blocks=7 obj#=75130 tim=249360299150

WAIT #2: nam=‘db file scattered read‘ ela= 951 file#=7 block#=136 blocks=8 obj#=75130 tim=249360386033

WAIT #2: nam=‘db file scattered read’ ela= 478 file#=7 block#=144 blocks=8 obj#=75130 tim=249360487978

WAIT #2: nam=‘db file scattered read‘ ela= 459 file#=7 block#=152 blocks=8 obj#=75130 tim=249360584467
….


WAIT #2: nam=’db file scattered read‘ ela= 213 file#=7 block#=368 blocks=2 obj#=75130 tim=249362258692

STAT #2 id=1 cnt=50000 pid=0 pos=1 obj=75130 op=’TABLE ACCESS FULL MBRC_EXT_64K (cr=50006 pr=242 pw=0 time=251918 us cost=64 size=1450000 card=50000)’

CLOSE #2:c=0,e=4,dep=1,type=3,tim=249362277148
=====================

=====================

In MBRC_EXT_512K (object_id = 75131)

Single block read identified by wait event ‘db file sequential read’
. Reads block# 128

— Let’s verify that block# 128 contains segment header

SQL> select segment_name, header_block from dba_segments where segment_name = 'MBRC_EXT_512k';

SEGMENT_NAME HEADER_BLOCK
-------------------- ------------
MBRC_EXT_512K 128

First multiblock read (identified by wait event ‘db file scattered read’)
.starting at block# 129 (Block next to segment header)
.No. of blocks read = 63 (blocks=63 = remainig blocks in the extent)

Subsequent multiblock reads (identified by wait event ‘db file scattered read’)
. from block# 192 (129+63) onwards
. No. of blocks read = 64 (blocks = 64)

Last multiblock read (identified by wait event ‘db file scattered read’)
. starting at block# 320
. No. of blocks read = 50 (blocks = 50 = remaining blocks containing data)

Last block# read = 320 + 50 (in last wait, starting block = 320 , blocks read = 50)
= 370
First block# read (after segment header) = 129

Total no. of blocks scanned = 370 – 129
= 241

— Let’s verify that this no. matches the No. of blocks containing data as stored in data dictionary

SQL>select table_name, blocks from user_tables where table_name = 'MBRC_EXT_512K';

TABLE_NAME BLOCKS
------------------------------ ----------
MBRC_EXT_512K 241

=====================
PARSING IN CURSOR #2 len=27 dep=1 uid=0 oct=3 lid=0 tim=249362277315 hv=3723803804 ad=’7ff097d1ec8′ sqlid=’grmbfwvfz9g4w’
SELECT * FROM MBRC_EXT_512K
END OF STMT
PARSE #2:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=748809365,tim=249362277313
EXEC #2:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=748809365,tim=249362277446

WAIT #2: nam=’Disk file operations I/O’ ela= 191 FileOperation=2 fileno=8 filetype=2 obj#=75131 tim=249362277741

WAIT #2: nam=’db file sequential read‘ ela= 18302 file#=8 block#=128 blocks=1 obj#=75131 tim=249362296085

WAIT #2: nam=‘db file scattered read’ ela= 4297 file#=8 block#=129 blocks=63 obj#=75131 tim=249362300903

WAIT #2: nam=‘db file scattered read‘ ela= 2675 file#=8 block#=192 blocks=64 obj#=75131 tim=249362851282

WAIT #2: nam=‘db file scattered read‘ ela= 3372 file#=8 block#=256 blocks=64 obj#=75131 tim=249363235459

WAIT #2: nam=‘db file scattered read‘ ela= 2651 file#=8 block#=320 blocks=50 obj#=75131 tim=249363612810

STAT #2 id=1 cnt=50000 pid=0 pos=1 obj=75131 op=’TABLE ACCESS FULL MBRC_EXT_512K (cr=50003 pr=242 pw=0 time=192560 us cost=64 size=1450000 card=50000)’

CLOSE #2:c=0,e=2,dep=1,type=3,tim=249363956416
EXEC #1:c=3634823,e=3681748,p=484,cr=100009,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=249363956444
WAIT #1: nam=’SQL*Net message to client’ ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=75131 tim=249363956494
WAIT #1: nam=’SQL*Net message from client’ ela= 2159 driver id=1111838976 #bytes=1 p3=0 obj#=75131 tim=249363958679
CLOSE #1:c=0,e=38,dep=0,type=0,tim=249363958919
=====================
=====================
=====================

In MBRC_EXT_1024K (object_id = 75139)

Single block read identified by wait event ‘db file sequential read’
. Reads block# 128

— Let’s verify that block# 128 contains segment header

SQL> select segment_name, header_block from dba_segments where segment_name = 'MBRC_EXT_1024k';

SEGMENT_NAME HEADER_BLOCK
-------------------- ------------
MBRC_EXT_1024K 128

First multiblock read (identified by wait event ‘db file scattered read’)
.starting at block# 129 (Block next to segment header)
.No. of blocks read = 64 (blocks=64 = db_file_multiblock_count since extent contains 128 blocks and there are still 127 blocks left in the extent)

Second multiblock read (identified by wait event ‘db file scattered read’)
.starting at block# 163 (129 + 64)
.No. of blocks read = 63 (blocks=63 = remaining blocks in the extent)

Subsequent multiblock reads (identified by wait event ‘db file scattered read’)
. from block# 256 (193 +63) onwards
. No. of blocks read = 64 (blocks = 64)

Last multiblock read (identified by wait event ‘db file scattered read’)
. starting at block# 320
. No. of blocks read = 50 (blocks = 50 = remaining blocks containing data)

Last block# read = 320 + 50 (in last wait, starting block = 320 , blocks read = 50)
= 370
First block# read (after segment header) = 129

Total no. of blocks scanned = 370 – 129
= 241

— Let’s verify that this no. matches the No. of blocks containing data as stored in data dictionary

SQL>select table_name, blocks from user_tables where table_name = 'MBRC_EXT_1024K';

TABLE_NAME BLOCKS
------------------------------ ----------
MBRC_EXT_1024K 241

==================================

PARSING IN CURSOR #2 len=28 dep=1 uid=0 oct=3 lid=0 tim=262780042519 hv=4010235831 ad=’7ff0d84e9e8′ sqlid=’7rs934mrhfpxr’
SELECT * FROM MBRC_EXT_1024K
END OF STMT

PARSE #2:c=15601,e=4201,p=0,cr=21,cu=0,mis=1,r=0,dep=1,og=1,plh=3425801585,tim=262780042518
EXEC #2:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3425801585,tim=262780042611
WAIT #2: nam=’Disk file operations I/O’ ela= 169 FileOperation=2 fileno=9 filetype=2 obj#=75139 tim=262780042872

WAIT #2: nam=‘db file sequential read‘ ela= 6916 file#=9 block#=128 blocks=1 obj#=75139 tim=262780049822

WAIT #2: nam=’db file scattered read‘ ela= 3837 file#=9 block#=129 blocks=64 obj#=75139 tim=262780053909

WAIT #2: nam=‘db file scattered read‘ ela= 2422 file#=9 block#=193 blocks=63 obj#=75139 tim=262780770697

WAIT #2: nam=’db file scattered read‘ ela= 2005 file#=9 block#=256 blocks=64 obj#=75139 tim=262781424869

WAIT #2: nam=db file scattered read‘ ela= 40794 file#=9 block#=320 blocks=50 obj#=75139 tim=262782155450

STAT #2 id=1 cnt=50000 pid=0 pos=1 obj=75139 op=’TABLE ACCESS FULL MBRC_EXT_1024K (cr=50003 pr=242 pw=0 time=274738 us cost=64 size=1450000 card=50000)’
CLOSE #2:c=0,e=3,dep=1,type=3,tim=262782568794
EXEC #1:c=7020045,e=7580902,p=732,cr=150081,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=262782568832
WAIT #1: nam=’SQL*Net message to client’ ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=75139 tim=262782568901
WAIT #1: nam=’SQL*Net message from client’ ela= 2776 driver id=1111838976 #bytes=1 p3=0 obj#=75139 tim=262782571712
CLOSE #1:c=0,e=44,dep=0,type=0,tim=262782571842

=========================
Conclusion:

– During FTS, segment header is accessed using single block read first to get the extent map.
– During first multiblock read,
if remaining blocks in the extent < db_file_multiblock_read_count
remaining blocks in the first extent (excluding segmnet header block) are read.
else (remaining blocks in the extent >= db_file_multiblock_read_count
blocks = db_file_multiblock_read_count are read

– During subsequent multiblock reads,
if remaining blocks in the extent < db_file_multiblock_read_count
remaining blocks in the extent are read.
else (remaining blocks in the extent >= db_file_multiblock_read_count
blocks = db_file_multiblock_read_count are read

– During last multiblock read, remaining blocks containing data are read.

– If extent size <= db_file_multiblock_read_count
No. of blocks read in one I/O = No. of blocks in one extent

– If extent size > db_file_multiblock_read_count
No. of blocks read in one I/O = least (db_file_multiblock_read_count, no. of blocks containing data)

Hence, we have been able to verify that multiblock reads cannot span extents and maximum no. of blocks that can be read in one I/O = db_file_multiblock_read_count

References:

https://forums.oracle.com/forums/thread.jspa?messageID=11051591#11051591

———————————————————————————

Related links:

Home

Tuning Index
Multiblock Reads And Cached Blocks
Oracle Always Full Scans Tables Smaller Than DFMBRC: A Myth

 

ORACLE ALWAYS FULL SCANS A TABLE SMALLER THAN DB_FILE_MULIBLOCK_READ_COUNT : A MYTH

Many of us believe that optimizer will prefer FTS to index access on a table whose size is smaller than DB_FILE_MULIBLOCK_READ_COUNT as whole table can be accessed with just a single I/O as compared to at least two  I/O’s ( 1 index block and 1 data block) in case of index access.

Well, that is not always true!

Let’s demonstrate …

SQL> sho parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> sho parameter db_file_multiblock_read_count

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 8

SQL> sho parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS

- Create a table whose size = db_file_multiblock_read_count
i.e. = 8 blocks and has a unique index on id column

SQL> drop table mbrc_7b purge;
create table mbrc_7b (id number primary key, text char(700));

begin
for i in 1..60 loop
insert into mbrc_7b values (i, 'txt'||i);
commit;
end loop;
end;
/

exec dbms_stats.gather_table_stats(USER, 'MBRC_7B', cascade=> true);

– Check that table’s HWM is set to 7 blocks

select table_name, blocks from user_tables where table_name= 'MBRC_7B';

TABLE_NAME BLOCKS
—————————— ———-
MBRC_60K 7

– check that table has been assigned 8 blocks

SQL> select segment_name, min_extents, blocks, extents
from dba_segments
where segment_name like '%MBRC%';

SEGMENT_NA MIN_EXTENTS BLOCKS EXTENTS
———- ———– ———- ———-
MBRC_7B 1 8 1

- Note that the following query uses index access  although table size =db_file_multiblock_read_count and can be read in one multi block I/O

SQL> set autotrace traceonly
select * from mbrc_7b where id =1;
set autotrace off

Execution Plan
———————————————————-
Plan hash value: 4289258908

Execution Plan
———————————————————-
Plan hash value: 4289258908

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 704 | 1 (0)| 00:00:09 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_7B | 1 | 704 | 1 (0)| 00:00:09 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0014075 | 1 | | 0 (0)| 00:00:01 |

Predicate Information (identified by operation id):
—————————————————

2 – access(“ID”=1)
Statistics
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1089 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

- Let’s verify that the if FTS is used,  cost of the query is more (4) as compared to index access (1).

SQL>set autotrace traceonly
select /*+ full (s) */ * from mbrc_7b s where id =1;
set autotrace traceonly

Execution Plan
———————————————————-
Plan hash value: 1640139338

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 704 | 4 (0)| 00:00:35 |
|* 1 | TABLE ACCESS FULL| MBRC_7B | 1 | 704 | 4 (0)| 00:00:35 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“ID”=1)
Statistics
———————————————————-
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1179 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2
– we can see that cost of FTS is more than index access
The reason as explained by Oracle Guru Richard Foote is :

Oracle needs to read the table segment header in order to determine what blocks need to be accessed to begin the FTS operation.

Oracle begins a FTS function call by accessing the segment header of the segment as it contains vital info required for the FTS operation.

However an index scan (but not a Fast Full Index Scan) begins by directly accessing the index root block (or single leaf block if it’s a level 0 index). there is nothing within the index segment header that’s required for an index scan, so the segment header is an “overhead” we can save and potentially taken advantage of.

Oracle must visit the table segment header during a FTS because it contains vital information necessary to perform the FTS, namely the extent map and the High Water Mark (HWM) associated with the table.

During an index scan operation, there’s nothing of interest within the index segment header. The critical index block, the index block by which all index scans must start is the root block of the index (except Fast Full Index Scans which are basically the FTS equivalent for indexes). There’s no need to access the index segment header because it’s the root block that actually contains all the necessary information by which to start the index scan operation. The root blocks contains the pointers to subsequent index blocks (be it a branch or leaf blocks) that Oracle needs to follow in order to find the index entry of interest.

with a Unique index, there can only be a maximum of 1 row returned. Therefore, when selecting this one row, Oracle doesn’t have to perform the second fetch operation to confirm there are indeed no more rows to return

– Let’s see what happens if we access all the rows with a predicate id between <min_val> and <max_val>

— Note that

  • Optimizer chooses FTS !!!
  • Cost of the query is 4 units i.e same as earlier with predicate where id=1
  • Consistent gets = 13
SQL> set autotrace traceonly
select * from mbrc_7b where id between 1 and 60;
set autotrace off
Execution Plan
----------------------------------------------------------
Plan hash value: 1640139338

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 42240 | 4 (0)| 00:00:35 |
|* 1 | TABLE ACCESS FULL| MBRC_7B | 60 | 42240 | 4 (0)| 00:00:35 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID">=1 AND "ID"<=60)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
43490 bytes sent via SQL*Net to client
449 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
60 rows processed

- Let’s verify that the cost of the query is more if index is used
– Note that index range scan has CPU cost of 1 as compared of 0 when index unique scan was used

– Cost of the query is 7 units as compared to 4 for FTS
– No. of consistent gets are 15 as compared to 13 in FTS

SQL>set autotrace traceonly
select /*+ index (s) */ * from mbrc_7b s where id between 1 and 60;
set autotrace off
Execution Plan
----------------------------------------------------------
Plan hash value: 1386006575

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 42240 | 7 (0)| 00:01:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_7B | 60 | 42240 | 7 (0)| 00:01:01 |
|* 2 | INDEX RANGE SCAN | SYS_C0014075 | 60 | | 1 (0)| 00:00:09 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=1 AND "ID"<=60)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
43490 bytes sent via SQL*Net to client
449 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
60 rows processed

*****************************************

Hence, it can be seen that whereas cost of FTS remains at 4 irrespective of the predicate, cost of index access increases from 1 to 7 as no. of data blocks accessed increase.

Logically speaking, there should be a threshold no. of data blocks accessed above which the execution plan changes from Index access to FTS.  After trying various ranges, I found out that plan switches from index access to FTS as id range changes from 1 – 29 to 1 – 30.

(Please note that above values might vary from  system to system  due to the difference in system statistics. )

Let’s verify…

— Note that index access is used for id range 1 – 29

SQL> set autotrace traceonly

select * from mbrc_7b where id between 1 and 29;

set autotrace off

29 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1386006575

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 20416 | 4 (0)| 00:00:3
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_7B | 29 | 20416 | 4 (0)| 00:00:3
|* 2 | INDEX RANGE SCAN | SYS_C0014075 | 29 | | 1 (0)| 00:00:0
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=1 AND "ID"<=29)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
21251 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
29 rows processed

– Note that plan changes to FTS as upper limit of the range changes to 30

SQL> set autotrace traceonly

select * from mbrc_7b where id between 1 and 30;

set autotrace off

30 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1640139338

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 21120 | 4 (0)| 00:00:35 |
|* 1 | TABLE ACCESS FULL| MBRC_7B | 30 | 21120 | 4 (0)| 00:00:35 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"<=30 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
21960 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

– Note that for  id between 1 and 30 indexed access costs 5 units whereas FTS cost is 4.

SQL> set autotrace traceonly
select /*+ index(s) */ * from mbrc_7b s where id between 1 and 30;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 1386006575

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 21120 | 5 (0)| 00:00:44 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_7B | 30 | 21120 | 5 (0)| 00:00:44 |
|* 2 | INDEX RANGE SCAN | SYS_C0014075 | 30 | | 1 (0)| 00:00:09 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=1 AND "ID"<=30)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
21960 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

— Let’s have a table with non unique index –

SQL> drop table mbrc_nu purge;
create table mbrc_nu (id number , text char(700));

begin
for i in 1..60 loop
for j in 1..10 loop
insert into mbrc_nu values (i, 'txt'||j);
commit;
end loop;
commit;
end loop;
end;
/

– Check that the table has 10 records for each value of id

select id, count(*) from mbrc_nu group by id order by id;
ID COUNT(*)
--------- ----------
1 10
2 10
3 10
4 10
...
59 10
60 10

– create non unique index on the table and gather statistics

create index mbrc_nu_idx on mbrc_nu(id);
exec dbms_stats.gather_table_stats(USER, 'MBRC_NU', cascade=> true);

select table_name, num_rows, blocks from user_tables where table_name= 'MBRC_NU';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
MBRC_NU 600 60
SQL> select segment_name, min_extents, blocks, extents
from dba_segments
where segment_name like '%MBRC_NU%';

SEGMENT_NAME MIN_EXTENTS BLOCKS EXTENTS
--------------- ----------- ---------- ----------
MBRC_NU 1 64 8
MBRC_NU_IDX 1 8 1

– Note that the index has a low clustering factor of 60 close to no. of  blocks (60) ) i.e rows for a key value are placed in the same block

SQL>col index_name for a15
Col table_name for a15
select index_name, table_name,blevel, leaf_blocks, clustering_factor
from user_indexes
where index_name = 'MBRC_NU_IDX';

INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
--------------- --------------- ---------- ----------- -----------------
MBRC_NU_IDX MBRC_NU 1 2 60

– check that all 10 rows with id = 1 are in the same block

sql>select count(*) , id, dbms_rowid.rowid_block_number(rowid)
from mbrc_nu
where id = 1
group by id, dbms_rowid.rowid_block_number(rowid);

COUNT(*) ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
--------- ---------- ------------------------------------
10 1 100025

– Note that for predicate id = 1,  index is used as only one table block needs to be accessed

SQL> set autotrace traceonly
select * from mbrc_nu where id =1;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 1257710425

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 7040 | 3 (0)| 00:00:26 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_NU | 10 | 7040 | 3 (0)| 00:00:26 |
|* 2 | INDEX RANGE SCAN | MBRC_NU_IDX | 10 | | 1 (0)| 00:00:09 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
7654 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

* Let’s check cost of plan if FTS is used
* Note that cost of the plan is much higher (24) as compared to index scan(3)

SQL>set autotrace traceonly
select /*+ full(s) */ * from mbrc_nu  where id =1;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 112258944

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 7040 | 24 (0)| 00:03:27 |
|* 1 | TABLE ACCESS FULL| MBRC_NU | 10 | 7040 | 24 (0)| 00:03:27 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
7602 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

Just like in case of unique indexed table, here also execution plan will change from Index access to FTS when more than a threshold range of id’s are queried

Here the threshold is id = 1 to 21 i.e. whenever more than 21 table blocks need to be queried, FTS will be used.

Let’s verify
* Note that Index access is used for range 1 – 21
* Note that cost of index access is 24 which is same as FTS as seen earlier

SQL> set autotrace traceonly
select * from mbrc_nu where id between 1 and 21;
set autotrace off
Execution Plan
----------------------------------------------------------
Plan hash value: 1257710425

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 213 | 146K| 24 (0)| 00:03:27 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_NU | 213 | 146K| 24 (0)| 00:03:27 |
|* 2 | INDEX RANGE SCAN | MBRC_NU_IDX | 213 | | 2 (0)| 00:00:18 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=1 AND "ID"<=21)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
77 consistent gets
0 physical reads
0 redo size
151313 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
210 rows processed

* Let’s check for range 1 – 22
* Note that plan switches to FTS as from this range onwards
Cost of index access is more than FTS

SQL> set autotrace traceonly
select * from mbrc_nu where id between 1 and 22;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 112258944

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 224 | 154K| 24 (0)| 00:03:27 |
|* 1 | TABLE ACCESS FULL| MBRC_NU | 224 | 154K| 24 (0)| 00:03:27 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"<=22 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
78 consistent gets
0 physical reads
0 redo size
158543 bytes sent via SQL*Net to client
569 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
220 rows processed

* Let’s verify that from this range onwards Cost of index access is more than FTS
* Note than cost of index access for id = 1 – 22  is 25 which is more than that of FTS (24)

SQL> set autotrace traceonly
select /*+ index (s) */ * from mbrc_nu s where id between 1 and 22;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 1257710425

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 224 | 154K| 25 (0)| 00:03:36 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_NU | 224 | 154K| 25 (0)| 00:03:36 |
|* 2 | INDEX RANGE SCAN | MBRC_NU_IDX | 224 | | 2 (0)| 00:00:18 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=1 AND "ID"<=22)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
158543 bytes sent via SQL*Net to client
569 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
220 rows processed

Hence, it can be concluded that as no. of blocks accessed increase, cost of FTS remains same but cost of index access increases. The switching of plan from Index access to FTS takes place after a thershold cost of index access becomes more than FTS.

In case of non unique index, we need to consider another factor i.e clustering factor of index. In the scenario above, clustering factor of the index was low (approached no. of blocks) and all the rows belonging to same id were placed in the same block and hence only one table block needed to be accessed for each value of id. But if clustering factor of the index is high (approaches no. of rows), rows of an id are scattered across a large no. of blocks and cost of index access increases. In that case switching from index access to FTS might occur at a smaller threshold.

Let’s verify

- create another table mbrc_hi_cf from mbrc_nu with rows ordered randomly

sql>drop table mbrc_hi_cf purge;
create table mbrc_hi_cf as
select * from
(select * from mbrc_nu order by dbms_random.random)
order by dbms_random.random;

create index mbrc_hi_cf_idx on mbrc_hi_cf(id);

exec dbms_stats.gather_table_stats(USER, 'MBRC_HI_CF', cascade=> true);

select table_name, num_rows, blocks from user_tables where table_name= 'MBRC_HI_CF';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
MBRC_NU 600 60

SQL>
SQL> col segment_name for a20
select segment_name, min_extents, blocks, extents
from dba_segments
where segment_name like '%MBRC_HI_CF%';

SEGMENT_NAME MIN_EXTENTS BLOCKS EXTENTS
-------------------- ----------- ---------- ----------
MBRC_HI_CF 1 64 8
MBRC_HI_CF_IDX 1 8 1

– Note that the index has a high clustering factor of 565 close to rows (600) )i.e rows for a key value are scattered acorss different blocks

SQL>col index_name for a15
Col table_name for a15
select index_name, table_name,blevel, leaf_blocks, clustering_factor
from user_indexes
where index_name = 'MBRC_HI_CF_IDX';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
--------------- --------------- ---------- ----------- -----------------
MBRC_HI_CF_IDX MBRC_HI_CF 1 2 565

— check that 10 rows with id = 1 are scattered across 10 blocks

sql>select count(*) , id, dbms_rowid.rowid_block_number(rowid)
from mbrc_hi_cf
where id = 1
group by id, dbms_rowid.rowid_block_number(rowid);

COUNT(*) ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ---------- ------------------------------------
1 1 109200
1 1 109227
1 1 109231
1 1 109186
1 1 109203
1 1 109210
1 1 109188
1 1 100092
1 1 109196
1 1 109234

10 rows selected.

– Let’s check the plan used by optimizer for id = 1
— Note that it still uses index access but cost has increased from 3 (in mbrc_nu) to 11 as 10 data blocks are accessed to get all the rows

SQL> set autotrace traceonly
select * from mbrc_hi_cf where id =1;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 3955578425

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 7040 | 11 (0)| 00:01:35 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_HI_CF | 10 | 7040 | 11 (0)| 00:01:35 |
|* 2 | INDEX RANGE SCAN | MBRC_HI_CF_IDX | 10 | | 1 (0)| 00:00:09 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
7654 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

* Let’s check cost of plan if FTS is used
* Note that cost of the plan is same (24) as earlier (mbrc_nu) and is more than
that of index access (11)

SQL>set autotrace traceonly
select /*+ full(s) */ * from mbrc_hi_cf s where id =1;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 1581728470

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 7040 | 24 (0)| 00:03:27 |
|* 1 | TABLE ACCESS FULL| MBRC_HI_CF | 10 | 7040 | 24 (0)| 00:03:27 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
7602 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

Just like in case of unique indexed table, here also execution plan will change from Index access to FTS when more than a threshold range of id’s are queried.

Here the  threshold is reached for a much smaller range of id’s i.e for id = 1 to 2 , index access is used but plan switches to FTS  for id = 1 to 3 and above as rows for same id are scattered across multiple blocks.

– Let’s verify..

* Note that Index access is used for range 1 – 2
* Note that cost of index access is 21 which is less than FTS (24) as seen earlier

SQL> set autotrace traceonly
select * from mbrc_hi_cf where id between 1 and 2;
set autotrace off
Execution Plan
----------------------------------------------------------
Plan hash value: 3955578425

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 14080 | 21 (0)| 00:03:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_HI_CF | 20 | 14080 | 21 (0)| 00:03:01 |
|* 2 | INDEX RANGE SCAN | MBRC_HI_CF_IDX | 20 | | 2 (0)| 00:00:18 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=1 AND "ID"<=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
65 consistent gets
0 physical reads
0 redo size
14142 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed


— Let’s verify that plan switches from index access to FTS for the range id = 1 – 3

SQL> set autotrace traceonly
select * from mbrc_hi_cf where id between 1 and 3;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 1581728470

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 21120 | 24 (0)| 00:03:27 |
|* 1 | TABLE ACCESS FULL| MBRC_HI_CF | 30 | 21120 | 24 (0)| 00:03:27 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"<=3 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
65 consistent gets
0 physical reads
0 redo size
19859 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> set autotrace off

– Let’s verify that from this range onwards Cost of index access is more than FTS as records of each id are scattered across a large no.. of blocks
— Note than cost of index access is 31 which is more than that of FTS (24)

SQL> set autotrace traceonly
select /*+ index (s) */ * from mbrc_hi_cf s where id between 1 and 3;
set autotrace off

Execution Plan
----------------------------------------------------------
Plan hash value: 3955578425

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 21120 | 31 (0)| 00:04:27 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_HI_CF | 30 | 21120 | 31 (0)| 00:04:27 |
|* 2 | INDEX RANGE SCAN | MBRC_HI_CF_IDX | 30 | | 2 (0)| 00:00:18 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=1 AND "ID"<=3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
21230 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

Hence it can be concluded that in case we have multiple rows with same id’s , FTS may be used for a smaller range of id’s if clustering factor is high.

Summary:

– For tables sized smaller than db_file-multiblock_read_count, index access may be preferred to FTS as no. of blocks accessed in FTS are more than those accessed in index access.

– As no. of table blocks accessed increase, cost of FTS remains same but cost of index access increases. The execution plan changes from index access to FTS after a threshold no. of table blocks accessed.

– For tables with non unique index, the clustering factor also plays a role in determining the threshold id range for plan switching. The switching takes place at a smaller range if clustering factor is high and vice versa.

I hope this post was useful.

Thanks for your time!

References:

https://forums.oracle.com/forums/thread.jspa?messageID=11051591#11051591
http://richardfoote.wordpress.com/2009/04/16/indexes-on-small-tables-part-i-one-of-the-few/
——————————————————————————————–

Related links:

Home
Tuning Index
Clustering Factor Demystified : Part-I
Clustering Factor Demystified : Part-II
Clustering Factor Demystified : Part-III
DB_FILE_MULTIBLOCK_READ_COUNT And Extent Size
Multiblock Reads and Cached Blocks

 

IDENTIFY THE DATABASE OWNING A CPU INTENSIVE PROCESS

On a linux/Unix server with multiple database instances running , if one of the databases has a process which is CPU intensive, we want to  identify the database which is the owner of  that process.

Currently, I have two databases – AMER and EURO running on the same server.
To simulate the environment, I needed to run a process in a database which consumes a lot of CPU. Since hash joins consume CPU, I wrote the following query and checked its explain plan

SQL> explain plan for
sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id;

select * from table(dbms_xplan.display);<

PLAN_TABLE_OUTPUT
————————————————————————————-
Plan hash value: 864676608

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 665 | 36575 | 409 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 665 | 36575 | 409 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| ORDERS | 105 | 3885 | 204 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ORDER_ITEMS | 665 | 11970 | 204 (0)| 00:00:01 |
———————————————————————

The explain clearly shows that hash join will be used. I executed this query repeatedly against EURO database using the following script :

SQL>conn / as sysdba

begin
for i in 1..100000000 loop
execute immediate 'sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id';
end loop;
end;
/

In another terminal, I issued “top” linux command to find out top CPU consuming processes. This is the output I got

 [root@node1 ~]# top

top – 21:54:39 up 2 days, 22:08, 6 users, load average: 4.75, 4.29, 4.16
Tasks: 244 total, 4 running, 240 sleeping, 0 stopped, 0 zombie
Cpu(s): 44.6%us, 44.9%sy, 0.0%ni, 0.0%id, 0.0%wa, 9.8%hi, 0.7%si, 0.0%st
Mem: 1667320k total, 1654112k used, 13208k free, 130188k buffers
Swap: 8193140k total, 643728k used, 7549412k free, 1133656k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14305 oracle 20 0 632m 66m 55m R 26.1 4.1 0:03.25 oracle
14665 root 20 0 22772 1548 1216 R 23.8 0.1 0:42.47 perl
21119 oracle 20 0 194m 3152 1500 D 15.2 0.2 164:15.69 mgr
142 root 20 0 0 0 0 R 13.5 0.0 132:57.93 kjournald
19272 oracle 20 0 166m 2292 1700 S 6.3 0.1 52:30.99 extract
3685 root 20 0 5924 544 488 D 5.6 0.0 52:14.51 syslogd

Here, It can be seen that the process with PID 14305 owned by oracle user is consuming most of the CPU.
But we don’t know which database does this process belong to. Let’s try to see the status of process with PID 14305

#[root@node1 ~]# ps -p 14305 -f

UID PID PPID C STIME TTY TIME CMD
oracle 14305 9918 2 21:44 ? 00:00:18 oracleeuro (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

It can be seen that process is running against euro database. Since local connection was made to the database, LOCAL= YES is present in the connection string.

— Now I will run the same script after making remote connection to euro database.

SQL>conn sys/oracle@euro as sysdba

begin
for i in 1..100000000 loop
execute immediate 'sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id';
end loop;
end;
/

In another terminal, I issued “top” linux command to find out top CPU consuming processes.

[root@node1 ~]# top

This is the output I got :
top – 22:35:47 up 2 days, 22:49, 6 users, load average: 4.24, 3.16, 3.34
Tasks: 236 total, 4 running, 232 sleeping, 0 stopped, 0 zombie
Cpu(s): 34.4%us, 46.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 17.5%hi, 2.1%si, 0.0%st
Mem: 1667320k total, 1650820k used, 16500k free, 132964k buffers
Swap: 8193140k total, 604000k used, 7589140k free, 1128896k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16789 oracle 20 0 626m 19m 17m R 32.6 1.2 0:39.00 oracle
21119 oracle 20 0 194m 3152 1500 R 23.4 0.2 172:30.19 mgr
142 root 20 0 0 0 0 R 21.3 0.0 140:19.24 kjournald
3685 root 20 0 5924 544 488 D 8.6 0.0 55:11.53 syslogd
19272 oracle 20 0 166m 2292 1700 S 8.3 0.1 55:32.88 extract
4704 root 20 0 109m 10m 3720 S 1.5 0.6 3:54.93 Xorg
7503 oracle 20 0 284m 11m 4432 S 1.2 0.7 1:09.44 gnome-terminal
4909 oracle 20 0 268m 2628 2328 S 0.6 0.2 15:52.64 ocssd.bin
4535 root 19 -1 112m 608 520 S 0.3 0.0 0:23.08 modclusterd

[root@node1 ~]# ps -p 16789 -f

UID PID PPID C STIME TTY TIME CMD
oracle 16789 1 33 22:33 ? 00:00:54 oracleeuro (LOCAL=NO)

Here again, It can be seen that process is running against euro database. Since remote connection was made, LOCAL=NO can be seen in the connection string

I hope this post was useful. Your comments and sugegstions are always welcome!

————————————————————————————————-

Home

Tuning Index

 

——————

CONSISTENT READS IN ORACLE : PART- II

In my earlier post consistent reads in oracle part-I I had demonstrated that no. of CR clones of a block are dictated by the undocumented parameter _db_block_max_cr_dba. I had also mentioned that this is a soft limit in the sense that CR clones more than _db_block_max_cr_dba may also be created.

Let us see a practical demonstration of the above concept.

OVERVIEW:

  •  Create a table whose one block has 10 rows.
  •   Update one row in each of 10 sessions
  •  Check that no. of CR clones created > _db_block_max_cr_dba

IMPLEMENTATION:

– Create simple table whose one block has 10 rows

 

HR1> drop table t1 purge;
    create table t1
    (c1 int, c2 char(700));

HR1> var j number;
     begin
       :j := 1;
       for i in 1..10 loop
         insert into t1 values(:j, ‘x’);
         :j := :j+1;
       end loop;
     commit;
     end;
     /

   col c1 for 99
   select c1 from t1;

C1

1
2
3
4
5
6
7
8
9
10

– Get the block address and browse X$BH view for that block.

 

HR1> select dbms_rowid.rowid_relative_fno(rowid) as file#,
           dbms_rowid.rowid_block_number(rowid) as block#
    from t1;

FILE#     BLOCK#
———- ———-
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551

– Check that value of the parameter _db_block_max_cr_dba = 8

 

SYS>SELECT 
  a.ksppinm  “Parameter”,
  decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
  c.ksppstvl “Instance”,
  decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
  decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
  decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
  a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;

Enter value for 1: _db_block_max_cr_dba
old  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%_db_block_max_cr_dba%’)

Parameter                                          Session                      Instance                                    S I D Description
————————————————– —————————- ——————————————————- – – – ——————————————————————————————
_db_block_max_cr_dba                                                            8                                           F F F Maximum Allowed Number of CR buffers per dba

– Check in x$bh that We have one CURRENT block(state=xcur).
Enter file#/block# found above when prompted

SYS>select b.dbarfil, b.dbablk, b.class,
  decode(state,0,’free’,1,’xcur’,2,’scur’,3,’cr’, 4,’read’,5,’mrec’,6,’irec’,7,’write’,8,’pi’, 

9,’memory’,10,’mwrite’,11,’donated’) as state,
  cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
  (select object_name from dba_objects where object_id = b.obj) as object_name
from sys.x$bh b
where dbarfil = &file_no and
      dbablk = &block_no;

Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 551
old   7:       dbablk = &block_no
new   7:       dbablk = 551

DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4        551          1 xcur                0          0          0          0          0 T1
4        551          1 free                0          0          0          0          0

Now, let’s see how consecutive DML statements generate CR blocks.

— update and monitor cr block

— Start 10 HR sessions
— Update one row in each of 10 sessions

HR1> update t1 set c2 = ‘y’ where c1 = 1;
     /
HR2> update t1 set c2 = ‘y’ where c1 = 2;
     /
HR3> update t1 set c2 = ‘y’ where c1 = 3;
     /
HR4> update t1 set c2 = ‘y’ where c1 = 4;
     /
HR5> update t1 set c2 = ‘y’ where c1 = 5;
     /
HR6> update t1 set c2 = ‘y’ where c1 = 6;
     /
HR7> update t1 set c2 = ‘y’ where c1 = 7;
     /
HR8> update t1 set c2 = ‘y’ where c1 = 8;
     /
HR9> update t1 set c2 = ‘y’ where c1 = 9;
     /
HR10> update t1 set c2 = ‘y’ where c1 = 10;
     /

– Check in x$bh that We have one CURRENT block(state=xcur) and > 8 (_db_block_max_cr_dba) cr clones (status=cr)

SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 551

DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4        551          1 cr           24257659          0          3        596       1025 T1
4        551          1 cr           24257658          0          3        181       1023 T1
4        551          1 cr           24257657          0          3        181       1023 T1
4        551          1 cr           24257651          0          3        181       1023 T1
4        551          1 cr           24257649          0          3     172517        954 T1
4        551          1 cr           24257648          0          3        181       1023 T1
4        551          1 cr           24257647          0          3        181       1023 T1
4        551          1 cr           24257645          0          3     176654       1063 T1
4        551          1 cr           24257639          0          3        181       1023 T1
4        551          1 cr           24257638          0          3        181       1023 T1
4        551          1 cr           24257635          0          3     279569       1005 T1

4        551          1 cr           24257634          0          3        181       1023 T1
4        551          1 cr           24257633          0          3        181       1023 T1
4        551          1 cr           24257622          0          3     175596        916 T1
4        551          1 cr           24257621          0          3        181       1023 T1
4        551          1 cr           24257620          0          3        181       1023 T1
4        551          1 xcur                0          0          0          0          0 T1

17 rows selected.

– CLEANUP –
HR1> rollback;
HR2>rollback;
HR3> rollback;
HR4> rollback;
HR5> rollback;
HR6> rollback;
HR7> rollback;
HR8> rollback;
HR9> rollback;
HR10> rollback;
     drop table t1 purge;

SYS>alter system set “_db_block_max_cr_dba”= 6 scope=spfile;
    startup force;

——————————————————————————-

Related links:

 

 

 

—————–