More About ORACLE_AFFINITY ORACLE_PRIORITY Windows Process Scheduling

Using ORACLE_AFFINITY

Under certain conditions, it might be desirable to reserve a CPU for a particular thread or set of threads. Good candidates are usually threads that either are single threads and whose performance degrades with higher load or who affect performance adversely on all CPU’s on the system.

 

When running RAC, the CPU utilization of LMS processes increases at a faster pace than the number of lock and CR and current blocks requests. The throughput and scalability of LMS are crucial to the performance of RAC, since current and CR server, as well as lock request latencies are directly correlated with how fast and efficient LMS can process the requests. Tests on Solaris in Oracle8i have shown, that binding LMD0 to a separate CPU, will give nearly optimal performance results. On NT, the LMS threads can also be bound to a dedicated processor.

 

Example:

 

HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->

 

  • ORACLE_AFFINITY=”LMS0:8;LMS1:8;DEF:7;USER:7”

On a 4-CPU machine, this would indicate that the LMS threads run on CPU 3, and all other Oracle threads on CPU’s 0, 1 and 2.

 

  • ORACLE_AFFINITY=”LMS0:1;DEF:14”

LMS0 runs on CPU 0, all other threads on CPU’s 1, 2 and 3.

 

Using ORACLE_PRIORITY

 

Similarly to ORACLE_AFFINITY, the priority of an Oracle thread can be modified. There exist six different thread priorities:

 

  • lowest
  • below_normal
  • normal
  • above_normal
  • highest
  • time_critical

 

Since threads can have their priorities dynamically adjusted by the system within a process in the idle, normal or high priority class, using ORACLE_PRIORITY may not have a big impact. Also, when CPU resources are already limited, having a single thread running with increased priority will not increase throughput any further. This was observed when running the LMD thread with priority set to HIGHEST in an Oracle8 8i environment.

 

More information can be found in the “Oracle Database 10g Architecture on Windows” white paper on OTN, in the Windows specific 10g documentation.

利用ProcDump工具诊断Windows平台上的Oracle数据库性能

ProcDump与Procexp一样是Windows平台上的内部调试工具,Procexp采用图形界面体现系统整体及个别进程的性能信息,而ProcDump则与我们在Unix平台上使用的性能分析工具类似使用CLI命令行界面。

Procdump目前的版本号为3.04,可以从其Homepage上下载到该工具,该监控软件由Mark Russinovich开发,是一款Free Software。其官方介绍如下:

ProcDump is a command-line utility whose primary purpose is monitoring an application for
CPU spikes and generating crash dumps during a spike that an administrator or developer can use to
determine the cause of the spike. ProcDump also includes hung window monitoring
(using the same definition of a window hang that Windows and Task Manager use),
unhandled exception monitoring and can generate dumps based on the values of system performance counters.
It also can serve as a general process dump utility that you can embed in other scripts.

Procdump可以用于监控应用进程的CPU峰值,并在峰值阶段生成crash dump转储信息,其功能还包括:

  • 监控hang住的窗口
  • 可以为未处理的意外(unhandled exceptions)生成dump
  • 基于系统性能计数器(system performance counters)生成dump
  • 因为Procdump是基于命令行的,所以它可以被嵌入在脚本中

以下为Procdump的命令行选项:

   -64     By default Procdump will capture a 32-bit dump of a 32-bit process
           when running on 64-bit Windows. This option overrides to create a
           64-bit dump.
   -b      Treat debug breakpoints as exceptions (otherwise ignore them).
   -c      CPU threshold at which to create a dump of the process.
   -e      Write a dump when the process encounters an unhandled exception.
           Include the 1 to create dump on first chance exceptions.
   -h      Write dump if process has a hung window (does not respond to
           window messages for at least 5 seconds).
   -m      Memory commit threshold in MB at which to create a dump of the
           process.
   -ma     Write a dump file with all process memory. The default
           dump format includes thread and handle information.
   -mp     Write a dump file with thread and handle information, and all
           read/write process memory. To minimize dump size, memory areas
           larger than 512MB are searched for, and if found, the largest
           area is excluded. A memory area is the collection of same
           sized memory allocation areas. The removal of this (cache)
           memory reduces Exchange and SQL Server dumps by over 90%.
   -n      Number of dumps to write before exiting.
   -o      Overwrite an existing dump file.
   -p      Trigger on the specified performance counter when the threshold
           is exceeded. Note: to specify a process counter when there are
           multiple instances of the process running, use the process ID
           with the following syntax: "\Process(_)\counter"
   -r      Reflect (clone) the process for the dump to minimize the time
           the process is suspended (Windows 7 and higher only).
   -s      Consecutive seconds before dump is written (default is 10).
   -t      Write a dump when the process terminates.
   -u      Treat CPU usage relative to a single core.
   -x      Launch the specified image with optional arguments.

