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 PARSE ILLUSION

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

Leave a Reply