Increased memory for Oracle Database Memory tuning

Note: This article source: Small yan Kevin "Oracle Database Memory adjustment increased memory "

Simulated operating system memory increased from 2G to 8G, adjust the database memory parameters, the example parameter is not as the actual production environment reference, because the need to take, adjust the parameters need to adjust according to the database, to avoid the calf pull cart, Daniel pull the car phenomenon.

View RAW configuration Data

1[Email protected] ~]$ Sqlplus/as SYSDBA2 3Sql*plus:release 11.2.0.4.0 Production on Sat Jan 7 18:42:30 20174 5Copyright (c) 1982, Oracle. All rights reserved.6 7Connected to:8Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production9With the partitioning, Real application Clusters, Automatic Storage Management, OLAP,TenData Mining and Real application testing options One  ASql> Show parameter SGA; -  -NAME TYPE VALUE the ------------------------------------ ----------- ------------------------------ -LOCK_SGA Boolean FALSE -PRE_PAGE_SGA Boolean FALSE -Sga_max_size Big Integer 700M +Sga_target Big Integer 0 -Sql> Show Parameter PGA +  ANAME TYPE VALUE at ------------------------------------ ----------- ------------------------------ -Pga_aggregate_target Big Integer 0 -Sql> Show Parameter mem -  -NAME TYPE VALUE - ------------------------------------ ----------- ------------------------------ inHi_shared_memory_address integer 0 -Memory_max_target Big Integer 1G toMemory_target Big Integer 1G +Shared_memory_address integer 0 -Sql> Ho cat/etc/sysctl.conf | grep Shmmax the# Oracle-rdbms-server-11gr2-preinstall setting for Kernel.shmmax are 4398046511104 on x86_64 *# Oracle-rdbms-server-11gr2-preinstall setting for Kernel.shmmax are 4294967295 on i386 $Kernel.shmmax = 4398046511104Panax NotoginsengKernel.shmmax = 4294967295 -  theSql> select 4294967295/1024/1024 from dual; +  A4294967295/1024/1024 the -------------------- +4096 -  $Sql> select 8*1024*1024*1024 from dual; $  -8*1024*1024*1024 - ---------------- the8589934592 - WuyiSql> exit the[[Email protected] ~]$ exit -[[email protected] ~]#/oracle/app/grid/11.2.0.3/bin/crsctl stop CRS Wu[email protected] ~]# halt -Increase physical memory after shutdown, modify parameters after power-on About[[email protected] ~]# echo "Kernel.shmmax = 8589934592">>/etc/sysctl.conf $[Email protected] ~]# sysctl-p -[Email protected] ~]# DF-THP -Filesystem Type Size used Avail use% mounted on -/dev/sda5 ext4 87G 20G 63G 24%/ ATmpfs tmpfs 4.0G 0 4.0G 0%/dev/shm +/DEV/SDA1 ext4 504M 61M 418M 13%/boot the/dev/sda2 ext4 7.7G 146M 7.2G 2%/tmp -[[email protected] ~]# grep tmpfs/etc/fstab $TMPFS/DEV/SHM TMPFS Defaults 0 0 the[Email protected] ~]# Vim/etc/fstab the[[email protected] ~]# grep tmpfs/etc/fstab theTmpfs/dev/shm Tmpfs defaults,size=8192m 0 0 the[Email protected] ~]# Mount-o remount TMPFS -[Email protected] ~]# DF-THP inFilesystem Type Size used Avail use% mounted on the/dev/sda5 ext4 87G 20G 63G 24%/ theTmpfs tmpfs 8.0G 116M 7.9G 2%/DEV/SHM About/DEV/SDA1 ext4 504M 61M 418M 13%/boot the/dev/sda2 ext4 7.7G 146M 7.2G 2%/tmp theAdjustment theSql> select 7*1024 from dual; +  -7*1024 the ----------Bayi7168 the  theSql> select 8*0.56*1024 from dual; -  -8*0.56*4.48*1024 the ---------------- the4587.52 the  theSql> alter system set memory_max_target=7168m Scope=spfile; -  theSystem altered. the  theSql> alter system set memory_target=7168m Scope=spfile;94  theSystem altered. the  theSql> alter system set sga_max_size=4587m Scope=spfile;98  AboutSystem altered. - 101Sql> alter system set sga_target=4587m Scope=spfile;102 103System altered.104  thesql> shutdown Immediate106Database closed.107Database dismounted.108ORACLE instance shut down.109Sql> Startup theORACLE instance started.111  theTotal System Global area 4793552896 bytes113Fixed Size 2261328 bytes theVariable Size 1124077232 bytes theDatabase buffers 3657433088 bytes theRedo buffers 9781248 bytes117Database mounted.118Database opened.119Sql> Show Parameter SGA - 121NAME TYPE VALUE122 ------------------------------------ ----------- ------------------------------123LOCK_SGA Boolean FALSE124PRE_PAGE_SGA Boolean FALSE theSga_max_size Big Integer 4592M126Sga_target Big Integer 4592M127Sql> Show Parameter PGA - 129NAME TYPE VALUE the ------------------------------------ ----------- ------------------------------131Pga_aggregate_target Big Integer 0 theSql> Show Parameter mem133 134NAME TYPE VALUE135 ------------------------------------ ----------- ------------------------------136Hi_shared_memory_address integer 0137Memory_max_target Big Integer 7G138Memory_target Big Integer 7G139Shared_memory_address integer 0 $ 141Sql> exit

Increased memory for Oracle Database Memory tuning