My company, US Foods, has posted a number of information technology jobs that are in Chicago (Rosemont) or Phoenix (Tempe). Here is the web site:
https://www.usfoods.com/careers.html
IT jobs are under the “Technical” category.
Bobby
I’m working on upgrading a very buggy unpatched 11.1.0.7 Oracle database to a fully patched 11.2.0.4 version. I’m using Delphix to support the upgrade and it has been a big help so far. This is on the HP-UX 11.31 Itanium platform.
The great thing about using Delphix to support an upgrade is that my very first pass through the upgrade scripts was with a full-sized clone of production. In the past I probably started with a tiny subset or even an out of the box demo database for my first upgrade pass and even when I got to QA it wasn’t a full test of a production upgrade. This time, my first test was with all the data and that was very cool.
The main example of how this helped is that we had a lot of data in the SYS.WRI$_OPTSTAT_HISTGRM_HISTORY and SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY tables in production and this made the first upgrade of its clone take a long time. After two or three attempts at other ways to speed things up, I ended up applying patch 12683802 on an 11.1.0.7 Oracle home and this allowed me to truncate these two tables.
Delphix helped me here because I had an unused 11.1.0.7 Oracle home on a different host from the one I was doing the upgrade on. I didn’t want to apply a patch on the upgrade host because there were three other databases using the home and I didn’t want to bring them down or patch them. Delphix let me move the VDB that I was upgrading over to the host that had the unused home. Then I applied the patch there and ran the truncate using the dbms_stats.purge_stats(dbms_stats.purge_all) procedure call that the patch enabled.
Then I moved the VDB back to the host where I intended to do the upgrade, which already had the fully patched 11.2.0.4 binaries installed, and ran the upgrade there. Pretty cool. I think I did the most recent upgrade in about 3.5 hours with the empty OPTSTAT tables.
By the way, doing an upgrade within Delphix is easy. You just bring the VDB up on the old oracle home, do the upgrade as you normally would include all the shutdown and startup commands, and then within the Delphix GUI you let Delphix know the VDB is now on a new Oracle home by shutting it down, choosing the new home, and bringing it back up. Piece of cake.
Bobby
I ran across a stackoverflow question and it gave me an idea for a simpler use of Python to graph some Oracle database performance information. I looked at my PythonDBAGraphs scripts and I’m not sure that it is worth modifying them to try to simplify those scripts since I like what they do. But they may make people think that Python scripts to graph Oracle performance data are difficult to write. But, I think if someone just wants to put together some graphs using Python, Matplotlib, and cx_Oracle they could do it more simply than I have in my PythonDBAGraphs scripts and it still could be useful.
Here is an example that looks at db file sequential read waits and graphs the number of waits per interval and the average wait time in microseconds:
import cx_Oracle
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
connect_string = "MYUSER/MYPASSWORD@MYDATABASE"
con = cx_Oracle.connect(connect_string)
cur = con.cursor()
query="""
select sn.END_INTERVAL_TIME,
(after.total_waits-before.total_waits) "number of waits",
(after.time_waited_micro-before.time_waited_micro)/
(after.total_waits-before.total_waits) "ave microseconds"
from
DBA_HIST_SYSTEM_EVENT before,
DBA_HIST_SYSTEM_EVENT after,
DBA_HIST_SNAPSHOT sn
where before.event_name='db file sequential read' and
after.event_name=before.event_name and
after.snap_id=before.snap_id+1 and
after.instance_number=1 and
before.instance_number=after.instance_number and
after.snap_id=sn.snap_id and
after.instance_number=sn.instance_number and
(after.total_waits-before.total_waits) > 0
order by after.snap_id
"""
cur.execute(query)
datetimes = []
numwaits = []
avgmicros = []
for result in cur:
datetimes.append(result[0])
numwaits.append(result[1])
avgmicros.append(result[2])
cur.close()
con.close()
title="db file sequential read waits"
fig = plt.figure(title)
ax = plt.axes()
plt.plot(datetimes,numwaits,'r')
plt.plot(datetimes,avgmicros,'b')
# Format X axis dates
fig.autofmt_xdate()
ax.fmt_xdata = mdates.DateFormatter('%m/%d/%Y %H:%M')
datetimefmt = mdates.DateFormatter("%m/%d/%Y")
ax.xaxis.set_major_formatter(datetimefmt)
# Title and axes labels
plt.title(title)
plt.xlabel("Date and time")
plt.ylabel("num waits and average wait time")
# Legend
plt.legend(["Number of waits","Average wait time in microseconds"],
loc='upper left')
plt.show()
The graph it produces is usable without a lot of time spent formatting it in a non-standard way:
It is a short 68 line script and you just need matplotlib and cx_Oracle to run it. I’ve tested this with Python 2.
Bobby
I have experimented with the Rosetta Code website as a way to learn more about computer technology. I have mentioned my exploration of the edX and OCW web sites in earlier posts so I thought I would mention Rosetta Code or RC as another way to learn.
The RC site displays code in a variety of programming languages so that you can compare the languages. They define certain tasks and people post code that performs the tasks. For example, the task could be to write a simple Hello World program that writes Hello World to the screen. The site shows how to do this in Java, Python, C, and others. At this moment there are 354 implementations of Hello World on the site. I added this simple one for Oracle’s version of SQL: My Hello World
But, I can’t help wondering if this is just a fun game or is it really educational? I just finished a task this morning that was interesting to me. It is a draft task which means that people have not yet written enough example programs for the site managers to publish it as an official task. But anyone can see it. I’ve written the Python and PL/SQL examples. This 4 squares problem is just a puzzle or game but it was interesting to think about solving it in a declarative way with SQL and not a procedural way with Python or any other regular language. Maybe there is value in looking at these tasks and thinking about how SQL and relational database thinking could be applied?
Anyway, I just thought I would document what I have done with the RC site. Be careful because once you start working up example code for the site it can become addictive! I am still thinking about what the real value of the site is but it has helped me exercise my programming muscles and think a bit about SQL. Check it out if you think it has value.
Bobby
The next Arizona Oracle User Group meeting is January 26th in Scottsdale. Signup on this meetup link.
Copied from invitation:
When: January 26, 2017 (Thursday)
12:30 pm – 4:00 pm
Where: Republic Services
1st Floor Training Rooms
14400 N 87th St (AZ101 & Raintree)
Scottsdale, AZ
Agenda:
12:30 – 1:00 Registration and Pizza
1:00-1:10 Welcome
1:10-2:10 Presentations
Room 1 Biju Thomas – OneNeck IT Solutions
(Oracle ACE Director)
“Oracle Database 12c New Features for 11gR2 DBA”
Room 2 Charles Kim – Viscosity North America
(Oracle ACE Director)
“Bullet Proof Your Data Guard Environment”
2:10-2:25 Break – Coffee & Cookies
2:25-3:25 Presentations
Room 1 Biju Thomas – OneNeck IT Solutions
(Oracle ACE Director)
“Introduction to Oracle Databases in the Cloud”
Room 2 Jerry Ward – Viscosity North America
“Building Faceted Search Navigation in APEX
with Oracle JET and PL/SQL Pipelines”
3:25-3:30 Wrap Up and Closing
Before I left for vacation I noticed this blog post about setting up a network on VirtualBox: “CREATING A TEST LAB USING VIRTUALBOX / NAT NETWORKING”(DOES NOT EXIST). Now that I am back in the office I thought I would try to apply some of the things that I learned from the blog post to my existing VirtualBox network. I’m pretty happy with the results so I thought I would document my experience here.
My situation differed slightly from the situation in the original post:
I was cautious at first about trying the things recommended in the post because I didn’t want to mess up my existing VMs. These are all just test environments but it took some work to set them up. But, I wasn’t happy with the way the network was setup on my VMs so that motivated me to try something different. My biggest problem with my network was that I couldn’t SSH into my VMs when I worked from home using my work VPN connection.
After re-configuring my network I now have a static ip address for each VM within its own NAT network and I have port forwarding setup so that I can SSH into each one even when I’m working from home using VPN into my company’s network. I also have port forwarding so that I can login to an Oracle database on one of the VMs from my laptop even when using VPN.
The original blog post has most of the details but I thought that I would document a few minor differences that I came across to be helpful.
Default network name was NatNetwork instead of LocalNat.
In port forwarding I forwarded a port to port 1521 on the VM that has an Oracle database. I had to disable and stop the firewall on that VM so that I could connect to the Oracle database.
I went to /etc/sysconfig/network-scripts and looked at the configuration file and the format wasn’t the same as in the original post. This is probably because it is OEL 7.3 instead of CentOs 6. Instead of editing the file manually I ended up using the graphical network configuration app so that I knew I would edit the configuration correctly.
Later, after I wrote the first draft of this blog post I realized that I wanted to set up the network so that it automatically appends a domain name for our internal systems. I.e. if my standard domain is mycompany.com and I want to connect to mymachine.mycompany.com it would be nice to just connect to mymachine without fully qualifying the name with the domain. I ended up manually editing the file /etc/sysconfig/network-scripts/ifcfg-enp0s3 after googling around on the Internet to see how to add this. I added a DOMAIN line like this:
DOMAIN=”mydomain1.com mydomain2.com”
I created two text files to keep track of two things about my network configuration:
I had to get the DNS server ip addresses from my laptop’s network settings.
Anyway, I don’t have much to add to the original post except just to point out these slight differences and my experience. It was super helpful to switch to this NAT network with port forwarding approach.
Bobby
P.S. After writing the first draft of this blog post I did some more testing. There is one thing about this configuration that I don’t like. It seems that anyone on my company’s network can SSH into one of my VMs if they use the forwarded port number and my laptop’s IP address. I tried this from one of our Unix servers and it worked disturbingly well, even over the VPN. I think that the original blog post wanted this behavior but I was just trying to connect from my laptop to my VMs when I was using VPN. Still, it might come in handy to SSH into my VMs from other computers on our network. At least it isn’t opening up every port on my VM – only the ones I have set up with port forwarding. Also, I usually only bring these VMs up for short periods of time so they wont be available for people to hack into except for short unpredictable intervals and when I’m actively using them.
Delphix is sponsoring a user webinar on Wednesday from 10 to 11 am California time. I and a couple of other technical Delphix users will take part in a panel discussion. The webinar will give you a good chance to hear about other users’ experience with Delphix and a chance to ask questions. Like any good user meeting it will not be a non-technical sales pitch but it will instead focus on users sharing helpful technical information with other Delphix users.
Noted Oracle expert and Delphix employee Kellyn Pot’Vin-Gorman will also be there facilitating the discussion.
Here is the link to sign up for the free webinar: https://www.delphix.com/resources/webinar/delphix-users-panel(DOES NOT EXIST)
Be there or be square! 
Bobby
P.S. Here is a Delphix blog post with answers to questions from the webinar and a recording: url
I’m on call this week. Here are the steps that I took to speed up a query today.
First I got an AWR report and found the top query. Also, someone from support told me to look at November 11 before the latest release and I found a similar top query.
I got a plan for both the new and old top queries – they both use the same plan which makes me think that the two queries are similar.
I looked at the top segments on the AWR report and found a particular table at the top of the logical reads. An index of that table was like number 5 on that list.
I looked at the columns of the table’s index to see how many distinct values there were. None of the three columns had more than 300 distinct values so they were not very selective. I noticed that there was a unique index on the table and the first column of that index had millions of distinct values.
I extracted some sample bind variable values for the query and find that the second bind variable was null or something like that. But, the index we were using included this second variable.
In looked at the bind variables and found that the first column from the unique index was part of the join conditions in the query. (The query had like 20 joins).
Then I extracted the query text and replaced the bind variables with literals to see how it would run. It used the unique index. I used hints to force the original index and compared to running with the unique index. It ran about 30 times faster with the unique index. I ran a few times to make sure it was all cached.
Then I tried to use SQLT’s coe_xfr_sql_profile.sql to force the plan that used the unique index but got an error. Had to download the latest version of SQLT to get it to work.
Now, on average, the query seems to run about 1000 times faster.
It is a delivered vendor package so it was nice to find the better plan and go behind the scenes to fix it. But, if another release comes out and changes this sql to a new sql_id we will have to create a new profile. It’s not perfect but its a good quick fix for my on call.
Bobby
I needed to write a new script that was running on a Red Hat Linux 6 virtual machine and that would connect to databases using SQL*Plus. I was going to write a bash shell script but decided to use Python instead to see if I could do it using the Python that came with this version of Linux. I wont paste the entire script here but the key was to run SQL*Plus from Python instead of a shell script. Here is a simple example showing how I did it:
| import subprocess | |
| """ | |
| Example of running a sqlplus script from Python. | |
| Works with Python 3 and 2. | |
| """ | |
| def run_sqlplus(sqlplus_script): | |
| """ | |
| Run a sql command or group of commands against | |
| a database using sqlplus. | |
| """ | |
| p = subprocess.Popen(['sqlplus','/nolog'],stdin=subprocess.PIPE, | |
| stdout=subprocess.PIPE,stderr=subprocess.PIPE) | |
| (stdout,stderr) = p.communicate(sqlplus_script.encode('utf-8')) | |
| stdout_lines = stdout.decode('utf-8').split("\n") | |
| return stdout_lines | |
| sqlplus_script=""" | |
| connect test/test@dbatest | |
| select * from dual; | |
| exit | |
| """ | |
| sqlplus_output = run_sqlplus(sqlplus_script) | |
| for line in sqlplus_output: | |
| print(line) | |
Here is the output:
$ python test.py SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 4 15:44:30 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. SQL> SQL> Connected. SQL> D - X SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
The function run_sqlplus takes a multi-line string as input. This is the text of a SQL*Plus script. It runs sqlplus /nolog to get a SQL*Plus prompt and then passes the strings in as the lines typed at the prompt. The function returns SQL*Plus’s output as a list of strings.
Anyway, this is just an example. I built a real script using these concepts. In this case we are using Python 2.6.6 without cx_Oracle installed so I couldn’t connect directly to an Oracle database. Instead I just ran SQL*Plus from Python.
Bobby
P.S. I edited this on 2/12/2018 to make it work with both Python 2 and 3. I added the encode and decode and made the print statement use parentheses.
P.P.S. Here is similar code on GitHub: runutils.py
I created a new GitHub public repository with my SQL scripts. Here is the URL:
https://github.com/bobbydurrett/OracleDatabaseTuningSQL
I’ve experimented with GitHub for my Python graphing scripts but wasn’t sure about putting the SQL out there. I don’t really have any comments in the SQL scripts. But, I have mentioned many of the scripts in blog posts so those posts form a type of documentation. Anyway, it is there so people can see it. Also, I get the benefit of using Git to version my scripts and GitHub serves as a backup of my repository.
Also, I have a pile of scripts in a directory on my laptop but I have my scripts mixed in with those that others have written. I’m pretty sure that the repository only has my stuff in it but if someone finds something that isn’t mine let me know and I’ll take it out. I don’t want to take credit for other people’s work. But, the point is to share the things that I have done with the community so that others can benefit just as I benefit from the Oracle community. I’m not selling anything and if there is someone else’s stuff in there it isn’t like I’m making money from it.
Like anything on the web use at your own risk. The repository contains scripts that I get a lot of benefits from but I make no guarantees. Try any script you get from the internet on some test system first and try to understand the script before you even run it there.
I hope that my new SQL repository helps people in their Oracle work.
Bobby
by