I can understand why stat in v$sgastat is not exactly equal to shared_pool_size in init.ora but shouldn't sum(value) in v$sga match with sum(bytes) in v$sgastat ? 

I know my SGA is 1.2G (confirmed by v$sga and ipcs). Somehow the value of 5.5G reported by v$sgastat (due to abnormal 4.2G for shared pool miscellaneous) is incorrect. Not sure why though.

This is what I see:

SQL> select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size                69616
Variable Size         367816704
Database Buffers      838860800
Redo Buffers              90112

SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
1206837232


  1  select nvl(pool,'<unnamed pool>') pool,  name, bytes from v$sgastat
  2* order by 1, 3 desc
SQL> /

POOL           NAME                            BYTES
-------------- -------------------------- ----------
<unnamed pool> db_block_buffers            838860800
<unnamed pool> fixed_sga                       69616
<unnamed pool> log_buffer                      66560
java pool      free memory                  17772544
java pool      memory in use                 2228224
shared pool    miscellaneous              4292546012
shared pool    sql area                    165410724
shared pool    library cache                92752548
shared pool    free memory                  44493116
shared pool    db_block_buffers             27852800
shared pool    dictionary cache              5134736

POOL           NAME                            BYTES
-------------- -------------------------- ----------
shared pool    ktprhtnew3                    4313176
shared pool    db_block_hash_buckets         3539016
shared pool    KQLS heap                     3047488
shared pool    PL/SQL MPCODE                 1709988
shared pool    PL/SQL DIANA                   977796
shared pool    event statistics per sess      390080
shared pool    sessions                       242880
shared pool    KGFF heap                       79244
shared pool    PX msg pool                     64496
shared pool    table columns                   62612
shared pool    joxlod: in ehe                  40636

POOL           NAME                            BYTES
-------------- -------------------------- ----------
shared pool    type object de                  34160
shared pool    table definiti                  17852
shared pool    trigger defini                   9180
shared pool    PX subheap                       7024
shared pool    KGK heap                         6612
shared pool    pl/sql source                    4800
shared pool    view columns d                   2160
shared pool    PLS non-lib hp                   2096
shared pool    trigger inform                   1780
shared pool    temporary tabl                   1692
shared pool    fixed allocation callback         960

POOL           NAME                            BYTES
-------------- -------------------------- ----------
shared pool    errors                            792
shared pool    joxs heap init                    432

35 rows selected.

SQL> select sum(bytes) from v$sgastat;

SUM(BYTES)
----------
5501744632
 

Tom:

1. What is the difference between sharred pool and block buffer cache? in your book you say the first one is used for cursors and stored procedures. DOes it mean for QUERY only while the block buffer cache is for insert and update?

2. How do you size these things? How do you determine the right size? In your book you say you do not want it too big or too small? can you figure out from queries how much cache memory they need?

3.  X$bh does not exist in oracle?

SQL> select tch from x$bh;
select tch from x$bh
                *
ERROR at line 1:
ORA-00942: table or view does not exist