ARE HARD PARSE SQL GOOD FOR ORACLE DATABASE ?
There are two types of parsing in oracle database
- Soft parse
- Hard parse
Hard parse means to perform additional SQL checks for an executed SQL, every time when it enter inside shared pool (library cache) considering it as a new entrant.
A typical example will be,
- Admission or onboarding of new employee in an organization
- Admission or onboarding of new student in an university
- Admission of new patient in a hospital
If a new employee join an organization, the organization has to perform all the prechecks like background verification, new employee account, salary account, technical or non technical work training, induction, policies etc..
There are lot of procedures to complete for the new employee which is an overhead in terms of cost and time. If there is already an existing old employee who stayed for long time, then there is no need for these additional processing right? Hard parsing can be depicted as follows,
- Similarly, consider database as an organization.
- Shared pool as the employee onboard processing department or team in organization.
- SQL processing as the employee background checks processing.
- Newly entering SQL as a new employee.
- All these processing steps for a new employee is considered as hard parse. If all these processing steps are already complete for an old employee then it is soft parse
Hard parsing stages include
- Syntax check
- Semantic check
- Security check
- Shared pool check
If the executions of a particular sql id is equal to once, then it means the SQL is hard parsed
kish@x3z6zx9<^>select count(*) from v$sqlarea where executions=1;
COUNT(*)
--------------------------------------------------
515
The sql_id with single execution can be identified which are unique
kish@x3z6zx9<^>select sql_id from v$sqlarea where executions=1 order by executions desc;
SQL_ID
-------------
dxg261d9ss0ck
55qsxrz72s0gd
1h7zt6jks80pa
9ngq09yhhc1qf
gvtpnhc5ww1rn
cfk4qsrg681sz
Hard parsing depth can also be identified with the total number of sql statements which has same FORCE_MATCHING_SIGNATURE. If the number of FMS is considerably negligible, then they can be ignored. But there are hundreds or thousands or millions of same FMS for multiple sql statements, then think about the resource usage and consequences. If one is ready to power up the resources with tons of memory and CPU and some extra cash, then this post is not for them. If one acts in a preventive and conservative manner, then hard parse needs to be eliminated to flatten the resource usage curve.
kish@x3z6zx9<^>col force_matching_signature for 999999999999999999999999999999999
col cntr for 9999999999
select * from
(
select force_matching_signature,
count(*) cntr
from v$sqlarea
group by force_matching_signature
order by cntr desc
)
where rownum < 6;
kish@x3z6zx9<^>kish@x3z6zx9<^> 2 3 4 5 6 7 8 9
FORCE_MATCHING_SIGNATURE CNTR
---------------------------------- -----------
16425079020282941956 259
0 164
8758528229247075989 8
6043408675031356560 2
17192947727905198177 2
Each sql statement has different sql_id but same force_matching_signature
kish@x3z6zx9<^>select sql_id,count(*) from v$sqlarea where force_matching_signature=16425079020282941956 group by sql_id;
SQL_ID COUNT(*)
------------- --------------------------------------------------
gvmftumnkcbd2 1
g7uq5sdk7ccpr 1
a93zsyanhsu8h 1
3cufct3q88w7n 1
53nqzmfyunywq 1
dy6c511aqx1fk 1
These details can also be checked in v$sysstat and v$sys_time_model
kish@x3z6zx9<^>select STAT_NAME,VALUE from v$sys_time_model where STAT_NAME like '%parse%';
STAT_NAME VALUE
---------------------------------------------------------------- -------------
parse time elapsed 12410728
hard parse elapsed time 7309616
failed parse elapsed time 2718
failed parse (out of shared memory) elapsed time 0
hard parse (sharing criteria) elapsed time 65909
hard parse (bind mismatch) elapsed time 2138
6 rows selected.
To mitigate the risks of significant hard parse due to literals in predicates of sql , a typical workaround will be to set the cursor_sharing parameter to force and increase the session_cached_cursors parameter to cache the hot cursors to prevent high session connects and disconnects.
kish@x3z6zx9<^>show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
Diagnosis:
AWR report shows high user calls and hard parses
Execute to parse ratio is in negative value which implies application is connecting and disconnecting frequently
%SQL with executions>1 is too low. This shows that database handle parsing of SQL inefficiently
There will be loads of shared pool waits when there is a need for high hard parse
Connection management call elapsed time will be high if application has frequent disconnections from database
Below is the workaround for heavy hard parse
kish@x3z6zx9<^>alter system set cursor_sharing=force;
System altered.
kish@x3z6zx9<^>alter system set session_cached_cursors=300 scope=spfile;
System altered.
kish@x3z6zx9<^>startup force
ORACLE instance started.
Total System Global Area 4710043648 bytes
Fixed Size 2261128 bytes
Variable Size 3321892728 bytes
Database Buffers 1375731712 bytes
Redo Buffers 10158080 bytes
Database mounted.
Database opened.
kish@x3z6zx9<^>show parameter cursor
NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
--------------------------------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
cursor_sharing
string
FORCE
session_cached_cursors
integer
300
In the results, there are queries with bind variables substituted in place of literals by forcing cursor sharing. In this way, database automatically use bind variables as alternative option instead of generating from application side
kish@x3z6zx9<^>col VERSION_COUNT for 9999999
kish@x3z6zx9<^>col EXECUTIONS for 9999999
kish@x3z6zx9<^>col LOADS for 9999999
kish@x3z6zx9<^>col INVALIDATIONS for 999999999
kish@x3z6zx9<^>col PARSE_CALLS for 99999999
kish@x3z6zx9<^>col SQL_TEXT for a30
kish@x3z6zx9<^>select SQL_ID,
2 SQL_TEXT,
3 VERSION_COUNT,
4 EXECUTIONS,
5 LOADS,
6 INVALIDATIONS,
7 PARSE_CALLS
8 from v$sqlarea
9 where sql_text like '%kish.xtbl%';
SQL_ID SQL_TEXT VERSION_COUNT EXECUTIONS LOADS INVALIDATIONS PARSE_CALLS
------------- ------------------------------ ------------- ---------- -------- ------------- -----------
1qr1x0kzamay7 select country from kish.xtbl 1 2 1 0 2
where :"SYS_B_0" > :"SYS_B_1"
gnjxgk41sztbk select * from kish.xtbl where 1 105 1 0 105
xid between :"SYS_B_0" and :"S
YS_B_1" * :"SYS_B_2"
cdfyxf3rk7ypn select * from kish.xtbl where 1 2 1 0 2
xid between :"SYS_B_0" and :"S
YS_B_1" +:"SYS_B_2"
Summary:
- Hard parse up to some extent can be tolerated. But excessive hard parse is bad for database health and its performance.
- Efficient solution should be to modify application mechanics by pooling the connections and apply bind variables in place of literals rather than adjustments from database