Archive

Archive for the ‘Uncategorized’ Category

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 and systematic. It’s laid out inDan Tow’s book SQL Tuning.

The method is tedious as it requires a lot of manual work to draw join trees, identify constraints and relationships, manual decomposition and execution of every 2 table join in the statement. It can add up to a lot of work but it is systematic and dependable.

Cool thing is it can all be done automatically with a tool called DB Optimizer that now (as I look today) only cost about $400.

If you cost your company $50/hour then in 8 hours of saved work it’s paid for its self. In my experience as a DBA I have serveral SQL a year that take me over a day to optimize manually but that I can get done in a few minutes with DB Optimizer.  Thus with just one hard SQL the tool has paid for itself. The DB Optimizer analysis might run for a couple hours, but afterwords with the data it collects and presents, I can find better tuning path in minutes if it exists.

 

Here is previous blog post that gives some an overview

Here is a video that explains the method. (Same presentation in a different video.)

Slides from the videos.

Here is Dan Tow’s book SQL Tuning that originally laid out the method.

Here is post by Jonathan Lewis demonstrating the method.

Pick up a copy. I think it’s super cool and interested in feedback on the your experiences.

 

sql9

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.

 

Screen Shot 2017-09-29 at 11.10.53 AM

Screen Shot 2017-09-29 at 11.11.04 AM

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 for a database workload. Two questions arise

  • What tool can benchmark the I/O?
  • What is a typical database I/O workload?

For the tool, the clear answer seems to be fio which not only is quite flexible but has an active community and updates and was started by the renown Linux I/O master Jens Axboe and still is actively maintained by him.

Now for database workloads there are 4 types of I/O

  1. Random single block reads (typically lookups by index)
  2. Large multi block reads (typically full table scans)
  3. Small sequential I/Os to transaction log files
  4. Large sequential  I/Os totransaction log files (generally when commits are infrequent and change rate is high)

Now in a database, all of these can be done concurrently by multiple processes concurrently and thus we need to benchmark for concurrency as well.

Thus the matrix is the 4 types of I/Os to be tested by different number of concurrent users.

Now fio doesn’t come with such prepackaged I/O benchmarks, so I created a script fio.sh to run configurable database I/O benchmarks.

The code and examples are on github at

Prerequisite is to have a binary of the fio command ready.

Download the source at

and compile it.

Here is an example running the script (-b  followed by full path to fio binary , -w followed by the directory to create a temporary large file for I/O testing)

  $  fio.sh -b `pwd`/fio.opensolaris -w /domain0/fiotest   

configuration: 
    binary=/home/oracle/fiodir/fio.opensolaris
    work directory=/domain0/fiotest   
    output directory=/home/oracle/fiodir
    tests=readrand read write
    direct=1
    seconds=60
    megabytes=65536
    custom users=-1
    custom blocksize=-1
    recordsize =8k
    filename (blank if multiple files)="filename=fiodata"
    size per file of multiple files=""
proceed?
y
CREATE 65536 MB file /home/oracle/fiodir/workdir/fiodata

creating 10 MB  seed file of random data

20480+0 records in
20480+0 records out
10485760 bytes (10 MB) copied, 1.17997 seconds, 8.9 MB/s

creating 65536 MB of random data on 

............................................................. 64926 MB remaining  300 MB/s 216 seconds left
............................................................. 64316 MB remaining  600 MB/s 107 seconds left
............................................................. 63706 MB remaining  300 MB/s 212 seconds left
............................................................. 63096 MB remaining  300 MB/s 210 seconds left
............................................................. 62486 MB remaining  200 MB/s 312 seconds left
............................................................. 61876 MB remaining  100 MB/s 618 seconds left
............................................................. 61266 MB remaining  35 MB/s 1750 seconds left
............................................................. 60656 MB remaining  300 MB/s 202 seconds left
............................................................. 60046 MB remaining  150 MB/s 400 seconds left
............................................................. 59436 MB remaining  75 MB/s 792 seconds left
............................................................. 58826 MB remaining  75 MB/s 784 seconds left
............................................................. 58216 MB remaining  85 MB/s 684 seconds left
............................................................. 57606 MB remaining  75 MB/s 768 seconds left
............................................................. 56996 MB remaining  75 MB/s 759 seconds left
............................................................. 56386 MB remaining  85 MB/s 663 seconds left

