statspack安装使用和report 分析(6)

pmon timer 373 373 1,083 2903 14.9

smon timer 3 3 924 ###### 0.1

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

8.TOP SQL

调整首要的25个缓冲区读操作和首要的25个磁盘读操作做的查询，将可对系统性能产生5%到5000%的增益。

SQL ordered by Gets for DB: BLISSDB Instance: blissdb Snaps: 4 -5

-> End Buffer Gets Threshold: 10000

-> Note that resources reported for PL/SQL includes the resources used by

all SQL statements called within the PL/SQL code. As individual SQL

statements are also reported, it is possible and valid for the summed

total % to exceed 100

CPU Elapsd

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

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

1,230,745 1 1,230,745.0 27.5 16.39 60.69 1574310682

Module: PL/SQL Developer

insert into city_day_cal select * from rptuser.city_day_cal@db15

1

143,702 1 143,702.0 3.2 1.75 18.66 3978122706

Module: PL/SQL Developer

insert into city_day_cal select * from rptuser.city_day_cal@db15

1 where curtime between to_date('200501','yyyymm') and to_date('

200502','yyyymm')-1  

在报表的这一部分，通过Buffer Gets对SQL语句进行排序，即通过它执行了多少个逻辑I/O来排序。顶端的注释表明一个PL/SQL单元的缓存获得(Buffer Gets)包括被这个代码块执行的所有SQL语句的Buffer Gets。因此将经常在这个列表的顶端看到PL/SQL过程，因为存储过程执行的单独的语句的数目被总计出来。

SQL ordered by Reads for DB: BLISSDB Instance: blissdb Snaps: 4 -5

-> End Disk Reads Threshold: 1000

CPU Elapsd

Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value

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

3,587 1 3,587.0 13.9 0.17 5.13 3342983569

Module: PL/SQL Developer

select min(curtime),max(curtime) from city_day_cal

1,575 1 1,575.0 6.1 1.75 18.66 3978122706

Module: PL/SQL Developer

insert into city_day_cal select * from rptuser.city_day_cal@db15

1 where curtime between to_date('200501','yyyymm') and to_date('

200502','yyyymm')-1  

这部分通过物理读对SQL语句进行排序。这显示引起大部分对这个系统进行读取活动的SQL，即物理I/O。

SQL ordered by Executions for DB: BLISSDB Instance: blissdb Snaps: 4 -5

-> End Executions Threshold: 100

CPU per Elap per

Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value

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

748 748 1.0 0.00 0.00 3371479671

select t.name, (select owner_instance from sys.aq$_queue_table_

affinities where table_objno = t.objno) from system.aq$_queue

_tables t where t.name = :1 and t.schema = :2 for update skip lo