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

Oracle Built-in Functions – Part 1

Filed under: Oracle How To — H.Tonguç Yılmaz @ 11:38 am

1- Date Functions

a) ADD_MONTHS; Add Month(s) To A Date


SELECT SYSDATE today, add_months(SYSDATE, 2) "two-months-later" FROM dual ;

TODAY      two-months
---------- ----------
31/12/2006 28/02/2007

SELECT SYSDATE today, add_months(SYSDATE, -2) "two-months-before" FROM dual ;

TODAY      two-months
---------- ----------
31/12/2006 31/10/2006

b) MONTHS_BETWEEN; Returns The Months Separating Two Dates – MONTHS_BETWEEN(, )


-- how-many-months-between-100-days-before-and-200-days-after
SELECT MONTHS_BETWEEN(SYSDATE+200, SYSDATE-100) MONTHS_BETWEEN FROM dual;

MONTHS_BETWEEN
--------------
9,90322581

-- changed the signs
SELECT MONTHS_BETWEEN(SYSDATE-200, SYSDATE+100) MONTHS_BETWEEN FROM dual;

MONTHS_BETWEEN
--------------
-9,8709677

c) EXTRACT; returns the value of a specified datetime field from a datetime or interval value expression. Also can be used for XMLType data,

EXTRACT(XML) is similar to the EXISTSNODE function.


SELECT EXTRACT(YEAR FROM SYSDATE) year, EXTRACT(MONTH FROM SYSDATE) month FROM DUAL;

YEAR      MONTH
---------- ----------
2006         12

2- Case and Decode Functions


WITH airplanes AS (
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'SYS' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'HR' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'SYSTEM' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE object_id > 1000 and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE object_id > 10000 and ROWNUM < 3
UNION ALL
SELECT 150000 line_number,
'TONG' customer
FROM dual )
SELECT line_number,
CASE WHEN (line_number BETWEEN 1 AND 1000) THEN 'Part-One'
WHEN (line_number BETWEEN 1001 AND 10000) THEN 'Part-Two'
ELSE 'Other'
END AS Part_Info, customer,
DECODE(customer,
'SYS', 'American Airlines',
'HR', 'Intl. Leasing Corp.',
'SYSTEM', 'Northwest Orient',
'Southwest Airlines') AIRLINE
FROM airplanes ;

LINE_NUMBER PART_INF CUSTOMER                       AIRLINE
----------- -------- ------------------------------ -------------------
20 Part-One SYS                            American Airlines
44 Part-One SYS                            American Airlines
12088 Other    HR                             Intl. Leasing Corp.
12089 Other    HR                             Intl. Leasing Corp.
2563 Part-Two SYSTEM                         Northwest Orient
2565 Part-Two SYSTEM                         Northwest Orient
1002 Part-Two SYS                            American Airlines
1004 Part-Two SYS                            American Airlines
10086 Other    SYS                            American Airlines
10136 Other    SYS                            American Airlines
150000 Other    TONG                           Southwest Airlines

11 rows selected.

3- NULL functions


DROP TABLE t PURGE ;
CREATE TABLE t (
category  VARCHAR2(20),
outval    NUMBER(3),
inval     NUMBER(3));

INSERT INTO t VALUES ('Groceries', 10, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 100);
INSERT INTO t VALUES ('Groceries', 20, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 200);
INSERT INTO t VALUES ('Groceries', 30, NULL);

SELECT * FROM t;

CATEGORY                 OUTVAL      INVAL
-------------------- ---------- ----------
Groceries                    10
Payroll                                100
Groceries                    20
Payroll                                200
Groceries                    30

a) NVL, if the value is NULL returns given value


select category, nvl(outval, 0), inval fro

CATEGORY             NVL(OUTVAL,0)      INVAL
-------------------- ------------- ----------
Groceries                       10
Payroll                          0        100
Groceries                       20
Payroll                          0        200
Groceries                       30

b) NVL2, Returns First Value if NULL, Second Value if NOT NULL


SELECT category, NVL2(outval, -outval, inval)
FROM t;

CATEGORY             NVL2(OUTVAL,-OUTVAL,INVAL)
-------------------- --------------------------
Groceries                                   -10
Payroll                                     100
Groceries                                   -20
Payroll                                     200
Groceries                                   -30

c) COALESCE, Returns the first non-null value


DROP TABLE test PURGE ;
CREATE TABLE test (
col1  VARCHAR2(1),
col2  VARCHAR2(1),
col3  VARCHAR2(1));

INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');

SELECT * FROM test ;

C C C
- - -
B C
A   C
C
A B C

