oracle資料庫記憶體調整之增加記憶體

標籤：copyright   參考   .com   database   data   kernel   media   reg   phrase   

註：本文來源：小顏Kevin   《oracle資料庫記憶體調整之增加記憶體》

類比作業系統記憶體從2G增加為8G後，調整資料庫記憶體參數，樣本中參數不作為實際生產環境參考，因為因需所取，調整參數需要根據資料庫相應調整，避免小牛拉大車，大牛拉小車的現象。

查看原始配置資料

  1 [[email protected] ~]$ sqlplus / as sysdba  2   3 SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 7 18:42:30 2017  4   5 Copyright (c) 1982, 2013, Oracle.  All rights reserved.  6   7 Connected to:  8 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  9 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 10 Data Mining and Real Application Testing options 11  12 SQL> show parameter sga; 13  14 NAME                                 TYPE        VALUE 15 ------------------------------------ ----------- ------------------------------ 16 lock_sga                             boolean     FALSE 17 pre_page_sga                         boolean     FALSE 18 sga_max_size                         big integer 700M 19 sga_target                           big integer 0 20 SQL> show parameter pga 21  22 NAME                                 TYPE        VALUE 23 ------------------------------------ ----------- ------------------------------ 24 pga_aggregate_target                 big integer 0 25 SQL> show parameter mem 26  27 NAME                                 TYPE        VALUE 28 ------------------------------------ ----------- ------------------------------ 29 hi_shared_memory_address             integer     0 30 memory_max_target                    big integer 1G 31 memory_target                        big integer 1G 32 shared_memory_address                integer     0 33 SQL> ho cat /etc/sysctl.conf | grep shmmax 34 # oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4398046511104 on x86_64 35 # oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4294967295 on i386 36 kernel.shmmax = 4398046511104 37 kernel.shmmax = 4294967295 38  39 SQL> select 4294967295/1024/1024 from dual; 40  41 4294967295/1024/1024 42 -------------------- 43                 4096 44  45 SQL> select 8*1024*1024*1024 from dual; 46  47 8*1024*1024*1024 48 ---------------- 49       8589934592 50  51 SQL> exit 52 [[email protected] ~]$ exit 53 [[email protected] ~]# /oracle/app/grid/11.2.0.3/bin/crsctl stop crs 54 [[email protected] ~]# halt 55 關機後增加實體記憶體，開機後修改參數 56 [[email protected] ~]# echo "kernel.shmmax = 8589934592" >> /etc/sysctl.conf 57 [[email protected] ~]# sysctl -p 58 [[email protected] ~]# df -ThP 59 Filesystem    Type    Size  Used Avail Use% Mounted on 60 /dev/sda5     ext4     87G   20G   63G  24% / 61 tmpfs        tmpfs    4.0G     0  4.0G   0% /dev/shm 62 /dev/sda1     ext4    504M   61M  418M  13% /boot 63 /dev/sda2     ext4    7.7G  146M  7.2G   2% /tmp 64 [[email protected] ~]# grep tmpfs /etc/fstab 65 tmpfs                   /dev/shm                tmpfs   defaults        0 0 66 [[email protected] ~]# vim /etc/fstab 67 [[email protected] ~]# grep tmpfs /etc/fstab 68 tmpfs                   /dev/shm                tmpfs   defaults,size=8192m        0 0 69 [[email protected] ~]# mount -o remount tmpfs 70 [[email protected] ~]# df -ThP 71 Filesystem    Type    Size  Used Avail Use% Mounted on 72 /dev/sda5     ext4     87G   20G   63G  24% / 73 tmpfs        tmpfs    8.0G  116M  7.9G   2% /dev/shm 74 /dev/sda1     ext4    504M   61M  418M  13% /boot 75 /dev/sda2     ext4    7.7G  146M  7.2G   2% /tmp 76 調整 77 SQL> select 7*1024 from dual; 78  79     7*1024 80 ---------- 81       7168 82  83 SQL> select 8*0.56*1024 from dual; 84  85 8*0.56*4.48*1024 86 ---------------- 87          4587.52 88  89 SQL> alter system set memory_max_target=7168M scope=spfile; 90  91 System altered. 92  93 SQL> alter system set memory_target=7168M scope=spfile; 94  95 System altered. 96  97 SQL> alter system set sga_max_size=4587M scope=spfile; 98  99 System altered.100 101 SQL> alter system set sga_target=4587M scope=spfile;102 103 System altered.104 105 SQL> shutdown immediate106 Database closed.107 Database dismounted.108 ORACLE instance shut down.109 SQL> startup110 ORACLE instance started.111 112 Total System Global Area    4793552896 bytes113 Fixed Size                     2261328 bytes114 Variable Size               1124077232 bytes115 Database Buffers            3657433088 bytes116 Redo Buffers                   9781248 bytes117 Database mounted.118 Database opened.119 SQL> show parameter sga120 121 NAME                                 TYPE        VALUE122 ------------------------------------ ----------- ------------------------------123 lock_sga                             boolean     FALSE124 pre_page_sga                         boolean     FALSE125 sga_max_size                         big integer 4592M126 sga_target                           big integer 4592M127 SQL> show parameter pga128 129 NAME                                 TYPE        VALUE130 ------------------------------------ ----------- ------------------------------131 pga_aggregate_target                 big integer 0132 SQL> show parameter mem133 134 NAME                                 TYPE        VALUE135 ------------------------------------ ----------- ------------------------------136 hi_shared_memory_address             integer     0137 memory_max_target                    big integer 7G138 memory_target                        big integer 7G139 shared_memory_address                integer     0140 141 SQL> exit

oracle資料庫記憶體調整之增加記憶體