v$sga, v$sgainfo and v$sgastat matching bytes challenge

This post is also available in: English

Those days I was challenging myself to make an exact match and correlate all lines returned by a select on v$sga, v$sgainfo and v$sgastat.

The biggest problem on those v$ views is that they bring different visions of the same thing (SGA), and I could never make a exact match of the bytes before. Now it was time to solve this puzzle. So I've built 5 different scenarios playing with AMM (Automatic Memory Management), ASMM (Automatic Shared Memory Management), complete separated pools, in-memory and PDBs.

Let's check the results and conclusions. Please share in the comments any finding or anything valuable that you may want to add.

So for this task, I've created a CDB database with default DBCA parameters and than for each scenario I've slightly changed some parameters.

The environment configuration is as follows:

Database: 12.1.0.2 + DBBP 170718

Parameter File:

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.memory_target=800m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

All Memory Parameters inferred by Oracle based based on this pfile are:

SQL> col name for a35
SQL> col value for a35
SQL> set tab off pages 1000 lines 1000

SQL> select name, type, value from v$parameter where name like '%size%' or name in ('memory_max_target','memory_target','pga_aggregate_target','sga_target') order by 1;

NAME                                                TYPE VALUE
----------------------------------- -------------------- -----------------------------------
bitmap_merge_area_size                                 3 1048576
client_result_cache_size                               6 0
create_bitmap_area_size                                3 8388608
db_16k_cache_size                                      6 0
db_2k_cache_size                                       6 0
db_32k_cache_size                                      6 0
db_4k_cache_size                                       6 0
db_8k_cache_size                                       6 0
db_block_size                                          3 8192
db_cache_size                                          6 0
db_flash_cache_size                                    6 0
db_keep_cache_size                                     6 0
db_recovery_file_dest_size                             6 4781506560
db_recycle_cache_size                                  6 0
dnfs_batch_size                                        3 4096
global_context_pool_size                               2
hash_area_size                                         3 131072
inmemory_size                                          6 0
java_max_sessionspace_size                             3 0
java_pool_size                                         6 0
large_pool_size                                        6 0
max_dump_file_size                                     2 unlimited
max_string_size                                        2 STANDARD
memory_max_target                                      6 838860800
memory_target                                          6 838860800
object_cache_max_size_percent                          3 10
object_cache_optimal_size                              3 102400
olap_page_pool_size                                    6 0
parallel_execution_message_size                        3 16384
pga_aggregate_target                                   6 0
result_cache_max_size                                  6 2097152
sga_max_size                                           6 838860800
sga_target                                             6 0
shared_pool_reserved_size                              6 6291456
shared_pool_size                                       6 0
sort_area_retained_size                                3 0
sort_area_size                                         3 65536
streams_pool_size                                      6 0
unified_audit_sga_queue_size                           3 1048576
workarea_size_policy                                   2 AUTO

40 rows selected.

PDBs of my environment:

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO

Queries that I used on every Scenario to check SGA sizes:

select * from v$sga order by 1;
select * from v$sgainfo order by 1;
select nvl(pool,name) pool, sum(bytes), con_id from v$sgastat group by nvl(pool,name), con_id order by 1,3,2;
select component, current_size, user_specified_size, con_id from v$memory_dynamic_components order by 1;

Let's begin...

Scenario 1:

For the first scenario I didn't change anything from the default configuration above. Let's check the results for the SGA queries and match them.

So, the SUM of bytes (VALUE column) in v$sga is the memory_target parameter (838860800), which is actually SGA + PGA. I've put colors for each line so will be easier to match them on next views:

We can easily match the Buffer Cache (yellow), Fixed SGA Size (orange) and Redo Buffers (light blue) of v$sga and v$sgainfo.

However, the Variable Size from v$sga is still not easy to match. However, here we have Java/Large/Shared/Streams Pools that we can match with next views (purple).

The first curious thing that we note when we compare v$sga or v$sgainfo with v$sgastat is that the Shared IO Pool is not part of the Shared Pool but part of the Buffer Cache (you need to SUM both to match with Buffer Cache line of v$sga/v$sgainfo)

When we add the v$memory_dynamic_components into the game, it also confirms the previous conclusion: Shared IO Pool is part of Buffer Cache. Note also that the Free SGA Memory Available of v$sgainfo matches the PGA Target of this view, as we are in a AMM environment. In this view, we also don't have Fixed SGA and Log Buffer as we are talking only about dynamic components.

