DB block edit utliity ( BBED) - 11G

BBED library files are available in 11g , so we could not directly link the library files to edit/view the database blocks in 11g

There is a work around to get the BBED ulitility in 11g.

Step 1: Copy the necessary files

We could copy the following library files from any of the 10g version ( please ensure the source 10g and target 11g binary are in the same os flavour/architecture)
$10G_HOME/rdbms/lib/ssbbded.o to $11G_HOME/rdbms/lib/ssbbded.o
$10G_HOME/rdbms/lib/sbbdpt.o to $11G_HOME/rdbms/lib/sbbdpt.o
$10G_HOME/rdbms/mesg/bbedus.msb to $11G_HOME/rdbms/mesg/bbedus.msb
$10G_HOME/rdbms/mesg/bbedus.msg to $11G_HOME/rdbms/mesg/bbedus.msgStep2 : set the 11g environmental values and ensure everying is in-tact by env command

Step3: link the library using make command

make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

Step4: invoke bbed and explore the blocks
( please dont edit blocks without oracle's advice , infact oracle would not advice us to edit .....)Note: Password for the bbedit is blockedit
oracle@xxxxxx$ $ORACLE_HOME/bin/bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun Nov 4 11:36:46 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> help
HELP [ <bbed command> | ALL ]
BBED> help all
SET DBA [ dba | file#, block# ]
SET FILENAME 'filename'
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] 'filename'
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ <SET parameter> | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
</Nuf>:
N - a number which specifies a repeat count.
u - a letter which specifies a unit size:
b - b1, ub1 (byte)
h - b2, ub2 (half-word)
w - b4, ub4(word)
r - Oracle table/index row
f - a letter which specifies a display format:
x - hexadecimal
d - decimal
u - unsigned decimal
o - octal
c - character (native)
n - Oracle number
t - Oracle date
i - Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
[ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ <bbed command> | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]


Now let us how to usiltise this tool for some cases

I How to amend a value through this tool

1. Find out the relative file no and block no

select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno,  dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where rowid='AAACfBAAGAAACTyAAY'
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno,  dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where rowid='AAACfBAAGAAACTyAAY'
  2  ;
JEYA1                AAACfBAAGAAACTyAAY          6       9458

2. Find  the file_name for the relative file no ( here it is 6)

 SQL> select file_name,bytes from dba_data_files where file_id=6;
 /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf 2097152000

3. capture the detail in a file in the following format strictly otherwise you mey get "BBED-00310: no datafile specified" when do set list

 ( please use running sequence if you want to mention more than 1 datafiles in this list,
 the no 1 is not the file_id for the datafile, it is just a running sequence in this filesystem, otherwise you may get
"BBED-00310: no datafile specified" erorr when you do set dba)
  
SQL> !cat /tmp/bbed.log
1 /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf 2097152000

4. set the listfile
BBED> set list '/tmp/bbed.log'
        LISTFILE        /tmp/bbed.log

5. verify if this is set properly

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
    
1 /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf 2097152000


6.now, mark the block by setting file running sequence number,rowid_blockNumber ( taken from step 1)

   BBED> set dba 1,9458
        DBA             0x004024f2 (4203762 1,9458)

7. seek the offset for the search string using find ( here it is 6366)

BBED> find /c JEYA
 File: /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf (1)
 Block: 9458             Offsets: 6366 to 6429           Dba:0x004024f2
------------------------------------------------------------------------
 4a455941 3107786d 05040101 01065359 5354454d 02c20b03 c2053e03 c2062803
 c12f0c03 4d4f4e04 52616a61 1973696e 70626366 696e6562 64627031 2e73672e
 <32 bytes per line>

8.dump the block (/v is for verbose mode)

  BBED> dump /v dba 1,9458 offset 6366 count 64
 File: /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf (1)
 Block: 9458    Offsets: 6366 to 6429  Dba:0x004024f2
-------------------------------------------------------
 4a455941 3107786d 05040101 01065359 l JEYA1.xm......SY
 5354454d 02c20b03 c2053e03 c2062803 l STEM.Â..Â.>.Â.(.
 c12f0c03 4d4f4e04 52616a61 1973696e l Á/..MON.Raja.sin
 70626366 696e6562 64627031 2e73672e l abctest.sg.

9.modify the value /C for character

BBED> modify /c HARIS dba 1,9458 offset 6366
BBED-00215: editing not allowed in BROWSE mode
vow BBEDIT is in browse mode by default. now , let me change into edit mode.
BBED> SET MODE  EDIT
        MODE            Edit

BBED> modify /c HARIS dba 1,9458 offset 6366
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
 File: /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf (1)
 Block: 9458             Offsets: 6366 to 6429           Dba:0x004024f2
------------------------------------------------------------------------
 48415249 5307786d 05040101 01065359 5354454d 02c20b03 c2053e03 c2062803
 c12f0c03 4d4f4e04 52616a61 1973696e 70626366 696e6562 64627031 2e73672e
 <32 bytes per line>

10.Dump the file to see the changes

BBED> dump /v dba 1,9458 offset 6366 count 64
 File: /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf (1)
 Block: 9458    Offsets: 6366 to 6429  Dba:0x004024f2
-------------------------------------------------------
 48415249 5307786d 05040101 01065359 l HARIS.xm......SY
 5354454d 02c20b03 c2053e03 c2062803 l STEM.Â..Â.>.Â.(.
 c12f0c03 4d4f4e04 52616a61 1973696e l Á/..MON.Raja.sin
 70626366 696e6562 64627031 2e73672e l abctest.sg.

11. Check the block checksum and apply

BBED> sum dba 1,9458
Check value for File 1, Block 9458:
current = 0x4dca, required = 0x26c6
BBED> sum dba 1,9458 apply
Check value for File 1, Block 9458:
current = 0x26c6, required = 0x26c6

12. verify the data from another sqlplus window Please flush the buffer_cache to see the latest changes

SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno,  dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where rowid='AAACfBAAGAAACTyAAY';
JEYA1                AAACfBAAGAAACTyAAY          6       9458
SQL> alter system flush buffer_cache;
System altered.
SQL>  select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno,  dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where rowid='AAACfBAAGAAACTyAAY';
HARIS                AAACfBAAGAAACTyAAY          6       9458

II . How to restore a deleted record by updating directly the block using BBED tool.

(A simple way of recovering the deleted record is the flashback query option). This method is just
explore the efficiency of BBED tool.
1. Simulate the deletion

SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno,  dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where ROWID='AAACfBAAMAAAj8nABV';
DB_NAME              ROWID                     FNO        BNO
-------------------- ------------------ ---------- ----------
JEYA                 AAACfBAAMAAAj8nABV         12     147239
SQL> delete from space_mon where ROWID='AAACfBAAMAAAj8nABV';
1 row deleted.
SQL> commit;
Commit complete.

SQL> alter system checkpoint;
System altered.
SQL>  alter system flush BUFFER_CACHE
  2  ;
System altered.

2. Find out the segment header details

SQL> select HEADER_FILE,HEADER_BLOCK,BLOCKS from dba_segments where SEGMENT_NAME = 'SPACE_MON';
HEADER_FILE HEADER_BLOCK     BLOCKS
----------- ------------ ----------
         12       147212      39936

3. dump the whole segment to find out the deleted rows but still stay in the block
alter system dump datafile 12 block min 147212 block max 187148;

4.open the trace file and check for the CC:3 which is for deleted records
 tab 0, row 0, @0x1f79  ---> hexa block number ( convert into decimal by select to_number('1f79','xxxx') from dual )
 4 tl: 31 fb: --H-FL-- lb: 0x0  cc: 3

5. create a listfile with the necessary datafiles

BBED> !cat /tmp/bbed.log
1 /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf 2097152000
2 /data/oracle/MYTEST/data01/MYTEST_perf_mon01.dbf 2097152000

6. set the listfile
BBED> set list '/tmp/bbed.log'
        LISTFILE        /tmp/bbed.log

7. mark the DBA which was found at step 4

BBED> set dba 2,147239
        DBA             0x00823f27 (8535847 2,147239)
BBED> find /c JEYA
 File: /data/oracle/MYTEST/data01/MYTEST_perf_mon01.dbf (2)
 Block: 147239           Offsets: 1087 to 1150           Dba:0x00823f27
------------------------------------------------------------------------
 4a455941 07786b0a 10010101 10444254 4d5f4c41 5247455f 494e4445 5802c229
 03c21e21 03c20b45 03c14a1f 05204556 45200620 52616a61 20092064 62747062
 <32 bytes per line>

8. dump the block with little head of the offset ( 1087 is the exact offset) to find out the row header.
   '<' sympbol shows that the row header marker which is 1084
BBED> dump /v dba 2,147239 Offset 1080
 File: /data/oracle/MYTEST/data01/MYTEST_perf_mon01.dbf (2)
 Block: 147239  Offsets: 1080 to 1143  Dba:0x00823f27
-------------------------------------------------------
 03c2033c 010a044a 45594107 786b0a10 l .Â.<...JEYA.xk..
 01010110 4442544d 5f4c4152 47455f49 l ....DBTM_LARGE_I
 4e444558 02c22903 c21e2103 c20b4503 l NDEX.Â).Â.!.Â.E.
 c14a1f05 20455645 20062052 616a6120 l ÁJ.. EVE . Raja

9. Confirm if it is row header marker

BBED> p kdbr
sb2 kdbr[0]                                 @118      5652
sb2 kdbr[1]                                 @120      5735
sb2 kdbr[2]                                 @122      5814
sb2 kdbr[3]                                 @124      5892
sb2 kdbr[4]                                 @126      5966
sb2 kdbr[5]                                 @128      6043
sb2 kdbr[6]                                 @130      6121
sb2 kdbr[7]                                 @132      6198
sb2 kdbr[8]                                 @134      6286
sb2 kdbr[9]                                 @136      6374
sb2 kdbr[10]                                @138      6460
sb2 kdbr[11]                                @140      6546
sb2 kdbr[12]                                @142      6635
sb2 kdbr[13]                                @144      6723
sb2 kdbr[14]                                @146      6800
sb2 kdbr[15]                                @148      6872
sb2 kdbr[16]                                @150      6946
sb2 kdbr[17]                                @152      7023
sb2 kdbr[18]                                @154      7102
sb2 kdbr[19]                                @156      7179
sb2 kdbr[20]                                @158      7258
sb2 kdbr[21]                                @160      7333
sb2 kdbr[22]                                @162      7405
sb2 kdbr[23]                                @164      7480
sb2 kdbr[24]                                @166      7557
sb2 kdbr[25]                                @168      7630
sb2 kdbr[26]                                @170      7709
sb2 kdbr[27]                                @172      7786
sb2 kdbr[28]                                @174      7864
sb2 kdbr[29]                                @176      7941
sb2 kdbr[30]                                @178      8012
sb2 kdbr[31]                                @180      1054
sb2 kdbr[32]                                @182      1130
sb2 kdbr[33]                                @184      1211
sb2 kdbr[34]                                @186      1290
sb2 kdbr[35]                                @188      1364
sb2 kdbr[36]                                @190      1440
sb2 kdbr[37]                                @192      1523
sb2 kdbr[38]                                @194      1606
sb2 kdbr[39]                                @196      1689
sb2 kdbr[40]                                @198      1771
sb2 kdbr[41]                                @200      1847
sb2 kdbr[42]                                @202      1914
sb2 kdbr[43]                                @204      1983
sb2 kdbr[44]                                @206      2049
sb2 kdbr[45]                                @208      2120
sb2 kdbr[46]                                @210      2191
sb2 kdbr[47]                                @212      2260
sb2 kdbr[48]                                @214      2331
sb2 kdbr[49]                                @216      2402
sb2 kdbr[50]                                @218      2473
sb2 kdbr[51]                                @220      2544
sb2 kdbr[52]                                @222      2615
sb2 kdbr[53]                                @224      2686
sb2 kdbr[54]                                @226      2757
sb2 kdbr[55]                                @228      2828
sb2 kdbr[56]                                @230      2900
sb2 kdbr[57]                                @232      2972
sb2 kdbr[58]                                @234      3044
sb2 kdbr[59]                                @236      3115
sb2 kdbr[60]                                @238      3187
sb2 kdbr[61]                                @240      3259
sb2 kdbr[62]                                @242      3331
sb2 kdbr[63]                                @244      3401
sb2 kdbr[64]                                @246      3465
sb2 kdbr[65]                                @248      3532
sb2 kdbr[66]                                @250      3596
sb2 kdbr[67]                                @252      3665
sb2 kdbr[68]                                @254      3732
sb2 kdbr[69]                                @256      3800
sb2 kdbr[70]                                @258      3865
sb2 kdbr[71]                                @260      3931
sb2 kdbr[72]                                @262      4000
sb2 kdbr[73]                                @264      4071
sb2 kdbr[74]                                @266      4146
sb2 kdbr[75]                                @268      4215
sb2 kdbr[76]                                @270      4290
sb2 kdbr[77]                                @272      4357
sb2 kdbr[78]                                @274      4419
sb2 kdbr[79]                                @276      4480
sb2 kdbr[80]                                @278      4546
sb2 kdbr[81]                                @280      4609
sb2 kdbr[82]                                @282      4684
sb2 kdbr[83]                                @284      4758
sb2 kdbr[84]                                @286      4831
sb2 kdbr[85]                                @288      983   ====> close to 1084)
sb2 kdbr[86]                                @290      4978
sb2 kdbr[87]                                @292      5052
sb2 kdbr[88]                                @294      5124
sb2 kdbr[89]                                @296      5195
sb2 kdbr[90]                                @298      5259
sb2 kdbr[91]                                @300      5321
sb2 kdbr[92]                                @302      5382
sb2 kdbr[93]                                @304      5447
sb2 kdbr[94]                                @306      5514
sb2 kdbr[95]                                @308      5577

BBED> p *kdbr[85]
BBED> rowdata[0]
----------
ub1 rowdata[0]                              @1083     0x3c  =====> confirm it is deleted record

10. change the value to 2c

BBED> 
BBED> modify /x 2c offset 1083
 File: /data/oracle/MYTEST/data01/MYTEST_perf_mon01.dbf (2)
 Block: 147239           Offsets: 1083 to 1146           Dba:0x00823f27
------------------------------------------------------------------------
 2c010a04 4a455941 07786b0a 10010101 10444254 4d5f4c41 5247455f 494e4445
 5802c229 03c21e21 03c20b45 03c14a1f 05204556 45200620 52616a61 20092064
 <32 bytes per line>

11. verify the changes
BBED> p *kdbr[85]
rowdata[0]
----------
ub1 rowdata[0]                              @1083     0x2c
12. check sum and apply the changes
BBED> sum dba 2,147239
Check value for File 2, Block 147239:
current = 0x65e3, required = 0x65f3
BBED> sum dba 2,147239 apply
Check value for File 2, Block 147239:
current = 0x65f3, required = 0x65f3

13. verify from the sqlplus

SQL> alter system checkpoint;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno,  dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where ROWID='AAACfBAAMAAAj8nABV';
no rows selected
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno,  dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where ROWID='AAACfBAAMAAAj8nABV';
DB_NAME              ROWID                     FNO        BNO
-------------------- ------------------ ---------- ----------
JEYA                 AAACfBAAMAAAj8nABV         12     147239


II. How to restore datafile which is restored from a old file where SCN is out of sync.

kscnbas – last change SCN
kcvcptim – time of the last change
kcvfhcpc – checkpoint count
kcvfhccc – a checkpoint checker value, which is one less than kcvfhcpc
BBED>p kcvfh --list out the structure of a datafile



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113:   file 6 needs media recovery
ORA-01110:  data file 6:
'/data/oracle/MYTEST/data/MYTEST_data02.dbf'
column checkpoint_change# format 99999999999999999
SQL> select file#, checkpoint_change#, to_char(checkpoint_time, 'dd/mm/yyyy hh24:mi:ss') from v$datafile;
     FILE# CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_
---------- ------------------ -------------------
         6               6977 25/10/2012 11:41:42     =====> target datafile.

        
SQL> select file#, change#, to_char(time, 'dd/mm/yyyy hh24:mi:ss') from v$recover_file;
     FILE#    CHANGE# TO_CHAR(TIME,'DD/MM
---------- ---------- -------------------
         6       6602 25/10/2012 11:42:42

===== Open the file 6 and 1 ( whichever datafile has latest SCN)
Target DATAFILE 6 - need to be recovered. ( 6 should be running sequnence in the listfile)

BBED> p dba 6,1 kcvfhckp
struct kcvfhckp, 36 bytes                   @142    
   struct kcvcpscn, 8 bytes                 @142    
      ub4 kscnbas                           @142      0x000019ca => SCN
      ub2 kscnwrp                           @145      0x0000
   ub4 kcvcptim                             @149      0x240bcfef => timestamp
BBED> p dba 6,1 kcvfhcpc
ub4 kcvfhcpc                                @178      0x00000004 => Checkpoint count
BBED> p dba 6,1 kcvfhccc
ub4 kcvfhccc                                @186      0x00000003 => Checkpoint counter which is less than checkpoint count

Source datafile 1

BBED> p dba 1,1 kcvfhckp
struct kcvfhckp, 36 bytes                   @142    
   struct kcvcpscn, 8 bytes                 @142    
      ub4 kscnbas                           @142      0x00001b51
      ub2 kscnwrp                           @145      0x0000
   ub4 kcvcptim                             @149      0x240bd56e
BBED> p dba 1,1 kcvfhcpc
ub4 kcvfhcpc                                @178      0x00000012
BBED> p dba 1,1 kcvfhccc
ub4 kcvfhccc                                @186      0x00000008
#select checkpoint_change# from v$database
modify /x 0x00001b51 dba 6,1 offset 142
modify /x 0x240bd56e dba 6,1 offset 149
modify /x 0x00000012 dba 6,1 offset 178
modify /x 0x00000008 dba 6,1 offset 186

BBED>sum dba 1,1
------
BBED> sum dba 1,1 apply
Check value for File 6, Block 1:
current = 0xc097, required = 0xc097
================================

Controlfile create script

Control file creation script  can be extracted in many ways

1. alter database backup controlfile to trace;

2. dbms_resotre.restoreControlfile

3. using views

select ' CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG ' from dual
/
Select ' MAXDATAFILES ' || records_total from v$controlfile_record_section where type = 'DATAFILE'
/
Select ' MAXLOGHISTORY ' || records_total  from v$controlfile_record_section where type = 'LOG HISTORY'
/
Select ' MAXINSTANCES ' || records_total  from v$controlfile_record_section where type = 'DATABASE'
/
Select ' MAXLOGFILES ' || records_total  from v$controlfile_record_section where type = 'REDO LOG'
/
Select ' MAXLOGMEMBERS ' || dimlm from x$kccdi/
select ' LOGFILE ' from dual
/
select ' GROUP ' || group# || ' ''' ||  member ||'''' from v$logfile
/
select ' datafile ' from dual
/
select ' ''' || file_name ||'''' from dba_data_files
/
select ' alter tablespace ' || tablespace_name || ' add tempfile ' || file_name || ' ; ' from dba_temp_files;
/

How to choose a correct index on table and the order of columns for the index?

Creating correct index or choosing the order of columns for an index is a big challenge.

In Oracle, when a query is slow, first thing we check is if the query is using correct index or if any full table scan(FTS).
Please note that Full table scan is not a bad thing.Sometime ,Full table scan(FTS) perform better than index can when the no blocks it has to scan
through is lesser.fo ex ,when the no of data blocks( all blocks in a table) read for full table scan is lesser than the no of blocks read fron index + no of data blocks access
(Ref:How to find out the logical IO?)

A developer should know the below before writing a query

     " Logical IO is the scientific unit to measure oracle query performance. "
      " How many blocks the query supposed to read to satisfy the given criteria based on the current data or trend"
  
      " Creating index will create additional overhead to insert and more overhead to delete and update statements"
      " For the frequently used queries, based on the where critieria
         if the No of blocks read by the index access + data blocks >=  total no of blocks, then index creation may  give adverse effect"

Choosing columns for index creation

1.Consider indexing keys that are used frequently in WHERE clauses.
2. Consider indexing keys that are used frequently to join tables in SQL statements.
3.Choose index keys that have high selectivity. ( Ref: How to determine the Selectivity ? )
4. In a composite index, choose the order of column with more selectivity as leading columns.....
5.Do not use standard B-tree indexes on keys or expressions with few distinct values.

Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear
less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently,
  as in a high concurrency OLTP application.

6.Do not index columns that are modified frequently.

UPDATE statements that modify indexed columns and
INSERT and DELETE statements that modify indexed tables take longer than if there were no index.
Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.

7.Do not index keys that appear only in WHERE clauses with functions or operators.

  A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path
  that uses the index except with function-based indexes.

8.Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT,
UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.
   ( This will avoid most of the deadlocks)

9. When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs,
  UPDATEs, and DELETEs and the use of the space required to store the index. You might want to experiment by
  comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

In 11g, you can mak ethe index invisible to test sql without indexes
    alter index owner.indexname invisible;
     - test query
    alter index owner.indexname visible;
 
10

  * Primary Keys - these are given an index automatically (unless you specify a suitable existing index for Oracle to use)
  * Unique Keys - these are given an index automatically (ditto)
  * Foreign Keys - these are not automatically indexed, but you should add one to avoid performance issues when the constraints are checked

11.Make use of Index Tuning using the SQLAccess Advisor to get some advices for additional indexes
     Seek OEM administrator to get this acccess in dev/uat if you want to run this advory though gui interface.

How to find out logical IOs?
  There are various methods to find out the logical IOs like tracing,awr report/autotrace.Let us see one of the easiest method using auto trace

case 1 How many logical ios for a full table scan
SQL> set linesize 200
SQL> set autotrace traceonly
SQL> select object_name from PERFORMANCE;
15972 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1331860161
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 15972 |   296K|    63   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| PERFORMANCE | 15972 |   296K|    63   (2)| 00:00:01 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1263  consistent gets
          0  physical reads
          0  redo size
     507677  bytes sent via SQL*Net to client
      12227  bytes received via SQL*Net from client
       1066  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      15972  rows processed

Case 2: Find out how many logical ios for an index scan

SQL> select object_name from PERFORMANCE where object_name='HELLO';
15972 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2578635553
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 15972 | 95832 |    66   (0)| 00:00:01 |
|   1 |  INDEX RANGE SCAN| PERF_IDX | 15972 | 95832 |    66   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1132  consistent gets
          0  physical reads
          0  redo size
     275151  bytes sent via SQL*Net to client
      12227  bytes received via SQL*Net from client
       1066  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      15972  rows processed

the consistent reads ( logical IO ) for index access is 1132 where as for FTS it is 1263.( This is just an example)
How to determine the Selectivity ?

The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value.
Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.

Index Selectivity :

B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table.

The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index.
The ideal selectivity is 1. Such a selectivity can be reached only by unique indexes on NOT NULL columns.

Example with good Selectivity

A table having 100'000 records and one of its indexed column has 88000 distinct values, then the selectivity of this index is 88'000 / 10'0000 = 0.88.
Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints.
These indexes are the most selective and the most effective in optimizing performance. The selectivity of an index is the percentage of rows
in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value.

Example with bad Selectivity
lf an index on a table of 100'000 records had only 500 distinct values, then the index's selectivity is 500 / 100'000 = 0.005 and
in this case a query which uses the limitation of such an index will retum 100'000 / 500 = 200 records for each distinct value.
It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.

How to Measure Index Selectivity ?

Manually measure index selectivity
The ratio of the number of distinct values to the total number of rows is the selectivity of the columns.
This method is useful to estimate the selectivity of an index before creating it.
select count (distinct job) "Distinct Values" from emp;
Distinct Values
---------------
              5
select count(*) "Total Number Rows" from emp;
Total Number Rows
-----------------
               14
Selectivity = Distinct Values / Total Number Rows
            = 5 / 14
            = 0.35
Automatically measure index selectivity
We can determine the selectivity of an index by dividing the number of distinct indexed values by the number of rows in the table.
create index idx_emp_job on emp(job);
analyze table emp compute statistics;
select distinct_keys from user_indexes
where table_name = 'EMP'
   and index_name = 'IDX_EMP_JOB';
DISTINCT_KEYS
-------------
            5
select num_rows from user_tables
where table_name = 'EMP';
NUM_ROWS
---------
       14
Selectivity = DISTINCT_KEYS / NUM_ROWS = 0.35
Selectivity of each individual Column

Assuming that the table has been analyzed it is also possible to query USER_TAB_COLUMNS to investigate the selectivity of each column individually.
select column_name, num_distinct
from user_tab_columns
where table_name = 'EMP';
COLUMN_NAME                     NUM_DISTINCT
------------------------------ ------------
EMPNO                                     14
ENAME                                     14
JOB                                        5
MGR                                        2
HIREDATE                                  13
SAL                                       12
COMM                                       4
DEPTNO                                     3

How to choose Composite Indexes ?
A composite index contains more than one key column. Composite indexes can provide additional advantages over single column indexes.
Better Selectivity  Sometimes two or more columns, each with poor selectivity, can be combined to form a composite index with good selectivity.
Oracle can return these values from the index without accessing the table. However in this case, it's better to use an IOT (Index Only Table).

An SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.
A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index.

References
http://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm
http://www.akadia.com/services/ora_index_selectivity.html

Optimal Ordering of table columns in Oracle - Best Practises

Most of the time Developers/DBAs create a table with set of columns on the fly without going into any concept/application requirement/best practises....Recently,I came across a post in Oracle Magazine ( July 2012) by Thomas Kyte about the ordering of column when we create a table in oracle .
( Tom Kyte is a database evangelist in Oracle’s Server Technologies division and has worked for Oracle since 1993. He is the author of Expert Oracle Database Architecture (Apress, 2005, 2010) and Effective Oracle by Design (Oracle Press, 2003), among other books ) abothe

I feel it is worth sharing his post in the mydb forum,it may help us ( architects/developers/DBAs) to get some idea about the ordering of column for a table .

http://www.oracle.com/technetwork/issue-archive/2012/12-may/o32asktom-1578049.html

Optimal Ordering

Is there an optimal order for creating columns in a table? The DBA at my shop enforces a standard of putting VARCHAR2 columns last, and the likelihood of nulls is not considered. Is there a real-world benefit to this order?

Here’s a little background first. A row is stored in a manner similar to this in the database block:

[null flag][length][data][null flag][length][data]. . . .

In order to get to the third column in a table, the database must parse through the first two columns (not pointers—the database must read the row byte by byte)— even if the columns are numbers, dates, whatever. The columns will be stored as a length field followed by the data, and the database must parse through them to get to the next column.

So, in general you should put the most frequently accessed columns first for the best performance.

However, there is something to be said for putting the column(s) most likely to be null last, because they will consume zero bytes. If the database hits the end of a row before finding the Nth column, you know that that column is NULL or the default column value (for not-null columns added to the table with fast add in Oracle Database 11g).

However, there is a convention to put primary key columns first in all cases.

However . . . there are too many howevers! And in general, it really isn’t going to matter too much. The bit about putting the most frequently accessed columns first would have the most weight in a data warehouse in which you are scanning over many millions of rows to build the answer (the repeated overhead of parsing over a lot of columns that are not interesting would add up). In an online transaction processing (OLTP) system, where you are processing tens of rows, though, it wouldn’t be as big a deal.

In a strange case involving dozens of columns that are almost always null, putting them last in a table with lots and lots of rows could save you quite a few bytes, but the table would have to be large and the columns would have to be all null and frequently all null.

So, my recommendation is to put the columns in the order that makes the most sense from an aesthetic and design perspective.

As for the DBA’s rule above (VARCHAR2 columns last and the likelihood of nulls not considered), it would do nothing for performance or manageability. In many cases, it could negatively affect performance. Just remember that pretty much all database data is stored in a stringlike fashion:

·         A string will have a null flag and a length byte or length bytes (<=250 characters [1 byte], >250 characters [2 bytes]) followed by the data.

·         A number will have a null flag and a length byte or length bytes (numbers are 0–22 bytes in length) followed by a varying amount of data.

·         A binary_float will consume 5 bytes—a leading null flag and a length byte followed by 4 bytes of floating-point data.

·         A binary_double will consume 9 bytes—a leading null flag and a length byte followed by 8 bytes of floating-point data.

The database must read the length byte to figure out how many bytes of that row constitute that column, read over that column’s data to get to the next length byte, and so on. You might want columns you frequently access to be first in a table, because it will take less CPU to access them. But you’d need to be accessing a lot of rows for this to be true!

Suppose you have a table with 1,000 columns and then you query the first column and the last column and compare the TKPROF reports for each. Listing 4 creates the table and shows the reports for the queries.

Code Listing 4: 1,000-column table created, queried, and reported

declare
        l_create long := 'create table t ( c1 number';
begin
        for i in 2 .. 1000
        loop
                l_create := l_create || ',c'||i||' number default ' || i;
        end loop;
        execute immediate l_create || ')';
end;
/

insert into t (c1, c1000 ) select rownum, rownum from all_objects;

exec dbms_stats.gather_table_stats( user, 'T' );

SELECT C1 FROM T
call       count    cpu      elapsed       disk      query    current    rows
————————— ———————  —————     ————————   ————————  ————————  ————————   ——————
Parse         1     0.00       0.00          0          0          0        0
Execute      10     0.00       0.00          0          0          0        0
Fetch      7230     6.41      15.72     414610     420920          0   722790
————————— ———————  —————     ————————   ————————  ————————  ————————   ——————
total      7241     6.41      15.72     414610     420920          0   722790

SELECT C1000 FROM T

call       count    cpu      elapsed       disk      query    current    rows
————————— ———————  —————     ————————   ————————  ————————  ————————   ——————
Parse         1     0.00       0.00          0          0          0       0
Execute      10     0.00       0.00          0          0          0       0
Fetch      7230     8.66      17.93     421260    3304860          0  722790
————————— ———————  —————     ————————   ————————  ————————  ————————   ——————
total      7241     8.66      17.94     421260    3304860          0  722790

In this case, the CPU overhead was partially from the parsing of 1,000 columns and the chasing down of the chained-row piece (because any row with more than 254 columns will be stored in multiple pieces). You can see this row-chaining side effect in the report in the increased query column values, which resulted from the number of buffer gets processed.

If you change the 1,000 columns in Listing 4 to 250 columns to avoid having to chase down the chained rows, you might see the result in Listing 5.

Code Listing 5: Report for table with only 250 columns

SELECT C1 FROM T

call       count    cpu      elapsed       disk      query    current    rows
————————— ———————  —————     ————————   ————————  ————————  ————————   ——————
Parse         1     0.00       0.00          0          0          0       0
Execute      10     0.00       0.00          0          0          0       0
Fetch      7230     0.62       0.62       1117      94520          0  722790
————————— ———————  —————     ————————   ————————  ————————  ————————   ——————
total      7241     0.62       0.62       1117      94520          0  722790

SELECT C250 FROM T
 
call       count    cpu      elapsed       disk      query    current    rows
————————— ———————  —————     ————————   ————————  ————————  ————————   ——————
Parse         1     0.00       0.00          0          0          0       0
Execute      10     0.00       0.00          0          0          0       0
Fetch      7230     0.96       0.97          7      94520          0  722790
————————— ———————  —————     ————————   ————————  ————————  ————————   ——————
total      7241     0.96       0.97          7      94520          0  722790

So, the farther down the CREATE list a column is, the longer it will take to retrieve that column. Having a rule that says, “The longer a column is, the farther down the create table statement it must be” doesn’t seem to make sense. There are arguments for putting nullable columns last (for space savings) and for putting infrequently accessed columns last (for performance) but none that I’m aware of for putting longer columns last.