Whenever a statement is
executed, Oracle follows a methodology to evaluate the statement in terms of
syntax, validity of objects being referred and of course, privileges to the
user. Apart from this, Oracle also checks for identical statements that may have
been fired, with the intention of reducing processing overheads. All this takes
place in a fraction of a second, even less, without the user knowing what is
happening to the statement that was fired. This process is known as Parsing.
Types of Parsing
All statements, DDL or DML, are
parsed whenever they are executed. The only key fact is that whether it was a Soft
(statement is already parsed and available in memory) or a Hard (all
parsing steps to be carried out) parse. Soft parse will considerably improve the
system performance where as frequent Hard parsing will affect the system.
Reducing Hard parsing will improve the resource utilization and optimize the
Oracle internally does the
following to arrive at the output of an SQL statement.
1. Syntactical check. The query
fired is checked for its syntax.
2. Semantic check. Checks on the
validity of the objects being referred in the statement and the privileges
available to the user firing the statement. This is a data dictionary check.
3. Allocation of private SQL
area in the memory for the statement.
4. Generating a parsed
representation of the statement and allocating Shared SQL area. This involves
finding an optimal execution path for the statement.
In point four, Oracle first checks
if the same statement is already parsed and existing in the memory. If found,
the parsed representation will be picked up and the statement executed
immediately (Soft parse). If not found, then the parsed representation is
generated and stored in a shared SQL area (Part of shared pool memory in SGA),
the statement is then executed (Hard parse). This step involves the
optimization of the statement, the one that decides the performance.
Oracle does the following to
find identical statements to decide on a soft or a hard parse.
a. When a new statement is
fired, a hash value is generated for the text string. Oracle checks if this new
hash value matches with any existing hash value in the shared pool.
b. Next, the text string of the
new statement is compared with the hash value matching statements. This
includes comparison of case, blanks and comments present in the statements.
c. If a match is found, the
objects referred in the new statement are compared with the matching statement
objects. Tables of the same name belonging to different a schema will not
account for a match.
d. The bind variable types of
the new statement should be of same type as the identified matching statement.
e. If all of the above is
satisfied, Oracle re-uses the existing parse (soft). If a match is not found,
Oracle goes through the process of parsing the statement and putting it in the
shared pool (hard).
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)
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, a.name, b.value
from v$sesstat b, v$statname a
where a.name 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
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 c.name = '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
Evaluate cached cursors for
sessions as compared to parsing
select a.sid, a.value parse_cnt,
from v$sesstat x, v$statname y
where x.sid = a.sid
and y.statistic# = x.statistic#
and y.name = 'session cursor cache hits') cache_cnt
from v$sesstat a, v$statname b
where b.statistic# = a.statistic#
and b.name = '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
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
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"
order by executions desc;
To pin a package in memory
SQL>exec dbms_shared_pool.keep('standard', 'p');
To view a list of pinned
select substr(owner, 1, 15) "Owner",
substr(namespace, 1, 20) "Type",
substr(name, 1, 100) "Text"
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
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
See All Articles by Columnist Amar Kumar Padhi