oracle数据库监控与压力测试方法

sqlplusc/xt@stress_test
set autot trace explain
关掉数据查询输出,只生成执行计划

[ora10@dev160 ~]$ export ORACLE_SID=crm0
[ora10@dev160 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 20 15:02:32 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1676038346 CRM0 1 crm0

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1676038346 1 CRM0 crm0 dev160

Using 1676038346 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
crm0 CRM0 530 20 Jan 2011 00:00 1
531 20 Jan 2011 01:00 1
532 20 Jan 2011 02:00 1
533 20 Jan 2011 03:00 1
534 20 Jan 2011 04:00 1
535 20 Jan 2011 05:00 1
536 20 Jan 2011 06:00 1
537 20 Jan 2011 07:00 1
538 20 Jan 2011 08:00 1
539 20 Jan 2011 09:00 1
540 20 Jan 2011 10:00 1
541 20 Jan 2011 10:42 1
542 20 Jan 2011 10:50 1
543 20 Jan 2011 11:00 1
544 20 Jan 2011 11:10 1
545 20 Jan 2011 11:20 1
546 20 Jan 2011 11:30 1
547 20 Jan 2011 11:40 1
548 20 Jan 2011 11:50 1
549 20 Jan 2011 12:00 1
550 20 Jan 2011 12:10 1
551 20 Jan 2011 12:20 1
552 20 Jan 2011 12:30 1
553 20 Jan 2011 12:40 1
554 20 Jan 2011 12:50 1
555 20 Jan 2011 13:00 1
556 20 Jan 2011 13:10 1
557 20 Jan 2011 13:20 1
558 20 Jan 2011 13:30 1
559 20 Jan 2011 13:40 1
560 20 Jan 2011 13:50 1
561 20 Jan 2011 14:00 1
562 20 Jan 2011 14:10 1
563 20 Jan 2011 14:20 1
564 20 Jan 2011 14:30 1
565 20 Jan 2011 14:40 1
566 20 Jan 2011 14:50 1
567 20 Jan 2011 15:01 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 563
Begin Snapshot Id specified: 563

Enter value for end_snap: 567
End Snapshot Id specified: 567

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_563_567.html. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

如何看报告：
Top 5 Timed Events

free buffer waits 缓存获取等待长，没有内存可用了。

Addm 性能建议报告（直接对目标时间段内影响数据库的事件进行修改建议）

SQL> @?/rdbms/admin/addmrpt.sql

压力测试准备工作：

1、每次压力时，先做为数据库表统计分析；
exec dbms_stats.gather_table_stats(ownname => 'crm551',tabname => 'CUST_0',estimate_percent => 10,method_opt=> 'for all indexed columns');
2.配置Jmx监控端口

export USER_MEM_ARGS="-server -Xms1536m -Xmx1536m -XX:MaxPermSize=256m -XX:GCTimeRatio
-XX:UseParallelGC -XX:UseParallelOldGC -XX:ParallelGCThreads=4
-XX:+UseAdaptiveSizePolicy -Dcom.tydic.crm.server.tcp.port=4000
-Dcom.sun.management.jmxremote.port=6050
-Dcom.sun.management.jmxremote.authenticate=false
-Dcom.sun.management.jmxremote.ssl=false -Dcom.sun.management.jmxremote -Djava.rmi.server.hostname=192.168.1.15"