Explore Oracle Stuff: Importance of bind variable in SQL code

Thank you very much for landing up here.In this post I will explain negative impact of using literals instead of bind variables and how that creates SQL tuning more challenging.

Problem statement: Recently I have faced a SQL query performance issue which I would like to share with you.Query runs well when we execute manually but takes lot of time from application user interface.Also worst part is application is not using bind variable which is highlighted at the end of the SQL statement below.This is more problematic as every time it will create a new SQL_ID and we can not stabilize the plan using SQL profile or plan baseline.In addition They use global temporary table whose data is visible only for that particular session.

Gather and Analyze:

Below is the SQL text

SELECT X.ENDTIME,
X.EXPORTHISTORYKEY,
X.EXTERNALSYSTEMALTERNATEKEY,
X.FILEINVENTORYKEY,
X.INTERVALTOLERANCE,

       X.READINGGROUPALTERNATEKEY,
       X.READINGGROUPKEY,
       X.READINGGROUPTYPE,
       X.READINGSOURCEID,
       X.RECEIVEDTIME,
       X.SPCNODEID,
       X.STARTTIME,
       X.STATUSID,
       X.TIMETOLERANCE

FROM V_READINGGROUP_RGSPC X

INNER JOIN T$_NUMBER_LIST READINGGROUPTYPE#

ON READINGGROUPTYPE#.NLISTVALUE = X.READINGGROUPTYPE

AND READINGGROUPTYPE#.NLISTNAME = 'READINGGROUPTYPE#'

WHERE (1 = 1)

AND x.SPCNODEID = SYS_CONTEXT ('CTX_ITRONADM', 'SPCNODEID#')

AND (ReceivedTime >

TO_TIMESTAMP (' 2024/09/21 16:05:26.000', 'YYYY/MM/DD HH24:MI:SS.FF3'))

Lets deep dive into the problem.Actually V_READINGGROUP_RGSPC is a view and T$_NUMBER_LIST READINGGROUPTYPE# is a global temporary table.Lets get the script of the view .I assume you know how to extract DDL using DBM_METADATA.GET_DDL function.

CREATE OR REPLACE FORCE VIEW ITRONADM.V_READINGGROUP_RGSPC

(

READINGGROUPALTERNATEKEY,

SPCNODEID,

STARTTIME,

ENDTIME,

TIMETOLERANCE,

INTERVALTOLERANCE,

STATUSID,

READINGGROUPKEY,

RECEIVEDTIME,

EXTERNALSYSTEMALTERNATEKEY,

READINGGROUPTYPE,

READINGSOURCEID,

FILEINVENTORYKEY,

EXPORTHISTORYKEY

)

BEQUEATH DEFINER

SELECT rgSpc.ReadingGroupAlternateKey,

rgSpc.SpcNodeID,

rgSpc.StartTime,

rgSpc.EndTime,

rgSpc.TimeTolerance,

rgSpc.IntervalTolerance,

rgSpc.StatusID,

rg.ReadingGroupKey,

rg.ReceivedTime,

rg.ExternalSystemAlternateKey,

rg.ReadingGroupType,

rg.ReadingSourceID,

rg.FileInventoryKey,

rgSpc.ExportHistoryKey

FROM ReadingGroup rg

INNER JOIN ReadingGroupSpc rgSpc

ON rg.ReadingGroupAlternateKey = rgSpc.ReadingGroupAlternateKey;

Now ReadingGroup is relatively small table of size 3.25 GB and ReadingGroupSpc is a partitioned table of size 240 GB.

Lets focus on the execution plan

PLAN_TABLE_OUTPUT

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

SQL_ID 3c8c07fgmr1gu, child number 0

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

Plan hash value: 2768073067

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

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

| 0 | SELECT STATEMENT | | | | 1070 (100)| | | |

| 1 | NESTED LOOPS | | 2 | 588 | 1070 (0)| 00:00:01 | | |

| 2 | NESTED LOOPS | | 2 | 588 | 1070 (0)| 00:00:01 | | |

| 3 | NESTED LOOPS | | 2 | 516 | 5 (0)| 00:00:01 | | |

| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| READINGGROUP | 2 | 86 | 4 (0)| 00:00:01 | | |

|* 5 | INDEX RANGE SCAN | IX3_READINGGROUP | 2 | | 2 (0)| 00:00:01 | | |

|* 6 | INDEX RANGE SCAN | PK_T$_NUMBER_LIST | 1 | 215 | 1 (0)| 00:00:01 | | |

| 7 | PARTITION RANGE ALL | | 1 | | 532 (0)| 00:00:01 | 1 | 443 |

