Oracle Performance Tuning

Chapter One
Chapter Two
Chapter Three
Chapter Four
Chapter Five


Chapter One

To find init.ora parameter settings:

select     name, value
from       v$parameter
where      name in ('db_block_buffers', 'db_block_size',
            'shared_pool_size',' 'sort_area_size');

NAME                          VALUE
db_block_buffers                4000
db_block_size                   4096
shared_pool_size           7,000,000
sort_area_size               262,144


To find out how much of the data selected by users is hit in memory:

select        (1 - sum ( decode(name, 'physical reads','value, 0)) /
              (sum (decode (name, 'db block gets', value, 0)) +
            sum (decode (name, 'consistent gets', value, 0 ))))
            * 100 "Hit Ratio"
from          v$sysstat;

Hit Ratio
99.08%

Oracle recommends  ...            > 90%
Performance Gain by 400% ... 95-99%


To find the dictionary hit ratio:

select    (1-(sum(getmisses) / sum(gets))) * 100 "Hit Ratio"
from      v$rowchache

Hit Ratio
95.40 %

To find the library cache hit ratio:
 

select         Sum(Pins) / (Sum(Pins) + Sum(Reloads)
               * 100 "Hit Ratio"
from           v$librarycache

Hit Ratio
99.40%
 


To determine how much memory is left for SHARED_POOL_SIZE
 
 

Shared Pool detail of 1,000 MB upon start-up:

select     sum(ksmchsiz) Bytes, ksmchcls Status
from       x$ksmsp
group by    ksmchcls;
 

     BYTES       STATUS
50,000,000         R-free
         40       R-freea
888,326,956          free
 61,702,380          perm
    359,008          recr

Query to get memory and disk sorts:
 
 
 
 
 
 
 

Block Count and Availbility:

select    decode(state, 0, 'FREE',
          1, decode (lrba_seq, 0, 'AVIALABLE','BEING USED'),
          3, 'BEING USED',3, 'BEING USED', state);

BLOCK STATUS       COUNT(*)
AVIALABLE               779
BEING USED              154
FREE                    167
 


Chapter Two

To find indexes on a specific table:

select    table_name, index_name
from      dba_indexes
where     table_name = 'EMP';

table_name           index_name
emp                   emp_idx1
emp                   emp_idx2

To find out which COLUMNS on a table are indexed:

select    table_name, index_name, column_name,
          column_position
from      dba_ind_columns
order by  table_name, index_name, column_position

table_name    index_name    column_name  column_positon
emp           emp_idx1      empid        1
emp           emp_idx1      ename        2
emp           emp_idx1      deptno       3
emp           emp_idx2      salary       4


Finding the binary height of an index:

analyze index emp_idx1 compute statistics;

Index analyzed.

select     blevel, index_name
from       dba_indexes
where      index_name = 'EMP_IDX1;
 

blevel     index_name
1          emp_idx1


To populate and check an index:

analyze index emp_idx1 validate structure;

Index analyzed.

select     name, height, blcks, distinct_keys
from       index_stats
where      name = 'EMP_IDX1';

NAME        HEIGHT        BLOCKS     DISTINCT_KEYS
emp_idx1    2             5          302


To create a bitmap index:

create bitmap index dept_idx2_bm on dept (deptno);

Index created.


To display indexes:

select        index_name, index_type
from          user_indexes;

index_name       index_type
ord_itm_item     bitmap
ord_itm_ord_id   normal
ord_itm_pk       normal


Chapter 3

To determine file I/O problems:

select       name, phyrds, phyerts, readtim, writetim
from         v$filestat a, v$bfile b
where        a.file# = b.file#
order by     readtim desc;

NAME                 PHYRDS   PHYWRTS  READTIM  WRITETIM

/d01/psindex_1.dbx   48,310   51,798   200,564  903,199
/d02/psindex_02.dbf  34,520   40,224   117,925  611,121
/d03/psdata_01.dbf   35,189   36,904   97,474   401,290
/d04/rbs01.dbf       1,320    11,725   1,214    39,892
/d05/system01.dbf    1,454    10       10       956

To find fragmented tables/indexes:

select  segment_name, segment_type, extents, bytes
from    dba_segments
where   extents > 5;

SEGMENT_NAME   SEGMENT_TYPE  EXTENTS  BYTES
ORDER          TABLE         22       22000000
ORDER_IDX1     INDEX         12       12000000
CUSTOMER       TABLE         7        7000000


 
 

Main theme of these scripts:
1) Information is easily avilable
2) Info is custoizeable on-the-fly
3) Query format is easily customizeable (i.e., row breaks, etc.) 1