(more output)

test  users size         MB       ms  IOPS    50us   1ms   4ms  10ms  20ms  50ms   .1s    1s    2s   2s+
    read  1   8K r   28.299    0.271  3622           99     0     0     0
    read  1  32K r   56.731    0.546  1815           97     1     1     0     0           0
    read  1 128K r   78.634    1.585   629           26    68     3     1     0           0
    read  1   1M r   91.763   10.890    91                 14    61    14     8     0     0
    read  8   1M r   50.784  156.160    50                              3    25    31    38     2
    read 16   1M r   52.895  296.290    52                              2    24    23    38    11
    read 32   1M r   55.120  551.610    55                              0    13    20    34    30
    read 64   1M r   58.072 1051.970    58                                    3     6    23    66     0
randread  1   8K r    0.176   44.370    22      0     1     5     2    15    42    20    10
randread  8   8K r    2.763   22.558   353            0     2    27    30    30     6     1
randread 16   8K r    3.284   37.708   420            0     2    23    28    27    11     6
randread 32   8K r    3.393   73.070   434                  1    20    24    25    12    15
randread 64   8K r    3.734  131.950   478                  1    17    16    18    11    33
   write  1   1K w    2.588    0.373  2650           98     1     0     0     0
   write  1   8K w   26.713    0.289  3419           99     0     0     0     0
   write  1 128K w   11.952   10.451    95           52    12    16     7    10     0     0           0
   write  4   1K w    6.684    0.581  6844           90     9     0     0     0     0
   write  4   8K w   15.513    2.003  1985           68    18    10     1     0     0     0
   write  4 128K w   34.005   14.647   272            0    34    13    25    22     3     0
   write 16   1K w    7.939    1.711  8130           45    52     0     0     0     0     0     0
   write 16   8K w   10.235   12.177  1310            5    42    27    15     5     2     0     0
   write 16 128K w   13.212  150.080   105                  0     0     3    10    55    26     0     2

What we see is

  • test – the test we are running either randread, write or read
  • users – number of concurrent users
  • size – size of I/O requests. Databases typically request 8kb  at a time
  • MB – MB per second throughput
  • ms – average latency
  • min – min latency(not shown here)
  • max – max latency (not shown here)
  • std – standard deviation on latency (not shown here)
  • IOPS – I/O operations per second
  • 50us 1ms 4ms 10ms 20ms 50ms .1s 1s 2s 2s+ – histogram of number of I/Os faster than heading value

 

This can be useful to just run on your laptop.

This summer I bought a used Mac laptop that had something called Hybrid SSD. I had been using a Mac with preinstalled SSD disks and thought the Hybrid would be similar response wise, but once I started using it, there was something cleary wrong, but before sending it back I wanted some empirical proof, so I ran fio.sh.

Here is the comparison

SSD - came with the Mac

test  	users size      MB       ms      min      max      std    IOPS 
randread    1   8K  32.684    0.234    0.002    9.393    0.144   4183,
randread    8   8K 240.703    0.257    0.001    2.516    0.137  30810,
randread   16   8K 372.503    0.333    0.001    1.994    0.185  47680,
randread   32   8K 478.863    0.520    0.001    5.281    0.294  61294,
randread   64   8K 476.948    1.045    0.001   11.564    0.582  61049,

SSHD - hybrid SSD installed after market

test  	users size      MB       ms      min      max      std    IOPS 
randread    1   8K   0.533   14.608    0.005  138.783    8.989     68,
randread    8   8K   0.767   80.769    0.035  256.965   53.891     98,
randread   16   8K   0.801  152.982    0.012  331.538   63.256    102,
randread   32   8K   0.810  298.122    0.015  519.073   79.781    103,
randread   64   8K   0.796  590.696    0.030  808.146  143.490    101,

(full list of SSD vs HSSD on my Macs at https://github.com/khailey/fio_scripts/blob/master/macssd)

The hybrid is atrocious compared to the SSD.

The random read is 14.6 ms which is the speed of a slow HDD.
A 7K RPM HDD should respond at under 10ms.
A 15K RPM HDD should respond at around 6ms.
An SSD on a 2 year old Mac responds at 0.23 ms.

Its nice to just have an easy to run script to test out storage.
Here is my linux box

test  users size         MB       ms      min      max      std    IOPS 
                                            
randread  1   8K r   14.417    0.517    0.005    8.922    0.382    1845
randread  8   8K r   26.497    2.355    0.004   12.668    0.790    3391
randread 16   8K r   24.631    5.069    0.004   15.168    1.080    3152
randread 32   8K r   24.726   10.101    0.005   32.042    2.124    3164
randread 64   8K r   24.899   20.051    0.005   37.782    4.171    3187

On my Linux desktop you can see how the MB/sec throughput maxes out about 26 MB/sec and after that latency just goes down proportionally as we add more concurrency.

The github repository all has r scripts to visualize the data (see the readme in github for details on how to generate the graphics)

 

skytap1_randread_bs_8K

Here is an explanation of the graphics.

graph_key

 

There are a number of factors that are important when benchmarking I/O such as whether using Direct I/O or not, what the size of caching is on the host running fio, what the back end storage  cache size is, what the size is of the file used to test I/O, how that file is initialized other with 0’s, or  patterned data, or random data, whether the file system compresses or not, etc. Check out this blog post for some anomalies and surprises: http://datavirtualizer.com/lies-damned-lies-and-io-statistics/

Uncategorized

Postgres monitoring script pgmon.sh

March 9th, 2017

Screen Shot 2017-03-09 at 8.13.35 AM

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 |       23600 |         1068 |        2316 |           0 
  16 |   146522 |         0 |      1497604 |       48647 |         2352 |        4599 |        2462 
   2 |   114046 |         0 |      1894329 |       46341 |         3822 |        3852 |        3066 
   2 |   146728 |         0 |      2239014 |       61420 |         3822 |        5668 |        3150 
  16 |    70446 |         0 |       945021 |       49284 |         2016 |         686 |         757 
  13 |   264149 |         0 |      1146816 |       53816 |         1638 |        2176 |        1852 
  15 |    54324 |         0 |       226542 |       19078 |          840 |         396 |          31 
  13 |  1170087 |         0 |      2301442 |      186967 |         2058 |        4276 |        1340 
   3 |  1036439 |         0 |      3411396 |       57392 |         4158 |        5041 |        3605 
   1 |   135927 |         0 |      1931473 |       90238 |         4788 |        5077 |        3654 
   5 |    92975 |         0 |      1427641 |       49175 |         2772 |        2812 |        1764 
  16 |    73695 |         0 |      1001290 |       35585 |         1806 |        1721 |        1915 
  14 |    65117 |         0 |       242383 |       22150 |          420 |         530 |         511 
   4 |   111906 |         0 |      1593090 |       49570 |         2982 |        4718 |        3086 

When there are databases for which I’m responsible, I like to have powerful performance analysis database tools. In the Oracle world I use tools such as Lab128, DB Optimizer and OEM. There a number of other tools that look good from companies such Quest, Solarwinds and Orachrome.  Often though when I’m dealing with a new situation, a new database or a database that’s temporary, then I just want a quick and easy way to see basic stats easily and quickly. For Oracle I have a number of easy monitoring scripts.

For monitoring databases other than Oracle, I have less experience. I did do work on SQL Server, Sybase and DB2 when working on the development of  DB Optimizer but monitoring popular open source databases such as MySQL and Postgres is relatively new to me.

Right now I’m looking into Postgres.

For Postgres I wanted some quick and easy way to monitor in realtime various stats to start iterating and prototyping the type of data I wanted to look at, so I  put together a shell script. The advantage of a shell script is it should work most anywhere and the only requirement is psql which should be readily available in a PosgreSQL environment.

I took a script from my Oracle experiences, oramon.sh, and retrofitted it for Postgres. It is a script that sets up a named pipe  then has psql (or for Oracle , SQL*Plus) read from that named pipe. The script procedurally feeds SQL statements into the named pipe. The output from psql goes to files and the script manages the output to the screen. The script is a little less optimal for PostgreSQL than Oracle as I had to use a temporary table to store variable values. Using psql has variables but they look to be static can not modifiable (seem to act like defines).

The script pulls out some basic stats and outputs them every 5 seconds on the command line, sort of like vmstat. The first stat is AAS or average active sessions which is the count of sessions actively running queries, followed by a few basic database stats.

 

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

jmeter1

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 the machine running PostgreSQL  and “database” is the name of the database you are using in that instance and “5432” is the port to connect over.

Screen Shot 2017-02-27 at 2.56.19 PM

 

You have to make TWO connection string changes. One is as shown above for “Setup Connection” and the other is 2 rows below “JDBC Connection Configuration” where you make the same changes.

Then just hit the green triangle to start the load.

The load will create a table named authors, a sequence called serial and an index called author_id if these don’t already exist.

It will then run inserts, deletes, updates and selects on this table.

 

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

Screen Shot 2017-02-11 at 9.19.43 PM

 

And here are some of my iPhone shots over the last few months with some Instagram filtering mixed in :)

Screen Shot 2017-02-10 at 8.17.09 AM Screen Shot 2017-02-10 at 8.15.09 AM

Screen Shot 2017-02-10 at 8.15.01 AMScreen Shot 2017-02-10 at 8.16.58 AM Screen Shot 2017-02-10 at 8.16.52 AM Screen Shot 2017-02-10 at 8.16.21 AM Screen Shot 2017-02-10 at 8.15.54 AM Screen Shot 2017-02-10 at 8.14.32 AM Screen Shot 2017-02-10 at 8.14.24 AM Screen Shot 2017-02-10 at 8.14.17 AM Screen Shot 2017-02-10 at 8.14.09 AM Screen Shot 2017-02-10 at 8.13.59 AM Screen Shot 2017-02-10 at 8.13.41 AM Screen Shot 2017-02-10 at 8.13.32 AM Screen Shot 2017-02-10 at 8.13.18 AM

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

 screen-shot-2017-01-06-at-12-01-10-pm

screen-shot-2017-01-06-at-12-00-50-pm

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 of a regular sql statement and I think that threw me off.

The error sounded to me like I needed to define an input or output variable.

I tried both of those, until finally I saw its not the input or output variable but the name of the JDBC connection pool that was missing

 

screen-shot-2017-01-06-at-11-51-15-am

 

 

This was the closest google hit I could find

http://stackoverflow.com/questions/36741446/add-summary-report-results-to-database-in-jmeter

In googling around, I did turn up

http://stackoverflow.com/questions/36741446/add-summary-report-results-to-database-in-jmeter

which sounds cool – loading up the results into a table after the Jmeter run

And thanks to Ivan Rancati who answered this same question of mine 4 weeks ago on user@jmeter.apache.org

 

Followup

Another problem I had today was “”The column index is out of range”

I was doing a

“INSERT INTO authors (id,name,email) VALUES(nextval(‘serial’),’Priya’,’p@gmail.com’);”

The JDBC Request worked when it was just

“INSERT INTO authors (id,name,email) VALUES(2,’Priya’,’p@gmail.com’);”