| 8 | PARTITION HASH SINGLE | | 1 | | 532 (0)| 00:00:01 | KEY | KEY |

|* 9 | INDEX RANGE SCAN | IX_RDGRPSPC_SPCNDEID_RGAK_SE | 1 | | 532 (0)| 00:00:01 | | |

| 10 | TABLE ACCESS BY LOCAL INDEX ROWID | READINGGROUPSPC | 1 | 36 | 532 (0)| 00:00:01 | 1 | 1 |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('19.1.0')

DB_VERSION('19.1.0')

OPT_PARAM('_optimizer_cost_based_transformation' 'off')

OPT_PARAM('_px_adaptive_dist_method' 'off')

OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

OPT_PARAM('optimizer_index_cost_adj' 60)

ALL_ROWS

OUTLINE_LEAF(@"SEL$9BA1C343")

MERGE(@"SEL$73112EEC" >"SEL$4")

OUTLINE(@"SEL$4")

OUTLINE(@"SEL$73112EEC")

MERGE(@"SEL$64EAE176" >"SEL$1")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$64EAE176")

MERGE(@"SEL$2" >"SEL$3")

OUTLINE(@"SEL$3")

OUTLINE(@"SEL$2")

INDEX_RS_ASC(@"SEL$9BA1C343" "RG"@"SEL$2" ("READINGGROUP"."RECEIVEDTIME"))

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$9BA1C343" "RG"@"SEL$2")

INDEX(@"SEL$9BA1C343" "READINGGROUPTYPE#"@"SEL$1" ("T$_NUMBER_LIST"."NLISTNAME" "T$_NUMBER_LIST"."NLISTVALUE"

"T$_NUMBER_LIST"."ROWNUMBER"))

INDEX(@"SEL$9BA1C343" "RGSPC"@"SEL$2" ("READINGGROUPSPC"."SPCNODEID" "READINGGROUPSPC"."READINGGROUPALTERNATEKEY"

"READINGGROUPSPC"."STARTTIME" "READINGGROUPSPC"."ENDTIME"))

LEADING(@"SEL$9BA1C343" "RG"@"SEL$2" "READINGGROUPTYPE#"@"SEL$1" "RGSPC"@"SEL$2")

USE_NL(@"SEL$9BA1C343" "READINGGROUPTYPE#"@"SEL$1")

USE_NL(@"SEL$9BA1C343" "RGSPC"@"SEL$2")

NLJ_BATCHING(@"SEL$9BA1C343" "RGSPC"@"SEL$2")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

5 - access("RG"."RECEIVEDTIME">

TIMESTAMP' 2024-09-21 16:05:26.000000000')

6 - access("READINGGROUPTYPE#"."NLISTNAME"='READINGGROUPTYPE#' AND "READINGGROUPTYPE#"."NLISTVALUE"="RG"."READINGGROUPTYPE")

9 - access("RGSPC"."SPCNODEID"=TO_NUMBER(SYS_CONTEXT('CTX_ITRONADM','SPCNODEID#')) AND "RG"."READINGGROUPALTERNATEKEY"="RGSPC"."READINGGROUPALTERNATEKEY")

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

80 rows selected.

Please see the line 5,6 and 9 where access predicate has been used and index was used to fetch rows from corresponding tables.

For line no 5 it has used index IX3_READINGGROUP based on column receivedtime under readinggroup table
For line no 6 it has used index PK_T$_NUMBER_LIST based on column nlisttime,nlistvalue,rownumber
For line no 9 it has used index IX_RDGRPSPC_SPCNDEID_RGAK_SE based on column SPCNODEID, READINGGROUPALTERNATEKEY, STARTTIME, ENDTIME under READINGGROUPSPC

This query was taking almost 20 minutes in production but less than a second in QA environment.

Lets find the execution plan in QA to compare

PLAN_TABLE_OUTPUT --------------------------------------------

SQL_ID 7uyxr4f6xdvh4, child number 0

Plan hash value: 3101949422

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

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

| 0 | SELECT STATEMENT | | | | 1782 (100)| | | |

|* 1 | HASH JOIN | | 2 | 588 | 1782 (0)| 00:00:01 | | |

|* 2 | HASH JOIN | | 2 | 516 | 9 (0)| 00:00:01 | | |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | READINGGROUP | 2 | 86 | 7 (0)| 00:00:01 | | |

|* 4 | INDEX RANGE SCAN | IX3_READINGGROUP | 2 | | 4 (0)| 00:00:01 | | |

|* 5 | TABLE ACCESS FULL | T$_NUMBER_LIST | 1 | 215 | 1 (0)| 00:00:01 | | |

