Search

Top 60 Oracle Blogs

Recent comments

Exadata

Real-World Performance Videos on YouTube – OLTP

In addition, here are some OLTP demos that demonstrate how much performance and throughput can be wasted by poor design and suboptimal database programming.

OLTP Performance – The Trouble with Parsing

width="640" height="510" src="http://www.youtube.com/embed/1oddFEyUAjs?rel=0" frameborder="0" allowfullscreen>

OLTP Performance – Concurrent Mid-Tier Connections

width="640" height="510" src="http://www.youtube.com/embed/xNDnVOCdvQ0?rel=0" frameborder="0" allowfullscreen>

Real-World Performance Videos on YouTube – Data Warehousing

Here are some videos of a data warehouse demo that the Real-World Performance Group has been running for a while now and we thought it was time to put them on YouTube. Hope you find them informative.

Migrate a 1TB Data warehouse in 20 Minutes (Part 1)

width="640" height="510" src="http://www.youtube.com/embed/q8zwfC_pruQ?rel=0" frameborder="0" allowfullscreen>

Migrate a 1TB Data warehouse in 20 Minutes (Part 2)

width="640" height="510" src="http://www.youtube.com/embed/qC5MT6qiPWw?rel=0" frameborder="0" allowfullscreen>

Migrate a 1TB Data warehouse in 20 Minutes (Part 3)

width="640" height="510" src="http://www.youtube.com/embed/2jWq-VUeOGs?rel=0" frameborder="0" allowfullscreen>

Migrate a 1TB Data warehouse in 20 Minutes (Part 4)

width="640" height="510" src="http://www.youtube.com/embed/hMXsrxyeRro?rel=0" frameborder="0" allowfullscreen>

Oracle Open World 2011 – Suggest a Session

Well my lone abstract submission didn’t get selected at Open World this year. But apparently they have a second chance system where you can “Suggest a Session” and users can vote on which papers they’d like to see on the agenda. I went ahead and suggested “Tuning Exadata” – It sounds like something you shouldn’t have to do, but remember that Exadata is not an appliance that has few or no knobs to turn. It has all the power and options of an Oracle database and there are certainly things that you can do wrong that will keep Exadata from performing at its best. So the paper is about how you can know for sure whether Exadata is doing what it should and how to coerce it if you need to.

The mix.oracle.com site where this voting is supposed to take place is a little difficult to navigate (in my humble opinion) so here’s a direct link to the page where you can see the abstract (and vote if you deem it worthy). ;)

Tuning Exadata

You will have to log in with your Oracle Single Signon account (what you use for My Oracle Support – or Metalink for the old guys) or I think you can create an separate account if you want. By the way, Andy Colvin has submitted an abstract for a talk on Exadata Patching, which should be very informative if it gets picked. He’s done more Exadata patching than anyone I am aware of. Here’s a link to his abstract:

Rolling with the Punches – Adventures in Exadata Patching

There will undoubtedly be many deserving abstracts. For example, several of my OakTable brethren have suggested sessions as well. So please look around the site for others of interest as well. You can vote for as many as you want.

Sasquatch – er, Exadata X2-8

We’ve been joking around at the office about whether the Exadata X2-8 model has actually been observed in the wild. Some of the guys have been affectionately referring to it as Sasquatch because we’ve never actually seen one. Well we actually got our hands on real one today.

Not as pretty as the X2-2, but as long as it’s fast it doesn’t really matter what it looks like I guess. Thanks to Andy Colvin for the iPhone snap. We’ll be doing some testing with it soon so stay tuned. By the way, we’ve got our T-Shirts on order:

Exadata drives exceed the laws of physics… ASM with intelligent placement improves IOPS

I recently had an interesting time with a customer who is all too familiar with SAN’s.  SAN vendors typically use IOPS/drive sizing numbers of 180 IOPS per drive.  This is a good conservative measure for SAN sizing, but the drives are capable of much more and indeed we state higher with Exadata.  So, how could this be possible?  Does Exadata have an enchantment spell that makes the drives magically spin faster?  Maybe a maybe a space time warp to service IO?

Extreme Performance with Oracle Exadata Executive Dinner

I spoke at an Oracle marketing event in San Antonio last night. Here is a link to the promotional page for the event (it will probably disappear soon). I promised to make a copy of my slides available so here it is:

6th Planboard DBA Symposium

The 17th of may 2011 Planboard is organising the 6th version of the Planboard DBA Symposium. The symposium is a whole day event, in Amstelveen near Amsterdam.

There is a wide range of Oracle database topics, including Apex, Oracle GoldenGate, Oracle E-Business suite and Oracle Exadata (presented by me :) !).

If you are interested, take a look at the full agenda!

Realtime SQL Monitoring – Designed with Exadata in Mind

One of the best new features of 11g from a diagnostic standpoint is the Real Time SQL Monitoring capabilities. I did a post about it a couple of years ago here: Oracle 11g Real Time SQL Monitoring In that post I talked about a procedure (DBMS_SQLTUNE.REPORT_SQL_MONITOR) that provides a very nicely formatted explain plan type output which contains quite a bit of useful information. Well, it has recently come to my attention that the report contains a column that shows cell offloading. That’s pretty cool. Here’s a script to call that procedure, report_sql_monitor.sql, and an example (note the format is really wide so be sure and use the scroll bar at the bottom to see the columns on the right of the output):

SYS@SANDBOX1> @report_sql_monitor.sql
Enter value for sid: 
Enter value for sql_id: 2hzzka3071hkj
Enter value for sql_exec_id: 
 
REPORT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
select /*+ INDEX (SKEW2 SKEW2_COL1) */ avg(pk_col) from kso.skew2
 
Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (3:2673)
 SQL ID              :  2hzzka3071hkj
 SQL Execution ID    :  16777216
 Execution Started   :  03/13/2011 17:02:35
 First Refresh Time  :  03/13/2011 17:02:35
 Last Refresh Time   :  03/13/2011 17:02:48
 Duration            :  13s
 Module/Action       :  sqlplus@Kerry-Osbornes-MacBook-Pro-4.local (TNS /-
 Service             :  SANDBOX
 Program             :  sqlplus@Kerry-Osbornes-MacBook-Pro-4.local (TNS
 Fetch Calls         :  1
 
Global Stats
============================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Fetch | Buffer | Read | Read  |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload |
============================================================================================================================
|      96 |      92 |     2.33 |        0.00 |        0.80 |     0.00 |     0.47 |     1 |   653K | 6136 |   5GB |  71.83% |
============================================================================================================================
 
Parallel Execution Details (DOP=8 , Servers Allocated=8)
===================================================================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Buffer | Read | Read  |  Cell   |        Wait Events        |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Offload |        (sample #)         |
===================================================================================================================================================================================
| PX Coordinator | QC    |         |    0.83 |    0.03 |     0.00 |        0.00 |        0.80 |     0.00 |          |      7 |    3 | 24576 |    NaN% |                           |
| p000           | Set 1 |       1 |      12 |      11 |     0.29 |             |             |          |     0.07 |  81601 |  770 | 637MB |  71.83% | cell smart table scan (1) |
| p001           | Set 1 |       2 |      12 |      11 |     0.31 |             |             |          |     0.06 |  81601 |  775 | 637MB |  71.83% |                           |
| p002           | Set 1 |       3 |      12 |      12 |     0.32 |             |        0.00 |          |     0.06 |  81601 |  764 | 637MB |  71.83% | cell smart table scan (1) |
| p003           | Set 1 |       4 |      12 |      11 |     0.25 |             |             |          |     0.04 |  81601 |  768 | 637MB |  71.83% |                           |
| p004           | Set 1 |       5 |      12 |      12 |     0.30 |             |             |          |     0.06 |  81601 |  757 | 637MB |  71.83% | cell smart table scan (1) |
| p005           | Set 1 |       6 |      12 |      11 |     0.26 |             |             |          |     0.05 |  81601 |  755 | 637MB |  71.83% |                           |
| p006           | Set 1 |       7 |      12 |      12 |     0.28 |             |             |          |     0.07 |  81601 |  769 | 637MB |  71.83% |                           |
| p007           | Set 1 |       8 |      12 |      12 |     0.33 |             |             |          |     0.06 |  81560 |  775 | 637MB |  71.83% |                           |
===================================================================================================================================================================================
 
SQL Plan Monitoring Details (Plan Hash Value=2117817910)
==============================================================================================================================================================================
| Id |            Operation             |   Name   |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  |  Cell   | Activity |      Activity Detail      |
|    |                                  |          | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload |   (%)    |        (# samples)        |
==============================================================================================================================================================================
|  0 | SELECT STATEMENT                 |          |         |       |         1 |    +13 |     1 |        1 |      |       |         |          |                           |
|  1 |   SORT AGGREGATE                 |          |       1 |       |         1 |    +13 |     1 |        1 |      |       |         |          |                           |
|  2 |    PX COORDINATOR                |          |         |       |         1 |    +13 |     9 |        8 |      |       |         |          |                           |
|  3 |     PX SEND QC (RANDOM)          | :TQ10000 |       1 |       |         2 |    +12 |     8 |        8 |      |       |         |          |                           |
|  4 |      SORT AGGREGATE              |          |       1 |       |        13 |     +1 |     8 |        8 |      |       |         |    50.52 | Cpu (49)                  |
|  5 |       PX BLOCK ITERATOR          |          |    128M | 24700 |        13 |     +1 |     8 |     128M |      |       |         |    45.36 | Cpu (44)                  |
|  6 |        TABLE ACCESS STORAGE FULL | SKEW2    |    128M | 24700 |        13 |     +1 |   104 |     128M | 6133 |   5GB |  71.83% |     3.09 | cell smart table scan (3) |
==============================================================================================================================================================================

So as you can see, this is a parallel query. For parallel queries there is a separate section that shows what each slave process did. There are a couple of columns that are particularly interesting in this section. The Cell Offload column shows the percentage reduction in data transferred to the database tier that resulted from offloading. The Wait Events column shows what the slaves waited on. In the Plan Details section you can see that the plan included a full table scan and that it was offloaded. This section has columns that show the same basic information (although the column names are slightly different), but it’s organized by plan step. This is extremely useful on complex statements that have multiple steps which may be offloaded. This section also clearly shows which steps are taking the most time (Activity %).

Here’s a more complicated example. The statement in this example doesn’t have a lot of steps, but the output is very useful in understanding what is happening. Again, be sure to scroll over to the right to see the interesting bits.

SYS@SANDBOX1> @report_sql_monitor
Enter value for sid: 
Enter value for sql_id: fm7y4nqu4tbv3
Enter value for sql_exec_id: 
 
REPORT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
update kso.skew_hcc1 set col1 =col1*1
 
Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  SYS (451:1843)
 SQL ID              :  fm7y4nqu4tbv3
 SQL Execution ID    :  16777216
 Execution Started   :  04/10/2011 17:41:24
 First Refresh Time  :  04/10/2011 17:41:24
 Last Refresh Time   :  04/10/2011 18:07:16
 Duration            :  1552s
 Module/Action       :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)/-
 Service             :  SYS$USERS
 Program             :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)
 
Global Stats
=========================================================================================================================================
| Elapsed | Queuing |   Cpu   |    IO    | Application | Concurrency | Cluster  | PL/SQL  |  Other   | Buffer | Read  | Read  |  Cell   |
| Time(s) | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Time(s) | Waits(s) |  Gets  | Reqs  | Bytes | Offload |
=========================================================================================================================================
|    1583 |    0.00 |    1442 |      132 |        0.00 |        0.03 |     2.35 |    0.00 |     7.01 |   231M | 63553 | 784MB | -29.87% |
=========================================================================================================================================
 
Parallel Execution Details (DOP=32 , Servers Allocated=32)
====================================================================================================================================================================================================================
|      Name      | Type  | Server# | Elapsed | Queuing |   Cpu   |    IO    | Application | Concurrency | Cluster  | PL/SQL  |  Other   | Buffer | Read  | Read  |  Cell   |              Wait Events              |
|                |       |         | Time(s) | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Time(s) | Waits(s) |  Gets  | Reqs  | Bytes | Offload |              (sample #)               |
====================================================================================================================================================================================================================
| PX Coordinator | QC    |         |    1551 |    0.00 |    1425 |      117 |        0.00 |        0.03 |     2.23 |    0.00 |     6.69 |   231M | 58878 | 460MB |    NaN% | gc current grant 2-way (1)            |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | log buffer space (5)                  |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | log file switch completion (3)        |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | enq: FB - contention (2)              |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | cell single block physical read (122) |
| p000           | Set 1 |       1 |    0.93 |         |    0.53 |     0.39 |             |             |     0.00 |         |          |   1682 |   120 |  10MB | -72.41% | cell smart table scan (2)             |
| p001           | Set 1 |       2 |    0.98 |         |    0.51 |     0.48 |             |             |          |         |          |   1389 |   107 |  10MB | -72.41% | cell single block physical read (1)   |
| p002           | Set 1 |       3 |    0.97 |         |    0.57 |     0.41 |             |             |     0.00 |         |          |   2954 |   181 |  10MB | -66.67% |                                       |
| p003           | Set 1 |       4 |    0.91 |         |    0.51 |     0.40 |             |             |     0.00 |         |          |   1412 |   119 |  10MB | -72.41% |                                       |
| p004           | Set 1 |       5 |    0.92 |         |    0.52 |     0.40 |             |             |     0.00 |         |          |   1415 |   121 |  10MB | -69.49% | cell single block physical read (1)   |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | cell smart table scan (1)             |
| p005           | Set 1 |       6 |    1.31 |         |    0.54 |     0.77 |             |             |     0.00 |         |          |   7985 |   389 |  12MB | -58.73% | cell single block physical read (2)   |
| p006           | Set 1 |       7 |    0.98 |         |    0.48 |     0.50 |             |             |     0.00 |         |          |   2653 |   123 |  10MB | -72.41% |                                       |
| p007           | Set 1 |       8 |    0.96 |         |    0.49 |     0.46 |             |             |     0.00 |         |          |   2926 |   175 |  10MB | -69.49% |                                       |
| p008           | Set 1 |       9 |    0.84 |         |    0.53 |     0.30 |             |             |     0.00 |         |          |   1380 |    98 |  10MB | -75.44% | cell smart table scan (1)             |
| p009           | Set 1 |      10 |    0.81 |         |    0.48 |     0.33 |             |             |     0.00 |         |          |   1389 |   108 |  10MB | -72.41% |                                       |
| p010           | Set 1 |      11 |    1.32 |         |    0.54 |     0.46 |             |             |     0.00 |         |     0.32 |   2283 |    90 |  10MB | -75.44% | cell smart table scan (1)             |
| p011           | Set 1 |      12 |    1.13 |         |    0.44 |     0.68 |             |             |     0.00 |         |          |   1382 |    92 |  10MB | -75.44% | cell smart table scan (1)             |
| p012           | Set 1 |      13 |    1.12 |         |    0.58 |     0.54 |             |             |     0.00 |         |          |   5818 |   238 |  11MB | -66.67% |                                       |
| p013           | Set 1 |      14 |    0.92 |         |    0.55 |     0.37 |             |             |     0.00 |         |          |   1408 |   114 |  10MB | -72.41% |                                       |
| p014           | Set 1 |      15 |    1.12 |         |    0.53 |     0.59 |             |             |     0.00 |         |          |   3604 |   217 |  11MB | -66.67% |                                       |
| p015           | Set 1 |      16 |    0.98 |         |    0.53 |     0.45 |             |             |     0.00 |         |          |   1973 |   110 |  10MB | -75.44% |                                       |
| p016           | Set 1 |      17 |    0.97 |         |    0.51 |     0.46 |             |             |     0.00 |         |          |   1407 |   106 |  10MB | -72.41% | cell single block physical read (2)   |
| p017           | Set 1 |      18 |    0.95 |         |    0.50 |     0.45 |             |             |     0.00 |         |          |   5017 |   210 |  11MB | -69.49% |                                       |
| p018           | Set 1 |      19 |    1.08 |         |    0.52 |     0.56 |             |        0.00 |     0.00 |         |          |   2305 |   156 |  10MB | -69.49% |                                       |
| p019           | Set 1 |      20 |    0.92 |         |    0.49 |     0.42 |             |             |          |         |          |   1366 |    87 |  10MB | -75.44% |                                       |
| p020           | Set 1 |      21 |    0.89 |         |    0.55 |     0.33 |             |             |     0.00 |         |          |   2804 |   107 |  10MB | -72.41% |                                       |
| p021           | Set 1 |      22 |    0.89 |         |    0.53 |     0.35 |             |             |     0.00 |         |          |   1392 |   110 |  10MB | -72.41% | cell smart table scan (1)             |
| p022           | Set 1 |      23 |    0.94 |         |    0.53 |     0.40 |             |             |     0.00 |         |          |   1384 |    97 |  10MB | -72.41% |                                       |
| p023           | Set 1 |      24 |    0.93 |         |    0.52 |     0.41 |             |             |     0.00 |         |          |   9753 |   177 |  10MB | -66.67% |                                       |
| p024           | Set 1 |      25 |    1.07 |         |    0.53 |     0.53 |             |             |     0.00 |         |          |   7194 |   275 |  11MB | -66.67% |                                       |
| p025           | Set 1 |      26 |    1.15 |         |    0.54 |     0.53 |             |             |     0.08 |         |          |   2394 |    96 |  10MB | -75.44% |                                       |
| p026           | Set 1 |      27 |    1.13 |         |    0.54 |     0.59 |             |             |     0.00 |         |          |   5178 |   224 |  11MB | -63.93% | cell smart table scan (1)             |
| p027           | Set 1 |      28 |    0.94 |         |    0.48 |     0.45 |             |             |     0.00 |         |          |   3969 |   151 |  10MB | -69.49% |                                       |
| p028           | Set 1 |      29 |    0.99 |         |    0.54 |     0.45 |             |             |     0.00 |         |          |   1387 |    97 |  10MB | -75.44% | cell smart table scan (1)             |
| p029           | Set 1 |      30 |    0.80 |         |    0.54 |     0.26 |             |             |     0.00 |         |          |   1375 |    90 |  10MB | -75.44% |                                       |
| p030           | Set 1 |      31 |    1.10 |         |    0.62 |     0.48 |             |             |     0.00 |         |          |   2958 |   184 |  10MB | -69.49% | cell smart table scan (1)             |
| p031           | Set 1 |      32 |    1.03 |         |    0.52 |     0.51 |             |             |     0.00 |         |          |   1389 |   106 |  10MB | -69.49% | cell smart table scan (1)             |
====================================================================================================================================================================================================================
 
SQL Plan Monitoring Details (Plan Hash Value=3842559352)
==========================================================================================================================================================================================
| Id |            Operation            |   Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Cell   | Activity |            Activity Detail            |
|    |                                 |           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Offload |   (%)    |              (# samples)              |
==========================================================================================================================================================================================
|  0 | UPDATE STATEMENT                |           |         |      |           |        |     1 |          |       |       |         |          |                                       |
|  1 |   UPDATE                        | SKEW_HCC1 |         |      |      1552 |     +1 |     1 |        0 | 58650 | 458MB |         |    94.61 | gc current grant 2-way (1)            |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | log buffer space (5)                  |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | log file switch completion (3)        |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | Cpu (1359)                            |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | enq: FB - contention (2)              |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | cell single block physical read (122) |
|  2 |    PX COORDINATOR               |           |         |      |      1551 |     +2 |    33 |      32M |       |       |         |     3.68 | Cpu (58)                              |
|  3 |     PX SEND QC (RANDOM)         | :TQ10000  |     32M |  375 |      1551 |     +2 |    32 |      32M |       |       |         |     0.38 | Cpu (6)                               |
|  4 |      PX BLOCK ITERATOR          |           |     32M |  375 |      1551 |     +2 |    32 |      32M |       |       |         |          |                                       |
|  5 |       TABLE ACCESS STORAGE FULL | SKEW_HCC1 |     32M |  375 |      1551 |     +2 |   417 |      32M |  4675 | 324MB | -69.49% |     1.33 | Cpu (4)                               |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | cell single block physical read (6)   |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | cell smart table scan (11)            |
==========================================================================================================================================================================================
 
 
1 row selected.

In this example, we have an update statement that took a while, even though it was using Smart Scans. So what gives? When we looked at the output from SQL Monitor it was clear that it wasn’t spending all it’s time looking for the records to update, but rather on the update itself. As you might have guessed from the name of the table, this statement was attempting to update every row in a table that had been compressed using HCC (not something you would normally want to do). This caused a lot of extra work, migrating the rows to OLTP compressed blocks. So that’s what’s going on under the covers. The point of the example is that the report quickly focuses our attention on the area that needs investigation.

By the way, SQL Monitoring is supposed to kick in automatically for long running statements (over 5 seconds by default) and for parallel statements. Occasionally though we would like to see the SQL Monitoring Report on a statement that is not being monitored for some reason. Fortunately, there is a hint (MONITOR) that can be used to force SQL Monitoring. In situations where we don’t have access to the code, like a packaged app for example, we can apply the hint via a SQL Profile. I created a little script called fm.sql to do just that. And no fm doesn’t stand a radio frequency range, and no it doesn’t stand for some kind of magic, and no it’s not the name of a Steely Dan song (well it is, but that’s not why I called it that). It stands for “force monitoring”. Here’s an example:

 
 
*******************
Force Monitoring via a SQL Profile
*******************
 
 
SYS@SANDBOX1> select count(*) from kso.little_skew a;
 
  COUNT(*)
----------
   1100098
 
1 row selected.
 
SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.little_skew a
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
8xrg1nunxn181      0 3498336203      1        .02      0 No             .00 select count(*) from kso.little_skew a
 
1 row selected.
 
SYS@SANDBOX1> @report_sql_monitor
Enter value for sid: 
Enter value for sql_id: 8xrg1nunxn181
Enter value for sql_exec_id: 
 
REPORT
-----------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
1 row selected.
 
SYS@SANDBOX1> -- It's a short duration non-PX statement so it wasn't monitored
SYS@SANDBOX1> -- let's force monitoring via a profile (we'll create the profile with fm.sql)
SYS@SANDBOX1> 
SYS@SANDBOX1> @fm
Enter value for sql_id: 8xrg1nunxn181
 
Profile PROFILE_8xrg1nunxn181_MONIOTR created.
 
SYS@SANDBOX1> select count(*) from kso.little_skew a;
 
  COUNT(*)
----------
   1100098
 
1 row selected.
 
SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.little_skew a
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
8xrg1nunxn181      0 3498336203      1        .02      0 No             .00 select count(*) from kso.little_skew a
8xrg1nunxn181      1 3498336203      1        .02      0 No             .00 select count(*) from kso.little_skew a
 
2 rows selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 8xrg1nunxn181
Enter value for child_no: 1
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8xrg1nunxn181, child number 1
-------------------------------------
select count(*) from kso.little_skew a
 
Plan hash value: 3498336203
 
----------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |       |  1546 (100)|          |
|   1 |  SORT AGGREGATE            |             |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| LITTLE_SKEW |  1100K|  1546   (1)| 00:00:19 |
----------------------------------------------------------------------------------
 
Note
-----
   - SQL profile PROFILE_8xrg1nunxn181_MONITOR used for this statement
 
 
18 rows selected.
 
SYS@SANDBOX1> @report_sql_monitor
Enter value for sid: 
Enter value for sql_id: 8xrg1nunxn181
Enter value for sql_exec_id: 
 
REPORT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
select count(*) from kso.little_skew a
 
Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (451:1845)
 SQL ID              :  8xrg1nunxn181
 SQL Execution ID    :  16777217
 Execution Started   :  04/10/2011 19:08:17
 First Refresh Time  :  04/10/2011 19:08:17
 Last Refresh Time   :  04/10/2011 19:08:17
 Duration            :  .020997s
 Module/Action       :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)/-
 Service             :  SYS$USERS
 Program             :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)
 Fetch Calls         :  1
 
Global Stats
======================================
| Elapsed |   Cpu   | Fetch | Buffer |
| Time(s) | Time(s) | Calls |  Gets  |
======================================
|    0.02 |    0.02 |     1 |   5612 |
======================================
 
SQL Plan Monitoring Details (Plan Hash Value=3498336203)
=========================================================================================================================================
| Id |          Operation           |    Name     |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                              |             | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
=========================================================================================================================================
|  0 | SELECT STATEMENT             |             |         |      |         1 |     +0 |     1 |        1 |          |                 |
|  1 |   SORT AGGREGATE             |             |       1 |      |         1 |     +0 |     1 |        1 |          |                 |
|  2 |    TABLE ACCESS STORAGE FULL | LITTLE_SKEW |      1M | 1546 |         1 |     +0 |     1 |       1M |          |                 |
=========================================================================================================================================

So there you have it. A quick and dirty way to enable Real Time SQL Monitoring on a production statement that wasn’t being monitored by default.

I have really been using this capability a lot lately. Being able to see which steps in a complex plan are offloaded and what wait events are affecting which steps is very useful. The “Activity %” column which shows which steps the statement spent all it’s time on is also pretty helpful. It’s a tool that has moved very close to the top of my bag.

P.S. Greg Rahn has been a proponent of this report for some time and has been blogging about it for several years. Here’s one from Jan of 2008: Oracle 11g: Real-Time SQL Monitoring Using DBMS_SQLTUNE.REPORT_SQL_MONITOR

How Oracle Result Cache and Smart Scans Work Together

I got an interesting question today from a guy who is reading through the Alpha version of our Exadata Book. I’m paraphrasing, but Ajay basically wanted to know about the interaction between the relatively new Result Cache feature and Smart Scans. The short answer is that they work well together, exactly as you would hope. But let me back up for minute.

The Results Cache feature was introduced with 11gR1. It’s a pretty cool feature – Tom Kyte calls it JIT MV (just in time materialized view).

It basically caches the result set of a query as opposed to blocks from the underlying objects that are necessary to process the query. It is designed for situations where data is relatively static and fairly long running queries are repeated many times. Think of a DW that is updated nightly with some key queries that are repeated several times a day. As you might expect, the performance impact can be dramatic.

The question is interesting because Smart Scans read result sets directly into the PGA as opposed to retrieving blocks into the buffer cache. Sound familiar? Anyway, here’s an example of executing an Offloadable statement with a RESULT_CACHE hint:

SYS@SANDBOX> -- flush the result cache in case something is still hanging around
SYS@SANDBOX> exec DBMS_RESULT_CACHE.flush;
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.07
SYS@SANDBOX> -- run an offloadable statement a few times
SYS@SANDBOX> select avg(pk_col) from kso.skew2 a where col1 > 1;
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:14.34
SYS@SANDBOX> /
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:12.16
SYS@SANDBOX> /
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:13.01
SYS@SANDBOX> -- run the same statement with a result_cache hint
SYS@SANDBOX> select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1;
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:11.97
SYS@SANDBOX> -- check execution statistics
SYS@SANDBOX> @fsx
Enter value for sql_text: %skew2%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
6fduhxkpdx1mc      0 4220890033      1      11.88      0 Yes          74.57 select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1
8vwynva819s92      0 4220890033      3      13.08      0 Yes          74.56 select avg(pk_col) from kso.skew2 a where col1 > 1
 
Elapsed: 00:00:00.13

So first I flushed all result cache entries from the SGA (it’s stored in the shared pool). Then I ran an offloadable SELECT statement 3 times.

The admittedly simple SQL statement takes between 12 and 14 seconds (the average elapsed time over three executions is 13 seconds). And the first execution with the RESULT_CACHE hint (before the Result Cache has been built) takes roughly the same amount of elapsed time. The fsx.sql script shows that both versions were offloaded and each cut out approximately the same amount of payload. (I’ve written about the fsx.sql script previously here: How to Tune an Exadata)

So clearly, using the RESULT_CACHE hint did not disable Smart Scans.

Now let’s see if Smart Scans disabled the Result Cache. To test this I’ll just run the hinted statement a few more times.

SYS@SANDBOX> 
SYS@SANDBOX> select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1;
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:00.08
 
. . .
 
SYS@SANDBOX> /
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:00.09
SYS@SANDBOX> @fsx
Enter value for sql_text: %skew2%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
6fduhxkpdx1mc      0 4220890033     10       1.19      0 Yes          74.57 select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1
8vwynva819s92      0 4220890033      3      13.08      0 Yes          74.56 select avg(pk_col) from kso.skew2 a where col1 > 1
 
Elapsed: 00:00:00.12

So as you can see, the execution time dropped from over 10 seconds to less than 0.1 seconds. The average elapsed time for the 10 executions was about 1 second, including the first execution that took about 12 seconds. The OFFLOAD column returned by the fsx script is somewhat misleading in this case, as described in my previous post. It basically tells us if any of the executions was offloaded. Let’s clear the entry from the cursor cache and check the stats again. I’ll do this using a script called flush_sql.sql which uses the DBMS_SHARED_POOL.PURGE procedure. Then I’ll re-run the statement a few times.

 
SYS@SANDBOX> @flush_sql
Enter value for sql_id: 6fduhxkpdx1mc
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.07
SYS@SANDBOX> select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1;
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:00.10
 
. . . 
 
SYS@SANDBOX> /
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:00.07
SYS@SANDBOX> @fsx
Enter value for sql_text: %skew2%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
6fduhxkpdx1mc      0 4220890033      5        .00      0 No             .00 select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1
8vwynva819s92      0 4220890033      3      13.08      0 Yes          74.56 select avg(pk_col) from kso.skew2 a where col1 > 1
 
Elapsed: 00:00:00.12
 
SYS@SANDBOX> @dplan
Enter value for sql_id: 6fduhxkpdx1mc
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6fduhxkpdx1mc, child number 0
-------------------------------------
select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1
 
Plan hash value: 4220890033
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |       |       |   178K(100)|          |
|   1 |  RESULT CACHE               | 2gq0a1pgm8yk88cqvfxtxhchny |       |       |            |          |
|   2 |   SORT AGGREGATE            |                            |     1 |    11 |            |          |
|*  3 |    TABLE ACCESS STORAGE FULL| SKEW2                      |   127M|  1342M|   178K  (1)| 00:35:42 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - storage("COL1">1)
       filter("COL1">1)
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 -

So it’s clear from the elapsed time and the fsx script and the xplan output that the statement used the Result Cache.

Cool, Smart Scan the first time, Result Cache after that.

Let’s see if it behaves as expected after the data has been changed (it should revert to a Smart Scan which should also build the Result Cache which will be used on subsequent executions). And while we’re at it, let’s see how long it takes to execute the statement without Smart Scan (I’ll do that first actually).

SYS@SANDBOX> -- turn offloading off
SYS@SANDBOX> alter session set cell_offload_processing=false;
 
Session altered.
 
Elapsed: 00:00:00.06
SYS@SANDBOX> select avg(pk_col) from kso.skew2 a where col1 > 1;
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:52.09
SYS@SANDBOX> -- turn offloading back on
SYS@SANDBOX> alter session set cell_offload_processing=true;
 
Session altered.
 
Elapsed: 00:00:00.06
SYS@SANDBOX> @flush_sql
Enter value for sql_id: 6fduhxkpdx1mc
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.08
SYS@SANDBOX> -- change the data
SYS@SANDBOX> update kso.skew2 set col1 =col1*1 where rownum < 10
SYS@SANDBOX> /
 
9 rows updated.
 
Elapsed: 00:00:00.07
SYS@SANDBOX> commit;
 
Commit complete.
 
Elapsed: 00:00:00.06
SYS@SANDBOX>  -- first run after data changed
SYS@SANDBOX> select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1;
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:10.60
SYS@SANDBOX> /
 
AVG(PK_COL)
-----------
   16049999
 
Elapsed: 00:00:00.09

So the statement takes almost a minute when executed without Smart Scan (52 seconds). I then updated a few records and re-ran the statement. As expected the Result Cache entry was invalidated and the statement reverted to a Smart Scan. I didn’t show any proof that a Smart Scan was used, but take my word for it (the execution time was only 10.6 seconds, not 52) and apparently it rebuilt the Result Cache because the next execution took about 0.1 seconds.

So there you have it. The result cache appears to work well with Smart Scans.

How to Tune an Exadata

Q: How do you tune Exadata?
A: Look for long running queries that are not using Smart Scans and then fix them so they do.

We’ve worked on a bunch of Proof of Concepts (POC’s) for customers over the last year or so. These usually involve loading a few terabytes of data and running a bunch queries or some other workload on the data. Generally speaking, anything we have thrown at Exadata has blown the doors off of the platforms that the applications were previously running on. But occasionally we run into a situation where the speed up is just not what we’ve come to expect. Generally speaking it’s because we’ve done something that has kept Exadata from doing what it does best – Smart Scans. While my lead in is obviously a tongue in cheek over simplification, it is basically true. Unfortunately, it’s not as easy as it sounds to determine whether a statement has been offloaded, because our main tool for looking at how a statement was executed (the execution plan) doesn’t tell us whether a Smart Scan was used or not. So in this post, my intent is to give you a couple of options for determining whether Smart Scans are happening or not. Here’s a quick example showing a basic execution plan:

 
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
 
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
SANDBOX1         24-MAR-2011 16:19 25-MAR-2011 22:57    1.28     110283
 
SYS@SANDBOX> 
SYS@SANDBOX> set timing on
SYS@SANDBOX> @avgskew3
SYS@SANDBOX> select avg(pk_col) from kso.skew3
  2  where col1 > 0
  3  /
 
AVG(PK_COL)
-----------
 16093750.2
 
Elapsed: 00:00:34.80
SYS@SANDBOX> select sql_id, sql_text from v$sql
  2  where sql_text like 'select avg(pk_col) from kso.skew3 where col1 > 0';
 
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------
4p62g77m9myak select avg(pk_col) from kso.skew3 where col1 > 0
 
Elapsed: 00:00:00.14
SYS@SANDBOX> @dplan
Enter value for sql_id: 4p62g77m9myak
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4p62g77m9myak, child number 0
-------------------------------------
select avg(pk_col) from kso.skew3 where col1 > 0
 
Plan hash value: 2684249835
 
------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   535K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |   383M|  4028M|   535K  (1)| 01:47:02 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
 
20 rows selected.
 
Elapsed: 00:00:00.22

The storage line in the predicate section indicates that a Smart Scan is possible, but it doesn’t actually tell us that one occurred. So how can you tell. Well there are several ways.

  1. You can Millsap it. (generate a 10046 trace)
  2. You can Poder it. (use Tanel Poder’s snapper script to check stats and wait events)
  3. You can Wolfgang it. (generate a 10053 trace) – well actually this doesn’t work since the optimizer doesn’t know whether a statement will do a Smart Scan or not.
  4. Or you can look in v$sql – I wrote a little script called fsx.sql (short for Find_Sql_eXadata.sql) to do that.

I think that tracing is the most foolproof way to verify a Smart Scan (just look for “cell smart table/index scan” wait events). But it can be a little cumbersome to generate a trace and then find it. (Note: Method-R has a great tool to make this easier called MR Trace which is a plug in for Oracle’s SQL Developer). Tanel’s snapper script is an awesome tool that is very versatile – so it’s a very valid option as well. But both of these methods depend on the fact that you can catch the statement of interest while it is executing. They provide no way of looking back at statements that ran in the past. My fsx script is not nearly as comprehensive as either of these approaches, but it has an advantage in that it looks at values stored in v$sql (which are also captured in AWR by the way). This allows us to do analysis that is not limited to what is happening right now. (i.e. we don’t have to catch the query while it’s running).

So how does it work?

The v$sql view contains a column (IO_CELL_OFFLOAD_ELIGIBLE_BYTES) which tells us whether this child has been offloaded or not. Very simply, if the column contains a value greater than 0, then the statement was processed with a Smart Scan. Here’s the basic trick:

   decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload 

So basically the fsx script just does a decode on the column and if it’s value is 0 then it returns ‘No’, otherwise it returns ‘Yes’. The script does a few other things too like attempting to calculate the savings in terms of reduced volume of data being transferred across the InfiniBand fabric that the Smart Scan was responsible for. I called the output column IO_SAVED_% although it’s not really I/O, it’s bytes transferred between the storage layer and the database layer. There are some situations where the results of this calculation don’t seem to make much sense, by the way. But that’s too much to go into here. (There are more details in our upcoming Apress Exadata Book if you’re so inclined.) Anyway, here’s a quick example of using the fsx script.

SYS@SANDBOX> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
4p62g77m9myak      0 2684249835      2      18.31      0 Yes          71.85 select avg(pk_col) from kso.skew3 where col1 > 0

So that’s pretty easy and straight forward. You can enter a bit of a SQL statement’s text or a SQL_ID or both to locate statements of interest in v$sql. The script’s output will tell you whether it was offloaded or not. But what if we want to get a feel for how the whole system is running with regard to Offloading. We could simply add a couple of additional where clauses to the fsx script to allow us to limit the rows returned based on whether the statements were offloaded or not and maybe add a filter on average execution time as well so we can just look at the long running statements. Have a look at fsxo.sql which does just that.

SYS@SANDBOX> @fsxo
Enter value for sql_text: 
Enter value for sql_id: 
Enter value for min_etime: 10
Enter value for offloaded: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
09m6t5qpgkywx      0 1885411402      1     116.79      0 No             .00 select /*+ bloom join 2  use_hash (skew temp_skew) */ a.col2, sum(a.co
1nfa7trushhpm      0 2684249835      2      15.87      0 Yes          74.60 select /*+ result_cache */ avg(pk_col) from kso.skew3 where col1 > 1
35q8ahgw2xhsp      0 3734762968      1      37.12      0 Yes          34.22 select /*+ bloom join  use_hash (skew temp_skew) */ a.col2, sum(a.col1
4p62g77m9myak      0 2684249835      2      18.31      0 Yes          71.85 select avg(pk_col) from kso.skew3 where col1 > 0
5zruc4v6y32f9      0          0      2     362.05      0 No             .00 DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZON
b6usrg82hwsa3      0          0      2     305.30      0 No             .00 call dbms_stats.gather_database_stats_job_proc (  )
 
6 rows selected.
 
SYS@SANDBOX> @fsxo
Enter value for sql_text: 
Enter value for sql_id: 
Enter value for min_etime: 
Enter value for offloaded: YES
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
0qa98gcnnza7h      0  568322376      2       3.56      0 Yes        -905.77 select avg(pk_col) from kso.skew where col1 > 0
1nfa7trushhpm      0 2684249835      2      15.87      0 Yes          74.60 select /*+ result_cache */ avg(pk_col) from kso.skew3 where col1 > 1
266gctwscrnn2      0  568322376      3       1.06      0 Yes        -805.04 select /*+ result_cache */ avg(pk_col) from kso.skew where col1 > 1
2uzgbm8azqqv3      0 2974987230      2       1.56      0 Yes          71.79 select avg(pk_col) from kso.skew_encrypt where col1 > 0
35q8ahgw2xhsp      0 3734762968      1      37.12      0 Yes          34.22 select /*+ bloom join  use_hash (skew temp_skew) */ a.col2, sum(a.col1
4p62g77m9myak      0 2684249835      2      18.31      0 Yes          71.85 select avg(pk_col) from kso.skew3 where col1 > 0
d15cdr0zt3vtp      0   62424106      1        .31      0 Yes          99.96 SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:
 
7 rows selected.
 
SYS@SANDBOX> @fsxo
Enter value for sql_text: %skew%
Enter value for sql_id: 
Enter value for min_etime: 5
Enter value for offloaded: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
09m6t5qpgkywx      0 1885411402      1     116.79      0 No             .00 select /*+ bloom join 2  use_hash (skew temp_skew) */ a.col2, sum(a.co
1nfa7trushhpm      0 2684249835      2      15.87      0 Yes          74.60 select /*+ result_cache */ avg(pk_col) from kso.skew3 where col1 > 1
35q8ahgw2xhsp      0 3734762968      1      37.12      0 Yes          34.22 select /*+ bloom join  use_hash (skew temp_skew) */ a.col2, sum(a.col1
399m90n8jzpu6      0 1923773943      2       5.09      0 No             .00 select avg(pk_col) from kso.skew
4p62g77m9myak      0 2684249835      2      18.31      0 Yes          71.85 select avg(pk_col) from kso.skew3 where col1 > 0

So the idea is to be able to take a high level look at what’s being offloaded and what’s not. Obviously you can do the same thing with AWR data for a longer look back in history. But I’ll leave that as an exercise for the reader. Note that my lab system was recently bounced and so the number of statements is very small. On larger production systems you will probably want to limit yourself to longer running statements.

By the way, there is another tool that can be very helpful in determining if a statement was offloaded, DBMS_SQLTUNE.REPORT_SQL_MONITOR. But I’ll have to save that for another post.