So how do we match Variable Size of v$sga with the lines from other 3 views? Note that Variable Size is almost the same as Purple (Pools) + Dark Blue (PGA).

Variable Size = 532679408

Purple + Blue = 4194304 + 8388608 + 218103808 + 8388608 + 293601280 = 532676608

The difference (532679408 - 532676608) is actually 2800 bytes only = 350 Kbytes

So in an AMM environment, the Variable Size of v$sga means the PGA + Java/Large/Shared/Streams Pool of either v$sgainfo, v$sgastat or v$memory_dynamic_components.

Conclusions until now:

  1. The v$sgainfo and v$memory_dynamic_components says that Shared IO Pool is actually part of the Buffer Cache inside the SGA.
  2. We have a difference of 350 KBytes between the Variable Size and the sum of components inside of it.
  3. In a AMM environment, SGA Variable Size includes the whole PGA, which makes sense.

Raw output:

SQL> select * from v$sga order by 1;

NAME                      VALUE     CON_ID
-------------------- ---------- ----------
Database Buffers      297795584          0
Fixed Size              2929936          0
Redo Buffers            5455872          0
Variable Size         532679408          0

SQL> select * from v$sgainfo order by 1;

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Buffer Cache Size                 297795584 Yes          0
Data Transfer Cache Size                  0 Yes          0
Fixed SGA Size                      2929936 No           0
Free SGA Memory Available         293601280              0
Granule Size                        4194304 No           0
In-Memory Area Size                       0 No           0
Java Pool Size                      4194304 Yes          0
Large Pool Size                     8388608 Yes          0
Maximum SGA Size                  838860800 No           0
Redo Buffers                        5455872 No           0
Shared IO Pool Size                16777216 Yes          0
Shared Pool Size                  218103808 Yes          0
Startup overhead in Shared Pool   130751656 No           0
Streams Pool Size                   8388608 Yes          0

14 rows selected.

SQL> select component, current_size, user_specified_size, con_id from v$memory_dynamic_components order by 1;

COMPONENT                                                        CURRENT_SIZE USER_SPECIFIED_SIZE     CON_ID
---------------------------------------------------------------- ------------ ------------------- ----------
ASM Buffer Cache                                                            0                   0          0
DEFAULT 16K buffer cache                                                    0                   0          0
DEFAULT 2K buffer cache                                                     0                   0          0
DEFAULT 32K buffer cache                                                    0                   0          0
DEFAULT 4K buffer cache                                                     0                   0          0
DEFAULT 8K buffer cache                                                     0                   0          0
DEFAULT buffer cache                                                281018368                   0          0
Data Transfer Cache                                                         0                   0          0
In-Memory Area                                                              0                   0          0
KEEP buffer cache                                                           0                   0          0
PGA Target                                                          293601280                   0          0
RECYCLE buffer cache                                                        0                   0          0
SGA Target                                                          545259520                   0          0
Shared IO Pool                                                       16777216                   0          0
java pool                                                             4194304                   0          0
large pool                                                            8388608                   0          0
shared pool                                                         218103808                   0          0
streams pool                                                          8388608                   0          0

18 rows selected.

SQL> select nvl(pool,name) pool, sum(bytes), con_id from v$sgastat group by nvl(pool,name), con_id order by 1,3,2;

POOL                       SUM(BYTES)     CON_ID
-------------------------- ---------- ----------
buffer_cache                281018368          0
fixed_sga                     2929936          0
java pool                     4194304          0
large pool                    7159808          0
large pool                    1228800          1
log_buffer                    5455872          0
shared pool                   9097464          0
shared pool                 187926720          1
shared pool                   7473672          2
shared pool                  13605952          3
shared_io_pool               16777216          0
streams pool                  8384136          0
streams pool                     4472          1

13 rows selected.

Scenario 2:

For this scenario, I will play with Share IO Pool size to ensure that the 16Mbytes was not a coincidence. So, for parameters, everything is the same as before plus:

alter system set java_pool_size=10M scope=spfile;
alter system set large_pool_size=20M scope=spfile;
alter system set streams_pool_size=30M scope=spfile;
alter system set "_shared_io_pool_size"=40M scope=spfile;

