oracle-03优化

谁借莪１个温暖的怀抱￠ 2022-04-08 14:45 128阅读 0赞

执行路径:

ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:  
    我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就可能要几十表了.  
    这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..  
    数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,  
    当然被共享的可能性也就越大了.

当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.  
    这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须  
    完全相同(包括空格,换行等).  
      
      
共享的语句必须满足三个条件:

A.       字符级的比较:  
    当前被执行的语句和共享池中的语句必须完全相同.  
           例如:

SELECT \* FROM EMP;  
       和下列每一个都不同  
           SELECT \* from EMP;  
           Select \* From Emp;  
           SELECT       \*      FROM EMP;

B.       两个语句所指的对象必须完全相同:  
      
       用户对象名                       如何访问  
      
    Jack   sal\_limit                      private synonym  
    Work\_city                           public synonym  
    Plant\_detail                         public synonym  
    Jill sal\_limit                         private synonym  
    Work\_city                           public synonym  
    Plant\_detail                        table owner  
          考虑一下下列SQL语句能否在这两个用户之间共享.  
      
    SQL 能否共享 原因  
      
    select max(sal\_cap) from sal\_limit; 不能 每个用户都有一个private synonym - sal\_limit , 它们是不同的对象  
    select count(\*) from work\_city where sdesc like 'NEW%'; 能 两个用户访问相同的对象public synonym - work\_city  
    select a.sdesc,b.location from work\_city a , plant\_detail b where a.city\_id = b.city\_id 不能   
    用户jack 通过private synonym访问plant\_detail 而jill 是表的所有者,对象不同.  
      
    C.       两个SQL语句中必须使用相同的名字的绑定变量(bind variables)  
      
    例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)

