Bobby Durrett's DBA Blog

Subscribe to Bobby Durrett's DBA Blog feed
Oracle database performance
Updated: 1 month 1 week ago

SQL Profile not used on slightly different query

Thu, 2019-01-31 15:09

Last week I was asked to help with a performance problem that looked a lot like a problem I fixed in July with a SQL Profile. The query whose plan I fixed back in July was modified by a minor application change over the weekend. A single column that was already in the select clause was added to another part of the select clause. As a result, the SQL_ID for the new query was different than the one for the July query. The SQL Profile from July associated SQL_ID 2w9nb7yvu91g0 with PLAN_HASH_VALUE 1178583502, but since the SQL_ID was now 43r1v8v6fc52q the SQL Profile was no longer used. At first, I thought I would have to redo the work I did in July to create a SQL Profile for the new query. Then I realized that the plan I used in July would work with the new SQL_ID so all I did was create a SQL Profile relating SQL_ID 43r1v8v6fc52q with PLAN_HASH_VALUE 1178583502 and the problem was solved. This is an 11.2.0.3 database running on the HP-UX Itanium platform. Here is a post from 2013 explaining how to create a SQL Profile: url. I thought it would be helpful to use this post to go over the steps that I went through with the July incident and how I originally generated the good plan. Then I wanted to make some comments about the various ways I come up with good plans for SQL Profiles by either generating a new better plan or by finding an older existing better one. Lastly, I wanted to talk about how a given good plan can be used for a variety of similar SQL statements.

The problem query that I worked on in July and many of the other SQL statements that I tune with SQL Profiles have bind variables in their where clauses. Usually the optimizer generates the plan for a query with bind variables once based on the values of the bind variables at that time. Then, unless the plan is flushed out of the shared pool, the query continues to run on the same plan even if it is horribly inefficient for other bind variable values. There is a feature that will cause the optimizer to run different plans based on the bind variable values in some cases but the SQL statements that I keep running into do not seem to use that feature. Since the query I worked on in July had bind variables I assumed that it was a typical case of a plan that worked well for one set of bind variables and that was terribly slow for another set. So, I had to find a set of bind variable values that made the query slow and figure out a better plan for those values. I used my bind2.sql script to extract the bind variable values for the problem query when I was working on the problem in July.

After extracting the bind variables, I used an AWR report to figure out which part of the plan contributed the most to the run time of the query so that I knew which bind variable value was causing the slowdown. Using an AWR report in this way only works if you do not have a bunch of slow SQL statements running at the same time. In this case the problem query 2w9nb7yvu91g0 was dominating the activity on the database with 62.19% of the total elapsed time. If there were a bunch of SQL Statements at the top of this list with similar percent of total values, it might be hard to use the AWR report to find information about this one query.

Since the activity for 2w9nb7yvu91g0 was 87.19% CPU I looked for the segments with the most logical reads. Logical reads are reads from memory, so they consume CPU and not disk I/O. In the graph below the segment for the S_ACCNT_POSTN table has 88.60% of the logical reads so most likely this segment caused the slowness of the query’s plan.

I looked at the plan for 2w9nb7yvu91g0 to see where the most heavily read table was used. This would probably be the source of the slow query performance. I found that it was doing a range scan of an index for the S_ACCNT_POSTN table that had the column POSITION_ID as its first column. This made me suspect that the plan was using the wrong index. If an index was used to retrieve many rows from the table that could take a long time. I did a count on all the rows in the table grouping by POSITION_ID and found that most rows had a specific value for that column. I replaced the actual POSITION_ID values with VALUE1, VALUE2, etc. below to hide the real values.

POSITION_ID            CNT
--------------- ----------
VALUE1             2075039
VALUE2               17671
VALUE3                8965
VALUE4                5830
VALUE5                5502
VALUE6                5070
VALUE7                4907
VALUE8                4903

Next, I verified that the query had an equal condition that related a bind variable to the POSITION_ID column of the problem table. This made me suspect that the plan in the shared pool was generated with a bind variable value for POSITION_ID other than VALUE1. So, that plan would work well for whatever value was used to create it. POSITION_ID would be equal to that value for a small percentage of the rows in the table. But, running the query in SQL*Plus with POSITION_ID=’VALUE1′ caused the optimizer to choose a plan that made sense given that this condition was true for most of the rows in the table. The PLAN_HASH_VALUE for the new plan was 1178583502.

I tested 1178583502 against a variety of possible bind variable values by using an outline hint in SQL*Plus scripts to force that plan no matter which values I tested against. I extracted the outline hint by running the query with POSITION_ID=’VALUE1′ and using this dbms_xplan call:

select * from table(dbms_xplan.display_cursor(null,null,'OUTLINE'));

Then I just added the outline hint to a copy of the same SQL*Plus script and tried various combinations of bind variable values as constants in the where clause just as I had tried VALUE1 for POSITION_ID. I used the values that I had extracted using bind2.sql. After verifying that the new plan worked with a variety of possible bind variable values, I used a SQL Profile to force 2w9nb7yvu91g0 to use 1178583502 and the problem was resolved.

I have just described how I created the original July SQL Profile by running a version of the problem query replacing the bind variables with constants that I knew would cause the original plan to run for a long time. The optimizer chose a better plan for this set of constants than the one locked into the shared pool for the original query. I used the PLAN_HASH_VALUE for this plan to create a SQL Profile for the July query. This is like an approach that I documented in two earlier blog posts. In 2014 I talked about using a hint to get a faster plan in memory so I could use it in a SQL Profile. In 2017 I suggested using an outline hint in the same way. In both of those cases I ran the problem query with hints and verified that it was faster with the hints. Then I used a SQL Profile to force the better PLAN_HASH_VALUE onto the problem query. So, in all these cases the key is to generate a better plan in any way possible so that it is in memory and then create a SQL Profile based on it. A lot of times we have queries that have run on a better plan in the past and we just apply a SQL Profile that forces the better plan that is already in the system. My December, 2018 post documents this type of situation. But the 2014 and 2017 blog posts that I mentioned above and the July 2018 example that I just described all are similar in that we had to come up with a new plan that the query had never used and then force it onto the SQL statement using a SQL Profile.

The incidents in January and July and the cases where I added hints all lead me to wonder how different one SQL statement can be from another and still share the same plan. The problem last week showed that two queries with slightly different select clauses could still use the same plan. The other cases show that you can add hints or run the statement with bind variables replaced with constants. In the January case I did not have to go back through the analysis that I did in July because I could quickly force the existing plan from the July query onto the January one. The January problem also shows the limits of SQL Profiles. The slightest change to a SQL statement causes a SQL Profile to be ignored, even though the plan would still work for the new SQL statement. But in the January case the ability to use the same plan for slightly different queries made it easy to create a new SQL Profile.

Bobby

Categories: DBA Blogs

Slides From January 2019 AZORA Meeting

Wed, 2019-01-30 10:20

Here are slides from the January 2019 AZORA Meeting with Charles Kim and Viscosity:

Oracle Database 12.2 New Features

Oracle Database 18c New Features

Thank you to Charles and Viscosity for making our meeting a big success, and thanks for sharing these slides.

Bobby

Categories: DBA Blogs

MIT’s Free Algorithms Track

Wed, 2019-01-23 17:58

MIT, the Massachusetts Institute of Technology, has published a free set of three undergraduate level Algorithms classes that I recommend to the readers of this blog. These are links to the three classes in the order that they should be taken.

Mathematics for Computer Science (Spring 2015)

Introduction to Algorithms (Fall 2011)

Design and Analysis of Algorithms (Spring 2015)

I took the 2010 version of the Mathematics for Computer Science class and I am almost done with the Introduction to Algorithms class. I am considering taking the third class next. I want to use this post to explain why I recommend this set of three classes.

All three of these classes have video lectures. These classes are part of a massive collection of free educational information that MIT publishes on its OCW or Open Courseware web site: ocw.mit.edu. I have focused on the Computer Science classes out of the many available. But only certain classes contain video. For example, the Database Systems class does not have video. It does have free lecture notes and other content that has real value, but the Algorithms track has three classes in a row with video and that makes them especially valuable.

In addition to the video content all three classes have valuable static content such as lecture notes. The Mathematics for Computer Science class has a free online textbook. It looks like the 2015 version has all kinds of interesting extra content that was not in the 2010 class that I took, and I thought the 2010 class was great. The last two classes have solutions to the problem sets, quizes, and exams which is great when you are studying this on your own.

The Introduction to Algorithms and Design and Analysis of Algorithms classes have a highly regarded standard Algorithms textbook for their readings. It is Introduction to Algorithms, 3rd Edition by by Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, Clifford Stein. This textbook is commonly referred to as CLRS and seems to be the standard textbook for people who are serious about Algorithms.

The price is right. All three of these classes are free. I did buy CLRS, or actually I got it as a present, so my wife’s parents bought it, but $65 is a worthwhile investment especially since it is the text for two of the three classes and a valuable reference afterwards. The first class is completely free since it has a free text book included. The three classes represent thousands of dollars’ worth of educational material for free.

These classes are not easy. I do not claim to have mastered this material. It is hard to really discipline yourself to study for a class by yourself without grades and deadlines and others to talk to about it. But I have learned a lot from the two classes that I have worked through. I am a little afraid to take on the third class which is the intermediate level Algorithms class. Yikes! But even if I struggle to fully grasp every assignment, I am learning something. Some of it is a reminder of things I learned long ago in college, but many things are new. So, I do not mind the classes being hard. Better hard than too easy.

So, if you want to learn about Algorithms this three-class sequence is a great way to go. But, why would readers of this blog want to study this subject? In my opinion, Algorithms is just one area of Computer Science that would be helpful for a database professional to learn. Obviously, it is great to learn about database theory if you work with databases. It is great to learn about other systems topics such as operating systems and compilers since your work uses this type of software. It is great to learn about hardware, software development, etc. I think that many aspects of my Computer Science background from long ago in school have helped me in my database job. So, I do not think that Algorithms is the most important or only thing within CS that a database person needs to know, but I do think that Algorithms is very helpful to database workers.

I think that all of the main topics within CS that a database person would want are covered on the list of OCW EE and CS classes. But it seems that Algorithms is unique in that it has a very nice three class sequence that is so fully developed with video and the other resources that I mentioned. Maybe there are other areas that I missed, but I know that Algorithms is well covered by OCW. So, that is why I am singling out this three-class sequence in this blog post. I believe that it is an especially well covered topic within Computer Science that is useful to database professionals and available for free. So, check it out!

Bobby

Categories: DBA Blogs

January 25th AZORA Meetup with Viscosity

Wed, 2019-01-09 17:36

If you are in the Phoenix, Arizona area on Friday, January 25th please come to the AZORA meetup with talks by Viscosity. Please RSVP on the meetup site so we know how much food to order: Meetup link. We have a great lineup of speakers with talks about new features of the latest Oracle database versions. I know that I have not kept up with all the latest features, especially with all the other technical training that is on my plate. So, I am looking forward to setting aside this dedicated time to learn about Oracle database features from highly qualified presenters.

Plus, it will be fun and includes lunch! Come join us for our first meeting of the new year.

Bobby

Categories: DBA Blogs

Long running queries and my DBA_HIST_SQLSTAT scripts

Mon, 2018-12-17 14:24

On December 2nd we ran into a problem with one of my scripts that uses the DBA_HIST_SQLSTAT view. It did not show the full run time of a SQL statement that ran for several hours. So, it hid the fact that the plan that the SQL statement was using was much less efficient than another better plan. This post will document the two DBA_HIST_SQLSTAT scripts that I have been using so far and will introduce a new script that should make it clearer that a SQL statement is running for several hours on a plan.

There are two scripts that are based on DBA_HIST_SQLSTAT that I use most often for tuning: sqlstat.sql, sqlstat2.sql. I use these scripts all the time to look at the performance of queries and the different execution plans that they use. But in this case the output of sqlstat.sql was misleading. Here is part of its output for the problem SQL statement am47wcwn336yj:

PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms
--------------- ------------------------- ---------------- ------------------
     3771021445 04-NOV-18 05.00.15.814 AM                1            2250696
     2117190322 11-NOV-18 03.01.00.265 AM                6         223502.352
     1484468961 18-NOV-18 06.00.22.364 AM                1         2695964.96
     2117190322 25-NOV-18 02.00.34.302 AM                3         198192.968
     1139203276 02-DEC-18 07.00.21.784 AM                1          322110.53

We got a performance ticket at about 9 am on December 2nd about a session with SQL ID am47wcwn336yj running for longer than normal. Based on the sqlstat.sql output above you would think that the current plan is 1139203276 and that it is a little slower than the other plans but not terrible. The problem is that sqlstat.sql only shows you SQL executions that have completed during the hour prior to the END_INTERVAL_TIME. So, one execution of am47wcwn336yj completed between 6 and 7 am on December 2nd, but the output makes it look like it only ran for 322110.53 milliseconds which is about 5 minutes. Really it probably ran for several hours and completed around 6:05 am. So, we thought that 1139203276 was an ok plan when really 2117190322 is the much more efficient plan. But, the sqlstat.sql output confused us because it did not show the hours where am47wcwn336yj was running with plan 1139203276 but had not completed. It is easy to see why sqlstat.sql leaves out these hours if you look at this condition in its where clause:

