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
One comment 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:

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>

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.
  • Good sources of Oracle information
    Pretty often I am being asked about various online and printed source of Oracle-related information. Finally I decided to create a blog post here where I will start to accumulate all the goodies that I just "happen" to find. Maybe I will eventually create even some kind of a guide... Let's see.Before we start - a couple of disclaimers:* It is
  • Analytic functions in real life
    Yesterday while working on a real production report I found a requirement that forced me to come up with a bit nontrivial usage of analytic functions. Here is the problem (translated to SCOTT-level terms):- I have a table that contains a DATE column (HIREDATE)- Input parameter is also a date (IN_DT)- For each department I need to show all rows that have HIRE