Kerry Osborne's Oracle Blog

Here’s a lovely Candy Striped pattern in an Explain Plan. Looks like the traditional Christmas candy canes. Just in time for the Holiday Season!

| 156 |     FAST DUAL                                                |                                |     1 |       |     2   (0)| 00:00:01 |
| 157 |      FAST DUAL                                               |                                |     1 |       |     2   (0)| 00:00:01 |
| 158 |       FAST DUAL                                              |                                |     1 |       |     2   (0)| 00:00:01 |
| 159 |        FAST DUAL                                             |                                |     1 |       |     2   (0)| 00:00:01 |
| 160 |         FAST DUAL                                            |                                |     1 |       |     2   (0)| 00:00:01 |
| 161 |          FAST DUAL                                           |                                |     1 |       |     2   (0)| 00:00:01 |
| 162 |           FAST DUAL                                          |                                |     1 |       |     2   (0)| 00:00:01 |
| 163 |            FAST DUAL                                         |                                |     1 |       |     2   (0)| 00:00:01 |
| 164 |             FAST DUAL                                        |                                |     1 |       |     2   (0)| 00:00:01 |
| 165 |              FAST DUAL                                       |                                |     1 |       |     2   (0)| 00:00:01 |
| 166 |               FAST DUAL                                      |                                |     1 |       |     2   (0)| 00:00:01 |
| 167 |                FAST DUAL                                     |                                |     1 |       |     2   (0)| 00:00:01 |
| 168 |                 FAST DUAL                                    |                                |     1 |       |     2   (0)| 00:00:01 |
| 169 |                  FAST DUAL                                   |                                |     1 |       |     2   (0)| 00:00:01 |
| 170 |                   FAST DUAL                                  |                                |     1 |       |     2   (0)| 00:00:01 |
| 171 |                    FAST DUAL                                 |                                |     1 |       |     2   (0)| 00:00:01 |
| 172 |                     FAST DUAL                                |                                |     1 |       |     2   (0)| 00:00:01 |
| 173 |                      FAST DUAL                               |                                |     1 |       |     2   (0)| 00:00:01 |
| 174 |                       FAST DUAL                              |                                |     1 |       |     2   (0)| 00:00:01 |
| 175 |                        FAST DUAL                             |                                |     1 |       |     2   (0)| 00:00:01 |
| 176 |                         FAST DUAL                            |                                |     1 |       |     2   (0)| 00:00:01 |
| 177 |                          FAST DUAL                           |                                |     1 |       |     2   (0)| 00:00:01 |
| 178 |                           FAST DUAL                          |                                |     1 |       |     2   (0)| 00:00:01 |
| 179 |                            FAST DUAL                         |                                |     1 |       |     2   (0)| 00:00:01 |
| 180 |                             FAST DUAL                        |                                |     1 |       |     2   (0)| 00:00:01 |
| 181 |                              FAST DUAL                       |                                |     1 |       |     2   (0)| 00:00:01 |
| 182 |                               FAST DUAL                      |                                |     1 |       |     2   (0)| 00:00:01 |
| 183 |                                FAST DUAL                     |                                |     1 |       |     2   (0)| 00:00:01 |
| 184 |                                 FAST DUAL                    |                                |     1 |       |     2   (0)| 00:00:01 |
| 185 |                                  FAST DUAL                   |                                |     1 |       |     2   (0)| 00:00:01 |
| 186 |                                   FAST DUAL                  |                                |     1 |       |     2   (0)| 00:00:01 |
| 187 |                                    FAST DUAL                 |                                |     1 |       |     2   (0)| 00:00:01 |
| 188 |                                     FAST DUAL                |                                |     1 |       |     2   (0)| 00:00:01 |
| 189 |                                      FAST DUAL               |                                |     1 |       |     2   (0)| 00:00:01 |
| 190 |                                       FAST DUAL              |                                |     1 |       |     2   (0)| 00:00:01 |
| 191 |                                        FAST DUAL             |                                |     1 |       |     2   (0)| 00:00:01 |
| 192 |                                         FAST DUAL            |                                |     1 |       |     2   (0)| 00:00:01 |
| 193 |                                          FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
| 194 |                                           FAST DUAL          |                                |     1 |       |     2   (0)| 00:00:01 |
| 195 |                                            FAST DUAL         |                                |     1 |       |     2   (0)| 00:00:01 |
| 196 |                                             FAST DUAL        |                                |     1 |       |     2   (0)| 00:00:01 |
| 197 |                                              FAST DUAL       |                                |     1 |       |     2   (0)| 00:00:01 |
| 198 |                                               FAST DUAL      |                                |     1 |       |     2   (0)| 00:00:01 |
| 199 |                                                FAST DUAL     |                                |     1 |       |     2   (0)| 00:00:01 |
| 200 |                                                 FAST DUAL    |                                |     1 |       |     2   (0)| 00:00:01 |
| 201 |                                                  FAST DUAL   |                                |     1 |       |     2   (0)| 00:00:01 |
| 202 |                                                   FAST DUAL  |                                |     1 |       |     2   (0)| 00:00:01 |
| 203 |                                                    FAST DUAL |                                |     1 |       |     2   (0)| 00:00:01 |
| 204 | L                                                   FAST DUA |                                |     1 |       |     2   (0)| 00:00:01 |
| 205 | AL                                                   FAST DU |                                |     1 |       |     2   (0)| 00:00:01 |
| 206 | UAL                                                   FAST D |                                |     1 |       |     2   (0)| 00:00:01 |
| 207 | DUAL                                                   FAST  |                                |     1 |       |     2   (0)| 00:00:01 |
| 208 |  DUAL                                                   FAST |                                |     1 |       |     2   (0)| 00:00:01 |
| 209 | T DUAL                                                   FAS |                                |     1 |       |     2   (0)| 00:00:01 |
| 210 | ST DUAL                                                   FA |                                |     1 |       |     2   (0)| 00:00:01 |
| 211 | AST DUAL                                                   F |                                |     1 |       |     2   (0)| 00:00:01 |
| 212 |.FAST DUAL                                                    |                                |     1 |       |     2   (0)| 00:00:01 |
| 213 |. FAST DUAL                                                   |                                |     1 |       |     2   (0)| 00:00:01 |
| 214 |.  FAST DUAL                                                  |                                |     1 |       |     2   (0)| 00:00:01 |
| 215 |.   FAST DUAL                                                 |                                |     1 |       |     2   (0)| 00:00:01 |
| 216 |.    FAST DUAL                                                |                                |     1 |       |     2   (0)| 00:00:01 |
| 217 |.     FAST DUAL                                               |                                |     1 |       |     2   (0)| 00:00:01 |
| 218 |.      FAST DUAL                                              |                                |     1 |       |     2   (0)| 00:00:01 |
| 219 |.       FAST DUAL                                             |                                |     1 |       |     2   (0)| 00:00:01 |
| 220 |.        FAST DUAL                                            |                                |     1 |       |     2   (0)| 00:00:01 |
| 221 |.         FAST DUAL                                           |                                |     1 |       |     2   (0)| 00:00:01 |
| 222 |.          FAST DUAL                                          |                                |     1 |       |     2   (0)| 00:00:01 |
| 223 |.           FAST DUAL                                         |                                |     1 |       |     2   (0)| 00:00:01 |
| 224 |.            FAST DUAL                                        |                                |     1 |       |     2   (0)| 00:00:01 |
| 225 |.             FAST DUAL                                       |                                |     1 |       |     2   (0)| 00:00:01 |
| 226 |.              FAST DUAL                                      |                                |     1 |       |     2   (0)| 00:00:01 |
| 227 |.               FAST DUAL                                     |                                |     1 |       |     2   (0)| 00:00:01 |
| 228 |.                FAST DUAL                                    |                                |     1 |       |     2   (0)| 00:00:01 |
| 229 |.                 FAST DUAL                                   |                                |     1 |       |     2   (0)| 00:00:01 |
| 230 |.                  FAST DUAL                                  |                                |     1 |       |     2   (0)| 00:00:01 |
| 231 |.                   FAST DUAL                                 |                                |     1 |       |     2   (0)| 00:00:01 |
| 232 |.                    FAST DUAL                                |                                |     1 |       |     2   (0)| 00:00:01 |
| 233 |.                     FAST DUAL                               |                                |     1 |       |     2   (0)| 00:00:01 |
| 234 |.                      FAST DUAL                              |                                |     1 |       |     2   (0)| 00:00:01 |
| 235 |.                       FAST DUAL                             |                                |     1 |       |     2   (0)| 00:00:01 |

