oracle调优（2）

最新推荐文章于 2024-06-12 07:22:08 发布

Knight10zzzz

最新推荐文章于 2024-06-12 07:22:08 发布

阅读量103

点赞数

分类专栏： oracle 文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/Knight10zzzz/article/details/120208721

版权

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

接上篇oracle调优（1）

11.用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的性能开销.

HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中

例如:

低效:

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

GROUP BY REGION

HAVING REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

高效 :

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

WHERE REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

GROUP BY REGION

12.减少子查询对表的查询

在含有子查询的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)

13.使用表的别名(Alias)

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

注释：olumn歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column

14.用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.。试验结果表明，not exists替代not in效率甚至比exists替代in提升的更高

低效:

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’)

15.用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 LEFT JOIN DEPT B

ON A.DEPT_NO=B.DEPT_NO

WHERE 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’);

16.用表连接替换EXISTS

例如：获取部门名为Finance的员工信息
1、使用EXISTS
SQL> select employee_id,first_name,salary,department_id
2 from employees
3 where exists(
4 select 1 from departments where departments.department_id=employees.department_id
5 and department_name=‘Finance’);

EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID

108 Nancy 12008 100
109 Daniel 9000 100
110 John 8200 100
111 Ismael 7700 100
112 Jose Manuel 7800 100
113 Luis 6900 100

6 rows selected.

Elapsed: 00:00:00.02

2、使用表连接
SQL> select employee_id,first_name,salary,employees.department_id
2 from employees,departments
3 where employees.department_id=departments.department_id
4 and department_name=‘Finance’;

EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID

108 Nancy 12008 100
109 Daniel 9000 100
110 John 8200 100
111 Ismael 7700 100
112 Jose Manuel 7800 100
113 Luis 6900 100

6 rows selected.

Elapsed: 00:00:00.00

17.用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换

例如:

低效:

SELECT DISTINCT E.DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效:

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS ( SELECT ‘X’

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

18.EXPLAIN查看sql语句执行计划(pl/sql developer等软件自带该功能，选中按F5即可)

explain + 语句

内容较多,这边不做详细说明，详情请看我另外一片博客:
explain

19.用索引提高效率

内容较多,这边不做详细说明，详情请看我另外一片博客:

20.基础表的选择

基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问). 根据优化器的不同, SQL语句中基础表的选择是不一样的.

首先需要了解一个概念

21.RBO和CBO的基本概念

Oracle数据库中的优化器又叫查询优化器（Query Optimizer）。它是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。Oracle的优化器有两种，基于规则的优化器（RBO）与基于代价的优化器（CBO)

     RBO: Rule-Based Optimization 基于规则的优化器

     CBO: Cost-Based Optimization 基于代价的优化器

RBO自ORACLE 6以来被采用，一直沿用至ORACLE 9i. ORACLE 10g开始，ORACLE已经彻底丢弃了RBO，它有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说RBO对数据不“敏感”；它根据ORACLE指定的优先顺序规则，对指定的表进行执行计划的选择。比如在规则中，索引的优先级大于全表扫描;RBO是根据可用的访问路径以及访问路径等级来选择执行计划，在RBO中，SQL的写法往往会影响执行计划，它要求开发人员非常了解RBO的各项细则，菜鸟写出来的SQL脚本性能可能非常差。