and executions_delta > 0

This part of the where clause conditions prevents you from seeing where the SQL statement is still running. But the select clause contains a division by executions_delta so you cannot have divide by zero so that is why I excluded it.

When I am working on a long running SQL statement that spans two or more hours, I run sqlstat2.sql after I run sqlstat.sql. Sqlstat2.sql shows all the time spent per hour regardless of whether a SQL statement’s execution completed. It does not have the condition requiring executions_delta to be > 0 because it does not divide by that column. Here is the output for am47wcwn336yj:

PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA ELAPSED_TIME_DELTA
--------------- ------------------------- ---------------- ------------------
     3771021445 04-NOV-18 03.00.43.465 AM                0         3584030311
     3771021445 04-NOV-18 04.00.27.516 AM                0         3588264762
     3771021445 04-NOV-18 05.00.15.814 AM                1         2250695998
     2117190322 11-NOV-18 03.01.00.265 AM                6         1341014109
     1484468961 18-NOV-18 02.00.07.772 AM                0         3171408998
     1484468961 18-NOV-18 03.00.58.545 AM                0         3648125526
     1484468961 18-NOV-18 04.00.44.680 AM                0         3586687594
     1484468961 18-NOV-18 05.00.32.832 AM                0         3592026797
     1484468961 18-NOV-18 06.00.22.364 AM                1         2695964960
     2117190322 25-NOV-18 02.00.34.302 AM                3          594578904
     1139203276 02-DEC-18 02.00.14.082 AM                0          480039949
     1139203276 02-DEC-18 03.00.05.255 AM                0         3591464884
     1139203276 02-DEC-18 04.00.50.936 AM                0         3645014119
     1139203276 02-DEC-18 05.00.40.304 AM                0         3591817904
     1139203276 02-DEC-18 06.00.31.820 AM                0         3587861998
     1139203276 02-DEC-18 07.00.21.784 AM                1          322110530
     1139203276 02-DEC-18 09.00.58.127 AM                0         2028205794
     1139203276 02-DEC-18 10.00.37.793 AM                0         3582326580

Here you can see that plan 1139203276 ran between 1 and 7 am on 02-DEC-18 and completed and then ran between 8 and 10 am and never completed. We ended up killing the long running session and we reran the SQL another way so there is no more history of it. The problem with sqlstat2.sql is that it does not show average elapsed time per execution like sqlstat.sql does. It just shows the total elapsed time for all executions, if any. This is because we don’t divide by executions_delta. Also, the units are microseconds instead of milliseconds. I find milliseconds to be easier to use.

To me looking at the output from sqlstat.sql and the output from sqlstat2.sql 2117190322 is the best plan. It is clear to me because I wrote these two scripts and am used to the way they look in this situation. If you go back to the first output, you can see that plan 2117190322 averages less than 224 seconds per execution (223502.352 ms and 198192.968 ms). Plans 3771021445, 1484468961, and 1139203276 ran for multiple hours. You can tell this because there are multiple consecutive hours with those plans and 0 executions.

I have used sqlstat.sql and sqlstat2.sql a lot as they are but today, I got the idea of combining them into a script that shows average elapsed time per execution for a given hour and includes hours where an execution did not complete. I have not used it yet in a real performance tuning situation but here it is, cleverly named sqlstat3.sql. Here is its output for this situation:

PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms
--------------- ------------------------- ---------------- ------------------
     3771021445 04-NOV-18 03.00.43.465 AM                0         3584030.31
     3771021445 04-NOV-18 04.00.27.516 AM                0         3588264.76
     3771021445 04-NOV-18 05.00.15.814 AM                1            2250696
     2117190322 11-NOV-18 03.01.00.265 AM                6         223502.352
     1484468961 18-NOV-18 02.00.07.772 AM                0            3171409
     1484468961 18-NOV-18 03.00.58.545 AM                0         3648125.53
     1484468961 18-NOV-18 04.00.44.680 AM                0         3586687.59
     1484468961 18-NOV-18 05.00.32.832 AM                0          3592026.8
     1484468961 18-NOV-18 06.00.22.364 AM                1         2695964.96
     2117190322 25-NOV-18 02.00.34.302 AM                3         198192.968
     1139203276 02-DEC-18 02.00.14.082 AM                0         480039.949
     1139203276 02-DEC-18 03.00.05.255 AM                0         3591464.88
     1139203276 02-DEC-18 04.00.50.936 AM                0         3645014.12
     1139203276 02-DEC-18 05.00.40.304 AM                0          3591817.9
     1139203276 02-DEC-18 06.00.31.820 AM                0            3587862
     1139203276 02-DEC-18 07.00.21.784 AM                1          322110.53
     1139203276 02-DEC-18 09.00.58.127 AM                0         2028205.79
     1139203276 02-DEC-18 10.00.37.793 AM                0         3582326.58

The nice thing is that you can see that 2117190322 ran 6 times averaging 223502.352 milliseconds on November 11 and 3 times averaging 198192.968 milliseconds on November 25th. Plus, you can see that the other plans span multiple hours. Note that the plans that span a full hour run for about 3600000 milliseconds which is an hour so that supports the idea that the SQL statement is active throughout the entire hour. I have only shown some of the total output to fit on the screen, but the full output includes more information such as a CPU and IO breakdown of the run time.

Anyway, the point is that the main DBA_HIST_SQLSTAT query that I use, sqlstat.sql does not show SQL statements that run for several hours very clearly. You can figure it out using sqlstat2.sql in addition to sqlstat.sql. The new sqlstat3.sql script may make it easier to diagnose multi-hour SQL statement executions in the future without having to use two scripts.

Bobby

Categories: DBA Blogs

To Blog, or Not to Blog?

Wed, 2018-12-12 17:42

As 2018 is ending I am thinking about what I spend my time on. Do I have my priorities correct? I know that I should spend some part of my time interacting with people over the Internet. I have gotten so much benefit from talking with other people about Oracle database issues through web-based forums. I have also had some in person interactions. I’m excited about the sort of rebirth of AZORA, my local Arizona Oracle user group. I talk to human beings in person about Oracle at user group meetings and that has great value. But I noticed that it had been a while since my last blog post of any substance. So, I asked myself if I am losing interest in blogging. I have not lost interest in writing blog posts, but I have had many other things tugging for my time, including other ways to interact with people over the Internet. So, I need to think about how much time to allocate to this blog next year.

I blame part of my lack of blog verbosity on Python. I have written a number of Python related posts, but Python has also drawn me into interacting with Rosetta Code and Stack Overflow. I’m trying to keep up my Python skills so that I have a general-purpose programming language available in my tool kit. But any time I might have devoted to my blog may have gotten used up by these Python related sites. Even GitHub is a form of distraction because maintaining Python repositories there takes time. But I don’t regret any time spent on Python because it is such a popular language now and it has really helped me in my work.

I guess the second time sink has been my PeopleSoft work. I don’t really talk much about my PeopleSoft work on this blog, but I have done a lot more of this sort of work in 2018 than I ever would have expected. With the push to move to the cloud and move to non-Oracle databases I have nevertheless been doing a bunch of old fashioned on premises ERP support, PeopleSoft applications on Oracle databases. I’ve been doing PeopleSoft on Oracle database for at least 24 years now so if my employer needs this sort of work, I’m capable of doing it. But PeopleSoft doesn’t excite me as much as database internals and performance tuning so that’s why I don’t blog about it much.

Speaking of the cloud and non-Oracle database, I have done some work in these areas in 2018 but not as much as I would have liked. I probably wouldn’t blog about the basics of using AWS or MySQL RDS, but if I can dig into some MySQL internals and use or build some MySQL performance tools, I can see blogging about that. My experience is mostly with Oracle, but I think open source is neat. I like having the MySQL and PostgreSQL source code even if I am unfamiliar with it. So, I guess I haven’t blogged about the cloud and non-Oracle databases because I just haven’t gotten very far. Maybe next year.

But, why have I not blogged about Oracle performance tuning? That really is the question. There have been several performance issues that I have worked on over the past few months, but in many cases, they were not anything new to me. Still, I think I may have missed out by not documenting the steps I went through in some of these real issues. I think it is easy to feel like what I am writing is not anything new and that there are better Oracle performance tuning bloggers out there. Also, I worry that I will make a mistake and confuse people or mislead them with wrong information. I would not intentionally mislead anyone, but I can certainly be wrong! I think going forward in 2019 I want to make the effort to write blog posts about Oracle performance issues that I have resolved even if they are not especially new territory. I probably can find some interesting angle from most issues. I think it might help people to see how I am using my SQL*Plus and Python scripts in different scenarios. Anyway, I hope to get back to blogging about Oracle performance.

Topics of my 15 blog posts in 2018 to this point: User Group6Oracle4Python3MySQL2 My Shakespeare inspiration (To be, or not to be):


To be, or not to be, that is the question:
Whether ’tis nobler in the mind to suffer
The slings and arrows of outrageous fortune,
Or to take Arms against a Sea of troubles…

Hamlet. Act III, Scene I

Categories: DBA Blogs

Parallel_degree_policy AUTO caused serial plan in qat

Thu, 2018-11-29 13:41

Recently during testing a merge statement was running forever in our qat environment after a simple change. We had simply added a few columns to an existing table. After looking around we found that our qat environment had the parameter parallel_degree_policy set to AUTO, but our other environments had it set to MANUAL. I thought I would show how we figured this out.

I had the SQL_ID for the problem merge statement and used my sqlstat.sql script to see if the same merge statement ran in production. I did not find any production executions. I was told that this merge statement was already in our production code and ran all the time without an issue. So, then I used my findsql.sql script to find the SQL_ID of the production version of the merge statement. 

To use findsql.sql you have to edit it to replace MYUSER with a real user on your database. I use my own personal user. Then you have to update the LIKE statement in the where clause to identify the query that you want. I replaced this line in the findsql.sql text on GitHub:

SQL_TEXT like '%a.FISC_WK_OF_Yr < to_number(to_char(sysdate+1, ''iW''))%' and

with this line which is part of the text of the problem merge statement:

upper(SQL_TEXT) like upper('%MERGE INTO DDMADM.FWB_MDL MDL%') and

I easily found the equivalent merge statement in production. The QAT merge statement had SQL_ID 00cmatqz5wymd and the prod one had 7pgqd82bgdah2. My coworker, who had brought me in on this issue, said that on QAT the query was not running in parallel. So, I used my getplans.sql script to look at the two plans. On prod the plan used parallelism:

-----------------------------------------
| Id  | Operation                        
-----------------------------------------
|   0 | MERGE STATEMENT                  
|   1 |  MERGE                           
|   2 |   TEMP TABLE TRANSFORMATION      
|   3 |    PX COORDINATOR                
|   4 |     PX SEND QC (RANDOM)          
|   5 |      LOAD AS SELECT              
|   6 |       UNION-ALL                  
|   7 |        WINDOW SORT               
|   8 |         PX RECEIVE               
|   9 |          PX SEND HASH            
|  10 |           VIEW                   
|  11 |            UNPIVOT               
|  12 |             PX BLOCK ITERATOR    
|  13 |              TABLE ACCESS FULL   

On QAT it did not:

-----------------------------------------------------
| Id  | Operation                                    
-----------------------------------------------------
|   0 | MERGE STATEMENT                              
|   1 |  MERGE                                       
|   2 |   VIEW                                       
|   3 |    NESTED LOOPS OUTER                        
|   4 |     VIEW                                     
|   5 |      TEMP TABLE TRANSFORMATION               
|   6 |       LOAD AS SELECT                         
|   7 |        UNION-ALL                             
|   8 |         WINDOW SORT                          
|   9 |          VIEW                                
|  10 |           UNPIVOT                            
|  11 |            PARTITION RANGE SINGLE            
|  12 |             TABLE ACCESS BY LOCAL INDEX ROWID
|  13 |              INDEX RANGE SCAN                

I found this extra line at the end of the QAT plan:

- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

This triggered a memory of a coworker of mine who was trying out some degree of parallelism parameters a couple of years ago. After searching the internet, I quickly found that the automatic DOP message was related to the parameter parallel_degree_policy. We found that it was set to manual on prod, so we set it to manual on QAT and the merge statement went back to the expected performance.

I guess the point of this post is to keep a close watch on the messages that show up at the end of your execution plan. They could lead you to a parameter setting that is the source of your problem.

Lastly, this is my first post using the new WordPress Gutenberg plugin. What do you think? It was pretty easy to put in the output from my scripts in little unformatted boxes by themselves. Much easier than with the previous WordPress editor.

Bobby

P.S. I have found one negative to Gutenberg. I can’t find a way to use the Jetpack Proofreading function to check my grammar. So, if you see that I have used passive voice in a bunch of my sentences you will know why. Hopefully they will add this into a future update. Of course, I could just use my brain to check the grammar but where is the fun in that?

P.P.S. I started working on another blog post and found a simple solution to my grammar checking needs using Gutenberg. I just copy and paste the blog post text into Microsoft Word and use its grammar checker. Pretty neat.

Categories: DBA Blogs

Hear my Python for the Oracle DBA talk in Scottsdale on November 16th

Mon, 2018-10-22 16:51

I am going to giving a talk about why Python is a good programming language for Oracle DBAs in Scottsdale on November 16th for the Arizona Oracle User Group, AZORA. We may get some other speakers together for the meeting but I will be there for sure. I did this talk for Collaborate and for the RMOUG Training Days earlier this year. I may try to make it a little more interactive if I can get it together in time. I’m thinking of encouraging people to bring their laptops and install Python 3.7.1 so they can play around with a couple of features during the talk. Here is the Meetup information:

#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }
“Python for the Oracle DBA” with Bobby Durrett and other related topics….

Friday, Nov 16, 2018, 12:00 PM

Republic Services – 2nd Floor Conference Rooms
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

23 AZORAS Attending

Come and learn first hand from Bobby Durrett about how an Oracle DBA has been using the Python programming language in his work and why Python might be a good language for other DBAs to use. There are hundreds of programming and scripting languages. Why Python? Also, database administrators are not full time programmers. Should they learn a program…

Check out this Meetup →

Let me know if you have any questions about this meeting. It should be fun.

Our meeting sponsor Clarivoyant graciously agreed to provide us with pizza and drinks so don’t worry about lunch!

Search my blog for Python posts if you want to see examples of how I have been using Python for my Oracle DBA work:

https://www.bobbydurrettdba.com/?s=Python

Bobby

Categories: DBA Blogs

Copy Oracle performance reports to SharePoint using Python

Fri, 2018-07-27 18:26

I have a couple of Oracle performance reports that I upload from my work Windows 7 laptop to our corporate SharePoint site on Office 365 on a regular basis. I thought that it would save me time to automate the uploading of these reports using Python. This post describes the continuing saga of my attempts to build a working Python script just to upload a file to SharePoint. I have a script that works for me today but there are no guarantees it will work in the future. Still, it might be helpful to other Oracle DBAs and others so I thought it was worth documenting.

The first Python script that I wrote that really helped me in my job was the ashcpu.py script within PythonDBAGraphs. I run this report just about every day at work to monitor the CPU usage on an important database. After I review the report which is an image of a graph I manually copy the report to SharePoint in a certain folder. It doesn’t take long to find the bookmark for the SharePoint folder and to copy the files over. But, what if I could just click on an icon and the files would be copied to SharePoint automatically?

Back in 2015 I wanted to start saving some historical Oracle performance data that went back further than the 6 weeks of AWR history that we keep on our Oracle databases. Ideally we would setup some sort of AWR data warehouse that we could mine for useful information based on data from the past. But setting up an AWR data warehouse would require money and approvals and time. Instead I built a simple script to get monthly AWR reports for all of our production databases. If the database had been up for the entire month it would get a report for that month, otherwise it would report on a subset of the month. I forget how I chose the subset. I think I picked the longest number of days. Anyway, the script tars and gzips the html AWR files for all of the databases into a single .tar.gz file. So, each month I use ftp to copy the .tar.gz file down to my laptop. Then I extract all of the html files and zip them up. Then I copy the zip to Sharepoint. So, in this case I wanted a Python script that would do the ftp, ungzip and untar, zip, and copy the zip to Sharepoint. It is only once a month but it would be nice to just click the button and have it done.

The script to upload the images is pretty simple. All it does is look for .png image files in the right directory and with the right pattern and upload them and then delete them from the source directory.

.gist table { margin-bottom: 0; }

The script that uploads the zip files with AWR reports is a little more complicated because it does the ftp, tar, gunzip, zip, and copies to SharePoint.

But things like untaring and zipping the files are easy in Python:

.gist table { margin-bottom: 0; }

The hard part was getting the file to SharePoint.

I had a fully working script that I was using to upload both of these types of reports, images and awr zips, to SharePoint. It probably worked for a week at most. Here is the code on GitHub that no longer works: url

I tried to find out why this script stopped working by asking on Stack Overflow and opening an issue on the GitHub site for the package that my script depended on. But I didn’t get an immediate answer so I moved on to create the copyfiletosharepoint script. It wasn’t easy getting copyfiletosharepoint to work. It is based on Microsoft Graph which seems to be Microsoft’s preferred way to interact with SharePoint on Office 365 through Python. Anyway, as of today 7/27/2018 it works for the two reports that I want to upload. Hopefully it will keep working for longer than my first script!

It has been quite the saga and has taken much longer than I expected. But hopefully the scripts that I have built will make me more productive by automating some of the mindless tasks that I do on a regular basis.

Bobby

Categories: DBA Blogs

MySQL client slow to display the first row of a large result set

Fri, 2018-06-01 16:28

A coworker of mine asked me to look at a MySQL query that was running longer than we want it too. If they added a LIMIT 1000 clause at the end of the query it popped up the results in a couple of minutes but without the LIMIT clause it apparently hung forever without returning a row of the result set. I figured out that the query was returning 4 million rows with a bunch of columns in each row which is why it was slow. But the question remained why did it pop up the first row faster with LIMIT clause? I checked the plan with and without the LIMIT clause and didn’t see a difference. Then I did more testing and realized that even a simple query that returns a large result set took a long time to display the first row. This is on MySQL 5.7 running in AWS RDS.

I asked a question about this on two forums that answer MySQL questions:

https://dba.stackexchange.com/questions/208161/mysql-select-from-table-without-conditions-does-not-return-rows-right-away

https://forums.mysql.com/read.php?24,666643

I appreciate people responding to my questions but I didn’t really get the answer I was looking for. I really wanted to know why large result sets made the mysql client take a long time to display the first row. But, I guess people thought that you should just add a LIMIT clause and not worry about it. Why would you want to return the first rows of a large result set quickly? I just want to understand it and I still don’t.

I built a test case and ran it against an Oracle database and a MySQL database and the Oracle database returned the first row immediately and the MySQL version hung for a couple of minutes. I used the Oracle sqlplus utility and MySQL mysql command line client.

Here is the test case:

.gist table { margin-bottom: 0; }

I ran this from my laptop at home over a VPN so they both had to send results over the internet. But the MySQL database was in AWS so it had to go over the internet twice to get to me. The Oracle database was in our data center. The select * from test statement pops up the first rows instantly on Oracle but took at least a minute to show up on MySQL. A simple Python script against the same MySQL table pops up rows instantly.

.gist table { margin-bottom: 0; }

So, this leaves me wondering if the slowness in displaying the first row is due to some processing that occurs in the mysql client. I assume, but don’t know, that running the query through Python is no different from through the mysql client from the server’s perspective.

I guess the point is that there is a lot that I don’t know about MySQL’s architecture because most of my experience is with Oracle. But it was helpful to me to know that the mysql client is slow to produce the first row of a large result set and to know that a Python script could be used as a workaround to this slowness.

Bobby

Categories: DBA Blogs

Python script to backup remote directory using sftp

Wed, 2018-05-30 12:38

This is the sftp version of the ftp script I published in an earlier post. It logs into a remote host using sftp and backs up a remote directory recursively backing up all subdirectories.

.gist table { margin-bottom: 0; }

This one uses Paramiko instead of ftplib so you can do encrypted file copies.

Bobby

Categories: DBA Blogs

Database links and SCNs on June 23, 2019

Wed, 2018-05-16 18:33

A few days or weeks ago I saw a Twitter post about database links failing on Oracle databases next year. So, I have researched this issue. My company has a bunch of Oracle databases with a web of database links connecting them. After researching this for a while I have convinced myself that our company will not experience an issue. I want to share some of what I have found in this post.

First, I have read some helpful documents on Oracle’s support site. I recommend that you review these if you want to understand the issue:

Mandatory Patching Requirement for Database Versions 11.2.0.3 or Earlier, Using DB Links (Doc ID 2335265.1)

Recommended patches and actions for Oracle databases versions 12.1.0.1, 11.2.0.3 and earlier – before June 2019 (Doc ID 2361478.1)

Master Note: Overview for SCN issues (Doc ID 1503937.1)

System Change Number (SCN), Headroom, Security and Patch Information (Doc ID 1376995.1)

ORA-19706 and Related Alert Log Messages (Doc ID 1393360.1)

Bug 23130347 : HIGH SCN GROWTH RATE

Besides reading Oracle support documents I wanted to recreate a database link issue on a couple of test databases. My idea was to build a database that was not patched enough based on Oracle’s recommendations and one that was. Then connect them and set the date forward to after June 23, 2019 and see if I could create the issues that could happen at that time. I was not successful in recreating the issue. But I did prove out one interesting fact about database links and SCN numbers. When you use a database link the Oracle RDBMS synchronizes the SCN numbers of the two databases. Here is an example. I have 11.2.0.3.0 and 12.1.0.2 databases running on small Linux VMs on top of VirtualBox on my laptop.

Here are their SCN numbers after I create a database link between the two and before I use the database link:

NLSRTL Version 11.2.0.3.0 - Production

ORCL:SYSTEM>ORCL:SYSTEM>
     CURRENT_SCN
----------------
         1003971
         
NLSRTL Version 12.1.0.2.0 - Production

ORCL:SYSTEM>ORCL:SYSTEM>
     CURRENT_SCN
----------------
         1858602

Here are their SCN numbers after an update over a link:

NLSRTL Version 11.2.0.3.0

ORCL:SYSTEM>ORCL:SYSTEM>
     CURRENT_SCN
----------------
         1859090
 
NLSRTL Version 12.1.0.2.0

ORCL:SYSTEM>ORCL:SYSTEM>
     CURRENT_SCN
----------------
         1859091

I did the same thing with a select statement and got the same sort of synchronization of SCN numbers.

Knowing that database links cause SCNs to sync I started looking at our SCNs on all of our databases and found that there were two groups of databases with very similar SCNs. One group’s SCN was around 475,000,000,000 and the other group’s SCN was around 970,000,000,000.

But, based on the documents that I have read the max SCN for May 16th, 2018, today, is almost 16 trillion. We are under 1 trillion on our busiest system so we have a lot of headroom. The Oracle support documents say that there is some internal limit to how much your SCN can grow and Oracle bases the limit on the number of seconds since midnight January 1, 1988. You multiply the seconds by 16K SCN numbers per second to get the max SCN for today’s date. I found a lot of this math in scnhealthcheck.sql which Oracle’s Doc ID 1503937.1 includes.  I hacked it into different pieces that I found helpful but I didn’t think I should post them because it is a copyrighted script. Just download it from Oracle support and see if what I’m saying makes sense.

Anyway, after reading up on all this stuff I don’t think that the SCN issues with database links apply to us. We have far too much headroom. Maybe a company with higher transaction rate databases would be closer to the limit. We will find out on June 23, 2019 if I am right.

Bobby

Categories: DBA Blogs

Post Collaborate and on call week post

Wed, 2018-05-02 11:59

So, I was at the Collaborate 2018 Oracle user group conference last week and I am on call this week. I have not posted anything about the conference so I thought I would try to get something out there.

I did my Python and Toastmasters talks. I’m not sure how people received these. Both of these talks sort of loaded extra burden on the Oracle DBAs who attended. One talk said they should learn Python and the other said they should join Toastmasters. I think both had good information and valuable advice. But I got the feeling looking into people’s eyes that I was just adding a weight to their already heavy load. Maybe next time I do some talks I should focus on teaching something that can help lighten people’s load. They should take something away from the talk that they could use right away and not have to go do a bunch of extra work. I’m not sure.

The Toastmasters talk was poorly attended just as it was at RMOUG Training days. I was able to convince the people who run the conferences to let me do the talk but it didn’t attract attendees. Maybe the next time that I do a talk on soft skills I should give specific helpful advice instead of just talking about a program that people could join. A couple of IOUG leaders at my talk made similar suggestions. I guess with the two conferences behind me I’m reviewing what I did and how I can improve. Of course people were appreciative of my efforts since I’m just a volunteer speaker. But, I would like to get better.

I think next time I talk about Python I want to do a demo. They say a picture is worth a thousand words. I think the same is true about demos. I talked about why Python is a good language for Oracle DBAs to use with just a bunch of slides. Maybe I could have made similar points in the context of a live demo. I have some Toastmasters talks coming up. I may work in a demo or two to get some practice doing that sort of talk.

I enjoyed attending the sessions. When I wasn’t presenting I attended a variety of sessions. In the past I would focus on Oracle performance tuning talks but this year I looked for other topics that are current.

I did go to two Oracle new features talks. One funny thing happened. I think both speakers talked about Oracle’s new autonomous data warehouse and how it would get rid of a lot of normal DBA work. Then both speakers went on to detail all the complex new features that are in Oracle 12.2 and 18c. It made me laugh because in the same hour talk they contradicted themselves. First they said all this DBA work is going away and then they listed a pile of new features that DBAs need to learn. Funny. I don’t buy all the autonomous stuff for a second. It is all hype like a lot of things. Think about SQL optimization. People have tried for decades to write a SQL optimizer and they still can’t make one that works. I think there are some things that we just can’t do and SQL optimization is one of them. I have no reason to think that the machine learning and AI that will supposedly drive the autonomous data warehouse will work any better than the optimizer. Time will tell.

I went to three talks on Kafka which was a little redundant. I also went to a talk on MySQL, Mongo DB, Devops, and a couple of talks related to SQL on Hadoop. I went to a couple of talks by Amazon Web Services employees including one on Oracle on RDS. I went to a couple of talks on block chain. Lastly I went to a little different talk about graph theory or network science. All of this is to say I was looking at a lot of non-Oracle stuff instead of just going to Oracle database performance sessions.

The funny thing is that I’m on a project at work that is talking about putting in a SQL on Hadoop system. I was able to apply the Hadoop stuff that I learned at Collaborate in my first day back in the office. I’m not a Hadoop expert but the exposure was valuable.

The graph theory stuff intrigues me. I learned some graph theory in the edX and OCW classes that I have worked through. I can’t help wondering if there are business or IT problems that we could be modelling with graphs in my work. I’m working on this issue of database links between older and newer versions of Oracle. I guess you could look at Oracle databases as nodes and database links as edges. But, I’m not sure beyond that.

Anyway, going to a conference always gives me a lot to think about.  It was good to do my two talks and to hear what others had to say.

Bobby

 

 

 

 

Categories: DBA Blogs

Had to set parallel_max_servers to 2 for Delphix clone

Mon, 2018-04-09 17:50

This is a quick note about a problem I had creating a Delphix virtual database clone of a production database.

This is an older 11.1.0.7 HP-UX Itanium database. I tried to make a new virtual database copy of the production database and it failed due to the following error:

ORA-07445: exception encountered: core dump [krd_flush_influx_buffers()+96] [SIGSEGV] [ADDR:0x10000000005D8] [PC
:0x400000000B415880] [Address not mapped to object] []

I only found one thing on Oracle’s support site about krd_flush_influx_buffers but it was not an exact match because it had to do with Data Guard.

So I tried various parameters and none worked. I tried setting parallel_max_servers to 0  down from 100 in production but that caused other issues. Then I remembered something about setting it to 2 so I tried that and it worked.

The strange things is that I still see a ORA-07445 krd_flush_influx_buffers error in the alert log for the successful clone. But, somehow changing the parallel_max_servers parameter to 2 allowed the various Delphix and Oracle processes to complete.

Bobby

Categories: DBA Blogs

Had to set parallel_max_servers to 2 for Delphix clone

Mon, 2018-04-09 17:50

This is a quick note about a problem I had creating a Delphix virtual database clone of a production database.

This is an older 11.1.0.7 HP-UX Itanium database. I tried to make a new virtual database copy of the production database and it failed due to the following error:

ORA-07445: exception encountered: core dump [krd_flush_influx_buffers()+96] [SIGSEGV] [ADDR:0x10000000005D8] [PC
:0x400000000B415880] [Address not mapped to object] []

I only found one thing on Oracle’s support site about krd_flush_influx_buffers but it was not an exact match because it had to do with Data Guard.

So I tried various parameters and none worked. I tried setting parallel_max_servers to 0  down from 100 in production but that caused other issues. Then I remembered something about setting it to 2 so I tried that and it worked.

The strange things is that I still see a ORA-07445 krd_flush_influx_buffers error in the alert log for the successful clone. But, somehow changing the parallel_max_servers parameter to 2 allowed the various Delphix and Oracle processes to complete.

Bobby

Categories: DBA Blogs

Does an automated cloud data warehouse need partitioning and indexes?

Thu, 2018-03-08 16:33

This is a late post inspired by my time at RMOUG Training Days 2018. I attended two RMOUG Training Days presentations about automated cloud data warehouse database systems. The first was about Snowflake. The second was about Oracle’s coming Autonomous Data Warehouse Cloud offering. Both products seem to have the goal of automating as much as possible in a data warehouse database and neither seem to allow users to create indexes or to partition their data in the way they choose.

This raises the obvious question – do you need the ability to create indexes and define your own partitioning scheme in a data warehouse database? I have seen many situations on data warehouse databases where both are helpful. When we went to Exadata there was a push to drop all of our indexes but we did not. Also, we used partitioning to improve query performance. If we had to get rid of both features what would we have done on Exadata?

I don’t know the answer, but people say that we need more automation and less control. So, you could spin up a cloud based data warehouse database quickly and start using it. But how many knobs are left to turn when performance is slow? Do users need indexes and partitioning methods to get acceptable performance? Really, what is wrong with giving users the ability to create indexes and to partition tables in the way they want?

Time will tell whether index and partition free cloud based data warehouse database systems make it. But, for now I’m left to wonder what we are losing without these features.

Bobby

 

Categories: DBA Blogs

Does an automated cloud data warehouse need partitioning and indexes?

Thu, 2018-03-08 16:33

This is a late post inspired by my time at RMOUG Training Days 2018. I attended two RMOUG Training Days presentations about automated cloud data warehouse database systems. The first was about Snowflake. The second was about Oracle’s coming Autonomous Data Warehouse Cloud offering. Both products seem to have the goal of automating as much as possible in a data warehouse database and neither seem to allow users to create indexes or to partition their data in the way they choose.

This raises the obvious question – do you need the ability to create indexes and define your own partitioning scheme in a data warehouse database? I have seen many situations on data warehouse databases where both are helpful. When we went to Exadata there was a push to drop all of our indexes but we did not. Also, we used partitioning to improve query performance. If we had to get rid of both features what would we have done on Exadata?

I don’t know the answer, but people say that we need more automation and less control. So, you could spin up a cloud based data warehouse database quickly and start using it. But how many knobs are left to turn when performance is slow? Do users need indexes and partitioning methods to get acceptable performance? Really, what is wrong with giving users the ability to create indexes and to partition tables in the way they want?

Time will tell whether index and partition free cloud based data warehouse database systems make it. But, for now I’m left to wonder what we are losing without these features.

Bobby

 

Categories: DBA Blogs

Pushing predicates into MySQL subqueries

Tue, 2018-03-06 13:16

I am just getting started looking at MySQL performance tuning but I thought I would share something that I may have figured out. I’m working with a query that does a count and a group by in a subquery and it runs too long. It seems that the query reads every row from a table instead of passing a predicate into the subselect and accessing only a couple of rows. MySQL 5.7 seems to behave this way normally. I have built a simple test case to show that MySQL 5.7 works differently than Oracle 12.1 in this situation.

I build a table called TEST with about 1,000,000 rows (1,1) and one row (2,2) and put an index on the first column.

create table test(a int,b int);

insert into test values(1,1);

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

-- 1048576 rows

select count(*) from test;

create index testi on test (a);

insert into test values (2,2);

analyze table test;

Then I have a subselect with a SUM and a GROUP by and a where clause condition outside of the subselect that would cause the subselect to look at only one row – the one with values (2,2).

select sum_b from
(select a,sum(b) sum_b
from test
group by a) inner_query
where 
a=2;

This takes almost 2 seconds running in an Amazon RDS instance:

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (1.53 sec)

The corresponding Oracle query running in an on-premises Linux VM runs faster:

SQL> select sum_b from
  2  (select a,sum(b) sum_b
  3  from test
  4  group by a) inner_query
  5  where
  6  a=2;

     SUM_B
----------
         2

Elapsed: 00:00:00.08

I realize that the hardware is not the same but I think they are reasonably close. Best I can tell the Oracle version is faster because Oracle pushes the a=2 predicate into the subselect but MySQL doesn’t. The Oracle execution plan shows the a=2 predicate in the inner index scan:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |       |     1 |     6 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |     1 |     6 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TESTI |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=2)

I am still learning MySQL plans but it seems that MySQL does a full index scan:

As I said, I am new to MySQL so I may have missed some way around this. One simple solution would be to write your query without a subselect so that the predicate is in the group by query. This runs about as fast as the Oracle example above:

select sum(b) sum_b
from test
where 
a=2
group by a
--------------

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (0.10 sec)

This does a simple index lookup.

I have uploaded a zip of my Oracle and MySQL test here: zip

Maybe if you have used Oracle in the past and are now using MySQL 5.7 you should keep in mind that MySQL 5.7 does not push predicates into subqueries in the same situations that Oracle does and build your queries around this difference.

Bobby

 

 

Categories: DBA Blogs

Pushing predicates into MySQL subqueries

Tue, 2018-03-06 13:16

I am just getting started looking at MySQL performance tuning but I thought I would share something that I may have figured out. I’m working with a query that does a count and a group by in a subquery and it runs too long. It seems that the query reads every row from a table instead of passing a predicate into the subselect and accessing only a couple of rows. MySQL 5.7 seems to behave this way normally. I have built a simple test case to show that MySQL 5.7 works differently than Oracle 12.1 in this situation.

I build a table called TEST with about 1,000,000 rows (1,1) and one row (2,2) and put an index on the first column.

create table test(a int,b int);

insert into test values(1,1);

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

-- 1048576 rows

select count(*) from test;

create index testi on test (a);

insert into test values (2,2);

analyze table test;

Then I have a subselect with a SUM and a GROUP by and a where clause condition outside of the subselect that would cause the subselect to look at only one row – the one with values (2,2).

select sum_b from
(select a,sum(b) sum_b
from test
group by a) inner_query
where 
a=2;

This takes almost 2 seconds running in an Amazon RDS instance:

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (1.53 sec)

The corresponding Oracle query running in an on-premises Linux VM runs faster:

SQL> select sum_b from
  2  (select a,sum(b) sum_b
  3  from test
  4  group by a) inner_query
  5  where
  6  a=2;

     SUM_B
----------
         2

Elapsed: 00:00:00.08

I realize that the hardware is not the same but I think they are reasonably close. Best I can tell the Oracle version is faster because Oracle pushes the a=2 predicate into the subselect but MySQL doesn’t. The Oracle execution plan shows the a=2 predicate in the inner index scan:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |       |     1 |     6 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |     1 |     6 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TESTI |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=2)

I am still learning MySQL plans but it seems that MySQL does a full index scan:

As I said, I am new to MySQL so I may have missed some way around this. One simple solution would be to write your query without a subselect so that the predicate is in the group by query. This runs about as fast as the Oracle example above:

select sum(b) sum_b
from test
where 
a=2
group by a
--------------

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (0.10 sec)

This does a simple index lookup.

I have uploaded a zip of my Oracle and MySQL test here: zip

Maybe if you have used Oracle in the past and are now using MySQL 5.7 you should keep in mind that MySQL 5.7 does not push predicates into subqueries in the same situations that Oracle does and build your queries around this difference.

Bobby

 

 

Categories: DBA Blogs

Follow up from RMOUG Training Days talks

Fri, 2018-02-23 15:34

I have uploaded a zip of my two RMOUG Training Days talks here: zip

During the Toastmasters talk there was a question about finding a club in your area.

Here is how to find a Toastmasters club near you. Go to www.toastmasters.org and click on the FIND A CLUB button. Enter your address or zip and you get a map with club locations like this:

Click on the club to see details of when it meets and whether they are open to new members.

You can call the phone number and you can visit their website if they have one.

Toastmasters provides clubs with hosting for sites like this so it is easy to find out where they meet and to ask questions. In a public club like the one in this example you can just show up at a meeting and they will welcome you. A corporate club will often be just for employees so unless you work for the company that hosts the club you probably will have to look elsewhere. But there are a ton of clubs and most would love to have new members.

At the Python presentation people wanted to look at my PythonDBAGraphs source code. It is on GitHub here: PythonDBAGraphs. This is my largest example of Python code that an Oracle DBA might write. I think that the Oracle community may find PythonDBAGraphs more useful as an example than as a practical application. I use it every day for my work so it has value but it is more command line and less finished than a product that you would buy. Still, it shows how to use Python with Oracle databases and how to graph things using Python. And, it is a fairly simple yet still useful example of Python code.

The README has a lot of these details but I thought I would point people to the source files that might be most helpful to them as examples.

db.py has the Oracle database routines using the cx_Oracle module.

myplot.py has the graphing routines using the Matplotlib module.

util.py has some general functions that read or write from files, prompt for input from the command line, or process command line arguments.

The rest of the files are each one graph. I use these in the same way that I use the command line sqlplus scripts that I use for performance tuning. For each graph there is some code for stitching together a SQL query to get information from DBA_HIST or V$ views. Then each one calls routines in db.py, myplot.py, and util.py to do the rest. I tried to put as much logic as possible in db.py, myplot.py, and util.py so that it would be easy to quickly create a new graph.

onewait.py is an example of a single graph.

A lot of this information is in other blog posts but I thought I would put it here based on the questions that I got from doing the Python talk at RMOUG Training Days.

Anyway, I wanted to publish the PowerPoint files and give these two follow ups based on the questions that I got after each talk.

Bobby

Categories: DBA Blogs

Pages