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:
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).
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:
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:
.
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:
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:
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:
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.
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:
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:
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:
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.
I had the privilege to read both your blog and the corresponding support message and I'm a bit surprised.
You asked for an simple easy way to ensure that the statistics for the Oracle dictionary are OK.
And note #871455 explicitely states that on Oracle 10g (or above) you should make sure to have collected the dictionary statisitics.
(which is fairly easy with brconnect)
Thus, you definitively should see the both tables when you're looking for tables that do have statistics (LAST_ANALYZED IS NOT NULL).
I got the impression that you got confused by the "make sure you don't have the 9.2.0.5 optimization in your system anymore!"-stuff here, which basically wanted to say: those replacement DBA_VIEWS and SYNONYMS shouldn't be there anymore.
Looking after statistics does make much sense (to me, at least), when the first step for Oracle >=10g was to re-collect them.
I agree with you that it would have been nicer if the support processor would have got your misunderstanding earlier. On the other hand, based on the last years of Oracle support, I don't recall any other case in which that kind of misunderstanding occured.
Thus, I wouldn't rate this as too critical or severe.
Just my 2c on this.
regards,
Lars
Thanks for your feedback.
I'm not rating it as critical either. It's a wake up call for people to check what they are actually doing.
Since information is widely available people just follow instructions and don't ask questions anymore or completely trust whatever information they get.
If you would follow the strict instructions, you would end up with a SAP system that doesn't have data dictionary statistics anymore. Having the statistics in place according to SAP Note 871455 is stated before the instructions followed.
Even if you have the statistics in place, data dictionary statistics have a recommendation to be calculated every 100 days (or after specific events).
It's a fact though that the initial SQL statement isn't useful and using it you cannot identify the Oracle databases which have deprecated content.
This blog is just one example, I'm sure there are other examples. Earlier today I heard for example that a specific Oracle event should not be set on Oracle 10.2.0.5.
Apparently the automated Oracle parameter check scripts sais it does have to be set and an Earlywatch report sais it doesn't need to be set.
Kind regards
Tom
The difference with the last example is that SAP support agreed and stated to rewrite the automated check script so the event is flagged as no longer needed in Oracle 10.2.0.5. That is the correct action from a customer point of view.
Kind regards
Tom
As you see, we're really doing what's reasonably possible to help customers to have an easy approach to complex technology components like databases management systems.
I really appreciate it to see people like you share their experiences with SAP basis components via SDN - heck, I do it a lot myself 🙂
Yet I'd like to mention, that the 'issue' discussed in here, really is something that usually doesn't put any problem to your system.
You've old stats on your dictionary objects? Well, in 99.999% of all cases you won't see a measurable change in the systems response times, if you change this.
Rule-of-Thumb approaches likes "Look for the statements that have the most buffer gets/row or total buffer gets" are just that: rule of thumb.
In many cases the overall effect of tuning these statements is negligibility since these are often not related to any user/dialog transaction.
And since you cannot save spare-cpu-cycles, the tuning effect ends up to be near zero.
EWA in this sense is just a bunch of rule-of-thumbs. It's meant to provide hind-sights to system admins so that things aren't overlooked. As starting points.
They aren't meant as to-do-lists - and here we agree again: it's about thinking and reasoning out yourself, what you do with your system.
Keep up your work!
regards,
Lars
Thanks for your feedback.
I would like to see more administrators active on SCN really, there is a developers community and business process expert community but where are all those administrators at.
In my opinion they perform important work with a lot of diversity and complex technology.
I agree on the fact that the blog is not about gaining tremendeous performance.
It started out as a blog to give information to community members how to check for expensive SQL statements and identify some SAP notes to solve them.
It expanded to interaction with SAP support and trying to get awareness out there so community members would check the actions they are performing rather than just performing them.
When a customer receives a Go Live check report or an EWA, he will want to see to-do-lists created by the content of those reports so we (or 2nd level support for EWA) do spend some time on creating to-do-lists.
I agree with you that not everything has to end up in those to-do-lists.
Kind regards
Tom
I understand how you feel personally. Personally I've done this level of analysis in the past(several years ago). Would I spend time analyzing this type of issues now or in future? No, I wouldn't. Why?
1) As Lars mentioned, DBMS is too complicated. What you see today may not be relevant tomorrow after applying just one patch.
2) I always perform VED (Vital, Essential, Desirable) analysis on tasks that are on my plate. Unless users complain about performance, proactive monitoring is just a desirable task. I normally don't spend time analyzing the SQL statements proactively. I've 40 hours every week. If users constantly complain about poor performance, then yes, ST04/DB02 analysis would become vital task.
3) If I start analyzing SQL statements even when the customers don't have performance issues, I know I could retire just by doing that. Your story is just one example. I know we can come up with several stories like that. What's ROI?
I can't speak for SAP support team but my guess-why they responded the way they did- is that note is meant to be used by several customers with SAP running on several different H/W platforms. I'm sure they've a process in place for amending/changing the contents of notes. That process may not be straight forward. As Lars pointed out, the note is not wrong; it is probably misleading. (Frankly speaking, I didn't review any of the notes you referenced; my response is based on a high level understanding of your story). So SAP support read the note, reviewed your story and thought the issue was not critical.
(As a side note, I normally don't open messages but whenever I open a message, I get a reasonably quick and actionable response. I'm planning to write a blog on how to get a response that you want from Global Support Team). Under similar conditions, my response to my customers may not be any different from SAP support team's response.(Note: If I've 10 messages in my queue with 3 production down situation, 4 critical performance issues and rest 3 are issues such as yours, I would definitely try to return your message back to you as soon as possible).
My 2 cents.
Regards,
Bala
Thanks for your feedback.
I didn't start proactively on this issue, it was a point in a go live check report. If it's reported someone should check it, else the go live check report is useless right.
Concerning ROI:
I wouldn't write out such actions if I would perform this during my daily work hours. If you know where to look and what to do about it, it doesn't take that long to investigate it.
I performed these actions outside of my work hours because it looked like a nice topic for community members to know how to find expensive SQL statements and to give an idea how to find information to solve them.
I didn't have to create the customer message if I didn't want to either but I found the statement results to be the same before and after the actions thus making the statement useless to start with and I wanted to try and get it corrected.
If there are serious performance issues, we do spend time on them and create a proper report (nothing that looks like this blog content) where the performance losses are situated and what actions to take.
The customer won't mind that I'm putting so much effort into a single issue either, it will only make them happy to see the effort I do.
I do get reasonable response times from SAP Support most of the times and like I wrote it's only one example, it doesn't mean all my experiences are bad.
In this case SAP support was also confused by the SAP note instructions. If I would sell a product and I don't understand my own products manual, I would rewrite it.
The instructions of th SAP Note are SQL statements for Oracle, they don't change depending on the H/W platform.
I wanted to address the fact that there are misleading, confusing instructions out there and that you should think about what you are doing. Information is so easily available people tend to just follow the instructions.
Kind regards
Tom
"I didn't start proactively on this issue, it was a point in a go live check report. If it's reported someone should check it, else the go live check report is useless right."
No. I'll perform VED analysis on items listed in go-live check report. I know even simple requirements such as "bring patch level current" in a complex environment is difficult if not impossible to implement.
"it doesn't take that long to investigate it."
This is subjective. Even 15 minutes may be too long in some situations.
"The instructions of th SAP Note are SQL statements for Oracle, they don't change depending on the H/W platform".
This is an interesting (generic) statement. Not sure why Oracle/SAP release h/w specific parameters/patches.
Thanks,
Bala
We do administration for SAP systems which are part of our own company, we do administration for other companies and we also have hosting services.
The decision whether or not to perform an item of a Go Live check report can depend on the agreement that exists.
Bringing the patch level to the current level will often be a decision based on either release management agreements or based on a customer decision.
The statement mentioned will work fine on different platforms as the DBA views are Oracle specific and are not related to the H/W platform.
It is not related to parameters or patches.
Kind regards
Tom