Statspack Events

enq: US - contention

When you see enq: US - contention on Top 5 wait events on AWR or Statspack report or in ADDM findings you can take the following actions :

1) Check undo advisor if in 10g anf up.
2) Edit your undo retension parameter
3) Give space to Undo Tablespace
4) Move Undo tablespace to an other disk not so hot disk.

Mostly this wait event concerns autoxents on undo tablespaces.

• db file sequential read and read by other session

Check buffer pool statistics and buffer wait (especiallyu data block wait)
A "db file sequential read" occurs when a an Oracle process looks for a block in the buffer cache and can’t find the block. The block is then read of disk, creating IO.

Db file sequential read wait time is increasing:
Check the average read times per file under FILE IO Stats
Check cache buffer hit ratio under "Buffer pool advisory"

Oracle Installation notes

• Create user and group:
•  groupadd -g 201 dba
• groupadd -g 200 oinstall
•  useradd -g oinstall -G dba -u 200 oracle
• mkdir /export/home/oracle
• chown -R oracle:oinstall /export/home/oracle 
• passwd oracle (setup password for oracle user) 

• Create ZFS pool to install Oracle (optional)

•  Find out which pools exist and which disks are used by those pools
• zpool status
•  Create a pool named oraclePool
• zpool create  oraclePool c0t5000C5001D250C7Fd0   c0t5000C5001D207157d0 
  

  zfs create oraclePool/11gR2

• Create directories and provide them right permissions:
• mkdir -p /u01/app/oracle
• mkdir -p /u01/app/11.2.0/grid
• mkdir -p /u01/app/oracle/11.2.0/db
• chown -R oracle:oinstall /u01
• chmod -R 775 /u01
• Oracle BASE : /u01/app/oracle
• GRID Base: /u01/app/11.2.0/grid
• DB BASE: /u01/app/oracle/11.2.0/grid

• Install the required packages

•  pkg install  SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWpool SUNWsprot SUNWtoo SUNWuiu8 SUNWfont-xorg-core SUNWfont-xorg-iso8859-1 SUNWmfrun SUNWxorg-client-programs SUNWxorg-clientlibs SUNWxwfsw pkg://solaris/SUNWxwplt
• 
  
• Set project attributes
• projmod -s -K 'process.max-file-descriptor=(basic,10000,deny)' oracleProject

Things to check

• 11.2.0.3 and later are supported on Solaris 11
•  
•  Set up AWT_TOOLKIT variable on Solaris 11

• if installing on Solaris 11, set the following variable so runInstaller can work
• export AWT_TOOLKIT=XToolkit

• To setup rsh/ rlogin/ssh between nodes on Solaris 11:

•  pkg  install service/network/legacy-remote-utilities
•  inetadm -e  svc:/network/login:rlogin
• svcadm enable /network/shell:default

• Clone ASM and DB installation

• Check out Appendix B of this document:

http://download.oracle.com/docs/cd/E11882_01/install.112/e24346/toc.htm

• If you want to do a slient install with response file check out appendix A.
•  Verify if you have all the required packages:
• 
  
• pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWpool SUNWpoolr SUNWsprot SUNWtoo SUNWuiu8 SUNWfont-xorg-core SUNWfont-xorg-iso8859-1 SUNWmfrun SUNWxorg-client-programs SUNWxorg-clientlibs SUNWxwfsw SUNWxwplt 
• Else install the packages
• pkg  install <list of space separated pkg names> 
• If required you can delete existing publisher and add new publisher
•  pkg set-publisher -G '*' -g  http://ipkg.us.oracle.com/solaris11/dev/ solaris
  # pkg publisher
  PUBLISHER                             TYPE     STATUS   URI
  solaris                               origin   online   http://ipkg.us.oracle.com/solaris11/dev/
   

Solaris Network monitoring

nicstat reports network utilization and saturation by interface.

The utilization measurement is based on the current throughput divided by maximum speed of the interface.The saturation measurement is a value that reflects errors due to saturation if kstat found any.

You can compile nicstat with gcc using the following options:
gcc nicstat.c -o nicstat -lkstat -lgen -lsocket -lrt

1 Gbit NIC deliver: 128 MBytes/sec
10GBit NIC delivers 1280 MBytes/sec
Consider the following nicstat ouput from a 1Gbs NIC which provides a max of 1000MB/8 =125MBytes/sec.

Int   rKB/s   wKB/s   rPk/s   wPk/s    rAvs    wAvs %Util    Sat

net0   14.31   14.20   18.40   16.40   796.2   886.8  0.01   0.00
net0 54421.9  1347.6 38495.8  7521.8  1447.6   183.5  44.6   0.00
net0  114501  3390.0 81297.2 24512.0  1442.2   141.6  93.8   0.00

For line 2, throughput is (54422+1348=55770KB/sec). So the util is 55770/125000 *100= 44%.
For line 3, throughut is (114501+81297).Hence util = 117891/125000*100 =94%


dladm is the admin utility for data link interfaces which helps to display information like the link status, speed,duplex and other vital statistics on each interface historically as well as on real time.

• How many physical NICS are up on the system

# dladm show-phys | grep up
net1              Ethernet             up         1000   full      igb1
net6              Ethernet             up         10     full      usbecm2
net0              Ethernet             up         1000   full      igb0

• What is the speed of the NIC

# dladm show-phys
LINK              MEDIA                STATE      SPEED  DUPLEX    DEVICE
net1              Ethernet             up         1000   full      igb1
net6              Ethernet             up         10     full      usbecm2
net4              Ethernet             down       0      unknown   ixgbe0
net3              Ethernet             unknown    0      unknown   igb3
net0              Ethernet             up         1000   full      igb0
net5              Ethernet             down       0      unknown   ixgbe1
net2              Ethernet             unknown    0      unknown   igb2
# dladm show-ether
LINK              PTYPE    STATE    AUTO  SPEED-DUPLEX                    PAUSE
net1              current  up       yes   1G-f                            bi
net6              current  up       no    10M-f                           none
net4              current  down     yes   0M                              bi
net3              current  unknown  yes   0M                              bi
net0              current  up       yes   1G-f                            bi
net5              current  down     yes   0M                              bi
net2              current  unknown  yes   0M                              bi

• How many links exist on each physical interface . And what is its MTU

# dladm show-link | grep up
net1                phys      1500   up       --
net6                phys      1500   up       --
net0                phys      1500   up       --
public1             vnic      1500   up       net0
public4             vnic      1500   up       net0

• Get the connection between link names, devices and locations , use dladm show-phys with -L option

root@p4826-01:~# dladm show-phys -L
LINK              DEVICE       LOC
net0              igb0         /SYS/MB
net1              igb1         /SYS/MB
net2              igb2         /SYS/MB
net3              igb3         /SYS/MB
net4              nxge0        /SYS/PM0
net5              nxge1        --
net6              nxge2        --
net7              nxge3        --
net8              nxge4        --
net9              nxge5        --
net10             nxge6        --
net11             nxge7        --
net12             usbecm2      --

• You can find the MAC address of the link using the following command:

root@p4826-01:~# dladm show-linkprop -p mac-address
LINK     PROPERTY        PERM VALUE        EFFECTIVE    DEFAULT   POSSIBLE
net5     mac-address     rw   0:21:28:93:b3:1 0:21:28:93:b3:1 0:21:28:93:b3:1 --
net10    mac-address     rw   0:21:28:93:b3:56 0:21:28:93:b3:56 0:21:28:93:b3:56 --
net3     mac-address     rw   0:21:28:fa:4a:93 0:21:28:fa:4a:93 0:21:28:fa:4a:93 --

• You can find the MAC address of the physical links on the system with the following command:

root@p4826-01:~# dladm show-phys -m | grep net2
net2                primary  0:21:28:fa:4a:92   no    --

• Basic receive side and transmit side statistics on all configured datalinks:

dlstat displays packets and byte sizes for receive and transmit by default.

 LINK    IPKTS   RBYTES    OPKTS   OBYTES
           net1    2.33K  139.62K    2.35K   98.77K
           net6        2      140    2.34K   98.21K
           net4        0        0        0        0
           net3        0        0        0        0
           net0    4.03G    1.35T    2.91G    7.75T
           net5        0        0        0        0
           net2        0        0        0        0
        public1  191.69K   18.74M        0        0
        public4  191.68K   18.74M        0        0

           net1        0        0        0        0
           net6        0        0        0        0
           net4        0        0        0        0
           net3        0        0        0        0
           net0  101.89K  120.01M   67.02K   73.86M
           net5        0        0        0        0
           net2        0        0        0        0
        public1        1       60        0        0
        public4        1       60        0        0

           net1        0        0        0        0
           net6        0        0        0        0
           net4        0        0        0        0
           net3        0        0        0        0
           net0  102.93K  118.61M   66.16K   77.21M

• To display historical data on each interface

Use -s option to show-link command


# dladm show-link -s
                ipackets  rbytes         ierrors opackets        obytes      oerrors
e1000g0         923206    815131608   0       297292    123324065   0
e1000g1         194550    58980153    0       20904     2093152     0
e1000g2         194804    25533658    0       2208      141594      0
e1000g3         165030    21211696    0       9824      2757277     0

• To display real time information on each interface,

  add -i <interval> option to -s option for show-link command


# dladm show-link -s -i 10
                ipackets  rbytes         ierrors opackets        obytes      oerrors
e1000g0         923273    815138604   0       297327    123328173   0
                ipackets  rbytes         ierrors opackets        obytes      oerrors
e1000g0         6         384         0       2         332         0
                ipackets  rbytes         ierrors opackets        obytes      oerrors
e1000g0         5         1380        0       2         332         0
^C# dladm show-dev -s -i 10
                ipackets  rbytes         ierrors opackets        obytes      oerrors
e1000g0         923320    815144418   0       297359    123332029   0
                ipackets  rbytes         ierrors opackets        obytes      oerrors
e1000g0         6         666         0       2         332         0
^C#

• Additonal info

http://www.softpanorama.org/Net/Solaris/network_configuration.shtml#Marking_an_Ethernet_Interface_as_Down
http://www.thezonemanager.com/2010/03/measuring-network-throughput-via.html

List hidden paramters in Oracle Database

You can get the hidden parameter values by querying x$ksppcv and x$ksppi as follows:

• select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where a.indx = b.indx and a.ksppinm=’_smm_max_size’;
• select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where a.indx = b.indx and a.ksppinm=’_pga_max_size’;

Create psrset

Here is how you can create a processor set with two cores (32 threads)

• psrset -c 0-31

if you get :

• An  "Operation not supported" error indicates that pool facility is active.

Disable the pool facility as under:

• svcs *pool*
• STATE          STIME    FMRI
• disabled       Feb_17   svc:/system/pools/dynamic:default
• online         Feb_17   svc:/system/pools:default
• you have mail
• # svcadm disable svc:/system/pools:default
• # svcs *pool*
• STATE          STIME    FMRI
• disabled       Feb_17   svc:/system/pools/dynamic:default
• disabled       11:44:04 svc:/system/pools:default

Create psrset with first 32 processors and verify that the processor set was successfully created

psrset 0-31

 psrset -i

user processor set 1: processors 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

The output indicates that process set named 1 has processors from id 0 to 31.