sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle SQL优化笔记之分析函数改写not exists的语句优化

时间:2016-11-07 09:26   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:开发DBA反映SQL语句执行较慢,结合应用分析该优化方法:分析函数改写not exists的语句后解决该问题。
下面是一条SQL的优化,由于当时时间很急,只忙于SQL信息的收集,没有具体看怎么优化,下面以dba_objects来模拟一下。

客户环境是11.2.0.4,我模拟的环境是11.2.0.3,没有修改过其它的参数。其它的环境不保证会得到相同的结果。

1,环境介绍

oracleplus.net> select * from v$version where rownum<3;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production

oracleplus.net> !lsb_release -a
LSB Version:    :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch
Distributor ID: RedHatEnterpriseAS
Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Release:        4
Codename:       NahantUpdate8

2,创建测试环境

以dba_objects来创建表
create table scott.htz1 as select * from dba_objects;

oracleplus.net> @desc scott.htz1
Name                                      Null     Type
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
NAMESPACE                                          NUMBER
EDITION_NAME                                       VARCHAR2(30)

oracleplus.net> update scott.htz1 set object_id=1 where data_object_id<5000;
1177 rows updated.

oracleplus.net> update scott.htz1 set object_id=2  where data_object_id<6000 and data_object_id>5000;
313 rows updated.

oracleplus.net>  update scott.htz1 set object_id=3  where data_object_id<7000 and data_object_id>6000;
451 rows updated.

oracleplus.net> commit;
Commit complete.

3,原始SQL

这里由于客户环境中SQL走的全表扫瞄,所以这里我没有创建任何的索引。
oracleplus.net> SELECT *
  2    FROM (  SELECT *
  3              FROM scott.htz1 f
  4             WHERE     f.OWNER = 'SYS'
  5                   AND f.OBJECT_TYPE = 'TABLE'
  6                   AND NOT EXISTS
  7                              (SELECT 1
  8                                 FROM scott.htz1 a
  9                                WHERE     a.NAMESPACE = f.NAMESPACE
10                                      AND a.STATUS = f.STATUS
11                                      AND a.object_id < f.object_id
12                                      AND f.OWNER = 'SYS')
13          ORDER BY f.object_id)
14   WHERE ROWNUM < 100;

99 rows selected.

Elapsed: 00:00:04.18
Execution Plan
----------------------------------------------------------
Plan hash value: 1058026417
-------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |    99 | 20493 |       |   673   (1)| 00:00:09 |
|*  1 |  COUNT STOPKEY          |         |       |       |       |            |          |
|   2 |   VIEW                  |         |  1396 |   282K|       |   673   (1)| 00:00:09 |
|*  3 |    SORT ORDER BY STOPKEY|         |  1396 |   331K|   376K|   673   (1)| 00:00:09 |
|*  4 |     HASH JOIN ANTI      |         |  1396 |   331K|       |   598   (1)| 00:00:08 |
|*  5 |      TABLE ACCESS FULL  | HTZ1    |  1403 |   283K|       |   298   (1)| 00:00:04 |
|   6 |      VIEW               | VW_SQ_1 | 75521 |  2655K|       |   299   (1)| 00:00:04 |
|   7 |       TABLE ACCESS FULL | HTZ1    | 75521 |  2286K|       |   299   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<100)
   3 - filter(ROWNUM<100)
   4 - access("ITEM_1"="F"."NAMESPACE" AND "ITEM_2"="F"."STATUS" AND
              "F"."OWNER"="ITEM_4")
       filter("ITEM_3"<"F"."OBJECT_ID")
   5 - filter("F"."OBJECT_TYPE"='TABLE' AND "F"."OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2140  consistent gets
          0  physical reads
          0  redo size
       5475  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         99  rows processed

这里由于有rownum<100,为了验证数据结果是否一致,所以这里我们取消rownum<100的现象,但是在这里多执行计划核心的部分没有影响。
oracleplus.net> SELECT *
  2    FROM (  SELECT *
  3              FROM scott.htz1 f
  4             WHERE     f.OWNER = 'SYS'
  5                   AND f.OBJECT_TYPE = 'TABLE'
  6                   AND NOT EXISTS
  7                              (SELECT 1
  8                                 FROM scott.htz1 a
  9                                WHERE     a.NAMESPACE = f.NAMESPACE
10                                      AND a.STATUS = f.STATUS
11                                      AND a.object_id < f.object_id
12                                      AND f.OWNER = 'SYS')
13          ORDER BY f.object_id);


456 rows selected.
Elapsed: 00:00:04.15
Execution Plan
----------------------------------------------------------
Plan hash value: 3726387066
----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  1396 |   331K|       |   673   (1)| 00:00:09 |
|   1 |  SORT ORDER BY       |         |  1396 |   331K|   376K|   673   (1)| 00:00:09 |
|*  2 |   HASH JOIN ANTI     |         |  1396 |   331K|       |   598   (1)| 00:00:08 |
|*  3 |    TABLE ACCESS FULL | HTZ1    |  1403 |   283K|       |   298   (1)| 00:00:04 |
|   4 |    VIEW              | VW_SQ_1 | 75521 |  2655K|       |   299   (1)| 00:00:04 |
|   5 |     TABLE ACCESS FULL| HTZ1    | 75521 |  2286K|       |   299   (1)| 00:00:04 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="F"."NAMESPACE" AND "ITEM_2"="F"."STATUS" AND
              "F"."OWNER"="ITEM_4")
       filter("ITEM_3"<"F"."OBJECT_ID")
   3 - filter("F"."OBJECT_TYPE"='TABLE' AND "F"."OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2140  consistent gets
          0  physical reads
          0  redo size
      19640  bytes sent via SQL*Net to client
        853  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        456  rows processed
这里看到整个SQL结果的行为456。

下面理解一下此SQL想表达的意思,不过在生产环境中,建议去找开发了解。此SQL想返回htz1表中owner=’SYS’时按namespace,status分组中取object_id的最小值,并且f.object_type=’TABLE’

4,改写的SQL

改写后的SQL:
oracleplus.net> SELECT *
  2    FROM (SELECT a.*,
  3                 RANK ()
  4                 OVER (PARTITION BY a.namespace, a.status ORDER BY a.object_id)
  5                    rownum1
  6            FROM scott.htz1 a
  7           WHERE a.owner = 'SYS') b
  8   WHERE b.object_type = 'TABLE' AND rownum1 = 1;


456 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 886518679
-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      | 36297 |  7798K|       |  1934   (1)| 00:00:24 |
|*  1 |  VIEW                    |      | 36297 |  7798K|       |  1934   (1)| 00:00:24 |
|*  2 |   WINDOW SORT PUSHED RANK|      | 36297 |  7337K|  8808K|  1934   (1)| 00:00:24 |
|*  3 |    TABLE ACCESS FULL     | HTZ1 | 36297 |  7337K|       |   299   (1)| 00:00:04 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"."OBJECT_TYPE"='TABLE' AND "ROWNUM1"=1)
   2 - filter(RANK() OVER ( PARTITION BY "A"."NAMESPACE","A"."STATUS" ORDER BY
              "A"."OBJECT_ID")<=1)
   3 - filter("A"."OWNER"='SYS')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1070  consistent gets
          0  physical reads
   Oracle oracleplus.net       0  redo size
      19707  bytes sent via SQL*Net to client
        853  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        456  rows processed

5,效果对比

下面是优化前后的对表,由于表小,对比的效果不是很明显,如果在几十G的表的时候,效果会很明显的。
逻辑读
消耗时间
优化前
2140
04.15
优化后
1070
00.06
本文固定链接: http://www.htz.pw/2015/04/24/%e5%88%86%e6%9e%90%e5%87%bd%e6%95%b0%e6%94%b9%e5%86%99%e4%b8%80%e6%9d%a1not-exists%e8%af%ad%e5%8f%a5%e4%bc%98%e5%8c%96.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle SQL优化笔记之分析函数改写not exists的语句优化

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1193.html

Oracle研究中心

关键词:

Oracle SQL优化

分析函数改写not exists的语句优化笔记

Oracle SQL改为not exists优化笔记