The Power of System Statistics / CPU Costing

An Example from my Blog on “DBA – Developer Relationship ! A fine example of collaboration”

On 20th April 2009, I wrote a blog on DBA-Developer Relationship, and the collaborative effort to optimize the performance of a Business Critical Batch Process. In the blog, I mentioned about the database version, which was 8.1.7.4. For an “All India Oracle User Group”, I was writing an Interesting article for their AIOUG Journal “Oracle Connect” and thought of sharing this experience to the User Group Forum. The article, labeled as “Efficient Indexing Strategy”, mostly concentrated on the issues due to the Number of rows, fetched by an Index and finally discarded during the Table fetch.

As an example, assuming a simple query on a table EMP and an Index on ENAME (no index on deptno). If I execute a Query to check for ENAME=’VIVEK SHARMA’ in deptno=10, the optimizer might opt for an Index on ENAME. Further assuming, since VIVEK SHARMA is a very common name, the Index Scan fetches around 30 rows, but there is only 1 VIVEK SHARMA in deptno 10. Therefore, when the additional predicate is applied during Table Scan, 29 rows from the Index Fetch will be discarded and these 29 rows are the throw-away rows. Efficient Indexing eliminates these throw aways, as these impact the performance of a Production System.

While writing this Article for AIOUG, I came across an Interesting and Amazing Optimization, which was not actually available in Oracle 8i and 9i (without system statistics). With System Statistics, the way Indexes are scanned, have changed and if you create a test case and run this across each of these versions, including Oracle 10g, you would notice this change.

Again, since my previous blog is inaccessible, let me post the content of that blog here and then we would discuss about the change implemented after introduction of system statistics.

The content of my Blog Posted on 20th April 2009 starts here.

Thomas Kyte wrote a very good paragraph on DBA – Developer Relationship in his book “Expert One-on-One”. He truly says that this relationship should be healthy. During my AIOUG presentation, I had a slide on this topic and I went on explaining about the importance of this relationship. I have come across many performance issues, where, the issues come up because Developers work in isolation, without bothering much about the performance of their application queries and this indirectly means, leaving the job of optimization for the DBA’s. This impacts the scalability of an entire production system. This also means that the application queries are revisited for optimization, based on the recommendations by the DBA’s.

Let me present a real life example, wherein, a discussion with the Developer helped me optimizing performance of a critical process. Recently, I was working on one Severity 1 performance issue. The problem summary is overall slowness at the database level and the main contributor to this performance issue being, Hard Parses and Huge Resource Intensive Queries. Surprisingly, The database version is 8.1.7.4.

While optimizing the performance of a critical process, we came across a top query. The entire process use to take almost 11 hours, wherein, this problematic step takes 8 hours or sometimes fails with ora-1555. Therefore, it was evident that optimizing this step would bring down the completion time of the entire process. This particular step had three different queries and based on the inputs from the developers, and 10046 trace files, the contribution of each of these were 7 hours, 25 minutes and 25 minutes respectively. Therefore, it is clearly the first query that takes significant amount of time.

Query Text

SELECT opnamt_gl, entdate, argdate
FROM ORDER_ALL
WHERE customer_id = :b1
AND ((invtype = 5 AND status IN('IN','CM'))
OR (status IN('FC','CO')))
AND opnamt_gl != 0; ## This last predicate is opnamt_gl not equal to 0;

This query executes in a loop and processes almost 1 Million customers. There is a Composite Index on (customer_id, status, invtype) and this query takes almost 2 seconds to process a customer. Based on this data, the next two queries are executed. The table ORDER_ALL is 16 GB in size. Since the last 2 columns of the Index is used as OR or IN predicate, the index scan is only done based on the customer_id. The Query fetches only 1 row for each customer.

A Casual Discussion that helped Optimizing this code

While we were discussing this with the developer of this process, he mentioned that this query takes 2 seconds, whereas, on another database it takes 31 milliseconds. I casually asked him to select all the columns used in the query, commenting all other columns used in the WHERE clause and execute it on both the databases. The query executed was :

SELECT opnamt_gl, entdate, argdate, invtype, status, opnamt_gl
FROM ORDER_ALL
WHERE customer_id = :b1;

Though, the complete query fetches only 1 row on both the databases, when this modified query was executed on each of these, it revealed that on the problematic database, it is fetching approx 115 rows for each customer against 25 rows on another. It is this difference that is taking resource and the additional time.

This 115 rows fetch was taking 2 Seconds. The issue was 2 seconds for each customer and this was not acceptable. The challenge was to reduce this processing time for each customer. As an optimizing technique, we executed the query, only on an indexed column, i.e.customer_id, in the WHERE clause and we got 115 rows. I concentrated on the values in the other columns of the query that were used in the WHERE clause of the Original Query and was surprised to see that only 2 rows had a non zero opnamt_gl. The original query has a predicate opnamt_gl != 0. When asked, the developer replied that for all the customers, there will be only 1 or 2 rows with non zero value and this input helped me in recommending an interesting optimizing technique.

