UDN-企业互联网技术人气社区

板块导航

浏览  : 2575
回复  : 7

[项目维护交流] oracle定时收集数据库统计信息脚本

[复制链接]
太子旭的头像 楼主
发表于 2015-8-10 15:26:55 | 显示全部楼层 |阅读模式
oracle统计信息对数据库性能有很大影响,错误的统计信息会导致sql采用错误的执行计划,例如对一个200万条记录的表执行全表扫描。以下提供自动收集统计信息的方法,切记不要在业务繁忙时段执行。


//连接用户,赋予权限,创建收集统计信息存储过程
SQL>connect UAP/UAP@oracle
SQL>grant create any table to UAP ;
    -- 这一步非常重要,需要显式地赋予用户建表权限
CREATE OR REPLACE PROCEDURE ANALYZE_TB AS

  OWNER_NAME  VARCHAR2(100);
  V_LOG       INTEGER;
  V_SQL1      VARCHAR2(800);
  V_TABLENAME VARCHAR2(50);

  CURSOR CUR_LOG IS
    SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'ANALYZE_LOG';

  --1

BEGIN
  --DBMS_OUTPUT.ENABLE (buffer_size=>100000);
  --1.1
  BEGIN
    OPEN CUR_LOG;
    FETCH CUR_LOG
      INTO V_LOG;
    IF V_LOG = 0 THEN
      EXECUTE IMMEDIATE 'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-
mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))';
    END IF;
  END;

  SELECT USER INTO OWNER_NAME FROM DUAL;
  V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
            OWNER_NAME || ''',''ANALYZE BEGIN'',''ALL'')';
  EXECUTE IMMEDIATE V_SQL1;
  sys.dbms_stats.gather_schema_stats(ownname          => UPPER(OWNER_NAME),
                                     estimate_percent => 100,
                                     method_opt       => 'FOR ALL INDEXED COLUMNS',
                                     cascade          => TRUE);
  V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
            OWNER_NAME || ''',''ANALYZE END'',''ALL'')';
  EXECUTE IMMEDIATE V_SQL1;
  commit;
  --1.2 delete tmptb statitics and lock statistics
  BEGIN
    for x in (select a.table_name, a.last_analyzed, b.stattype_locked
                from user_tables a, user_tab_statistics b
               where a.temporary = 'Y'
                 and a.table_name = b.table_name
                 and (b.STATTYPE_LOCKED is null or
                     a.last_analyzed is not null)) LOOP
      IF x.last_analyzed IS NOT NULL THEN
        --delete stats
        dbms_stats.delete_table_stats(ownname => user,
                                      tabname => x.table_name,
                                      force   => TRUE);
      END IF;
      IF x.stattype_locked IS NULL THEN
        --lock stats
        dbms_stats.lock_table_stats(ownname => user,
                                    tabname => x.table_name);
      END IF;
    END LOOP;
  end;
EXCEPTION
  WHEN OTHERS THEN
    IF CUR_LOG%ISOPEN THEN
      CLOSE CUR_LOG;
    END IF;

    commit;
end;
/
SQL>exec ANALYZE_TB ;  //手工执行存储过程
创建定时任务。下面提供的脚本示范如何创建定时任务,也是要在"sqlplus"中运行。当天的凌晨2点开始更新统计信息,以后每2天的凌晨2点更新统计信息
,注意建立当前job时,使用UAP的用户连接数据库执行,具体优化时间设置用户根据实际情况灵活调整。
VARIABLE JOBNO NUMBER;
VARIABLE INSTNO NUMBER;
BEGIN
SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
DBMS_JOB.SUBMIT(:JOBNO,'ANALYZE_TB;
',TRUNC(SYSDATE)+1+2/24,'TRUNC(SYSDATE)+2+2/24',TRUE,:INSTNO);
COMMIT;
END;
/


发表于 2015-9-7 12:11:29 | 显示全部楼层
问一个问题 为啥要收集临时表的 ?
使用道具 举报

回复

发表于 2015-9-29 11:12:25 | 显示全部楼层
粉丝来了
使用道具 举报

回复

发表于 2015-10-2 16:10:30 | 显示全部楼层
使用道具 举报

回复

发表于 2015-11-24 21:14:32 | 显示全部楼层
使用道具 举报

回复

发表于 2015-11-26 11:05:31 | 显示全部楼层
我也想知道为什么这样做,有什么意义
使用道具 举报

回复

发表于 2016-3-6 10:13:56 | 显示全部楼层
好好学习
使用道具 举报

回复

发表于 2016-8-12 13:49:50 | 显示全部楼层
看不大懂
使用道具 举报

回复

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部