Forcing ASMM component to shrink

Here’s the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can’t shrink.

When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasing in small increments over time. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, because the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find granules that can be freed. Therefore, the tuning algorithm only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit.

Currently ASMM is enabled.

SQL> alter system set sga_target = 300M;

System altered.

SQL> alter system set shared_pool_size = 0;

System altered.

SQL> alter system set db_cache_size = 0;

System altered.

SQL> show sga

Total System Global Area  314572800 bytes
Fixed Size                  1261564 bytes
Variable Size             222298116 bytes
Database Buffers           88080384 bytes
Redo Buffers                2932736 bytes

Lets try to shink shared pool and increase db_cache_size

SQL> alter system set shared_pool_size = 75M;

System altered.

SQL> alter system set db_cache_size = 200M;

alter system set db_cache_size = 200M

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00384: Insufficient memory to grow cache

Now lets switch to manual mode temporarily

SQL> alter system set sga_target = 0;

System altered.

SQL> alter system set shared_pool_size = 75M;

System altered.

SQL> alter system set db_cache_size = 200M;

System altered.

SQL> show sga

Total System Global Area  314572800 bytes
Fixed Size                  1261564 bytes
Variable Size             100663300 bytes
Database Buffers          209715200 bytes
Redo Buffers                2932736 bytes

Voila! We have successfully decreased shared pool and increased the db_cache_size. Now let switch back to ASMM mode.

SQL> alter system set sga_target = 300M;

System altered.

SQL> alter system set shared_pool_size = 0;

System altered.

SQL> alter system set db_cache_size = 0;

System altered.

SQL> show sga

Total System Global Area  314572800 bytes
Fixed Size                  1261564 bytes
Variable Size              92274692 bytes
Database Buffers          218103808 bytes
Redo Buffers                2932736 bytes

Tags:

How to extract Oracle user DDL

#database #oracle

In some cases, you need to duplicate an Oracle user, along with all it’s privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here’s how to do it: set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS_METADA... Read more

20 Feb 2018 - 1 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a hung database and must be quickly resolved as the database isn’t accessible for your users anymore. As you cannot connect to the hung database, yo... Read more

20 May 2017 - 1 minute read

Rename a Postgresql database

#database #postgresql

Here's the procedure to rename a PostgreSQL database: 1. Disconnect from the database that you want to rename and connect to a different database. 2. Check and terminate all active connections to the database that you want to rename. 3. Use the `ALTER DATABASE` statement to rename the database to the new one. Let’s take a look at an example of... Read more

20 May 2019 - 1 minute read

How to duplicate a Postgresql database

#database #postgresql

If you need to duplicate an existing Postgresql database, and possibly transfer ownership of the database objects to a new user, here's how to do that in a quick way: ```sql -- First, I recommand getting the size of the database to copy, as this might be important for the rest of the process. SELECT pg_database.datname,pg_size_pretty(pg_databas... Read more

20 Apr 2019 - less than 1 minute read