As mentioned earlier, the query fetches only 1 row, which means, out of the (approx) 115 rows for each customers, 114 rows are discarded. TKPROF also showed this behaviour. Therefore, instead of discarding 114 rows, why not get 2 rows from an index and discard 1 row. By now, the developer understood that we are planning to recommend a new composite index on (customer_id, opnamt_gl) and therefore, before we recommend anything, he himself mentioned that they tried creating this index but the response time did not improve. The reason they did not see any performance gain was the condition opnamt_gl != 0. The recommendation provided here gave a major performance relief. The steps involved for optimizing this piece of the code were :

  1. A Function based Index.
  2. Create a View. This is an additional step since the db version is 8174.
  3. Modify the query so that it queries the view.

We primarily targetted the Conditions

WHERE customer_id = :b1 and   opnamt_gl != 0;

We created a function based Index on (customer_id, case when opnamt_gl=0 then null else ‘Y’ end). Since the db version is 8174, CASE statements cannot be used in a pl/sql block, therefore we created a view as :

create view ORDER_ALL_VW as
SELECT opnamt_gl, entdate, argdate, customer_id
FROM ORDER_ALL
WHERE ((invtype = 5
AND status IN(‘IN’,’CM’))
OR (status IN(‘FC’,’CO’)))
AND (case when opnamt_gl=0 then null else ‘Y’ end) = ‘Y’;

Then the Query was modified as

SELECT opnamt_gl, entdate, argdate FROM ORDER_ALL_VW WHERE customer_id = :b1;

With this optimization, the query response time was 3 msec for a customer and with this the completion time of this query for 1 million customer came down from 7 hours to 7 minutes. This optimization also guaranteed that the process would not fail with ora-1555.

Definetely, a discussion on the optimizing strategy with the developer helped us optimize the performance of a critical query. Developers know their data well, and in my view, this recommendation should have come from the developers. For this, developers needs to be well conversant with database features and explore each of these while writing an application query.

The content of my Blog Posted on 20th April 2009 ends here.

Starting Oracle 9i, with System Statistics Collected and Oracle 10g (CPU Stats are Mandatory), a normal composite Index on customer_id, opnamt_gl helped optimizing the performance of this query. The issue, without system stats is, the optimizer cease to use an Index for any search on NOT EQUAL TO predicate and therefore, in 8174, even after creating a normal index yield no performance benefit.

While writing for AIOUG Journal, I created a test case to check for the performance of this query across each version. In Oracle 8i, the query was doing huge Logical Reads, Oracle 9i (with system stats) and 10g, the I/O’s dropped drastically. In Oracle 9i, without System Statistics, the behaviour was same as Oracle 8i.

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 The Power of System Statistics / CPU Costing

  1. Raghav says:

    Hi Vikram,
    There was a small blog by you on consistnt gets. Its no longer available at :
    http://viveklsharma.blogspot.com/2007/11/consistent-gets-myth.html

    Can you please provide with the new link

    Like

    • Vivek says:

      Hi Raghav,

      For unknown reason, my previous blogspot site is unaccessible. But, I do have backup of these blogs and can easily post them here. Therefore, if you feel that any of my previous blogs were useful, do let me know and I would have them here.

      BTW..my name is Vivek and not Vikram 🙂

      Like

  2. santhosh says:

    Hi Vivek,

    I am very much impressed with your blogs. But i am unable to view the sql statement clearly , is there any way you can help us.

    Like

    • Vivek says:

      Hi Santhosh,

      It seems to be an issue with your Internet Browser…anyways, can you check whether the queries on any of the other postings are visible to you ?

      Regards
      Vivek

      Like

  3. balasaheb says:

    Hi,
    I am reading ur blogs.
    I am oracle dba.
    In one of the my database,
    one of the user executing “truncate table command”.
    And commang get hang or pretty much slow.
    Why?
    that time , CPU utilization goes 100% percent.
    I cheked in db level, there is no any specific event.
    No load at db level. I checked another user’s utilization in glance. i killed some of the process, which are top at the glance , but no use.
    what is cause for this utilization?
    How can i find,which users utilize more CPU Utilization.
    How can i minimize CPU utilization.
    I am waiting for ur reply.

    Like

    • Vivek says:

      What is the db version ? Also, check whether the table is in Dictionary Managed Tablespace. In case of DMT, cleanup of Extents require recursive calls and updates/deletes/inserts to dictionary tables. This sometimes take too much of time.

      Regards
      Vivek

      Like

  4. balas says:

    Hi,
    How can i get ur previous blog information regarding performance issues?

    Ur new Fan.
    Commit.

    Thanks And Regards.
    Balasaheb

    Like

    • Vivek says:

      Hi,

      The previous blog URL is still unaccessible and I have lost all hope of getting it activated. Fortunately, I have the backup of all my previous posts’. Therefore, do let me know which post you would be interested in and I would post this here.

      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