Let's check the views:

Buffer Cache is now 8 Mbytes bigger than before (306184192-297795584). Everything is matching the same manner.

Java Pool is now with 10Mbytes, Large Pool with 20Mbytes, Streams Pool with 30Mbytes and Shared IO Pool with 40Mbytes, as expected.

Note that the buffer_cache + shared_io_pool from v$sgastat is still matching Buffer Cache Size from v$sga and v$sgainfo.

And the same applies for v$memory_dynamic_components. You need to add Shared IO Pool to DEFAULT buffer cache to have a match of Database Buffers of v$sga.

So, is the Variable Size of v$sga still matching Purple + Blue?

Variable Size = 524290800

Purple + Blue = 12582912 + 20971520 + 163577856 + 33554432 + 293601280 = 524288000

The difference (524290800 - 524288000) is still 2800 bytes = 350 Kbytes

Conclusions until now: same as before.

Raw output:

SQL> select * from v$sga order by 1;

NAME                      VALUE     CON_ID
-------------------- ---------- ----------
Database Buffers      306184192          0
Fixed Size              2929936          0
Redo Buffers            5455872          0
Variable Size         524290800          0

SQL> select * from v$sgainfo order by 1;

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Buffer Cache Size                 306184192 Yes          0
Data Transfer Cache Size                  0 Yes          0
Fixed SGA Size                      2929936 No           0
Free SGA Memory Available         293601280              0
Granule Size                        4194304 No           0
In-Memory Area Size                       0 No           0
Java Pool Size                     12582912 Yes          0
Large Pool Size                    20971520 Yes          0
Maximum SGA Size                  838860800 No           0
Redo Buffers                        5455872 No           0
Shared IO Pool Size                41943040 Yes          0
Shared Pool Size                  163577856 Yes          0
Startup overhead in Shared Pool   130920304 No           0
Streams Pool Size                  33554432 Yes          0

14 rows selected.

SQL> select component, current_size, user_specified_size, con_id from v$memory_dynamic_components order by 1;

COMPONENT                                                        CURRENT_SIZE USER_SPECIFIED_SIZE     CON_ID
---------------------------------------------------------------- ------------ ------------------- ----------
ASM Buffer Cache                                                            0                   0          0
DEFAULT 16K buffer cache                                                    0                   0          0
DEFAULT 2K buffer cache                                                     0                   0          0
DEFAULT 32K buffer cache                                                    0                   0          0
DEFAULT 4K buffer cache                                                     0                   0          0
DEFAULT 8K buffer cache                                                     0                   0          0
DEFAULT buffer cache                                                264241152                   0          0
Data Transfer Cache                                                         0                   0          0
In-Memory Area                                                              0                   0          0
KEEP buffer cache                                                           0                   0          0
PGA Target                                                          293601280                   0          0
RECYCLE buffer cache                                                        0                   0          0
SGA Target                                                          545259520                   0          0
Shared IO Pool                                                       41943040            41943040          0
java pool                                                            12582912            12582912          0
large pool                                                           20971520            20971520          0
shared pool                                                         163577856                   0          0
streams pool                                                         33554432            33554432          0

18 rows selected.

SQL> select nvl(pool,name) pool, sum(bytes), con_id from v$sgastat group by nvl(pool,name), con_id order by 1,3,2;

POOL                       SUM(BYTES)     CON_ID
-------------------------- ---------- ----------
buffer_cache                264241152          0
fixed_sga                     2929936          0
java pool                    12582912          0
large pool                   20480000          0
large pool                     491520          1
log_buffer                    5455872          0
shared pool                   6351424          0
shared pool                 141749672          1
shared pool                   6850024          2
shared pool                   8626736          3
shared_io_pool               41943040          0
streams pool                 33554432          0

12 rows selected.

Scenario 3:

Now let's convert AMM to ASMM and enable In-Memory database option and see what happens. I will keep the environment the same as scenario 2 plus:

alter system reset memory_target scope=spfile;
alter system set sga_target=500M scope=spfile;
alter system set pga_aggregate_target=300M scope=spfile;
alter system set inmemory_size=100M scope=spfile;

Checking the v$ views:

v$sga now shows a new In-Memory Area line. The SUM of all lines is not including PGA size anymore.

In v$sgainfo a the already exitent In-Memory Area line gets populated. Note that the Free SGA Memory Available is now zero as the DB cannot steal memory from PGA in a ASMM database.

In v$sgastat we still have the same behavior of buffer_cache line that needs to be added with shared_io_pool line to result in the Buffer Cache Size of previous views.

And how do we match now the Variable Size of v$sga with the other views?

Variable Size = 285214992

Purple = 12582912 + 20971520 + 218103808 + 33554432 = 285212672

The difference (285214992 - 285212672) is now 2320 bytes = 290 Kbytes (5 Mbytes less than in AMM)

New conclusions:

  1. The new difference between the Variable Size and the actual pools dropped from 350 Kbytes to 290 Kbytes.
  2. In a ASMM env, SGA Variable Size does not include the PGA. This also makes sense.

Raw output:

SQL> select * from v$sga order by 1;

NAME                      VALUE     CON_ID
-------------------- ---------- ----------
Database Buffers      125829120          0
Fixed Size              2926320          0
In-Memory Area        104857600          0
Redo Buffers            5459968          0
Variable Size         285214992          0

SQL> select * from v$sgainfo order by 1;

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Buffer Cache Size                 125829120 Yes          0
Data Transfer Cache Size                  0 Yes          0
Fixed SGA Size                      2926320 No           0
Free SGA Memory Available                 0              0
Granule Size                        4194304 No           0
In-Memory Area Size               104857600 No           0
Java Pool Size                     12582912 Yes          0
Large Pool Size                    20971520 Yes          0
Maximum SGA Size                  524288000 No           0
Redo Buffers                        5459968 No           0
Shared IO Pool Size                41943040 Yes          0
Shared Pool Size                  218103808 Yes          0
Startup overhead in Shared Pool   128349688 No           0
Streams Pool Size                  33554432 Yes          0

14 rows selected.

SQL> select component, current_size, user_specified_size, con_id from v$memory_dynamic_components order by 1;

COMPONENT                                                        CURRENT_SIZE USER_SPECIFIED_SIZE     CON_ID
---------------------------------------------------------------- ------------ ------------------- ----------
ASM Buffer Cache                                                            0                   0          0
DEFAULT 16K buffer cache                                                    0                   0          0
DEFAULT 2K buffer cache                                                     0                   0          0
DEFAULT 32K buffer cache                                                    0                   0          0
DEFAULT 4K buffer cache                                                     0                   0          0
DEFAULT 8K buffer cache                                                     0                   0          0
DEFAULT buffer cache                                                 83886080                   0          0
Data Transfer Cache                                                         0                   0          0
In-Memory Area                                                      104857600           104857600          0
KEEP buffer cache                                                           0                   0          0
PGA Target                                                          314572800           314572800          0
RECYCLE buffer cache                                                        0                   0          0
SGA Target                                                          524288000           524288000          0
Shared IO Pool                                                       41943040            41943040          0
java pool                                                            12582912            12582912          0
large pool                                                           20971520            20971520          0
shared pool                                                         218103808                   0          0
streams pool                                                         33554432            33554432          0

18 rows selected.

SQL> select nvl(pool,name) pool, sum(bytes), con_id from v$sgastat group by nvl(pool,name), con_id order by 1,3,2;

POOL                       SUM(BYTES)     CON_ID
-------------------------- ---------- ----------
buffer_cache                 83886080          0
fixed_sga                     2926320          0
java pool                    12582912          0
large pool                   20480000          0
large pool                     491520          1
log_buffer                    5459968          0
shared pool                   4004152          0
shared pool                 198747816          1
shared pool                   6771144          2
shared pool                   8580696          3
shared_io_pool               41943040          0
streams pool                 33554432          0

12 rows selected.

Scenario 4:

For this scenario, now I will enable some other Buffer Cache pools like 2k/4k/recycle/keep/etc. As before, the parameters are all the same of scenario 3 plus:

alter system set db_recycle_cache_size=10M scope=spfile;
alter system set db_keep_cache_size=10M scope=spfile;
alter system set db_2k_cache_size=10M scope=spfile;
alter system set db_4k_cache_size=10M scope=spfile;
alter system set db_16k_cache_size=10M scope=spfile;
alter system set db_32k_cache_size=10M scope=spfile;
alter system set sga_target=600M scope=spfile;

Let's check the views:

No surprise here. Let's check latter how we can match those lines with other views:

v$sga and v$sgainfo have a direct match as before (expect for Variable Size of v$sga).

And the same strange behavior regarding shared_io_pool again for v$sgastat.

Now for v$memory_dynamic_components things get more excited. Note that the yellow lines that represents segments in Buffer Cache are nor more divided. We need to SUM all the "* buffer cache" and Shared IO Pool to have a match with Buffer Cache Size of v$sgainfo:

Buffer Cache in v$sgainfo = 226492416

"* buffer cache" and Shared IO Pool in v$memory_dynamic_components = 12582912 + 12582912 + 12582912 + 12582912 + 109051904 + 12582912 + 12582912 + 41943040 = 226492416

And what about v$sga Variable Size? It's almost again the SUM of purples for either v$sgastat, v$sgainfo or v$memory_dynamic_components:

Variable Size = 289408088

Purple = 12582912 + 20971520 + 222298112 + 33554432 = 289406976

The difference (289408088 - 289406976) dropped now to 1112 bytes = 139 Kbytes

New conclusions:

  1. The new difference between the Variable Size and the actual pools dropped from 290 Kbytes to 139 Kbytes.

Raw output:

SQL> select * from v$sga order by 1;

NAME                      VALUE     CON_ID
-------------------- ---------- ----------
Database Buffers      226492416          0
Fixed Size              2927528          0
In-Memory Area        104857600          0
Redo Buffers            5459968          0
Variable Size         289408088          0

SQL> select * from v$sgainfo order by 1;

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Buffer Cache Size                 226492416 Yes          0
Data Transfer Cache Size                  0 Yes          0
Fixed SGA Size                      2927528 No           0
Free SGA Memory Available                 0              0
Granule Size                        4194304 No           0
In-Memory Area Size               104857600 No           0
Java Pool Size                     12582912 Yes          0
Large Pool Size                    20971520 Yes          0
Maximum SGA Size                  629145600 No           0
Redo Buffers                        5459968 No           0
Shared IO Pool Size                41943040 Yes          0
Shared Pool Size                  222298112 Yes          0
Startup overhead in Shared Pool   131422720 No           0
Streams Pool Size                  33554432 Yes          0

14 rows selected.

SQL> select component, current_size, user_specified_size, con_id from v$memory_dynamic_components order by 1;

COMPONENT                                                        CURRENT_SIZE USER_SPECIFIED_SIZE     CON_ID
---------------------------------------------------------------- ------------ ------------------- ----------
ASM Buffer Cache                                                            0                   0          0
DEFAULT 16K buffer cache                                             12582912            12582912          0
DEFAULT 2K buffer cache                                              12582912            12582912          0
DEFAULT 32K buffer cache                                             12582912            12582912          0
DEFAULT 4K buffer cache                                              12582912            12582912          0
DEFAULT 8K buffer cache                                                     0                   0          0
DEFAULT buffer cache                                                109051904                   0          0
Data Transfer Cache                                                         0                   0          0
In-Memory Area                                                      104857600           104857600          0
KEEP buffer cache                                                    12582912            12582912          0
PGA Target                                                          314572800           314572800          0
RECYCLE buffer cache                                                 12582912            12582912          0
SGA Target                                                          629145600           629145600          0
Shared IO Pool                                                       41943040            41943040          0
java pool                                                            12582912            12582912          0
large pool                                                           20971520            20971520          0
shared pool                                                         222298112                   0          0
streams pool                                                         33554432            33554432          0

18 rows selected.

SQL> select nvl(pool,name) pool, sum(bytes), con_id from v$sgastat group by nvl(pool,name), con_id order by 1,3,2;

POOL                       SUM(BYTES)     CON_ID
-------------------------- ---------- ----------
buffer_cache                184549376          0
fixed_sga                     2927528          0
java pool                    12582912          0
large pool                   20480000          0
large pool                     491520          1
log_buffer                    5459968          0
shared pool                   5184240          0
shared pool                 201543320          1
shared pool                   6800384          2
shared pool                   8770168          3
shared_io_pool               41943040          0
streams pool                 33554432          0

12 rows selected.

Scenario 5:

For the last scenario, I will now define each SGA component size individually and check what happens. Again, the same parameters as before plus:

