Automatic Shared Memory Management is disabled for your database instance. You realize that
there are cases of SQL statements performing poorly because of repeated parsing activity, resulting
in degradation of performance. What would be your next step to improve performance?
A.
Run the SQL Access Advisor
B.
Run the memory Advisor for the SGA
C.
Run the memory Advisor for the PGA
D.
Run the memory advisor for the shared pool
E.
Run the memory advisor for the buffer cache
If you prefer to exercise more direct control over the sizes of individual memory components, you can disable automatic memory management and configure the database for manual memory management. There are two different manual memory management methods for the SGA, and two for the instance PGA.
Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules.
Three memory advisor parameter in Oracle:
Shared Pool Advisor (SGA)
Program Global Area Advisor (PGA)
1. Shared Pool Advisor: The System Global Area (SGA) is a group of shared memory structures that contains data and control information for one Oracle database. The SGA is allocated in memory when an Oracle database instance is started.
2. Program Global Area Advisor: The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started.
In the case of disabled ASMM, sql run performance degraded, there is a lot of duplication of analytical results in performance degradation.
sql is ran and parsed in the shared pool.
The reason is that the shared pool SHARED_POOL_SIZE parameter setting is too small, the proposed value is a guide to perform a memory reset SHARED_POOL_SIZE parameters.
SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multi-user systems. Smaller values use less memory.