oracle统计分析信息拷贝介绍.doc

数据库统计分析信息拷贝介绍1. 数据库统计分析简介 统计分析主要包括产生表及索引的统计信息。表的统计信息主要包括表的行数，每行的平均长度（字节），空闲块，统计时间等信息；索引的统计信息主要包括行数、层数、叶块数、统计时间等信息。另外ORACLE还可以统计列及数据不对称信息。ORACLE执行成本分析时首先取出所应用表及索引的统计数据进行分析，其中数据行数是一个重要的参数，因为ORACLE在分析表大小时行数为主要参数，如果进行两个表联合时，ORACLE会通过分析表的大小，决定应用小表进行全表查询，而大表执行联合查询，这种性能明显高 于先大表进行全表扫描。索引的统计信息对分析也产生比较大的影响，如ORACLE通过统计可以分析产生多个索引的优先级及索引的实用性来确定最优的索引策 略。ORACLE还可以统计列及数据对称信息以产生更精确的分析。 dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。2. 存储过程解析2.1 DBMS_STATS.GATHER_TABLE_STATS介绍DBMS_STATS.GATHER_TABLE_STATS功能为：统计表、列、索引的统计信息。DBMS_STATS.GATHER_TABLE_STATS的语法如下:DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2,tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2,cascade BOOLEAN,stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);参数说明:ownname:要分析表的拥有者tabname:要分析的表名.partname:分区的名字,只对分区表或分区索引有用.estimate_percent:采样行的百分比,取值范围0.000001,100,null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle绝定最佳取采样值.block_sapmple:是否用块采样代替行采样.method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:for all columns:统计所有列的histograms.for all indexed columns:统计所有indexed列的histograms.for all hidden columns:统计你看不到列的histogramsfor columns SIZE | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围1,254; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by there is skew in the datadegree:决定并行度.默认值为null. degree = 15 granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.cascace:是收集索引的信息.默认为falase.stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息 表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.force:即使表锁住了也收集统计信息.例子:execute dbms_stats.gather_table_stats(ownname = owner,tabname = table_name ,estimate_percent = null ,method_opt = for all indexed columns ,cascade = true);2.2 oracle程序包Oracle提供的程序包如下：COPY_CBO_STATS_SUBPART.pksCOPY_CBO_STATS_SUBPART.pkbCOPY_CBO_STATS.pksCOPY_CBO_STATS.pkbORADB_COPY_STATS.prc主要实现信息统计分析和拷贝的功能。2.3 自行封装的存储过程job_copy_stats job_copy_stats是自行封装的存储过程，通过调用Oracle提供的程序包，来实现信息统计分析和拷贝的功能。部署前需要根据需求修改相关内容。3. 部署过程3.1 部署存储过程以下是新网点核算的部署过程：NCSAPP1/home/ncsapp/xbin/COPY_CBOls -lrttotal 64-rw-r-r- 1 ncsapp app 20803 Jun 17 11:27 COPY_CBO_STATS.pkb-rw-r-r- 1 ncsapp app 767 Jun 17 11:27 COPY_CBO_STATS.pks-rw-r-r- 1 ncsapp app 16486 Jun 17 11:27 COPY_CBO_STATS_SUBPART.pkb-rw-r-r- 1 ncsapp app 691 Jun 17 11:27 COPY_CBO_STATS_SUBPART.pks-rw-r-r- 1 ncsapp app 6184 Jun 17 11:27 ORADB_COPY_STATS.prc-rw-r-r- 1 ncsapp app 2405 Jun 17 11:27 job_copy_stats_ncappdb.sqlNCSAPP1/home/ncsapp/xbin/COPY_CBOchmod 755 *NCSAPP1/home/ncsapp/xbin/COPY_CBOsqSQL*Plus: Release .0 Production on Tue Jun 17 11:29:07 2014Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL COPY_CBO_STATS.pksPackage created.SQL COPY_CBO_STATS.pkbPackage body created.SQL COPY_CBO_STATS_SUBPART.pksPackage created.SQL COPY_CBO_STATS_SUBPART.pkbPackage body created.SQL ORADB_COPY_STATS.prcProcedure created.SQL job_copy_stats_ncappdb.sqlProcedure created.3.2 通过脚本调用存储过程#!/bin/bashsqlplus -s $INIT_USING_DBASE/$INIT_USING_DBASEPWDEOFset serverout on lin 1400 time onexec job_copy_stats;EOF3.3 通过oracle自动任务job调用存储过程a. 在数据库里将job挂起variable job_num number ;begindbms_job.submit(:job_num,JOB_COPY_STATS;,SYSDATE,trunc(sysdate+1)+1/24 ) ;end ; /b. 启动任务begindbms_job.run(:job_num);end;/c. 验证select * from dba_jobs;任务列表里有JOB_COPY_STATS任务即可。select * from dba_jobs_running;当有任务运行时此表会有运行记录。d. 取消任务将挂载定时任务删除begindbms_job.remove(:job_num);end;/3.4 oracle JOB常见的执行时间1、每分钟执行TRUNC(sysdate,mi)+1/(24*60) 2、每天定时执行例如：每天凌晨0点执行TRUNC(sysdate+1)每天凌晨1点执行TRUNC(sysdate+1)+1/24每天早上8点30分执行TRUNC(SYSDATE+1)+(8*60+30)/(24*60)3、每周定时执行例如：每周一凌晨2点执行TRUNC(next_day(sysdate,1)+2/24TRUNC(next_day(sysdate,星期一)+2/24每周二中午12点执行TRUNC(next_day(sysdate,2)+12/24TRUNC(next_day(sysdate,星期二)+12/244、每月定时执行例如：每月1日凌晨0点执行TRUNC(LAST_DAY(SYSDATE)+1)每月1日凌晨1点执行TRUNC(LAST_DAY(SYSDATE)+1)+1/245、每季度定时执行每季度的第一天凌晨0点执行TRUNC(ADD_MONTHS(SYSDATE,3),q)每季度的第一天凌晨