Ho-ho-ho!

Check it out! All of us at Hotsos have some seasons greetings for you at the Hotsos web site!

Happy holidays everyone!

-------------------------------------------------------------------------------

The 2010 Hotsos Symposium

Hey everyone the Hotsos Sym is coming soon! March 7^th to the 11^th of 2010. There is a great group off speakers, including me! (Wow, how’d I slip in again, someone must not have been watching carefully! Hehehe.. ) Make sure you are there and sign up now.

Click here for a link to the official page.

Make sure to check out the videos on the page.

Tom Kyte is the Keynote and is sure to deliver an awesome presentation he will also do two more presentations during the Symposium. Tanel Põder as the trainer for the optional training day on the 11^th. Make sure to sign up for the training day when you sign up. Seating is limited for the training day, don’t miss it. Tanel is a fantastic presenter and trainer. His presentations are standing room only around the world. Don't miss this opportunity.

I hear the party night is schedule to be Disco!

Where are my white bell-bottom slacks, vest, big collar shirt and jacket.... Staying-alive-ah-ah-ah!

I was recently in Houston TX teaching for two weeks and had the excellent opportunity to eat at a TEXAS ROADHOUSE while I was there. Yes it is a chain and sort of more know for stakes rather then ribs, but these were darn good ribs. I'm adding them to my list of restaurants worth going to if I find one in a town I'm in. And heck with free peanuts that you toss the shells on the floor, yeehaw! Count me in! :-)

Yes, this is the performance problem.

Recently in a class of mine we were all reviewing a trace file (10046) of a transaction that ran on the client’s system. There was one SQL statement that consumed about 70% of the total time for the run. The statement was rather simple one table and one predicate. The predicate was a concatenation of several columns (each had an index) with an equality operator to a concatenation of a couple of binds.

The predicate looked something like this (it was longer and slightly more complicated, then this but this captures the flavor):

where COL1 || '-' || COL2 = :B1 || '-' || :B2

Pretty clearly the Optimizer can’t use the indexes on the columns because they are concatenated together in the predicate. The STAT lines weren’t in the trace data so we couldn’t see for sure but it was likely doing a full table scan which was driving about 60,000 LIOs for the statement. After some discussion several options were bantered about on how to fix the SQL, with a new index and rewriting the predicate being the top two.

As we were finishing up the discussion, the person who brought in the trace said “But this statement isn’t the performance problem.”

To which I replied “Oh yes it is.”

This is something that I’ve experienced before. Folks will think another SQL statement is the problem before they start and when it isn’t they still focus on the statement they think is the problem. We all do it. We have a preconceived idea of what the problem is and when faced with something else, we have a hard time getting over our preconceived idea.

This is the root of why having some one with little knowledge of the system can seem to have such great insight. Really it’s just that they have no preconceived idea of what is wrong. They can then follow the clues without prejudice and see what everyone else may have missed.

Cardinal Thomas Wosley (1471-1530) was an advisor to King Henry the VIII. He gave the King the advice of "Be very, very careful what you put into that head, because you will never, ever get it out." Several 100 years later this advice still rings true.

Temperatures

Living in the Great State of Michigan in the USA gives me the opportunity to experience weather in all its ways. This is my thermometer on my back porch.

Ribs In New Jersey

I'm here in New Jersey the Garden state of the USA. Went out for some good ribs at Houston's (181 Riverside Sq Mall Hackensack, NJ). Quite good. Very nice atmosphere and friendly staff. Although I do prefer a dry rub on the ribs the sauce they used was excellent. I also really liked the shoe string fries, they were nice and crunchy. The coleslaw was different from my norm and quite tasty as well. Great meal!

Were you there?

Click here for a video of the 2009 Hotsos Symposium.

Will you be there next year?

BBQ in Phoenix AZ

Ahhh... a great lunch at the BBQ Company here in Phoenix AZ. I got a 1/3 rack of ribs for lunch with some sweet beans and a corn medley. Excellent sauce, and the corn medley was particularly good. The only bummer about this place is that it's only open for lunch. This is now added to the list of places to get to while in Phoenix.



OK, now back to some SQL optimization... How many LIOs is that thing doing?!?!

Breakfast at Matt's

One of my other favorite foods is Breakfast Food and in particular Waffles. Matt's Big Breakfast has the best Waffles I've ever had. And the other food there is excellent as well. I try to get to Matt's at least once each time I'm in the Phoenix/Tempe area in Arizona. This morning was the day for this week and my waffle was excellent as always!!



They open at 06:30 and the best thing to do is to be there at opening if you want a seat. There aren't many and they go fast!

The SQL runs fast but....

For the most part I don't think any one would get to excited about dealing with a SQL statement that runs in .3 seconds. Most of us are worried about the statements that run for hours and we tend to focus on them. Which is not necessarily a bad thing. But how many statements on your system fit into this category I'm about to describe?

This statement on a per execution runs in .336 seconds, and does 1,627 Logical IOs. It's a pretty simple 3 way table join. All the predicates are simple equality ones that are ANDed together. The kicker is that this statement was called 5,935 times in a 40 minute window for a total of 9,658,869 LIOs and 275,527 Physical IOs and didn't return any rows. For this 40 minute trace this statement consumed 69% of the total response time and nearly all the PIOs via Sequential Read events. All this to get back nothing.

Maybe the statement can be optimized to do less work, but the real question is why do this at all? Is there a way to avoid running this statement at all?

It's easy to see why no one would have even looked at this SQL in the conventional tuning type engagement. It's fast, the LIOs per execution aren't necessarily bad either given that the tables involved are rather large. Looking at it from a per execution basis, there isn't much reason to get excited. But when looked at with in the context of a running application, it's easy to see that this statement is doing a lot of work for nothing.

Use the Force!

I was out this morning and saw this group at a local comic book store. I just couldn't resist getting a picture with them. They didn't have any BBQ...

Intelligent Cursor Sharing in 11.1.0.7 More testing

I’m continuing my research on Intelligent Cursor Sharing (Adaptive Cursor Sharing). At the symposium this year one thing I pointed out was the range in the view v$sql_cs_selectivity view. There is a high and low to each range. Looking at these values for a set of queries there is a pattern to what is going on but exactly what it means is a bit less then clear to me.

It starts out pretty simple. When I run my simple query:

select /* RVD */ count(*) from big_tab where object_type = :obj_typ

With a couple of values is seems pretty simple. I run it with the values “RULE”, then “SYNONYM” twice, this kicks in Cursor Sharing. And I see this in the v$sql_cs_selectivity view:

LOW = 0.351151
HIGH = 0.429185

Doing a bit of arithmetic and it is easy to find that the midpoint of this range is .390168. The selectivity for SYNONYM in this table is 0.390168 so it’s very clear that this is the midpoint of this range. The span of the range is .078034 from the low to the high values.

I run another query with the value set to INDEX, and a new range is created with:

LOW = 0.044807
HIGH = 0.054764

Doing the arithmetic again the selectivity of INEX (.049785470) falls on the mid point, .0497855. But the span of the range from low to high is different: .009957.

If I run with several other values I end up with 6 child cursors and the low high look like this:



A couple of interesting points about this, the span between high and low generally fluctuates, child number 5 and 4 are nearly the same, but the others differ by quite a bit. Only cursor number 5 and 6 are sharable from V$SQL which means they are the only ones that are useable.

Cursor 5 is uses and INDEX RANGE SCAN and cursor 6 uses a INDEX FAST FULL SCAN. Cursor 5 has a Span from low to high of 0.000013 to 0.077194
The selectivity for the queries that use cursor 5 are:

0.000015 (RULE)
0.049785 (INDEX)
0.000073 (LOB)
0.039517 (TABLE)
0.070177 (VIEW)
0.000102 (CONTEXT)
0.000160 (JOB)
0.000145 (CLUSTER)
0.000015 (EDITION)
0.018602 (PACKAGE)

Cursor 6 has a span from low to high of 0.290623 to 0.429185. The selectivity for the queries that use cursor 6 are:

0.390168 (SYNONYM)
0.322915 (JAVA CLASS)

So there seems to be some correlation between the selectivity and the range. It appears that after a few runs the Cursor Sharing is able to figure out it doesn’t need more cursors. Somehow it also seems to come up with a decent ranger for each, how the range is calculated is still a bit of a mystery. Also there is a gap between the ranges used for 5 and 6. So far this is just with one BIND in the statement. Next to move on to multiple BINDs and see what happens there.

