OraCsdP: Help the Optimizer when it is Wrong : some examples

0] Performance with condition on Dates

Compare this :

FULL SCAN

select count(*) from TICK_OWN.TICK_TAB where to_char(date_heure_debut,'RRRRMM')='201404' – 1118937 rows 50 s RANGE ALL et FULL

INDEX USED

select count(1) from TICK_OWN.TICK_TAB where DATE_HEURE_DEBUT between TO_DATE('2014-04-01','YYYY-MM-DD') AND TO_DATE('2014-04-01','YYYY-MM-DD') + INTERVAL '1' MONTH -- 1118937 rows Less than one second RANGE ITERATOR ET IDX

1] Bad Statistics

First of all, have a look to the Statistics ( If they are right, then we can use the “Magical List” below ).

1.1 INSERT never ends [ easy ]

DAY	SQL_ID	PLAN_HASH_VALUE	MODULE	ELAPSED(min)	ROWS
01 08	agtfcvybvdkjc	2357812205	SQL*Plus	375,4	0
01 09	agtfcvybvdkjc	2357812205	SQL*Plus	659,65	0
01 09	agtfcvybvdkjc	4106027982	SQL*Plus	0,21	1563

Plan KO

SQL_ID agtfcvybvdkjc

--------------------

INSERT INTO SCH01.LISTE_BENEFICIAIRES_APAD SELECT DISTINCT

Plan hash value: 2357812205

--------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | | | 100 (100)| |

| 1 | LOAD TABLE CONVENTIONAL | | | | | |

| 2 | HASH UNIQUE | | 1 | 230 | 100 (3)| 00:00:02 |

| 27 | TABLE ACCESS FULL| TEMP_PREST_TABLE | 1 | 13 | 2 (0)| 00:00:01 |

| 28 | BUFFER SORT | | 240K| 1411K| 49 (3)| 00:00:01 |

Stats are different at the beginning of the INSert on one table of the Select part :

Table SCH01.TEMP_PREST_TABLE

Jan 08 17:30 : 0

Jan 09 11:00 : 14634

We often see tables emptied, then loaded, but no stats calculated -> Set Stats to correct value, for example, using a restore :

-- vs Jan 08 17:30

exec DBMS_STATS.RESTORE_TABLE_STATS( ownname => 'SCH01', tabname => 'TEMP_PREST_TABLE', as_of_timestamp => to_timestamp('08-01-2013 17:30:00','DD-MM-YYYY hh24:mi:ss'), no_invalidate => FALSE ) ;

-- 14634 VS 0

1.2 FTS instead of Index on Partitions

Index aren’t used anymore !!

1. Problem : Full Table Scan on Tables Partition 2013

PLAN_TABLE_OUTPUT

SQL_ID 49acywubw67fj, child number 0

-------------------------------------

SELECT pt.rowid,pt.psecid,pt.ps_secid,pt.imputation,pt.absid,pt.dat,pt.heure,pt.minut,pt.se

conde,pt.es,pt.datp,pt.heurep,pt.minutp,pt.secondep,pt.esp,pt.dats,pt.heures,pt.minuts,pt.s

econdes,pt.ess,pt.term,pt.lisf,pt.rang,pt.gencal,pt.provenance,pt.usersign,pt.datesign,pt.h

eursign FROM pt WHERE (matric=:v0 and dat>=:v1 and dat<=:v2 ) ORDER BY

dats,heures,minuts,secondes

Plan hash value: 3680625233

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 125 (100)| | | |

| 1 | SORT ORDER BY | | 6 | 630 | 125 (2)| 00:00:02 | | |

|* 2 | FILTER | | | | | | | |

| 3 | PARTITION RANGE ITERATOR| | 6 | 630 | 124 (1)| 00:00:02 | KEY | KEY |

|* 4 | TABLE ACCESS FULL | PT | 6 | 630 | 124 (1)| 00:00:02 | KEY | KEY |

---------------------------------------------------------------------------------------------------

SQL_ID CHILD_NUMBER NAME POSITION DATATYPE_STRING VALUE_STRING LAST_CAPTURED

49acywubw67fj 0 :V0 1 NUMBER 905978 07/01/2013 10:37:10

49acywubw67fj 0 :V1 2 DATE 01/01/13 00:00:00 07/01/2013 10:37:10

49acywubw67fj 0 :V2 3 DATE 01/02/13 00:00:00 07/01/2013 10:37:10

2. Set Stats to a sufficient value to avoid FTS

exec dbms_stats.set_table_stats( ownname => 'OWNER1', tabname => 'PT', partname => 'P1_2013', stattab => null, statid => null, numrows => 3000000, numblks => 50000, avgrlen => 100);

3. Lock table Stats

exec dbms_stats.lock_table_stats( ownname => 'OWNER1', tabname => 'PT');

2] Magical Hints List /*+ … */

OPT_PARAM('OPTIMIZER_FEATURES_ENABLE','10.2.0.5') */

OPT_PARAM('optimizer_index_cost_adj' , 0)

OPT_PARAM('_b_tree_bitmap_plans' 'false')

OPT_PARAM('star_transformation_enabled' 'true')

OPTIMIZER_FEATURES_ENABLE('10.2.0.5')

OPT_PARAM('_optimizer_mjc_enabled' 'false')

Avoid View Pushed Predicate -> /*+ NO_PUSH_PRED */ &

opt_param('_optimizer_cost_based_transformation','off')

alter session set "_optimizer_undo_cost_change"='10.1.0.3' ;

alter session set "_gby_hash_aggregation_enabled" = FALSE;
alter session set "_new_initial_join_orders"=false ;

3] Give the Index a Chance

3.1 Index promotion or Creation

1. Promote the Index with : OPT_PARAM('optimizer_index_cost_adj' , 0)

2. Select : 15 Hours goes down to 2 minutes

SELECT T_LIGNE_FACTURE.ID_SOCIETEOVD AS critereIntermodal2,T_LIGNE_FACTURE.ORIGINE,T_LIGNE_FACTURE.DESTINATION,

SUM (T_LIGNE_FACTURE.DEBIT) AS DEBIT FROM T_OPERATION , T_LIGNE_FACTURE , T_TAB01 , T_FACTURE

WHERE (T_LIGNE_FACTURE.CODE_TYPE_OPERATION IN (40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 70, 71, 105, 106) )

AND T_LIGNE_FACTURE.CODE_TYPE_OPERATION = T_OPERATION.CODE_TYPE_OPERATION

AND EXISTS ( SELECT t_tab01_vte_secu.code_session FROM t_tab01_vte_secu,T_FACTURE

WHERE (t_tab01_vte_secu.id_societe_session IN (209) OR t_tab01_vte_secu.id_societe_ligne IN (209))

AND t_facture.code_session = t_tab01_vte_secu.code_session AND t_ligne_facture.id_facture = t_facture.id_facture )

AND T_FACTURE.CODE_SESSION=T_TAB01.CODE_SESSION and T_LIGNE_FACTURE.ID_FACTURE=T_FACTURE.ID_FACTURE

AND ((T_TAB01.HEURE_DATE_OUV>=to_date('01/09/2012 00:00','DD/MM/YYYY HH24:MI:SS')

AND T_TAB01.HEURE_DATE_OUV<=to_date('30/09/2012 23:59:59','DD/MM/YYYY HH24:MI:SS')))

AND T_FACTURE.CODE_SESSION=T_TAB01.CODE_SESSION and T_LIGNE_FACTURE.ID_FACTURE=T_FACTURE.ID_FACTURE

AND ((T_TAB01.ID_UTILISATEUR<>999 OR T_TAB01.ID_SOCIETE<>209)) AND ((T_LIGNE_FACTURE.CODE_PRODUIT=302

AND T_LIGNE_FACTURE.ID_SOCIETE=1)) AND T_FACTURE.CODE_SESSION=T_TAB01.CODE_SESSION and T_LIGNE_FACTURE.ID_FACTURE=T_FACTURE.ID_FACTURE

AND ((T_TAB01.ID_SOCIETE=209)) AND ((T_LIGNE_FACTURE.CODE_LIGNE1=1089 AND T_LIGNE_FACTURE.ID_SOCIETEOVD=1))

GROUP BY T_LIGNE_FACTURE.ORIGINE,T_LIGNE_FACTURE.DESTINATION,T_LIGNE_FACTURE.ID_SOCIETEOVD ORDER BY T_LIGNE_FACTURE.ORIGINE,T_LIGNE_FACTURE.DESTINATION

|* 11 | TABLE ACCESS FULL | T_TAB01_VTE_SECU | 1 | 12 | 1318 (2)| 00:00:16 |

-- direct path read

11 - filter(("T_TAB01_VTE_SECU"."ID_SOCIETE_SESSION"=209 OR

"T_TAB01_VTE_SECU"."ID_SOCIETE_LIGNE"=209) AND "T_FACTURE"."CODE_SESSION"="T_TAB01_VTE_SECU"."CODE_SESSION")

Index is Selective :

select count(1) from ( select distinct code_session from t_tab01_vte_secu order by 1 ) -- 106348 / 1.4 MRows

create index <user>.t_tab01_vte_sec_i1 on SCH1.T_TAB01_VTE_SECU(CODE_SESSION) TABLESPACE SBIIDX ;

3.2 Create Index & Hints

1. Update : 3 hours goes down to 10 minutes

The elapse time for the Update statement was 3 hours.

explain plan for

UPDATE usr_creation_carte s1

SET no_ligne_od =

(SELECT RTRIM (i.iti_cod_itinerair)

|| ' ('

|| RTRIM (c.csi_cod_secondair)

|| ')'

FROM usr_creation_carte s2,

itineraire i,

code_second_iti c,

arr_iti a1,

arr_iti a2,

phy_log p1,

phy_log p2

WHERE s1.no_arret_o1 = s2.no_arret_o1

AND s1.no_arret_d1 = s2.no_arret_d1

AND s2.no_arret_o1 = TO_NUMBER (p1.pl_cod_arret)

AND s2.no_arret_d1 = TO_NUMBER (p2.pl_cod_arret)

AND a1.ai_cod_arret = p1.pl_cod_arret_as

AND a2.ai_cod_arret = p2.pl_cod_arret_as

AND a1.ai_cod_itinerair = a2.ai_cod_itinerair

AND a1.ai_cod_itinerair = i.iti_cod_itinerair

AND i.iti_ligne = 'O'

AND NVL (i.iti_dat_finvalid, '99999999') >=

TO_NUMBER ( TO_CHAR (SYSDATE, 'YYYY')

|| TO_CHAR (SYSDATE, 'MM')

|| TO_CHAR (SYSDATE, 'DD')

)

AND i.iti_cod_itinerair = c.csi_cod_itinerair(+)

AND i.iti_dat_debvalid = c.csi_dat_debvalid(+)

AND i.iti_cod_itinerair <> '5000'

AND ROWNUM <= 1)

Plan hash value: 1358736967

--------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 15704 | 322K| 89M (1)|296:51:30 |

| 2 | TABLE ACCESS FULL | USR_CREATION_CARTE | 15704 | 322K| 69 (2)| 00:00:01 |

|* 3 | COUNT STOPKEY | | | | | |

| 4 | NESTED LOOPS | | 4 | 504 | 5671 (1)| 00:01:09 |

| 5 | MERGE JOIN CARTESIAN | | 261 | 28449 | 5148 (1)| 00:01:02 |

| 6 | MERGE JOIN CARTESIAN | | 24 | 2328 | 4545 (1)| 00:00:55 |

| 7 | NESTED LOOPS OUTER | | 12 | 1056 | 3744 (1)| 00:00:45 |

| 8 | NESTED LOOPS | | 12 | 684 | 3732 (1)| 00:00:45 |

| 9 | NESTED LOOPS | | 209 | 6061 | 3314 (1)| 00:00:40 |

|* 10 | INDEX FULL SCAN | PHY_LOG_PK | 11 | 132 | 25 (0)| 00:00:01 |