a.  
    select pin , name from people where pin = :blk1.pin;  
    select pin , name from people where pin = :blk1.pin;  
    b.  
    select pin , name from people where pin = :blk1.ot\_ind;  
    select pin , name from people where pin = :blk1.ov\_ind;  
    重点关注1:选择最有效率的表名顺序(只在基于规则的优化器中有效)重点关注  
    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.  
    例如:       表 TAB1 16,384 条记录  
              表 TAB2 1       条记录  
          选择TAB2作为基础表 (最好的方法)  
         
      
       select count(\*) from tab1,tab2    执行时间0.96秒  
          选择TAB2作为基础表 (不佳的方法)  
      
    select count(\*) from tab2,tab1    执行时间26.09秒  
      
    如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.  
    例如:    EMP表描述了LOCATION表和CATEGORY表的交集.  
      
    复制代码  
    SELECT \*  
    FROM LOCATION L ,  
           CATEGORY C,  
           EMP E  
    WHERE E.EMP\_NO BETWEEN 1000 AND 2000  
    AND E.CAT\_NO = C.CAT\_NO  
    AND E.LOCN = L.LOCN  
    将比下列SQL更有效率  
    SELECT \*  
    FROM EMP E ,  
    LOCATION L ,  
           CATEGORY C  
    WHERE   E.CAT\_NO = C.CAT\_NO  
    AND E.LOCN = L.LOCN  
    AND E.EMP\_NO BETWEEN 1000 AND 2000  
    复制代码  
      
    重点关注2:WHERE子句中的连接顺序．重点关注  
      
    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.  
    例如:  
      
    复制代码  
    (低效,执行时间156.3秒)  
    SELECT …  
    FROM EMP E  
    WHERE   SAL >; 50000  
    AND     JOB = ‘MANAGER’  
    AND     25 < (SELECT COUNT(\*) FROM EMP  
    WHERE MGR=E.EMPNO);  
    (高效,执行时间10.6秒)  
    SELECT …  
    FROM EMP E  
    WHERE 25 < (SELECT COUNT(\*) FROM EMP  
                  WHERE MGR=E.EMPNO)  
    AND     SAL >; 50000  
    AND     JOB = ‘MANAGER’;  
    复制代码  
    重点关注3:SELECT子句中避免使用 ‘ \* ‘ ．重点关注  
      
    当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘\*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’\*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.  
    7.      减少访问数据库的次数  
    当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.  
    例如,  
         以下有三种方法可以检索出雇员号等于0342或0291的职员.  
      
    复制代码  
    方法1 (最低效)  
         SELECT EMP\_NAME , SALARY , GRADE  
         FROM EMP  
         WHERE EMP\_NO = 342;  
          SELECT EMP\_NAME , SALARY , GRADE  
         FROM EMP  
         WHERE EMP\_NO = 291;  
    方法2 (次低效)  
            DECLARE  
             CURSOR C1 (E\_NO NUMBER) IS  
             SELECT EMP\_NAME,SALARY,GRADE  
    FROM EMP  
             WHERE EMP\_NO = E\_NO;  
         BEGIN  
             OPEN C1(342);  
             FETCH C1 INTO …,..,.. ;  
                     OPEN C1(291);  
            FETCH C1 INTO …,..,.. ;  
              CLOSE C1;  
           END;  
    方法3 (高效)  
         SELECT A.EMP\_NAME , A.SALARY , A.GRADE,  
                 B.EMP\_NAME , B.SALARY , B.GRADE  
         FROM EMP A,EMP B  
         WHERE A.EMP\_NO = 342  
         or    B.EMP\_NO = 291;  
    复制代码  
    注意:  
    在SQL\*Plus , SQL\*Forms和Pro\*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200.  
      
    重点关注4:使用DECODE函数来减少处理时间.重点关注  
    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.  
    例如:  
         
      
    复制代码  
     SELECT COUNT(\*)，SUM(SAL)  
        FROM　EMP  
        WHERE DEPT\_NO = 0020  
        AND ENAME LIKE　‘SMITH%’;  
        SELECT COUNT(\*)，SUM(SAL)  
        FROM　EMP  
        WHERE DEPT\_NO = 0030  
        AND ENAME LIKE　‘SMITH%’;  
    复制代码  
       
      
    你可以用DECODE函数高效地得到相同结果  
      
    SELECT COUNT(DECODE(DEPT\_NO,0020,’X’,NULL)) D0020\_COUNT,  
             COUNT(DECODE(DEPT\_NO,0030,’X’,NULL)) D0030\_COUNT,  
             SUM(DECODE(DEPT\_NO,0020,SAL,NULL)) D0020\_SAL,  
             SUM(DECODE(DEPT\_NO,0030,SAL,NULL)) D0030\_SAL  
    FROM EMP WHERE ENAME LIKE ‘SMITH%’;  
       
      
    类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.  
      
    重点关注5: 删除重复记录.重点关注  
      
    最高效的删除重复记录方法 ( 因为使用了ROWID)  
      
    DELETE FROM EMP E  
    WHERE E.ROWID > (SELECT MIN(X.ROWID)  
                        FROM EMP X  
                        WHERE X.EMP\_NO = E.EMP\_NO);  
    重点关注6: 用TRUNCATE替代DELETE.重点关注  
      
    当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)  
    而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.  
    (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)  
      
    重点关注7: 尽量多使用COMMIT.重点关注  
      
    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:  
    COMMIT所释放的资源:  
    a.        回滚段上用于恢复数据的信息.  
    b.        被程序语句获得的锁  
    c.        redo log buffer 中的空间  
    d.       ORACLE为管理上述3种资源中的内部花费  
    (译者按: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)  
    重点关注8:减少对表的查询.重点关注  
      
    在含有子查询的SQL语句中,要特别注意减少对表的查询.  
      
    例如:  
           
      
    复制代码  
     低效  
               SELECT TAB\_NAME  
               FROM TABLES  
               WHERE TAB\_NAME = ( SELECT TAB\_NAME  
                                     FROM TAB\_COLUMNS  
                                     WHERE VERSION = 604)  
               AND　DB\_VER= ( SELECT DB\_VER  
                                FROM TAB\_COLUMNS  
                                WHERE VERSION = 604)  
          高效  
               SELECT TAB\_NAME  
               FROM TABLES  
               WHERE   (TAB\_NAME,DB\_VER)  
    = ( SELECT TAB\_NAME,DB\_VER)  
                        FROM TAB\_COLUMNS  
                        WHERE VERSION = 604)  
          Update 多个Column 例子:  
          低效:  
                UPDATE EMP  
                SET EMP\_CAT = (SELECT MAX(CATEGORY) FROM EMP\_CATEGORIES),  
                   SAL\_RANGE = (SELECT MAX(SAL\_RANGE) FROM EMP\_CATEGORIES)  
                WHERE EMP\_DEPT = 0020;  
          高效:  
                UPDATE EMP  
                SET (EMP\_CAT, SAL\_RANGE)  
    = (SELECT MAX(CATEGORY) , MAX(SAL\_RANGE)  
    FROM EMP\_CATEGORIES)  
                WHERE EMP\_DEPT = 0020;  
    复制代码  
      
    重点关注9:用EXISTS替代IN.重点关注  
      
    在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.  
      
    复制代码  
    低效:  
    SELECT \*  
    FROM EMP (基础表)  
    WHERE EMPNO > 0  
    AND DEPTNO IN (SELECT DEPTNO  
    FROM DEPT  
    WHERE LOC = ‘MELB’)  
         高效:  
    SELECT \*  
    FROM EMP (基础表)  
    WHERE EMPNO > 0  
    AND EXISTS (SELECT ‘X’  
    FROM DEPT  
    WHERE DEPT.DEPTNO = EMP.DEPTNO  
    AND LOC = ‘MELB’)  
    复制代码  
      
    (译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)  
    重点关注10:用NOT EXISTS替代NOT IN .重点关注  
      
    在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).   为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.  
    例如:  
      
    SELECT …  
    FROM EMP  
    WHERE DEPT\_NO NOT IN (SELECT DEPT\_NO  
                              FROM DEPT  
                              WHERE DEPT\_CAT=’A’);  
       
      
       
      
    为了提高效率.改写为:  
      
    复制代码  
    (方法一: 高效)  
    SELECT ….  
    FROM EMP A,DEPT B  
    WHERE A.DEPT\_NO = B.DEPT(+)  
    AND B.DEPT\_NO IS NULL  
    AND B.DEPT\_CAT(+) = ‘A’  
    (方法二: 最高效)  
    SELECT ….  
    FROM EMP E  
    WHERE NOT EXISTS (SELECT ‘X’  
                         FROM DEPT D  
                         WHERE D.DEPT\_NO = E.DEPT\_NO   
                         AND DEPT\_CAT = ‘A’);  
    复制代码  
       
      
       
      
    当然,最高效率的方法是有表关联.直接两表关系对联的速度是最快的!  
    重点关注11:识别’低效执行’的SQL语句.重点关注  
      
    用下列SQL工具找出低效SQL:  
      
    复制代码  
    SELECT EXECUTIONS , DISK\_READS, BUFFER\_GETS,  
             ROUND((BUFFER\_GETS-DISK\_READS)/BUFFER\_GETS,2) Hit\_radio,  
             ROUND(DISK\_READS/EXECUTIONS,2) Reads\_per\_run,  
             SQL\_TEXT  
    FROM    V$SQLAREA  
    WHERE   EXECUTIONS>;0  
    AND      BUFFER\_GETS >; 0  
    AND (BUFFER\_GETS-DISK\_READS)/BUFFER\_GETS < 0.8  
    ORDER BY 4 DESC;

