Got a Large SGA?, USE HugePages

3 Key Reasons:

  1. Oracle "Strongly recommends always deploying HugePages with Oracle Databases"

  2. Reduces memory footprint by a factor of 500

  3. 4K vs. 2MB memory pages.  More pages can be mapped in memory with less overhead

Great video from OpenWorld DemoGrounds


HOW TO

Do steps 1-4 on EACH NODE


1. Set Oracle User memlock Limits
---------------------------------
NOTE: Value should be 90% of total memory
      <value> is in kb (ex. 200 gb = 209715200 kb)

As Root 
vi /etc/security/limits.conf

oracle soft memlock <value>
oracle hard memlock <value>


2. Set kernel.shmmax
--------------------
NOTE: Value should be comfortably larger than the largest SGA

As Root
get existing value:
 cat /proc/sys/kernel/shmmax
set new value:
 vi /etc/sysctl.conf
 AND
 echo <value in bytes> > /proc/sys/kernel/shmmax
 AND
 sysctl –p

verify new value
 cat /proc/sys/kernel/shmmax


3. Set kernel.shmall
--------------------
NOTE: Value should be sum of SGA's / pagesize 

Get pagesize
 getconf PAGESIZE
Determine sum of SGAs
 ipcs -m |grep oracle, sum of 5th column values + future SGAs

example: 
pagesize= 4,096
SGA's   = 92gb + 58gb growth = 161061273600 bytes
SGA's / pagesize = 39,321,600
kernel.shmall should be 39321600

get existing value:
 cat /proc/sys/kernel/shmall
set new value:
 vi /etc/sysctl.conf
 AND
 echo <value> > /proc/sys/kernel/shmall 
 AND
 sysctl -p
verify new value
 cat /proc/sys/kernel/shmall


4. Set vm.nr_hugepages
----------------------
NOTE: Use recommended value returned from hugepages_settings.sh
get the script here

As Root, add the following to /etc/sysctl.conf

 # Hugepages
 # Allow oracle user access to hugepages
 vm.hugetlb_shm_group = 310
 vm.nr_hugepages = <value>

AND run

sysctl -p

verify setting:
 cat /proc/sys/vm/nr_hugepages


5. Set use_large_pages=ONLY in spfile (11g only)
------------------------------------------------
 For Each DATABASE
  alter system set use_large_pages=ONLY scope=spfile;


6. Restart Database(s)
----------------------
 Verify huge pages in use (alert log)
 Run ipcs -m (should only have a couple segments per database)

Session PGA Memory Usage

A helpful script to find sessions using large amounts of system memory:

SET LINESIZE 140
SET PAGESIZE 100
COL session      HEADING 'SID - User - Client' FORMAT a35
COL current_size HEADING 'Current MB' FORMAT '999,999.99'
COL maximum_size HEADING 'Max MB'     FORMAT '999,999.99'
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size ON REPORT
SELECT TO_CHAR(ssn.sid, '9999') || ' - ' || 
       NVL(ssn.username, NVL(bgp.name, 'background')) || ' - ' ||
       NVL(lower(ssn.machine), ins.host_name) "SESSION",
       TO_CHAR(prc.spid, '999999999') "PID/THREAD",
       se1.value/1024/1024 current_size,
       se2.value/1024/1024 maximum_size
 FROM  v$sesstat se1, 
       v$sesstat se2, 
       v$session ssn, 
       v$bgprocess bgp, 
       v$process prc,
       v$instance ins,  
       v$statname stat1, 
       v$statname stat2
 WHERE se1.statistic# = stat1.statistic# 
   AND stat1.name = 'session pga memory'
   AND se2.statistic# = stat2.statistic# 
   AND stat2.name = 'session pga memory max'
   AND se1.sid = ssn.sid
   AND se2.sid = ssn.sid
   AND ssn.paddr = bgp.paddr (+)
   AND ssn.paddr = prc.addr  (+)
-- AND NVL(ssn.username, NVL(bgp.name, 'background')) = '<SCHEMA>'
ORDER BY 4
/
!free
exit
/

Sample output:
SID - User - Client                 PID/THREAD  Current MB      Max MB
----------------------------------- ---------- ----------- -----------
cut...
  867 - XXXX - server01                   9615        3.47      223.60
  729 - XXXX - server01                   9611        3.47      223.78
  469 - XXXX - server01                   3479        3.16      223.85
  605 - XXXX - server03                   4007       10.97    1,373.78
 1039 - XXXX - server01                  21216       11.16    1,373.97
  448 - XXXX - server02                   3852       10.22    1,378.91
                                               -----------
Total                                               980.91

             total       used       free     shared    buffers     cached
Mem:     132085672  125739556    6346116          0     836680   24065240
-/+ buffers/cache:  100837636   31248036
Swap:      8193140       9968    8183172

Tablespace usage over time

If you have OEM, this repository query may be very helpful:

SELECT KEY_VALUE Tablespace_Name,
       ROLLUP_TIMESTAMP Sample_date,
       METRIC_COLUMN metric,
       AVERAGE
  FROM sysman.MGMT$METRIC_DAILY
 WHERE metric_name='tbspAllocation'
   and TARGET_NAME='<dbname>' 
   and KEY_VALUE = '<tablespacename>'
 ORDER BY 1,2,3;

Auto-Start Standby Database

1. Start Standalone Grid Infrastructure - High Availability Services (HAS)

host > $CRS_HOME/bin/crsctl config has

CRS-4621: Oracle High Availability Services autostart is disabled.

host > $CRS_HOME/bin/crsctl enable has

CRS-4622: Oracle High Availability Services autostart is enabled.

2. Change auto_start to “always” and the default startup mode to “mount”

srvctl modify database -d stndbydb -s mount

crsctl modify resource ora.stndbydb.db –attr “AUTO_START=always”

Test with crs_stat -p

How to Trace SQL in the Current Session

Trace SQL in the current session:

alter session set timed_statistics=true;

alter session set max_dump_file_size=unlimited;

alter session set tracefile_identifier='20Jul2012_special_trace_1';​

alter session set events '10046 TRACE NAME CONTEXT FOREVER,level 12';

..Run some things..

select 'close cursor' from dual; --to dump the rowsource information
alter session set events '10046 trace name context off';

Trace file will be in diag/admin/db_name/instance_name/trace

ASM Debugging Info

ASM Debugging Information

If you have an ASM issue, you'll want these commands handy.

cat /etc/*release

uname -a

rpm -qa|grep oracleasm

/usr/sbin/oracleasm configure

/sbin/modinfo oracleasm

/etc/init.d/oracleasm status​

/usr/sbin/oracleasm-discover

oracleasm scandisks

oracleasm listdisks

ls -l /dev/oracleasm/disks

/sbin/blkid

ls -l /dev/mpath/*

ls -l /dev/mapper/*

ls -l /dev/dm-*

another approach...

1. uname -a
2. rpm -qa | grep oracleasm
3 cat /etc/sysconfig/oracleasm
4. upload /var/log/oracleasm
5. cat /proc/partitions
6. ls -la /dev/oracleasm/disks
7. /etc/init.d/oracleasm scandisks
8. /etc/init.d/oracleasm listdisks
9. Run "sosreport" from command line. That will generate a bzip file. Attach it to SR
10. Send me following command outputs
a. cat /proc/partitions |grep sd|while read a b c d;do echo -n $d$'\t'" scsi_id=";(echo $d|tr -d [:digit:]|xargs -i scsi_id -g -s /block/{})done
b. blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;
11. Also you can verify the disk has correct header as follows:
# dd if=/dev/path/to/disk bs=16 skip=2 count=1 | hexdump -C
example:
# dd if=/dev/mapper/data0p1 bs=16 skip=2 count=1 | hexdump -C
1+0 records in
1+0 records out
16 bytes (16 B) copied, 0.037821 seconds, 0.4 kB/s
00000000 4f 52 43 4c 44 49 53 4b 44 41 54 41 30 00 00 00 |ORCLDISKDATA0...|

Reference note: Troubleshooting a multi-node ASMLib installation (Doc ID 811457.1)

RAC - Relocating VIP and SCAN

Failover VIP (on the destination node)
./crs_relocate [vip resource name]

The VIP will now go where it's configured to be

Failover SCAN
srvctl relocate scan -i [LISTENER_NUMBER] -n [DESTINATION_NODE_NAME]