Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
To configure Automatic Memory Management(AMM) in databases that do not use AMM, it is necessary to change the required parameters and restart the database.
When AMM is active in Oracle databases, if the memory values grow and shrink too frequently, “database: pin S wait on X” or “library cache lock” wait events can be observed in the database. In such a case, you can find what needs to be done in the “Effects of Very Frequent Growth and Shrink Operations on Automatic Memory Management(AMM)” article.
Change the parameter:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=250G SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET MEMORY_TARGET=150G SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE; System altered. |
The database needs to be restarted after you change the parameter.
1 2 3 | srvctl stop database -d ORCL srvctl start database -d ORCL |
