Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1973908
  • 博文数量: 148
  • 博客积分: 7697
  • 博客等级: 少将
  • 技术积分: 3071
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-10 23:04
个人简介

MiBDP,数据开发、项目团队、数据应用和产品在路上,金融保险、互联网网游、电商、新零售行业、大数据和AI在路上。对数仓、模型、ETL、数据产品应用了解。DTCC 2013演讲嘉宾,曾做过两款大获好评的数据产品平台。知识星球ID:35863277

文章分类
文章存档

2020年(1)

2019年(2)

2017年(2)

2016年(5)

2015年(1)

2014年(1)

2013年(6)

2012年(5)

2011年(24)

2010年(28)

2009年(1)

2008年(6)

2007年(30)

2006年(36)

分类: Oracle

2007-03-23 17:45:49

文件: 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
 
阅读(3283) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

chinaunix网友2009-04-09 14:19:58

交个朋友怎样?我的邮箱AlexExpress84@gamil.com