• Oracle性能优化1-总体思路和误区


    最近在看梁敬彬老师关于Oracle性能优化的一些案例,在这里做一些简单的总结

    1.COUNT(*)与COUNT(列)哪个更快

    drop table t purge;
    create table t as select * from dba_objects;
    --alter table T modify object_id  null;
    update t set object_id =rownum ;
    set timing on 
    set linesize 1000
    set autotrace on 

    select count(*) from t;
    /
    select count(object_id) from t;

    推论1:在不建索引的情况下,count(*)和count(列)没有区别

    看来count(列)比count(*) 更快是谣传,明明是一样快嘛,真相是这样吗?

    建索引试试
    create index idx_object_id on t(object_id);
    select count(*) from t;

    select count(object_id) from t;

    推论2:原来真的是用COUNT(列)比COUNT(*)要快啊,因为COUNT(*)不能用到索引,而COUNT(列)可以,真相真是如此吗?

    继续

    alter table T modify object_id  not  null;
    select count(*) from t;
    select count(object_id) from t;

    推论3:看来count(列)和count(*)其实一样快,如果索引列是非空的,count(*)可用到索引,此时一样快!真相真是如此吗?

    其实两者根本没有可比性,性能比较首先要考虑写法等价,这两个语句根本就不等价!

    最佳字段顺序(结论:越往后的列访问CPU开销大)

    验证脚本1 (先构造出表和数据)
    SET SERVEROUTPUT ON
    SET ECHO ON
    ---构造出有25个字段的表T
    DROP TABLE t;
    DECLARE
      l_sql VARCHAR2(32767);
    BEGIN
      l_sql := 'CREATE TABLE t (';
      FOR i IN 1..25 
      LOOP
        l_sql := l_sql || 'n' || i || ' NUMBER,';
      END LOOP;
      l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10';
      EXECUTE IMMEDIATE l_sql;
    END;
    /
    ----将记录还有这个表T中填充
    DECLARE
      l_sql VARCHAR2(32767);
    BEGIN
      l_sql := 'INSERT INTO t SELECT ';
      FOR i IN 1..25
      LOOP
        l_sql := l_sql || '0,';
      END LOOP;
      l_sql := l_sql || 'NULL FROM dual CONNECT BY level <= 10000';
      EXECUTE IMMEDIATE l_sql;
      COMMIT;
    END;
    /

    --验证脚本2(一次访问该表各字段验证) 
    execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')
    SELECT num_rows, blocks FROM user_tables WHERE table_name = 'T';
    --以下动作观察执行速度,比较发现COUNT(*)最快,COUNT(最大列)最慢
    DECLARE
      l_dummy PLS_INTEGER;
      l_start PLS_INTEGER;
      l_stop PLS_INTEGER;
      l_sql VARCHAR2(100);
    BEGIN
      l_start := dbms_utility.get_time;
      FOR j IN 1..1000
      LOOP
        EXECUTE IMMEDIATE 'SELECT count(*) FROM t' INTO l_dummy;
      END LOOP;
      l_stop := dbms_utility.get_time;
      dbms_output.put_line((l_stop-l_start)/100);


      FOR i IN 1..25
      LOOP
        l_sql := 'SELECT count(n' || i || ') FROM t';
        l_start := dbms_utility.get_time;
        FOR j IN 1..1000
        LOOP
          EXECUTE IMMEDIATE l_sql INTO l_dummy;
        END LOOP;
        l_stop := dbms_utility.get_time;
        dbms_output.put_line((l_stop-l_start)/100);
      END LOOP;
    END;
    /

    结论:
    原来优化器是这么搞的:列的偏移量决定性能,列越靠后,访问的开销越大。由于count(*)的算法与列偏移量无关,所以count(*)最快。
    后面还有看图说话,看看结果输出的趋势图,就更了然了。

    2.IN和EXISTS之争

    10g执行一下
    select * from v$version;
    drop table emp purge;
    drop table dept purge;
    create table emp as select * from scott.emp;
    create table dept as select * from scott.dept;
    set timing on 
    set linesize 1000
    set autotrace traceonly 
    select * from dept where deptno NOT IN ( select deptno from emp ) ;
    select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;

    select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;
    -结论:10g与空值有关,如果确保非空,可以用到anti的半连接算法

    11g执行一下
    select * from v$version;

    drop table emp purge;
    drop table dept purge;
    create table emp as select * from scott.emp;
    create table dept as select * from scott.dept;
    set timing on 
    set linesize 1000
    set autotrace traceonly explain
    select * from dept where deptno NOT IN ( select deptno from emp ) ;
    select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;

    select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;

    结论:11g与空值有关,都可以用到anti的半连接算法,执行计划一样,性能一样

    版权声明:本文为博主原创文章,未经博主允许不得转载。

  • 相关阅读:
    mysql启动时报错:Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql/data/mysql.pid)
    awk技巧【转】
    在分析nginx日志时常用命令总结【转】
    mybatis 详解(八)------ 懒加载
    mybatis 详解(七)------一对一、一对多、多对多
    mybatis 详解(六)------通过mapper接口加载映射文件
    mybatis 详解(五)------动态SQL
    mybatis 详解(四)------properties以及别名定义
    mybatis 详解(三)------入门实例(基于注解)
    mybatis 详解(二)------入门实例(基于XML)
  • 原文地址:https://www.cnblogs.com/weiguo21/p/4823979.html
Copyright © 2020-2023  润新知