If you don’t ask, you will never find out why your system is performing poorly

We had a large operation that took up to a minute to run.  It was copying data to a staging area, running over a thousand data validation rules (~50,000 lines of code), and then formatting all of the validation errors for viewing.

Seven years ago, when we first put the procedure into production, it took about 20 seconds to execute and was never thought about again.

Recently, we wanted to reuse the same routine in a different system.  In the new system, there was motivation to run the routine over and over again, so a 20-second execution time was uncomfortable.

Therefore, we asked the question, what is this routine actually doing?  In about a minute we white-boarded the following steps:

1)    It is reading a few hundred records (one logical customer file) using indexed searches.

2)    It writes all of those records into a staging area.

3)    It runs 1500 little data validation routines against the staging area.

My intuition told me that none of those steps should take more than a second each, so if the routine was taking over 5 seconds, something was wrong. In our initial tests, the routine was taking up to a minute. Hmmmm….

After about an hour of analysis on the routine, we found that there was a single view with a function in the SELECT statement that had a cursor in it.  That function was also calling a function that had a cursor in it.  The view was returning about 80,000 records, so we were doing 160,000 context switches and queries. The really stupid thing is that all of the columns we needed were already in the view, so the function calls were completely unnecessary.

In a couple of hours, we fixed the problem and now the routine takes about 3 seconds.

I am sooooo embarrassed.

Share this!
Tagged with: ,
Posted in BLOG, PL/SQL, SQL
2 comments on “If you don’t ask, you will never find out why your system is performing poorly
  1. And as usual – a bit more technical side of calling user-defined functions in SELECT clause:
    http://wonderingmisha.blogspot.com/2013/04/performance-tuning-spending-time-is-not-ok.html

  2. >> The really stupid thing is that all of the columns
    >> we needed were already in the view, so the function
    >> calls were completely unnecessary.

    CBO usually removes unnecessary columns in the early stages(we can see it in “projection” section of plans).
    Am I right that in your case, the column alias to function from view was in the select list of your main query?

    Just example of eliminating unnecessary calls: https://gist.github.com/xtender/dadf038e3b29cd448175

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Disclaimer
The information presented on this blog is presented to provide general technical information. If, while attempting to apply any of the ideas, procedures, or suggestions herein, you experience any kind of programming or system problems or failure, it will be as a result of your own actions. Dulcian, Inc. and all authors of text found anywhere on this site, and all internally-linked Web sites, Mail Lists, Blogs and/or e-mail group discussion, disclaim responsibility for any user's actions and any damage that may occur based on information found on this website and associated Mail Lists, Blogs and/or e-mail group discussion. Any technical advice or directions found on or through this site is provided AS IS and its provided without warranty or any guarantee of its accuracy. You perform any modifications to programs or software AT YOUR OWN RISK.
  • NYOUG Training Event: "High Performance PL/SQL" on November 7, 2014
    If you want to be able to create contemporary high-performing PL/SQL code, welcome to my training event in NYC (hosted by NYOUG) on November 7, 2014:http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few […]
  • Oracle OpenWorld'14 Guide!
    I think, the biggest challenge for people coming to such huge events as Oracle OpenWorld is to navigate through all of the events that simultaneously happen at various locations. You are always under stress that you may miss something, or that you may hit a bad talk, or that you may not even know that […]