Poor performance when accessing Oracle DBA views – the story

Introduction

Hello SCN Community

I received a Go Live check report form SAP which stated expensive SQL statements existed for a particular SAP system. So I decided to check if I could find SQL statement which could be improved.

Identifying the expensive SQL statement(s)

The start point is transaction DB02 also referred to as DBA cockpit in the recent SAP product versions.

Picture 1.1

To find expensive SQL statements you can check the Shared Cursor Cache which you can find under Performance -> SQL Statement Analysis -> Shared Cursor Cache (see picture 1.1).

Double-click on it to go to the selection screen for the shared cursor cache.

Picture 1.2

Here I usually start by clicking on the Buffer gets option (see picture 1.2) under List Sort (as most expensive statements also have the most buffer gets). The most important ones are Disk Reads, Buffer gets and Elapsed time.

Now click yes to get the overview.

Picture 1.3

After checking the top SQL statements by sorting buffer gets, I sorted the list on Elapsed Time to find out the top 3 remained the same.

You can see that the top 3 are executed 412 times (first row) with buffer gets ranging from 56 million to 114 million.

Picture 1.4

Let's take a look at the first SQL statement. To view the full SQL statement click on the SQL statement column (in the result - picture 1.3) to see the full statement.

Now you have some options here (picture 1.4), on top you can request DDIC information, next to it a explain plan (to check how the data is accessed, index, full table scan etc) and a button to get to the call point in ABAP (easy to identify from which program or function the statement is coming). If you click the ABAP call point button and you get an error DB6_DIAG_VIEW_SOURCE: "program source not found", SAP Note 1309309 - "Application Info" is missing could help you resolve that error.

Now when you look at the statement itself you can see that it selects data from the dba_segments view and the dba_tablespaces view. Since there are a lot of customers who have had expensive SQL statements and had them investigated by SAP, a buck load of SAP notes exists on SQL statements.

Information to improve the SQL statement

Picture 1.5

Let us take a look at some SAP notes we can find. Using the right keywords on the right source is very important when you search for information online. If you are interested in finding valuable information on internet, you are welcome to read one of my previous blogs on this topic: How to find valuable information on the internet. To search for SAP notes I used the keywords "expensive SQL dba_sgements" (see picture 1.5).

Picture 1.6

As a result of the search I only find 6 SAP notes (see picture 1.6). That is great isn't it. You can already see I checked SAP Note 871455 as it seems to be the most relevant for my situation as I'm looking at "possible" poor performance of the dba_segments view.

Following SAP Note instructions

Next I check the content of SAP Note 871455, since it's a SAP system residing on Oracle 10g I scroll down to point 4. Oracle 10g.

The first remark is to make sure dictionary statistics and fixed object statistics are implemented as described in SAP Note 838725. I have those in place, you can check one of my previous blogs on this topic by the way (how to calculate dictionary and system statistics along with the regular statistics run):Improving Oracle performance by maintaining exception table DBSTATC.

Alright moving on to the next point For problems relating to DBA_SEGMENTS, note the following points(extract from SAP Note 871455). Great this looks likes the right section.

In SAP Note 871455 it states you have to check that there are no remainders from earlier optimization measures and you can find those under paragraph Oracle 9i (9.2.0.5 or higher). Let us take a look.

There I can find the following information:

Ensure that only BW systems create statistics for the Oracle DDIC. If the following query returns tables such as TAB$ and IND$ in a non-BW system, delete the statistics as described in SAP Note 558746 (in the lower part of the note) (extract from SAP Note 871455):

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'SYS' AND LAST_ANALYZED IS NOT NULL;

Where is the where?

Now maybe it's just me but why isn't there a where clause in this statement? It sais in the text check if TAB$ and IND$ are returned.

Imagine you go to a car dealer and you want to buy an Audi R8 (it's an imagination so it's ok). The dealer has around 200 Audi R8's on the parking lot and he has a map with an index on which he can see where that specific Audi R8 is parked. If he sais check each car on the parking lot and if you find that specific Audi R8 let me know, I doubt he would sell much.

In the same logical sense, let's rewrite the above statement to only look for table name TAB$ or IND$ (since that is what we are really looking for).

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'SYS' AND LAST_ANALYZED IS NOT NULL AND TABLE_NAME='TAB$' OR LAST_ANALYZED IS NOT NULL AND TABLE_NAME='IND$';

The result

The result:
TABLE_NAME
----------
IND$
TAB$

