Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 31, 2006

How shared pool works in Oracle and binding example

Filed under: Oracle Concepts and Architecture,Oracle How To — H.Tonguç Yılmaz @ 5:14 pm

In my previous post on Introduction to Oracle Trace Utulity and Understanding The Fundamental Performance Equation I mentioned how a query is executed in Oracle and the importance of using bind variables for the scalability of your applications in Oracle. With this post I want to show how queries shared on a memory area called shared pool in Oracle, for this purpose I will use V$SQLAREA view;


-- reset the shared pool for the demostration, do ot run this at a production system, only maybe with your local XE
alter system flush shared_pool ;

-- lets create a bind variable at sql*plus and bind 100 to it
variable x number;
exec :x := 100 ;

-- lets get first name from employees table for employee_id 100
select first_name from employees where employee_id = :x ;

FIRST_NAME
----------
Steven

-- and check the shared pool for our first query
SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;

SQL_TEXT
-------------------------------------------------------

PARSE_CALLS EXECUTIONS
----------- ----------
select first_name from employees where employee_id = :x
          1          1

-- lets bind 110
exec :x := 110 ;

-- and get first name from employees table for employee_id 110
select first_name from employees where employee_id = :x ;

FIRST_NAME
----------
John

-- and check the shared pool for our first query
SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;

SQL_TEXT
-------------------------------------------------------

PARSE_CALLS EXECUTIONS
----------- ----------
select first_name from employees where employee_id = :x
          2          2

-- lets bind 110
exec :x := 120 ;

-- carefull this time I changed the query, upper SELECT FROM and WHERE
SELECT first_name FROM employees WHERE employee_id = :x ;

FIRST_NAME
-----------
Matthew

SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;

SQL_TEXT
-------------------------------------------------------

PARSE_CALLS EXECUTIONS
----------- ----------
SELECT first_name FROM employees WHERE employee_id = :x
1          1

select first_name from employees where employee_id = :x
2          2

Two important things I wanted to show here;
1- The first query experienced a hard-parse and since the second query was using bind variable and exactly the same query experienced a soft-parse,
2- Using bind variables is not enough to limit hard parsing overhead, you also need to share your application codes. The third query was different from the first two, SELECT FROM and WHERE was written upper. In order to overcome this setting development standarts and favoring package usage is needed, a function called fnc_get_first_name_by_employee_id for everyone.

Also with 10g V$SQL_BIND_CAPTURE view introduced to give the bind variable values;


SELECT DATATYPE_STRING, VALUE_STRING
FROM v$sqlarea a, V$SQL_BIND_CAPTURE b
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') and
a.HASH_VALUE = b.HASH_VALUE and b.ADDRESS = a.ADDRESS;

DATATYPE_STRING
----------------
VALUE_STRING
----------------

NUMBER
120

NUMBER
100

To show the cost of no binding I will use two examples, first with pl/sql and second with jdbc. Both will have a loop yo simulate a heavy On-line Transaction Processing(OLTP) system, like 1000 of people executing the same query.

a) JDBC example;


-- noBind JDBC
..
// record the start time
long start_time = System.currentTimeMillis();

// insert the rows
for (int count = 0; count < TOTAL_NUM_ROWS; count++) {
stmt.executeUpdate("insert into perf_test ( id, code, descr, insert_user, insert_date ) " +
"values ( " + Integer.toString( count ) + ", '123456789012345678901234567890', " +
"'12345678901234567890123456789012345678901234567890123456789012345678901234567890', " +
"USER, to_date('" + sdf.format(new java.util.Date(System.currentTimeMillis())) + "', 'YYYYMMDDHH24MISS'))");
}

// record the end time
long end_time = System.currentTimeMillis();

// display the total time taken to insert the rows
System.out.println("Total time for inserting " + TOTAL_NUM_ROWS +
" rows was " + (end_time - start_time) + " milliseconds");

..
-- bind JDBC
..
// create a PreparedStatement object
PreparedStatement myPrepStatement =
myConnection.prepareStatement(
"INSERT INTO perf_test " +
"(id, code, descr, insert_user, insert_date) VALUES "+
"(?, ?, ?, ?, ?)"
);

//SimpleDateFormat sdf = new SimpleDateFormat( "yyyyMMddHHmmss" );
// record the start time
long start_time = System.currentTimeMillis();

// insert the rows
for (int count = 0; count < TOTAL_NUM_ROWS; count++) {
myPrepStatement.setInt(1, count);
myPrepStatement.setString(2, Integer.toString(count));
myPrepStatement.setString(3, "123456789012345678901234567890");
myPrepStatement.setString(4, "12345678901234567890123456789012345678901234567890123456789012345678901234567890");
myPrepStatement.setString(4, "TONGUC");
myPrepStatement.setDate(5, new java.sql.Date(System.currentTimeMillis()));
myPrepStatement.executeUpdate();
}

// record the end time
long end_time = System.currentTimeMillis();

// display the total time taken to insert the rows
System.out.println("Total time for inserting " + TOTAL_NUM_ROWS +
" rows was " + (end_time - start_time) + " milliseconds");
..

With Prepared Statement…
Total time for inserting 2000 rows was 2703 milliseconds

WithOUT Prepared Statement…
Total time for inserting 2000 rows was 4281 milliseconds

b) PL/SQL Example;


set serveroutput on
declare

-- noBind PL/SQL
procedure step_1 is
TYPE rc IS REF CURSOR;
l_rc    rc;
l_dummy all_objects.object_name%TYPE;
l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000 LOOP
OPEN l_rc FOR 'select object_name
from all_objects
where object_id = ' || i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
END LOOP;
dbms_output.put_line(round((dbms_utility.get_time - l_start) / 100,
2) || 'seconds...');
END;

-- bind PL/SQL
procedure step_2 is
TYPE rc IS REF CURSOR;
l_rc    rc;
l_dummy all_objects.object_name%TYPE;
l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000 LOOP
OPEN l_rc FOR 'select object_name
from all_objects
where object_id = :x' USING i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
END LOOP;
dbms_output.put_line(round((dbms_utility.get_time - l_start) / 100,
2) || 'seconds...');
end;

begin
runstats_pkg.rs_start;

step_1;

runstats_pkg.rs_middle;

step_2;

runstats_pkg.rs_stop(100000);
end;
/

43,88seconds...
,63seconds...
Run1 ran in 4388 hsecs
Run2 ran in 63 hsecs
run 1 ran in 6965,08% of the time

Name                                  Run1        Run2        Diff
LATCH.library cache                187,190       4,273    -182,917
LATCH.row cache objects            513,635      50,094    -463,541
STAT...session pga memory        2,228,224           0  -2,228,224

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
973,738      69,950    -903,788  1,392.05%

PL/SQL procedure successfully completed.

Heavy loaded Oracle OLTP applications must be optimized for two important topics;

1- SQL Sharing,
2- Fast and reliable transaction handling.

Here I have given examples for JDBC and PL/SQL, please check documentation for others like .Net or ODBC. Before closing I recommend you the asktom thread given at references for deeper information and I want to mention three important related topics more;
1- Peeking of User-Defined Bind Variables
2- Caching Session Cursors
3- When to use CURSOR_SHARING

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
Using the Shared Pool Effectively Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Chapter 7 Memory Configuration and Use
http://asktom.oracle.com/tkyte/runstats.html
Let’s go deeper with Mr.Thomas Kyte

2 Comments »

  1. […] Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management 2- How shared pool works in Oracle and binding example 3- The Storage Hierarchy Summary in an Oracle […]

    Pingback by Oracle Concepts and Architecture - Part 1 « H.Tonguç YILMAZ Oracle Blog — October 6, 2007 @ 8:40 am | Reply

  2. […] bind variables, side effects on your system will not only be poor scalability as I mentioned in one of my previous posts but especially applications open to the Internet have many hidden vulnerabilities. Be carefull and […]

    Pingback by Without Bind Variables Your Code Is Also Less Secure « H.Tonguç YILMAZ Oracle Blog — October 6, 2007 @ 12:19 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: