Kerry Osborne’s Oracle Blog

Seems like all I ever write about these days is SQL Profiles. I do other stuff, honest! It just seems like getting Oracle to do what you want when you can’t touch the code is the closest thing to “Magic” that DBAs get to do. By the way, software developers get to create “Magic” all the time. They have the ability to create something out of nothing. I think it’s one of the coolest jobs ever. Of course a lot of art is like that too. But painters and sculptors start with some sort of raw material - programmers don’t. Musicians don’t really use raw material either, but music is much less tangible than software. Maybe one of these days I’ll write a little on the similarities between musicians and software developers, but right now I’m way off in the weeds. Back to the subject at hand.

A few weeks ago Jonathan Lewis called me to task on a couple of posts regarding SQL Profiles (in a very nice collegial sort of way). You can see the original dialog here. One of his main points was that SQL Profiles were not meant to be a generic mechanism for forcing a particular execution plan the way Outlines are. There is after all, no documented way (that I’m aware of) to directly create a SQL Profile on a statement. I had to agree with him that I was using them in a way that was not necessarily intended. Outlines were designed to lock execution plans, SQL Profiles were designed to overcome shortcomings in the optimizer with regards to statistics. Tom Kyte described SQL Profiles like this:

So, a SQL profile is sort of like gathering statistics on A QUERY - which involves many
tables, columns and the like….

In fact - it is just like gathering statistics for a query, it stores additional
information in the dictionary which the optimizer uses at optimization time to determine
the correct plan. The SQL Profile is not “locking a plan in place”, but rather giving
the optimizer yet more bits of information it can use to get the right plan.

Tom is referring to the documented way of creating a SQL Profile which is to use the SQL Tuning Advisor. The Tuning Advisor verifies the optimizer’s calculations and can create a SQL Profile that corrects the calculations, if they are found to be in error. The corrections most often come in the form of OPT_ESTIMATE hints which apply a scaling factor at various places in the optimizer’s calculations. But take note that the underlying mechanism of applying these corrections is hints. So a SQL Profile is ultimately a mechanism for applying a set of stored hints to a SQL statement (or set of statements) behind the scenes. And whether it was intended by the developers or not, this gives us a tremendously powerful tool. With this tool we can influence (and often times control) execution plans for statements coming from application code that is difficult or impossible to change.

By the way, all this discussion of SQL Profiles and whether they should be used as a generic mechanism for affecting plans without touching a SQL statement is probably going to be fairly short lived. SQL Baselines (introduced in 11g) are the latest revision of the “behind the scenes hint application” idea. They will most likely make SQL Profiles a less attractive option in the future. Fortunately, it is a simple matter to convert a SQL Profile into a Baseline. And SQL Profiles still work fine in 11g as well (so do Outlines for that matter), but on the off chance that Oracle decides to do away with SQL Profiles (or alter their behavior) in some future release, it’s comforting to know that we are not headed down a dead end street.

Back to the subject at hand. Jonathan expressed concern and doubt on a couple of points:

1. That Profiles really were a generic mechanism to apply any hint behind the scenes. (i.e. that they could be used to apply any arbitrary hint, not just the hints created by the Tuning Advisor - like opt_estimate).
2. Whether Profiles created by the Tuning Advisor ever had hints other than “Change the Optimizer Calculations” type hints (i.e. opt_estimate, index_stats, table_stats, etc…).

I think we finally agreed that they can be used to apply hints in a generic fashion, even if that was not the intention of the developers. Nevertheless, here is a simple test case to demonstrate that they can be used for that purpose. I created a little script to create a SQL Profile with a single manually typed hint called create_1_hint_sql_profile.sql. Please note that the syntax can be quite finicky with regards to query block names and aliases.

> !sql
sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 25 15:23:02 2010
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SYS@LAB112> @flush_pool
 
System altered.
 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
SYS@LAB112> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          1           .05          190 select avg(pk_col) from kso.skew where col1 = 136133
 
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    35 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   840 |    35   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    35 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
 
20 rows selected.
 
SYS@LAB112> -- let's create a 1 hint profile to force a full table scan
SYS@LAB112> 
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full(skew)
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> @sql_profiles   
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           NO
PROFILE_69k5bhm12sz98          DEFAULT         DISABLED SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name,  NO
PROFILE_8js5bhfc668rp          DEFAULT         DISABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
PROFILE_bxd77v75nynd8          DEFAULT         DISABLED select /*+ parallel (a 4) */ avg(pk_col) from kso.skew a where col1 >  NO
PROFILE_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew where col1 NO
PROFILE_7ng34ruy5awxq          DEFAULT         DISABLED select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prope NO
PROFILE_84q0zxfzn5u6s_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
 
7 rows selected.
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full(skew)
 
1 rows selected.
 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SYS@LAB112> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 84q0zxfzn5u6s
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          1           .01           86 select avg(pk_col) from kso.skew where col1 = 136133
 
1 row selected.
 
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    35 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   840 |    35   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    35 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_MANUAL used for this statement
 
 
24 rows selected.
 
SYS@LAB112> -- didn't work - i.e. Profile got created and used, but the hint was ignored
SYS@LAB112> -- oh yeah, Query Block needed
SYS@LAB112> 
SYS@LAB112> @drop_sql_profile
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> -- must reload SQL statement for create_1_hint_sql_profile to work
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full( SKEW@SEL1$ )
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full( SKEW@SEL$1 )
 
1 rows selected.
 
SYS@LAB112> @avgskewi
 
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 28360 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    35 |   840 | 28360   (1)| 00:05:41 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=136133)
 
Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_MANUAL used for this statement
 
 
23 rows selected.
 
SYS@LAB112> -- so that worked

Note that the syntax can be tricky requiring correct Query Block name (and aliases if there are any). SEL$1 is the default name for the query block of a simple select. (DEL$1 for deletes, UPD$1 for updates) As you can see from the example, the FULL hint was silently ignored without the QB name.

Since this post has gotten a little long, I’ll save my response to Johnathan’s second concern for the next post. Please let me know if you have any questions.

This evening I went to the grocery store with my wife, Jill. It was an interesting trip. I have to admit that I took a sneak peek at the shopping list before I agreed to go, and seeing that there were only 7 items on the list, I figured I could earn some extra points without spending too much time. Sneaky, I know, but I am a little weird about optimizing my time. By way of example, one of the main reasons I have a pony tail is because I once calculated how much time it took to get a haircut every month. The calculation went something like this:

Haircut Calculation

Time	Primary Action	Snide Comment
0.50	Commute to the barber shop	Everything is a half hour away in Dallas
0.25	Wait for an open barber chair	Not totally wasted because you could read the 6 year old copies of Field and Stream or the 10 year old copies of Lowrider Magazine - just in case I ever decide to go fishing in my newly restored 57 Chevy
0.75	Actually getting my haircut	Takes a little extra time because of barbers need to give advice on everything from re-wiring a house to colonoscopy
0.25	Paying up	Again takes a little extra time to listen to advice on tattoo removal (not that I have any tattoos, much less any tattoos that need to be removed, but it just seems rude not to act interested, especially to a guy that’s basically in charge of you public image)
0.50	Commute back to wherever	Can take longer if it’s raining because nobody can drive in the rain in Dallas

So that’s a total of 2.75 hours per haircut. Multiply that by 12 times a year and that comes out to 33 hours per year. So that’s almost a whole work week. That just seemed like too much time to commit to such an unrewarding task, so I quit getting my hair cut.

But I digress.

Back to the shopping trip. Like I said, the list had 7 items on it, so I expected a quick trip. Well we get into the store and Jill asks me to get a shopping cart. I’m thinking a little hand basket thingy would be fine, because I’ve seen the list! But I don’t want to tell her that (I’m still being sneaky), so I get the shopping cart. And we proceed down the first isle, stopping every two feet or so to put something in the basket. By the end of the first isle we have at least double the number of items on the list, and by the way, nothing has been marked off the list yet. Just to give you an example, we ended up with 4 bags of chips (Lays Bar-B-Que, Tostitos Scoops, Fritos, and Sun Chips). We’d have had one more (because part of my family likes the Scoop Tostitos and another contingent likes the flat Tostitos) but we had to draw the line somewhere. It wasn’t all my wife’s doing either. We were both throwing stuff into the cart like we were expecting a hurricane to cut off food supplies for the next several weeks. By the time we got out of the store we had 17 bags of groceries. They were the small plastic kind (which I don’t really like - I always say paper when they ask - but I digress again).

I found the whole thing quite amusing, although I don’t think Jill really did.  I asked if I could check the list while we were checking out, just to make sure we hadn’t missed anything. She didn’t really appreciate my humor. (we did forget one of the things on the list by the way)

Anyway, the experience reminded me that trying to get too clever can often backfire. It also reminded me that I am predisposed to make that particular mistake. Implementing a tricky solution to a problem, while it may be intellectually stimulating,  is often not the best approach. The experience reminded of a talk I did a couple of years ago (Creative Problem Solving for Oracle Systems) where I talked about several tendencies that all of us have (to a greater or lesser extent). And how those tendencies can interfere with our ability to solve problems. One of the points of the talk was that being aware of your tendencies can help you avoid pitfalls.

Like Clint Eastwood said, “A Man’s Gotta Know His Limitations”.

This week I attended the Hotsos Symposium - It was great as usual. There are more smart guys at this event every year than you can shake a stick at. In fact, I often learn as much from the attendees as I do from the presenters.  Here’s a fancy link to the presentation I gave:

Note: I struggled a bit with how to label myself, since I don’t really have an official title. I thought about calling myself a “Senior Oracle Specialist”, but that sounded a little too puffed up. Especially the “Specialist” part. So then I thought maybe “Senior Oracle Guy” would be a little more down to earth. That was better, but it sounded a little too old, like a Senior Citizen. And since I am still in my late 40’s (OK very late 40’s) I am still quite a ways from being a “Senior” I think. Then I thought maybe I should go with something more generic like “Nice Guy and All Around Prince of a Fellow”, but that seemed a little too uninformative (and beside, my former partner used to have that on his business cards). So I decided to go back to the “Oracle guy” idea and considered using something like “Very Experienced Oracle Guy”. That sounded OK, but “Very Experienced” is really just code for old. So I was back to that, how to say old, but not too old. “Oldish” - that’s what I ended up with, mainly because I ran out of time to think about it any more (probably a good thing).

I was originally scheduled to deliver my talk on Tuesday afternoon. But when I checked in on Monday morning, Becky Goodman asked me if I would mind swapping time slots with Stephan Haisley, who had a “conflict”. His slot was first thing in the morning on Wednesday. So I said sure. Only later did I find out that the conflict was related to the Tuesday night party, which has a tendency to stretch into the wee hours of the morning. Stephan’s a smart guy and he was thinking ahead. He realized that he probably wouldn’t be at his best, first thing on Wednesday morning. As Clint Eastwood said, “A man’s gotta know his limitations”.

Anyway, the talk went pretty well but I did have one embarrassing moment. I’ve been doing Oracle stuff for a long time, so I often run into people that I haven’t seen for a while (sometimes a very long while). I’m pretty good with faces and places, but names sometimes escape me. Isn’t it odd how our brains work? I can remember minute details about some arcane unix command that I haven’t used in 10 years, but a guy’s name that I worked closely with for half a decade can escape me. How does that happen?

I’ve gotten used to it, but occasionally something even more bizarre happens. Like getting a couple of bits of memory cross wired. This actually happens more often than you would think. Try this on a friend. Get them to say “Silk” five times as quickly as they can.  Like … “Silk, Silk, Silk, Silk, Silk” …  Then immediately ask them what cows drink. Almost without fail they will say “Milk”. Of course they know that cows don’t drink milk. They know that cows drink water. But for some reason the word “Milk” just comes rolling off their tongue. Why? Because the word “Milk” sounds almost the same as the word “Silk” and you’ve just made them access the part of their brain that stores the word “Silk” several times in a row. In addition, you have asked them a question with a word (cow) that is very closely associated with the word “Milk”. And finally, milk is a liquid that people drink. So there are 3 very strong associations in your brain, even though you know that it is not the correct answer to the question.

So what’s the point, well  … The first day of the Symposium, I ran into a guy that I have known for several years and that I had in fact shared office space with just a couple of years ago. His name is Jeff Holt and he co-wrote a book with a guy named Cary Millsap called Optimizing Oracle Performance. So I see Jeff, walk over with a big grin on my face, shake hands with him and say “Hi Kevin!”.

And he just looks at me like I’m crazy (which he does pretty well, by the way). And I realize what I’ve done and say “I’m sorry Jeff, I do know what your name is”. And he looks somewhat dubious but accepts my apology. The thing is, I have done this to Jeff several times in the past. I explained to Jeff that there is a perfectly reasonable explanation for me calling him by the wrong name. I used to work with a guy named Kevin Holt and for some inexplicable reason, Kevin’s name always comes out when I think about Jeff. Maybe it’s because my brain stores data by last_name and the cells holding the first names have become damaged in some way, maybe I’ve used the name “Kevin Holt” a lot more than the name “Jeff Holt”, maybe my brain was more impressionable when I was younger and so the earlier memory is stronger. I’m not sure. Anyway, I pretty much just wrote it off as one of those questions for which there is no answer.

But I digress, back to the embarrassing moment during my presentation: So the talk is going along well and I get to this page where I reference Cary and Jeff’s book and I look at the big overhead and the reference looks like this:

Cary Millsap & Kevin Holt. Optimizing Oracle Performance
O’Reilly, 2003.

Of course to me it looked like this:

Cary Millsap & Kevin Holt. Optimizing Oracle Performance
O’Reilly, 2003.

Yes that’s right. Not only did I call him by the wrong name when I ran into him, but I actually typed it wrong on my presentation. To make matters worse, Cary Millsap is in the audience with a puzzled look on his face. So I have to apologize to him while the rest of the audience looks on. Then as soon as the talk is over, I fix the presentation materials and resubmit them (hopefully wiping out any trace of my cross wired brain). This whole experience gets me really thinking about how my brain is working and why it continues to make this repeated error. It seems unlikely that just knowing two guys with the same last name would cause such a problem. I know lot’s of people with the same last name, and I don’t get their first names mixed up.

So I start racking my brain to see if I can come up with any other explanation. What other associations do I have with the name “Kevin”? Well for starters, my only brother’s name is Kevin. We were born only a year apart so when I was a kid, almost every time I heard my name it was closely followed by his name (usually it was at the top of some adult’s lungs due to some trouble we were stirring up). In fact, the old folks often couldn’t be bothered  to keep us straight, so even when we weren’t together (which was rare) they often just combined our names (Kervin was the most common version - Kevrry was a lot less common - for obvious reasons). So anyway, I do have a very strong association between my name and my brother’s name. Then it occurred to me that my first name sounds just like Cary Millsap’s first name. Hmmmm. Cary and Jeff are closely associated (at least in my mind) as I mentioned before. They co-authored the book and have worked together at the same company (first Hotsos and then Method-R) for the last decade or so. I’ve known Cary a long time, but only met Jeff 4 or 5 years ago. So I’ve only ever known Jeff to be associated with Cary. You probably can see where this is heading. I believe my brain does something like this old school fill in the blank problem:

__________________________________________________________________________________________

Fill in the Blank with the Word that Connects the Other Two Words

Cary (which sounds like Kerry)  ____________   Holt

__________________________________________________________________________________________

It’s like a little pattern matching or free association thing. My brain just wants to put the word “Kevin” in that spot as the link between the other two words.

By the way, there have been lots of studies done over the years about how our brains store memories, how we retrieve them, how we forget things, etc… Some of those studies have indicated that most long term memory is semantic based while short term memory is more acoustic based. So most people would tend to mix up words that sound alike (like milk and silk) in short term memory while mixing up words that mean the same thing (like auto and car) in long term memories. Of course there are other studies that prove we all have some long term acoustic memory (being able to identify specific accents for example). The fact that I am a long time musician and that I play mostly by ear is probably a contributing factor as well. I am said to have a “good ear” which means that I can reproduce music pretty accurately after a very short exposure to it. So I think all that extra exercise my brain has done has made me more likely to store long term memories with an acoustic or “sound alike” kind of memory organization. By the way, if you are interested in this kind of stuff, there is an excerpt from a survey of the literature which discusses several of these studies here: Human Memory by Elizabeth Loftus.

So that’s my story and my rationalization for why it happened. And for what ever it’s worth, I’m sorry Kevin, err… I mean Jeff! -  I guess my brain just has a mind of it’s own.