oracle 数据库快照快速定位耗时最久的 sql 语句方法总结

一、安装数据库的AWR工具

su – oracle

$sqlplus sys/coship as sysdba （在pl/sql 下执行SQLPLUS / AS SYSDBA

SQL>@$ORACLE_HOME/rdbms/admin/spdcreate.sql ---安装工具

其它：

SQL>@$ORACLE_HOME/rdbms/admin/spdrop.sql ---卸载工具

[A1] sQL>@$ORACLE_HOME/rdbms/admin/sptrunc.sql ---清除表中的统计数据

二、跑压力场景

SQL>exec dbms_workload_repository.create_snapshot;先执行一次快照后，再跑场景

三、压力场景跑完后，使用AWR工具收集数据中的SQL语句执行的相关数据,产生数据快照

SQL>exec dbms_workload_repository.create_snapshot;

SQL>@?/rdbms/admin/awrrpt;

Current Instance


   DB Id    DB Name   Inst Num Instance

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

 1288247510 ORCL         1 orcl

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: [c2]

Type Specified: html

Instances in this Workload Repository schema


   DB Id     Inst Num DB Name    Instance    Host

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

* 1288247510        1 ORCL     orcl   adp126

Using 1288247510 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 without

specifying a number lists all completed snapshots.

Enter value for num_days: [c3]

Listing all Completed Snapshots

                                 Snap

Instance DB Name Snap Id Snap Started Level

orcl ORCL 232 08 Sep 2011 09:00 1

            233 08 Sep 2011 10:00     1

            234 08 Sep 2011 11:00      1

            235 08 Sep 2011 12:00     1

            236 08 Sep 2011 13:00     1

            237 08 Sep 2011 14:00     1

            427 08 Sep 2011 15:00     1

            428 08 Sep 2011 16:00     1

Specify the Begin and End Snapshot Ids


Enter value for begin_snap: 427[c4] 

Begin Snapshot Id specified: 427

Enter value for end_snap: 428[c5] 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_427_428.html. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:[c6] 

SQL>exit

$ ls

awrrpt_1_427_428.html

$sz awrrpt_1_427_428.html  --导出文件到本地

四、分析收集到的数据库语句执行信息

1.用浏览器打开“sz awrrpt_1_427_428.html”，在第一面找到信息列表，点击SQL Statistics

跳转到SQL语句的分析页面，找出哪一个SQL语句耗时最长，点击相应的连接跳转到相应的SQL语句

3.跳转到相应的SQL语句后，把它复制出来

五、通过SQLPLUS工具分析，sql语句的具体延时原因

新建一个sql Windows，把SQL语句贴进去按键盘上的F5,检查SQL语句的执行路径，可以看到第一个红框中的语句执行路径为全表扫描，这是影响性能关键，需要进行优化，改为按索引进行执行

定位数据中在执行的sql语句

用普通数据库用户登陆，选择tools sessions

在展示出来的页面中，点击Status按状态进行排序，选择一第条记录，点击下列表中的SQL Txt查询出可能阻碍性能的SQL语句

查询哪个表最后执行时间：

select * from all_tab_modifications where table_name='UMC_CREATE_PASSID_NOTIFY'

[A1]这两个步骤默认情况下，ORACLE在安装的时候会自动执行安装了，不需要重复安装

[c2]在些处选择导出的格式，可以按回车默认为HTML格式

[c3]选择要收集数据的时间范围，这里按回车，默认显示所有日期的选项

[c4]从上面展示的列表中选择一个开始的时间段的Snap Id，：427

[c5]从上面展示的列表中选择一个结束的时间段的Snap Id，：428

[c6]在这里输入想要导出的文件的名字，可以按回车，直接取数据的默认命名

测试派 Give testers a home.