Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 21, 2004

Parsing in Oracle - Page 2

By Amar Kumar Padhi

Reduce hard parsing

The shared pool memory can be increased when contention occurs, but more important is that such issues should be addressed at the coding level. Following are some initiatives that can be taken to reduce hard parsing.

1. Make use of bind variables rather than hard-coding values in your statements.

2. Write generic routines that can be called from different places. This will also eliminate code repetition.

3. Even with stringent checks, it may so happen that same statements are written in different formats. Search the SQL area periodically to check on similar queries that are being parsed separately. Change these statements to be look-alike or put them in a common routine so that a single parse can take care of all calls to the statement.

Identifying unnecessary parse calls at system level

select parse_calls, executions, 
	substr(sql_text, 1, 300) 
from v$sqlarea 
where command_type in (2, 3, 6, 7); 

Check for statements with a lot of executions. It is bad to have the PARSE_CALLS value in the above statement close to the EXECUTIONS value. The above query will fire only for DML statements (to check on other types of statements use the appropriate command type number). Also ignore Recursive calls (dictionary access), as it is internal to Oracle.

Identifying unnecessary parse calls at session level

select b.sid,, b.value 
from v$sesstat b, v$statname a 
where in ('parse count (hard)', 'execute count') 
and b.statistic# = a.statistic# 
order by sid;

Identify the sessions involved with a lot of re-parsing (VALUE column). Query these sessions from V$SESSION and then locate the program that is being executed, resulting in so much parsing.

select a.parse_calls, a.executions, substr(a.sql_text, 1, 300)
from   v$sqlarea a, v$session b
where  b.schema# = a.parsing_schema_id
and    b.sid = <:sid>
order  by 1 desc;

The above query will also show recursive SQL being fired internally by Oracle.

4. Provide enough private SQL area to accommodate all of the SQL statements for a session. Depending on the requirement, the parameter OPEN_CURSORS may need to be reset to a higher value. Set the SESSION_CACHED_CURSORS to a higher value to allow more cursors to be cached at session level and to avoid re-parsing.

Identify how many cursors are being opened by sessions

select a.username, a.sid, b.value
from   v$session a, v$sesstat b, v$statname c
where  b.sid = a.sid
and    c.statistic# = b.statistic#
and = 'opened cursors current'
order  by 3 desc;

The VALUE column will identify how many cursors are open for a session and how near the count is to the OPEN_CURSORS parameter value. If the margin is very small, consider increasing the OPEN_CURSORS parameter.

Evaluate cached cursors for sessions as compared to parsing

select a.sid, a.value parse_cnt, 
       (select x.value
        from   v$sesstat x, v$statname y
        where  x.sid = a.sid
        and    y.statistic# = x.statistic#
        and = 'session cursor cache hits') cache_cnt
from   v$sesstat a, v$statname b
where  b.statistic# = a.statistic#
and = 'parse count (total)'
and    value > 0;

The CACHE_CNT ('session cursor cache hits') of a session should be compared to the PARSE_CNT ('parse count (total)'), if the difference is high, consider increasing the SESSION_CACHED_CURSORS parameter.

The following parse related information is available in V$SYSSTAT and V$SESSTAT views, connect with V$STATNAME using STATISTIC# column.

SQL> select * from v$statname where name like '%parse%';

STATISTIC# NAME                           CLASS
---------- ------------------------- ----------
       217 parse time cpu                    64
       218 parse time elapsed                64
       219 parse count (total)               64
       220 parse count (hard)                64
       221 parse count (failures)            64

5. Shared SQL area may be further utilized for not only identical but also for some-what similar queries by setting the initialization parameter CURSOR_SHARING to FORCE. The default value is EXACT. Do not use this parameter in Oracle 8i, as there is a bug involved with it that hangs similar query sessions because of some internal processing. If you are on 9i, try out this parameter for your application in test mode before making changes in production.

6. Prevent large SQL or PL/SQL areas from ageing out of the shared pool memory. Ageing out takes place based on Least recently used (LRU) mechanism. Set the parameter SHARED_POOL_RESERVED_SIZE to a larger value to prevent large packages from being aged out because of new entries. A large overhead is involved in reloading a large package that was aged out.

7. Pin frequent objects in memory using the DBMS_SHARED_POOL package. This package is created by default. It can also be created explicitly by running DBMSPOOL.SQL script; this internally calls PRVTPOOL.PLB script. Use it to pin most frequently used objects that should be in memory while the instance is up, these would include procedure (p), functions (p), packages (p) and triggers (r). Pin objects when the instance starts to avoid memory fragmentation (Even frequently used data can be pinned but this is a separate topic).

To view a list of frequently used and re-loaded objects

select loads, executions, substr(owner, 1, 15) "Owner", 
       substr(namespace, 1, 20) "Type", substr(name, 1, 100) "Text" 
from v$db_object_cache 
order by executions desc;

To pin a package in memory

SQL>exec dbms_shared_pool.keep('standard', 'p'); 

To view a list of pinned objects

select substr(owner, 1, 15) "Owner", 
       substr(namespace, 1, 20) "Type", 
       substr(name, 1, 100) "Text" 
from v$db_object_cache 
where kept = 'YES'; 

8. Increasing the shared pool size is an immediate solution, but the above steps need to be carried out to optimize the database in the long run. The size of the shared pool can be increased by setting the parameter SHARED_POOL_SIZE in the initialization file.


Reduce Hard parsing as much as possible! This can be done by writing generic routines that can be called from different parts of the application, thus the importance of writing uniform and generic code.

» See All Articles by Columnist Amar Kumar Padhi

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM