What to Do When Performance Problems Appear

23042011

April 23, 2011

I have a bit of a performance problem. To be more specific, I am preparing to give a presentation on the topic of Oracle Database performance tuning for a specific ERP product, and I have encountered a performance problem. Tuning the performance problem will requiring dropping too many slides – 59 slides for a 60 minute presentation with live demos taking up probably 20 minutes. I think that my performance problem stems from spending too much time in PowerPoint – in the screen capture below a person watching the presentation can click the smiley face in the purple box to have the computer read my notes to the viewer.

The notes that go along with the above slide are as follows:

So, what do you do when you encounter a performance problem… contacting (the vendor’s) support probably is not the best approach.

You must be specific – stating that the database is slow does not offer a suitable starting point for performance improvement. Pick one person who is encountering performance problems, and ask the person to describe what is happening – has this feature always been slow? Gather metrics that measure the current performance, otherwise you will not know if the changes that are made will help or hurt performance.

Work your way out from the database instance to the server, the network, and then the client. If the database instance reports that the vast majority of the time is spent waiting for the next request from the client, then you need to move on to checking the network and client computer. If the database instance reports that a lot of CPU time is being consumed, but not by the instance, then you need to look at the other processes that are running on the server. If the database instance reports significant amounts of CPU time or wait events, then you need to take a closer look at the server configuration, database instance configuration, and execution plans.

Once you implement a change you should again measure the performance to verify that it improved – you might then identify another issue that should be addressed. Avoid compulsive tuning disorder.

—

Just to make certain that that the above is clear, I put together this additional bit of information:

Of course, there are notes to go along with the slide:

Breaking it down again – when a performance problem is identified you will ideally take it down to the report or SQL statement level for analysis. That level very often utilizes 10046 extended SQL traces for analysis. The other suggestions provided on this slide will hopefully help you determine if the performance problem is in the database instance, or somewhere else.

—

If you had this performance tuning problem, what would you do (remember, there is only 60 to 120 seconds to convey the message of the slides)? The second of the above slides was originally intended to appear as a decision tree, but that organization never quite materialized.

On a related topic, I was reviewing the data captured by one of my tools and found an old friend of a SQL statement:

This is the SQL statement that is highlighted, second from the bottom:

The SQL statement directly below it is child 1 of the same SQL ID. I know that you just can’t wait to see the execution plan for this SQL statement (take a close look at the above picture to see the CPU and consistent gets for a 70 second capture period):

So, where does this SQL statement come from? If memory serves correctly, this SQL statement is submitted by the Oracle 10.2 ODBC connector. Oracle Database 11.2 comes up with a slightly different execution plan for the same SQL statement, but I do not know if the 11.2 ODBC connector ever submits the same SQL statement. The execution plan from Oracle Database 11.2.0.2 for the above SQL statement (STATISTICS_LEVEL = ALL):

Thank you for the idea – I can just say, “That slide speaks for itself, no point in discussing it now. Click the smiley and the darn computer will not stop talking.”

—

That made be think of a related topic. In the U. S. there is a TV trivia show named “Jeopardy!”, where the answers to the questions (edit, a couple of missing words added: are provided by the TV host) and the contestants must provide the correct questions for the supplied answers (I do not regularly watch the show). Not long ago two previous winners of the show were pitted against an IBM super computer, and the super computer won. I now wonder if this was a fair competition, after all how long does it take a computer to print out:

SELECT EVERYTHING FROM ENCYCLOPEDIA WHERE SHOW='JEOPARDY';

I have not yet watched the episode where the IBM super computer dominates the unsuspecting humans, but it appears that part of the show is on YouTube:

Thank you for the suggestion. The “Disable RULE Hint” option does not appear in the 10.2.0.1 or 10.2.0.2 Oracle client, but I do see that it is found in the 10.2.0.5 Oracle client. It would be interesting to see if this checkbox made a difference in the performance of this SQL statement (the execution plan does change, but both plans are currently completing in 0.01 or fewer seconds). I wonder if this SQL statement would simply disappear if the “Disable SQLDescribeParam” option were set for the ODBC connection?

I have seen a few cases where the better plan generated by the CBO made quite a difference in the aggregated data from Statspack, however this was with a client application with very bad, actually no connection management at all (reconnecting over and over again).

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: