Best method for tuning sub-optimal execution plans

October 18th, 2017
How do you determine if the Oracle SQL optimizer has created a sub-optimal execution plan? re-run statistics and see what happens? wait for Oracle to fin other execution plans? What if neither method is helping? Do you read the execution plan? What do you look at? Differences in actual vs estimated? How successful is that? Look for full table scans?  Do you look at the 10053 trace? How much time and effort does that take?  What do you look at in the 10053 trace. Do you have a systematic methodology  that works in almost all cases? Well there is a method that is reliable ...


Uncategorized

Oaktable World 2017 @ Oracle Open World

September 29th, 2017
  http://www.oaktable.net/blog/oak-table-world-2017-oracle-open-world The Oak Table members will be discussing their latest technical obsessions and research on Monday and Tuesday, (Oct. 2nd and 3rd, 2017).  The truth is, folks-  The Oak Table experts are an AWESOME group, (if I don’t say so myself! :)) as we could have easily done another day of incredible sessions, but alas, two days is all we have available for this year’s event.  


Uncategorized

benchmarking your disks

March 17th, 2017
  While at Delphix, we did a lot of storage benchmarking. The I/O response times of Delphix depends, as one would logically imagine, heavily on the underlying disks. Sure Delphix can cache a lot ( with 1 TB of ram and 3x compression that's 3TB and that 3TB can be shared by 10 or a 100 copies being the equivalent to 30TB or 300TB of databases) but really there will always be important I/O coming from the storage subsystem. Now Delphix mainly runs databases loads, so the best test for storage that is hooked up to Delphix is to benchmark the storage I/O ...


Uncategorized

Postgres monitoring script pgmon.sh

March 9th, 2017
' photo by Thomas Hawk https://github.com/khailey/pgmon $ ./pgmon.sh Usage: pgmon.sh [username] [password] [host] <sid=postgres> <port=5432> <runtime=3600> $ ./pgmon.sh kyle kyle mymachine.com psql -t -h mymachine.com -p 5432 -U kyle postgres < /tmp/MONITOR/tmp/mymachine.com:postgres_collect.pipe & RUN_TIME=-1 COLLECT_LIST= FAST_SAMPLE=wts TARGET=mymachine.com:postgres DEBUG=0 Connected, starting collect at Wed Mar 8 12:05:12 PST 2017 starting stats collecting SET AAS| blks_hit | blks_read | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted 1 | 38281 | 1 | 628629 ...


Uncategorized

Example Jmeter workload for Postgres and Oracle

February 27th, 2017
pgload.jmx is JMX file you can load into Jmeter and run a substantial load on a Postgres database. Should work just as well on Oracle if you change the test SQL from "Select 1" to "select 1 from dual" Install jmeter on our machine . On my mac, I did brew install jmeter You will need the Postgres driver. I used postgresql-9.4-1202.jdbc4.jar from https://jdbc.postgresql.org/download.html Put it in the classpath in jmeter To use this file, save it as pgload.jmx and then open it up with Jmeter. Change the database URL with host, port, database name, and fill in your username and password. The database URL looks like jdbc:postgresql://machine.com:5432/database where "machine.com" is ...


Uncategorized

9th Circuit Court Ruling 3-0

February 12th, 2017
Little did I know this building that captured my visual attention and imagination so many times walking to work over the last 6 months would play a historic roll in the current political climate. Here is a picture of the US District Court House from recent articles   And here are some of my iPhone shots over the last few months with some Instagram filtering mixed in :)


Uncategorized

jmeter – Variable Name must not be null in JDBC Request

January 6th, 2017
So Jmeter seems super cool. I've only used it a little bit but it does seem a bit touchy about somethings (like spaces in input fields) and the errors are often less than obvious and I'm not finding that much out there on google for the errors. Today I ran into the error Variable Name must not be null in JDBC Request   and Googling it didn't turn up anything. I'm pretty sure I ran into this same error a few weeks ago when I was first starting with Jmeter, so blogging here to document it. I was trying something new - running a procedure - instead ...


Uncategorized

jmeter – getting started

January 5th, 2017
This blog post is just a start at documenting some of my experiences with jmeter. As far as load testing tools go, jmeter looks the most promising to me. It has an active community, supports many different databases and looks quite flexible as far as architecting different work loads goes. The flexibility of jmeter also makes it hard to use. One can use jmeter for many other things besides databases so the initial set up is a bit oblique and there look to be many paths to similar results. As such, my understand and method for doing things will probably change ...


Uncategorized

Graphics for SQL Optimization

January 4th, 2017
Dan Tow, in his book SQL Tuning, lays out a simple method of tuning SQL queries. The method is Draw a diagram of each table in the query with Children above Parents Draw join lines between each join (many-to-many, one-to-many) Mark each table with a predicate filter and calculate the amount of table filtered out Then to find a great optimal optimization path candidate Start at the table with the strongest predicate filter (the filter that returns the fewest % of the table) join down to children (if multiple children join to child with strongest predicate filter) If you can't join to children, join up to parent The ...


Uncategorized

Apple Upset – upgrading to iPhone 7

December 22nd, 2016
Upgrading is always stressful - be it a computer, an Oracle database or an iPhone. There's always a good chance for lost data and lost time dealing with complications. So yesterday I picked up a new iPhone 7 from Verizon. The pickup was seamless. I had signed up for an upgrade program when I got the iPhone 6, so now I just walked in, gave them my old iPhone 6 and they gave me an new iPhone 7. It's bit scary giving up my old phone before restoring to my new phone, but I had a backup AND I asked Verizon to please ...


Uncategorized