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

时间:2021-09-23 06:20:24

最近在看梁敬彬老师关于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的半连接算法,执行计划一样,性能一样

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