性能检查
• 操作系统性能检查
• CPU检查:
• OLTP系统,通常CPU总使用率应该在50%((根据该系统
历史CPU情况,可以适当下调)以下,峰值80%以下;
• DSS/DW系统,通常均值在70%以下,峰值在95%以下。
• IO检查:
• 通常CPU利用率中WIO%应该在10%以内,持续超过30%,必
须立即检查:
• 磁盘情况
• 应用情况(不良SQL)
• 内存检查:
• 内存空闲率,通常操作系统内存空闲大小(free memory)应
该在5%左右。
48
© 2010 Oracle Corporation
性能诊断工具
• Learn to solve performance problems, using diagnostic tools
to such as:
• OS tools and utilities
• Vmstat, truss and strace, ps, iostat, netstat
• Common database tools
• Statspack, ASH reports, 10046 and TKProf and
oradebug
• Specialized tools
• OS Watcher [301137.1]
49
© 2010 Oracle Corporation
10046 event dbms_system
• 使用dbms_system
- Enable trace at level 12 for session 1234 with serial#
56789
execute dbms_system.set_ev( 1234, 56789, 10046,
12, '');
- To turn off the tracing:
execute dbms_system.set_ev( 1234, 56789, 10046,
0, '');
• User_background_dump找到trc文件
50
© 2010 Oracle Corporation Lesson 2: OS Perf
使用topas/top
top - 12:55:06 up 18 days, 21:41, 2 users, load average: 9.09, 6.58, 3.27
Tasks: 88 total, 12 running, 76 sleeping, 0 stopped, 0 zombie
Cpu(s): 32.5% us, 4.7% sy, 0.0% ni, 62.6% id, 0.1% wa, 0.0% hi, 0.0% si
Mem: 775780k total, 151216k used, 624564k free, 58588k buffers
Swap: 1534196k total, 22548k used, 1511648k free, 37156k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3441 oracle 16 0 2292 296 240 R 18.3 0.0 2:25.78 eatcpu
3440 oracle 16 0 2204 300 240 R 12.9 0.0 2:39.98 eatcpu
3442 root 17 0 2196 300 240 R 4.6 0.0 1:02.55 eatcpu
3439 root 17 0 1996 296 240 R 3.8 0.0 0:56.31 eatcpu
5319 root 16 0 3552 840 660 R 0.8 0.1 0:00.02 top
1 root 16 0 1748 536 476 S 0.0 0.1 0:01.00 init
2 root 34 19 0 0 0 S 0.0 0.0 0:05.62 ksoftirqd/0
3 root 5 -10 0 0 0 S 0.0 0.0 0:00.21 events/0
4 root 5 -10 0 0 0 S 0.0 0.0 0:00.01 khelper
5 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 kblockd/0
© 2010 Oracle Corporation
10046 Oradebug
• 使用top或topas找到最高CPU的操作系统进程号
• 使用SQL*Plus以sysdba连接数据库
• SQL>alter system set timed_statistics=true;
• SQL>oradebug setospid 2269310;
• SQL>Oradebug unlimit;
• SQL>Oradebug event 10046 trace name context
forever ,level 12;
• SQL>Oradebug event 10046 trace name context off;
• SQL>oradebug TRACEFILE_NAME
52
© 2010 Oracle Corporation
使用Tkprof 格式化10046跟踪文件
• Tkprof $HOME/bdump/file_name.trc output.txt
sys=no explain=user/pwd sort=fchcpu
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27573546/viewspace-761479/,如需转载,请注明出处,否则将追究法律责任。