Turns out I had set “Parameter values” and “Parameter types”. When I took them out it worked. What confuses me, and what I’ll have to look back into, is the whole reason I added the  Parameters was because the nextval wasn’t work. Forgot what that original error was.

Uncategorized

jmeter – getting started

January 5th, 2017

jmeter

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 considerably as I start to use jmeter more and more.

I’m installing it on a mac and using RDS instances.

installing jmeter

brew install jmeter

see

Database Driver download (I’m using the following)

Created a test table

  • CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));
  • INSERT INTO authors (id,name,email) VALUES(2,’foo’,’foo@foo.com’);

Startup up jmeter

    $ which jmeter 
      /usr/local/bin/jmeter 
    $ jmeter 
      Writing log file to: /Users/kylelf/jmeter.log

brings up a graphic window

screen-shot-2017-01-03-at-2-40-06-pm

 

Add your path to the database drivers at the bottom of the screen by clicking “Browse …” and going to your driver file and selecting it.

screen-shot-2017-01-04-at-1-48-19-pm

 

We are going to create the following (minimum setup for an example)

  1. create test: Thread group named ‘Database Users’
  2. db connection: Config element of type JDBC Connection Configuration
  3. query to run: Sampler of type JDBC Request
  4. results output: Listener of type “View Results Tree”

1. First to do is add a “Thread Group”

(right click on “Test Plan”)

Define how many connections to make and how many loops to make of the workload

thread_group

 

screen-shot-2017-01-03-at-2-45-25-pm

interesting parts here are

  • “Number of Threads (users)” : can set the number of database connections
  • “Loop Count ” : can set the number of iterations of the test query

2. Add a Config Element of type JDBC Connection Configuration

 

Define what database to connect to

screen-shot-2017-01-03-at-2-46-28-pm

For Oracle make sure and change “Select 1″ to “Select 1 from dual” or you’ll get non-obvious error.

Name the pool. For example I call mine “orapool”

and fill out all the connection information

  • Database machine, port and SID of form: jdbc:oracle:thin:@yourmachine:1521:ORCL
  • JDBC Driver Class: oracle.jdbc.OracleDriver
  • Username
  • Password

screen-shot-2017-01-03-at-3-03-46-pm

3. Sampler of type JDBC Request

Define a SQL statement to run

screen-shot-2017-01-03-at-3-35-47-pm

screen-shot-2017-01-03-at-3-39-24-pm

 

Make sure and include the name of the thread pool created above. In my case it’s called “orapool”

add a SQL statement to run

screen-shot-2017-01-03-at-3-53-39-pm

4. Listener of type “View Results Tree”

create a widget to see the output

screen-shot-2017-01-03-at-3-55-36-pm

Final setup looks like

run your load and look at the output

screen-shot-2017-01-03-at-4-42-49-pmNow you hit the run button, the green triangle.

Then click on “View Results Tree” to see the output.

screen-shot-2017-01-03-at-4-02-54-pm

I clicked on “View Results Tree” and then clicked on “JDBC Request” in red.

Then I’ll see some output. I choose “Response data” because it’s a bit more succinct and see the error. In this case there is an extra space ” ” at the end of “oracle.jdbc.OracleDriver “. Jmeter is sensitive to spaces. I’ve gotten a lot of errors because of spaces in fields such as variable names and such.

Correcting that it runs

screen-shot-2017-01-03-at-4-52-52-pm

 

All the setup might sound like a bit of a pain but once it’s set up, it’s easy to click through and make modifications.

All the setup is available in a text .jmx file and if you are brave you can edit directly there.

Here is the above example .jmx file on github.

Look for “my” and replace

  • myinstance.rds.amazonaws.com
  • myuser
  • mypassword

The above example is more or less pointless – sort of a “Hello World”.

From here though you can increase the number of threads, increase the number of loops, add more SQL statements.

