Understanding Prepared Statements in Oracle

1
Understanding Prepared Statements in Oracle

• Improving Database Code Through Prepared
  Statements and Bind Variables

2
Abstract

• Using prepared SQL statements with bind
  variables has many advantages over simple
  Statements, including gains in performance,
  scalability, instrumentation, security, and
  code-clarity.  This presentation will introduce
  the concepts behind prepared statements and
  examine each of the practical benefits in turn.
  The session will touch on best practices for use
  with Oracle in client-server environments as well
  as J2EE web containers.

Chris Gates has been developing software
solutions using Oracle databases for six years in
business domains including higher-education,
publishing, health-care, and pharmaceuticals. 
Experience with specific technologies includes
ASP, Java (client-server and web-based), PL/SQL
(server-side and Oracle Forms).  He has taught
several courses at Washtenaw Community College,
including certification-track courses for Java
and Oracle Developer. Content and examples can
be downloaded from www.chrisgatesconsulting.com. c
gates_at_chrisgatesconsulting.com
3
Introductions
11
4
Understanding Prepared Statements

• Basic JDBC Syntax
• How Oracle Processes DML
• Advantages of Prepared Statements
• Advanced Concepts

5
Basic JDBC Syntax

• private void executeStatement(Connection conn)
• throws SQLException
• Statement statement null
• ResultSet resultSet null
• for (int id 0 id lt 200 id)
• try
• statement conn.createStatement()
• resultSet statement.executeQuery(
• "SELECT username FROM big_user_table
  WHERE id " id)
• SqlHelper.processResult(resultSet)
• finally
• resultSet.close()
• statement.close()

8,10
6
Statement vs. Prepared Statement

• ...
• for (int id 0 id lt 200 id)
• statement conn.createStatement()
• resultSet statement.executeQuery(
• "SELECT username FROM big_user_table
  WHERE id " id)
• SqlHelper.processResult(resultSet)
• ...

... for (int id 0 id lt 200 id)
preparedStatement conn.prepareStatement(
"SELECT username FROM big_user_table WHERE id
1") preparedStatement.setInt(1, id)
resultSet preparedStatement.executeQuery()
SqlHelper.processResult(resultSet) ...
7
How Oracle Processes DML

• Dynamic SQL Statement
• Static SQL PreparedStatement
• Data Manipulation Language (DML)
• Select
• Update/Insert/Delete
• Everything else
• CREATE TABLE ...
• COMMIT ...
• ALTER SESSION...

8
What Oracle Does
SELECT ename, job, d.deptno, dname FROM emp e,
dept d WHERE e.deptno d.deptno AND e.job
CLERK ORDER BY d.deptno ENAME JOB
DEPTNO DNAME ---------- --------- ----------
-------------- MILLER CLERK 10
ACCOUNTING SMITH CLERK 20
RESEARCH ADAMS CLERK 20
RESEARCH JAMES CLERK 30 SALES
9
How Oracle Does It

• Parsing
• Optimization
• Execution

2,7
10
Parsing

• Syntactical Analysis
• Semantic Analysis
• Work the shared pool

11
Parsing 1 Syntactic Analysis

• Tokenize query / verify syntax

select ename, job, d.deptno, dname from emp e
dept d where e.deptno d.deptno and e.job
"CLERK order deptno
SELECT ltlist of expressionsgt FROM ltlist of data
source objectsgt WHERE ltconditiongt ORDER BY ltlist
of expressionsgt
12
Parsing 2 Semantic Analysis

• Resolve references (latches)
• Verify privileges

SELECT ename, job, d.deptno, dname FROM emp e,
dept d WHERE e.deptno d.deptno AND e.job
CLERK ORDER BY deptno
Data Dictionary
Schema SCOTT
SCOTT.EMP (table) ENAME JOB DEPTNO
SCOTT.DEPT (table) DEPTNO DNAME
13
Parsing 3a Enter the Shared Pool

• Hash SQL and look in the Shared Pool

SELECT ename, job, d.deptno, dname FROM emp e,
dept d WHERE e.deptno d.deptno AND e.job
CLERK ORDER BY d.deptno
628938992
HASH
Oracle Instance
User 1
System Global Area
Shared Pool
User 2
Shared SQL Area
User 3
4
14
Parsing 3b Work the Shared Pool

• Add to shared pool if not found

SELECT ename, job, d.deptno, dname FROM emp e,
dept d WHERE e.deptno d.deptno AND e.job
CLERK ORDER BY d.deptno
628938992
HASH
VSQL (Data Dictionary view into SQL in Shared
Pool) HASH_VALUE SQL_TEXT ----------
--------------------------------------------------
--- 619739417 SELECT COUNT() FROM
USER_POLICIES V WHERE V.OB... 619739417 SELECT
COUNT() FROM USER_POLICIES V WHERE V.OB...
628938992 SELECT ename, job, e.deptno, dname
FROM emp e, d... 636388251 insert into
ccol(con,obj,intcol,pos,col) val... ...
5,6
15
Optimization

• Evaluate different access paths (more latches)
• Determine best path and save in shared pool

SELECT ename, job, d.deptno, dname
Plan A Cost 5
Plan B Cost 3
Plan C Cost 10
Optimizer

• Final Execution Plan / Row Source Generator
• 0 SELECT STATEMENT OptimizerCHOOSE
• 1 0 MERGE JOIN
• 2 1 SORT (JOIN)
• 3 2 TABLE ACCESS (FULL) OF 'DEPT'
• 4 1 SORT (JOIN)
• 5 4 TABLE ACCESS (FULL) OF 'EMP'

Data Dictionary
2
16
Execution

• Allocate cursor in private SQL area
• Bind values
• Run cursor
• INSERT/UPDATE/DELETE
• Locks / Modifications
• SELECT
• Identify active-set
• Fetch from cursor
• Close cursor

17
How Oracle Processes DML

• Parse
• Syntactic / Semantic / Shared Pool
• Optimize (Execution Plan)
• Generate plans
• Execute
• Process modifications / return results

18
Advantages of Prepared Statements

• Performance / Efficiency / Scalability
• Instrumentation
• Code-clarity
• Security

19
Performance / Efficiency / Scalability

• Performance (elapsed time)
• Efficiency (memory usage shared pool)
• Scalability (latching)

20
Performance Stats (1)

• TKPROF

SELECT ename, job, d.deptno, dname FROM emp e,
dept d WHERE e.deptno d.deptno AND e.job
'CLERK' ORDER BY d.deptno call count
cpu elapsed ------- ------ --------
---------- Parse 1 0.01
0.01 Execute 1 0.00 0.00 Fetch
2 0.00 0.00 ------- ------
-------- ---------- total 4 0.01
0.01
2
21
Performance Stats (2)
SELECT COUNT() FROM big_user_table22739 call
count cpu elapsed ------- ------
-------- ---------- Parse 1 0.07
0.08 Execute 1 0.00 0.00 Fetch
2 0.95 1.12 ------- ------
-------- ---------- total 4 1.02
1.21
22
Performance Stats (3)
SELECT username FROM big_user_table WHERE id
100 call count cpu elapsed -------
------ -------- ---------- Parse 1
0.08 0.07 Execute 1 0.00
0.00 Fetch 2 0.00 0.00 -------
------ -------- ---------- total 4
0.08 0.07

• (Parsing is expensive.)

23
Parse Faster With Shared Pool

• Optimization saves execution plan

SELECT username FROM big_user_table WHERE id
100
298300393
HASH
VSQL (Data Dictionary view into SQL in Shared
Pool) HASH_VALUE SQL_TEXT ----------
--------------------------------------------------
--- 2591785020 select obj,type,ctime,mtime,stime
,status,dataobj... 2591785020 select
obj,type,ctime,mtime,stime,status,dataobj...
298300393 SELECT username FROM big_user_table
WHERE object... 4049165760 select
order,columns,types from access where d_o... ...
24
Hard-Parse vs. Soft-Parse
25
Soft-Parse Is Better
--After added to Shared Pool --executed 4 times
in a new session SELECT username FROM
big_user_table WHERE object_id 100 call
count cpu elapsed ------- ------
-------- ---------- Parse 4 0.00
0.00 Execute 4 0.00 0.00 Fetch
8 0.01 0.00 ------- ------
-------- ---------- total 16 0.01
0.00
26
Soft Parse Isn't Enough
SELECT username FROM big_user_table WHERE id
100 SELECT username FROM big_user_table WHERE
object_id 250
298300393
HASH
1737037929
HASH
VSQL (Data Dictionary view into SQL in Shared
Pool) HASH_VALUE SQL_TEXT ----------
--------------------------------------------------
--- 2591785020 select obj,type,ctime,mtime,stime
,status,dataobj... 2591785020 select
obj,type,ctime,mtime,stime,status,dataobj... 173
7037929 SELECT object_name FROM all_objects
WHERE objec... 298300393 SELECT object_name FROM
all_objects WHERE object... 4049165760 select
order,columns,types from access where d_o... ...
27
What Can We Do?

• Problem
• Parse the first time (performance)
• Fill up Shared Pool (performance/memory)
• Solutions
• Build a your app to use a single query? (No.)
• Increase size of shared pool? (No.)
• Flush shared pool? (No.)
• ?

28
Why Is This Sad?
SELECT username FROM big_user_table WHERE id
100 SELECT username FROM big_user_table WHERE
id 250
298300393
HASH
1737037929
HASH
SELECT username FROM big_user_table WHERE id
?
42
HASH
29
Enter Prepared Statements / Bind Variables
SELECT username FROM big_user_table WHERE id
100 SELECT username FROM big_user_table WHERE
id 250
298300393
HASH
1737037929
HASH
SELECT username FROM big_user_table WHERE id
?
42
HASH
? 100
SELECT username FROM big_user_table WHERE id
?
HASH
42
? 250
30
How Prepared Statements Are Processed (First Time)
SELECT username FROM big_user_table WHERE id
?
42
HASH
? 100
Oracle Instance
User 1
Shared SQL Area HASH_VALUE SQL_TEXT ----------
--------------------- 42 select username
...
Private SQL Area ? 100
31
How Prepared Statements Are Processed (n-th Time)

• Parse
• Optimize (Optional!)
• Execute

Oracle Instance
Shared SQL Area HASH_VALUE SQL_TEXT ----------
--------------------- 42 select username
...
User n
User 1
Private SQL Area ? 250
Private SQL Area ? 100
32
Prepared Statements Performance
Could we parse less?
33
Could We Parse Less?
... for (int id 0 id lt 200 id)
preparedStatement conn.prepareStatement(
"SELECT blah blah blah") preparedStatement.se
tInt(1, id) resultSet preparedStatement.exe
cuteQuery() ... preparedStatement.close() .
..
loop parse bind execute close
34
Can We Parse Less?
... preparedStatement conn.prepareStatement(
"SELECT blah blah blah") for (int id 0 id
lt 200 id) preparedStatement.setInt(1,
id) resultSet preparedStatement.executeQuer
y() ... preparedStatement.close() ...
parse loop bind execute close
35
Performance / Efficiency / Scalability

• Use PreparedStatement avoid hard parses
• Parse once, execute many to avoid soft parses

36
Instrumentation (Simplify Log Analysis)

• SELECT expiry FROM users WHERE username 'scott'
  30ms
• SELECT lastname FROM client WHERE id 75
  500ms
• SELECT id FROM employee WHERE ssn '113-75'
  10ms
• SELECT lastname FROM client WHERE id 505
  430ms
• SELECT expiry FROM users WHERE username 'king'
  30ms
• SELECT zipcode FROM address WHERE id 224
  150ms

SELECT expiry FROM users WHERE username 1 08
scott SELECT lastname FROM client WHERE id 1
15 75 SELECT id FROM employee WHERE ssn
1 07 113-75 SELECT lastname FROM
client WHERE id 1 18 505 SELECT expiry
FROM users WHERE username 1 05 king
SELECT zipcode FROM address WHERE id 1
22 224
37
Prepared Statements Clarify Code

• ...
• public boolean authenticate(String username,
• String password,
• Date expiration)
• ...
• statement conn.createStatement()
• resultSet statement.executeQuery(
• "SELECT username FROM big_user_table "
• " WHERE username '" username "'"
• " AND expires gt " dateToSqlString(expiration
  )
• " AND password '" password "'")
• boolean authenticated resultSet.next()
• ...
• return authenticated

38
Static SQL Is Final

• ...
• private static final String QUERY
• "SELECT username FROM big_user_table "
• " WHERE username 1 "
• " AND expires gt 2 "
• " AND password 3"
• public boolean authenticate(String username,
• String password,
• Date expiration)
• ...
• preparedStatement conn.prepareStatement(QUERY)
  
• preparedStatement.setString(1, username)
• preparedStatement.setDate(2, expiration)
• preparedStatement.setString(3, password)
• resultSet preparedStatement.executeQuery()
• boolean authenticated resultSet.next()

39
Bind Variables Are Typed
... statement conn.createStatement()
resultSet statement.executeQuery( "SELECT
username FROM big_user_table " " WHERE
username '" username "'" " AND expires
gt " dateToSqlString(expiration) " AND
password '" password "'")

• private static final String QUERY
• "SELECT username FROM big_user_table "
• " WHERE username 1 "
• " AND expires gt 2 "
• " AND password 3"
• ...
• preparedStatement conn.prepareStatement(QUERY)
  
• preparedStatement.setString(1, username)
• preparedStatement.setDate(2, expiration)
• preparedStatement.setString(3, password)

2
40
Prepared Statements Are Secure

• ...
• statement conn.createStatement()
• resultSet statement.executeQuery(
• "SELECT username FROM big_user_table "
• " WHERE username '" username "'"
• " AND password '" password "'")
• boolean authenticated resultSet.next()
• ...

2
41
Normal Login
SELECT username FROM big_user_table WHERE
username 'scott' AND password 'tiger'
42
SQL Injection
SELECT username FROM big_user_table WHERE
username 'whoKnows' AND password
'whoCares' OR 'A''A'
SELECT username FROM big_user_table WHERE
(username 'whoKnows' AND password
'whoCares') OR 'A''A'
43
Prepared Statements Defeat SQL Injection Through
Typing

• private static final String QUERY
• "SELECT username FROM big_user_table "
• " WHERE username 1 "
• " AND password 2"
• ...
• preparedStatement conn.prepareStatement(QUERY)
  
• preparedStatement.setString(1, username)
• preparedStatement.setString(2, password)
• ...

SELECT username FROM big_user_table WHERE
username 'whoKnows' AND password
'whoCares'' OR ''A''''A'
44
Advantages of Prepared Statements

• Performance / Efficiency / Scalability
• Faster
• Better use of shared pool
• Fewer latches
• Instrumentation
• Simplified log analysis
• Code-clarity
• Static, final strings
• Implicit type conversion
• Security
• SQL injection

45
Advanced Concepts

• Using stored procedures
• Forcing cursor sharing
• Prepared statement cache (JDBC 3.0)
• When to avoid prepared statements / bind
  variables

46
Using Stored Procedures
CREATE OR REPLACE FUNCTION get_user(i_id IN
NUMBER) RETURN big_user_table.usernameTYPE IS
v_username big_user_table.usernameTYPE BEGIN
SELECT username INTO v_username
FROM big_user_table WHERE id i_id
RETURN v_username END / SHOW ERRORS
2
47
Using Stored Procedures
callableStatement conn.prepareCall( "
call ? SCOTT.get_user(?) ") for (int i
1 i lt count i) callableStatement.register
OutParameter(1, OracleTypes.VARCHAR)
callableStatement.setInt(2, i)
callableStatement.execute() username
callableStatement.getString(1)
8
48
Using Stored Procedures
49
Using Stored Procedures

• Pros
• Skip hard parse and most soft parses
• Compile-time verification of SQL
• Stored procedures available across environments
• Clean separation of SQL enables porting to other
  DBs
• Cons
• Java and PL/SQL
• Porting to other DBs not trivial

50
Forcing Cursor Sharing

• Why is this still sad?

SELECT username FROM big_user_table WHERE id
100 SELECT username FROM big_user_table WHERE
id 250
298300393
HASH
1737037929
HASH
SELECT username FROM big_user_table WHERE id
?
42
HASH
3
51
Cursor Sharing
52
Forcing Cursor Sharing

• Pros
• Simulate prepared statements with dynamic SQL
• Cons
• Only works with Oracle
• Still has to soft parse
• Weird explain-plan/tuning behavior
• Over-binding

53
Over-Binding
"SELECT name FROM contacts WHERE contact_type
'faculty' AND name LIKE '" nameSearch
"'"
"SELECT name FROM contacts WHERE contact_type
1 AND name LIKE 2"
Cursor Sharing does this
"SELECT name FROM contacts WHERE contact_type
'faculty' AND name LIKE 1"
You should do this
3
54
JDBC Prepared Statement Cache Introducing the
DataSource
DataSource
Client get Connection()d
Connection 1
Connection 2
Connection 3
Client conn.close()
Connection 2
8,9
55
JDBC Prepared Statement Cache
DataSource
(Client) prepare Statement
Prepared statements are cached in connection
Connection 1
Connection 2
Connection 3
(Client) ps.close()
Connection 2
56
JDBC Prepared Statement Cache
OracleDataSource dataSource SqlHelper.oracleData
Source() dataSource.setMaxStatements(25)
dataSource.setImplicitCachingEnabled(true)
conn dataSource.getConnection() ... for
(int id 0 id lt 200 id)
preparedStatement conn.prepareStatement(
"SELECT username FROM big_user_table WHERE id
1") preparedStatement.setInt(1, id)
resultSet preparedStatement.executeQuery()
SqlHelper.processResult(resultSet)
resultSet.close() preparedStatement.close()
...
57
JDBC Prepared Statement Cache
58
JDBC Prepared Statement Cache

• Pros
• Efficient
• Cons
• Only works in Java

59
When Not to Use Prepared Statements / Bind
Variables

• Non-DML
• Analytical queries
• Prepared statements OK
• Be careful with bind variables

"SELECT contact_type, COUNT() FROM contacts
WHERE account 'expired' GROUP BY
contact_type"
3
60
Conclusions
61
Conclusions

• Prefer prepared statements
• Parse once execute often
• Use bind variables where appropriate
• Don't over-bind
• Do not rely on Cursor Sharing
• Use JDBC prepared statement caching

62
References

• Ask Tomhttp// asktom.oracle.com
• Effective Oracle by Design, Tom Kyte
• Expert One-on-One Oracle, Tom Kyte
• Oracle 9i Oracle Concepts Memory Architecture,
  Shared Pool
• Oracle 9i Database Reference VSQL dynamic view
• Oracle 9i SQL Reference ALTER SYSTEM FLUSH
  SHARED_POOL
• Oracle 9i Oracle Concepts SQL, PL/SQL and Java
• Oracle 9i JDBC Developer's Guide and Reference
• JDBC 3.0 Reuse of PreparedStatementhttp//www.ora
  cle.com/technology/products/oracle9i/daily/jun24.h
  tml
• The Java Tutorial JDBC(TM) Database Access
  http//java.sun.com/docs/books/tutorial/jdbc/basic
  s/prepared.html
• PowerPoint is Evil http//wired-vig.wired.com/wir
  ed/archive/11.09/ppt2.html