Set the database to start with spfile by default, and set the database SGA size to 2G and PGA size to 200M.

Check the database default startup file first:

SELECT value FROM v$parameter WHERE name = ‘spfile’;

It can be seen that the database defaults to SPFILE startup

Use the following command to check whether the current session is using SPFILE or PFILE:

First, set the MEMORY_TARGET parameter to 0. This will ensure that Oracle does not automatically manage the size of the SGA and PGA, but instead lets you configure them manually. Execute the following command:

ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE;

Set the size of the SGA to 2GB and the PGA to 200MB using the following commands:

ALTER SYSTEM SET sga_target=2G scope=spfile;

ALTER SYSTEM SET pga_aggregate_target=200M scope=spfile;

This sets the values of the system-level parameters SGA_TARGET and PGA_AGGREGATE_TARGET in spfile and sets their scope to spfile. Note that the database must be restarted before changing these values to the new values.

Encountered a problem:

Solution:

If you encounter the problem of insufficient memory, you can try to optimize by adjusting the size of SGA (System Global Area) and PGA (Program Global Area). Here are the general steps to do this in Oracle:

  1. Connect to the correct database instance and ensure you have the appropriate permissions.

  2. First, set the MEMORY_TARGET parameter to 0. This will ensure that Oracle does not automatically manage the size of the SGA and PGA, but instead lets you configure them manually. Execute the following command:

    ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE;

  3. Then, set the SGA_TARGET and PGA_AGGREGATE_TARGET parameters to the desired size. You can choose the appropriate value according to your actual needs. Execute the following command to set it up:

    ALTER SYSTEM SET SGA_TARGET=2G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M SCOPE=SPFILE;

  4. Restart the database instance to make the new parameter settings take effect:

    SHUTDOWN IMMEDIATE;
STARTUP;

  5. Check whether the parameter settings take effect. Execute the following command to confirm whether the parameter settings are correct:

    SHOW PARAMETER sga_target;
SHOW PARAMETER pga_aggregate_target;

  6. You can try different SGA and PGA size combinations and adjust according to actual conditions. It is recommended to set it to a smaller value first and then gradually increase it until you achieve performance that satisfies you.

Note that sizing SGA and PGA may require a lot of experimentation and testing to find the configuration that works best for your database instance. Also, ensure that the server’s physical memory is sufficient to support the configured SGA and PGA sizes to avoid out-of-memory issues.

Specific reason: In fact, it is the difference between AMM and ASMM

Specific reference:

Youdao Cloud Notesicon-default.png?t=N7T8https://note.youdao.com/s/Kp5LSlv4

1. Automatic memory management (AMM) (the method adopted by the default Oracle11g instance): only MEMORY_TARGET needs to be explicitly set, and no other memory parameter settings are required.

2. Automatic Shared Memory Management (ASMM) (the memory management method adopted by Oracle10g): Explicitly specify the value of SGA_TARGET, without setting the internal value of SGA.

3. Manual shared memory management (memory management method adopted by Oracle9i): SGA_TARGET and MEMORY_TARGET must be set to 0. Then manually set share_pool_size, db_cache_size and other sga parameters.

4. Automatic PGA memory management (method introduced by Oracle9i): Here you only need to set the value of PGA_AGGREGATE_TARGET. Of course, if you want to achieve fine control and switch to manual PGA memory management mode, you need to set WORKAREA_SIZE_POLICY = manual (the default is AUTO), and then set other values such as SORT_AREA_SIZE.

From ASMM to AMM

SQL> alter system set memory_max_target=360m scope=spfile;
 
SQL> alter system set memory_target=360m scope=spfile;
 
SQL> alter system set sga_target=0m scope=spfile;
 
SQL> alter system set sga_max_size=0 scope=spfile;
 
SQL> alter system set pga_aggregate_target=0 scope=spfile;

From AMM to ASMM

SQL> alter system set memory_max_target=0 scope=spfile;
 
SQL> alter system set memory_target=0 scope=spfile;
 
SQL> alter system set pga_aggregate_target=100m scope=spfile;
 
SQL> alter system set sga_target=260m scope=spfile;
 
SQL> alter system set sga_max_size=260m scope=spfile;
 
 
-------------------------------------------------- --------------------------
If you restart the system, an error will be reported.
 
SQL> startup force
 
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
 
ORA-00849: SGA_TARGET 272629760 cannot be set to more than MEMORY_MAX_TARGET 0.
 
The cause of this problem is the internal checking of parameters during Oracle startup. Because MEMORY_MAX_TARGET is "explicitly" assigned, it conflicts with the SGA_TARGET assignment.
 
The solution is to use parameter default values. After creating the pfile, delete the MEMORY_TARGET and MEMORY_MAX_TARGET record lines with a value of 0. Then use pfile to start the database and rebuild spfile.
  • Start ASMM

1. From manual SGA management to ASMM
Get the size of a SGA_TARGET according to the following query

SELECT ( (SELECT SUM(value) FROM V$SGA) – (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY) ) “SGA_TARGET”FROM DUAL;

Set size ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]

2. From AMM to ASMM

Set the MEMORY_TARGET parameter to 0, ALTER SYSTEM SET MEMORY_TARGET = 0;

Make a note of setting the value of SGA_TARGET, and you can also set the minimum value of each component.

ALTER SYSTEM SET SGA_TARGET = 992M;

ALTER SYSTEM SET SHARED_POOL_SIZE = 0;

ALTER SYSTEM SET LARGE_POOL_SIZE = 0;

ALTER SYSTEM SET JAVA_POOL_SIZE = 0;

ALTER SYSTEM SET DB_CACHE_SIZE = 0;

ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;

The value of each component in SGA can be customized to the minimum value of the component, and then the database adjusts the corresponding value according to the operating conditions.

The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. MySQL entry-level skills treeHomepageOverview 76772 people are learning the system