SELECT COALESCE(col1, col2, col3) FROM test;

C
-
B
A
C
A

4- String Functions

a) TRANSLATE, Character Replacement function


-- change "'" with "|"
SELECT TRANSLATE('comma,delimited,list', ',', '|') with_pipe FROM dual;

WITH_PIPE
--------------------
comma|delimited|list

-- In this demo a string is first encrypted then decrypted
SELECT TRANSLATE('this is a secret', 'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv') encrypted , TRANSLATE('p78o 8o 0 o42i4p', '0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz') decrypted FROM   dual;

ENCRYPTED        DECRYPTED
---------------- ----------------
p78o 8o 0 o42i4p this is a secret

-- turkish character example
SELECT TRANSLATE('Aç üş jı', 'ığüşöç', 'igusoc') all_english FROM dual;

ALL_ENGL
--------
Ac us ji

b) REVERSE, returns the reverse of the given string(but an un-supported function)


-- finding palidromes example
CREATE TABLE palidrome_test ( text VARCHAR2(100) );

INSERT ALL
INTO palidrome_test VALUES (’Eat More Bananas’)
INTO palidrome_test VALUES (’A Toyota’’s a Toyota’)
INTO palidrome_test VALUES (’Never odd or even.’)
INTO palidrome_test VALUES (’Some men interpret nine memos.’)
INTO palidrome_test VALUES (’Palindromes are the enemy of productivity’)
SELECT * FROM dual;

SELECT rownum, CASE
WHEN text_filtered = REVERSE(text_filtered)
THEN ‘Yes’
ELSE ‘No’
END AS palindrome
FROM   ( SELECT text, UPPER(REPLACE(TRANSLATE(text,’ ‘’,.:;!?”‘,’ ‘),’ ‘)) AS text_filtered FROM   palidrome_test );

ROWNUM PAL
---------- ---
1 No
2 Yes
3 Yes
4 Yes
5 No

5- Numeric Functions

a) GREATEST and LEAST, Returns the largest and the smallest of multiple values


SELECT GREATEST(9, 67.6, 10) the_great, LEAST(9, 67.6, 10) the_small FROM dual;

THE_GREAT  THE_SMALL
---------- ----------
67,6          9

b) NANVL, Returns Alternate Number If The Value Is Not A Number


CREATE TABLE fpd (
dec_num    NUMBER(10,2),
bin_double BINARY_DOUBLE,
bin_float  BINARY_FLOAT);

INSERT INTO fpd VALUES (0, 'NaN', 'NaN');
COMMIT;

SELECT bin_double, NANVL(bin_double, 0) FROM fpd;

BIN_DOUBLE NANVL(BIN_DOUBLE,0)
---------- -------------------
Nan                   0

SELECT bin_float, NANVL(bin_float, 0) FROM fpd;

BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
Nan                  0

6- Other

a) SOUNDEX, Returns Character String Containing The Phonetic Representation Of Another String


SELECT CASE WHEN SOUNDEX('doych') = SOUNDEX('deutch') THEN ‘Match’
ELSE ‘No match’
END comparison1,
CASE WHEN SOUNDEX('pee-air') = SOUNDEX('pierre') THEN ‘Match’
ELSE ‘No match’
END comparison2
FROM DUAL;

COMPARIS COMPA
-------- -----
No match Match

b) VSIZE, returns the Byte Size


SELECT VSIZE('Tonguc') character, VSIZE(5) numb, VSIZE(SYSDATE) dat FROM dual;

CHARACTER       NUMB        DAT
---------- ---------- ----------
6          2          7

- DATE type stored as seven bytes; “century, year, month, day, hour, minute, second”,
– VARCHAR2 type stored as its length,
– NUMBER type uses variable-length scientific notation;
* Oracle stores a number in base 100 format.
* Each byte can store 2 digits.
* One byte is reserved for the exponent.
* When a negative number is stored, an additional byte is required for the sign (-).
* NUMBER(10) and NUMBER will take the same amount of space.
* They will also shrink to the minimum size required to store the contained data.
* The maximum precision for a number is NUMBER(38)


select vsize(1100) from dual;

VSIZE(1100)
-----------
2 /* 2 bytes : Note that the number is stored as (11*10exp2) Thus it requires 1 byte for the two digits (11) and one byte for the exponent(2) */

select vsize(-10.02) from dual;

VSIZE(-10.02)
————-
4 /* 4 bytes : One for sign, 2 for digits(1002) and one for exponent(-2) */

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

References Used :
Complete grouped list of Oracle functions
Oracle® Database SQL Reference 10g Release 2 (10.2)

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers