SQLHC

Health-checks are performed over:


CBO Statistics for schema objects accessed by the one SQL statement being analyzed
CBO Parameters
CBO System Statistics
CBO Data Dictionary Statistics
CBO Fixed-objects Statistics






查看数据库相当性能
定位到sql gh1dpjja3mn6w  为查询语句,查询物理读较多


Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)


START sqlhc.sql T gh1dpjja3mn6w   


由于sql执行完,被清出了shared_pool,等待了很久，最后手工中止了


生成了sqlhc_20170301_090721_gh1dpjja3mn6w.zip文件


解压后有
sqlhc_20170301_090721_gh1dpjja3mn6w_1_health_check.html
sqlhc_20170301_090721_gh1dpjja3mn6w_2_diagnostics.html
sqlhc_20170301_090721_gh1dpjja3mn6w_3_execution_plans.html
sqlhc_20170301_090721_gh1dpjja3mn6w_4_sql_detail.html
sqlhc_20170301_090721_gh1dpjja3mn6w_67108864_4220921888_4_5_sql_monitor.html
一直到9,由于中断，可以少了几个


先看一下sql_text


select a.INVMaterialDocID,
       x.mtlDocNO,
       x.revMtlDocNo,
       x.mtlDocItem,
       revMtlDocItem,
       x.mtlDocYear,
       x.revMtlDocYear
  from INVMaterialDoc       a,
       INVMaterialDocHeader b,
       INVReverseMtlDoc     x,
       MMRPlantMtl          pm
 where a.client = b.client
   and a.mtlDocNO = b.mtlDocNO
   and a.mtlDocYear = b.mtlDocYear
   and a.client = '800'
   and ((((a.plant = '001'))))
   and ((((a.postDate >= to_date('2017-02-01', 'yyyy-MM-dd') and
       a.postDate <= to_date('2017-02-28', 'yyyy-MM-dd')))))
   and ((((b.userName = 'JCBDAIHY'))))
   and (a.collProcureIndic = 'N' or a.collProcureIndic is null)
   and (a.storageLocation like 'AXC%')
   and pm.client(+) = a.client
   and pm.plant(+) = a.plant
   and pm.mtlNO(+) = a.mtlNO
   and (a.client = x.client and a.mtlDocYear = x.revMtlDocYear and
       a.mtlDocItem = x.revMtlDocItem and a.mtlDocNO = x.revMtlDocNo)
 order by x.mtlDocNO, x.revMtlDocNo

1.sqlhc_20170301_090721_gh1dpjja3mn6w_1_health_check.html
TABLE JC800.INVMATERIALDOC Table contains 4 column(s) where the number of buckets is 1 for a "FREQUENCY" histogram.
看到有这样的提示
SQL>select t.NUM_BUCKETS,t.HISTOGRAM from dba_tab_columns t where t.OWNER = 'JC800' and t.TABLE_NAME = 'INVMATERIALDOC'  and t.HISTOGRAM = 'FREQUENCY' and t.NUM_BUCKETS = 1;
                   1 FREQUENCY
                   1 FREQUENCY
                   1 FREQUENCY
                   1 FREQUENCY


Review column statistics for this table and look for "Num Buckets" and "Histogram". Possible Bugs 1386119, 4406309, 4495422, 4567767, 5483301 or 6082745.
If you are referencing in your predicates one of the missing values the CBO can over estimate table cardinality, and this may produce a sub-optimal plan.
You can either gather statistics with 100% or as a workaround: ALTER system/session "_fix_control"='5483301:OFF';


它的建议应该是bug引起的, 建议执行  ALTER system/session "_fix_control"='5483301:OFF';


TABLE JC800.INVREVERSEMTLDOC Table contains 2 column(s) with no popular values on a "HEIGHT BALANCED" histogram.




A Height-balanced histogram with no popular values is not helpful nor desired. Consider dropping this histogram by collecting new CBO statistics while using METHOD_OPT with SIZE 1.


这一行它说 2列是 HEIGHT BALANCED,建议手工把信息回收了




TABLE JC800.MMRPLANTMTL Table CBO statistics are 33 days old: 2017-01-26/22:02:23.


Consider gathering better quality table statistics with DBMS_STATS.AUTO_SAMPLE_SIZE on 11g or with a sample size of 100% on 10g.
Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan.




这一行，就是说这表统计信息过旧了,让收集一下


ABLE PARTITION JC800.INVMATERIALDOC 11 out of 105 partition(s) with number of rows equal to zero according to partition's CBO statistics.




If these table partitions are not empty, consider gathering table statistics using GRANULARITY=>GLOBAL AND PARTITION.


这是表分区让收统计信息.下面大多都是建议收统计信息的内容




2.sqlhc_20170301_090721_gh1dpjja3mn6w_2_diagnostics.html
229 2 2 REVMTLDOCITEM VARCHAR2 6109087 0 5569 0.1 6086 3030303031 3036303836 2017-02-27/06:44:00 6 1.428571e-03 254 HEIGHT BALANCED YES NO


主要内容是查询涉及到的列,统计信息情况
后面还有表分区信息
还实例的参数内容
主要帮助还是看看过旧的统计信息


3.sqlhc_20170301_090721_gh1dpjja3mn6w_3_execution_plans.html
内容是空,可能是没开启功能,或者是sql太low


4.sqlhc_20170301_090721_gh1dpjja3mn6w_4_sql_detail.html
以图形化的方式,查看历史这个SQL的情况,应该是从active_session_history里面抽的


5.sqlhc_20170301_090721_gh1dpjja3mn6w_67108864_4220921888_4_5_sql_monitor.html
图形方式,这个SQL的执行计划.


以下就没有啦.大家可以自己玩一下

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7569309/viewspace-2134471/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7569309/viewspace-2134471/