Monday, January 16, 2012

Oracle Shared Pool: Hash-Value and Parsing Concept

Oracle server keeps the most commonly used SQL and PL/SQL statements in the Shared Pool while the less commonly used statements are phased out.

What is Hash Value:

Lets say if I write the below statement in sqlplus

SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE empno = 1345;

So the following things will happen implicitly by Oracle

  1. Oracle will convert the characters in the SQL to their ASCII equivalent numeric codes. 
  2. These ASCII characters are then passed through a hashing algorithm which  produces a single hashed value. 
  3. Oracle (Server Process) then checks to see if that hashed value already exists in the Shared Pool. If it does, the user’s Server Process uses the cached version of the statement to execute the statement.

What is Parsing:

So if the Oracle's server process doesn't find Hash value as mentioned in point 3 above, the process will go for the parsing by adding extra cost to the SQL processing. How does the parsing increase the cost ? well parsing has to perform following tasks

  1. Syntax error check
  2. Privileges check on the underlying objects against data dictionary, in our case its emp table .
  3. Gathering stats for the objects referenced for the underlying object
  4. Preparing the query execution plan based on available stats
  5. generating P-code

V$SQL, V$SQLAREA, V$SQL_TEXT, V$SQL_PLAN can be used to view above points

Cache hit vs Cache miss:

Finding a matching SQL statement in the Shared Pool is referred to as a cache hit. Not finding a matching statement and having to perform the parse operation is considered a cache miss. Therefore, maximizing cache hits and minimizing cache misses is the goal of all Shared Pool tuning

1 comment: