Skip to Content

Thoughts on RDBMS tuning!

Recently I worked with a few consultants from one of the largest software vendors. The objective was to evaluate the performance of their new product(Let us call it Target System). We decided to migrate R/3 QA system from current system(Source) to target system. We documented our test cases to compare the performance of our source system with the target system. They reviewed ST04 and evaluated the most expensive statements from the cache based on time elapsed. After spending a few hours, they recommended new indexes to help speed up the performance.

My thoughts are not in line with their recommendations. And unfortunately we didn’t have time to discuss their recommendations in detail. So I decided to document my thoughts in this blog.

          • Note: While documenting my thoughts, I watched Thiru Swamy’s presenation at SIT-NYC. Details here. Even though the presentation discusses DB2 tuning, the process he followed applies to Oracle as well. Does anyone disagree?
          • Thiru.PNG

They reviewed top 4 SQL statements and made 4 recommendations – one new index per table for 4 tables.

I analyzed their first recommendation: Build an additional index on a Z table. Let us call it Z_table.

Some facts:

The statistics on Z_Table is given below:

System Name
DB has been up since
Executions Elapsed Time in Micro Seconds
Elapsed time in Hours
Time spent per day
QA 02/29/2012 – 100+ days 1,540 846,869,461 15 minutes approx ~8 seconds per day
Production 03/18/2012 – 85+ days 30,278 14,381,103,998 3.9 hours ~169 seconds per day

I can’t explain why they even considered optimizing the query accessing Z_Table. Even if we completely eliminate running this query, we would save 169 seconds per day in the production system. Is that worth the efforts?

The consultants recommended we create an index on Z_Table. The index to include 3 columns: Column_A(MANDT), Column_B(VARCHAR2(60) and Column_C(VARCHAR2(3)).

I checked the cardinality of columns to be indexed.

1) Column A  –> One value(MANDT=500)

2) Column B  –>  6 values.

Value
# of records
1 312,223
2 258,168
3 166,632
4 3,350
5 186,752
6 60,968

3) Column C –> 2 values

Value
# of records
1 956,588
2 31,505

The data distribution for all 3 columns combined: 11 values (1 times 6 times 2 = 12; however no records for one possible combination 1+6+2).

Value
# of records
1+1+1 3,294
1+1+2 56
1+2+1 60,670
1+2+2 298
1+3+1 312,223
1+3+2 1
1+4+1 157,470
1+4+2 29,282
1+5+1 164,764
1+5+2 1,868
1+6+1 258,168

If you’re familiar with DB05 output, see below: (The table – 47MB size – is not buffered. What would be the impact of changing the buffer mode to “Buffered”?)

DB05_1.png

DB05_2.png

DB05_3.png

From Thiru’s presentation, the complexity of DB tuning is shown below.

From_NYC.PNG

Based on the information I provided,

  1. Does anyone believe saving 169 seconds per day is worth the tuning efforts?
  2. What do you think about turning on SAP’s “Buffering”? The answer is probably “It depends”.
  3. They didn’t evaluate the index’s impact on DML statements.
  4. Have you ever tuned Oracle DB based on ST04 cache output alone or use other TOCDEs as Thiru showed inhis presentation? SAP’s buffering(TCODE SE13) is another option we could consider to improve the performance.

        Thiru4.PNG.

8 Comments
You must be Logged on to comment or reply to a post.
  • Hi,

    Nice, and as most of the time, there are a lot of missing information to answer your queries.

    First one, you mentioned if it is worth to tune a query that last 169 SECONDS per day. How much is the DB time per day? How much this query represents? What is the TUNING OBJECTIVE? this last question is one of the most important.

    Based only on the information you provide => TOP 4 statemetn in elapsed time, then it is worth to tune if you want to reduce the DB time.

    Regarding buffering. Again, you do not mention if the table is modified a lot or not and this is more important for the buffering that the size of the table.

    DML impact, unless you have a lot of indexes and you perform masive DML, the impact usually can be dismissed. And you have to ponderate it agains the TUNING OBJECTIVE (unknown)

    Have you ever tuned Oracle DB based on ST04 cache output alone

    I like this one. Yes, I have done it, in the dark ages of Oracle tuning. And I think that is the current status of other DBs, for what I have heard.

    I do not use many other transactions but SQL EDITOR (or sqlplus)

    AWR is your friend. And clear objectives.

    I do not use DB05 (it doe not provide real useful information)

    SM51, what for? I look at this to check environment variables, and to know how many AS are. But I do not know what useful information you could find on it for Oracle tuning. Let me know.

    Similar SM50 and SM66. The only useful information is when NOW something slow is running, it allows me to know the client PID and then I can take a look at the active sessions to find out the ORACLE session that is asociated and then I can start the analysis, nothing else.

    Depending on the OBJECTIVE, I may need to do traces (transaction XYZ is slow, do ST12 trace, never ST05 alone)

    ST03, to get the "general" picture of how is the time distribution.

    SM37, again, what for? only when the objective is to tune a job, I can find it there.

    something that may be very useful and you do not mention (and I do not use much either) is STAD

    • Hi Fidel,

      I believe you hit the nail on the head by saying there are a lot of missing information. That's my concern as well.

      I'll answer your questions in a random order:

      1) What is the TUNING OBJECTIVE?

           None. The objective was to compare the source system with the target system with no changes. So the focus was on migrating the DB from the source to the target and then compare the performance using the test cases we documented without making any changes.

      2) How much is the DB time per day?

           Don't know (missing information). I'll run tomorrow and let you know. I would like to offer one clarification: 169 Seconds I mentioned is over a period of 24 hours; the query gets executed several times over a period of 24 hours. Each time it takes approximately half a second (Elapsed time).

      3) Buffering: I checked DB20 and didn't notice too many changes since May 31st. I don't know for sure if this is a good assumption to make based on limited data.

      4) DML impact: Agreed. Not a big deal. However what happens in real world is that this - coming from s/w vendors' consultants - gets misinterpreted by the developers and they prefer building indexes for almost every performance issue.

      5) I use SM51 to jump from one app server to another for at least three reasons: A) To perform Runtime Analysis (SE30, missing in the list) or Debug B) ST05 or ST01 and C) Read the developers' trace files. They need to be run locally. Sometimes - BW jobs on ECC - we may need to activate ST05 on all servers when we're not sure where it will run. SM51 by itself is not useful for tuning but acts as a gateway to tuning.

      6) SM66 might sometimes provide details on whether the issue is on a specific server or system wide in addition to PID you mentioned. If more than one job is running on an application server, then we may need to jump to app server to check PID.

      7) STAD - Good point. I've used it when we need to troubleshoot an issue posthumously.

      8) SM50 ---> A) I use this tcode to review the developer trace file instead of going to the app server - the formatting, in addition to convenience, is a little bit more user friendly than Unix VI. In addition, to activate/increase trace level.

                          B) I use this tcode to run SE30, debugging, ST05, ST01.

      9) SM37 ---> If BGD job is running slow, then reviewing the job log may be useful sometimes.

      10) SE38/SE37: Program logic is another thing should be reviewed as well.

      Finally what are your thoughts on cardinality? IMO, Z_Table has very low cardinality - 11 distinct values.

      Thanks for your response.

      • I'm going to short the answer as is the 2nd time that it get "lost" in this interface.

        => Use ST12 instead of SE30 or ST05

        it gives you both in one, you should review ABAP and SQL together most of the cases (it includes your comment about "abap logic"

        => you mentioned "then reviewing the job log may be useful sometimes"

        Do you realize this is true for ANY transaction?

        Finally what are your thoughts on cardinality? IMO, Z_Table has very low cardinality - 11 distinct values.

        That would be the index cardinality, not the table.

        You are asking without providíng "key" figures of the statement (again)

        => The statement takes 1/2 second and returns no rows => perfect selectivity index is great

        => The statement tales 1/2 second and return 99% of the table, do not bother create the index.

        That is your job as "tuner" to find out. What are the typical bind variables? what scenario? is it useful? ....

        And finaly, the first question. If you do not have a tuning objective, then do not tune.

        Comparing both systems is not an objective. Comparing and "improving" the differences may be an objective.

        But here you only mention that you took the top 4 SQL statements and this is one of them.

        Why you took 4? what is the reason behind? tune them? check them? do nothing with them?

        It is something you have to answer, you should NOT ask "us" if we would tune it.

        That depends (on the tuning objective)

        I've tuned faster statements. Also, I have said I would NOT even look at others much more slower because it was out of "scope"

        The objective will tell you if you do it or not. 1/2 second could be a lot (or not)

        NOTE: here I express my PERSONAL opinion.

        • Hi,

          I believe we agree on one point: The issue I discussed would require more time and efforts to analyze.

          The statement takes 1/2 second and returns no rows => perfect selectivity index is great.

          I agree to disagree. If the query returns no rows and runs several times, then I would ask why bother running the query. It is probably due to program bug.

          DB05 IMO is very important transaction. It provides valuable information on the proposed/existing index(columns) cardinality/skewness. The skewness sometimes might explain unpredictable response times of a query based on what where criteria was used.

          Thanks for your input once again,

          Bala

          • The statement takes 1/2 second and returns no rows => perfect selectivity index is great.

            I agree to disagree. If the query returns no rows and runs several times, then I would ask why bother running the query. It is probably due to program bug.

            1.- Yes, why bother running the query. I pass that to the application to analyze if it is needed (may be to check if something exists, may be it is not a bug, not for the database to decide) but I provide a sokutioin until they decide. solution can be implemented immediately. application discussion can take months. 😥

            DB05 IMO is very important transaction. It provides valuable information on the proposed/existing index(columns) cardinality/skewness. The skewness sometimes might explain unpredictable response times of a query based on what where criteria was used.

            2.- I do not agree with the utility of DB05 for this. It provides you information about the "different" values for the posisble index. you can get that from the oracle data dictionary.

            Regarding how "skew" is, yes it may tell you that most of the combination return 1 value and there are a few ones that return a lot of values. BUT, what combination is looking for the application? 😯 . Basically, you are running a "non parallelyzed" count(*) on a (may be) large table to et some incomplete information.

            I prefer to look at the

            AWR => what are the typical bind variables used

            ABAP => may be there are some constant there

            AWR > how may rows are typically returned

            Finally, I count myself for the values I know they are queried (if I'm lucky) and I can find that, for the typical values how many are the amount of rows returned.

            There you have your SKEWNES and CARDINALITY information that YOU are interested but DB05 do NOT provide.

            I still have to see one analyis were DB05 was used and provided "useful" information.

          • I provide a sokutioin until they decide. solution can be implemented immediately. application discussion can take months. 😥

            If users are having performance issues or the objective of tuning is to address issues immediately, then yes, I agree. Otherwise I would consider all options. A story: 2 months back, a job was accessing the same record thousands of times and ran several hours. The developer wanted me to build an index outside SAP. He mentioned that job was running fine before migration. I knew what happened immediately.

            Two or three years ago, he asked me to build an index outside SAP in the production system. Reason: He didn't have time to build in D and then promote through the landscape. I reviewed and found the job was accessing the same record several thousand times; I provided that info to the developer and suggested he review the program to see why it was accessing the same record several thousand times. His response: "Build the index, no time for discussions". I built it in Oracle level.  Two months back, SAPINST's export/import got rid of that index. So I built that index once again.

            Basically, you are running a "non parallelyzed" count(*) on a (may be) large table to et some incomplete information.

            Good point on parallelism for large tables. However I still like the output of DB05 for small to medium sized tables. This is just a matter of liking. The information provided by DB05 is simple and easy to understand - subjective.

            SKEWNES and CARDINALITY information that YOU are interested but DB05 do NOT provide

            Please review the screenshot I provided in the blog - just above a screenshot titled "Lot of KPIs".....

            DB05 does provide information on skewness and cardinality: This is what I gather from that screenshot:

            1) There are 11 distinct values.

            2) 1 value would return 1-10 records (3rd column 1-10 records)

            3) 1 value would return 11-100 records (4th column 11-100)

            and so on....

            Thanks,

            Bala

  • Hi,

    comments inline:

    1. Does anyone believe saving 169 seconds per day is worth the tuning efforts?

    Users tend to be impatient. If this helps to make people happy ... ok. But if this Statement is under your top 4 I think you have different problems. I guess since they checked it by looking into the EWA report, it is an easy "success" story to point out in the next EWA Report, that the statement has been optimized.

    1. What do you think about turning on SAP's "Buffering"? The answer is probably "It depends".

    It helps, if the table is small, not changed too frequently and access is based on PK. If all factors are matching, it is ususally a good tuner. Even if the statement itself is very speedy, it can help for the rest of the system to take off quite a couple of calls from the

    DB interface. The Table Call Statistic helps to evaluate this.

    (ST10? ST11? not sure right now, can be drilled down from ST04)

    1. They didn't evaluate the index's impact on DML statements.

    Well, if you you allready know, that the table in charge gets frequent DML (Table Call Stats know it), it would be worth to check, otherwise, I'll tend to try and see.

    1. Have you ever tuned Oracle DB based on ST04 cache output alone or use other TOCDEs as Thiru showed in his presentation?

    In addition to what Fidel already mentionend, SQLPLUS with "set autotrace on" which gives you a quick info about the gets per statement. This is my fastest indicator when

    creating indexes directly on the DB to check the get-relation.

    Table Call Statistics is important to evaluate SAP Buffering.

    Just out of curiosity: Was this the top addressed problem from the 4 statements?

    Volker

    • Hi,

      Thank you for sharing your thoughts.

      1. Does anyone believe saving 169 seconds per day is worth the tuning efforts?

      Users tend to be impatient. If this helps to make people happy ... ok. But if this Statement is under your top 4 I think you have different problems. I guess since they checked it by looking into the EWA report, it is an easy "success" story to point out in the next EWA Report, that the statement has been optimized.

      Four points regarding that statement:

      • That is run by a batch program. And that statement runs 300-400 times. Each run takes about 1/2 a second.
      • As Fidel pointed out, we should match our tuning efforts to objectives. However we didn't have tuning objectives.
      • No, EWA report didn't point that statement out.
      • Subsequently I reviewed the program, found the issue, emailed my thoughts to the developer;he agreed with my recommendation of fixing the code so it doesn't run 300-400 times selecting 0 records. That statement was a redundant one. This didn't take more than an hour.

      Just out of curiosity: Was this the top addressed problem from the 4 statements?

      Yes.

      Bala