Friday, December 18, 2009
Tuesday, November 24, 2009
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 11th. 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!
Monday, November 2, 2009
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! :-)
Saturday, October 3, 2009
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.
Thursday, August 13, 2009
Thursday, July 23, 2009
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!
Saturday, May 23, 2009
Thursday, May 14, 2009
OK, now back to some SQL optimization... How many LIOs is that thing doing?!?!
Wednesday, May 13, 2009
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!
Tuesday, May 12, 2009
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.
Saturday, May 2, 2009
Wednesday, April 8, 2009
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:
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.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:
Thursday, April 2, 2009
(Note: These test runs on a 22.214.171.124 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.
Wednesday, April 1, 2009
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.
Friday, March 27, 2009
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.
Thursday, March 26, 2009
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.
Friday, March 20, 2009
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 126.96.36.199 I got the following when I looked at V$SQL:
With 188.8.131.52 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 184.108.40.206 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 220.127.116.11 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.
Thursday, March 19, 2009
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...
Monday, March 16, 2009
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 18.104.22.168, I have just pulled that patch set down and will be testing this over the next few days.
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.
Saturday, March 14, 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 22.214.171.124, 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!