Subselect in Oracle 12c with bad performance

6
votes

After migration from Oracle 11g to 12c we have a massive performance issue for example with the following select statement. The same statement works fine in 11g.

Table and Indexes

-- 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, 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 PS1CS_ADRART_TB on PS_CS_ADRART_TB (CS_KDNR_AS400, 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 PS2CS_ADRART_TB on PS_CS_ADRART_TB (ROW_ADDED_DTTM, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, 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, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, 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, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, 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, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, 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, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT)
  tablespace PSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 40K
    next 1M
    minextents 1
    maxextents unlimited
  );

Table Size

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

Select Statement

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

Explain Plan 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@!))

Explain Plan 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

As you can see, from the index PS_CS_ADRART_TB only CS_ADRESSMANDANT and EFFDT are used to filter the data and that is quite bad.

With the following slightly different select statements the index is used by Oracle 12c as expected to determine data of subselect.

Changing where condition of outer select

  SELECT  A.CS_ADRESS_ID, A.SEQNUM, TO_CHAR(A.EFFDT, '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

Using min() instead of max() in the subselect

    SELECT A.CS_ADRESS_ID, A.SEQNUM, TO_CHAR(A.EFFDT, '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

Rebuilding indexes (as described above) and updating statistics by

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

doesn't help.

What is going on here? Any advice is highly appreciated. Thanks in advance.

2
oracleoracle11goracle12c
Please let me know why you down vote the question. – Frank Ockenfuss
The question is clear, well tagged, contains all the statements to build the needed structures, gives informations on the size of the tables, contains many attempts and the plan for each statement... I can't figure what else should this kind of question contain... +1 – Aleksej
The "dynamic sampling used for this statement in the 12c plan seems to indicate that the statistics are not up-to-date despite the gather_table_stats(). Does this change if you call gather_table_stats() with cascade => true? (And to rule out the obvious: is SYSADM really the owner of those tables?) – a_horse_with_no_name
@a_horse_with_no_name We called DBMS_STATS.gather_table_stats with option cascade => true with the same result. And yes, SYSADM is the owner of the table. The database is an upgraded copy of our Oracle 11g database. – Frank Ockenfuss
What is the value of the parameter OPTIMIZER_DYNAMIC_SAMPLING in both databases? – Rob van Wijk

2 Answers

1
votes

I think you should set the Parameter _unnest_subquery to FALSE.

You can try it with

alter session set "_unnest_subquery" = FALSE;
0
votes

Frank. As I understand you want to get one last row by any cs_person_id and cs_beziehung_id where b.effdt lower or equal than today. First of all you may gather statistic with information about selectivity. It helps CBO to make choice. 

BEGIN 
 dbms_stats.Gather_table_stats('SYSADM', 'PS_CS_ADRART_TB',
 method_opt => 'FOR COLUMNS SIZE 254 CS_KDNR_AS400 CS_ADRESSMANDANT'); 
END; 
/

Anothr one think what I find. You have a lot of indexes witch might be created as unique. It already contain unique index columns. 

create unique index PS1CS_ADRART_TB on PS_CS_ADRART_TB (CS_KDNR_AS400, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT)  ;
create unique index PS0CS_ADRART_TB on PS_CS_ADRART_TB (CS_ADRESS_ID, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT);
create unique index PS1CS_ADRART_TB on PS_CS_ADRART_TB (CS_KDNR_AS400, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT);
create unique index PS2CS_ADRART_TB on PS_CS_ADRART_TB (ROW_ADDED_DTTM, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT);
create unique index PS3CS_ADRART_TB on PS_CS_ADRART_TB (ROW_ADDED_OPRID, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT);
create unique index PS4CS_ADRART_TB on PS_CS_ADRART_TB (ROW_LASTMANT_DTTM, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT);
create unique index PS5CS_ADRART_TB on PS_CS_ADRART_TB (ROW_LASTMANT_OPRID, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT);
create unique index PS6CS_ADRART_TB on PS_CS_ADRART_TB (CS_RECSTAT_XL, CS_ADRESSMANDANT, CS_PERSON_ID, CS_BEZIEHUNG_ID, SEQNUM, EFFDT);

And at the end I try to rewrite query to exclude subquery

SELECT cs_adress_id,seqnum, effdt_chr 
FROM   (SELECT /* */ a.cs_adress_id 
          ,a.seqnum 
          ,to_char(a.effdt, 'YYYY-MM-DD') AS effdt_chr 
          ,dense_rank() over(PARTITION BY a.cs_adressmandant, a.cs_person_id
                                        , a.cs_beziehung_id, a.seqnum 
                                 ORDER BY a.effdt DESC) AS  rnk 
    FROM   ps_cs_adrart_tb a 
    WHERE  a.cs_adressmandant = '001' 
      AND  a.cs_kdnr_as400 = '63916917' 
      AND  a.effdt <= trunc(sysdate) 
) 
WHERE  rnk = 1;