Search

Top 60 Oracle Blogs

Recent comments

January 2011

Finding Unnecessary Effective Date Processing in PS/Query

In PeopleSoft for the Oracle DBA (Ch11, p309) I commented on how PS/Query will automatically add effective date criteria to and EFFDT column, even if it is not a key column. 

PS/Query does warn you that it has added the criteria, but it won't warn that the column is not a key column

Latest Oracle Security Critical Patch Update is out

The most recent patch Tuesday happened yesterday for Oracle. The sizes of the patches are increasing but that is due solely to the recent aquisition of Sun. The database security patches are the area where I have an interest and....[Read More]

Posted by Pete On 19/01/11 At 11:10 AM

Solaris Eye for the Linux Guy… Part II (oprofile, Dtrace, and Oracle Event Trace)

Proper tool for the job

My grandfather used to say to me: “Use the proper tool for the job”.  This is important to keep in mind when faced with performance issues.  When I am faced with performance problems in Oracle, I typically start at a high level with AWR reports or Enterprise Manager to get a high level understanding of the workload.   To drill down further, the next step is to use Oracle “10046 event” tracing.  Cary Millsap created a methodology around event tracing called “Method-R” which shows how to focus in on the source of a performance problem by analyzing the components that contribute to response time.   These are all fine places to start to analyze performance problems from the “user” or “application” point of view.  But what happens if the OS is in peril?

Cost

As soon as I saw the title of Jonathan Lewis' post, I had an inkling of what it might have to say and I wasn't too far off the mark. Although I don't disagree with a single statement of his post (I've read it a few times to make sure), I tend to take a different line at work because I keep finding myself in conversations with perfectly professional, bright and knowledgeable people that go something like this

Them: 'So it's picking this new plan and it's much slower than the old plan, but the new plan has a much lower cost.'

Me: 'Of course it has a lower cost. That's why it's picked it. That's what a cost-based optimiser does.'

Them: 'But it's slower'

Me: 'Well spotted'

Them: 'Well then why is the cost lower?'

Now at this point there are diverging paths you could take and it's utterly valid to find out *why* the calculations haven't delivered the best plan. The cost *is* the result of a calculation and not an arbritary number plucked from the air. The optimiser selected the 'bad' plan because it had the lowest cost.

To quote Jonathan

'Cost IS time - but only in theory.'

However, it's equally valid to say, I don't care why the cost is wrong, I *know* the plan that performs most efficiently and that's the plan I want the server to use. In fact, I'd suggest that *most* SQL performance problems I look at have at their very core a plan with a lower cost that doesn't deliver the best response time! My users couldn't care less about optimiser calculations. (I use the cliche carefully - they probably don't care at all.)

What isn't easy for me is to watch people spend a day aiming for the lowest cost, brandish it at me proudly and then be disappointed when it runs more slowly than the previous version!

Them: 'But look at the cost!'

Me: 'Who cares? It's slower.'

My take on it is this. Cost is the output of a model designed to deliver the lowest execution time but sometimes the model gets things wrong and, in the face of actual run times, I'll take reality over the model every time. When people arrive at my desk (perhaps electronically) with a SQL performance problem, one of the first things I say if the conversation begins like that one above is

"Ignore the cost!"

Jonathan's telling the truth and the true answer to a question is important, as long as you understand what the original question was. I know from experience that many people react to the cost without understanding how the optimiser arrives at that answer. Of course, it could be that I happen to see the problem queries, where the optimiser calculation isn't working out too well, but I come across plenty of them and it would save a lot of time if people didn't spend so long arguing about costs!

Cost is a fundamental metric if you care about the CBO, develop it, write about it (Jonathan) or are trying to work out what the hell it's doing (anyone who has ever looked at a 10053 trace in desperation). But as a performance metric? I'm not sure it's very important at all.

How about time?

P.S. No more blog posts for a while now. That Hotsos presentation looms large. Although, now I've said that, I'll probably contradict myself ....

P.P.S. I should say that I've missed the main thrust of Jonathan's post and picked up on the bit that suited me. Really, it was about this ...

"As I’ve pointed out in the past, “Cost is Time”. The cost of a query represents the optimizer’s estimate of how long it will take that query to run – so it is perfectly valid  to compare the cost of two queries to see which one the optimizer thinks will be faster but, thanks to limitations and defects in the optimizer it may not be entirely sensible to do so.

The point I want to address in this post though is the comment that 'it’s valid to compare the cost of different plans, but not to compare the cost of two different queries'."

'Valid' and 'sensible'. I liked that and I hope it's what people absorbed.

[Late update: I happened to look at AskTom today. I don't look as often now that the pace of interesting follow-ups has slowed down. That's no bad thing. Lots of things have been answered (for now) and I still search on old threads constantly. Anyway, I should have known that this subject would crop up there.]

Debunking More Netezza FUD About Exadata

class="tweetmeme_button" style="float: right; margin-left: 10px;"> /> />

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.

Gabramel writes:

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 1 year (365 days) in the SALES table of billions of rows
  • There are 5000 stores in the UK (seems like a realistic number to me)

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:

  • STORES has a cardinality of 5000 rows
  • CURRENCY has a cardinality of 365 rows (1 year)

The table JOIN order should be STORES -> SALES -> CURRENCY.

With Exadata what will happen is such:

  • Get STORE_IDs from STORE where COUNTRY = ‘UK’
  • Build a Bloom Filter of these 5000 STORE_IDs and push them into Exadata
  • Scan SALES and apply the Bloom Filter in storage, retuning only rows for UK STORE_IDs and project only the necessary columns
  • JOIN that result to CURRENCY
  • Compute the SUM aggregate

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).

name="googleone_share_1" style="position:relative;z-index:5;float: right; margin-left: 10px;">{lang: 'ar'} class='wpfblike' >

Teradata Join Index

TeradataのJoinは基本的にはHash Joinだけ。OracleでサポートされているNested Loop Join(NLJ)やBit Map Joinなどはない。しかし、DWHとしてExadataを使う場合にNLJなどが動いてしまったら返ってマイナスに働く。そういった意味でDWHシステムではHash Joinのみを使う。

Join Index(参考資料:Teradata Database : Database Desigin(B035-1094-115A))
TeradataのJoinはHash Joinのみだが、Joinした状況でCompressされたTableを持つ機能がJoin Index機能です。実態を持っているという点でOracleのMaterialized Viewと同じだが、TeradataではView名などを意識しないで自然にアクセスができる。

以下のようなJoin Indexを作っておくと:

CREATE JOIN INDEX cust_ord2
AS SELECT cust.customerid,cust.loc,ord.ordid,item,qty,odate
FROM cust, ord, orditm
WHERE cust.customerid = ord.customerid
AND ord.ordid = orditm.ordid;

以下のようなパターンはJoin Indexを使って処理される:


SELECT cust.customerid, ord.ordid, item, qty
FROM cust, ord, orditm
WHERE cust.customerid = ord.customerid
AND ord.ordid = orditm.ordid
AND cust.loc = 'WI';

I’m an Android…

I wrote recently about the whole Apple vs Android thing and announced I was not in either camp. Recent family issues have meant I’m on the phone a lot more now and the “pay as you go” thing just isn’t making sense. I ventured into the murky world of phone contracts and came out with a HTC Wildfire, which is about the cheapest phone/contract you can get that in my opinion can still be called a smartphone. In fact, you can get them so cheap that they really are an entry level phone now.

The iPhone 4 was never really in the running because it is so darn expensive. I did the Pepsi challenge between the HTC Desire HD, Samsung Galaxy S and the HTC Wildfire and I picked the latter. They are all Android phones, so there was really not much difference in user experience. The Desire was the biggest, which in my opinion was its downfall. It feels like you are holding a tablet to your ear. Obviously, having such a big screen makes web stuff easier, but I’ve got my iPad for that, so it’s not really that relevant for me. The Galaxy S was a bit smaller, but still massive. The Wildfire actually felt like a phone and boy was it cheap compared to the others. That kinda swung it for me. When doing anything with the internet my preference is always, “Desktop > Laptop > iPad > Phone”, so I can’t see the phone getting much use as a smartphone, so paying through the nose for it just doesn’t seem sensible for me.

Negotiating the whole phone contract thing is a bloody nightmare. It’s like haggling with Cut -Me-Own-Throat Dibbler. Armed with lots of information from competitors websites, I ended up with the Wildfire with an unlimited data plan for less that their website was offering dumb phones with no data plans. As I said before, it really is an entry level device now.

A couple of days in and the novelty has worn off and I’m completely bored with the phone. I’m so glad I didn’t spend more money on something fancy. :)

Cheers

Tim…

Row Level Locking in the PeopleTools Component Processor

My apologies in advance, but this entry is a bit of a rant.  It is one of those things in PeopleSoft about which you do nothing, but it still is interesting to know because it reveals something of how it works under the covers.

In the PeopleSoft for the DBA, I commented on how the columns updated in the UPDATE statements issued by the Component Processor at save time contain only the fields that have been updated in the component.  You get different update statements depending upon what you update in the component, but the alternative is to update all the columns, possibly to the same value, and that would generate additional unnecessary redo.

Row Level Locking in the PeopleTools Component Processor

My apologies in advance, but this entry is a bit of a rant.  It is one of those things in PeopleSoft about which you do nothing, but it still is interesting to know because it reveals something of how it works under the covers.

In the PeopleSoft for the DBA, I commented on how the columns updated in the UPDATE statements issued by the Component Processor at save time contain only the fields that have been updated in the component.  You get different update statements depending upon what you update in the component, but the alternative is to update all the columns, possibly to the same value, and that would generate additional unnecessary redo.