\#\#1.sql语言是否完全相同  空格 大小写问题  
    select \* from emp;  
    select  \* from emp;  
    select \* from EMP;  
    \#\#2.绑定变量的使用  
    select \* from emp where empno=10;      Statement  
    select \* from emp where empno=:empno;  PreparedStatement  
    \#\#3.减少或避免\*的出现  \*号需要先访问数据字典表，找到该表中所有的字段名，然后替换\*号  
    select EMPNO,ENAME,JOB from EMP where EMPNO=:EMPNO;  
    \#\#4.多表查询时，基表的选择  dept 表1行数据 emp 1000行数据 from后跟的最后一张表叫基表  
    select count(\*) from user\_tables;    
    select count(owner) from dba\_tables;  
    select count(distinct owner) from dba\_tables;  
    \#\#5.将小表作为基表，放到from最后一个位置  
    select \* from emp,dept where emp.deptno = dept.deptno;  
      
    \#\#6.员工表emp  部门表dept  绩效表 empno days 三张表，将中间表作为基表  
    select \* from dept,jixiao,emp where emp.empno=jixiao.empno and emp.deptno=dept.deptno;  
      
    select \* from emp join dept  
           on emp.deptno=dept.deptno  
           join jixiao   
           on emp.empno = jixiao.empno;  
             
    \#\#7.显示员工编号，部门号，工资 （部门编号为 10 并且工资大于3000）  
       where 先写表连接，后写筛选条件  
       筛选条件 从后往前执行，剩余数据最少的条件放到后面位置  
    select e.empno,d.deptno,sal  
    from emp e, dept d  
    where e.deptno = d.deptno and d.deptno=10 and sal>3000  ;  
    \#\#8.删除重复记录  rowid  
    select emp.\*,rowid from emp;  
    \#\#9.创建表结构  
    create table emp1 as select \* from scott.emp;  
      
    select \* from emp1;  
    insert into emp1 select \* from scott.emp where sal>3000;  
      
    select count(empno) from emp1;  
    select count(distinct empno) from emp1;  
    \#\#10.删除重复记录  
    select \* from emp1 e where rowid > (select min(rowid) from emp1 where e.empno=emp1.empno);  
    delete from emp1 e where rowid > (select min(rowid) from emp1 where e.empno=emp1.empno);  
      
    delete from emp1 e where rowid < (select max(rowid) from emp1 where e.empno=emp1.empno);  
      
    \#\#11.删除所有数据  truncate 代替delete删除所有数据  
    delete truncate  
      
    \#\#12.大批量sql中多使用commit   
      
    \#\#13.用EXISTS替代IN  
      
    select \* from emp where deptno=10 or deptno=20;  
    select \* from emp where deptno in (10,20);  
    select \* from dept;  
      
      SELECT \*  
        FROM EMP  
        WHERE EMPNO > 0  
        AND DEPTNO IN (SELECT DEPTNO  
        FROM DEPT  
        WHERE LOC = 'NEW YORK');  
        
        SELECT \*  
        FROM EMP   
        WHERE EMPNO > 0  
        AND EXISTS (SELECT 'X'  
        FROM DEPT  
        WHERE DEPT.DEPTNO = EMP.DEPTNO  
        AND LOC = 'NEW YORK');  
        
      SELECT \*  
        FROM EMP  
        WHERE EMPNO > 0  
        AND DEPTNO  not IN (SELECT DEPTNO  
        FROM DEPT  
        WHERE LOC = 'NEW YORK');  
        
      SELECT \*  
        FROM EMP   
        WHERE EMPNO > 0  
        AND not EXISTS (SELECT 'X'  
        FROM DEPT  
        WHERE DEPT.DEPTNO = EMP.DEPTNO  
        AND LOC = 'NEW YORK');  
        
        
    ALTER SYSTEM FLUSH SHARED\_POOL;  
    ALTER SYSTEM FLUSH BUFFER\_CACHE;  
    ALTER SYSTEM FLUSH GLOBAL CONTEXT;  
      
    \#\#查看sql语句的执行效率       
    SELECT EXECUTIONS , DISK\_READS, BUFFER\_GETS,  
                 ROUND((BUFFER\_GETS-DISK\_READS)/BUFFER\_GETS,2) Hit\_radio,  
                 ROUND(DISK\_READS/EXECUTIONS,2) Reads\_per\_run,  
                 SQL\_TEXT  
        FROM    V$SQLAREA  
        WHERE   EXECUTIONS>0  
        AND      BUFFER\_GETS > 0  
        AND (BUFFER\_GETS-DISK\_READS)/BUFFER\_GETS < 0.8  
        ORDER BY 4 DESC;  
    \#\#如果sql锁定，释放锁  
      
    SELECT s.sid, s.serial\#, s.username, s.schemaname, s.osuser, s.process, s.machine,  
    s.terminal, s.logon\_time, l.type  
    FROM v$session s, v$lock l  
    WHERE s.sid = l.sid  
    AND s.username='SCOTT'  
    ORDER BY sid;  
      
    alter system kill session 'sid,serial\#';  
    alter system kill session '130,1737';  
      
      
    \#\#index（索引）  
    \#\#使用索引（在表中特定列上使用索引）  提高查询效率，对数据进行检查（检查数据完整性  -- 唯一索引）  
    \#\#什么时候适合使用索引提高效率  
    1.表中数据量大  
    2.该列重复数据非常小  
    \#\#使用索引的劣势  
    1.索引降低dml操作的效率  
    2.如果表数据量小，重复数据多，使用索引反而会降低查询效率  
    \#\#索引常用的分类  
    普通索引 -- 提高查询效率  
    唯一索引 -- 检查数据完整性，提高效率  
    复合索引 -- 针对同时查询的多个列，同时建立索引  
      
    create index idx\_emp\_empno on emp(empno);  
    create unique index idx\_emp\_empno on emp(empno);  
      
    select \* from emp where mgr='7902' and job='SALESMAN';  
    create index idx\_emp\_job\_mgr on emp(job,mgr);  
      
    \#\#partition（分区）  rac集群   
      
    表数据量非常大的情况下，写入、查询效率非常低  ，使用表分区策略  
    表分区策略主要是将一张表分为多张小表，多外是一张表，但是底层有多张表组成，可以放置到多个表空间，多个数据文件中  
      
    分区方法：  
                            范围分区（数值范围，时间范围）  
                            列表分区（根据散列值分区 根据仓库编号定位）  北京库  南京库  郑州库  
                            Hash分区  
                            复合分区  
      
     CREATE TABLE EMPLOYEE\_RANGE\_PART  
      2  ( EMPNO NUMBER(4,0),  
      3    ENAME VARCHAR2(10),  
      4    JOB VARCHAR2(9),  
      5    MGR NUMBER(4,0),  
      6    HIREDATE DATE,  
      7    SAL NUMBER(7,2),  
      8    COMM NUMBER(7,2),  
      9    DEPTNO NUMBER(2,0)  
     10  )  
     11  PARTITION BY RANGE (HIREDATE)  
     12  (  
     13  PARTITION EMPLOYEE\_PART01 VALUES LESS THAN (TO\_DATE('1981-01-01','yyyy-mm-dd'))  
     14  TABLESPACE users,  
     15  PARTITION EMPLOYEE\_PART02 VALUES LESS THAN (TO\_DATE('1982-01-01','yyyy-mm-dd'))  
     16  TABLESPACE users,  
     17  PARTITION EMPLOYEE\_PART03 VALUES LESS THAN (MAXVALUE)  
     18  TABLESPACE users  
     19  );    
      
    CREATE TABLE "EMPLOYEE\_HASH\_PART"  
      2  ( "EMPNO" NUMBER(4,0),  
      3    "ENAME" VARCHAR2(10),  
      4    "JOB" VARCHAR2(9),  
      5    "MGR" NUMBER(4,0),  
      6    "HIREDATE" DATE,  
      7    "SAL" NUMBER(7,2),  
      8    "COMM" NUMBER(7,2),  
      9    "DEPTNO" NUMBER(2,0)  
     10  )  
     11  PARTITION BY HASH (ENAME)  
     12  (  
     13  PARTITION EMPLOYEE\_PART01 TABLESPACE test\_tbs\_01,  
     14  PARTITION EMPLOYEE\_PART02 TABLESPACE test\_tbs\_02  
     15  );     
      
      
    CREATE TABLE "EMPLOYEE\_LIST\_PART"  
      2  ( "EMPNO" NUMBER(4,0),  
      3    "ENAME" VARCHAR2(10),  
      4    "JOB" VARCHAR2(9),  
      5    "MGR" NUMBER(4,0),  
      6    "HIREDATE" DATE,  
      7    "SAL" NUMBER(7,2),  
      8    "COMM" NUMBER(7,2),  
      9    "DEPTNO" NUMBER(2,0)  
     10  )  
     11  PARTITION BY LIST (JOB)  
     12  (  
     13  PARTITION EMPLOYEE\_PART01 VALUES ('PRESIDENT','MANAGER','ANALYST')  
     14  TABLESPACE test\_tbs\_01,  
     15  PARTITION EMPLOYEE\_PART02 VALUES ('CLERK')  
     16  TABLESPACE test\_tbs\_02,  
     17  PARTITION EMPLOYEE\_PART03 VALUES (DEFAULT)  
     18  TABLESPACE test\_tbs\_03  
     19  );     
      
      
    select \* from user\_tablespaces;  
      
    \#\#OLTP  OLAP  
      
    OLTP  online transaction process  联机事务处理  事务（侧重于数据完整性）  
      
    OLAP  online analytical process   联机分析处理  （效率）（比如推广数据，分区、索引、rac集群）  sqlplus /nolog  
      
    mysql：myisam（可以使用全文索引，不能使用事务）  innodb（事务，不能使用全文索引）