Bobby Durrett's DBA Blog

Subscribe to Bobby Durrett's DBA Blog feed
Oracle database performance
Updated: 2 days 12 hours ago

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.

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

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

Early morning RMOUG post

Wed, 2018-02-21 06:44

Well, it is early Wednesday morning here at the Westin hotel in Denver where the RMOUG Training Days conference is being held. I can’t sleep anyway so I thought I would write-up some of my impressions of yesterday’s presentations.

I appreciate all the effort people put in making their presentations. Since I have done Toastmasters I’ve learned to appreciate more what goes into being an effective speaker. But, the nature of my work is that I have to be critical of everything people say about technology. Maybe I should say “I have to think critically” instead of “be critical”. The problem with the type of work we do is that it involves a lot of money and that inevitably obscures the truth about the technical details of how things work. So, I want to just sit back and applaud but my technical side wants to tear apart every detail.

A nice perk of being a RMOUG presenter is that I got to attend the pre-conference workshops for free as well as the rest of the talks. In past conferences that I have spoken at that was not the case. So, I went to a four-hour Snowflake workshop. I have read a fair amount on Snowflake so much that the speaker presented was familiar. I wonder how people who had no Snowflake background perceived the talk? Being a nuts and bolts Oracle person I would have liked to dig in more to Snowflake internals and discuss its limitations. Surely any tool has things it does better and things that it does not do so well because of the choices that the developers made in its design. I’m interested in how Snowflake automatically partitions data across files on S3 and caches data in SSD and RAM at the compute level. At least, that is what the information on the web site suggests. But with cloud computing it seems that people frown upon looking under the covers. The goal is to spin up new systems quickly and Snowflake is fantastic at that. Also, it seems to get great performance with little effort. No tuning required! Anyway, it was a good presentation but didn’t get into nuts and bolts tuning and limitations which I would have liked to see.

I spent the rest of the day attending hour-long presentations on various topics. AWS offered a 3 hour session on setting up Oracle on RDS but since I’ve played with RDS at work I decided to skip it. Instead I went to mostly cloud and Devops sessions. I accidentally went to an Oracle performance session which was amusing. It was about tuning table scans in the cloud. The speaker claimed that in Oracle’s cloud you get sub-millisecond I/O which raised a bunch of questions in my mind. But the session was more about using Oracle database features to speed up a data warehouse query. It was fun but not what I expected.

I was really surprised by the Devops sessions. Apparently Oracle has some free Devops tools in their cloud that you can use for on premise work. My office is working with a variety of similar tools already so it is not something we would likely use. But it could be helpful to someone who doesn’t want to install the tools yourself. I’m hopeful that today’s Devops session(s) will fill in more details about how people are using Devlops with databases. I’m mostly interested in how to work with large amounts of data in Devops. It’s easy to store PL/SQL code in Git for versioning and push it out with Flywaydb or something like it. It is hard to make changes to large tables and have a good backout. Data seems to be Devops’s Achilles heel and I haven’t seen something that handles it well. I would love to hear about companies that have had success handling data changes with Devops tools.

Well, I’ve had one cup of coffee and Starbucks doesn’t open for another half hour but this is probably enough of a pre-dawn RMOUG data dump. Both of my talks are tomorrow so today is another day as a spectator. Likely it will be another day of cloud and Devops but I might sneak an Oracle performance talk in for one session.

Bobby

Categories: DBA Blogs

Early morning RMOUG post

Wed, 2018-02-21 06:44

Well, it is early Wednesday morning here at the Westin hotel in Denver where the RMOUG Training Days conference is being held. I can’t sleep anyway so I thought I would write-up some of my impressions of yesterday’s presentations.

I appreciate all the effort people put in making their presentations. Since I have done Toastmasters I’ve learned to appreciate more what goes into being an effective speaker. But, the nature of my work is that I have to be critical of everything people say about technology. Maybe I should say “I have to think critically” instead of “be critical”. The problem with the type of work we do is that it involves a lot of money and that inevitably obscures the truth about the technical details of how things work. So, I want to just sit back and applaud but my technical side wants to tear apart every detail.

A nice perk of being a RMOUG presenter is that I got to attend the pre-conference workshops for free as well as the rest of the talks. In past conferences that I have spoken at that was not the case. So, I went to a four-hour Snowflake workshop. I have read a fair amount on Snowflake so much that the speaker presented was familiar. I wonder how people who had no Snowflake background perceived the talk? Being a nuts and bolts Oracle person I would have liked to dig in more to Snowflake internals and discuss its limitations. Surely any tool has things it does better and things that it does not do so well because of the choices that the developers made in its design. I’m interested in how Snowflake automatically partitions data across files on S3 and caches data in SSD and RAM at the compute level. At least, that is what the information on the web site suggests. But with cloud computing it seems that people frown upon looking under the covers. The goal is to spin up new systems quickly and Snowflake is fantastic at that. Also, it seems to get great performance with little effort. No tuning required! Anyway, it was a good presentation but didn’t get into nuts and bolts tuning and limitations which I would have liked to see.

I spent the rest of the day attending hour-long presentations on various topics. AWS offered a 3 hour session on setting up Oracle on RDS but since I’ve played with RDS at work I decided to skip it. Instead I went to mostly cloud and Devops sessions. I accidentally went to an Oracle performance session which was amusing. It was about tuning table scans in the cloud. The speaker claimed that in Oracle’s cloud you get sub-millisecond I/O which raised a bunch of questions in my mind. But the session was more about using Oracle database features to speed up a data warehouse query. It was fun but not what I expected.

I was really surprised by the Devops sessions. Apparently Oracle has some free Devops tools in their cloud that you can use for on premise work. My office is working with a variety of similar tools already so it is not something we would likely use. But it could be helpful to someone who doesn’t want to install the tools yourself. I’m hopeful that today’s Devops session(s) will fill in more details about how people are using Devlops with databases. I’m mostly interested in how to work with large amounts of data in Devops. It’s easy to store PL/SQL code in Git for versioning and push it out with Flywaydb or something like it. It is hard to make changes to large tables and have a good backout. Data seems to be Devops’s Achilles heel and I haven’t seen something that handles it well. I would love to hear about companies that have had success handling data changes with Devops tools.

Well, I’ve had one cup of coffee and Starbucks doesn’t open for another half hour but this is probably enough of a pre-dawn RMOUG data dump. Both of my talks are tomorrow so today is another day as a spectator. Likely it will be another day of cloud and Devops but I might sneak an Oracle performance talk in for one session.

Bobby

Categories: DBA Blogs

Should I use SQL or Python?

Thu, 2018-01-18 13:57

We had an outage on an important application last Thursday. A particular SQL statement locked up our database with library cache: mutex X waits. I worked with Oracle support to find a bug that caused the issue and we came up with a good workaround. The bug caused a bunch of shared cursor entries. So, I wanted to run a test on a test database to recreate the excess shared cursor entries. I wanted to run the SQL query that caused the outage a bunch of times. Also, we embed the SQL query inside a PL/SQL procedure so I wanted to run the query by calling the procedure. So, I needed to come up with a bunch of calls to the procedure using realistic data as a test script. This blog post is about the decision I had to make about creating the test script. Would I use SQL or Python to quickly hack together my test script? I thought it would be interesting to write about my choice because I am working on my Python for the Oracle DBA talk that encourages Oracle DBAs to learn Python. In this situation I turned to SQL instead of Python so what does that say about the value of Python for Oracle DBAs?

Let me lay out the problem that I needed to solve. Note that I was trying to get this done quickly and not spend a lot of time coming up with the perfect way to do it. I had over 6000 sets of bind variable values that the problem query has used in the past. I used my bind2.sql script to get some sample bind variable values for the problem query. The output of bind2.sql was in this format:

2017-11-27 15:08:56 :B1 1
2017-11-27 15:08:56 :B2 ABC
2017-11-27 15:08:56 :B3 JAFSDFSF
2017-11-27 15:08:56 :B4 345
2017-11-27 15:08:56 :B5 6345
2017-11-27 15:08:56 :B6 10456775
2017-11-27 15:08:56 :B7 34563465
2017-11-27 15:08:56 :B8 433
2017-11-27 15:09:58 :B1 1
2017-11-27 15:09:58 :B2 JUL
2017-11-27 15:09:58 :B3 KSFJSDJF
2017-11-27 15:09:58 :B4 234
2017-11-27 15:09:58 :B5 234253
2017-11-27 15:09:58 :B6 245
2017-11-27 15:09:58 :B7 66546
2017-11-27 15:09:58 :B8 657576
2017-11-27 15:10:12 :B1 1
2017-11-27 15:10:12 :B2 NULL
2017-11-27 15:10:12 :B3 NULL
2017-11-27 15:10:12 :B4 45646
2017-11-27 15:10:12 :B5 43
2017-11-27 15:10:12 :B6 3477
2017-11-27 15:10:12 :B7 6446
2017-11-27 15:10:12 :B8 474747

I needed to convert it to look like this:

exec myproc(34563465,10456775,345,433,6345,'JAFSDFSF','ABC',1,rc);
exec myproc(66546,245,234,657576,234253,'KSFJSDJF','JUL',1,rc);
exec myproc(6446,3477,45646,474747,43,'NULL','NULL',1,rc);

I gave myself maybe a minute or two to decide between using SQL or Python. I choose SQL. All I did was insert the data into a table and then manipulate it using SQL statements. Note that the order of the arguments in the procedure call is not the same as the order of the bind variable numbers. Also, some are character and some are number types.

Here is the SQL that I used:

drop table bindvars;

create table bindvars
(datetime varchar2(20),
 varname varchar2(2),
 varvalue varchar2(40));

insert into bindvars values ('2017-11-27 15:08:56','B1','1');
insert into bindvars values ('2017-11-27 15:08:56','B2','ABC');
insert into bindvars values ('2017-11-27 15:08:56','B3','JAFSDFSF');
insert into bindvars values ('2017-11-27 15:08:56','B4','345');
insert into bindvars values ('2017-11-27 15:08:56','B5','6345');
insert into bindvars values ('2017-11-27 15:08:56','B6','10456775');
insert into bindvars values ('2017-11-27 15:08:56','B7','34563465');
insert into bindvars values ('2017-11-27 15:08:56','B8','433');
insert into bindvars values ('2017-11-27 15:09:58','B1','1');
insert into bindvars values ('2017-11-27 15:09:58','B2','JUL');
insert into bindvars values ('2017-11-27 15:09:58','B3','KSFJSDJF');
insert into bindvars values ('2017-11-27 15:09:58','B4','234');
insert into bindvars values ('2017-11-27 15:09:58','B5','234253');
insert into bindvars values ('2017-11-27 15:09:58','B6','245');
insert into bindvars values ('2017-11-27 15:09:58','B7','66546');
insert into bindvars values ('2017-11-27 15:09:58','B8','657576');
insert into bindvars values ('2017-11-27 15:10:12','B1','1');
insert into bindvars values ('2017-11-27 15:10:12','B2','NULL');
insert into bindvars values ('2017-11-27 15:10:12','B3','NULL');
insert into bindvars values ('2017-11-27 15:10:12','B4','45646');
insert into bindvars values ('2017-11-27 15:10:12','B5','43');
insert into bindvars values ('2017-11-27 15:10:12','B6','3477');
insert into bindvars values ('2017-11-27 15:10:12','B7','6446');
insert into bindvars values ('2017-11-27 15:10:12','B8','474747');

commit;

drop table bindvars2;

create table bindvars2 as
select 
b1.varvalue b1,
b2.varvalue b2,
b3.varvalue b3,
b4.varvalue b4,
b5.varvalue b5,
b6.varvalue b6,
b7.varvalue b7,
b8.varvalue b8
from 
bindvars b1,
bindvars b2,
bindvars b3,
bindvars b4,
bindvars b5,
bindvars b6,
bindvars b7,
bindvars b8
where
b1.datetime = b2.datetime and
b1.datetime = b3.datetime and
b1.datetime = b4.datetime and
b1.datetime = b5.datetime and
b1.datetime = b6.datetime and
b1.datetime = b7.datetime and
b1.datetime = b8.datetime and
b1.varname = 'B1' and
b2.varname = 'B2' and
b3.varname = 'B3' and
b4.varname = 'B4' and
b5.varname = 'B5' and
b6.varname = 'B6' and
b7.varname = 'B7' and
b8.varname = 'B8';

select 'exec myproc('||
B7||','||
B6||','||
B4||','||
B8||','||
B5||','''||
B3||''','''||
B2||''','||
B1||',rc);'
from bindvars2;

I hacked the insert statements together with my Textpad text editor and then loaded the rows into a little table. Then I built a new table which combines the 8 rows for each call into a single row with a column for each bind variable. Finally I queried the second table generating the procedure calls with single quotes, commas and other characters all in the right place.

Now that the rush is past and my testing is done I thought I would hack together a quick Python script to do the same thing. If I had chosen Python how would have I done it without spending a lot of time making it optimal? Here is what I came up with:

Instead of insert statements I pulled the data into a multi-line string constant. Then I split it into a list of strings with each string representing a line. Then I split each line into space delimited strings so each line would have date,time,bind variable name, bind variable value. Finally I looped through each set of 8 lines extracting the bind variable values and then printing the bind variables in the correct order and format.

These are two quickly hacked together solutions. I think the key point is how I stored the data. With SQL I used tables. With Python I used lists. I’m not sure which I like better in this case. I’ve been doing SQL longer but Python wasn’t really harder. I guess my decision under pressure to use SQL shows that I still have more comfort with the SQL way of doing things, but my after the fact Python hacking shows that the Python solution was not any harder. FWIW.

Bobby

 

Categories: DBA Blogs

Pages