Home » RDBMS Server » Server Administration » How can I safely limit the amount of memory used by Oracle 12c? (12c on Windows)
How can I safely limit the amount of memory used by Oracle 12c? [message #682521] Tue, 27 October 2020 11:05 Go to next message
Darth Waiter
Messages: 78
Registered: October 2020
Member
My development machine only has 8 GB or RAM. Since I only run a handful of very simple queries, there is probably no need for Oracle to use much ram.
Oracle 12c, upon startup, takes 2.8 GB. With all of the IDE, SqlDeveloper, SquirrelSQL, browser, and whatnot, I am almost running out of available RAM.
I read Oracle's document on memory management and realized that it is too much for me, not being a skilled Oracle DBA, to try and figure out all available settings.
Are there some simple steps that I could take to limit Oracle's the memory footprint to less than 1 GB?

Thank you!
Al

Report message to a moderator

Re: How can I safely limit the amount of memory used by Oracle 12c? [message #682526 is a reply to message #682521] Tue, 27 October 2020 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Always post your exact version: query v$version.
Memory taken by Oracle at startup are determined by some instances mostly those you can see with:
show parameter memory
show parameter size
(Not all these parameters determined the allocated memory, other parameters determined some internal tables sizes, like processes... but they are small compared to the SGA)

See Oracle Database Architecture in Oracle Database Concepts book.

Report message to a moderator

Re: How can I safely limit the amount of memory used by Oracle 12c? [message #682529 is a reply to message #682526] Tue, 27 October 2020 11:51 Go to previous messageGo to next message
Darth Waiter
Messages: 78
Registered: October 2020
Member
 
SQL> select * from v$version;
BANNER                                                                              CON_ID
------------------------------------------------------------------------------------------
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production               0
PL/SQL Release 12.2.0.1.0 - Production
         0
CORE    12.2.0.1.0      Production
         0

BANNER                                                                              CON_ID
------------------------------------------------------------------------------------------
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production                                  0
NLSRTL Version 12.2.0.1.0 - Production
         0
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
inmemory_adg_enabled                 boolean     TRUE
inmemory_clause_default              string
inmemory_expressions_usage           string      ENABLE
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     0
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0
inmemory_trickle_repopulate_servers_ integer     1
percent
inmemory_virtual_columns             string      MANUAL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0
optimizer_inmemory_aware             boolean     TRUE
shared_memory_address                integer     0
SQL> show parameter size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size               integer     1048576
client_result_cache_size             big integer 0
create_bitmap_area_size              integer     8388608
data_transfer_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_block_size                        integer     8192
db_cache_size                        big integer 0

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recovery_file_dest_size           big integer 0
db_recycle_cache_size                big integer 0
dnfs_batch_size                      integer     4096
hash_area_size                       integer     131072
inmemory_size                        big integer 0
java_max_sessionspace_size           integer     0
java_pool_size                       big integer 0
large_pool_size                      big integer 0
max_dump_file_size                   string      unlimited

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     10240000
olap_page_pool_size                  big integer 0
parallel_execution_message_size      integer     16384
result_cache_max_size                big integer 0
sga_max_size                         big integer 2432M
sga_min_size                         big integer 0
shared_pool_reserved_size            big integer 24M
shared_pool_size                     big integer 0
sort_area_retained_size              integer     0

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536
streams_pool_size                    big integer 0
unified_audit_sga_queue_size         integer     1048576
workarea_size_policy                 string      AUTO
SQL>
These are top processes on this machine, by memory size:

https://i.imgur.com/0UTf3kgl.png

[Updated on: Tue, 27 October 2020 11:57]

Report message to a moderator

Re: How can I safely limit the amount of memory used by Oracle 12c? [message #682531 is a reply to message #682529] Tue, 27 October 2020 12:14 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
If you give it this,
alter system set sga_target=1g scope=spfile;
alter system set pga_aggegate_target=256m scope=spfile;
startup force;
you should find that the memory usage halves.

Report message to a moderator

Re: How can I safely limit the amount of memory used by Oracle 12c? [message #682532 is a reply to message #682531] Tue, 27 October 2020 12:20 Go to previous message
Darth Waiter
Messages: 78
Registered: October 2020
Member
John,

This did it:
SQL> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8927144 bytes
Variable Size             373295192 bytes
Database Buffers          683671552 bytes
Redo Buffers                7847936 bytes
Database mounted.
Database opened.
SQL>
Thanks a whole lot!
Just for posterity, if you could edit your post for a tiny typo and insert 'r' into 'aggegate' it would run without errors.

Regards
Al

Report message to a moderator

Previous Topic: Oracle Database Cross Platform Migration from 9i to 11g
Next Topic: Did I set up UTL_MAIL correctly and completely?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 15 19:17:33 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.