Note: If you are looking at doing some test like this on your own, the high and low values are stored as varchar2 data. And at least on my windows box were stored in a multi-byte char set. To do any arithmetic with them I had to do this to get the values as a number:

(TO_NUMBER(TO_SINGLE_BYTE(HIGH))

BINDs and Selectivity Calculations

One of the issues with doing an explain plan vs an execution plan is that if at explain time you use a bind the optimizer will "assume" a selectivity for the bind. If it's an equality operator it will use the density value from the stats, which is 1/NDV (NDV - Number of district values).

(Note: These test runs on a 11.1.0.7 database, but I have seen the same values on a 10.2.0.1 database as well.)

Given this SQL:
select /* RVD */ count(*) from big_tab where object_type = :obj_typ1
/

An explain plan shows:

INDEX RANGE SCAN on BIG_OBJTYPE_IDX with estimated rows of 59464

The table has 2,200,160 rows in it, the desity is 1/37 = .02702702702703

Doing the arithmetic: 2200160*.02702702702703 = 59463.78378379 so it sure looks like that is the calculation going on.

Interesting note is that when there is a histogram on the column, the stored density in the stats shows a .00000, not the .02070207 number. The optimizer must just do the calculation during the parse of the statement.

OK, how about when we use other operators? What selectivity does it use for them?

With <, <=, >,>=, LIKE, and BETWEEN I got this:

INDEX RANGE SCAN on BIG_OBJTYPE_IDX with estimated rows of 110K

Doing a bit of arithmetic, this is 5% (2200160*.05=110008).

This seems to prove that with a BIND it will go with 5% selectivity, except for the equality operator where it will use the 1/NDV calculation.

BBQ in Vancouver BC Canada

The other night I went to Dix BBQ and Brewery here in Vancouver. The food was good, but the beer is really their trade. I had ordered a Margareta, mostly because I really like tequila. Beer and I haven't been getting along as well these days... I'll head back there again and try some more of the beer, I did have a small sample the other night and it was very good.

Fogo de Chão - Chicago

OK not exactly the BBQ I usually get, but some darn good meat for sure! I think I eat enough meat last night to feed a village some where. Excellent cuts and salty which I really like. Had great conversion with a SQL Server guy, whom I'm working on converting to the light side. Great place for a good dinner for those coming to Chi-Town, Fogo de Chão.

Count(*)

For years I've heard folks promote the idea that COUNT(1) is better then COUNT(*). This is not true, and I'm pretty sure this has never been true. Here is a very simple test in 11.1.0.7. If some one can prove to me that this works different in a different version of Oracle, please let me know.

Here is a count(*):



Here is count of a constant:



Both plans do exactly the same thing, a fast full index scan on the primary key column of the table. Oracle will use an index on a non-null column for the count, it doesn't have to be the primary key.

Intelligent Cursor Sharing in 11.1.0.7

I've installed 11.1.0.7 and it does seem that Intelligent Cursor Sharing is smarter in this version. When I did the testing in 11.1.0.6, I had a SQL statement that generated 10 child cursors, with 11.1.0.7 the same test only generates 6 child cursors. So it's better.

The test case is this SQL:

select /* RVD */ count(*) from big_tab where object_type = :obj_typ
/

The object type column has some good skew to it, some values only appear tens of times and others many thousands. The bind (:obj_typ) is then set to 12 different values.

With 11.1.0.6 I got the following when I looked at V$SQL:




With 11.1.0.7 I got the following when I looked at V$SQL:



I know the code here is hard to read, an important column is the first one which is the "IS_SHARABLE" column. This more of less says will this cursor be used in the future. With it set to N it wouldn't be. So in both these versions only the last two cursors are sharable (usable).

There are only two different plan for each version. An index range scan, and an index fast full scan.

For the 11.1.0.6 version the index range scan is use for child cursor 0, 2, 3, 4, 5, 6, 7, 8, and 9. Only child cursor 1 and 10 have the index fast full scan.

For the 11.1.0.7 version the index range scan is used for child cursors 0, 2, 3, 4, and 5. Again only the second and last (child number 1 and 6) have the index fast full scan.

This is definitely an improvement. I hope to see that we get to a point with a simple query like this that we only have 3 plans, cursor 0 and then the two plans that are used. Because of the nature of how Intelligent Cursor Sharing works, cursor 0 will always be an unused cursor once Intelligent Cursor Sharing kicks in.

Windows....

Last night I was trying to apply the 11.1.0.7 patch to my 11 Database on my laptop. I kept getting this error:





I checked and checked, all services were shutdown, no other programs were running. I check MetaLink for anything like this, I search with Google. Nothing.

So out of desperation I reboot my laptop. Sure enough the install runs without a hitch...

Intelligent Cursor Sharing in 11.1.0.6

In my presentation at the 2009 Hotsos Symposium I talked about a new 11g feature called Intelligent Cursor Sharing. It was originally called Adaptive Cursor Sharing. The basic premises of the new feature is that a cursor with a bind might have more then one best path depending on which value the bind happens to be. This is a classic problem that we all have had to deal with at one level or another. We have a data column with just enough skew in it that a maybe a full table scan is the right thing to do for some values and that an index scan is right for others.

A histogram will let the optimizer know about the skew but with bind peeking, that histogram will only be used at the initial hard parse. So who ever gets there first will get the right plan of them, however if the next run would be better with a different plan then the performance will suffer for queries wanting a different plan.

Intelligent Cursor Sharing to the rescue! Now with this feature you will still suffer some pain, but over time the pain will subside, that’s the plan anyway. Now the sequence of event should go like this.

Someone runs a plan with a value that is best done with an index scan for example.

The next person’s value would be best with a full table scan. This person will still suffer the pain of having the wrong plan for this initial run. But the next time they run this Oracle will know that it wasn’t such a good idea and re-parse the plan. There will now be a couple of child cursors for the statement. The idea being based on which value comes in Oracle will pick the right plan for the given values.

The problem appears to be that the optimizer doesn’t seem to know that the plan it came up with might be the same one it just used. I showed a very simple statement, ran it with 12 different values, and ended up with 11 cursors, but only 2 plans. 8 of the cursors had one plan (an index range scan), only the second one and that last one had a different plan (both were an index fast full scan). One member of the audience told me he had a client with over 32,000 child cursors! Clearly this isn’t working quite as expected.

I had a few folks tell me this is fixed in 11.1.0.7, I have just pulled that patch set down and will be testing this over the next few days.

BBQ in Rochester NY

Speaking of BBQ, the week before I was in Dallas for the Sym I was in Rochester NY. I hit two quite good BBQ places. Dinosaurs BBQ and Sticky Lips.

This is pulled pork at Dinosaurs:



And a full rack of ribs at Sticky Lips, one half with sauce and the other with a dry rub. I really like a good dry rub and this was a good one!



Both are good. I'd recommend both to anyone wanting some BBQ. Sticky Lips was a bit harder to find. But not that hard. Neither one has parking really close by. Dinosaurs is right down town on the river so if your lucky (like I was) you can parallel park on the street out front, or you parallel parking gives you the shakes, across the bridge there is a lot you can park in. Sticky Lips there is a large parking lot just a short walk behind the restaurant.

Hotsos Sym 2009

Once again a great symposium has come to an end. Outstanding speakers great friends and coworkers, super food and of course a great Pirate Party on Tuesday night!



Chris Date started us off with a drop back to academia to let us all know what we are doing wrong with our databases. Always good to get back to the Fundamentals. Just like in sports if your team doesn't do the fundamentals right, then it unlikely that your team will reach the playoffs.

I had the honor of working directly with Chris to help him set things up and keep things moving smoothly for him. We had the opportunity to just sit and chat a couple of times. He was quite interested in talking about many other things outside of the database world and that was refreshing to step out of the professional world for a moment.



I did a presentation on the new 11g feature "Intelligent Cursor Sharing", I must admit I was a bit hung over from the party the night before so I did get off to a rough start. But we had a lively conversation with about what appears to be a bug with this in 11.1.0.6, hopefully this is fixed in the .7 patch set and I will be checking that shortly.

Jonathan Lewis gave the one day of training on Thursday and it was great as always. Great talk on troubleshooting.

One disappointment was there was not much on the BBQ fount for food this year. I'll have to make a comment about that...

Do what you can to get there next year!