How to Reduce DB File Sequential Read Wait

Yet another article to share.. 

DB File Sequential Read wait event occurs when we are trying to access data using index and oracle is waiting for the read of index block from disk to buffer cache to complete.  A sequential read is a single-block read.Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.Db file sequential read wait events may also appear when undo blocks are read from disk in order to provide a consistent get(rarely).

To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait .  A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2 indicates a file header read) ,where p1,p2 and p3 gives the the absolute file number ,the block being read ,and  the number of blocks (i.e, P3 should be 1) respectively.

Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance.Hence to reduce this wait event follow the below points .

1.) Tune Oracle - tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.
2.) Tune Physical Devices - Distribute(stripe) the data on diferent disk to reduce the i/o . Logical distribution is useless. "Physical" I/O performance is only governed by "independency of devices".
3.) Faster Disk - Buy the faster disk to reduce the unnecessary I/O request .
4.) Increase db_block_buffers - A larger buffer cache can (not will, "might") help .


Reference :: From  Asktom site

Correlated update vs merge

some notes from my friend  : heribertus Bramundito

I created a small comparison between below 3 update methods (so far that I know) with Full Table Scan and also Index Scan:

-          Correlated Update

-          Merge

-          Update from Select

Some highlighted items:

1.       “Correlated Update” is not good at all if we don’t have index access on the inner table. It will produces huge I/O since we have to FTS the inner table over and over again (as much as the number of rows in the outer table)

2.       If the join condition is not matched, “Correlated Update” will update the row with empty string. We need to pay attention on this (please check “Data Changes” section below)

It will produce more Undo for those additional update

3.       If the join condition is not matched, both “Merge” and “Update from Select” method will leave the row as is. To me it is more reasonable

4.       With “Update from Select” method, we need to create unique index on the inner table, otherwise we will ended up with ORA- error

5.       “Update from Select” method is little bit superior compare to the other 2 methods when we update small percentage of outer table and there is index on the outer table, so that we can get benefit from Index Range Scan

Sample Data

SQL> create table ttarget

  2  pctfree 99

  3  as

  4  select rownum id, lpad(chr(rownum), 70) data

  5  from dual

  6  connect by rownum <= 1000;

Table created.

SQL> create table tsource

  2  pctfree 99

  3  as

  4  select rownum id, lpad(chr(rownum)||'_CHG', 70) data

  5  from dual

  6  connect by rownum <= 500;

Table created.

SQL> exec dbms_stats.gather_table_stats(USER, 'TTARGET');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(USER, 'TSOURCE');

PL/SQL procedure successfully completed.

SQL> select table_name, blocks, empty_blocks, num_rows

  2  from user_tables

  3  where table_name in ('TTARGET', 'TSOURCE');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS

------------------------------ ---------- ------------ ----------

TSOURCE                               518            0        500

TTARGET                              1024            0       1000

SQL> create unique index idx_tsource on tsource(id);

Index created.

SQL> select leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, uniqueness from user_indexes where index_name = 'IDX_TSOURCE';

LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR UNIQUENES

----------- ------------- ----------------------- ----------------------- ----------------- ---------

          1           500                       1                       1               500 UNIQUE

SQL> create index idx_ttarget on ttarget (id);

Index created.

Data Changes

Correlated Update

For un-matched rows, we can see DATA is CHANGED

SQL> select id, trim(data) from ttarget where id > 500 and rownum < 3;

        ID TRIM(DATA)

---------- --------------------

       501 )

       502 ÷

SQL> select count(*) from ttarget where data like '%CHG%';

  COUNT(*)

----------

         0

SQL> update ttarget a set data = (select data from tsource b where a.id=b.id);

1000 rows updated.

SQL> select count(*) from ttarget where data like '%CHG%';

  COUNT(*)

----------

       500

SQL> select id, trim(data) from ttarget where id > 500 and rownum < 3;

        ID TRIM(DATA)

---------- --------------------

       501

       502

Merge

For un-matched rows, we can see data is not changed

SQL> select id, trim(data) from ttarget where id > 500 and rownum < 3;

        ID TRIM(DATA)

---------- --------------------

       501 )

       502 ÷

SQL> select count(*) from ttarget where data like '%CHG%';

  COUNT(*)

----------

         0

SQL> merge into ttarget a

  2  using tsource b

  3  on (a.id=b.id)

  4  when matched then

  5  update set a.data=b.data;

500 rows merged.

SQL> select count(*) from ttarget where data like '%CHG%';

  COUNT(*)

----------

       500

SQL> select id, trim(data) from ttarget where id > 500 and rownum < 3;

        ID TRIM(DATA)

---------- --------------------

       501 )

       502 ÷

Update from Select

For un-matched rows, we can see data is not changed

SQL> select id, trim(data) from ttarget where id > 500 and rownum < 3;

        ID TRIM(DATA)

---------- --------------------

       501 )

       502 ÷

SQL> select count(*) from ttarget where data like '%CHG%';

  COUNT(*)

----------

         0

SQL> update

  2  (select a.data dtarget, b.data dsource

  3   from ttarget a, tsource b

  4   where a.id=b.id) c

  5  set c.dtarget=c.dsource;

500 rows merged.

SQL> select count(*) from ttarget where data like '%CHG%';

  COUNT(*)

----------

       500

SQL> select id, trim(data) from ttarget where id > 500 and rownum < 3;

        ID TRIM(DATA)

---------- --------------------

       501 )

       502 ÷

Export Import with sysdba user

Fellows just little share, really use full if you guys dealing with export import

How to use "/ as sysdba" during exp/imp
On Windows platform:
userid="'"/ as sysdba"'"
or
userid="""/ as sysdba"""


On Unix Platform:
userid=\'/ as sysdba\'

These tricks can be used with Oracle datapump (expdp/impdp) as well.

How to configure Grid HAS manually

#### How to configure Grid HAS manually ####

Hi Fellows, just share and make a note, enjoyyy...

-- Deconfigure First --
$GRID_HOME/crs/install
[root@DB_MACHINE install]# perl rootcrs.pl -deconfig -force

-- Reconfigure --
$GRID_HOME/perl/bin/perl -I $GRID_HOME/perl/lib -I $GRID_HOME/crs/install $GRID_HOME/crs/install/roothas.pl


-- Bringup Daemon cssd to enable asm to started --
+ASM DB_MACHINE /oravl01/oracle/dba > crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.cssd       ora.cssd.type  OFFLINE   OFFLINE
ora.diskmon    ora....on.type OFFLINE   OFFLINE
ora.evmd       ora.evm.type   ONLINE    ONLINE    crmb...app1
ora.ons        ora.ons.type   OFFLINE   OFFLINE

+ASM DB_MACHINE /oravl01/oracle/dba > crsctl start resource ora.cssd
CRS-2672: Attempting to start 'ora.cssd' on 'DB_MACHINE'
CRS-2672: Attempting to start 'ora.diskmon' on 'DB_MACHINE'
CRS-2676: Start of 'ora.diskmon' on 'DB_MACHINE' succeeded
CRS-2676: Start of 'ora.cssd' on 'DB_MACHINE' succeeded


-- Add asm instance to crs and Start ASM instance --
+ASM DB_MACHINE $GRID_HOME/dbs > srvctl add asm -p $GRID_HOME/dbs/init+ASM.ora
+ASM DB_MACHINE $GRID_HOME/dbs > crsctl start resource ora.asm
CRS-2672: Attempting to start 'ora.asm' on 'DB_MACHINE'
CRS-2676: Start of 'ora.asm' on 'DB_MACHINE' succeeded

-- Altering diskgroup --
+ASM DB_MACHINE $GRID_HOME/dbs > sqlplus / as sysasm
SQL > alter diskgroup DGDATA mount;

-- Bring up database --
TESTDB DB_MACHINE $ORACLE_HOME > sqlplus / as sysdba
SQL > startup;