Skip to Main Content
  • Questions

  • When oracle use different plans when the statements use bind variables

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, xiao.

Asked: January 09, 2017 - 12:53 pm UTC

Last updated: March 30, 2021 - 12:31 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

An application frequently executed similar types of queries that vary only in the usage of literals in the WHERE clause. You plan to use bind variable in place of literal values.

The CURSOR_SHARING parameter to set to EXACT.

Which two statements are true about the usage of bind variables?

A. The number of latch gets in the library cache will be reduced.
B. Bind peeking will take place and subsequent execution of queries can have different plans based on the cardinality of the bind value in the column.
C. Bind peeking will take place and subsequent execution of queries can have different plans only when the histograms exists on a column used in WHERE clause.
D. Bind peeking will not happen and the optimizer will user the same plan for all bind values if no histograms exist on a column used in the WHERE clause.
E. Bind peeking will happen and subsequent execution of queries will have the same parent cursor but different child cursors.

My thinking is as follows:
A. Because I plan to use bind variable, so the hard parse count will decrease and latch will be reduced. (correct)
B. The adaptive cursor sharing is open automatically and cannot be closed. The execution of queries can have different plans based on different selectivity cube on bind variables. selectivity related to cardinality. (correct)
C.The adaptive cursor sharing is open automatically and cannot be closed. The execution of queries can have different plans based on different selectivity cube on bind variables. selectivity related to cardinality. He just mentioned column not bind value. (incorrect)
D. incorrect.
E. Based on the knowledge of adaptive cursor sharing and bind peeking, the plan has one parent cursor and different children cursors. and there is a mechanism transfer bind-sensitive cursor to bind-aware cursor.(correct)

What's wrong with my thinking? Just have two correct options.

and we said...

Unfortunately both the question and the possible answers are extremely poorly worded.

The question is also missing a critical piece of information regarding what kind of WHERE clause predicate is used with the bind (equality, range , expression etc.), as this will affect if and when bind peeking will kick in and if the presence of a histogram will make a difference or not.

Without this information, its impossible to know for sure which answers are actually correct. You could argue that none of them are true, in some cases.

But let us assume the simplest use case, a single table query with a single equality predicate and examine each answer.

A. The number of latch gets in the library cache will be reduced.

Using bind variables instead of literal values will reduce the number of hard parses, which in turn should reduce the number of library cache latch gets. Making this answer correct.

B. Bind peeking will take place and subsequent execution of queries can have different plans based on the cardinality of the bind value in the column.

If we consider this statement at a high level it could be true. But if we examine the wording more closely and we keep in mind our specific use case (single column equality predicate) then there is no guarantee bind peeking *will* take place and even if it did it would not generate a different execution plan for different bind variables, since we have not been told there is a histogram on the column. So, the selectivity would be calculated for all bind values as NDV / number of rows in the table, which would generate the same plan every time. Making this answer incorrect.

C. Bind peeking will take place and subsequent execution of queries can have different plans only when the histograms exists on a column used in WHERE clause.

If we focus just on our specific use case (single column equality predicate) and we assume bind peeking does actually take place, then the presence of a histogram on the column used in the WHERE clause may result in different execution plans being generated for the statement. Making this answer correct.

D. Bind peeking will not happen and the optimizer will user the same plan for all bind values if no histograms exist on a column used in the WHERE clause.

The presence of a histogram is not the only reason for bind peeking to occur, so the first half of this statement is already false, which makes the whole answer incorrect.

E. Bind peeking will happen and subsequent execution of queries will have the same parent cursor but different child cursors.

Again let's focus on just our specific use case (single column equality predicate) and we assume bind peeking does actually take place, there is no mention of a histogram in this answer, so there is no reason for the optimizer to generate a different execution for different bind variables. What I would expect to see is the parent cursor and just one child cursor. Making this answer incorrect.

So, only A and C are correct given our specific use case but let me stress that the wording here is extremely misleading and whoever wrote the question should have really included more information and change the wording of the answers to be more specific.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

shawn, January 11, 2017 - 6:32 am UTC

Thanks for your response and it's very useful for me!! Yes, I agree with you that this question is poorly worded. So let's do not matter which of them are correct or incorrect and be focus on your explanation.

In your answer, you mentioned that *the presence of a histogram is not the only reason for bind peeking to occur*. What else reason that affect the use of bind peeking? like bind parameter SIZE AND VALUE?
Maria Colgan
January 13, 2017 - 5:50 am UTC