| 6 | PARTITION RANGE ALL | | 1 | 36 | 887 (0)| 00:00:01 | 1 | 443 |

| 7 | PARTITION HASH SINGLE | | 1 | 36 | 887 (0)| 00:00:01 | KEY | KEY |

| 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| READINGGROUPSPC | 1 | 36 | 887 (0)| 00:00:01 | | |

|* 9 | INDEX RANGE SCAN | IX_RDGRPSPC_SPCNDEID_RGAK_SE | 1 | | 886 (0)| 00:00:01 | | |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$9BA1C343" "RGSPC"@"SEL$2")

INDEX_RS_ASC(@"SEL$9BA1C343" "RGSPC"@"SEL$2" ("READINGGROUPSPC"."SPCNODEID" "READINGGROUPSPC"."READINGGROUPALTERNATEKEY"

"READINGGROUPSPC"."STARTTIME" "READINGGROUPSPC"."ENDTIME"))

USE_HASH(@"SEL$9BA1C343" "RGSPC"@"SEL$2")

FULL(@"SEL$9BA1C343" "READINGGROUPTYPE#"@"SEL$1")

USE_HASH(@"SEL$9BA1C343" "READINGGROUPTYPE#"@"SEL$1")

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('19.1.0')

DB_VERSION('19.1.0')

OPT_PARAM('_optimizer_cost_based_transformation' 'off')

ALL_ROWS

OUTLINE_LEAF(@"SEL$9BA1C343")

MERGE(@"SEL$73112EEC" >"SEL$4")

OUTLINE(@"SEL$4")

OUTLINE(@"SEL$73112EEC")

MERGE(@"SEL$64EAE176" >"SEL$1")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$64EAE176")

MERGE(@"SEL$2" >"SEL$3")

OUTLINE(@"SEL$3")

OUTLINE(@"SEL$2")

INDEX_RS_ASC(@"SEL$9BA1C343" "RG"@"SEL$2" ("READINGGROUP"."RECEIVEDTIME"))

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$9BA1C343" "RG"@"SEL$2")

LEADING(@"SEL$9BA1C343" "RG"@"SEL$2" "READINGGROUPTYPE#"@"SEL$1" "RGSPC"@"SEL$2")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

1 – access(“RG”.”READINGGROUPALTERNATEKEY”=”RGSPC”.”READINGGROUPALTERNATEKEY”)

2 – access(“READINGGROUPTYPE#”.”NLISTVALUE”=”RG”.”READINGGROUPTYPE”)

4 – access(“RG”.”RECEIVEDTIME”>TIMESTAMP’ 2025-02-09 20:54:30.000000000’)

5 – filter(“READINGGROUPTYPE#”.”NLISTNAME”=’READINGGROUPTYPE#’)

9 – access(“RGSPC”.”SPCNODEID”=TO_NUMBER(SYS_CONTEXT(‘CTX_ITRONADM’,’SPCNODEID#’)))

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

- this is an adaptive plan

Please see that in this plan temporary table has been accessed using full table scan and then filter

has been applied.Also, this plan is using hash join instead of nested loop join.

Veryt first thought came in mind that may be stats are not updated in production which leads to an unoptimized plan.I have checked stats in production and qa and both are updated as we have scheduled stats job runs over weekend.

Then thought process starts.What else is different as I know prod and QA has same kind of infrastructure.My guess was this could be a difference in optimizer.

If you look carefully outline section of both the plan

from 1st execution plan

OPTIMIZER_FEATURES_ENABLE('19.1.0')

DB_VERSION('19.1.0')

OPT_PARAM('_optimizer_cost_based_transformation' 'off')

OPT_PARAM('_px_adaptive_dist_method' 'off')

OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

OPT_PARAM('optimizer_index_cost_adj' 60)

From 2nd execution plan

OPTIMIZER_FEATURES_ENABLE('19.1.0')

DB_VERSION('19.1.0')

OPT_PARAM('_optimizer_cost_based_transformation' 'off')

Can you see that all hidden paraemeters with the keyword adaptive?

This is big indication that adaptive features are not being used in production and it is being used in QA

as end of outline we can see

- this is an adaptive plan

When I check parameter optimizer_adaptive_plans that is set to true in QA which leads to better execution plan but in prod it is set to false.

I changed it in QA to reproduce the case and it started perfoming slowly and giving same execution plan.Finally it was changed in prod and issue was resolved.

Conclusion:I hope now you have understood how critical optimizer_adaptive_plans and learnt something new.

Happy learning!!

Explore Oracle Stuff

Thursday, April 3, 2025

Importance of bind variable in SQL code

No comments:

Post a Comment

When SQL plan baseline is not used

Report Abuse

Labels