• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Execute Immediate hard parsing

Hi,
I have a condition where I am using several variables in my query (in WHERE clause). It has lot of OR coditions. Based on the user selection, one of the condition becomes true and clause in conjuction gets executed.
e.g.
OR
                   (pi_date_type = pkg_imo_constants.DATE_TYPE_SETT_AGE_0
                      AND TRUNC(trd.orig_settlement_date) = TRUNC(SYSDATE)
                   )
                   OR
                   (pi_date_type = pkg_imo_constants.DATE_TYPE_SETT_AGE_1
                      AND TRUNC(NVL(pi_end_date,SYSDATE)) BETWEEN TRUNC(TRD.TRADE_DATE) AND TRUNC(TRD.ORIG_SETTLEMENT_DATE)--AND TRUNC(trd.orig_settlement_date) >= TRUNC(SYSDATE)
                      AND TRUNC(trd.orig_settlement_date) - TRUNC(SYSDATE) = 1
                   )
                   OR
                   (pi_date_type = pkg_imo_constants.DATE_TYPE_SETT_AGE_2
                      AND TRUNC(NVL(pi_end_date,SYSDATE)) BETWEEN TRUNC(TRD.TRADE_DATE) AND TRUNC(TRD.ORIG_SETTLEMENT_DATE)--AND TRUNC(trd.orig_settlement_date) >= TRUNC(SYSDATE)
                      AND TRUNC(trd.orig_settlement_date) - TRUNC(SYSDATE) = 2
                   )
                   OR
                   (pi_date_type = pkg_imo_constants.DATE_TYPE_SETT_AGE_3
                      AND TRUNC(NVL(pi_end_date,SYSDATE)) BETWEEN TRUNC(TRD.TRADE_DATE) AND TRUNC(TRD.ORIG_SETTLEMENT_DATE) --AND TRUNC(trd.orig_settlement_date) >= TRUNC(SYSDATE)
                      AND TRUNC(trd.orig_settlement_date) - TRUNC(SYSDATE) >= 3
                   )


There are too many conditions like this.
Performance of this query is very poor, feedback I recieved that it is due to the complex conditions in WHERE clause.

Now there are 2 options to remove these I know
1. To create separate query for each condition.
2. Use Execute immediate

I want to know which of them would be better. I have heard problems related to hard parsing with dynamic sql.
If there is some other option, please let me know.

Thanks.
0
ankitupadhyay
Asked:
ankitupadhyay
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Going from memory here.  I cannot find where in the docs it talks about this.

If you use bind variables dynamic sql will not have to hard parse the sql.  Worst, a soft parse.  This should exactly miror creating several of the same SQL.

Just set up some experiments and check the ratios.



0
 
slightwv (䄆 Netminder) Commented:
I guess I should ask:  Is you system so strained that you need to worry about hard parsing?

I've only talked with a few DBAs that had databases where they had to account for them.  These were very large and very active data warehouses.
0
 
johnsoneSenior Oracle DBACommented:
I have had issues with hard parsing in very large very active OLTP databases.  Read as poorly written application.  Nothing was bound, so hard parsing became an issue when everything is hard parsed.

Anyway, in my experience with dynamic SQL, using bind variables is definitely the way to go.  It does avoid the hard parsing.  And, as mentioned by slightwv should bring into the soft parse world.  It would probably only be an issue if this was being run many times.  If this is an occasional query, you would probably end up hard parsing it anyway as it will get flushed.

Based on what you describe, I would get rid of the ORs and turn it into a dynamic SQL statement with bind variables.  That should improve your performance enough that any additional parsing or overhead of dynamic SQL is overcome by the performance of the simpler query.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now