The formulas in this section are just starting points. Whether you are using automated or manual tuning, you should monitor usage on an on-going basis to ensure the size of memory is not too large or too small. To ensure the correct memory size, tune the shared pool using the following query:
SELECT POOL, NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL LIKE '%pool%' GROUP BY ROLLUP (POOL, NAME);
Your output should resemble the following:
POOL NAME SUM(BYTES)
----------- -------------------------- ----------
shared pool Checkpoint queue 38496
shared pool KGFF heap 1964
shared pool KGK heap 4372
shared pool KQLS heap 1134432
shared pool LRMPD SGA Table 23856
shared pool PLS non-lib hp 2096
shared pool PX subheap 186828
shared pool SYSTEM PARAMETERS 55756
shared pool State objects 3907808
shared pool character set memory 30260
shared pool db_block_buffers 200000
shared pool db_block_hash_buckets 33132
shared pool db_files 122984
shared pool db_handles 52416
shared pool dictionary cache 198216
shared pool dlm shared memory 5387924
shared pool event statistics per sess 264768
shared pool fixed allocation callback 1376
shared pool free memory 26329104
shared pool gc_* 64000
shared pool latch nowait fails or sle 34944
shared pool library cache 2176808
shared pool log_buffer 24576
shared pool log_checkpoint_timeout 24700
shared pool long op statistics array 30240
shared pool message pool freequeue 116232
shared pool miscellaneous 267624
shared pool processes 76896
shared pool session param values 41424
shared pool sessions 170016
shared pool sql area 9549116
shared pool table columns 148104
shared pool trace_buffers_per_process 1476320
shared pool transactions 18480
shared pool trigger inform 24684
shared pool 52248968
90641768
Evaluate the memory used as shown in your output, and alter the setting for SHARED_POOL_SIZE based on your processing needs.
To obtain more memory usage statistics, execute the following query:
SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';
Your output should resemble the following:
STATISTIC VALUE ------------------- ----- Buffers Allocated 23225 Buffers Freed 23225 Buffers Current 0 Buffers HWM 3620
The amount of memory used appears in the Buffers Current and Buffers HWM statistics. Calculate a value in bytes by multiplying the number of buffers by the value for PARALLEL_EXECUTION_MESSAGE_SIZE. Compare the high water mark to the parallel execution message pool size to determine if you allocated too much memory. For example, in the first output, the value for large pool as shown in px msg pool is 38,092,812 or 38 MB. The Buffers HWM from the second output is 3,620, which when multiplied by a parallel execution message size of 4,096 is 14,827,520, or approximately 15 MB. In this case, the high water mark has reached approximately 40 percent of its capacity.