alter system reset sga_target scope=spfile;
alter system set db_cache_size=100M scope=spfile;
alter system set shared_pool_size=200M scope=spfile;

Checking the views:

Nothing special changed in v$sga.

Nothing special changed in v$sgainfo.

Nothing special changed in v$sgastat.

Lastly, let's check if the difference among Variable Size of v$sga and SGA pools have changed.

Variable Size = 289408376

Purple = 12582912 + 20971520 + 222298112 + 33554432 = 289406976

The difference (289408376 - 289406976) increased now to 1400 bytes = 175 Kbytes

New conclusions:

  1. The new difference between the Variable Size and the actual pools increased from 139 Kbytes to 170 Kbytes.

Raw output:

SQL> select * from v$sga order by 1;

NAME                      VALUE     CON_ID
-------------------- ---------- ----------
Database Buffers      209715200          0
Fixed Size              2927240          0
In-Memory Area        104857600          0
Redo Buffers            5459968          0
Variable Size         289408376          0

SQL> select * from v$sgainfo order by 1;

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Buffer Cache Size                 209715200 Yes          0
Data Transfer Cache Size                  0 Yes          0
Fixed SGA Size                      2927240 No           0
Free SGA Memory Available                 0              0
Granule Size                        4194304 No           0
In-Memory Area Size               104857600 No           0
Java Pool Size                     12582912 Yes          0
Large Pool Size                    20971520 Yes          0
Maximum SGA Size                  612368384 No           0
Redo Buffers                        5459968 No           0
Shared IO Pool Size                41943040 Yes          0
Shared Pool Size                  222298112 Yes          0
Startup overhead in Shared Pool   127957928 No           0
Streams Pool Size                  33554432 Yes          0

14 rows selected.

SQL> select component, current_size, user_specified_size, con_id from v$memory_dynamic_components order by 1;

COMPONENT                                                        CURRENT_SIZE USER_SPECIFIED_SIZE     CON_ID
---------------------------------------------------------------- ------------ ------------------- ----------
ASM Buffer Cache                                                            0           104857600          0
DEFAULT 16K buffer cache                                             12582912            12582912          0
DEFAULT 2K buffer cache                                              12582912            12582912          0
DEFAULT 32K buffer cache                                             12582912            12582912          0
DEFAULT 4K buffer cache                                              12582912            12582912          0
DEFAULT 8K buffer cache                                                     0                   0          0
DEFAULT buffer cache                                                 92274688            92274688          0
Data Transfer Cache                                                         0                   0          0
In-Memory Area                                                      104857600           104857600          0
KEEP buffer cache                                                    12582912            12582912          0
PGA Target                                                          314572800           314572800          0
RECYCLE buffer cache                                                 12582912            12582912          0
SGA Target                                                                  0                   0          0
Shared IO Pool                                                       41943040            41943040          0
java pool                                                            12582912            12582912          0
large pool                                                           20971520            20971520          0
shared pool                                                         222298112           222298112          0
streams pool                                                         33554432            33554432          0

18 rows selected.

SQL> select nvl(pool,name) pool, sum(bytes), con_id from v$sgastat group by nvl(pool,name), con_id order by 1,3,2;

POOL                       SUM(BYTES)     CON_ID
-------------------------- ---------- ----------
buffer_cache                167772160          0
fixed_sga                     2927240          0
java pool                    12582912          0
large pool                   20971520          0
log_buffer                    5459968          0
shared pool                   9369744          0
shared pool                 198289040          1
shared pool                   6500608          2
shared pool                   8138720          3
shared_io_pool               41943040          0
streams pool                 33554432          0

11 rows selected.

Conclusion:

After these 5 tests, I came with 3 major conclusions:

  1. The Shared IO Pool is part of Buffer Cache accordingly to v$sgastat and v$memory_dynamic_components views. v$sga put it also in Database Buffers, not in Variable Size. v$sgainfo show them separated.
  2. The Variable Size of v$sga is the sum of Large Pool, Java Pool, Streams Pool and Shared Pool (not Shared IO Pool). If AMM is enabled, it also includes PGA.
  3. There is very small difference (350 Kbytes is the highest I got) in Variable Size to match it with the pools. I would love to understand why this difference is not exactly zero. If anyone knows please share.
Have you enjoyed? Please leave a comment or give a 👍!

Deixe um comentário

Seu e-mail não será publicado.