Buffer pool Stats

A) Get the Details of Buffer Pools.

SQL> compute sum of bytes on pool
SQL> break on pool skip 1
SQL> select pool, name, bytes
2 from v$sgastat
3 order by pool, name ;
POOL NAME BYTES
----------- ------------------------------ ----------
java pool free memory 18366464
memory in use 2605056
*********** ----------
Sum 20971520
large pool free memory 6079520
session heap 64480
*********** ----------
sum 6144000



B) Check buffers in Buffer cache.

SQL> select file_id, block_id
2 from dba_extents
3 where segment_name = 'DUAL' and owner = 'SYS';
FILE_ID BLOCK_ID
---------- ----------
1 465

Now we can use that information to see the 'touch count' on that block:

sys@TKYTE816> select tch from x$bh where file# = 1 and dbablk = 465;
TCH
----------
10

sys@TKYTE816> select * from dual;
D
-
X

sys@TKYTE816> select tch from x$bh where file# = 1 and dbablk = 465;
TCH
----------
11

sys@TKYTE816> select * from dual;
D
-
X

sys@TKYTE816> select tch from x$bh where file# = 1 and dbablk = 465;
TCH
----------
12


c) To get the Session process id (Server) Process id (Clinet) .


ops$tkyte@ORA8I.WORLD> select a.spid dedicated_server,
2 b.process clientpid
3 from v$process a, v$session b
4 where a.addr = b.paddr
5 and b.audsid = userenv('sessionid')
6 /