oracle性能调忧工具AWR,ASH,ADDM

最新推荐文章于 2023-05-16 21:43:50 发布

cny0120

最新推荐文章于 2023-05-16 21:43:50 发布

阅读量166

点赞数

文章标签：数据库开发工具

一.ASH(Active Session History)活动会话历史记录

ASH以V$SESSION为基础，每秒采样一次记录活动会话等待的事件，不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成。ASH buffers 的最小值为1MB,最大值不超过30MBm，内存中记录数据，期望值是记录一小时的内容。

[oracle@ogg2 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/

[oracle@ogg2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 15:17:03 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @ ashrpt.sql --使用sql生成报告

End of Report

Report written to ashrpt_1_0411_1517.txt

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ogg2 admin]$ vim ashrpt_1_0411_1517.txt --查看内容

二.ADDM(Automatic Database Diagnostic Monitor)自动数据库诊断监视器

ADDM是Oracle内部的一个顾问系统，能够自动的完成最数据库的一些优化的建议，给出SQL的优化，索引的创建，统计量的收集等建议。

[oracle@ogg2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 15:23:12 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @ addmrpt.sql

End of Report

Report written to addmrpt_1_10660_10723.txt

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ogg2 admin]$ vim addmrpt_1_10660_10723.txt

三.AWR(Automatic Workload Repository)自动工作负载信息库

AWR报告生成时间是Oracle启动到关闭中间，数据库重启后会影响AWR报告连续性。Oracle 11.2.0.3有个bug是AWR报告不能以html格式输出，可以输出text格式。

[oracle@ogg2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 15:23:12 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select snap_interval, retention from dba_hist_wr_control; --查询awr设置

SNAP_INTERVAL RETENTION

---------------------------------------------------------------------------

+00000 01:00:00.0 +00008 00:00:00.0

SQL> @ awrrpt.sql

End of Report

Report written to awrrpt_1_10692_10723.txt

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ogg2 admin]$

四.找出数据库正在运行的慢SQL

找出正在运行时的慢SQL语句可以通过v$session+v$sqlarea视图。如果在一个小时以内发生的我们可以通过生成ASH报告来找出SQL。如果是1小时以上或几天我们可以通过AWR报告来找出最影响系统的SQL语句。ADDM报告基于AWR库，默认可以保存30天的ADDM报告。

SQL> select sql_id,SQL_FULLTEXT,disk_reads from v$sqlarea order by disk_reads; --查出最耗磁盘IO的sql语句

575n0h472axp5 DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda 26071305

8rkkvsws8j7mc SELECT DISTINCT MAX("A1"."END_DATE"),"A1"."DATA_SOUR" FROM "UPINFO"."HK_SZ_SAMP_ 32066026

c5qwm85rqx158 DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda 33885567

b5cbrvz8a2uhb SELECT LINE_ID, PUBLISH_DATE, TITLE, URL, AFFIX_NAME, AFFIX_PATH, ORIG_CLASS, UP 38591289

d2xrh5ata01ah SELECT URL, LINE_ID, PUBLISH_DATE, TITLE, AFFIX_PATH, AFFIX_NAME, UPDATETIME FRO 43511414

0hzc203fng3t4 begin upmain.PR_STK_SEC_TRADE_INFO(:a,:b); end; 45662039

5nrvy4cqq3aq8 SELECT "ISVALID","TRADE_DATE","MKT_CLS_PAR","SEC_UNI_CODE","SELL_BALANCE_VOL" FR130555461

74ju8jvk9k70t DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda253372505

56293grsrhapw begin upmain.PR_PUB_ORG_INFO(:a,:b); end; 270812668

8mp3aausbm77c MERGE INTO UPMAIN.PUB_ORG_INFO T USING( SELECT DISTINCT ORG_UNI_CODE ,GEN_MAN FR 1062109712

5842 rows selected.

SQL> select distinct USER_ID from dba_hist_active_sess_history where sql_id='8mp3aausbm77c'; --根据sql_id查sql

USER_ID

----------

SQL> select user_id,username from dba_users where user_id=85; --找用户

USER_ID USERNAME

---------- ------------------------------

85 UPMAIN

SQL>