|* 11 | INDEX FULL SCAN | ARR_ITI_PK | 19 | 323 | 299 (1)| 00:00:04 |

|* 12 | TABLE ACCESS BY INDEX ROWID| ITINERAIRE | 1 | 28 | 2 (0)| 00:00:01 |

|* 13 | INDEX RANGE SCAN | ITINERAIRE_FK4 | 1 | | 1 (0)| 00:00:01 |

|* 14 | INDEX RANGE SCAN | CODE_SECOND_ITI_PK | 1 | 31 | 1 (0)| 00:00:01 |

| 15 | BUFFER SORT | | 2 | 18 | 4544 (1)| 00:00:55 |

|* 16 | TABLE ACCESS FULL | USR_CREATION_CARTE | 2 | 18 | 67 (2)| 00:00:01 |

| 17 | BUFFER SORT | | 11 | 132 | 5082 (1)| 00:01:01 |

|* 18 | INDEX FULL SCAN | PHY_LOG_PK | 11 | 132 | 25 (0)| 00:00:01 |

|* 19 | INDEX RANGE SCAN | ARR_ITI_PK | 1 | 17 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------------

We seek for the steps where cost increases : FTS

Step 16 - filter("S2"."NO_ARRET_O1"=:B1 AND "S2"."NO_ARRET_D1"=:B2)

ð Add 2 Index

create index usr_creation_carte_i1 on usr_creation_carte ( no_arret_o1 )

create index usr_creation_carte_i2 on usr_creation_carte ( no_arret_d1 )

Watch for tables with most number of rows : do their index are on the right columns, the ones with join that cost most.

ARR_ITI 45 000 rows

Step 11 - access("A1"."AI_COD_ARRET"="P1"."PL_COD_ARRET_AS")

filter("A1"."AI_COD_ARRET"="P1"."PL_COD_ARRET_AS")

ð Add 1 Index

create index arr_iti_i1 on arr_iti ( ai_cod_arret )

We got 10 minutes.

Plan hash value: 3545033266

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 15704 | 322K| 35M (1)|118:18:14 |

| 2 | TABLE ACCESS FULL | USR_CREATION_CARTE | 15704 | 322K| 69 (2)| 00:00:01 |

|* 3 | COUNT STOPKEY | | | | | |

| 4 | NESTED LOOPS | | 4 | 504 | 2260 (1)| 00:00:28 |

| 5 | MERGE JOIN CARTESIAN | | 261 | 28449 | 1737 (1)| 00:00:21 |

| 6 | NESTED LOOPS OUTER | | 24 | 2328 | 1134 (1)| 00:00:14 |

| 7 | NESTED LOOPS | | 24 | 1584 | 1110 (1)| 00:00:14 |

| 8 | NESTED LOOPS | | 414 | 15732 | 281 (1)| 00:00:04 |

| 9 | MERGE JOIN CARTESIAN | | 22 | 462 | 53 (2)| 00:00:01 |

|* 10 | VIEW | index$_join$_002 | 2 | 18 | 3 (34)| 00:00:01 |

|* 11 | HASH JOIN | | | | | |

|* 12 | INDEX RANGE SCAN | USR_CREATION_CARTE_I1 | 2 | 18 | 1 (0)| 00:00:01 |

|* 13 | INDEX RANGE SCAN | USR_CREATION_CARTE_I2 | 2 | 18 | 1 (0)| 00:00:01 |

| 14 | BUFFER SORT | | 11 | 132 | 50 (0)| 00:00:01 |

|* 15 | INDEX FULL SCAN | PHY_LOG_PK | 11 | 132 | 25 (0)| 00:00:01 |

| 16 | TABLE ACCESS BY INDEX ROWID| ARR_ITI | 19 | 323 | 18 (0)| 00:00:01 |

|* 17 | INDEX RANGE SCAN | ARR_ITI_I1 | 19 | | 1 (0)| 00:00:01 |

|* 18 | TABLE ACCESS BY INDEX ROWID | ITINERAIRE | 1 | 28 | 2 (0)| 00:00:01 |

