It’s that time again — time to figure out what sessions you will be attending at Oracle OpenWorld 2011. In my slightly biased opinion, session by members of the OakTable Network generally have great technical content and give you the most value for your time. To aid you with your scheduling, I’ve compiled a list of sessions by OakTable members here. Enjoy!
As my loyal readers will know, I have been a big (maybe BIG) fan of the SQL Monitor Report since it’s introduction in 11g. It would not surprise me if I have looked at over 1000 SQL Monitor Reports in the past 4+ years — so I’m pretty familiar with these bad boys. Since I find them so valuable (and many customers are now upgrading to 11g), I’ve decided to do a deep dive into the SQL Monitor Report at both Oracle OpenWorld 2011 in October and the UKOUG in December. I think I have some pretty interesting and educational examples, but for anyone willing to share Active SQL Monitor Reports from their system, I thought I would extend the possibility to have it publicly discussed at either one of these sessions (or even a future blog post). Sound cool? I think so, though I may be slightly biased.
Here are some rules, requirements, restrictions, etc.:
In order to participate in this once in a lifetime offer, just email the Active SQL Monitor Report file as an attachment to sqlmon@structureddata.org. If you are going to be attending my session at either OOW11 or UKOUG11, let me know so if I choose your report I’ll notify you so you can bring your friends, significant other, boss, etc. Thanks in advance!
-- -- script to create an Active SQL Monitor Report given a SQL ID -- 11.2 and newer (EM/ACTIVE types are not in 11.1) -- set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feedback off spool sqlmon_4vbqtp97hwqk8.html select dbms_sqltune.report_sql_monitor(report_level=>'ALL', type=>'EM', sql_id=>'4vbqtp97hwqk8') monitor_report from dual; spool off
The summer is flying by and in no time it will be October and that means Oracle OpenWorld 2011 should be on your radar. Once again the Oracle Real-World Performance Group will be hosting three sessions. For those unfamiliar with our presentations, you will get marketing free, no nonsense performance insight of the highest caliber from Oracle’s most elite database performance engineers — the kind of things hard core Oracle people want to hear about. Hope to see you there!
| Session ID: | 13641 (Wednesday Oct. 5th, 10:00) |
| Session Title: | Real-World Performance: The Forklift Upgrade |
| Session Abstract: | Today the motivation to consolidate and migrate existing data and applications into the extreme-high-performance database environments of Oracle Exadata and Oracle Exalogic is being driven by a desire to reduce costs and deliver increased performance and service levels to users. The process is often called a forklift migration, because applications and data are simply picked up and lifted onto new platforms.In this session, Oracle’s Real World Performance group describes how best to maximize your investment and achieve world-class performance and discusses the challenges and compromises that need to be made in the process. |
| Session ID: | 13643 (Tuesday Oct. 4th 13:15) |
| Session Title: | Real-World Performance: How Oracle Does It |
| Session Abstract: | Oracle’s Real-World Performance Group has been achieving world-class performance for clients for more than 15 years. In this session, some of the senior engineers describe the techniques, philosophy, and tools they use to address performance challenges. The session is packed with demos and examples to convey the pragmatism required in real-world performance today. |
| Session ID: | 13640 (Thursday Oct. 6th 10:30) |
| Session Title: | Real-World Performance Questions and Answers |
| Session Abstract: | This is your chance to pose specific questions to Oracle’s Real-World Performance Group. All questions need to be in writing (yes, cards will be provided) and should relate to database performance challenges. In the past, the best questions have been related to specific topics of interest to the audience. |
The crew at My Oracle Support (MOS) [@myoraclesupport] have an excellent starting point for troubleshooting Oracle Exadata. I’d recommend to add this one to your MOS bookmarks.
Oracle Database Machine and Exadata Storage Server Information Center [ID 1306791.1]
Over the past few weeks Oracle Mix had opened the Oracle OpenWorld 2011 Suggest-a-Session to the general public where anyone could submit or vote on a session. One limitation of the Oracle Mix site was that it was impossible to sort the sessions by votes but that challenge was tackled by Roel Hartman with his blog post and APEX demo. After seeing the top session by votes, it was very interesting to me that around half of the top 15 sessions were all from the same author. That got me thinking…and that thinking turned into a little data hacking project that I embarked on. Now I admit it, I think data is very cool, and even cooler is extracting patterns and neat information from data.
The Oracle Mix site is very “crawler friendly” — it has well defined code and tags which made extracting the data fairly painless. The basic process I used came down to this:
I did all of that with curl, wget and some basic regex as a “version 1″ but was hoping to go back and try it again using some more sexy technology like Beautiful Soup. That will have to be continued…
With Oracle Mix Suggest-a-Session, people generally vote for a session for one of two reasons:
What I think is interesting to know is just how much of the voting is done because of #2. After all, Oracle Mix is a social networking site so there certainly is some voting for that reason. In fact, one of the session authors, Yury Velikanov from Pythian, even blogged his story of rounding up votes. The data shows us this, but more on that in just a bit…
The (Unofficial) Data
I took some time to mingle around the data and found some very interesting things. Let’s just start with a few high level points:
Here are some interesting tidbits I extracted from the data set (apologize for not making a cool visualization chart of all this – but I’ll make up for it later):
-- top 10 sessions by total votes: +-------------+-----------------+--------------------------------------------------------------------------------+ | total_votes | session_author | title | +-------------+-----------------+--------------------------------------------------------------------------------+ | 167 | tariq farooq | Oracle RAC Interview Q/A Interactive Competition | | 156 | tariq farooq | Database Performance Tuning: Getting the best out of Oracle Enterprise Manager | | 137 | tariq farooq | Overview & Implementation of Clustering & High Availability with Oracle VM | | 130 | tariq farooq | Migrate Your Online Oracle Database to RAC Using Streams and Data Pump | | 127 | tariq farooq | 360 Degrees - Achieving High Availability for Enterprise Manager Grid Control | | 126 | yury velikanov | Oracle11G SCAN: Sharing successful implementation experience | | 124 | sandip nikumbha | Accelerated Interface Development Approach - Integration Framework | | 123 | tariq farooq | Oracle VM: Overview, Architecture, Deployment Planning & Demo/Exercise | | 123 | sandip nikumbha | Remote SOA - Siebel Local Web Services Implementation | | 119 | yury velikanov | AWR Performance data mining | +-------------+-----------------+--------------------------------------------------------------------------------+ -- top 10 voters (who place the most votes) +--------------------+--------------+ | voter_name | votes_placed | +--------------------+--------------+ | arup nanda | 53 | | tariq farooq | 43 | | connie cservenyak | 36 | | xiaohuan xue | 36 | | bruce elliott | 36 | | peter khoury | 35 | | yugant patra | 35 | | balamohan manickam | 35 | | suresh kuna | 34 | | eddie awad | 34 | +--------------------+--------------+ -- top 10 voters by unique session authors (how many unique authors did they vote for?) +--------------------+----------------+ | name | unique_authors | +--------------------+----------------+ | arup nanda | 28 | | paul guerin | 24 | | eddie awad | 24 | | bruce elliott | 23 | | xiaohuan xue | 23 | | connie cservenyak | 23 | | peter khoury | 22 | | wai ling ng | 22 | | yugant patra | 22 | | balamohan manickam | 22 | +--------------------+----------------+ -- top 10 session authors by total votes received, number of sessions, avg votes per session +---------------------+-------------+----------+-----------------------+ | session_author | total_votes | sessions | avg_votes_per_session | +---------------------+-------------+----------+-----------------------+ | tariq farooq | 1057 | 8 | 132.1250 | | yury velikanov | 557 | 5 | 111.4000 | | alex gorbachev | 429 | 6 | 71.5000 | | sandip nikumbha | 360 | 3 | 120.0000 | | syed jaffar hussain | 281 | 4 | 70.2500 | | kristina troutman | 233 | 5 | 46.6000 | | russell tront | 221 | 3 | 73.6667 | | wendy chen | 217 | 3 | 72.3333 | | asif momen | 184 | 2 | 92.0000 | | alison coombe | 183 | 5 | 36.6000 | +---------------------+-------------+----------+-----------------------+
I could not help noticing that Tariq Farooq had the top 5 spots by total vote count. I would assert that is related to these two points:
I have no doubt there there is some of both in the mix, but just how much influence on the votes is there from a person’s circle of friends? Or to put another way: How many voters only voted for a single session author? Or even more interesting, how many people voted for every session for a single author, and voted for no other sessions? All good questions…with answers that reside in the data!
-- number of users who voted for exactly one author +---------------------------+ | users_voting_for_1_author | +---------------------------+ | 828 | +---------------------------+ -- number of voters who voted for every session by a given author -- and total # of votes per voter is the same # as sessions by an author +-------------------------------------------------+ | users_who_voted_for_every_session_of_an_author | +-------------------------------------------------+ | 826 | +-------------------------------------------------+
Wow – now that interesting! Of people only voting for a single session author, just two of them did not vote for every one of that author’s sessions. That’s community for you!
I was very interested to see what the Mix Voting Graph looked liked, so I imported the voting data into Gephi and rendered a network graph. What I was in search of was to identify the community structure of the voting community. Gephi lets you do this by partitioning the graph into modularity classes so that the communities become visible. This process is similar to how the LinkedIn InMap breaks your professional network into different communities.
Here is what the Oracle Mix voting community looks like:
This is a great visualization of the communities and it accentuates the data from above – the voters who only voted for a single author. This can be seen by the small nodes on the outer part of the graph that have just a single edge between it and the session author’s node. Good examples of this are for Yury Velikanov and Tariq Farooq. Also clearly visible is what I’d refer to the “Pythian and friends” community centered around Alex Gorbachev and Yury Velikanov in the darker green color. There are also several other distinct communities and the color coding makes that visible.
This is my first real data hacking attempt with web data and using some of the tools like Gephi for the graph analysis. One of my inspirations was Neil Kodner‘s Hadoop World 2010 Tweet Analysis, so I need to give a big shout out to Neil for that as well as his help with Gephi. Thanks Neil!
So what are people’s sessions about that were submitted? This Wordle says quite a bit.
If you wish to play on you own: https://github.com/grahn/oow-vote-hacking
Here is another graph where the edges are weighed according to votes to an author (obviously related to number of sessions for that author).
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
OLTP Performance – Concurrent Mid-Tier Connections
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)
Migrate a 1TB Data warehouse in 20 Minutes (Part 2)
Migrate a 1TB Data warehouse in 20 Minutes (Part 3)
Migrate a 1TB Data warehouse in 20 Minutes (Part 4)
Earlier today I exchanged some tweets with @martinberx about some optimizer questions and after posting more information on the ORACLE-L list, I was able to reproduce what he was observing.
DB: 11.2.0.2.0 – 64bit
I have a small query with a little error, which causes big troubles.
The relevant part of the query is
WHERE ….
AND inst_prod_type=003
AND setid=’COM01′but INST_PROD_TYPE is VARCHAR2.
this leads to filter[ (TO_NUMBER("INST_PROD_TYPE")=3 AND "SETID"='COM01') ]
based on this TO_NUMBER ( I guess!) the optimiser takes a fix selectivity of 1%.
Can someone tell me if this 1% is right? Jonathan Lewis “CBO Fundamentals” on page 133 is only talking about character expressions.
Unfortunately there are only 2 distinct values of INST_PROD_TYPE so this artificial [low] selectivity leads to my problem:
An INDEX SKIP SCAN on PS0RF_INST_PROD is choosen. (columns of PS0RF_INST_PROD: INST_PROD_TYPE, SETID, INST_PROD_ID )After fixing the statement to
AND inst_prod_type=’003′
another index is used and the statement performs as expected.Now I have no problem, but want to find the optimizers decisions in my 10053 traces.
From Martin’s email we need to pay close attention to:
From this information I’ll construct the following test case:
create table foo (c1 varchar2(8)); insert into foo select '003' from dual connect by level <= 1000000; insert into foo select '100' from dual connect by level <= 1000000; commit; exec dbms_stats.gather_table_stats(user,'foo');
And using the display_raw function we’ll look at the column stats.
col low_val for a8 col high_val for a8 col data_type for a9 col column_name for a11 select a.column_name, display_raw(a.low_value,b.data_type) as low_val, display_raw(a.high_value,b.data_type) as high_val, b.data_type, a.density, a.histogram, a.num_buckets from user_tab_col_statistics a, user_tab_cols b where a.table_name='FOO' and a.table_name=b.table_name and a.column_name=b.column_name / COLUMN_NAME LOW_VAL HIGH_VAL DATA_TYPE DENSITY HISTOGRAM NUM_BUCKETS ----------- -------- -------- --------- ---------- --------------- ----------- C1 003 100 VARCHAR2 .5 NONE 1
Take note of the lack of a histogram.
Now let’s see what the CBO estimates for a simple query with and without quotes (explicit cast and implicit cast).
SQL> explain plan for select count(*) from foo where c1=003;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1342139204
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 875 (3)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| FOO | 1000K| 3906K| 875 (3)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("C1")=003)
14 rows selected.
SQL> explain plan for select count(*) from foo where c1='003';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1342139204
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 868 (2)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| FOO | 1000K| 3906K| 868 (2)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1"='003')
14 rows selected.
In this case the estimated number of rows is spot on – 1 million rows. Now lets regather stats and because of our queries using C1 predicates, it will become a candidate for a histogram. We can see this from sys.col_usage$.
select oo.name owner,
o.name table_name,
c.name column_name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds,
u.timestamp
from sys.col_usage$ u,
sys.obj$ o,
sys.user$ oo,
sys.col$ c
where o.obj# = u.obj#
and oo.user# = o.owner#
and c.obj# = u.obj#
and c.col# = u.intcol#
and oo.name = 'GRAHN'
and o.name = 'FOO'
/
OWNER TABLE_NAME COLUMN_NAME EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
----- ---------- ----------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
GRAHN FOO C1 1 0 0 0 0 0 2011-06-08 22:29:59
Regather stats and re-check the column stats:
SQL> exec dbms_stats.gather_table_stats(user,'foo'); PL/SQL procedure successfully completed. SQL> select 2 a.column_name, 3 display_raw(a.low_value,b.data_type) as low_val, 4 display_raw(a.high_value,b.data_type) as high_val, 5 b.data_type, 6 a.density, 7 a.histogram, 8 a.num_buckets 9 from 10 user_tab_col_statistics a, user_tab_cols b 11 where 12 a.table_name='FOO' and 13 a.table_name=b.table_name and 14 a.column_name=b.column_name 15 / COLUMN_NAME LOW_VAL HIGH_VAL DATA_TYPE DENSITY HISTOGRAM NUM_BUCKETS ----------- -------- -------- --------- ---------- --------------- ----------- C1 003 100 VARCHAR2 2.5192E-07 FREQUENCY 2
Note the presence of a frequency histogram. Now let’s re-explain:
SQL> explain plan for select count(*) from foo where c1=003;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1342139204
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 875 (3)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| FOO | 1 | 4 | 875 (3)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("C1")=003)
SQL> explain plan for select count(*) from foo where c1='003';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1342139204
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 868 (2)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| FOO | 1025K| 4006K| 868 (2)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1"='003')
And whammy! Note that the implicit cast [ filter(TO_NUMBER("C1")=003) ] now has an estimate of 1 row (when we know there is 1 million).
So what is going on here? Let’s dig into the optimizer trace for some insight:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for FOO[FOO]
Column (#1):
NewDensity:0.243587, OldDensity:0.000000 BktCnt:5458, PopBktCnt:5458, PopValCnt:2, NDV:2
Column (#1): C1(
AvgLen: 4 NDV: 2 Nulls: 0 Density: 0.243587
Histogram: Freq #Bkts: 2 UncompBkts: 5458 EndPtVals: 2
Using prorated density: 0.000000 of col #1 as selectvity of out-of-range/non-existent value pred
Table: FOO Alias: FOO
Card: Original: 2000000.000000 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50
Access Path: TableScan
Cost: 875.41 Resp: 875.41 Degree: 0
Cost_io: 853.00 Cost_cpu: 622375564
Resp_io: 853.00 Resp_cpu: 622375564
Best:: AccessPath: TableScan
Cost: 875.41 Degree: 1 Resp: 875.41 Card: 0.50 Bytes: 0
As you can see from the line
Using prorated density: 0.000000 of col #1 as selectvity of out-of-range/non-existent value pred
The presence of the histogram and the implicit conversion of TO_NUMBER(“C1″)=003 causes the CBO to use a density of 0 because it thinks it’s a non-existent value. The reason for this is that TO_NUMBER(“C1″)=003 is the same as TO_NUMBER(“C1″)=3 and for the histogram the CBO uses TO_CHAR(C1)=’3′ and 3 is not present in the histogram only ’003′ and ’100′.
So, what if the predicate contained a number that was not left padded with zeros, say 100, the other value we put in the table?
SQL> explain plan for select count(*) from foo where c1=100;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1342139204
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 875 (3)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| FOO | 1009K| 3944K| 875 (3)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("C1")=100)
While not exact, the CBO estimate is quite close to the 1 million rows with C1=’100′.
It’s quite clear that Martin’s issue came down to the following:
The combination of these created a scenario where the CBO thinks the value is out-of-range and uses a prorated density of 0 resulting in a cardinality of 1 when there are many more rows than 1.
The moral of the story here is always cast your predicates correctly. This includes explicit cast of date types as well – never rely on the nls settings.
All tests performed on 11.2.0.2.
Since I’ve been on a blogging hiatus for the past few months (and hopefully that will change shortly) I thought I’d mention that the inverview I did with the famous Gwen (Chen) Shapira has now been published in the May 2011 NoCOUG Journal. Hopefully you find it interesting and insightful. Feel free to leave me a comment on your thoughts.
A reader recently left a comment for which my reply was longer than I’d like to leave for a comment so I’m answering it in detail with this blog post.
Greg,
Nice article. I am just reading the Netezza paper.You don’t appear to have debunked the following statement.
“Exadata is unable to process this three table join in its MPP tier and instead must inefficiently move all the data required by the calculation across the network to Oracle RAC.”
Not many queries exist where data is only required from two tables. Are Oracle suggesting we need to change the way data is structured to enable best use of Exadata – increasing TCO significantly?
Thanks & Nice post.
There is a reason that I did not debunk that statement – it did not exist in the original version of Netezza’s paper. It seems they have taken the shopping basket example that I debunked in my previous post and replaced it with this one. Nonetheless lets take a look at Netezza’s claim:
Exadata’s storage tier provides Bloom filters to implement simple joins between one large and one smaller table, anything more complex cannot be processed in MPP. Analytical queries commonly require joins more complex than those supported by Exadata. Consider the straightforward case of an international retailer needing insight to the dollar value of sales made in stores located in the UK. This simple SQL query requires a join across three tables – sales, currency and stores.
select sum(sales_value * exchange_rate) us_dollar_sales from sales, currency, stores where sales.day = currency.day and stores.country = 'UK' and currency.country = 'USA'Exadata is unable to process this three table join in its MPP tier and instead must inefficiently move all the data required by the calculation across the network to Oracle RAC.
Before I comment, did you spot the error with the SQL query? Hint: Count the number of tables and joins.
Now that we can clearly see that Netezza marketing can not write good SQL because this query contains a cross product as there is no JOIN between sales and stores thus the value returned from this query is not “the [US] dollar value of sales made in stores located in the UK”, it’s some other rubbish number.
Netezza is trying to lead you to believe that sending data to the database nodes (running Oracle RAC) is a bad thing, which is most certainly is not. Let’s remember what Exadata is – Smart Storage. Exadata itself is not an MPP database, so of course it needs to send some data back to the Oracle database nodes where the Oracle database kernel can use Parallel Execution to easily parallelize the execution of this query in an MPP fashion efficiently leveraging all the CPUs and memory of the database cluster.
The reality here is that both Netezza and Oracle will do the JOIN in their respective databases, however, Oracle can push a Bloom filter into Exadata for the STORES.COUNTRY predicate so that the only data that is returned to the Oracle database are rows matching that criteria.
Let’s assume for a moment that the query is correctly written with two joins and the table definitions look like such (at least the columns we’re interested in):
create table sales (
store_id number,
day date,
sales_value number
);
create table currency (
day date,
country varchar2(3),
exchange_rate number
);
create table stores (
store_id number,
country varchar2(3)
);
select
sum(sales.sales_value * currency.exchange_rate) us_dollar_sales
from
sales,
currency,
stores
where
sales.day = currency.day
and sales.store_id = stores.store_id
and stores.country = 'UK'
and currency.country = 'USA'
For discussion’s sake, let’s assume the following:
There is no magic in those numbers, it’s just something to add context to the discussion, so don’t think I picked them for some special reason. Could be more, could be less, but it really doesn’t matter.
So if we think about the the cardinality for the tables:
The table JOIN order should be STORES -> SALES -> CURRENCY.
With Exadata what will happen is such:
All of these operations are performed in parallel using Oracle’s Parallel Execution.
Netezza suggests that Exadata can use Bloom filters for only two table joins (1 big, 1 small) and that analytical queries are more complex than that so Exadata can not use a Bloom filter and provide an example to suggest such. The reality is not only is their example incorrectly written SQL, it also works great with Exadata Bloom filters and it is more than 2 tables! In addition, it is a great demonstration of efficient and smart data movement as Exadata can smartly filter using Bloom filters and needs to only project a very few columns, thus likely creating a big savings versus sending all the columns/rows from the storage. Thus Exadata Bloom filters can work with complex analytical queries of more than two tables and efficiently send data across the network to the Oracle RAC cluster where Parallel Execution will work on the JOINs and aggregation in an MPP manor.
Now to specifically answer your question: No, Oracle is not suggesting you need to change your data/queries to support two table joins, Exadata will likely work fine with what you have today. And to let you and everyone else in on a little secret: Exadata actually supports applying multiple Bloom filters to a table scan (we call this a Bloom filter list denoted by the Predicate Information of a query plan by SYS_OP_BLOOM_FILTER_LIST), so you can have multiple JOIN filters being applied in the Exadata storage, so in reality Bloom filters are not even limited to just 2 table JOINs.
Oh well, so much for Netezza competitive marketing. Just goes to show that Netezza has a very poor understanding how Exadata really works (yet again).
Over the past few weeks several people have asked me about an Exadata article entitled “Making the Most of Oracle Exadata” by Marc Fielding of Pythian. Overall it’s an informative article and touches on many of the key points of Exadata, however, even though I read (skimmed is a much better word) and briefly commented on the article back in August, after further review I found some technical inaccuracies with this article so I wanted to take the time to clarify this information for the Exadata community.
Marc writes:
Smart scans: Smart scans are Exadata’s headline feature. They provide three main benefits: reduced data transfer volumes from storage servers to databases, CPU savings on database servers as workload is transferred to storage servers, and improved buffer cache efficiency thanks to column projection. Smart scans use helper processes that function much like parallel query processes but run directly on the storage servers. Operations off-loadable through smart scans include the following:
- Predicate filtering – processing WHERE clause comparisons to literals, including logical operators and most SQL functions.
- Column projection – by looking at a query’s SELECT clause, storage servers return only the columns requested, which is a big win for wide tables.
- Joins – storage servers can improve join performance by using Bloom filters to recognize rows matching join criteria during the table scan phase, avoiding most of the I/O and temporary space overhead involved in the join processing.
- Data mining model scoring – for users of Oracle Data Mining, scoring functions like PREDICT() can be evaluated on storage servers.
I personally would not choose a specific number of benefits from Exadata Smart Scan, simply stated, the design goal behind Smart Scan is to reduce the amount of data that is sent from the storage nodes (or storage arrays) to the database nodes (why move data that is not needed?). Smart Scan does this in two ways: it applies the appropriate column projection and row restriction rules to the data as it streams off of disk. However, projection is not limited to just columns in the SELECT clause, as Marc mentions, it also includes columns in the WHERE clause as well. Obviously JOIN columns need to be projected to perform the JOIN in the database nodes. The one area that Smart Scan does not help with at all is improved buffer cache efficiency. The reason for this is quite simple: Smart Scan returns data in blocks that were created on-the-fly just for that given query — it contains only the needed columns (projections) and has rows filtered out from the predicates (restrictions). Those blocks could not be reused unless someone ran the exact same query (think of those blocks as custom built just for that query). The other thing is that Smart Scans use direct path reads (cell smart table scan) and these reads are done into the PGA space, not the shared SGA space (buffer cache).
As most know, Exadata can easily push down simple predicates filters (WHERE c1 = ‘FOO’) that can be applied as restrictions with Smart Scan. In addition, Bloom Filters can be applied as restrictions for simple JOINs, like those commonly found in Star Schemas (Dimensional Data Models). These operations can be observed in the query execution plan by the JOIN FILTER CREATE and JOIN FILTER USE row sources. What is very cool is that Bloom Filters can also pass their list of values to Storage Indexes to aid in further I/O reductions if there is natural clustering on those columns or it eliminates significant amounts of data (as in a highly selective set of values). Even if there isn’t significant data elimination via Storage Indexes, a Smart Scan Bloom Filter can be applied post scan to prevent unneeded data being sent to the database servers.
Marc writes:
Storage indexes: Storage indexes reduce disk I/O volumes by tracking high and low values in memory for each 1-megabyte storage region. They can be used to give partition pruning benefits without requiring the partition key in the WHERE clause, as long as one of these columns is correlated with the partition key. For example, if a table has order_date and processed_date columns, is partitioned on order_date, and if orders are processed within 5 days of receipt, the storage server can track which processed_date values are included in each order partition, giving partition pruning for queries referring to either order_date or processed_date. Other data sets that are physically ordered on disk, such as incrementing keys, can also benefit.
In Marc’s example he states there is correlation between the two columns PROCESSED_DATE and ORDER_DATE where PROCESSED_DATE = ORDER_DATE + [0..5 days]. That’s fine and all, but to claim partition pruning takes place when specifying ORDER_DATE (the partition key column) or PROCESSED_DATE (non partition key column) in the WHERE clause because the Storage Index can be used for PROCESSED_DATE is inaccurate. The reality is, partition pruning can only take place when the partition key, ORDER_DATE, is specified, regardless if a Storage Index is used for PROCESSED_DATE.
Partition Pruning and Storage Indexes are completely independent of each other and Storage Indexes know absolutely nothing about partitions, even if the partition key column and another column have some type of correlation, as in Marc’s example. The Storage Index simply will track which Storage Regions do or do not have rows that match the predicate filters and eliminate reading the unneeded Storage Regions.
Marc writes:
Columnar compression: Hybrid columnar compression (HCC) introduces a new physical storage concept, the compression unit. By grouping many rows together in a compression unit, and by storing only unique values within each column, HCC provides storage savings in the range of 80 90% based on the compression level selected. Since data from full table scans remains compressed through I/O and buffer cache layers, disk savings translate to reduced I/O and buffer cache work as well. HCC does, however, introduce CPU and data modification overhead that will be discussed in the next section.
The Compression Unit (CU) for Exadata Hybrid Columnar Compression (EHCC) is actually a logical construct, not a physical storage concept. The compression gains from EHCC come from column-major organization of the rows contained in the CU and the encoding and transformations (compression) that can be done because of that organization (like values are more common within the same column across rows, vs different columns in the same row). To say EHCC only stores unique values within each column is inaccurate, however, the encoding and transformation algorithms use various techniques that yield very good compression by attempting to represent the column values with as few bytes as possible.
Data from EHCC full table scans only remains fully compressed if the table scan is not a Smart Scan, in which case the compressed CUs are passed directly up to the buffer cache and the decompression will then be done by the database servers. However, if the EHCC full table scan is a Smart Scan, then only the columns and rows being returned to the database nodes are decompressed by the Exadata servers, however, predicate evaluations can be performed directly on the EHCC compressed data.
Read more: Exadata Hybrid Columnar Compression Technical White Paper
Marc also writes:
Use columnar compression judiciously: Hybrid columnar compression (HCC) in Exadata has the dual advantages of reducing storage usage and reducing I/O for large reads by storing data more densely. However, HCC works only when data is inserted using bulk operations. If non-compatible operations like single-row inserts or updates are attempted, Exadata reverts transparently to the less restrictive OLTP compression method, losing the compression benefits of HCC. When performing data modifications such as updates or deletes, the entire compression unit must be uncompressed and written in OLTP-compressed form, involving an additional disk I/O penalty as well.
EHCC does require bulk direct path load operations to work. This is because the compression algorithms that are used for EHCC need sets of rows as input, not single rows. What is incorrect with Marc’s comments is that when a row in a CU is modified (UPDATE or DELETE), the entire CU is not uncompressed and changed to non-EHCC compression, only the rows that are UPDATED are migrated to non-EHCC compression. For DELETEs no row migrations take place at all. This is easily demonstrated by tracking ROWIDs as in the example at the bottom of this post.
Marc writes:
Flash cache: Exadata s flash cache supplements the database servers buffer caches by providing a large cache of 384 GB per storage server and up to 5 TB in a full Oracle Exadata Database Machine, considerably larger than the capacity of memory caches. Unlike generic caches in traditional SAN storage, the flash cache understands database-level operations, preventing large non-repeated operations such as backups and large table scans from polluting the cache. Since flash storage is nonvolatile, it can cache synchronous writes, providing performance benefits to commit-intensive applications.
While flash (SSD) storage is indeed non-volatile, the Exadata Smart Flash Cache is volatile – it loses all of its contents if the Exadata server is power cycled. Also, since the Exadata Smart Flash is currently a write-through cache, it offers no direct performance advantages to commit-intensive applications, however, it does offer indirect performance advantages by servicing read requests that would otherwise be serviced by the HDDs, thus allowing the HDDs to service more write operations.
Read more: Exadata Smart Flash Cache Technical White Paper
--
-- EHCC UPDATE example - only modified rows migrate
--
SQL> create table order_items1
2 compress for query high
3 as
4 select rownum as rnum, x.*
5 from order_items x
6 where rownum <= 10000;
Table created.
SQL> create table order_items2
2 as
3 select rowid as rid, x.*
4 from order_items1 x;
Table created.
SQL> update order_items1
2 set quantity=10000
3 where rnum in (1,100,1000,10000);
4 rows updated.
SQL> commit;
Commit complete.
SQL> select b.rnum, b.rid before_rowid, a.rowid after_rowid
2 from order_items1 a, order_items2 b
3 where a.rnum(+) = b.rnum
4 and (a.rowid != b.rid
5 or a.rowid is null)
6 order by b.rnum
7 ;
RNUM BEFORE_ROWID AFTER_ROWID
--------------- ------------------ ------------------
1 AAAWSGAAAAAO1aTAAA AAAWSGAAAAAO1aeAAA
100 AAAWSGAAAAAO1aTABj AAAWSGAAAAAO1aeAAB
1000 AAAWSGAAAAAO1aTAPn AAAWSGAAAAAO1aeAAC
10000 AAAWSGAAAAAO1aXBEv AAAWSGAAAAAO1aeAAD
--
-- EHCC DELETE example - no rows migrate
--
SQL> create table order_items1
2 compress for query high
3 as
4 select rownum as rnum, x.*
5 from order_items x
6 where rownum <= 10000;
Table created.
SQL> create table order_items2
2 as
3 select rowid as rid, x.*
4 from order_items1 x;
Table created.
SQL> delete from order_items1
2 where rnum in (1,100,1000,10000);
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select b.rnum, b.rid before_rowid, a.rowid after_rowid
2 from order_items1 a, order_items2 b
3 where a.rnum(+) = b.rnum
4 and (a.rowid != b.rid
5 or a.rowid is null)
6 order by b.rnum
7 ;
RNUM BEFORE_ROWID AFTER_ROWID
--------------- ------------------ ------------------
1 AAAWSIAAAAAO1aTAAA
100 AAAWSIAAAAAO1aTABj
1000 AAAWSIAAAAAO1aTAPn
10000 AAAWSIAAAAAO1aXBEv
You’ve probably heard sayings like “sometimes things aren’t always what they seem” and “people lie”. Well, sometimes execution plans lie. It’s not really by intent, but it is sometimes difficult (or impossible) to represent everything in a query execution tree in nice tabular format like dbms_xplan gives.
One of the optimizations that was introduced back in 10gR2 was the use of bloom filters. Bloom filters can be used in two ways: 1) for filtering or 2) for partition pruning (bloom pruning) starting with 11g. Frequently the data models used in data warehousing are dimensional models (star or snowflake) and most Oracle warehouses use simple range (or interval) partitioning on the fact table date key column as that is the filter that yields the largest I/O reduction from partition pruning (most queries in a time series star schema include a time window, right!). As a result, it is imperative that the join between the date dimension and the fact table results in partition pruning.
Let’s consider a basic two table join between a date dimension and a fact table. For these examples I’m using STORE_SALES and DATE_DIM which are TPC-DS tables (I frequently use TPC-DS for experiments as it uses a dimensional (star) model and has a data generator.) STORE_SALES contains a 5 year window of data ranging from 1998-01-02 to 2003-01-02.
For this example I used range partitioning on STORE_SALES.SS_SOLD_DATE_SK using 60 one month partitions (plus 1 partition for NULL SS_SOLD_DATE_SK values) that align with the date dimension (DATE_DIM) on calendar month boundaries. STORE_SALES has the parallel attribute (PARALLEL 16 in this case) set on the table to enable Oracle’s Parallel Execution (PX). Let’s look at the execution time and plan for our test query:
SQL> select
2 max(ss_sales_price)
3 from
4 store_sales ss,
5 date_dim d
6 where
7 ss_sold_date_sk = d_date_sk and
8 d_year = 2000
9 ;
MAX(SS_SALES_PRICE)
-------------------
200
Elapsed: 00:00:41.67
SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +parallel +partition +predicate'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select max(ss_sales_price) from store_sales ss, date_dim d where
ss_sold_date_sk=d_date_sk and d_year = 2000
Plan hash value: 934332680
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | SORT AGGREGATE | | | | | | |
| 2 | PX COORDINATOR | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | | | Q1,01 | PCWP | |
|* 5 | HASH JOIN | | | | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | Q1,01 | PCWC | |
| 7 | PART JOIN FILTER CREATE| :BF0000 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | | | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | | | | S->P | BROADCAST |
|* 10 | TABLE ACCESS FULL | DATE_DIM | | | | | |
| 11 | PX BLOCK ITERATOR | |:BF0000|:BF0000| Q1,01 | PCWC | |
|* 12 | TABLE ACCESS FULL | STORE_SALES |:BF0000|:BF0000| Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SS_SOLD_DATE_SK"="D_DATE_SK")
10 - filter("D_YEAR"=2000)
12 - access(:Z>=:Z AND :Z<=:Z)
In this execution plan you can see the creation of the bloom filter on line 7 which is populated from the values of D_DATE_SK from DATE_DIM. That bloom filter is then used to partition prune on the STORE_SALES table. This is why we see :BF0000 in the Pstart/Pstop columns.
For the next experiment, I kept the same range partitioning scheme but also added hash subpartitioning using the SS_ITEM_SK column (using 4 hash subpartitions per range partition). STORE_SALES2 has 61 range partitions x 4 hash subpartitions for a total of 244 aggregate partitions. Let’s look at the execution plan for our test query:
SQL> select
2 max(ss_sales_price)
3 from
4 store_sales2 ss,
5 date_dim d
6 where
7 ss_sold_date_sk = d_date_sk and
8 d_year = 2000
9 ;
MAX(SS_SALES_PRICE)
-------------------
200
Elapsed: 00:00:41.06
SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +parallel +partition +predicate'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select max(ss_sales_price) from store_sales2 ss, date_dim d where
ss_sold_date_sk=d_date_sk and d_year = 2000
Plan hash value: 2496395846
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | SORT AGGREGATE | | | | | | |
| 2 | PX COORDINATOR | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | | | Q1,01 | PCWP | |
|* 5 | HASH JOIN | | | | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | Q1,01 | PCWC | |
| 7 | PART JOIN FILTER CREATE| :BF0000 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | | | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | | | | S->P | BROADCAST |
|* 10 | TABLE ACCESS FULL | DATE_DIM | | | | | |
| 11 | PX BLOCK ITERATOR | | 1 | 4 | Q1,01 | PCWC | |
|* 12 | TABLE ACCESS FULL | STORE_SALES2 | 1 | 244 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SS_SOLD_DATE_SK"="D_DATE_SK")
10 - filter("D_YEAR"=2000)
12 - access(:Z>=:Z AND :Z<=:Z)
Once again you can see the creation of the bloom filter from DATE_DIM on line 7, however you will notice that we no longer see :BF0000 as our Pstart and Pstop values. In fact, it may appear that partition pruning is not taking place at all as we see 1/244 as our Pstart/Pstop values. However, if we compare the execution times between the range and range/hash queries you note they are identical to the nearest second, thus there really is no way that partition (bloom) pruning is not taking place. After all, if this plan read all 5 years of data it would take 5 times as long as reading just 1 year and that certainly is not the case. Would you have guessed that partition pruning is taking place had we not worked though the range only experiment first? Hmmm…
Before we dive in, let’s quickly look at what the execution plans would look like if PX was not used (using serial execution).
--
-- Range Partitioned, Serial Execution
--
---------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | | |
|* 2 | HASH JOIN | | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | | |
|* 4 | TABLE ACCESS FULL | DATE_DIM | | |
| 5 | PARTITION RANGE JOIN-FILTER| |:BF0000|:BF0000|
| 6 | TABLE ACCESS FULL | STORE_SALES |:BF0000|:BF0000|
---------------------------------------------------------------------
--
-- Range-Hash Composite Partitioned, Serial Execution
--
----------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | | |
|* 2 | HASH JOIN | | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | | |
|* 4 | TABLE ACCESS FULL | DATE_DIM | | |
| 5 | PARTITION RANGE JOIN-FILTER| |:BF0000|:BF0000|
| 6 | PARTITION HASH ALL | | 1 | 4 |
| 7 | TABLE ACCESS FULL | STORE_SALES2 | 1 | 244 |
----------------------------------------------------------------------
When using composite partitioning, pruning is placed on one of the partition iterators. When the two nested partition iterators (range/hash in this case) are changed into a block iterator (line 14 – PX BLOCK ITERATOR), we have to pick a “victim” in the query plan tree since only one node in the plan needs now to carry the pruning information (with PX the pruning is really done by the QC, not the row source like in serial plans). As a result, the information associated the the victimized partition iterator is lost in the explain plan. This is why there is no :BF0000 for Pstart/Pstop in the plan in this case. It is probably more accurate to have the parallel plans for both range and range/hash look like this:
--------------------------------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | | 1 | SORT AGGREGATE | | | | | | | | 2 | PX COORDINATOR | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | | | Q1,01 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | | | Q1,01 | PCWP | | |* 5 | HASH JOIN | | | | Q1,01 | PCWP | | | 6 | BUFFER SORT | | | | Q1,01 | PCWC | | | 7 | PART JOIN FILTER CREATE| :BF0000 | | | Q1,01 | PCWP | | | 8 | PX RECEIVE | | | | Q1,01 | PCWP | | | 9 | PX SEND BROADCAST | :TQ10000 | | | | S->P | BROADCAST | |* 10 | TABLE ACCESS FULL | DATE_DIM | | | | | | | 11 | PX BLOCK ITERATOR | | | | Q1,01 | PCWC | | |* 12 | TABLE ACCESS FULL | STORE_SALES |:BF0000|:BF0000| Q1,01 | PCWP | | ---------------------------------------------------------------------------------------------------
Where the bloom pruning is on the TABLE ACCESS FULL row source. This is because there is no Pstart/Pstop for a PX BLOCK ITERATOR row source (it’s block ranges, so partition information is lost – it had been contained in level above this).
Hopefully this helps you understand and correctly identify execution plans contain bloom pruning even though at first glance you may not think they do. If you are uncertain, use the execution stats for the query looking at metrics like amount of data read and execution times to provide some empirical insight.