Perhaps I wasn't careful enough in the wording of my answer! : )

I should have said that the presence of a histogram is not the only reason for adaptive cursor sharing to occur as a result of bind peeking.

Adaptive cursor sharing will kick in (mark the cursor bind sensitive) if the bind is used in an equality predicates where a histogram exists on the column and range predicates (with or without histogram). We do not currently consider LIKE predicates, but it is on the top of our list for future work.

Probably a ideal question than the one originally asked

Vamsi, March 19, 2021 - 5:21 pm UTC

CURSOR_SHARING is set to EXACT for the instance. An application is frequently executing almost identical queries that vary in literal values in the WHERE clause, causing a large number of hard parses to occur. Which four statements would be true if you use bind variables for these queries? (Choose four.) A. Mutex contention in the library cache will be reduced. B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values. C. Hard parses will be reduced for the queries. D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality. E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries. F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.
Connor McDonald
March 22, 2021 - 7:26 am UTC

This sounds a lot like an exam prep :-)

A. Mutex contention in the library cache will be reduced.
=>true

B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.
=>false

C. Hard parses will be reduced for the queries.
=>true

D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.
=>false

E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries.
=>true (mostly...this statement could be viewed as out of date for modern versions)

F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.
=>true


Can a force a bind variable to work as a literal variable???

A reader, March 27, 2021 - 5:43 pm UTC

select * from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0



So my query is something like this -
SELECT ...
, ...
FROM (
SELECT RANK ()
OVER (PARTITION BY col1
ORDER BY col2 DESC) AS RANK
, ...
, colN
FROM <<table_name>>
WHERE date < :x + 1
)
WHERE RANK = 1
AND col = :bindNewState
AND ( ( :5 = 'NO'
AND col5 || col6 IN
(SELECT col1 || col2
FROM table2
WHERE col3 = :bindCol3
UNION ALL
SELECT col1 || 0
FROM table2
WHERE col3 = :bindCol3))
OR (:5 = 'YES'))

And the plan it generates is as follows -

Plan
SELECT STATEMENT ALL_ROWSCost: 5,970 Bytes: 4,781,140 Cardinality: 13,135
5 NESTED LOOPS Cost: 2 Bytes: 41 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE <<TableNameHidden>> Cost: 1 Bytes: 21 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) <<IndexNameHIdden>> Cost: 0 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE <<TableNameHidden>> Cost: 1 Bytes: 20 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) <<IndexNameHIdden>> Cost: 0 Cardinality: 1
10 NESTED LOOPS Cost: 2 Bytes: 41 Cardinality: 1
7 TABLE ACCESS BY INDEX ROWID TABLE <<TableNameHidden>> Cost: 1 Bytes: 21 Cardinality: 1
6 INDEX UNIQUE SCAN INDEX (UNIQUE) <<IndexNameHIdden>> Cost: 0 Cardinality: 1
9 TABLE ACCESS BY INDEX ROWID TABLE <<TableNameHidden>> Cost: 1 Bytes: 20 Cardinality: 1
8 INDEX UNIQUE SCAN INDEX (UNIQUE) <<IndexNameHIdden>>Cost: 0 Cardinality: 1
26 FILTER <<<<<<---------------------------------
20 VIEW REPORTING. Cost: 5,970 Bytes: 4,781,140 Cardinality: 13,135
19 WINDOW SORT PUSHED RANK Cost: 5,970 Bytes: 1,169,015 Cardinality: 13,135
18 HASH JOIN Cost: 5,666 Bytes: 1,169,015 Cardinality: 13,135
16 NESTED LOOPS Cost: 5,666 Bytes: 1,169,015 Cardinality: 13,135
14 NESTED LOOPS
12 STATISTICS COLLECTOR
11 TABLE ACCESS FULL TABLE viewTable2 Cost: 1,266 Bytes: 932,585 Cardinality: 13,135
13 INDEX UNIQUE SCAN INDEX (UNIQUE) viewTable1_PK
15 TABLE ACCESS BY INDEX ROWID TABLE viewTable1 T Cost: 4,400 Bytes: 18 Cardinality: 1
17 TABLE ACCESS FULL TABLE viewTable1 Cost: 4,400 Bytes: 950,256 Cardinality: 52,792
25 UNION-ALL
22 TABLE ACCESS BY INDEX ROWID BATCHED TABLE table1 Cost: 9 Bytes: 85 Cardinality: 5
21 INDEX RANGE SCAN INDEX table1index1 Cost: 4 Cardinality: 461
24 TABLE ACCESS BY INDEX ROWID BATCHED TABLE table1 Cost: 9 Bytes: 55 Cardinality: 5
23 INDEX RANGE SCAN INDEX table1index1Cost: 4 Cardinality: 461