Jmeter allows a lot of customization so you can add .cvs files for input values, capture output values into variables and use them in input values, have different types of loops with different users running concurrently etc.

More to come.

Christian Antognini gave a presentation at Oaktable World SF in Sept 2016. He was gracious enough to send along his functionally rich .jmx file and I’ll blog on that soon.

 

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

  1. Start at the table with the strongest predicate filter (the filter that returns the fewest % of the table)
  2. join down to children (if multiple children join to child with strongest predicate filter)
  3. If you can’t join to children, join up to parent

The basics are pretty simple and powerful. Of course there are many cases that get more complex and Dan goes into these complex cases in his book.

What about indexes? Well the method will point out joins that should happen and if those joins are missing indexes then it indicates that indexes should be created.

What about join type? I generally leave this to the optimizer. The join type can be important but generally order of joins and indexes are more important. I look at join type as the final optimization.

Let’s take an example query:

SELECT COUNT (*)
FROM   a,
       b,
       c
WHERE
       b.val2 = 100 AND
       a.val1 = b.id AND
       b.val1 = c.id;

There are  indexes on b.id and c.id.  Diagramming the query in DB Optimizer gives

sql1

The red lines with crows feet mean that as far as the definitions go, the relations could be many to many.

Question is “what is the optimal execution path for this query?”

One of  the best execution plans is to

  1. start at the most selective filter table
  2. join to children  if possible
  3. else join to parent

There is one filter in the diagram, represented by the green F on table B. Table B has a filter criteria in the query “b.val2=100″.

Ok, table B is where we start the query. Now where do we go from B? Who is the parent and who is the child? It’s not defined in the constraints nor indexes on these tables so it’s hard for us to know. Guess what ? It’s also hard for Oracle to figure it out. Well, what does Oracle decide to do? This is where the cool part of DB Optimizer  comes in.

The super cool thing with DB Optimizer is we can overlay the diagram with the actual execution path (I think this is awesome)

sql2

For the digram we can see Oracle starts with B and joins to A. The result if this is joined to C. Is this the optimal path?

Well, let’s keep the same indexes and just add some constraints:

alter table c add constraint c_pk_con unique (id);
alter table b add constraint b_pk_con unique (id);

Now let’s diagram the query with DB Optimizer:

sql3

We can now see who the parent and child is, so we can determine the optimal query path which is to start at B, the only filter and  join to the child C then to the parent A.  Now what does Oracle do with the added constraint info:

sql4

Guess what? The execution plan has now changed with the addition of constraints and now Oracle’s execution path goes from a suboptimal plan to  the optimal path. Moral of the story is to make sure and define constraint information because it helps the optimizer, but what I wanted to show here was the explain plan overlay on the diagram which makes comparing execution plans much easier. Putting the queries VST diagrams side by side along with the overlay of execution path we can clearly and quickly see the differences:

sql5sql6

I plan to blog more about this awesome feature. It’s really cool.

Here is an example from an article by Jonathan Lewis

http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

The query Jonathan discusses is

SELECT order_line_data
FROM
         customers cus
         INNER JOIN
         orders ord
         ON ord.id_customer = cus.id
         INNER JOIN
         order_lines orl
         ON orl.id_order = ord.id
         INNER JOIN
         products prd1
         ON prd1.id = orl.id_product
         INNER JOIN
         suppliers sup1
         ON sup1.id = prd1.id_supplier
   WHERE
         cus.location = 'LONDON' AND
         ord.date_placed BETWEEN '04-JUN-10' AND '11-JUN-10' AND
         sup1.location = 'LEEDS' AND
    EXISTS (SELECT NULL
            FROM
                 alternatives alt
                 INNER JOIN
                 products prd2
                 ON prd2.id = alt.id_product_sub
                 INNER JOIN
                 suppliers sup2
                 ON sup2.id = prd2.id_supplier
           WHERE
                  alt.id_product = prd1.id AND
                  sup2.location != 'LEEDS')