I had an interesting little project this morning. Of course it takes longer to write it down than to do actually do it, but it was kind of interesting and since I haven’t done a post in quite some time (and it’s the day before Thanksgiving, so it’s pretty quite at the office anyway) I decided to share. One of the Enkitec guys (Tim Fox) was doing a performance comparison between various platforms (Exadata using it’s IB Storage Network, Oracle Database Appliance (ODA) using it’s direct attached storage, and a standard database on a Dell box using EMC fiber channel attached storage). The general test idea was simple – see how the platforms stacked up for a query that required a full scan of a large table. More specifically, what Tim wanted to see was the relative speed at which the various storage platforms could return data. The expectation was that the direct attached storage would be fastest and the fibre channel storage would be slowest (especially since we only had a single 2G HBA). He tested ODA and Exadata and got basically what he expected, but when he went to test the database on the Dell he was surprised that it was actually faster than either of the other two tests. So here’s some output from the initial tests: First the Exadata. It’s an X2 quarter rack with one extra storage server. Note that we had to set cell_offload_processing to false to turn off the Exadata storage optimizations, thus giving us a measurement of the hardware capabilities without the Exadata offloading.

> !sqlp
sqlp
 
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 11:08:28 2011
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
SYS@DEMO1> @uptime
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
DEMO1            07-NOV-2011 12:37 23-NOV-2011 11:08   15.94    1377058
 
SYS@DEMO1> set sqlprompt "_USER'@'EXADATA'>' "
SYS@EXADATA> 
SYS@EXADATA> ! cat /etc/redhat-release
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
 
SYS@EXADATA> ! uname -a
Linux enkdb03.enkitec.com 2.6.18-194.3.1.0.3.el5 #1 SMP Tue Aug 31 22:41:13 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
 
SYS@EXADATA> alter session set "_serial_direct_read"=always;
 
Session altered.
 
SYS@EXADATA> alter session set cell_offload_processing=false;
 
Session altered.
 
SYS@EXADATA> set autotrace on
SYS@EXADATA> set timing on
SYS@EXADATA> select count(*) from instructor.class_sales;
 
  COUNT(*)
----------
  90000000
 
Elapsed: 00:00:43.01
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3145879882
 
----------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |   314K  (1)| 00:00:02 |
|   1 |  SORT AGGREGATE            |             |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| CLASS_SALES |    90M|   314K  (1)| 00:00:02 |
----------------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1168567  consistent gets
    1168557  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SYS@EXADATA> set autotrace off
SYS@EXADATA> @fss
Enter value for sql_text: select count(*) from instructor.class_sales
Enter value for sql_id: 
 
SQL_ID         CHILD      EXECS   AVG_ROWS     AVG_ETIME       AVG_CPU       AVG_PIO      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------- ------------- ------------ ----------------------------------------
b2br1x82p9862      0          1          1         43.00          3.16  1,168,557.00    1,168,567 select count(*) from instructor.class_sa
 
Elapsed: 00:00:00.08

So the test on the Exadata took 43 seconds to read and transport roughly 1 million 8K blocks. The same test on the ODA looked like this: Continue reading ‘Tuning Oracle to Make a Query Slower’ »

Oracle today announced a new database appliance product. It’s called Oracle Database Appliance (ODA). I’m not crazy about the name, but I really like the product. Here’s a picture of the one in Enkitec’s lab:
|
|
The project was code named “Comet” – thus the yellow sticky note. ;)

