Bobby Durrett's DBA Blog

It has been two years or more since I first thought about moving my blog to Amazon Web Services (AWS) from iPage, the hosting company that this blog has always been on. My company uses AWS for a number of cloud initiatives, and I need to learn more about it. I thought it made sense to make moving my blog a little cloud training project for me. Plus, there were a couple of things that I wanted to improve over my experience with iPage. My point is not to criticize them. It has been great having a blog, and iPage made it easy to get started. But performance of my blog has really gone down. Often my site was so slow that updates would time out and often the blog would just hang. Also, I wanted to get full control over the environment and have a Linux host that I could ssh into. So, it made sense to move to AWS and put my blog on an EC2 Linux virtual machine. But I put it off until now because it seemed like too big of a project. It turned out to not be that bad. I felt like I had a little extra time at the beginning of the year before we get deep into new projects for 2021 so it made sense to do this simple cloud project. I am glad that I did it because now my blog is more responsive, and I learned a lot about AWS and hosting a web site.

My move to AWS revolved around me reading a number of informative web pages – mostly AWS tutorial pages – so I want to put the links to them in this post and describe things I learned from them or ways that I deviated from them. First off, I created an AWS account for myself two years ago and let it sit. I do not recommend this approach. I probably could have saved myself a little money on free tier pricing if I created the account right before I started on the blog migration. But I do not care. The money is not very much, and the training is worth far more. I do not have the link to whatever page I used to setup my AWS account but it was not hard. You need a credit card pretty much. I ended up deciding to use the same Oregon region that my company uses since it is out West near where I live in Arizona.

Since I already had an AWS account my first step was to see how to setup an EC2 instance that would hold a WordPress blog. Since I want to save money I chose the inexpensive t2.micro size instance which has 1 core and 1 gigabyte of memory with 8 gigabytes of storage. Hopefully I could run this simple blog on an affordable instance. I knew from iPage support that I was running in around 128-256 megabytes of memory on their shared server so hopefully a 1 GB memory host would be enough. The first tutorial I used showed me how to setup a “LAMP” server which would support WordPress on an EC2:

Tutorial: Install a LAMP web server on Amazon Linux 2

It seems that “Amazon Linux 2” is the latest version so I went with that. The biggest change I ended up making from this tutorial is that I wanted to use PHP 7.4 which WordPress recommends. So, I changed this line from the tutorial:

sudo amazon-linux-extras install -y lamp-mariadb10.2-php7.2 php7.2

to

sudo amazon-linux-extras install php7.4

Much later on when I was trying to get Jetpack to work I ended up installing php-xml as well with:

sudo yum install php-xml

I thought about trying to install the latest version of MySQL but got bogged down trying to figure out the right yum command so I just stuck with the MariaDB install as listed in the tutorial. The only other thing from this tutorial is that after migrating my blog’s files over I had to rerun the chmod commands listed to get the directories and files to have the group permissions needed to run updates. So, I ran it once to get the server setup but much later I ran it again with all my files in place.

Now that I had the LAMP server setup on my small EC2 instance I needed to install WordPress just to get a basic install working. Later I would overwrite all the WordPress files with ones from my iPage host. I followed this tutorial:

Tutorial: Host a WordPress blog on Amazon Linux 2

One problem with the tutorial is that it left it where I could not do WordPress updates without an FTP password. But with my EC2 I could only use a key file to login so I followed the instructions in this URL to fix it where I could run updates without a password:

https://stackoverflow.com/questions/640409/can-i-install-update-wordpress-plugins-without-providing-ftp-access

I did:

chmod -R 775 ./wp-content

vi wp-config.php

# add the next line:

define('FS_METHOD', 'direct');

The only other thing I could say about this tutorial is that it does not include things like how to deal with plugins like Wordfence and Jetpack that I had to deal with later in the migration.

After getting a basic WordPress instance working on an EC2, I had to figure out how the internet works. I really have not messed that much with domain registration and DNS records prior to this. In the end it was much simpler than it seemed at first. After looking at tutorials on moving domains to AWS I figured out what seemed like a safe path. Leave the domain registration with iPage and keep using the iPage name servers but point the web server DNS records to my EC2 instance. If something went wrong, I could just point the DNS records back to iPage, delete everything from AWS, and I would be no worse off than when I started. I ended up doing the following steps to get my blog working and up on AWS but still with the domain and DNS at iPage:

1. Put an under-construction message on my current blog
2. Backup current down EC2 to snapshot
3. Setup Elastic IP and bring up EC2
4. Copy database from iPage to EC2 with edits
5. Copy files from iPage to EC2 with edits
6. Move DNS entries from iPage host (for web only) to EC2 and test
7. Setup certificate with Certbot

I edited this list for clarity because I did some wrong things that failed but these are the steps that really worked. I had to setup the certificate after switching over the DNS to point to the EC2. One key thing I learned was that you really needed to get a domain pointed to your site before you could finish setting it up. That is a bit of a pain when you are moving from one site to another.

I put a post out there letting people know this blog would be up and down. Then I posted an update about how things are going and kept updating it with PS’s.

Backing up an EC2 is a lot like taking a snapshot of a virtual machine in VirtualBox. You just stop the EC2 instance and take a snapshot of its volume. My small instance only has one volume which is the root filesystem /. The only thing I had to figure out was that the device name for the root filesystem was /dev/xvda. You must know that when you restore a volume from a snapshot. Works well. Nice to be able to take snapshots and restore from them.

An Elastic IP is Amazon’s term for an IP address that is on the internet. If you create an Elastic IP address and associate it with an EC2 instance, then it will always have that address after you stop and start it. Otherwise your instance’s public IP address changes with every stop and start. For me I was using Putty and WinSCP to access the host and it was nice to finally get a consistent IP address. Also, I could setup my DNS entries to point to an IP address that would not change which is good since I am stopping and starting the EC2 instance all the time. Documentation for Elastic IP:

Elastic IP addresses

Copying the database was not hard. I had played with this years ago. You download the SQL script to rebuild and load the MySQL tables for the blog database through phpMyAdmin in iPage’s GUI control panel. Was only about a 27-megabyte file. I just had to edit it to have my database name. IPage had a bunch of letters and numbers as the database name and I made up one that was more friendly. I just used WinSCP to copy the edited SQL dump up to my EC2 and ran it against the MariaDB database I had already created when I installed WordPress. The only minor trick was that I did a custom export of the database from iPage telling it to include drop table commands. That way the script dropped the tables created by the initial WordPress install and created new ones.

I ended up copying the files using SCP commands from the EC2 host. They looked like this:

scp -pr -P 2222 bobbydurrettdbacom@ftp.bobbydurrettdba.com:/* .
scp -p -P 2222 bobbydurrettdbacom@ftp.bobbydurrettdba.com:/.* .

I was worried that I might miss an error copying a file and not notice it, but I just ran these commands manually and they seemed to run fine so I did not worry about it.

The most interesting thing was how simple it was to move the DNS records from my iPage host to the new one. There were two “A” records for the web site. One was for bobbydurrettdba.com and the other for www.bobbydurrettdba.com. I just made a note of the current IP address of my host on iPage and replaced it with the Elastic IP address from AWS.

Originally, I tried to use the certificate that I had already paid for through next year. IPage lets you download the certificate and its private key from its GUI control panel. This worked in terms of letting the site be visible, but Jetpack kept throwing these errors:

cURL error 60: SSL certificate problem: unable to get local issuer certificate

I tried everything I could find on the internet and finally concluded that there must be some file that iPage uses with the “chain” of certificates or certificate authorities. Something like that. I didn’t delve that deep into it. I just figured that there was a file that I didn’t have. So, I gave up on the certificate I paid for and installed the free Certbot instead. Here are the instructions I used to setup SSL:

Tutorial: Configure SSL/TLS on Amazon Linux 2

I did not do step 3 to harden it, but I did do the steps at the end called “Certificate automation: Let’s Encrypt with Certbot on Amazon Linux 2”. Those steps gave my site an A rating on https://www.ssllabs.com/ssltest/ so they must do the hardening steps automatically. I tried to do step 2 with my certificate from iPage but that did not work with Jetpack. I think I was missing the file for SSLCACertificateFile in /etc/httpd/conf.d/ssl.conf and that was why I got the certificate error. In any case the Let’s Encrypt with Certbot steps seemed to cure a lot of ills.

I ran into a funny problem at one point in this setup. I ran out of memory. I don’t think I kept the error message. WordPress just told me something was wrong, and I had to update this line in /var/www/html/wp-config.php to see the error:

define('WP_DEBUG', true);

This showed the out of memory error on the PHP web pages. I think you can also send these errors to a log on the filesystem which is probably what I will configure it to do later. But this WP_DEBUG true setting showed me that various PHP scripts were out of memory. The t2.micro instance has 1 gigabyte of memory and no swap. So, when you run out of memory you are out. You cannot swap processes out to disk. I found a bunch of processes named php-fpm using the memory. I had to edit the file /etc/php-fpm.conf to limit my system to 5 php-fhm processes with this setting:

; The maximum number of processes FPM will fork. This has been designed to control
; the global number of processes when using dynamic PM within a lot of pools.
; Use it with caution.
; Note: A value of 0 indicates no limit
; Default Value: 0
;process.max = 128
process.max = 5

So far, the limit of 5 processes has not hurt the site. It is much more responsive than it was before. I may have to increase this later if it gets to be a problem. Right now, I am running with plenty of free memory.

The last steps were to move my DNS entries from iPage and then the domain registration. This tutorial describes moving the DNS entries:

Making Route 53 the DNS service for a domain that’s in use

This was a lot easier than I thought it would be. I only had 5 DNS entries to move. Two were the A records for the web site and 3 were for the email forwarding company ImprovMX. I thought email forwarding would be done within AWS, but it was a lot easier to use the free ImprovMX service. I only get a handful of emails a month. Anyway, you have to setup two MX records and one TXT record for the email forwarding. So, I manually added 5 entries on Route 53 in AWS and moved my DNS from iPage to Amazon. This site shows you which name servers your domain is using and who it is registered by:

https://lookup.icann.org/lookup

Here are the steps to transfer the domain registration:

Transferring registration for a domain to Amazon Route 53

Pretty cool. The domain registration transfer finally finished after 7 days. I shut down my iPage account and I am fully on AWS. I was on iPage for over 8 years and it has been a great value to me, but it was worth it to move my blog to AWS at this time both for the better performance and for the experience messing with AWS.

Bobby

P.S. While waiting for the domain registration transfer to finish I realized that I was not getting any email from my blog site. Evidently AWS blocks email sent from an EC2 instance by default. You have to ask AWS to remove their email block as documented here: How do I remove the restriction on port 25 from my Amazon EC2 instance? They approved it easily, so it was no big deal, but I am still having issues.

I added a couple of yum packages:

yum install sendmail

yum install mailx

Not sure if these were needed. Now I am trying to get “reverse DNS” setup which I think means that when I run nslookup on my IP address it returns bobbydurrettdba.com instead of the Amazon public DNS name. ImprovMX may require this to prevent outgoing email being blocked as spam. This is now working:

>nslookup bobbydurrettdba.com
...

Non-authoritative answer:
Name:    bobbydurrettdba.com
Address:  44.241.233.131


>nslookup 44.241.233.131
...

Name:    bobbydurrettdba.com
Address:  44.241.233.131

Was not as hard as I thought. This was a helpful page:

https://aws.amazon.com/premiumsupport/knowledge-center/route-53-reverse-dns/

Meanwhile I setup a weekly backup script and a space monitoring script. I also wrote a Python script to automate bringing down the EC2 instance and creating a snapshot of its root volume. There is more that I could say but this is enough for one post.

I have more to do but I have bobbydurrettdba.com pointing to an Amazon EC2 virtual machine. Seems faster.

I still have email forwarding done on iPage and they still have the domain registration and dns.

I plan to work on the email forwarding next so all the something@bobbydurrettdba.com email addresses will be handled by AWS. Either that or just change them to my personal email address. Would be nice to use the domain since I have it.

Bobby

P.S. Jetpack was down for a good bit. Had to edit the php.ini file for some reason.

P.P.S. Jetpack was down again this morning. Changed from the certificate I got from iPage to a free one from certbot. Seems to be working now.

P.P.P.S. Looks like I got email forwarding working with improvmx which is free instead of doing something in AWS which is hard to use and costs money. Kind of nice because the certificate is free and now the email is also. So it is just the cost of the hosting and domain.

PPPPS Up on Amazon’s name servers. Transferring domain. Could be a few days but it is the last step to be fully AWS.

1/26/21 8:18 AM Phoenix Arizona time

Outgoing email is not working. So, no one will get notifications of new comments or posts including my replies to comments. Also I will not get any notifications from WordPress of my plugins. I am trying to figure out how to get this going. I have email forwarding working but I cannot get an email out of the EC2 instance.

2:15 PM

This might be fixed. I got a few emails out that had been blocked. They all went into spam. I am trying to get reverse dns setup so that may keep the emails from looking like spam.

1/27/21 8:10 AM Phoenix time

AWS got reverse dns setup and now email works well from blog to world including to my @bobbydurrettdba.com alias.

I am moving this blog from iPage to AWS starting now. This page, bobbydurrettdba.com, might be down, or you might see certificate errors. This is just me. I will put up an all clear post when it is done.

Bobby

I put together a quick testcase to show myself how to use DBMS_RLS on 11.2.0.4 to mask a column. If you would like to run it or see the output it is here: test case zip.

I ran the test case as SYSTEM with a tnsnames.ora entry named ORCL1124.

I based the test on the Oracle 11.2.0.4 Security manual section titled: “Using Column Masking to Display Sensitive Columns as NULL Values”

The most interesting thing was that I did a 10053 trace like I did on an earlier post: earlier VPD post with trace.

Tracing the column masking I found that Oracle sticks a CASE statement into the select clause:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TABLE_LIST"."OWNER" "OWNER","TABLE_LIST"."TABLE_NAME" "TABLE_NAME",CASE  WHEN USER@!<>'CANNOTSEE' THEN "TABLE_LIST"."BLOCKS" ELSE NULL END  "BLOCKS" FROM "TEST"."TABLE_LIST" "TABLE_LIST"

The predicate that I setup just checked for

user<>'CANNOTSEE'

So, Oracle’s 11.2.0.4 DBMS_RLS package for VPD features just adds a CASE statement that returns a NULL for the column when the predicate is true and returns the real column otherwise.

Not earth shattering but I did not want to forget this.

Bobby

I did a little research yesterday and I am writing this post to document what I learned so I can remember it and since I could not easily find this information. I have a database that uses shared servers and want to connect to it using a dedicated server connection. Everything I found said to add (SERVER = DEDICATED) on your client but I was not able to do that. A coworker of mine said that I should be able to add a service that only connects as a dedicated server process so I tried to figure out how to do that. I found a way to configure a service in the listener.ora file on the database server so that anyone connecting with that service name would only get a dedicated server connection. I tested this on Oracle 11.2.0.4 on Linux

I setup a test database for shared servers with these parameters:

alter system set max_shared_servers = 2 scope=both;

alter system set shared_servers=2 scope=both;
alter system set dispatchers='(PROTOCOL=TCP)(DISPATCHERS=2)' scope=both;

I set the service_name and domain to be like production:

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      ORCL

SQL> show parameter domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      WORLD

I set the listener.ora similar to production:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ora1124)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL.WORLD)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /oracle/product/db/11.2.0.4)
    )
  )

I stopped the listener and database and then started the listener and database. At this point if I connected using (SERVICE_NAME = ORCL.WORLD) or (SID = ORCL) I got a shared server process. For example:

ORCL1124.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 127.0.0.1)(Port = 61020))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL.WORLD)
    )
  )

ORCL1124.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 127.0.0.1)(Port = 61020))
    )
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )

To this point all I was doing was matching a production database of a different name’s configuration. What I wanted to do was do something to the listener.ora to add a new service name that only connects dedicated. I changed the SID_LIST_LISTENER part of the listener.ora to this:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL.WORLD)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /oracle/product/db/11.2.0.4)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDED)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /oracle/product/db/11.2.0.4)
    )
  )

This added the service name ORCLDED that only connects dedicated. I used a tns entry like this:

ORCL1124.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 127.0.0.1)(Port = 61020))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCLDED)
    )
  )

I verified this by running this query with the connection:

select username,server,count(*)
from v$session
group by username,server
order by username,server;

It would show my user’s session as either SHARED or DEDICATED.

Also, I could tell from the listener status:

[oracle@ora1124 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2021 14:46:26

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora1124)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                13-JAN-2021 14:45:51
Uptime                    0 days 0 hr. 0 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/db/11.2.0.4/network/admin/listener.ora
Listener Log File         /oracle/product/diag/tnslsnr/ora1124/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1124)(PORT=1521)))
Services Summary...
Service "ORCL.WORLD" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ORCL", status READY, has 3 handler(s) for this service...
Service "ORCLDED" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Notice that service ORCLDED only has 1 handler which is the one to spawn dedicated server processes. ORCL.WORLD has 3 handlers which includes 2 for the 2 dispatchers.

I also experimented with adding the setting SERVICE= to the dispatchers parameter but it did not meet my needs in this case. Here is the format of the parameter as I tested it:

alter system set dispatchers='(PROTOCOL=TCP)(DISPATCHERS=2)(SERVICE=SS)' scope=both;

With this setting the listener status had this entry for service SS:

Service "SS" has 1 instance(s).
  Instance "ORCL", status READY, has 2 handler(s) for this service...

So, it looks like setting the service in the dispatchers parameter would pull out the shared server process connections on to that service but that was not what I needed in this case.

Anyway, I messed with this yesterday and thought I would put it out there for my own memory and in case others need it.

Bobby

I have some Python scripts that I use to access a bunch of databases to gather information such as the size of the database. Usually it is not that important how long they take so I have been running queries against a list of 100 databases one at a time and it can take a while depending on what I do. Coworkers of mine have worked on running Unix/Linux shell scripts in parallel to hit a bunch of systems like this more quickly. So, I thought I would look at how to do the same in Python. I am sure there are more complicated ways to do it, but I got good results based on the simplest example from this part of the Python documentation:

https://docs.python.org/3.8/library/multiprocessing.html

Here is a simple python script to get the size of the database files from four databases at the same time:

All I did was take the first example from the multiprocessing documentation and replace f() which squared a number with dbspace() which connects to a database and runs a query to get the total db size. I experimented with different Pool() sizes. It is 4 in the example.

I picked 82 non-production databases that I have access to and ran the same query sequentially in a loop and it took 375 seconds. I took the code above and increased the Pool to 32 processes and it ran in 24 seconds. That is about a 15x speedup.

I am sure that I could get a lot more complicated with all this stuff, but this works well with minimal effort.

Bobby

I guess I have had a desk in an office since around Summer 1990 when I first reported for work in San Bernardino. I have moved offices multiple times including across the country twice and did not have a desk for a week or two during those moves. But otherwise I have always had a place where I could leave my stuff at work. A couple of weeks ago I cleaned out my desk, maybe for good, even though I am still employed. Like a lot of people COVID-19 has pushed my work to 100% remote work out of my home. So, it was no surprise that I needed to clean out the stuff I had in my unused cubicle in the office. It is fine, but it still is kind of the end of an era and I am a little nostalgic mainly about the framed pictures and certificates that I am disposing of, several of which I have had for two decades. Of course, the funny thing is that the last time I moved desks from one cubicle to another I put all these framed pictures and certificates in a drawer and have never gotten the energy to put them back up again. I can never get them that straight when I hang them up. Anyway, while I was off work on Wednesday, I took all the pictures and certificates out of their frames. Will probably donate the frames and throw away the pictures if they are duplicates. But the certificates are a record of my efforts at self-improvement at work.

These are my family pictures that I had at work. I have had some of these at work for over 28 years. Of course, like I said, I had these in a drawer at work anyway! Most of these are duplicates and one thing about working from home is I see the real people, so I do not need their picture to remind me of them!

I figured I would donate the frames. I do not plan to hang anything up at home and if we do go back in the office, I think I will just keep images on my computer instead.

This was so long ago. I started on Oracle 7. I think it was 7.0.16. I do not remember much except that it was a hard test for me.

This is the most significant OCP certification. There is so much I could say about my career and my self-development as represented by this point in time. Between the Oracle 7 cert in 1998 and this one in 2003 I went through a time of discouragement in my career and part of that was not pursing the Oracle 8 OCP cert. I had a lot of non-technical things to do at work and the challenge of providing for my young growing family and did not focus on Oracle learning. But when I finally decided to leave my first job, which I thought would be my only and last job, I decided to get OCP certified to help me get a new job and that was when I got my 9i cert. I think I had to pay for an Oracle class out of my own pocket to get this one which was a big deal at the time. Since then all my OCP tests were upgrade exams and my employers have paid for most of my tests. They paid for my first 12c test that I failed but I paid for the one I passed – happy to do it!

I must be getting old because I cannot remember this at all. I just celebrated my 15th year with my current employer so the rest of these OCP certs must have been while here. 3 years per cert seems about the way it goes. I think I was still in South Carolina for this one.

I do not remember this one either, but it was 5 years from the last so that is a little significant. We merged DBA teams into one team around 2010 and I moved to Arizona at that time. We have a ton of 11.2 systems, but I think this was an 11.1 cert.

This one nearly killed me. I cannot believe it has been 6 years since I took this test. I guess I am due for another one, but I do not know what I would study. Maybe some Oracle cloud cert? 20c? I wrote a couple of blog posts about this one. One reason that I have not had Oracle certs since 2014 is that I have been doing other kinds of self-development such as through Toastmasters.

As you can tell from the certificates, I did a bunch of Toastmasters from 2016-2018. This has been very valuable to me in my career. Toastmasters has really changed the way I think about communicating with other people at work about Oracle database technology. I have put the focus on making myself a better communicator instead of getting frustrated with people who do not understand or agree with things I am trying to say. But since 2018 I have slowed down a bit and the last few months have been a mess with COVID-19. I have not felt like I had time to blog or pursue self-improvement. But hopefully that has changed for the better now. Maybe things will improve going into next year? The big question is what do I work on next for self-development? Oracle certification? Do I develop Toastmasters mastery? Online Computer Science academic studies? Other databases – SQL Server, MySQL, etc? Cloud – the future is cloudy. 🙂

I do not know what the future holds but my desk cleanout and taking my pictures and certificates out of their frames paints a picture of where I have been. What comes next?

Bobby

Quick note to myself. If I am tuning a query by its SQL_ID I use these scripts:

From AWR:

findsql.sql – finds SQL_ID of queries that match certain strings

sqlstat.sql – shows execution history of SQL_ID

getplans.sql – shows all plans for SQL_ID

From V$ views:

vfindsqlid.sql – finds SQL_ID of queries that match certain strings

vsqlarea.sql – shows current executions of SQL_ID

extractplansqlid.sql – shows current plan of SQL_ID

I mainly mine the history from the AWR but I have been looking at V$ view information more recently, especially after putting in a fix to a slow query plan. I updated this on 6/18/21 to include the find scripts. The six scripts together are a pretty good toolkit for finding a SQL_ID and getting information about it. The AWR gives you history (we keep 6 weeks of hourly snapshots). The V$ scripts give you current information.

Bobby

I wanted to write a quick post to document how to format nice columns in Python. Mainly I want to remember this myself and if I put it on my blog, I can find it quickly. There are probably better posts out there but so be it.

If you have read my other posts, you will realize that I am command line oriented. Yes, I know that makes me a dinosaur. 😊🦕🦕 My two main GitHub repositories are command line scripts. PythonDBAGraphs displays graphs but you run it from the command line. OracleDatabaseTuningSQL is a collection of SQL*Plus scripts that I run from the command line. Typically I work on my Windows 10 laptop running the Windows version of command line SQL*Plus in a command prompt window. Some of the SQL*Plus scripts like tablestats.sql use the column command to format query output in nicely sized columns:

The column commands just trim owner to 20 characters and table_name to 30 to format the output nicely.

Since I am not a professional application developer and since programming is not my primary task, I tend to learn just enough of something to get by. In the C language that I learned in college around 1984 – eons ago – I know that there is a formatted print command printf that will do all kinds of fancy output. But I just use it in the most basic way such as:

printf("%s is a string.\n",mystringvariable);

%s is for strings, %d integers, %f floats – simple. But you can format columns nicely if you need to. I recently did a little formatted output for a Nethack code modification that I played with. In my hacked version of the delivered files.c I put some commands to print out some debugging information in column format(edited for clarity):

fprintf(fptr,"\nInventory Letter     Object Type or Name                     Priority\n");
fprintf(fptr,"---------------------------------------------------------------------\n");

for (i = 0; i < num_autoletter ; i++) {
    fprintf(fptr,"%c                    %-40s    %4d \n",letter, object_type_or_name, priority);
}

The output looks something like this:

Inventory Letter     Object Type or Name                     Priority
---------------------------------------------------------------------
b                    blindfold                                      1
l                    magic lamp                                     1
l                    lamp                                           2
l                    oil lamp                                       2
l                    brass lantern                                  2
k                    key                                            1

This is just a game, but the point is that I needed to line up the output in columns. %-40s and %4d were the C formatting strings that I can never remember and had to look up for that situation, but they did the job. So, the question is how to do the same in Python?

Usually in Python I just use the print() command and pass it a string that I construct. For example:

Python 3.8.3 (tags/v3.8.3:6f8c832, May 13 2020, 22:20:19) [MSC v.1925 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> i = 5
>>> print("i = "+str(i))
i = 5
>>>

I use the str() function to convert numbers to strings and the concatenation operator + to combine strings. Then I pass the full string to print(). Easy and good enough for basic output that does not need to be formatted into columns or otherwise. But I know there is a Python equivalent to C’s printf formats, but it just is not in the forefront of my mind. I have to look it up to use it. I recently did that for a Rosetta Code example program. The output was supposed to look like this:

Name     Bits  Start  End
=======  ====  =====  ===
ID         16      0   15
QR          1     16   16
Opcode      4     17   20
AA          1     21   21
TC          1     22   22
RD          1     23   23
RA          1     24   24
Z           3     25   27
RCODE       4     28   31
QDCOUNT    16     32   47
ANCOUNT    16     48   63
NSCOUNT    16     64   79
ARCOUNT    16     80   95

Here is the code to print this formatted output (simplified):

print("Name     Bits  Start  End")
print("=======  ====  =====  ===")

for line in lines:
    print('{0:7}    {1:2d}     {2:2d}   {3:2d}'.format(name, bits, startbit, endbit))

I guess the 0:, 1:, 2:, 3: numbers represent the arguments to the format method of the type str. A regular string like “Hello World” is of type str in Python.

>>> type("asdfa")
<class 'str'>
>>>

:7 seems to just say that this is a string and stick the string in a 7-character width column. :2d seems to say that this is an integer and stick it in a two-character column. The spaces between the {} bracketed format strings line up the columns. I know this is a simple example.

There are a lot of nice web pages out there. This is from the tutorial:

7.1.2. The String format() Method

This seems to be from the reference:

Format String Syntax

Crazily complex examples:

Format examples

There is a lot of great Python help on print formatting on Stack Overflow. I had a great post that I wanted to link in but could not find it.

Anyway, this is not a database post, but I use Python in my Oracle database work and wanted to do a quick post for my own reference if nothing else.

Bobby

I resolved another production performance issue with a SQL Profile yesterday. I have several posts about SQL Profiles, so I do not want to be redundant, but this case was a little different because it was not clear that I had a better plan. I want to document the challenge that I had deciding if I had the best plan and show the resolution.

On September 21 when I think I was on vacation or otherwise not in the office there was a big performance issue on an older production database. The on-call DBA identified the sql_id of the top SQL statement as 30q69rbpn7g75. But he and an application support developer together could not connect that SQL statement back to the long running reports that were impacting the business. Eventually the issue went away later that night. Here is some of the execution history from that original issue:

This output is from my sqlstat.sql script. Notice how earlier in the day there are several plans with widely varying elapsed times. Plan hash value 1732425686 averages 66 millisconds during the hour ending at 10 am. I do not know why the query has so many different plans. 2823019537 was the bad plan and it got locked in throughout the afternoon and into the night.

Later in that same week I reviewed the plans and found that the longest running plans used certain indexes on the top table and the shorter ones used another index. I looked at the “Segments by Logical Reads” section of an AWR report during the problem time and found that the top segment was index X6_WFCTOTAL. I used by getplans.sql to get all the plans for SQL id 30q69rbpn7g75 and found that the fastest ones used range scans against index X1_WFCTOTAL and the slow ones did range or skip scans against indexes X5_WFCTOTAL or X6_WFCTOTAL. So I picked one plan, 382022017, and used coe_xfr_sql_profile.sql to force 30q69rbpn7g75 to always run with plan 382022017 which used index X1_WFCTOTAL. Here is some execution history of the plan I picked:

This plan was averaging less than 1800 milliseconds. But after putting in the supposedly better plan it seemed to have worse execution times that other plans. Also, the plan hash value was different than 382022017. Here is the execution history from when I put this SQL Profile in place:

Notice first that the plan hash value is 653445232 instead of 382022017. I usually see things like this when the plan has system generated table temporary table names, but I have not seen that in the plan. There must be something like that going on. Either that on the SQL Profile just locks in a slightly different plan. Anyway, 653445232 is the plan caused by the SQL Profile. Notice how the average elapsed time hangs around 90,000 to 100,000 milliseconds. But 382022017 has elapsed times under 1800 milliseconds. Seeing these results, I dropped the SQL Profile. Kind of like a doctor who swears to “do no harm” I did not want to slow down queries that were running very efficiently in my attempt to prevent another system slowdown or outage like we had on September 21. I dropped the SQL Profile on September 29.

Then yesterday, October 19, the problem recurred. This time the bad plan was 3908549093 and used a skip scan. I seem to see a lot of bad plans with skip scans. Here was the execution history before I put the SQL Profile back in:

Between 12 and 1 pm the system was really bogged down with executions of 30q69rbpn7g75 taking almost an hour, 3543778 milliseconds. So, I put the SQL Profile back in. I had the script sitting there from the first time I tried it. These are all kept in the sqlt/utl directory. After putting it in things cleared up and the backlog of reports slowly emptied. I think we killed one long running session and had the user rerun the report with the SQL Profile in place.

But with the SQL Profile in place it still in consistently around 90,000 milliseconds even today. There are no executions under 1800 milliseconds like before. It is a puzzle, but it got us through the issue. I am planning to leave this in place to prevent another production outage, but I suspect that these reports may be running longer than normal in many cases. But at least they are consistent and the business needs are being met.

So, this is another example of a SQL Profile to the rescue in a production database performance issue. But this was different because it was not clear that the one plan was always the best one. I went so far as to put it in and take it out and then put it in again. I ended up leaving it in because it resolved a performance issue that we had twice and do not want to have in the future. Hopefully any less than optimal performance outside of these peak times will be worth it since we are preventing an outage with significant impact to the business.

Bobby