而基础表的选择
如果你使用的是CBO (COST BASED OPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.

如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的连接条件都有索引对应, 在这种情况下, 基础表就是FROM 子句中列在最后的那个表.

举例:

SELECT A.NAME , B.MANAGER

FROM　WORKER A,

LODGING B

WHERE　A.LODGING = B.LODING;

由于LODGING表的LODING列上有一个索引, 而且WORKER表中没有相比较的索引, WORKER表将被作为查询中的基础表.

CBO优于RBO是因为RBO是一种呆板、过时的优化器，它只认规则，对数据不敏感。毕竟规则是死的，数据是变化的，这样生成的执行计划往往是不可靠的，不是最优的，CBO由于RBO可以从很多方面体现。下面请看一个例子，此案例来自于《让Oracle跑得更快》。

SQL> create table test as select 1 id ,object_name from dba_objects;
 
Table created.
 
SQL> create index idx_test on test(id);
 
Index created.
 
SQL> update test set id=100 where rownum =1;
 
1 row updated.
 
SQL> select id, count(1) from test group by id;
 
        ID   COUNT(1)
---------- ----------
       100          1
         1      50314

从上面可以看出，该测试表的数据分布极其不均衡,ID=100的记录只有一条，而ID=1的记录有50314条。我们先看看RBO下两条SQL的执行计划.

SQL> select /*+ rule */ * from test where id =100;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
 
------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=100)
 
Note
-----
   - rule based optimizer used (consider using cbo)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        588  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

SQL> select /*+ rule */ * from test where id=1;
 
50314 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
 
------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1)
 
Note
-----
   - rule based optimizer used (consider using cbo)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7012  consistent gets
         97  physical reads
          0  redo size
    2243353  bytes sent via SQL*Net to client
      37363  bytes received via SQL*Net from client
       3356  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50314  rows processed

从执行计划可以看出，RBO的执行计划让人有点失望，对于ID=1，几乎所有的数据全部符合谓词条件，走索引只能增加额外的开销（因为ORACLE首先要访问索引数据块，在索引上找到了对应的键值，然后按照键值上的ROWID再去访问表中相应数据），既然我们几乎要访问所有表中的数据，那么全表扫描自然是最优的选择。而RBO选择了错误的执行计划。可以对比一下CBO下SQL的执行计划，显然它对数据敏感，执行计划及时的根据数据量做了调整,当查询条件为1时，它走全表扫描；当查询条件为100时，它走区间索引扫描。如下所示：

SQL> select * from test where id=1;
 
50314 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49075 |  3786K|    52   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49075 |  3786K|    52   (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
         32  recursive calls
          0  db block gets
       3644  consistent gets
          0  physical reads
          0  redo size
    1689175  bytes sent via SQL*Net to client
      37363  bytes received via SQL*Net from client
       3356  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50314  rows processed
 
SQL> select * from test where id =100;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
 
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=100)
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         73  consistent gets
          0  physical reads
          0  redo size
        588  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

22.多个平等的索引

当SQL语句的执行路径可以使用分布在多个表上的多个索引时, ORACLE会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录.

在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引. 然而这个规则只有

当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的.

如果不同表中两个想同等级的索引将被引用, FROM子句中表的顺序将决定哪个会被率先使用. FROM子句中最后的表的索引将有最高的优先级.

如果相同表中两个想同等级的索引将被引用, WHERE子句中最先被引用的索引将有最高的优先级.

举例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.

SELECT ENAME,

FROM EMP

WHERE DEPT_NO = 20

AND EMP_CAT = ‘A’;

这里,DEPTNO索引将被最先检索,然后同EMP_CAT索引检索出的记录进行合并. 执行路径如下:

TABLE ACCESS BY ROWID ON EMP

AND-EQUAL

INDEX RANGE SCAN ON DEPT_IDX

INDEX RANGE SCAN ON CAT_IDX

23.等式比较和范围比较

当WHERE子句中有索引列, ORACLE不能合并它们,ORACLE将用范围比较.

举例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.

SELECT ENAME

FROM EMP

WHERE DEPTNO > 20

AND EMP_CAT = ‘A’;

这里只有EMP_CAT索引被用到,然后所有的记录将逐条与DEPTNO条件进行比较. 执行路径如下:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON CAT_IDX

Knight10zzzz

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
oracle调优（2）

接上篇oracle调优（1）11.用Where子句替换HAVING子句避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的性能开销.HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中例如:低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONGROUP BY REG
复制链接

扫一扫