Now I modify the query to -
SELECT ...
, ...
FROM (
SELECT RANK ()
OVER (PARTITION BY col1
ORDER BY col2 DESC) AS RANK
, ...
, colN
FROM <<table_name>>
WHERE date < :x + 1
)
WHERE RANK = 1
AND col = :bindNewState
AND ( ( 'NO' = 'NO'
AND col5 || col6 IN
(SELECT col1 || col2
FROM table2
WHERE col3 = :bindCol3
UNION ALL
SELECT col1 || 0
FROM table2
WHERE col3 = :bindCol3))
OR ('NO' = 'YES'))

And the plan is as follows -

Plan
SELECT STATEMENT ALL_ROWSCost: 5,988 Bytes: 49,168,630 Cardinality: 121,105
5 NESTED LOOPS Cost: 2 Bytes: 41 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE <<TableNameHidden>> Cost: 1 Bytes: 21 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) <<IndexNameHIdden>> Cost: 0 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE <<TableNameHidden>> Cost: 1 Bytes: 20 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) <<IndexNameHIdden>> Cost: 0 Cardinality: 1
10 NESTED LOOPS Cost: 2 Bytes: 41 Cardinality: 1
7 TABLE ACCESS BY INDEX ROWID TABLE <<TableNameHidden>> Cost: 1 Bytes: 21 Cardinality: 1
6 INDEX UNIQUE SCAN INDEX (UNIQUE) <<IndexNameHIdden>> Cost: 0 Cardinality: 1
9 TABLE ACCESS BY INDEX ROWID TABLE <<TableNameHidden>> Cost: 1 Bytes: 20 Cardinality: 1
8 INDEX UNIQUE SCAN INDEX (UNIQUE) <<IndexNameHIdden>> Cost: 0 Cardinality: 1
28 HASH JOIN Cost: 5,988 Bytes: 49,168,630 Cardinality: 121,105 <<<<<<---------------------------------
17 VIEW VIEW SYS.VW_NSO_3 Cost: 18 Bytes: 38,724 Cardinality: 922
16 HASH UNIQUE Cost: 18 Bytes: 25,816 Cardinality: 922
15 UNION-ALL
12 TABLE ACCESS BY INDEX ROWID BATCHED TABLE table1 Cost: 9 Bytes: 7,837 Cardinality: 461
11 INDEX RANGE SCAN INDEX table1index1 Cost: 4 Cardinality: 461
14 TABLE ACCESS BY INDEX ROWID BATCHED TABLE table1 Cost: 9 Bytes: 5,071 Cardinality: 461
13 INDEX RANGE SCAN INDEX table1index1 Cost: 4 Cardinality: 461
27 VIEW REPORTING. Cost: 5,970 Bytes: 4,781,140 Cardinality: 13,135
26 WINDOW SORT PUSHED RANK Cost: 5,970 Bytes: 1,169,015 Cardinality: 13,135
25 HASH JOIN Cost: 5,666 Bytes: 1,169,015 Cardinality: 13,135
23 NESTED LOOPS Cost: 5,666 Bytes: 1,169,015 Cardinality: 13,135
21 NESTED LOOPS
19 STATISTICS COLLECTOR
18 TABLE ACCESS FULL TABLE viewTable2 Cost: 1,266 Bytes: 932,585 Cardinality: 13,135
20 INDEX UNIQUE SCAN INDEX (UNIQUE) viewTable1_PK
22 TABLE ACCESS BY INDEX ROWID TABLE viewTable1 Cost: 4,400 Bytes: 18 Cardinality: 1
24 TABLE ACCESS FULL TABLE viewTable1 Cost: 4,400 Bytes: 950,256 Cardinality: 52,792


In the second plan the query is using a "HASH JOIN", which leads to a turn around time of less than 3 secs. In the original query with bind variable the query uses the filter clause which keeps running for minutes.
What can I do so that I need not change the query but it still takes the 2nd plan (with hash join)?
Chris Saxon
March 30, 2021 - 12:31 pm UTC

Not that I'm aware of. Look into SQL plan management (baselines) and SQL profiles to help you get the plan you want.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.