 |
| 文件: |
ssreport_070323.rar |
| 大小: |
13KB |
| 下载: |
下载 | |
这两天有时间学习了statspack这个强大工具的使用。关于这个工具的使用网上有很多好的文章介绍，这里我主要是根据eygle大师总结的那个pdf来学习的及turner老师总结的一个pdf。
一并传上来，供大家共同学习。
 |
| 文件: |
Eygle_Statspack-v3.0.pdf |
| 大小: |
141KB |
| 下载: |
下载 | |
|
| 文件: |
如何读懂statspack.pdf |
| 大小: |
191KB |
| 下载: |
下载 | |
结合这两个PD自己也在内部做了收集测试，以下是我生成的一个report，采样间隔为1小时。
根据生成的这个report我主要查询了系统中消耗资源比较high的SQL。这是查询比较耗资源的SQL：
SELECT vt.sql_text FROM v$sql_text vt,stats$sql_summary vs
WHERE vt.hash_value=vs.hash_value AND vs.disk_reads>&v(v:50000)
(注意：这里查出来的SQL有可能是分片的SQL)
select apacminfo0_.PK as PK0_, apacminfo0_.NAME as NAME0_, apacminfo0_.ACMNO as ACMNO0_,apacminfo0_.APOWNERINCORPORATOR as APOWNERI4_0_, apacminfo0_.APMANAGEDEPTINCORPORATOR as APMANAGE5_0_, apacminfo0_.AUTHORIZENO as AUTHORIZ6_0_, apacminfo0_.ADMINISTRATOR as ADMINIST7_0_, apacminfo0_.MANAGENAME as MANAGENAME0_, apacminfo0_.USEKIND as USEKIND0_, apacminfo0_.FLYLEVELONE as FLYLEVE10_0_, apacminfo0_.FLYLEVELTWO as FLYLEVE11_0_, apacminfo0_.PCN as PCN0_, apacminfo0_.PCNTYPE as PCNTYPE0_, apacminfo0_.PCNBASE as PCNBASE0_, apacminfo0_.PCNTYRE as PCNTYRE0_, apacminfo0_.PCNEVALUE as PCNEVALUE0_, apacminfo0_.PLANETYPE as PLANETYPE0_, apacminfo0_.EMERGENCYLEVEL as EMERGEN18_0_, apacminfo0_.RUNNINGTYPE as RUNNING19_0_, apacminfo0_.ASSISTANCE as ASSISTANCE0_, apacminfo0_.AWARDDEPT as AWARDDEPT0_, apacminfo0_.ACMDATE as ACMDATE0_,apacminfo0_.LICENCESTATE as LICENCE23_0_, apacminfo0_.HISTORYFLAG as HISTORY24_0_, apacminfo0_.DELETED as DELETED0_, apacminfo0_.TRANSFERFLAG as TRANSFE26_0_, apacminfo0_.CREATETIME as CREATETIME0_, apacminfo0_.UPDATETIME as UPDATETIME0_, apacminfo0_.OPERATEDEPT as OPERATE29_0_, apacminfo0_.OPERATOR as OPERATOR0_,
apacminfo0_.ADMINISTRATORNAME as ADMINIS31_0_, apacminfo0_.USEKINDNAME as USEKIND32_0_,
apacminfo0_.PCNTYPENAME as PCNTYPE33_0_, apacminfo0_.RUNNINGTYPENAME as RUNNING34_0_,
apacminfo0_.AIRINFOPK as AIRINFOPK0_, apacminfo0_.REQUISITIONPK as REQUISI36_0_
from HANGAN.AP_ACMINFO apacminfo0_ where apacminfo0_.REQUISITIONPK=:1;
我这个SQL就是分了24片的（PIECE
）
分析这个SQL的执行计划显示不是很理想:
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=797)
1 0 TABLE ACCESS (FULL) OF 'AP_ACMINFO' (Cost=2 Card=1 Bytes=7
97)
统计信息
----------------------------------------------------------
103 recursive calls
0 db block gets
285 consistent gets
212 physical reads
0 redo size
2665 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
0 sorts (disk)
1 rows processed
查询该表在条件列上的索引，发现条件列REQUISITIONPK上没有建立索引，建立相应的索引：
CREATE INDEX ind_ap_acminfo ON ap_acminfo(requisitionpk);
重新分析下原表：
analyze TABLE ap_acminfo compute statistics for all indexes;
再显示下执行计划，明显比以前的优化了。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AP_ACMINFO'
2 1 INDEX (RANGE SCAN) OF 'IND_AP_ACMINFO' (NON-UNIQUE)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
2665 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
下面是自己分析的一个ssreport
阅读(3365) | 评论(1) | 转发(0) |
