Reusability of SQL:

nostrilshumorousInternet and Web Development

Nov 18, 2013 (3 years and 8 months ago)

56 views

Reusability of SQL:

Cursor Sharing, Bind Variables and

Bind Peeking




Sameer Malik

sameer18july@gmail.com

(770) 876
-
3281

Oracle 9i and Oracle 10g certified professional,

Six Sigma Certified (Green Belt)

Sun Certified Solaris
-
10 System Administrator

Topics

1) Cursors and Cursor Sharing.

2) Parent and child cursors.

3) Cursor_Sharing Parameter effect.

4) Bind Variables.

5) Bind variable Peeking.

6) Eliminating bind peeking problem.

7) Best practices for using bind variables.

Life Cycle of a Cursor
-

I


Open cursor
: A memory structure for the cursor is allocated in the
server
-
side private memory
of the server process associated with the
session, the
user global area (UGA). Note that no
SQL statement is
associated with the cursor yet.


Parse cursor
: A SQL statement is associated with the cursor. Its parsed
representation that
includes the execution plan (which describes
how the SQL engine will execute the SQL statement) is loaded in
the shared pool, specifically, in the library cache. The structure in
the UGA is updated to store a pointer to the location of the
shareable cursor in the library cache.


Define output variables
: If the SQL statement returns data, the
variables receiving it must
be defined. This is necessary not only for
queries but also for DELETE, INSERT, and UPDATE statements that
use the RETURNING clause.


Bind input variables
:

If the SQL statement uses bind variables, their values
must be provided .
No check is performed during the binding. If invalid data
is passed, a runtime error will be raised during the execution.


Execute cursor
:
The SQL statement is executed. But be careful, because the
database engine doesn’t always do anything significant during this phase. In fact,
for many types of queries , the real processing is usually delayed to the fetch
phase.


Fetch cursor
:

If the SQL statement returns data, this step retrieves it.
Especially for queries , this step is where most of the processing is
performed. In the case of queries, rows might be partially fetched. In other
words, the cursor might be closed before fetching all the rows.


Close cursor
:

The resources associated with the cursor in the UGA are
freed and consequently made available for other cursors. The shareable
cursor in the library cache is not removed. It remains there in the hope of
being reused in the future.

Life Cycle of a Cursor
-

II

How Parsing Works
-

I


Include VPD predicates
:

If Virtual Private Database (VPD, formerly
known as row
-
level security) is in use and active for one of the tables
referenced in the parsed SQL statement , the predicates generated by
the security policies are included in its WHERE clause.


Check syntax, semantics, and access rights
:

This step makes sure
not only that the SQL statement is correctly written but also that all
objects referenced by the SQL statement exist and the current user
parsing it has the necessary privileges to access them.


Store parent cursor in library cache
:

Whenever a shareable parent
cursor is not yet available , some memory is allocated from the library
cache, and a new parent cursor is stored inside it . The key information
associated with the parent cursor is the text of the SQL statement.

How Parsing Works
-

II


Logical optimization
:

During this phase, new and semantically equivalent
SQL statements are produced by applying different transformation techniques. In
doing so, the amount of execution plans considered, the search space, is
increased. The purpose is to explore execution plans that would not be
considered without such transformations.


Physical optimization:

During this phase, At first, the execution plans related
to each SQL statement resulting from the logical optimization are generated.
Then, based on statistics found in the data dictionary or gathered through
dynamic sampling, a cost is associated with each execution plan. Lastly, the
execution plan with the lowest cost is selected. Simply put, the query optimizer
explores the search space to find the most efficient execution plan.


Store child cursor in library cache:

Some memory is allocated, and the
shareable child cursor is stored inside it and associated with its parent cursor.
The key elements associated with the child cursor are the execution plan and
the execution environment.


Once stored in the library cache, parent and child cursors are externalized
through the views v$sqlarea and v$sql, respectively. The cursors are identified in
three columns: address, hash_value, and child_number. With address and
hash_value, the parent cursors are identified with all three values, the child
cursors are identified. In addition, as of Oracle Database 10g, it is also possible,
and it is more common as well, to use sql_id instead of the pair address and
hash_value for the same purpose.



When shareable parent and child cursors are available and, consequently, only
the first two operations are carried out, the parse is called a soft parse. When all
operations are carried out, it is called a hard parse.



The result of a parse operation is a parent and a child cursor stored in the
library cache. The aim of storing them in a shared memory area is to allow their
reutilization and thereby avoid hard parses.

More on Parsing…

Examples: Cursor Sharing


Example
-
1

:
-

The purpose of the first example is to show a case where the
parent cursor cannot be shared. The key information related to a parent cursor is
the text of a SQL statement. Therefore, several SQL statements share the same
parent cursor if their text is exactly the same.



Example
-
2

:
-

It show a case where the parent cursor, but not the child cursor,
can be shared. The key information related to a child cursor is an execution plan
and the execution environment related to it. The execution environment is
important because if it changes, the execution plan might change as well. As a
result, several SQL statements are able to share the same child cursor only if
they share the same parent cursor and their execution environments are
compatible.

Cursor_Sharing

Parameter Effect


CURSOR_SHARING

is a parameter Oracle Database uses to
control whether it will "auto
-
bind" a SQL statement.



Oracle Database can take a query of the form SELECT * FROM
TABLE WHERE COL = 'literal' and replace the 'literal' with a
bind
value , so
the predicate will become WHERE COL = :"SYS_B_0".
This permits the reuse of the generated query plan, perhaps
leading to better utilization of the shared pool and a reduction of
hard parses performed by the system.



The CURSOR_SHARING parameter can have one of three
values:
1) Exact; 2) Force; 3)Similar


1
.

EXACT
-
This

is

the

default

setting
.

With

this

value

in

place,

the

query

is

not

rewritten

to

use

bind

variables
.




With

CURSOR_SHARING=EXACT

(the

default),

every

unique

SQL

statement

executed

will

create

a

new

entry

in

V
$
SQL,

it

will

be

hard
-
parsed,

and

an

execution

plan

will

be

created

just

for

it
.

There

can

be

hundreds

or

thousands

of

very

similar

queries

in

the

shared

pool

that

differ

only

in

the

literals

used

in

the

SQL

statement

itself
.

This

implies

that

the

database

is

forced

to

hard
-
parse

virtually

every

query,

which,

in

turn,

not

only

consumes

a

lot

of

CPU

cycles

but

also

leads

to

decreased

scalability
.




The

database

just

cannot

hard
-
parse

hundreds

or

thousands

of

SQL

statements

concurrently

the

application

ends

up

waiting

for

the

shared

pool

to

become

available
.

One

of

the

major

scalability

inhibitors

in

the

database

is

not

using

bind

variables
.

That

was

the

motivation

behind

adding

CURSOR_SHARING=FORCE

.





2
.

FORCE
-
This

setting

rewrites

the

query,

replacing

all

literals

with

bind

values

and

setting

up

a

one
-
size
-
fits
-
all

plan

a

single

plan

for

the

rewritten

query
.




With

CURSOR_SHARING=FORCE

in

place

the

database

generated

only

one

shareable

query

in

the

shared

pool

it

replaced

'A'

and

'B'

with

:
"SYS_B_
0
"

and

made

the

cursor

shareable

by

as

many

sessions

as

would

need

it
.

In

general,

just

one

query

plan

would

be

reused

by

all

sessions
.




This

would

turn

the

hard

parse

into

a

soft

parse,

which

would

consume

fewer

resources

and

simultaneously

increase

the

scalability

of

the

system,

by

allowing

for

more

concurrent

work,

because

a

soft

parse

needs

to

"latch"

(use

a

certain

type

of

lock

on)

the

shared

pool

less

than

a

hard

parse
.



3
.

SIMILAR

-

This

setting

also

rewrites

the

query,

replacing

the

literals

with

bind

variables,

but

can

set

up

different

plans

for

different

bind

variable

combinations
.

This

setting

may

or

may

not

reduce

the

number

of

actual

plans

you

observe

in

the

shared

pool
.




When

CURSOR_SHARING

is

set

to

SIMILAR,

Oracle

will

replace

all

literals

with

bind

variables,

just

as

FORCE

would,

but

the

SIMILAR

value

does

one

other

thing

it

looks

at

each

literal

it

replaces

and

asks,

"Could

different

values

for

this

bind

variable

lead,

in

turn,

to

different

plans?"

For

example,

if

the

predicate

WHERE

X=
6

will

have

a

full

scan

but

the

predicate

WHERE

X=
5

have

index

range

scan,

the

database

would

recognize

that

and

set

up

different

plans

for

me
.

In

the

case

of

different

plans,

you

mark

the

bind

variable

as

unsafe

and

add

its

value

to

the

signature

of

the

query,

so

to

reuse

this

cursor,

you

must

not

only

have

the

same

SQL

statement

but

also

the

same

value

for

that

particular

bind

variable
.

That

is

why

SIMILAR

might

reduce

the

number

of

plans

you

see

in

the

shared

pool

but,

then

again,

might

not



In the example three plans were generated, because the optimizer detected that a
different value used when searching against the ID column could lead to a different
plan. Hence the actual bind variable value was added to the signature of that query
plan and only a query with exactly the same signature could reuse the plan. There
were not six queries in V$SQL, just four. It is only when the bind variable substitution
is deemed unsafe that CURSOR_SHARING=SIMILAR will generate a new plan.



If we varied the literals used to search against the OBJECT_ID column

but not the
ID column

in . The optimizer recognized that OBJECT_ID is safe. it would not
generate different plans based on different inputs for that column, so it did not add
OBJECT_ID to the signature of the cursor.



If the application were to vary the literals used against the ID column and use
hundreds or thousands of unique values, it would not have a significant impact on
the shared pool utilization. On the other hand, if the application used only two values
against the ID column, it could have a dramatic, positive effect on shared pool
utilization.

Bind Variables
-

I


Bind

variables

allow

the

sharing

of

cursors

in

the

library

cache

and

that

way

avoid

hard

parses

and

the

overhead

associated

with

them
.




There are, however, situations where several child cursors are created even
with bind variables. The following example shows such a case:



Notice that the INSERT statement is the same as in the previous example.
Only the maximum size of the VARCHAR2 variable has changed (from32
to 33). The new child cursor (1) is created because the execution
environment between the first three INSERT statements and the fourth
has changed. The mismatch, as can be confirmed by querying the view
v$sql_shared_cursor is because of the bind variables.





Bind

variable

graduation

:
-
The

aim

of

this

feature

is

to

minimize

the

number

of

child

cursors

by

graduating

bind

variables

(which

vary

in

size)

into

four

groups

depending

on

their

size
.

The

first

group

contains

the

bind

variables

with

up

to

32

bytes,

the

second

contains

the

bind

variables

between

33

and

128

bytes,

the

third

contains

the

bind

variables

between

129

and

2
,
000

bytes,

and

the

last

contains

these

bind

variables

of

more

than

2
,
000

bytes
.

Bind

variables

of

datatype

NUMBER

are

graduated

to

their

maximum

length,

which

is

22

bytes
.

As

the

following

example

shows,

the

view

v
$
sql_bind_metadata

displays

the

maximum

size

of

a

group
.

Notice

how

the

value

128

is

used,

even

if

the

variable

of

child

cursor

1

was

defined

as

33
.



Each

time

a

new

child

cursor

is

created,

an

execution

plan

is

generated
.

Whether

this

new

execution

plan

is

equal

to

the

one

used

by

another

child

cursor

also

depends

on

the

value

of

the

bind

variables
.

Bind variables
-

II


While using bind variables in WHERE clauses, crucial information is hidden
from the query optimizer. With literals, it is able to improve its estimations. This
is especially true when it checks whether a value is outside the range of
available values (that is, lower than the minimum value or higher than the
maximum value stored in the column) and when it takes advantage of
histograms. To illustrate, let’s take a table t with 1,000 rows that store, in the
column id, the values between 1 (the minimum value) and 1,000 (the
maximum value):




When a user selects all rows that have an id of less than 990, the query
optimizer knows (thanks to object statistics) that about 99 percent of the
table is selected. Therefore, it chooses an execution plan with a full table scan.
Also notice how the estimated cardinality (column Rows in the execution plan)
corresponds to the number of rows returned by the query.

Disadvantages of Bind Variables


When another user selects all rows that have an id of less than
10, the query optimizer knows that only about 1 percent of the
table is selected. Therefore, it chooses an execution plan with
an index scan. Also in this case, notice the good estimation.



Of course, as shown in the following example, if the first
execution takes place with the value 10, the query optimizer
chooses an execution plan with the index scan

and that, once
more, occurs for both queries. Note that to avoid sharing the
cursor used for the previous example, the queries were written
in lowercase letters.


It is essential to understand that as long as the cursor remains in the library cache and
can be shared, it will be reused. This occurs regardless of the efficiency of the execution
plan related to it. To solve this problem, as of Oracle Database 11g, a new feature called
extended cursor sharing (also known as adaptive cursor sharing) is available. Its purpose
is to automatically recognize when the reutilization of an already available cursor leads
to inefficient executions. The following new columns are available as of Oracle Database
11g



is_bind_sensitive


indicates not only whether bind variable peeking was used to
generate the execution plan but also whether the execution plan depends on the
peeked value. If this is the case, the column is set to Y; otherwise, it’s set to N.



is_bind_aware


indicates whether the cursor is using extended cursor sharing. If yes, the
column is set to Y; if not, it’s set to N. If set to N, the cursor is obsolete, and it will no
longer be used.



is_shareable

indicates whether the cursor can be shared. If it can, the column is set to
Y; otherwise, it’s set to N. If set to N, the cursor is obsolete, and it will no longer be
used.


Adaptive/Extended Cursor Sharing


In the following example, the cursor is shareable and sensitive to bind variables, but it is not using
extended cursor sharing:




SQL> SELECT
child_number
,
is_bind_sensitive
,
is_bind_aware
,
is_shareable

FROM
v$sql

WHERE
sql_id

= '7h6n1xkn8trkd’ ORDER BY
child_number
;


CHILD_NUMBER

IS_BIND_SENSITIVE

IS_BIND_AWARE

IS_SHAREABLE





0



Y


N


Y



When the cursor is executed several times with different values for the bind variable. After a few
executions with the values 10 and 990, the information provided by the view
v$sql

is different.
Notice that child number 0 is no longer shareable and that two new child cursors use extended
cursor sharing.


SQL> SELECT
child_number
,
is_bind_sensitive
,
is_bind_aware
,
is_shareable



FROM
v$sql

WHERE
sql_id

= '7h6n1xkn8trkd‘ ORDER BY
child_number
;


CHILD_NUMBER IS_BIND_SENSITIVE


IS_BIND_AWARE


IS_SHAREABLE


-----------

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


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

-----------


0




Y




N



N


1 Y
Y


Y


2 Y
Y



Y



Looking at the execution plans related to the cursor, as you might expect, you see that one of the
new children has an execution plan based on the full table scan, while the other is based on the
index scan:




To further analyze the reason for the generation of the two child cursors, new
dynamic performance views are available:


v$sql_cs_statistics


v$sql_cs_selectivity


v$sql_cs_histogram
.



The first shows whether peeking was used and the related execution statistics for
each child cursor. In the following output, it is possible to confirm that for one
execution, the number of rows processed by child cursor 1 is higher than for child
cursor 2



Hence, in one case the query optimizer chose a full table scan and in the other
an index scan.


SQL> SELECT child_number, peeked, executions, rows_processed,
buffer_gets

FROM v$sql_cs_statistics WHERE sql_id =
'7h6n1xkn8trkd' ORDER BY child_number;

CHILD_NUMBER

PEEKED


EXECUTIONS

ROWS_PROCESSED

BUFFER_GETS

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

------

----------

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

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


0


Y

1



19


3


1


Y

1



990 1


9


2


Y

1



19


3


The view v$sql_cs_selectivity shows the selectivity range related to each predicate of each child cursor. In fact, the
database engine does not create a new child cursor for each bind variable value. Instead, it groups values together
that have about the same selectivity and, consequently, should lead to the same execution plan.


SQL> SELECT child_number, predicate, low, high FROM
v$sql_cs_selectivity

WHERE sql_id = '7h6n1xkn8trkd'


ORDER BY child_number;


CHILD_NUMBER


PREDICATE


LOW


HIGH

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

---------

----------

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


1



<ID 0.89099


1


1.088989


2



<ID 0.00810


8


0.009910


In summary, to increase the likelihood that the query optimizer will generate efficient execution plans, you should not
use bind variables. Bind variable peeking might help. Unfortunately, it is sometimes a matter of luck whether an
efficient execution plan is generated. The only exception is when the new extended cursor sharing of Oracle
Database 11g automatically recognizes the problem.


1)
Any feature should be used only if the advantages related to its utilization outweigh the
disadvantages. In some situations, it is easy to decide. For example, there is no reason for
not using bind variables with SQL statements without a WHERE clause (for example, plain
INSERT statements).On the other hand, bind variables should be avoided at all costs
whenever histograms provide important information to the query optimizer.


2)
SQL statements processing little data: Whenever little data is processed, the parsing time
might be close to or even higher than the execution time. In that kind of situation, using
bind variables is usually a good thing. This is especially true for SQL statements that are
expected to be executed frequently. Typically, such SQL statements are used in data entry
systems (commonly referred to as OLTP systems).


3)
SQL statements processing a lot of data: Whenever a lot of data is processed, the parsing
time is usually several orders of magnitude less than the execution time. In that kind of
situation, using bind variables is not only irrelevant for the whole response time, but it also
increases the risk that the query optimizer will generate very inefficient execution plans.



Best Practices

How to eliminate Bind Variable Peeking
-
I

1)

Only create histograms on skewed columns.


2)

Use literals in where clauses on columns where you have histograms and
want to use them. Note that it’s not necessary to use literals for every
possible value of a skewed column. There may be only a few outlier values
that result in significantly different plans. With a little extra code you can
use literals for those values and bind variables for the rest of the values
that don’t matter.


3)

If you can’t modify the code, consider turning off Bind Variable Peeking by
setting the
_OPTIM_PEEK_USER_BINDS
parameter to false. You
won’t get the absolute best performance for every possible statement, but
you will get much more consistent performance, Keep in mind that this is
a hidden parameter and so should be carefully tested and probably
discussed with Oracle support prior to implementing it in any production
system.

4
)

You

can

also

consider

stronger

methods

of

forcing

the

optimizer’s

hand

such

as

Outlines
.

This

option

provides

a

quick

method

of

locking

in

a

single

plan,

but

it’s

not

fool

proof
.

Even

with

outlines,

there

is

some

possibility

that

the

plan

can

change
.

Also

note

that

this

option

is

only

palatable

in

situations

where

you

have

a

relatively

small

number

of

problem

SQL

statements
.



5
)

Upgrade

to

11
g

and

let

Adaptive

Cursor

Sharing

takes

care

of

all

your

problems
.


How to eliminate Bind Variable Peeking
-
II

References & Readings

Antognini
, Christian.
2008. Troubleshooting Oracle Performance.
Apress
:
Berkeley, CA.


Lewis, Jonathan.
2006. Cost Based Oracle Fundamentals.
Apress
: Berkeley, CA.


Robert G Freeman.
2009.Oracle Database 11g New
Features.Tata

Mcgraw
-

Hill.


Sam
Alapati
.
2009.OCP Oracle Database 11g New features for Administrators.
Tata
Mcgraw
-

Hill.


Ask Tom (
Kyte
)
http://www.oracle.com/technology/oramag/oracle/06
-
jan/o16asktom.html


Kerry Osborne’s Oracle Blog:
http://kerryosborne.oracle
-
guy.com/