|* 19 | INDEX RANGE SCAN | ITINERAIRE_FK4 | 1 | | 1 (0)| 00:00:01 |

|* 20 | INDEX RANGE SCAN | CODE_SECOND_ITI_PK | 1 | 31 | 1 (0)| 00:00:01 |

| 21 | BUFFER SORT | | 11 | 132 | 1736 (1)| 00:00:21 |

|* 22 | INDEX FULL SCAN | PHY_LOG_PK | 11 | 132 | 25 (0)| 00:00:01 |

|* 23 | INDEX RANGE SCAN | ARR_ITI_PK | 1 | 17 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------------

Note : I had another performance problem on that one.

To get a correct execution plan, I had to fix the Join order as follow :

/* LEADING */ Hint – specifies the set of tables to be used as the prefix in the execution plan.

The "leading" hint is ignored if the tables specified cannot be joined first in the order specified

If you specify two or more conflicting LEADING hints – all of them are ignored

The ORDERED hint overrides all LEADING hints

/* ORDERED */ Hint – Oracle joins tables in the order in which they appear in the FROM clause

The optimizer normally chooses the order in which to join the tables, but it's time-consuming and wrong if you have bad CBO stats (especially histograms)

You may want to specify the "ordered" hint if you know something about the number of rows selected from each table that the optimizer does not

The Oracle documentation notes the difference between the "ordered" and "leading" hints:

The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.

update USR_CREATION_CARTE s1 set NO_LIGNE_OD =

(select /*+ LEADING(S2, P1, A1, I, C, P2, A2) */

rtrim(i.ITI_COD_ITINERAIR) || ' (' || rtrim(c.CSI_COD_SECONDAIR) || ')'

from USR_CREATION_CARTE s2, ITINERAIRE i, CODE_SECOND_ITI c, ARR_ITI a1, ARR_ITI a2, PHY_LOG p1, PHY_LOG p2

where s1.NO_ARRET_O1 = s2.NO_ARRET_O1

and s1.NO_ARRET_D1 = s2.NO_ARRET_D1

and s2.NO_ARRET_O1 = to_number(p1.PL_COD_ARRET)

and s2.NO_ARRET_D1 = to_number(p2.PL_COD_ARRET)

and a1.AI_COD_ARRET = p1.PL_COD_ARRET_AS

and a2.AI_COD_ARRET = p2.PL_COD_ARRET_AS

and a1.AI_COD_ITINERAIR = a2.AI_COD_ITINERAIR

and a1.AI_COD_ITINERAIR = i.ITI_COD_ITINERAIR

and i.ITI_LIGNE = 'O'

and NVL (i.ITI_DAT_FINVALID, '99999999') >= to_number(to_char(sysdate,'YYYY') || to_char(sysdate,'MM') || to_char(sysdate,'DD'))

and i.ITI_COD_ITINERAIR = c.CSI_COD_ITINERAIR (+)

and i.ITI_DAT_DEBVALID = c.CSI_DAT_DEBVALID (+)

-- pas la ligne generique via

and i.ITI_COD_ITINERAIR <> '5000'

and rownum <= 1);

2. Select : 4 minute goes down to 30 seconds

This query returns 190 000 rows in 4 minutes.

SELECT DISTINCT

UPPER(AUDIT_EVENT.User_Name),

AUDIT_EVENT.Start_Timestamp,

DERIVED_SESSION_ID.Detail_Text,

LPAD(RTRIM(TO_CHAR(AUDIT_EVENT.Start_Timestamp, 'HH AM'),CHR(0)), 5, '0')

FROM

(

select

AUDIT_EVENT.Server_CUID, AUDIT_EVENT.Event_ID, RTRIM(To_CHAR(AUDIT_DETAIL.Detail_Text),CHR(0)) as

Detail_Text

from

AUDIT_EVENT, AUDIT_DETAIL

where

(AUDIT_EVENT.Server_CUID = AUDIT_DETAIL.Server_CUID) and

(AUDIT_EVENT.Event_ID = AUDIT_DETAIL.Event_ID) and

(AUDIT_DETAIL.Detail_Type_ID = 45)

) DERIVED_SESSION_ID RIGHT OUTER JOIN AUDIT_EVENT ON (AUDIT_EVENT.EVENT_ID=DERIVED_SESSION_ID.EVENT_ID and AUDIT_EVENT.SERVER_CUID=DERIVED_SESSION_ID.SERVER_CUID)

