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
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 