Oracle Performance Monitoring Basic

                                Oracle Performance Monitoring  Basic 


Lately few people have been requesting me to share something on Tuning Concepts .  Basically we might know 100 of  things but we must smartly know when to use them . Though i am still to explore more.. but it will surely help mid level dba's


I always prefer to OEM for everything .. It can be used for lot things we even think of .

If we talk about tuning . we can use OEM for  checking concurrency ,  generating reports ,  checking fragmentation , ruing segment advisory, checking session details and its wait  , historical performance etc.

Also its just lat year i  realized the importance of using past performance .

If i have time i will share some of my important sql to share how to check, but in this topic let me share what needs to be checked and what should be your  target areas


Its very crucial for us have all our scripts really handy . Have seen many Dba dying to search proper
script to monitor the very last moment .
Here are area where one should looks for as first steps. It depends whether you are  targeting to
diagnose one session or whole database  performance  :

-- wait event , blocking , multiple plan hash value, check alert logs, check for recent generated  trace files in     dump area, last analyzed , stale stats ,
    fragmentation of table and index ,  check sql plan for fts   ,  check if table was initially analyzed that caused    performance change ,   ofcource from O/S check  resource utilization , O/S error logs, V$resource_limit ,
  check if any backups are running hampering performance ,  check if any smon recovery / rollback is       happening  ,  check for hit ratios, check if session i/o is changing , session last et call ,  check for parallel
operations and long opps ,  check database temp and undo utilization - check highest consumer ,
check archive generation rate ,  ask application guys if any changes went in ,  check listener log ,
check defunct process  on o/s ,  check sqlid  historical excution : many time application guys just fool with you calming sql was running fine ,but it never did.. so check for historical sql performance using dba_hist_sqlstat  , try generating session trace

-- check for database report...  there are lot of reports which 90% dba  dont even know of ..
 ideally people just check awr report... but i prefer  ash report.. specially ashrpti.sql  .. to concentrate  on     one session or sqlid .  some of reports that can help you are,,  ash , ashrpti ,,  awr  sql report, awr compare report, addm ,   awr/statspack ,  sqldiag report-- check on metalink ,



When it comes for recommendation and fixes 

-- if case of multiple plan:  try creating profiles and baselines ,  gathering stats , creating missing index ,
sga recommendation based on hit ratios,   flushing shared pool, purging sqlid ,   restoring old stats -- from 10g ,  try invalidating cursor ,

 In case of Rac : 

For checks : 

-- check  resource status , check cluster alert log, deamon logs ,  ocr and voting disk status ,  service status ,  check deamons status ,  check asm logs , o/s logs , 

è Logs to monitor :

1) CRS HOME :

   Ÿ  CRS ALERT LOG :  log/<hostname>/alert<nodename>.log

   Ÿ  CRS logs :  log/<hostname>/crsd/  --- > archived every 10mb

   Ÿ  CSS logs : log/<hostname>/cssd/  -- > archived every 20mb

   Ÿ  EVM logs : log/<hostname>/evmd+evm/log

   Ÿ  OPMN logs – opmn/logs

   Ÿ  resource specific logs – log/<hostname>/racg/

   Ÿ  cluster communication logs :  log

2) ORACLE_home :

  Ÿ  resource specific logs :  log/<hostname>/racg

  Ÿ  srvm logs --  log/<hostname>/client

  Ÿ  alert and other trace files : bdump/cdump/udump

  Ÿ  AWR/ statspack/ ash/ addm  of each node

  Ÿ  listener logs .

3)  ASM_logs : 

  Ÿ   alert logs and other trace files  : oracle_home/rdbms/log/ bdump/udump/cdump

4)   /etc/oracle/oprocd/  (node eviction)

*** Oprocd log locations:

In /etc/oracle/oprocd or /var/opt/oracle/oprocd depending on version/platform.

Note that oprocd only runs when no vendor clusterware is running or on Linux > 10.2.0.4

è enable tracing :

1) SRVM tracing : We enable srvm tracing by giving “ export SRVM_TRACE=TRUE  ” .  By doing so we enable tracing for srvctl,cvu ,VIPCA and gsdctl .

srvctl config srvpool

2) Debugging the resource as root user :

crsctl debug log module_name component:debugging_level

 module_name = crs , css , evm

level = 1 to 5

component :  “crsctl lsmodules module_name”

3) Resource debugging : crsctl debug log res  "ora.node1.vip:1" 

4) ./crsctl get log css "all"

./crsctl set log css CSSD:4

5) To change amount  of  ocr logging, edit the file CRS_Home/srvm/admin/ocrlog.ini.

è using cluvfy to monitor

cluvfy -h

cluvfy stage -pre crsinst -n node_list

cluvfy stage -post crsinst -n node_list [-verbose]

cluvfy comp clu

cluvfy comp scan -verbose

Cluvfy comp crs –n all –verbose

cluvfy comp ocr –n all            (to verify ocr )

cluvfy comp ocr -n all -verbose

setenv MYNODES node1,node3,node5  ||  cluvfy comp nodecon -n $MYNODES [-verbose]  || 

cluvfy comp nodecon -n node1,node2,node3 –i eth0 -verbose

cluvfy comp nodecon -n all -verbose

cluvfy comp nodecon -n rac1

STORAGE ::

cluvfy comp ssa  -n all -verbose    ( to confirm if storage is shared)

cluvfy comp ssa  -n all -s /dev/sda

cluvfy comp space  -n all -l  /home/dbadmin/products  –z 2G -verbose    (to confirm free space . does not support block or raw device)

cluvfy comp cfs -f /oradbshare –n all -verbose    ( check intigrity of cluster filesystem )

è  Commands useful in day to day  :

.  ./setSID_k +asm1

crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-45s%-15s%-10s%-30s\n", n,t,g,s)}'

Ÿ crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-45s%-15s%-10s%-30s\n", n,t,g,s)}'

Ÿ crs_stat -t –v

Ÿ . $CRS_HOME/bin/crsstat

Ÿ ./crs_stat –t                      (status of all resources ) ( as oracle user)

Ÿ . $CRS_HOME/bin/crsctl check crs   ( to deamon  status ) ( as oracle user)

Ÿ ps –ef | grep d.bin

Ÿ  ./crsctl stop crs                  ( to stop crs ) (with root user )

Ÿ  ./crsctl start  crs                ( to start  crs) ( with root user )

Ÿ crsctl start resource –all

Ÿ crsctl stop has

Ÿ crsctl start has

Ÿ crsctl stop crs –f

Ÿ crsctl enable  crs  -->  enable crs  on next startup

Ÿ crsctl disable crs 

It will disable crs from coming up after reboot (These functions update the file /etc/oracle/scls_scr/node_name/root/crsstart  that           contain the string enable or disable)

Ÿ crsctl query crs softwareversion

Ÿ crsctl query crs activeversion

Ÿ  crsctl check daemon .              (see infor of each deamon )

Ÿ  crsctl stop  cluster -all

Ÿ  crsctl check cluster –all

Ÿ crs_start ora.q-faykan.ons

Ÿ crsctl stop resource ora.QGISC.INS_WFC.QGISC2.srv       

Ÿ crsctl stop resource ora.QGISC.QGISC_INS_FGEA.QGISC2.srv

Ÿ crsctl stop resource ora.QGISC.QGISC_INS_WFC.QGISC2.srv 

Ÿ crsctl stop resource ora.QRDBC.DBR_SRVC.QRDBC2.srv      

Ÿ crsctl start resource ora.QGISC.INS_WFC.QGISC2.srv       

Ÿ crsctl start resource ora.QGISC.QGISC_INS_FGEA.QGISC2.srv

Ÿ crsctl start resource ora.QGISC.QGISC_INS_WFC.QGISC2.srv 

Ÿ crsctl start resource ora.QRDBC.DBR_SRVC.QRDBC2.srv      

Ÿ  export ORACLE_SID=orcl1

Ÿ ons debug                                   (ons ip debuging)

Ÿ olsnodes -s -t  / olsnodes -n      ( node information )

Ÿ  /runcluvfy.sh stage -post crsinst -n node1,node2 -verbose

Ÿ $ date; ssh q-feyd "date";ssh q-faykan "date"

Ÿ ps –fu oracle     -->  to check resource started by oracle

Ÿ srvctl getenv database -d orcl

Ÿ srvctl getenv instance -d orcl -i orcl1

Ÿ Crs_getper

Ÿ Crs_setperm

Ÿ  crs_profile –print                 ( to see crs profile .)

Ÿ  crsctl start res -all

Ÿ  crsctl stop resource ora.QRDBC.DBR_SRVC.QRDBC2.srv    

Ÿ  crsctl stat res -t -init

Ÿ  crsctl start res ora.evmd -init

Ÿ  crsctl stop res ora.evmd -init -f

Ÿ  crsctl modify resource "ora.cssd" -attr "AUTO_START=1"

Ÿ crsctl delete serverpool msinqprd -f

+QUEUE_IN_DATA/msinqprd/spfilemsinqprd.ora -n msinqprd -a QUEUE_IN_DATA,QUEUE_IN_FLASHBACK

  crsctl (check / start / stop ) cluster –all  -- 11g

Ÿ srvctl config network

Ÿ srvctl config nodeapps

Ÿ srvctl modify database –dbname –y automatic/manual  (automatic start/stop resource )

Ÿ srvctl status database -d wwprd

Ÿ srvctl status nodeapps -n wwprod3

Ÿ srvctl status asm –n node_name

Ÿ srvctl start nodeapps -n green -4  ( starts  ons, listener , gsd, vip  )

Ÿ srvctl start asm -n green –i asm1

Ÿ srvctl start instance –d orcl –i orcl1

Ÿ srvctl remove database -d databasename ( -f)

Ÿ srvctl start database -d dctmdb

Ÿ srvctl start listener -n node_name

Ÿ srvctl stop database -d dctmdb  -o immediate

Ÿ srvctl start database -d dctmdb –i dctmdb1 –o mount

Ÿ srvctl stop instance –d orcl –i orcl1

Ÿ srvctl getenv database -d PBOKCC

Ÿ srvctl setenv database -d PBOKCC -t

Ÿ srvctl stop  asm -n green –i asm1 –o immediate

Ÿ srvctl stop nodeapps -n green -4

Ÿ srvctl remove instance -d DBM -i DBM8

Ÿ srvctl remove database -d DBM

srvctl add database -d msinqprd -o /oracle/product/11.2.0/db -p

srvctl add database -d BOSTON –o /opt/oracle/product/10g_db_rac

srvctl add instance -d BOSTON -i BOSTON1 -n boston_host1

srvctl add instance -d BOSTON -i BOSTON2 -n boston_host2

srvctl add database -d tststby -o

srvctl add instance -d tststby -i tststby2 -n raclinux2

srvctl add instance -d tststby -i tststby1 -n raclinux1

srvctl add service -d tststby -s tststby_s -r tststby2 -a tststby1

srvctl modify database -d tststby -p ’+DGDUP/spfiletststby.ora’

srvctl config database -d tststby

srvctl start instance -d orcl -i orcl1

srvctl status database –d ITDBSS

srvctl status service –d ITDBSS

srvctl status listener

srvctl status scan_listener

srvctl status scan

srvctl status nodeapps

srvctl remove/enable/disable/ asm -n node_name -i instance_name

srvctl start asm -n node_nmae -i instance_name

srvctl stop diskgroup -g DATA

srvctl [*] diskgroup -n'

srvctl add database -d JIRAP2_sldn0833por -n JIRAP2 -o /app/ora/local/product/11.2.0.2/db_1

srvctl disable database -d JIRAP2_sldn0833por

srvctl add listener -l LISTENER_JIRAP2 -o /app/ora/local/product/11.2.0.2/db_1 -p TCP:1552 -s

srvctl disable listener -l LISTENER_JIRAP2