WHERE

(

AUDIT_EVENT.Start_Timestamp >= '01/01/2010'

AND UPPER(AUDIT_EVENT.User_Name) NOT IN ( 'ADMINISTRATOR','ADMINISTRATOR1','ADMINISTRATOR2','ADMINISTRATOR3','ADMINISTRATOR4','KCASTERES','CKIRIKIAN','CLAURA' )

)

We add the Hint SELECT /*+ no_push_pred(DERIVED_SESSION_ID) */ DISTINCT to avoid the Push-Predicate ; Cost is superior but the elapsed time falls to 1 minute.

Moreover, creating one index to get rid of the FTS drops the elapse to 32 seconds.

The condition is selective enough :

select count(1) from AUDIT_DETAIL where Detail_Type_ID = 45 -- 295 735 / 5.7 Millions

create index audit_detail_2 on AUDIT_DETAIL(Detail_Type_ID) ;

4] Unnesting of Nested Subqueries

Optimizer Cost Based Query Transformation [ID 1082127.1]

Subquery Unnesting and View Merging

Oracle exhaustively attempts to transform statements containing subqueries and views in to simpler statements. The goal of this activity is to make more access paths accessible and find the optimal plan. However, Subquery Unnesting and View Merging is a heuristic process. Subqueries are unnested and views are merged based upon a set of rules. No costs are generated and compared back to the unchanged statement. This could mean that the transformed statement does not perform as well as the untransformed one.

N.B. Oracle 10G introduces Costed Subquery Unnesting and View Merging which should go some way to alleviating the effects of this limitation

Here are Selects that had performance degradation after upgrade from 9i to 10g :

SELECT servgrp, xcodept, xlibpt, xnomrpt

FROM xlv.xpostetrav

WHERE xpostetrav.xcodept IN (

SELECT xparampt.xcodept

FROM xlv.xparampt, xlv.ordtask

WHERE xparampt.xtestcode = ordtask.testcode

AND ordtask.xnumfp IS NULL

AND ordtask.xhydro = 'Y'

AND ordtask.ts IN ('En Cours')

PLAN 9i OK

PLAN 10g KO

optimizer_features_enable	Plan	Temps d’exécution
[9i] 9.2.0	HASH JOIN SEMI–VIEW– HJ	2 secondes
[10g] 10.2.0.5	FILTER – NESTED LOOP	> 1 min

Those SQL have a Where Condition that is a IN over a Sub-Query on other tables.

The Optimizer replaces the IN statement with Joins only : rewriting known as « subquery unnesting » :

IN / EXISTS -> SEMI JOIN

- en 9i : Based on Rules, almost automatic

- en 10g :Cost-Based, may be discarded if judged a lesser Plan

This happens : Plan keeps Filters on sub-select returned rows, hence a wrong plan.

Go back to previous Rule-Based behavior :

PLAN 10g OK alter session set "_OPTIMIZER_COST_BASED_TRANSFORMATION" = off ;)

Set _optimizer_undo_cost_change to a release prior to 10.2.0.1

alter system set "_optimizer_undo_cost_change"='10.1.0.3' ;

Notes :

Rewriting with Joins

SELECT distinct a.servgrp, a.xcodept, a.xlibpt, a.xnomrpt

FROM xlv.xpostetrav a , xlv.xparampt, xlv.ordtask

WHERE a.xcodept = xparampt.xcodept

AND xparampt.xtestcode = ordtask.testcode

AND ordtask.xnumfp IS NULL

AND ordtask.xhydro = 'Y'

AND ordtask.ts IN ('En Cours')

PLAN 10g OK

PLAN 10G OK with HINT to force the S-Q UNNESTING

OraCsdP

Categories

mardi 2 juillet 2013

Help the Optimizer when it is Wrong : some examples