|
Category :
Database
Resources -> Database Administration
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Reference
: Oracle Metalink, Oracle technet and Oracle Magazine
Let's start off with some
details on how oracle processes SQL statements.
Each
time an
application or a user connects to a database instance, it is
ultimately assigned to a server process that resides on the database
server. The server process provides the interface between the end-user
application and the database. It is responsible for checking SQL
statement syntax, determining how to execute the SQL statements,
executing the statements, and returning the
results to the end-user application. When a SQL statement is received
by the server process, a soft parse occurs. During this
phase, the statement syntax is checked for correctness, it is verified
that the user has the privileges to access the objects referenced in
the statement, and the SQL hash value (SHV) is computed on the
statement text. The SHV is a number used to easily identify the
statement within the database. Next, the library cache in the SGA is
searched to determine if the SHV already exists. This will occur if
the same or another server process has already run the identical SQL
statement. If the SHV is found, the server process retrieves the
information stored in the library cache about the statement. This
includes the algorithms used to access the various
objects in the query known as the execution plan. After the statement
has been retrieved, if bind variables were present in the statement,
the literal values are substituted and the operations specified by the
execution plan are performed by the server process. This is the
desired sequence of events since it produces the lowest overall
resource cost for executing SQL statements and produces the fastest
response time.
If the SHV corresponding to the SQL statement is not found in the
library cache during the soft parse, the server process must perform a
hard parse on the statement. During this operation, the
execution plan for the statement must be determined and the result
must be stored in the library cache. This is typically a
computationally expensive step and may be time-consuming for several
reasons. Depending upon the complexity of the statement, hundreds or
even thousands of different permutations may be tried before the
optimal execution plan is discovered. By default,
the optimizer will test as many as 80,000 different execution plans to
find the one with the lowest resource cost. To store the execution
plan, memory must be allocated in the library cache. During this
process, a number of latches must be acquired and held by the server
process. Latches are simply flags or semaphores used within the
database to assure that only one process at a time is writing to a
specific part of a common memory object such as the library,
dictionary, and buffer caches. If many sessions are concurrently
performing hard parses, contention for these latches may result. This
will decrease the response time for the query. If many simple unique
SQL statements are executed by many sessions, the shared pool may
become highly fragmented which will further exacerbate the latch
contention problem. In extreme cases, latch contention may become so
great that sessions are consuming more than 50% of their time waiting
to acquire the latches necessary to write to the library cache.
Response time and throughput for the queries will likely become
unacceptable
The following steps illustrate soft
and hard parses. Note that step 5 will force all subsequent SQL
statements to hard parse the next time that they are run so testing on
a live production system is inadvisable.
-
ALTER SESSION
SET sql_trace=true;
-
SELECT * FROM
dual WHERE 10=10;
-- Hard Parse
-
SELECT /*
Test */ * FROM dual WHERE 10=10;
-- Hard Parse
-
SELECT * FROM
dual WHERE 10=10;
-- Soft Parse
-
ALTER system
FLUSH shared_pool;
-
SELECT * FROM
dual WHERE 10=10;
-- Hard Parse
To identify the SQL statements that
are receiving a lot of parse calls execute the following query:
select sql_text, parse_calls,
executions from v$sqlarea
where parse_calls > 100 and executions < 2*parse_calls;
To identify the total amount of
parsing going on in the system execute the
following:
select name, value from v$sysstat where name like 'parse count%';
NAME VALUE
-----------------------------------------------
----------
parse count
(total) 123456
parse count
(hard) 12345
Analyzing
the Raw Trace file to get parsing details (Trace
output is from Oracle9i release 9.2.0 on Solaris) :
Step 1: ALTER SESSION
SET sql_trace=true;
Sets up the tracing.
Step 2: select * from
dual where 10=10
PARSING IN CURSOR #1 len=32 dep=0 uid=109 oct=3 lid=109 tim=328242274096 hv=2021
551448 ad='12cc8d4c'
select * from dual where 10=10
END OF STMT
PARSE #1:c=0,e=2160,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=328242274073
EXEC #1:c=0,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328242274741
FETCH #1:c=0,e=208,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=328242275207
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=328242276279
..
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
From the raw trace above, the
'PARSE #1' line indicates that there has been a parse call for
this statement. The 'mis=1' entry on this line indicates that this is
a hard parse.
Step 3: select /*
Test */ * from dual where 10=10;
PARSING IN CURSOR #1 len=44 dep=0 uid=109 oct=3 lid=109 tim=328245863439 hv=4155
56111 ad='21cc07fc'
select /* Test */ * from dual where 10=10
END OF STMT
PARSE #1:c=10000,e=1828,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=328245863418
EXEC #1:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328245863867
FETCH #1:c=0,e=167,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=328245864284
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=328245865330
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
Another 'hard' parse (mis=1) as
expected since this SQL has not been parsed before.
Step 4: select * from
dual where 10=10;
PARSING IN CURSOR #1 len=32 dep=0 uid=109 oct=3 lid=109 tim=328250975145 hv=2021
551448 ad='21cc5d4c'
select * from dual where 600=600
END OF STMT
PARSE #1:c=0,e=354,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328250975125
EXEC #1:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328250975561
FETCH #1:c=0,e=184,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=328250976009
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=328250977032
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
Since this select is a repeat of Step 2 it has already been parsed and, in this case,
the parse information is still in the library cache, the query is found in the shared pool and a soft parse
occurs. The parse line shows this as mis=0.
Step 5: ALTER system
FLUSH shared_pool;
This step 'removes' all the SQL in the shared pool
Step 6: select * from
dual where 600=600;
PARSING IN CURSOR #1 len=33 dep=0 uid=109 oct=3 lid=109 tim=328261680757 hv=2409
74811 ad='21fff274'
select * from dual where 600=600
END OF STMT
PARSE #1:c=60000,e=63411,p=0,cr=57,cu=0,mis=1,r=0,dep=0,og=4,tim=328261680736
EXEC #1:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328261681114
FETCH #1:c=0,e=138,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=328261681504
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=328261682605 XCTEND rlbk=0,
rd_onfiltered=1
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL OBJ#(222) '
(The trace output above follows large amounts of recursive SQL that
has also had to be reparsed due to flushing ALL SQL from the shared
pool). The query in this step is the same as the query in Step 2
and Step 4 Step 2 was a hard parse whereas Step 4 was soft parsed.
This time a hard parse is required since the SQL has been flushed from
the shared pool and so the parse line shows 'mis=1'.
TKProf Output:
If the output is formatted in
TKProf then it will look similar to the following:
Steps 2, 4 and 6:
select * from dual where 10=10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.03 0.32 0 4 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.03 0.32 0 13 0 3
Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: 109
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
Note: The Parse count above shows 3 parse calls. This consists of two Hard Parses
(Step 2 and Step 6) and a single soft parse (Step 4). Misses in library cache during parse:
2 records where the SQL was not found (i.e. a library cache miss) in the library cache and
indicates that 2 of the parses were hard parses. Step 4 found an existing entry in the library
cache for this statement and therefore, only a soft parse was necessary.
Step 3:
select /* Test */ * from dual where 10=10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 109
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
In this case the Query shows a hard
parse as the parse count is 1 and the 'Misses in library cache during
parse: 1'.
Querying
v$sqlarea and v$sysstat dictionary
views will give us an overall idea of parsing within the database
since the instance startup. However, using SQL_TRACE at the session
level will help us gather parsing information at a more granular level
which will help in diagnosing database performance problems due to
excessive parsing. A fix for this issue can be as easy as forcing the
developers to use bind variables with their SQL statements.
Additional Notes
(Added on 04/17/2007):
Things that can
be done to reduce Hard parsing:
- Make sure
bind variables are used as part of the SQL statements
- As much as
you can, try and have your application adopt the parse once and
execute many type of processing.
- If third
party applications are being used or if you are in a situation where
SQL statements cannot be changed to use bind variables, try the
possibility of automatic binding by setting cursor_sharing=true. This
can be set at the session level but extensive testing need to be done
since changing this parameter can affect execution plans for sql
statements.
- Try and use PLSQL with static SQL
statements as much as possible.
- For batch or bulk processing, use
bulk binding to do array DMLs which will reduce the overall number of
parsing.
Things that can
be done to reduce SOFT parsing:
- SOFT
parsing can be made "softer" by caching session's cursors. If PLSQL
code mostly uses static SQL statements, then you can make the session
cache the SQL statements by setting session_cached_cursors parameter
Example: SQL>
alter session set session_cached_cursors=40; |