which diagrammed looks like

sql7

There are multiple filters, so we need to know which one is the most selective to know where to start, so we ask DB Optimizer to display the statistics as well  (blue below a table is the filter %, green above is # of rows in table and numbers on join lines are rows returned by a join of just those two tables)

sql8

Now that we can determine a candidate for best optimization path, does Oracle take it?

sql9

Can you find the optimization error?

Dark green is where execution starts. There are two starts: one for the main query body and one for the subquery.

The red is where query execution ends.

PS a big part of this work is by the lead developer Matt Vegh. Many thanks to Matt for this awesome work.

 

PPS another example from Karl Arao

sql10

The dark green nodes are starts, so there are 4 separate starts. We can see how the result sets from each start are joined with each successive table join set. The red is the final step.

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 not wipe my iPhone 6 for 24  hours incase there were upgrade errors. They normally wipe the phone immediately.

The day was off to a good start. It only took about 10 minutes to get the phone and I had taken a full backup of my iPhone 6 the day before and thought I’d plug in , restore back up and wow, that would be easy.

I get back to my office, luckily just a couple blocks away. Plug it in, try to restore the backup and it asked me for a password. I’m like ‘rrr’! The day before when I had taken the backup, I saw that the “encrypt” checkbox was filled and thought about taking it off, but then thought, “well it’s probably more prudent to leave it on”. Of course the backup didn’t ask me to verify my password. It just took the backup.

screen-shot-2016-12-22-at-10-52-13-am

Now in order to use the backup, I had to know the password. The day before when it took the backup, I was thinking my computer had it cached, or else why didn’t it ask for the password when I took the backup?

So now I had to figure out what the password it was. I tried all conceivable passwords I could think of. My name space of passwords is limited to about 3 core, 10 common  and 20 rare passwords. I tried them all. It wasn’t my Apple ID password which it should have been. It wasn’t my iPhone 6 number code. It wasn’t any of the 20 passwords I’ve used over the past several years.

OK, fine. I’ll go back get my iPhone 6 and take a new un-encrypted backup!

I go back to the store. To their credit they gave me my old phone in a couple of minutes and it was back to the office.

I go to the backup interface, seen above, and try to un-check the “Encrypt iPhone backup”.

This is  where it gets fun.

*** No ***

you can’t uncheck the Encrypt!  What has been seen can never be unseen.

To uncheck the box you need the password. If I knew the password I wouldn’t be unchecking the box.

So now I  *** CAN’T *** take a backup (at least one that is usable)! Thanks a lot Apple. Are you serious?

It’s my  iPhone, my computer, I’m using everything on both and Apple won’t let me take a backup of the iPhone on the computer!

From Apple:

screen-shot-2016-12-22-at-10-47-24-am

Unbelievable. There is no way for me to backup my iPhone 6 so I can upgrade to my iPhone 7.

Are they serious ??

The work around is to backup to the iCloud which doesn’t need the password. Does that make sense? Can we say slow, insecure and inefficient?

Backing up my 64G  iPhone 6 to the iCloud is a recipe for a huge waste of time.

So I go through and blow everything away on my iPhone (so much for a good backup) until I’m down to about 6G and then backup to the iCloud. Of course, I have to buy more space on the iCloud to do this. rrrr

I bought more space, I backed up the iPhone, I went back to Verizon, switched phones again, back to the office, restored from iCloud and it worked. Of course I’m missing all the photos, music, books and apps I rarely used. Now time to put that stuff on back by hand.

Some parts of the upgrade are magic, but this part blows me away. Why? Why so much pain? Just to force me to buy some iCloud space for a day?

What a frustrating waste of time from a company that prides itself in easy powerful user interfaces.

First, why can’t you turn of encryption of backups once chosen?

Second why not use apple ID password ?

Third, why not alert the user every time they take an encrypted backup asking for password? No point in taking the encrypted backup if you don’t know what the password is.

 

Uncategorized