So we do actually find these entries, as such, following the instructions in blue (see above) we should delete the statistics as described in SAP Note 558746 - Better Oracle Data Dictionary BW Performance.

SAP Note 558746 states that the solution is only valid for BW 2.x and BW 3.x SAP systems. I will ignore this as long as the instructions make sense.

Cleaning up

Let's perform the action.

Picture 1.7

In the lower part of SAP Note 558746 you find SQL statements to delete the Oracle data dictionary statistics. Copy the lines and paste them in SQLPlus and execute them.

Back to the main SAP Note

Once that is done we return back to the section on Oracle 10g in SAP Note 871455. There is another bullet points for dba_segments, extraction from the content of the SAP Note:

Therefore, as described in the "Oracle 9i (9.2.0.5 or higher)" section, check whether problematic segments exist , if necessary, run the script relating to DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS again.

Alright, back to the Oracle 9i section and checking for content on problematic segments. When I do a search on DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS in SAP Note 871455 I can only find the following in the Oracle 9i section:

If you use 9.2.0.5 on HP-IA64, the definition for the tablespace_fix_segment_extblks procedure is missing. Therefore the above script terminates at runtime with the following error: PLS-00302: component 'TABLESPACE_FIX_SEGMENT_EXTBLKS' must be declared

.

Checking the DBA segments

Now this is somewhat confusing, instead of using proper numbering in the note or something similar and referring to the numbering the Oracle10g section refers to TABLESPACE_FIX_SEGMENT_EXTBLKS. It sais if you use 9.2.0.5, no I'm on Oracle10g so I read on and I notice the words in bold. Alright so I should look up, in SAP Note 871455 that is, where I find the following:

Execute the script Segments_DBA_SEGMENTSPrecalculation.txt of Note 1438410. This generates a list of DBMS_SPACE_ADMIN commands if required.

Now we are getting somewhere again, let us take a look at SAP Note 1438410 - SQL: Script collection for Oracle which I happen to know already (useful stuff in it, you should check it out). This note gives a zip file which has SQL scripts that you can execute in DBACOCKPIT (or in Oracle).

Picture 1.8

Among all the scripts I can find the Segments_DBA_SEGEMENTSPrecalculation.txt.

Picture 1.9

Let's run this and take a look at the results. To run this I will use the SQL Command Editor in the local SAP system (transaction DB02).

The result

Picture 2.0

The result is nothing. In SAP Note 871455 the following is stated on the result of the script:

If all segments are converted cleanly, the script Segments_DBA_SEGMENTSPrecalculation.txt may no longer return lines.

Done?

Good news, this means we are done. The instructions for section Oracle10g for DBA_SEGMENTS are as they should be. But wait, does all this make sense? When you go through the actions performed in this blog you come to the conclusion you removed the Oracle data dictionary statistics available in Oracle for this SAP system.

The dark side

Before someone asks, yes I'm a fan of Star Wars (I guess I give the stereotype administrator geek some credit). If I could choose my role I would be a Jedi SAP Administrator :)

This is a great example of what the dark side can do. Information on what I consider to be the dark side can be found in one of my other blogs: To read or not to read the manual, that's the question.

Of course if you delete statistics, you should recreate them to get the correct ones so I performed a system statistics and dictionary statistics run. When you perform the initial statement again to check if the old statistics are present you get the following result:

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'SYS' AND LAST_ANALYZED IS NOT NULL AND TABLE_NAME='TAB$' OR LAST_ANALYZED IS NOT NULL AND TABLE_NAME='IND$';

The result, Euh?

The result:
TABLE_NAME
_________
IND$
TAB$

This makes it look like after all the instructions (which take about six pages in Word in blog format) were followed that nothing seemed to have changed.

The statement in the note that if you find entries like IND$ or TAB$ you should perform the actions seems to be useless since you get the same result after performing all of the instructions.

A customer message is called for

I decided to create a customer message and I handed my six page long Word blog to SAP to see what they could make out of it. I also raised the point that the instructions didn't state to perform new statistics runs, which seemed odd to me as you delete the statistics.

The first feedback I received offered me a rewritten SQL statement (of the SQL statement in which I inserted the "where" clause.

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'SYS' AND LAST_ANALYZED IS NOT NULL AND (TABLE_NAME='TAB$' OR TABLE_NAME='IND$');

The rewritten statement looks nicer but it doesn't change the result.

Confusion on both sides

After getting some conversation between both parties, I received the feedback to follow the instructions and recalculate the statistics (been there done that) but there is no proper way to check if I had changed anything.

Confusion caused by following instructions for Oracle 10g in the Oracle 9 section, jumping from SAP note to SAP note, performing actions from SAP notes which clearly state they are not valid for Oracle 10g, missing instructions and so on. My request to rewrite some parts to avoid other customers to get confused was returned with a request to "forget" the confusion. Rewriting the instructions would be too complicated.

A challenge rises

Of course I like a challenge so this sounded like a small challenge, one I was willing to take up. Too complicated that doesn't exist in my dictionary.

This calls for a call

My first step to get started to find an alternative was to call one of my colleagues who knows a lot about Oracle. I asked him if he could provide me with some available views which I could use to find an alternative. Most of the times Google is my friend but Google isn't my only friend.

One of the views he mentioned is DBA_TAB_STATS_HISTORY. I immediately found it interesting and I checked the available columns to see if I could come up with a proper statement.

The statement

So I whipped up a statement which could provide valuable information:

SELECT TABLE_NAME,STATS_UPDATE_TIME FROM DBA_TAB_STATS_HISTORY where (table_name = 'IND$' or table_name = 'TAB$');

I decided to execute the statement on an Oracle database which I suspected to be upgraded from Oracle 9 to Oracle 10 in the past.

The result before following the instructions

The result:
no rows returned

Now this looks interesting, if I now get a result after performing the instructions that is. I also needed to test on Oracle databases which were newly installed (Oracle 10g, Oracle 11g) to get some confirmation on the result by performing different test cases.

On all the correct or corrected SAP systems I do get a result back.

The result after following the instructions

The result:
TABLE_NAME STATS_UPDATE_TIME
__________ _________________
IND$ 10-FEB-11 02.17.56.787577 PM +01:00
TAB$ 10-FEB-11 02.18.10.412705 PM +01:00

I provided this information to SAP support but they insisted to forget the confusion and since I was the first to bring up the confusion, no one else seems to find the instructions confusing or noticed the SQL statement is useless. My response was that other customers should know the correct statement to be able to check if a certain Oracle database has old statistics yes or no. Even if the instructions themselves are valid (although they don't state you should recalculate statistics and I can imagine some forget to do so and as an effect run SAP on Oracle without those statistics) the initial check is wrong. As an effect you could perform the operation on Oracle databases where it's not needed and loose valuable time.

The response was that it was too complicated to change it. Because I didn't want to get involved in an endless discussion I closed down the customer stating I would contribute this information on SCN as I find it to be my duty as an active SCN contributor.

The correct instructions

What I found to be the correct instructions concerning the cleanup of old statistics was to use the following statement to check if the statistics are correct:

SELECT TABLE_NAME,STATS_UPDATE_TIME FROM DBA_TAB_STATS_HISTORY where (table_name = 'IND$' or table_name = 'TAB$');

If the result was no rows returned I followed the instructions from SAP Note 558746 to delete the statistics on the Oracle Data Dictionary.

Once the statistics are deleted, I perform a brconnect run to calculate the system statistics and once that was done, a brconnect run to calculate the dictionary statistics again:
brconnect -u / -c -f stats -t system_stats
brconnect -u / -c -f stats -t oradict_stats

After performing those actions I run the SQL statement again:

SELECT TABLE_NAME,STATS_UPDATE_TIME FROM DBA_TAB_STATS_HISTORY where (table_name = 'IND$' or table_name = 'TAB$');

The result then shows entries for IND$ and TAB$:
TABLE_NAME STATS_UPDATE_TIME
__________ _________________
IND$ 10-FEB-11 02.17.56.787577 PM +01:00
TAB$ 10-FEB-11 02.18.10.412705 PM +01:00

Conclusion

I waited on SAP support to get a view on the situation. I do have a lot of respect for SAP support services and persons who are receiving customer message questions.

However, when you come across such information you should do something with it. Stating it's too complicated isn't a very good statement, requesting to forget the confusion isn't either. If you receive a question and you understand the customers concern, do something with it. If you are not the correct person to pick up the issue, pass the issue on to the correct person. There is no problem to request assistance from others if it's needed.

This story could have been very different, I could have written a blog about the great interaction between me and SAP support and how the instructions were rewritten, the confusion wiped away. I could have been given the assurance that other customers would be following correct instructions but that's not how the story ended.

The bottom line is you should still think about what you are performing, I don't say you have to spit out every single piece of information that is handed to you but use common sense, don't put it away.