How to Change the Default Oracle Database Memory Parameter to Improve Query Response Time ?

Asset ID:	1-71-2062990.1
Update Date:	2017-06-14
Keywords:

Solution Type Technical Instruction Sure

Solution 2062990.1 : How to Change the Default Oracle Database Memory Parameter to Improve Query Response Time ?

Applies to:

Oracle Communications Performance Intelligence Center (PIC) Software - Version 9.0.3 and later
Information in this document applies to any platform.

Goal

Oracle Database on Storage server is configured to use only 8GB of RAM whatever the hardware configuration of the server. As some servers are configured with 24GB (HP G6) or 32 GB (HP Gen8) we can allocate more memory to Oracle instance to boost the query response time.

Solution

If in doubt, open a Service Request.

Important notes:

Before proceeding set datawarehouse to maintenance mode in NSP CCM.
This procedure concerns only DWS servers installed with oracle database 11.2.0.x 64bits and having at least 24GB of ram.
If the server has 24GB of RAM the memory target will be set to 16GB, if the server has 32GB of RAM the memory target will be set to 24GB.
Temporary filesystem shmfs must be sized equal or larger than memory_target. If not, "ORA-00845: MEMORY_TARGET not supported on this system" will be raised during startup. To resolve ORA-00845: MEMORY_TARGET, look at 465048.1.

The example below is given for an HP G6 with 24GB of RAM, so memory target is set to 16GB.

Login to the DWS server as oracle user and login to sqlplus as sysdba:
sqlplus / as sysdba
Backup existing spfile to a pfile:
create pfile='/home/oracle/pfile_origin.ora' from spfile;
Change the database parameter with the following commands:
1. Unset default values (this will set them to automatic). Expected output can be "System altered" or "ORA-32010: cannot find entry to delete in SPFILE", both of which are acceptable:
  alter system reset sga_target scope=spfile;
  alter system reset sga_max_size scope=spfile;
  alter system reset pga_aggregate_target scope=spfile;
  alter system reset memory_max_target scope=spfile;
2. Set new value:
  alter system set memory_target=16G scope=spfile;
  alter system set processes=2400 scope=spfile;
  alter system set open_cursors=1000 scope=spfile;
3. Output example:
  SQL> alter system set memory_target=16G scope=spfile;
  
  System altered.
  
  SQL> alter system set processes=2400 scope=spfile;
  
  System altered.
  
  SQL> alter system set open_cursors=1000 scope=spfile;
  
  System altered.
  
  SQL>
Shutdown the oracle database:
1. Command:
  shutdown immediate
2. Output example:
  SQL> shutdown immediate
  Database closed.
  Database dismounted.
  ORACLE instance shut down.
  SQL>
Startup the oracle database:
SQL> startup
ORACLE instance started.

References

<NOTE:465048.1> - ORA-00845 Raised When Starting Instance

Attachments

This solution has no attachment