Bobby Durrett's DBA Blog

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.

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:

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

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:

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.

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

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.

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

Bobby

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

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

I’m leaving for Las Vegas a week from this Sunday for the Collaborate 2018 conference #C18LV.

I’m doing the same two talks that I did in Denver at RMOUG Training Days.

Here are the times and locations for the talks if you will be there at C0llaborate:

Session: 1415: Python for the Oracle DBA
Date/time: Tue, Apr 24, 2018 (09:45 AM – 10:45 AM)
Location: Jasmine H

Session: 1416: Toastmasters for the Oracle DBA
Date/time: Wed, Apr 25, 2018 (08:30 AM – 09:30 AM)
Location: South Pacific I, North Convention Center

I haven’t looked at these talks since the conference in February and I have focused my attention on other things so now I am starting to get my mind back on these talks. I am passionate about both topics so hopefully that will come across. I got good feedback at the conference in Denver so I think that those who hear my talks in Vegas will benefit from them. If you can make it to Collaborate I would love to have you attend.

Las Vegas will be just a bit warmer than the 6 degree Fahrenheit mornings that we had in Denver, so that will be fun. 🙂

Bobby

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

P.S.

Today, 4/30/2020, more than 2 years after this post I have the same error on most likely the same database and setting parallel_max_servers to 2 did not solve it. I have upgraded Delphix recently.

After trying a zillion parameter options I finally got the idea of using a different 11.1.0.7 home on the same server. It is at a different patch level than the source database so normally that is not what you want. But the other Oracle home must not have the bug because the provision worked like a charm using it. After successfully provisioning the VDB on the wrong 11.1.0.7 home I used the upgrade feature to point it to the correct 11.1.0.7 home.

Maybe the useful suggestion here is that if you have a version of Oracle that has a bug that prevents you from provisioning a VDB through Delphix you can set up a new Oracle home with a different patch level that does not have the bug and do your provision on it. Then use the upgrade feature to move the VDB back to the home you wanted it on in the first place.

Bobby

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

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

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.md 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