I was doing an R&D in ORACLE 11g Release 2 and thought of to check how the data is getting populated across the views. As every other DBA knows how important is the above v$ views and what type of information does it provide. I am not going talk about the same old story regarding the purpose and usage of the above v$ views, as I believe you can get enough information from the ORACLE Documentation.
Let’s see something else today about the v$views and Pool called “numa pool” and its usage in Oracle 11g Release 2. why we are waiting across let’s get’s started why to waste time….
let’s see the output of v$sga
1: SQL> desc v$sga
2: Name Null? Type
3: ----------------------------------------- -------- -------------
4: NAME VARCHAR2(20)5: VALUE NUMBER
6: 7: SQL> select * from v$sga;
8: 9: NAME VALUE
10: -------------------- ----------
11: Fixed Size 1374808
12: Variable Size 385877416
13: Database Buffers 385875968
14: Redo Buffers 5259264let’s check from the details are getting populated. We have ideas kernel tables are the base tables which are having information regarding the our Database. Which Internals tables are acting as base tables for v$sga.
It’s
X$KSMSD – Kernel Service Layer Memory Management SGA Definitions
1: SQL> desc x$ksmsd
2: Name Null? Type
3: ----------------------------------------- -------- --------------
4: ADDR RAW(4) 5: INDX NUMBER 6: INST_ID NUMBER 7: KSMSDNAM VARCHAR2(20) 8: KSMSDVAL NUMBER 9: 10: SQL> select * from x$ksmsd;
11: 12: ADDR INDX INST_ID KSMSDNAM KSMSDVAL13: -------- ---------- ---------- -------------------- ----------
14: 0697DD98 0 1 Fixed Size 1374808
15: 0697DDB8 1 1 Variable Size 385877416
16: 0697DDD8 2 1 Database Buffers 385875968
17: 0697DDF8 3 1 Redo Buffers 5259264If you check the above screen shots both results and data is matching. Going further we will see how to get the details and compare from trace files events.
Coming to V$SGASTAT,
1: SQL> select * from v$sgastat;
2: 3: POOL NAME BYTES 4: ------------ -------------------------- ----------
5: fixed_sga 1374808 6: buffer_cache 385875968 7: log_buffer 5259264 8: shared pool kgqbt_alloc_block 3072 9: shared pool dpslut_kfdsg 256 10: shared pool kkj jobq wor 4104 11: shared pool hot latch diagnostics 80
12: shared pool vips_package_file 924 13: shared pool ENQUEUE STATS 16296 14: shared pool vem_user_actlog 464 15: shared pool transaction 428428
16: 17: ...... 18: ....... 19: ...... 20: 21: POOL NAME BYTES 22: ------------ -------------------------- ----------
23: shared pool KTI latches 432 24: shared pool KGSK scheduler 35400 25: shared pool KKJ WRK LAT 312 26: shared pool HM_RECOMMENDATION 1452 27: shared pool kfkhsh_kfdsg 2052 28: shared pool Wait History Segment 257920 29: shared pool IPS_FILE_METADATA 660 30: shared pool INC_METER_CONFIG 860 31: shared pool event statistics ptr arra 992
32: shared pool KGKP randnum 40000 33: large pool PX msg pool 491520
34: 35: POOL NAME BYTES 36: ------------ -------------------------- ----------
37: large pool free memory 7897088
38: java pool free memory 8388608
39: streams pool free memory 16777216
40: 41: 872 rows selected.
We have got almost 872 records, which are memory components of SGA – the memory is divided across under the pool. Each memory component is going it work/participant in the algorithm written by ORACLE for the Optimizer. It’s interesting, so many components and from top level we look at it we only know some of them. let’s see the base version of kernel tables acting for this view.
kernel tables are
x$ksmfs - Details of Fixed SGA Varitables
x$ksmss - Statistics of SGA objects
x$ksmls - large Pool stats
x$ksmjs - Java Pool stats
x$ksmns - Numa Pool stats - presently we need to see how/ where it is internally getting used
x$ksmstrs - Streams Pool stats
The union of above all tables makes the v$sgastats, Please check the query below.
1: SQL> select inst_id,'', ksmssnam, ksmsslen
2: from x$ksmfs
3: where ksmsslen>1
4: union all
5: select inst_id,'shared pool',ksmssnam, sum(ksmsslen)
6: from x$ksmss
7: where ksmsslen>1 group by inst_id, 'shared pool', ksmssnam
8: union all
9: select inst_id,'large pool',ksmssnam, sum(ksmsslen)
10: from x$ksmls
11: where ksmsslen>1
12: group by inst_id, 'large pool', ksmssnam
13: union all
14: select inst_id,'java pool',ksmssnam, sum(ksmsslen)
15: from x$ksmjs
16: where ksmsslen>1
17: group by inst_id, 'java pool', ksmssnam
18: union all
19: select inst_id,'numa pool',ksmnsnam, sum(ksmnslen)
20: from x$ksmns
21: where ksmnslen>1
22: group by inst_id, 'numa pool', ksmnsnam
23: union all
24: select inst_id,'streams pool',ksmssnam, sum(ksmsslen)
25: from x$ksmstrs
26: where ksmsslen>1
27: group by inst_id, 'streams pool', ksmssnam;
28: 29: INST_ID '' KSMSSNAM KSMSSLEN
30: ---------- ------------ -------------------------- ----------
31: 1 fixed_sga 1374808 32: 1 buffer_cache 385875968 33: 1 log_buffer 5259264 34: 1 shared pool kgqbt_alloc_block 3072 35: 1 shared pool dpslut_kfdsg 256 36: 1 shared pool kkj jobq wor 4104 37: 1 shared pool hot latch diagnostics 80
38: 1 shared pool vips_package_file 924 39: 1 shared pool ENQUEUE STATS 16296 40: 1 shared pool vem_user_actlog 464 41: 1 shared pool transaction 428428
42: 43: ... 44: ... 45: ... 46: 47: INST_ID '' KSMSSNAM KSMSSLEN
48: ---------- ------------ -------------------------- ----------
49: 1 shared pool KTI latches 432 50: 1 shared pool KGSK scheduler 35400 51: 1 shared pool KKJ WRK LAT 312 52: 1 shared pool HM_RECOMMENDATION 1452 53: 1 shared pool kfkhsh_kfdsg 2052 54: 1 shared pool Wait History Segment 257920 55: 1 shared pool IPS_FILE_METADATA 660 56: 1 shared pool INC_METER_CONFIG 860 57: 1 shared pool event statistics ptr arra 992
58: 1 shared pool KGKP randnum 40000 59: 1 large pool PX msg pool 491520
60: 61: INST_ID '' KSMSSNAM KSMSSLEN
62: ---------- ------------ -------------------------- ----------
63: 1 large pool free memory 7897088
64: 1 java pool free memory 8388608
65: 1 streams pool free memory 16777216
66: 67: 872 rows selected.
Numa pool is mainly used for working with huge pages in Oracle. If you like to get details about the Pool and how it will get used then you can get enough information from the following link
0 comments: on "V$SGA and V$SGASTAT kernel details and Numa Pool in Oracle 11g Release 2."
Post a Comment