加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

在Oracle 12c中进行子选择,性能不佳

发布时间：2020-12-12 16:29:32 所属栏目：百科 来源：网络整理
导读：从Oracle 11g迁移到12c之后,我们遇到了一个巨大的性能问题,例如使用以下select语句.同样的声明在11g中运行良好. 表和索引 -- Create tablecreate table PS_CS_ADRART_TB( cs_adressmandant VARCHAR2(5) not null,cs_person_id VARCHAR2(20) not null,cs_bezi
从Oracle 11g迁移到12c之后,我们遇到了一个巨大的性能问题,例如使用以下select语句.同样的声明在11g中运行良好.

表和索引

-- Create table
create table PS_CS_ADRART_TB
(
  cs_adressmandant   VARCHAR2(5) not null,cs_person_id       VARCHAR2(20) not null,cs_beziehung_id    VARCHAR2(20) not null,seqnum             INTEGER not null,effdt              DATE,eff_status         VARCHAR2(1) not null,cs_adrart_cd       VARCHAR2(20) not null,cs_adress_id       VARCHAR2(20) not null,cs_kdnr_as400      VARCHAR2(8) not null,cs_plzgk           VARCHAR2(11) not null,cs_plz_pf          VARCHAR2(15) not null,cs_aendgr_cd       VARCHAR2(20) not null,cs_datasource_cd   VARCHAR2(20) not null,cs_betrag          NUMBER(14,4) not null,cs_belegdat        DATE,cs_adrtyp_xl       VARCHAR2(2) not null,cs_checked         VARCHAR2(1) not null,cs_journal_xl      VARCHAR2(4) not null,address2           VARCHAR2(55) not null,row_added_dttm     TIMESTAMP(6),row_added_oprid    VARCHAR2(30) not null,row_lastmant_dttm  TIMESTAMP(6),row_lastmant_oprid VARCHAR2(30) not null,cs_recstat_xl      VARCHAR2(4) not null,cs_update_count    NUMBER(10) not null
)
tablespace CS_APP
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 102416K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create unique index PSACS_ADRART_TB on PS_CS_ADRART_TB (CS_ADRESSMANDANT,CS_KDNR_AS400,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 20M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PSBCS_ADRART_TB on PS_CS_ADRART_TB (CS_PERSON_ID)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 20M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PSCCS_ADRART_TB on PS_CS_ADRART_TB (CS_BEZIEHUNG_ID)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 20M
    next 1M
    minextents 1
    maxextents unlimited
  );
create unique index PS_CS_ADRART_TB on PS_CS_ADRART_TB (CS_ADRESSMANDANT,CS_PERSON_ID,CS_BEZIEHUNG_ID,SEQNUM,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 20M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PSDCS_ADRART_TB on PS_CS_ADRART_TB (CS_PLZ_PF)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 40K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PS0CS_ADRART_TB on PS_CS_ADRART_TB (CS_ADRESS_ID,CS_ADRESSMANDANT,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 20M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PS1CS_ADRART_TB on PS_CS_ADRART_TB (CS_KDNR_AS400,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 20M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PS2CS_ADRART_TB on PS_CS_ADRART_TB (ROW_ADDED_DTTM,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 40K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PS3CS_ADRART_TB on PS_CS_ADRART_TB (ROW_ADDED_OPRID,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 40K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PS4CS_ADRART_TB on PS_CS_ADRART_TB (ROW_LASTMANT_DTTM,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 40K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PS5CS_ADRART_TB on PS_CS_ADRART_TB (ROW_LASTMANT_OPRID,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 40K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PS6CS_ADRART_TB on PS_CS_ADRART_TB (CS_RECSTAT_XL,EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 40K
    next 1M
    minextents 1
    maxextents unlimited
  );

表大小

select count(*) from ps_cs_adrart_tb a 
--> 41367270

选择声明

SELECT A.CS_ADRESS_ID,A.SEQNUM,TO_CHAR(A.EFFDT,'YYYY-MM-DD')
      from PS_CS_ADRART_TB A
    where A.CS_ADRESSMANDANT = '001'
       and a.cs_kdnr_as400 = '63916917'
       and a.effdt =
           (select max(b.effdt)
              from ps_cs_adrart_tb b
             where b.cs_adressmandant = a.cs_adressmandant 
               and b.cs_person_id = a.cs_person_id 
               and b.cs_beziehung_id = a.cs_beziehung_id
               and b.seqnum = a.seqnum
               and b.effdt  <= trunc(sysdate)
               )

解释计划Oracle 11g

--------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name            | Rows | Bytes | Cost | Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                 |    1 |    89 |    2 | 00:00:01 |
    | * 1 |   FILTER                        |                 |      |       |      |          |
    |   2 |    TABLE ACCESS BY INDEX ROWID  | PS_CS_ADRART_TB |    1 |    89 |    1 | 00:00:01 |
    | * 3 |     INDEX RANGE SCAN            | PSACS_ADRART_TB |    1 |       |    1 | 00:00:01 |
    |   4 |    SORT AGGREGATE               |                 |    1 |    59 |      |          |
    |   5 |     FIRST ROW                   |                 |    1 |    59 |    1 | 00:00:01 |
    | * 6 |      INDEX RANGE SCAN (MIN/MAX) | PS_CS_ADRART_TB |    1 |    59 |    1 | 00:00:01 |
    --------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 1 - filter("A"."EFFDT"= (SELECT MAX("B"."EFFDT") FROM "PS_CS_ADRART_TB" "B" WHERE "B"."EFFDT"<=TRUNC(SYSDATE@!) AND "B"."SEQNUM"=:B1 AND "B"."CS_BEZIEHUNG_ID"=:B2 AND "B"."CS_PERSON_ID"=:B3 AND
      "B"."CS_ADRESSMANDANT"=:B4))
    * 3 - access("A"."CS_ADRESSMANDANT"='001' AND "A"."CS_KDNR_AS400"='63916917')
    * 6 - access("B"."CS_ADRESSMANDANT"=:B1 AND "B"."CS_PERSON_ID"=:B2 AND "B"."CS_BEZIEHUNG_ID"=:B3 AND "B"."SEQNUM"=:B4 AND "B"."EFFDT"<=TRUNC(SYSDATE@!))

解释计划Oracle 12c

------------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name            | Rows     | Bytes     | Cost  | Time     |
    ------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                 |        1 |       140 | 34366 | 00:00:02 |
    | * 1 |   HASH JOIN                            |                 |        1 |       140 | 34366 | 00:00:02 |
    |   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | PS_CS_ADRART_TB |        1 |        89 |     1 | 00:00:01 |
    | * 3 |     INDEX RANGE SCAN                   | PS1CS_ADRART_TB |        1 |           |     1 | 00:00:01 |
    |   4 |    VIEW                                | VW_SQ_1         |    41889 |   2136339 | 34365 | 00:00:02 |
    | * 5 |     FILTER                             |                 |          |           |       |          |
    |   6 |      HASH GROUP BY                     |                 |    41889 |   2471451 | 34365 | 00:00:02 |
    | * 7 |       INDEX RANGE SCAN                 | PS_CS_ADRART_TB | 12746381 | 752036479 | 34365 | 00:00:02 |
    ------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 1 - access("A"."EFFDT"="MAX(B.EFFDT)" AND "ITEM_1"="A"."CS_ADRESSMANDANT" AND "ITEM_2"="A"."CS_PERSON_ID" AND "ITEM_3"="A"."CS_BEZIEHUNG_ID" AND "ITEM_4"="A"."SEQNUM")
    * 3 - access("A"."CS_KDNR_AS400"='63916917' AND "A"."CS_ADRESSMANDANT"='001')
    * 5 - filter('001'='001')
    * 7 - access("B"."CS_ADRESSMANDANT"='001' AND "B"."EFFDT"<=TRUNC(SYSDATE@!))
    * 7 - filter("B"."EFFDT"<=TRUNC(SYSDATE@!))


    Note
    -----
    - dynamic sampling used for this statement

如您所见,从索引PS_CS_ADRART_TB只有CS_ADRESSMANDANT和EFFDT用于过滤数据,这是非常糟糕的.

使用以下略有不同的select语句,Oracle 12c将按预期使用索引来确定subselect的数据.

改变外部选择的条件

SELECT  A.CS_ADRESS_ID,'YYYY-MM-DD')
    from PS_CS_ADRART_TB A
  where a.cs_kdnr_as400 = '53916917' -- without CS_ADRESSMANDANT condition
     and a.effdt =
         (select  max(b.effdt)
            from ps_cs_adrart_tb b
           where b.cs_adressmandant = a.cs_adressmandant and 
                 b.cs_person_id = a.cs_person_id 
             and b.cs_beziehung_id = a.cs_beziehung_id 
             and b.seqnum = a.seqnum 
             and b.effdt <= trunc(sysdate)
             )

    --------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name            | Rows | Bytes | Cost | Time     |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                 |    1 |    89 |    2 | 00:00:01 |
    |   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | PS_CS_ADRART_TB |    1 |    89 |    1 | 00:00:01 |
    | * 2 |    INDEX RANGE SCAN                   | PS1CS_ADRART_TB |    1 |       |    1 | 00:00:01 |
    |   3 |     SORT AGGREGATE                    |                 |    1 |    59 |      |          |
    |   4 |      FIRST ROW                        |                 |    1 |    59 |    1 | 00:00:01 |
    | * 5 |       INDEX RANGE SCAN (MIN/MAX)      | PS_CS_ADRART_TB |    1 |    59 |    1 | 00:00:01 |
    --------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 2 - access("A"."CS_KDNR_AS400"='53916917')
    * 2 - filter("A"."EFFDT"= (SELECT MAX("B"."EFFDT") FROM "PS_CS_ADRART_TB" "B" WHERE "B"."EFFDT"<=TRUNC(SYSDATE@!) AND "B"."SEQNUM"=:B1 AND "B"."CS_BEZIEHUNG_ID"=:B2 AND "B"."CS_PERSON_ID"=:B3 AND
      "B"."CS_ADRESSMANDANT"=:B4))
    * 5 - access("B"."CS_ADRESSMANDANT"=:B1 AND "B"."CS_PERSON_ID"=:B2 AND "B"."CS_BEZIEHUNG_ID"=:B3 AND "B"."SEQNUM"=:B4 AND "B"."EFFDT"<=TRUNC(SYSDATE@!))


    Note
    -----
    - dynamic sampling used for this statement

在subselect中使用min()而不是max()

SELECT A.CS_ADRESS_ID,'YYYY-MM-DD'),a.cs_person_id 
      from PS_CS_ADRART_TB A
    where a.cs_kdnr_as400 = '63916917' and a.cs_adressmandant = '001'
       and a.effdt =
           (select  min(b.effdt)
              from ps_cs_adrart_tb b
             where b.cs_adressmandant = a.cs_adressmandant
               and b.cs_person_id = a.cs_person_id
               and b.cs_beziehung_id = a.cs_beziehung_id
               and b.seqnum = a.seqnum
               and b.effdt  <= sysdate
               );

    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name            | Rows | Bytes | Cost | Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                 |    1 |   109 |    2 | 00:00:01 |
    |   1 |   NESTED LOOPS                         |                 |    1 |   109 |    2 | 00:00:01 |
    |   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | PS_CS_ADRART_TB |    1 |    89 |    1 | 00:00:01 |
    | * 3 |     INDEX RANGE SCAN                   | PS1CS_ADRART_TB |    1 |       |    1 | 00:00:01 |
    | * 4 |    VIEW PUSHED PREDICATE               | VW_SQ_1         |    1 |    20 |    1 | 00:00:01 |
    |   5 |     SORT GROUP BY                      |                 |    1 |    59 |    1 | 00:00:01 |
    | * 6 |      INDEX RANGE SCAN                  | PS_CS_ADRART_TB |    1 |    59 |    1 | 00:00:01 |
    ---------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 3 - access("A"."CS_KDNR_AS400"='63916917' AND "A"."CS_ADRESSMANDANT"='001')
    * 4 - filter("A"."EFFDT"="MIN(B.EFFDT)" AND "ITEM_1"="A"."CS_ADRESSMANDANT")
    * 6 - access("B"."CS_ADRESSMANDANT"='001' AND "B"."CS_PERSON_ID"="A"."CS_PERSON_ID" AND "B"."CS_BEZIEHUNG_ID"="A"."CS_BEZIEHUNG_ID" AND "B"."SEQNUM"="A"."SEQNUM" AND "B"."EFFDT"<=SYSDATE@!)


    Note
    -----
    - dynamic sampling used for this statement

重建索引(如上所述)并按以下方式更新统计信息

EXEC DBMS_STATS.delete_table_stats('SYSADM','PS_CS_ADRART_TB');
-- EXEC DBMS_STATS.gather_table_stats('SYSADM','PS_CS_ADRART_TB');
EXEC DBMS_STATS.gather_table_stats(ownname => 'SYSADM',tabname =>  'PS_CS_ADRART_TB',cascade => true );

没有帮助.

这里发生了什么？任何建议都非常感谢.提前致谢.

我认为你应该将参数_unnest_subquery设置为FALSE.

你可以尝试一下

alter session set "_unnest_subquery" = FALSE;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同