Tricks and tips for everyone


How do you change SGA and PGA size in Oracle 11g?

How do you change SGA and PGA size in Oracle 11g?

Connect to the database sysdba sqlplus sys/oracle@op as sysdba 2. Increase sga_max_size to 6 GB as below SQL> ALTER SYSTEM SET sga_max_size=6144m scope=spfile; System altered. 3. Increase pga_aggregate_targe to 3 GB as below SQL> ALTER SYSTEM SET pga_aggregate_target= 3072MB SCOPE=spfile; 4.

How does Oracle determine SGA and PGA size?

The Oracle DBA can use math to determine the optimal RAM allocation for a MS-Windows server….The total RAM SGA and PGA estimates for Oracle are as follows:

  1. OS RAM : 20 percent of total RAM for MS-Windows, 10% of RAM for UNIX.
  2. Oracle SGA RAM : determined with the show sga command.

Which initialization parameter is used to automatically size the SGA?

DB_nK_CACHE_SIZE initialization parameter
Fixed SGA and other internal allocations are sized using the DB_nK_CACHE_SIZE initialization parameter.

How do I increase my target memory?

Before you can increase memory in oracle database, you must first understand four oracle memory parameters which govern the instance. If you set MEMORY_TARGET, they oracle will perform AMM (automatic memory management) and both SGA + PGA are managed within the allocated memory.

How do I check my PGA memory usage?

Monitoring Automatic PGA Memory Management

  1. Using the V$PGASTAT View.
  2. Using the V$PROCESS View.
  3. Using the V$PROCESS_MEMORY View.
  5. Using the V$WORKAREA_ACTIVE View.
  6. Using the V$SQL_WORKAREA View.

How can I increase my SGA size?

Step by Step How to increase SGA size in Oracle

  1. 2:- Check the value of SGA.
  2. 1:- Check file location of spfile/pfile.
  3. 2:- Check the value of SGA.
  4. 3:-Take a backup of spfile before modification.
  5. 4:- Modify SGA values and reboot the database.

Related Posts