Do we need to take care of Hard Parses only when Library Cache related Latches are on top ?

I just concluded a 3 months Production Optimization Project. The problem summary was an overall slowness of the production system, which is a three tier architecture. The Production System includes a 2 Node 10.2.0.5 Database with 4 Application Servers configured with Connection Pool. As with all my Performance Project, this time too I started off with a one round of discussion with all the stake holders. These stake holders manage the production system and were called upon to express their opinion on the overall slowness. While everyone voiced together that the slowness is consistent the entire day, they also mentioned that they are confident that the issue is with the Oracle Application Server and the way it manages the Connection Pool. The rationale behind this assumption was the Connection Pool Statistics, which says that as the load increases, the number of Connection Pools configured increase drastically and are not freed until the load comes down. The peak starts at 11 am and, since the production system is slow, the load comes down by 9 pm. The business hours of the customer is 9 am to 5 pm.

Initial investigation revealed that with Automatic Shared Memory Management enabled, the Shared Pool had grown upto 40GB. This is pretty high. I have not seen any configuration that requires such a huge Shared Pool. This was surely a cause of concern and therefore, this was further drilled down to one of a serious bug in the application. This was Hard Parse.

An Application or Database flooded with Unshared Application queries is considered as an Unscalable Application. Slowly or Gradually, as the User Load increase, the performance issues are bound to come. In this case, the Hard Parse / Second ratio was around 180 per second. While there were many queries that were using Literals, the most surprising part was the Hard Parse failures, which were almost 68% of the total Hard Parses. Therefore, once a patch to fix the queries that were using Literals was applied, there was no significant improvement.

The most challenging part of this assignment was the reluctance from the Application Team to accept the Hard Parses or the Parse failure to be an Issue. The debate from the Application Team was that reducing the Hard Parses would not yield any benefit as they do not see any Library Cache related latches on top. They have been taught to see the AWR reports and look to the top events. Reducing the number of waits of the top most component seemed to be on top of the list for Application team. While this is true, to some extent, but in case of Hard Parses, this would not work.

This is the reason, the title of my blog is “Do we really need to take care of Hard Parses only when Library Cache related Latches are on top ?”. In this case, the Hard Parses can be easily related to the contention that we observed on the Connection Pool. Usually, the response time of a Connection Pool or a Shared Server Architecture should be less than a Second. If this goes high, we might see a slowness or contention on Connection Pool, as other users may have to wait for the pool to be released. This again explains, why the number of connections pools increased as the load increases.

Hard Parses are a performance killer, also called as a Silent Killer. Many a times, Hard Parses does not cause significant waits on Library Cache latches. It depends on the resources available at the Database Server. In this case, it was a 2 Node RAC with 56 CPU’s on each machine. The Hardware configuration was enough and therefore, the contention was not that significant to cause Latch Free waits. As we all know, Hard Parse takes more time than a Soft Parse or a No Parse and this resulted in a signifcant waits or contention on Connection Pool. Also, as mentioned above, Hard Parses are Silent Killer. With that said, if these are not taken care of at a initial stage, this can bring down the Production System (as it did in the case of my Customer).

To summarize, Hard Parses are to be addressed even if these are not visible as a Latch Contention. In case of my customer, as soon as the Hard Parses and Parse failures were fixed, the performance of the production system improved drastically and was consistent through out the day. The users were able to enter their transactions in the business hours. Further, the contention on the connection pool came down as well.

Advertisements

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. Some of these are my real life examples, which I hope, you would find interesting. Comments are always a welcome. The Technical Observations & Views here are my own and not necessarily those of Oracle or its affiliates. These are purely based on my understandings, learnings and resolutions of various customer issues.

8 Responses to Do we need to take care of Hard Parses only when Library Cache related Latches are on top ?

  1. Prasad says:

    Good post Vivek,

    It would be very much helpfull if you can upload the part of AWR report which is having top wait event and the details about hards parse,latch activity,load profile… etc, from which you came to know the root cause.

    Like

  2. Mahesh Padhmanabhan says:

    Hi Vivek. Thanks for sharing the issue.

    May i know how the hard parses and parse failures were fixed.

    regards
    Mahesh

    Like

    • Vivek says:

      Hi Mahesh,

      In this case, since it was an appllication bug, the queries failing were removed from the application. Since these were failing, the application team were sure that these are not required but unknowingly left in the application. As mentioned in one of my comment, it is a very bad application design. Fixing these, reduced the Hard Parses/Second as well.

      Regards
      Vivek

      Like

  3. Bhavik Desai says:

    Good investigation Vivek.
    May i ask, what was the real cause for having parse failure ? When you say ‘parse failure’ , are u referring to awr.’failed parse elapsed time’ statistic ?
    Would you mind sharing, load profile and Instance Efficiency Percentages ?
    Have you tried changing ‘cursor_sharing’ ?
    Can large parse time makes ‘session connection establishment’ process lengthy and thus PREVENT other connections to even established connection with db?

    Like

    • Vivek says:

      Hi Bhavik,

      Sorry for the delay in replying. I checked for “parse count (failures)” statistics in the AWR report. Unfortunately, this is not available in the Instance Efficiency section.

      The queries causing this was identified using 10046 trace. There were some queries that were failing with Syntax Error. I would say it a very bad design and exception handling.

      Regards
      Vivek

      Like

  4. lalu says:

    Hello Vivek,

    superb explanation as usual.

    Like

  5. PD says:

    Thanks Vivek, good stuff.

    I am just wondering what were the top wait events or wait classes when the things were bad and when it improved? Were these high on ‘Concurrency Wait’ waitclass or elsewhere?

    Its good work and no doubt hard parses are killers always but just was wondering if in this case could we relate the performance problem directly with anything in the DB Time Model which also improved post fixing the issue?

    Thanks.

    Like

    • Vivek says:

      PD,

      As mentioned in my blog, since there was sufficient hardware, there were no predominant waits observed at the db level. Since hard parsing is a resource & time consuming process, it had an impact on the concurreny at the Middle Tier with waits on Connection Pooling. This is the reason my blog heading says “Do we really need to work of hard parses only when Library Cache latches are on top ?”.

      Once resolved, there was a significant improvement on “Transaction Per Second” as the connection pools were now easily available for more number of users.

      Regards
      Vivek

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s