I really like that name better than ODA, so I think I will just stick with Comet.

Enkitec participated in the beta test program for the product and we were very impressed, particularly with the speed at which the product could be deployed and configured. There is a new tool called the “OAK Configurator” that is sort like the Exadata OneCommand for configuring the system. Keep an eye out for Karl Arao‘s upcoming post with screen shots of the tool in action.

I’m sure there will be plenty of people talking about the specs so I won’t get carried away with that. But I will tell you that it’s basically 4 terrabytes of usable storage, 2 node RAC with up to 24 cores and a SSD component that is used for improving redo write speeds (more on that later), all in a 4U chassis. Andy Colvin has already got a really good post on the  hardware components that are included in the Oracle Database Appliance (along with pictures of the bits and bobs inside the chassis).

I should point out that while I have heard people refer to Comet as a “Baby Exadata”, I really don’t view it that way. That’s because it DOES NOT have separate storage and compute tiers. So there is no Exadata Smart Scan / Offloading secret sauce here. It also does not provide the ability to utilize Exadata’s Hybrid Columnar Compression. On the other hand, like Exadata, it is a pre-configured and tested system that can be dropped in a data center and be ready for use almost immediately (it took us only a couple of hours to set it up and create a database). Pretty unbelievable really.

So much like my favorite Bill Clinton quote, whether ODA is a “Baby Exadata” or not, really depends on your definition of the word “is”. It is a hardware platform that is built specifically to run Oracle databases, but it does not embed any of the unique Exadata software components. Nevertheless, it is an extremely capable platform that will appeal to wide variety of companies running Oracle databases.

And best of all, the list price for this puppy is only $50K. I predict this thing is going to sell like hot cakes!

I ran into a really ugly SQL statement last week. It was the most expensive statement running on the system for the last several weeks. On top of the fact that the statement ran for hours, it also had a number of “issues”. The statement had the following characteristics:

• Several DISTINCT Operators – these tend to show up in statements where developers have left off a join condition
• ANSI Join Syntax – generally Oracle converts this syntax back to Oracle join syntax under the covers. I realize this is standard SQL syntax, but it still makes me nervous due to previous experience with problems caused by this conversion process.
• Functions Used in WHERE Clauses (DECODE and NVL in particular) – can disable indexes
• UNION – forces a sort of the union’ed result sets so that it can eliminate duplicates (which is often not necessary). Depending on how the statement is coded, UNIONs can also sometimes result in multiple accesses of the same objects (i.e. if the developer has used the UNION in the place of a more robust where clause).
• Numerous != and <> Comparison Operators – Not necessarily a problem but often can be coded in a more efficient manner
• Several OR’s – OR’s are tricky. I get suspicious when there are numerous other problems in a statement as well.

I’ll refrain from publishing the actual statement to protect the guilty, but one of the most interesting bits of the statement looked something like this:

...
WHERE a.col1 =  DECODE ('XYZ', '*', a.col1 ,'XYZ')
...

The processing of the where clause can be roughly translated to the following psuedo code:

if 'XYZ' = '*' then 
  x = a.col1
else
  x = 'XYZ'
end if

...
WHERE a.col1 = x
...

Since the literal ‘XYZ’ will never be equal to the literal ‘*’, the value returned by this DECODE will always be ‘XYZ’. Therefore the statement could have been written much more simply as:

...
WHERE a.col1 = 'XYZ'
...

So this is an extremely strange way to write the statement and clouds the intention considerably, but does it hurt performance? Well actually no. The optimizer is smart enough to know that this DECODE will always result in the same literal value and so it appears that Oracle does not run the DECODE statement for each row. Here’s a quick example from a 10.2.0.4 database. (note that I used dplan.sql and fss.sql in this code snippet)

Continue reading ‘Funny Developer Tricks – Decode’ »

Cary Millsap is teaching a new one day class next week in Dallas (well Southlake actually) on Oracle trace data. This is a class that he has personally been working on recently and is teaching. I am planning on attending. Here’s a link to the sign up page which has all the details:

Mastering Oracle Trace Data

Check it out.