以下为常见的Procdump使用示例:

To just create a dump of a running process, omit the CPU threshold. If
you omit the dump file name, it defaults to _.dmp.

Example: Write up to 3 dumps of a process named 'consume' when it exceeds
         20% CPU usage for five seconds to the directory
         c:\dump\consume with the name consume.dmp:
            C:\>procdump -c 20 -s 5 -n 3 -o consume c:\dump\consume
Example: Write a dump for a process named 'hang.exe' when one of it's
         windows is unresponsive for more than 5 seconds:
            C:\>procdump -h hang.exe hungwindow.dmp
Example: Write 3 dumps 5 seconds apart:
            C:\>procdump -s 5 -n 3 notepad.exe notepad.dmp
Example: Launch a process and then monitor it for excessive CPU usage:
            C:\>procdump -c 30 -s 10 -x consume.exe consume.dmp
Example: Write a dump of a process named 'iexplore' to a dump file
         that has the default name iexplore.dmp:
            C:\>procdump iexplore
Example: Write a dump of a process named 'outlook' when total system
         CPU usage exceeds 20% for 10 seconds:
            C:\>procdump outlook -p "\Processor(_Total)\% Processor Time" 20
Example: Write a dump of a process named 'outlook' when Outlook's handle count
         exceeds 10000:
            C:\>procdump outlook -p "\Process(Outlook)\Handle Count" 10000

具体使用Procdump监控Oracle进程的方法:

在我们的场景中某个服务线程消耗了大量的CPU资源:

Terminal A:
sqlplus / as sysdba
SQL> select count(1) from obj$,obj$,obj$;

Terminal B:
C:\Users\Maclean Liu>procdump  -c 20  -ma oracle.exe

ProcDump v3.04 - Writes process dump files
Copyright (C) 2009-2011 Mark Russinovich
Sysinternals - www.sysinternals.com

Process:               oracle.exe (1776)
CPU threshold:         20% of system
Performance counter:   n/a
Commit threshold:      n/a
Threshold seconds:     10
Number of dumps:       1
Hung window check:     Disabled
Exception monitor:     Disabled
Terminate monitor:     Disabled
Dump file:             C:\Users\Maclean Liu\oracle.dmp

[19:56.51] CPU:          25%  1s
[19:56.52] CPU:          24%  2s
[19:56.53] CPU:          24%  3s
[19:56.54] CPU:          25%  4s
[19:56.55] CPU:          24%  5s
[19:56.56] CPU:          24%  6s
[19:56.57] CPU:          24%  7s
[19:56.58] CPU:          25%  8s
[19:56.59] CPU:          24%  9s
[19:57.00] CPU:          25%  10s

Process has hit CPU spike threshold.
Writing dump file C:\Users\Maclean Liu\oracle_110805_195700.dmp ...
Dump written.

Dump count reached.

因为Oracle.exe的cpu峰值达到了我们所设置的20,所以产生了进程dump转储文件oracle_110805_195700.dmp

Windbg是windows平台上的图形化dump分析工具,可以通过安装Debugging Tools for Windows包获得该程序,或者直接点击这里下载。

成功安装debugging工具后,从start->Debugging Tools for Windows (x86)->windbg启动工具,在主界面上选中File->Open Crash Dump 并输入dump文件的位置,成功分析后的显示:
windbg-dump-oracle

How to make BBED(Oracle Block Brower and EDitor Tool) on Unix/Linux/Windows

“BBED(Oracle Block Brower and EDitor Tool),用来直接查看和修改数据文件数据的一个工具,是Oracle一款内部工具,可以直接修改Oracle数据文件块的内容,简单来说就是一个针对 Oracle的二进制编辑工具。该工具不受Oracle支持,所以默认是没有生成可执行文件的,在使用前需要重新编译。”

 

在10g中编译该工具显得较简单:

 

[maclean@rh2 ~]$ cd $ORACLE_HOME/rdbms/lib

[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
make: `/s01/10gdb/rdbms/lib/bbed' is up to date.

[maclean@rh2 lib]$ rm bbed

[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

Linking BBED utility (bbed)
rm -f /s01/10gdb/rdbms/lib/bbed
gcc -o /s01/10gdb/rdbms/lib/bbed -L/s01/10gdb/rdbms/lib/ -L/s01/10gdb/lib/ -L/s01/10gdb/lib/stubs/  /s01/10gdb/lib/s0main.o /s01/10gdb/rdbms/lib/ssbbded.o /s01/10gdb/rdbms/lib/sbbdpt.o `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 /s01/10gdb/rdbms/lib/defopt.o -ldbtools10 -lclntsh  `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10  -lvsn10 -lcommon10 -lgeneric10 -lmm -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /s01/10gdb/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10  -lvsn10 -lcommon10 -lgeneric10   -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -lclient10 -lnnetd10  -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10   `cat /s01/10gdb/lib/sysliblist` -Wl,-rpath,/s01/10gdb/lib -lm    `cat /s01/10gdb/lib/sysliblist` -ldl -lm   -L/s01/10gdb/lib

[maclean@rh2 lib]$ cp bbed $ORACLE_HOME/bin

[maclean@rh2 lib]$ bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 2 14:18:27 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

BBED>

/* 你可能要问密码是什么? 呵呵 .. 🙂 */

11.2.0.1中编译bbed可执行文件所需要的ssbbded.o和sbbdpt.o对象文件被移除了,所幸我们可以使用10g下的这2个对象文件在11.2.0.1中编译。

[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
Linking BBED utility (bbed)
rm -f /s01/11gdb/rdbms/lib/bbed
gcc -o /s01/11gdb/rdbms/lib/bbed -m64 -L/s01/11gdb/rdbms/lib/ -L/s01/11gdb/lib/ -L/s01/11gdb/lib/stubs/  /s01/11gdb/lib/s0main.o /s01/11gdb/rdbms/lib/ssbbded.o /s01/11gdb/rdbms/lib/sbbdpt.o `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -ldbtools11 -lclntsh  `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /s01/11gdb/lib/sysliblist` -Wl,-rpath,/s01/11gdb/lib -lm    `cat /s01/11gdb/lib/sysliblist` -ldl -lm   -L/s01/11gdb/lib
gcc: /s01/11gdb/rdbms/lib/ssbbded.o: No such file or directory
gcc: /s01/11gdb/rdbms/lib/sbbdpt.o: No such file or directory
[maclean@rh2 ~]$ cp /s01/10gdb/rdbms/lib/ssbbded.o /s01/11gdb/rdbms/lib
[maclean@rh2 ~]$ cp /s01/10gdb/rdbms/lib/sbbdpt.o  /s01/11gdb/rdbms/lib
[maclean@rh2 ~]$ cp /s01/10gdb/rdbms/mesg/bbedus.ms* /s01/11gdb/rdbms/mesg/
/* bbed 需要用到bbedus.msg和bbedus.msb 2个信息文件 */
[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
Linking BBED utility (bbed)
rm -f /s01/11gdb/rdbms/lib/bbed
gcc -o /s01/11gdb/rdbms/lib/bbed -m64 -L/s01/11gdb/rdbms/lib/ -L/s01/11gdb/lib/ -L/s01/11gdb/lib/stubs/  /s01/11gdb/lib/s0main.o /s01/11gdb/rdbms/lib/ssbbded.o /s01/11gdb/rdbms/lib/sbbdpt.o `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -ldbtools11 -lclntsh  `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/11gdb/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /s01/11gdb/lib/sysliblist` -Wl,-rpath,/s01/11gdb/lib -lm    `cat /s01/11gdb/lib/sysliblist` -ldl -lm   -L/s01/11gdb/lib
[maclean@rh2 lib]$ file bbed
bbed: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
[maclean@rh2 lib]$ size bbed
text    data     bss     dec     hex filename
154473   43448      32  197953   30541 bbed
[maclean@rh2 lib]$ ldd bbed
libclntsh.so.11.1 => /s01/11gdb/lib/libclntsh.so.11.1 (0x00002b042b883000)
libnnz11.so => /s01/11gdb/lib/libnnz11.so (0x00002b042dead000)
libdl.so.2 => /lib64/libdl.so.2 (0x00000039f2400000)
libm.so.6 => /lib64/libm.so.6 (0x00000039f2000000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039f2800000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00000039f5c00000)
libc.so.6 => /lib64/libc.so.6 (0x00000039f1c00000)
libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002b042e293000)
/lib64/ld-linux-x86-64.so.2 (0x00000039f1800000)
[maclean@rh2 lib]$ cp bbed $ORACLE_HOME/bin
[maclean@rh2 lib]$ which bbed
/s01/11gdb/bin/bbed
[maclean@rh2 lib]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 2 15:18:37 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
BBED>

 

如图:

 

 

 

 

沪ICP备14014813号

沪公网安备 31010802001379号