Thursday, March 29, 2012

How to see environmental variables of a running process

You can use unix ps command to see environmental variables of a running process. For example, for oracle asm pmon process:

$ps -ef|grep pmon

oracle XXXXXX  YYYYYY   0  hh:mm:ss       pts/2  0:xx grep pmon
oracle ZZZZZZZ   VVVVVV   0   MMM DD      -    0:xx asm_pmon_+ASM2

$ps eauwww ZZZZZZZ

USER          PID %CPU %MEM   SZ  RSS    TTY STAT    STIME  TIME COMMAND
oracle   .... asm_pmon_+ASM2 _=/..../oraagent.bin MANPATH=.... SUDO_GID=... LANG=.... LOGIN=... SUDO_UID=.... ORACLE_HOME=.... LIBPATH=... LD_LIBRARY_PATH=...

Wednesday, March 28, 2012

Finding Literal Sqls

a better way to find literal sqls in oracle 10g

Finding literal sqls, example for ORACLE 10g & 11g

SELECT force_matching_signature, COUNT(1)
FROM v$sql
WHERE force_matching_signature > 0
  AND force_matching_signature <> exact_matching_signature
GROUP BY force_matching_signature
HAVING COUNT(1) > 10
ORDER BY 2