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.
I think you should set the Parameter _unnest_subquery to FALSE.
You can try it with
alter session set "_unnest_subquery" = FALSE;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments