Kerry Osborne's Oracle Blog

Here’s another interesting piece of SQL that I ran into last week:

select con_id from xyz_blah where con_id=’BS-002342′;

Actually there was a whole set of them with different literals. My first thought was, “Why would the developers want to select the con_id when they already had the con_id?”, quickly followed by “Ohhhhhhhh, it’s one of those deals where the developers didn’t trust the database or don’t know how to check for an error after executing a SQL statement”.

Presumably they know that ‘BS-002342’ is a valid con_id (it looks pretty specific to me). So the app’s probably doing one of the following three bad things (listed in what I think is the most likely order).

1. Checking to make sure a record exists with that con_id before doing something (UPDATE or DELETE).
2. Checking to make sure that a record doesn’t already exist with that con_id, before doing an INSERT.
3. Checking to make sure a DELETE actually worked.

When I looked at the stats in v$sql, the queries never return any rows. So it’s probably not #1. I didn’t find any DELETEs on the table but I did find INSERT statements with matching con_id’s, so it looks like it’s the check before insert scenario (#2). There is a Primary Key on the con_id field, so the check is of course, totally unnecessary. They should have just done the insert and handled the duplicate key error if one ever happened. By the way, in the 30 days of AWR data we had available, none of these statements ever returned a row. So most likely, they never have this issue in the first place. Of course, they might also consider using a sequence to generate the key instead of having the app manufacture a 9 character key!

Anyway, this is one of a whole set of coding issues where unnecessary work is done as a standard coding practice. Like Cary Millsap always says, “the fastest way to do anything is not to do it at all”.

I saw this statement last week:

SELECT co_id, co_name, extra_object_id FROM XYZ_BLAH
WHERE co_id in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:
11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:
27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:
43,:44,:45,:46,:47,:48,:49,:50,:51,:52,:53,:54,:55,:56,:57,:58,:
59,:60,:61,:62,:63,:64,:65,:66,:67,:68,:69,:70,:71,:72,:73,:74,:
75,:76,:77,:78,:79,:80,:81,:82,:83,:84,:85,:86,:87,:88,:89,:90,:
91,:92,:93,:94,:95,:96,:97,:98,:99,:100,:101,:102,:103,:104,:105
,:106,:107,:108,:109,:110,:111,:112,:113,:114,:115,:116,:117,:11
8,:119,:120,:121,:122,:123,:124,:125,:126,:127,:128,:129,:130,:1
31,:132,:133,:134,:135,:136,:137,:138,:139,:140,:141,:142,:143,:
144,:145,:146,:147,:148,:149,:150,:151,:152,:153,:154,:155,:156,
:157,:158,:159,:160,:161,:162,:163,:164,:165,:166,:167,:168,:169
,:170,:171,:172,:173,:174,:175,:176,:177,:178,:179,:180,:181,:18
2,:183,:184,:185,:186,:187,:188,:189,:190,:191,:192,:193,:194,:1
95,:196,:197,:198,:199,:200,:201,:202,:203,:204,:205,:206,:207,:
208,:209,:210,:211,:212,:213,:214,:215,:216,:217,:218,:219,:220,
:221,:222,:223,:224,:225,:226,:227,:228,:229,:230,:231,:232,:233
,:234,:235,:236,:237,:238,:239,:240,:241,:242,:243,:244,:245,:24
6,:247,:248,:249,:250,:251,:252,:253,:254,:255,:256,:257,:258,:2
59,:260,:261,:262,:263,:264,:265,:266,:267,:268,:269,:270,:271,:
272,:273,:274,:275,:276,:277,:278,:279,:280,:281,:282,:283,:284,
:285,:286,:287,:288,:289,:290,:291,:292,:293,:294,:295,:296,:297
,:298,:299,:300,:301,:302,:303,:304,:305,:306,:307,:308,:309,:31
0,:311,:312,:313,:314,:315,:316,:317,:318,:319,:320,:321,:322,:3
23,:324,:325,:326,:327,:328,:329,:330,:331,:332,:333,:334,:335,:
336,:337,:338,:339,:340,:341,:342,:343,:344,:345,:346,:347,:348,
:349,:350,:351,:352,:353,:354,:355,:356,:357,:358,:359,:360,:361
,:362,:363,:364,:365,:366,:367,:368,:369,:370,:371,:372,:373,:37
4,:375,:376,:377,:378,:379,:380,:381,:382,:383,:384,:385,:386,:3
87,:388,:389,:390,:391,:392,:393,:394,:395,:396,:397,:398,:399,:
400,:401,:402,:403,:404,:405,:406,:407,:408,:409,:410,:411,:412,
:413,:414,:415,:416,:417,:418,:419,:420,:421,:422,:423,:424,:425
,:426,:427,:428,:429,:430,:431,:432,:433,:434,:435,:436,:437,:43
8,:439,:440,:441,:442,:443,:444,:445,:446,:447,:448,:449,:450,:4
51,:452,:453,:454,:455,:456,:457,:458,:459,:460,:461,:462,:463,:
464,:465,:466,:467,:468,:469,:470,:471,:472,:473,:474,:475,:476,
:477,:478,:479,:480,:481,:482,:483,:484,:485,:486,:487,:488,:489
,:490,:491,:492,:493,:494,:495,:496,:497,:498,:499,:500,:501,:50
2,:503,:504,:505,:506,:507,:508,:509,:510,:511,:512,:513,:514,:5
15,:516,:517,:518,:519,:520,:521,:522,:523,:524,:525,:526,:527,:
528,:529,:530,:531,:532,:533,:534,:535,:536,:537,:538,:539,:540,
:541,:542,:543,:544,:545,:546,:547,:548,:549,:550,:551,:552,:553
,:554,:555,:556,:557,:558,:559,:560,:561,:562,:563,:564,:565,:56
6,:567,:568,:569,:570,:571,:572,:573,:574,:575,:576,:577,:578,:5
79,:580,:581,:582,:583,:584,:585,:586,:587,:588,:589,:590,:591,:
592,:593,:594,:595,:596,:597,:598,:599,:600,:601,:602,:603,:604,
:605,:606,:607,:608,:609,:610,:611,:612,:613,:614,:615,:616,:617
,:618,:619,:620,:621,:622,:623,:624,:625,:626,:627,:628,:629,:63
0,:631,:632,:633,:634,:635,:636,:637) ORDER BY upper(co_name)
/

That’s a lot of bind variables. I wondered how big the table was.

SQL> select count(*) from xyz_blah;

  COUNT(*)
----------
       644

Ha! That’s funny. Basically get all the rows by specifying each primary key (except for 7) in a bind variable. Seems like the following would have been simpler:

SELECT co_id, co_name, extra_object_id FROM XYZ_BLAH
WHERE co_id not in (:1,:2,:3,:4,:5,:6,:7) ORDER BY upper(co_name)
/

It made me wonder how Oracle handled that many bind variables. There does appear to be some internal limit on how many the optimizer will keep up with (and that number appears to be 585). XPLAN also reports all above 585 as “Not Captured”. Actually the number keep track of appears to depend on the size. For example, if varchar2(10) variables are used as opposed to varchar2(30) variables, the number of values that Oracle keeps up with goes up significantly.

Continue reading ‘How Many Bind Variables Is Too Many?’ »

This week I got to look at a system where a new version of an application had been rolled out. I came across a SQL statement that had taken a turn for the worse in terms of its average elapsed time. It was using an index I hadn’t noticed before, so I took a look to see if the index was new (it was – apparently it was added as part of the code roll out). So I got to wondering if there were any other statements that had suffered a backward movement in performance. So I wrote this little script called whats_changed.sql. It’s the same basic idea as one I wrote about earlier here called unstable_plans.sql. Both of these scripts are based on a calculated variance. The original unstable_plans script uses variance in average elapsed times for statements with multiple plans (i.e. it shows statements that have multiple plans which exhibit a significant difference in average elapsed time between plans). The whats_changed.sql script shows statements that exhibit significant differences in average elapsed time before and after a point in time (i.e. when you rolled out new code, or added an index, etc…) A short example may explain it better than I can:

$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 25 22:24:35 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> @whats_changed
Enter Days ago: 3
Enter value for min_stddev:
Enter value for min_etime:
Enter value for faster_slower:

SQL_ID               EXECS AVG_ETIME_BEFORE AVG_ETIME_AFTER   NORM_STDDEV RESULT
------------- ------------ ---------------- --------------- ------------- ------
an9114vtxukz2           17             1.25            5.00        2.1148 Slower
803u6vwz0ah1p           29             0.04            0.18        2.1552 Slower
bvf3fxv3hatw7        2,390             0.03            0.14        2.4147 Slower
5sgs7q9pjnzg5            2             2.21            0.42        3.0130 Faster
b0zkuv9qtcvnn           48             0.16            0.85        3.1500 Slower
9ws5c6p77d384        1,852             0.57            3.30        3.3609 Slower
dy98ajwqc8s2x           15             1.32            8.34        3.7558 Slower
bkka22vb5y7t6          596             0.19            1.26        3.8804 Slower
6qfwkqgvjkn2q           12             0.51            3.60        4.2641 Slower
05xcf43d9psvm        1,197             0.02            0.14        5.4263 Slower
8wgmc9w5ubtsy          183             0.49            4.32        5.4946 Slower
fwfpuf1vfwfu2            6             0.02            0.22        5.6314 Slower
50c18dwvxq4sb          222             0.54            5.29        6.1586 Slower
aukanfjd3d8fa            3             0.88           10.00        7.3496 Slower
44bq4r5z2xrsm           54             3.13           43.39        9.0946 Slower
0az7czjdw8z7j          110             0.62            0.02       17.5933 Faster
f41agfq9qdhk6            3             0.24            8.06       22.6765 Slower

17 rows selected.

SQL> -- or to see just the slower ones
SQL>
SQL> @whats_changed
Enter Days ago: 3
Enter value for min_stddev:
Enter value for min_etime:
Enter value for faster_slower: Slower

SQL_ID               EXECS AVG_ETIME_BEFORE AVG_ETIME_AFTER   NORM_STDDEV RESULT
------------- ------------ ---------------- --------------- ------------- ------
an9114vtxukz2           17             1.25            5.00        2.1148 Slower
803u6vwz0ah1p           29             0.04            0.18        2.1552 Slower
bvf3fxv3hatw7        2,390             0.03            0.14        2.4147 Slower
b0zkuv9qtcvnn           48             0.16            0.85        3.1500 Slower
9ws5c6p77d384        1,852             0.57            3.30        3.3609 Slower
dy98ajwqc8s2x           15             1.32            8.34        3.7558 Slower
bkka22vb5y7t6          596             0.19            1.26        3.8804 Slower
6qfwkqgvjkn2q           12             0.51            3.60        4.2641 Slower
05xcf43d9psvm        1,197             0.02            0.14        5.4263 Slower
8wgmc9w5ubtsy          183             0.49            4.32        5.4946 Slower
fwfpuf1vfwfu2            6             0.02            0.22        5.6314 Slower
50c18dwvxq4sb          222             0.54            5.29        6.1586 Slower
aukanfjd3d8fa            3             0.88           10.00        7.3496 Slower
44bq4r5z2xrsm           54             3.13           43.39        9.0946 Slower
f41agfq9qdhk6            3             0.24            8.06       22.6765 Slower

15 rows selected.

A couple of comments on the script:

• The default minimum normalized standard deviation is 2. So only statements that are 2X slower were listed.
• The default minimum average elapsed time per execution is 0.1. So only statements that ran longer than 0.1 seconds were reported.
• The DAYS_AGO parameter sets a pivot point, we’ll call it the REFERENCE_TIME. The calculations are based on averages from before and after the REFERENCE_TIME.

And a brief explanation of what’s in the output:

• SQL_ID – the statement id
• EXECS – the total number of executions in the AWR tables
• AVG_ETIME_BEFORE – the average elapsed time per execution before the REFERENCE_TIME
• AVG_ETIME_AFTER – the average elapsed time per execution after the REFERENCE_TIME
• NORM_STDDEV – this is a normalized standard deviation (i.e. how many times slower/faster it is now)

So the above output indicates that only a few statements are significantly worse (avg. elapsed time 2X or greater) since our new code rollout, which occurred about 3 days ago. Statement 44bq4r5z2xrsm obviously sticks out with a new average elapsed time of 43+ seconds and about 9 times slower than before. This was the one that had picked up the new index. The rest were primarily due to increased physical i/o caused by some newly introduced statements which were doing a few million lio’s per execution and thus causing all the other statements to suffer slightly.

Let me know what you think. Your comments are always welcome.

Last week was interesting. On Monday I was on my way to lunch with a couple of co-workers (Randy Johnson and James Garner) when the SUV in front of us launched itself off of a bridge. It looked like a scene from a car chase on a movie (but without the explosions). It just launched off into the air and then disappeared from view. I’m guessing the drop was 40 or 50 feet and the SUV was doing maybe 45 or so. James was driving and he was able to pull over into a parking lot right next to the bridge. We all bailed out and went running down the steep embankment. The SUV was floating in the middle of the lake. The front end was under water but we could still see the passengers through the front windows. They looked like they were in shock. You know how time seems to slow down when adrenaline gets released in your system. Well this was definitely one of those experiences. We spent what seemed like forever trying to get their attention and to get them to roll down their windows so they could crawl out but they never moved (it was probably like 15 seconds). Then Randy says, “We gotta get them outta there” (we’re from Texas, so that’s the way we talk). And about 3 seconds later he’s ditched his shoes and wallet and phone and he’s in the water. And I’m thinking “Rats, I guess I better go help him”. Meanwhile, James has dialed 911 and is running back up to his car to get a crow bar to break the window out. So I jump in and start swimming the 50 yards or so to the vehicle, which is surprisingly hard to do in blue jeans by the way. Another couple of guys follow us into the water as well, so there are four of us altogether.

So Randy is first to the SUV and he is at the front trying to get the passengers to wake up and roll the windows down, but he doesn’t get much response from them. Meanwhile, a couple of us swim around to the back of the truck and start trying to get the hatch open. It ended up taking three of us to get it opened. Once the door was open, the water went rushing in and in a matter of about 15 seconds the whole thing was underwater. There was a very scary couple of seconds while the passengers were clambering over the seats to the back hatch and the water was rushing in. Fortunately they were able to climb towards the back and we were able to pull them both out and then pull them back to shore. Did I mention how hard it is to swim with blue jeans on?

I really believe that the passengers were in serious risk of drowning and that if Randy had not jumped in as quickly as he did they may not have made it out. They were pretty shook up. And had the vehicle sunk before we got them I think it’s unlikely they would have been able to get themselves out. We would not have been able to help as you couldn’t see 2 inches in that water. And the water was a lot deeper than I expected. After the SUV sunk we were briefly treading water above it and I never kicked anything.  Anyway, here’s what Randy looked like in my mind’s eye on Monday:

Randy Johnson – SuperDBA

I felt more like this guy (did I mention the blue jeans, hard to swim in thing):

Kerry Osborne – Swimming in Blue Jeans

So what to do after a heroic water rescue? We went to Taco Diner and had lunch on the patio. No one even asked us why we were dripping big puddles under our chairs. Then we went back to the office, got our stuff and took the rest of the day off. It was a good day!

Recently I’ve been thinking about how often we DON’T fix problems. I mean, we usually make the situation better and often in very short order. But a lot of the time we don’t really fix “the problem”. We just do something to cover it up, or make it less painful. I call it putting Band-Aids on the problem.

The Band-Aid metaphor works on several levels.

1. Band-Aids don’t actually fix the problem, they just cover it up.
2. They aren’t meant to be permanent.
3. And they don’t keep you from stabbing yourself again in another spot.
4. But they do make it less painful.
5. And they keep out germs so they keep some problems from getting worse.
6. And they are cheap (all those boxes in the picture cost less than $20 combined).

There are all kinds of special purpose Band-Aids for special applications. Like the ones that have antibiotics, butterfly bandages for closing big cuts, etc… When it comes to Oracle, my personal favorite Band-Aid is memory. Additional memory covers up a lot of sins. One of the primary design goals of the original Oracle database was to eliminate disk access. This remains a key objective today. You can think of the database as a very sophisticated disk cache. The goal for a lot of systems is to never do real time disk access (with the exception of commit processing). So it stands to reason that memory is a key component (maybe “the” key component) of most Oracle systems. And it’s relatively cheap. There is often a surplus of memory already installed that is just not being used as effectively as it could be. But even if it’s necessary to add an additional memory module to a server, this option is generally very inexpensive when compared to other potential “hardware upgrades”.

Don’t get me wrong, there are a lot of valid reasons for using Band-Aids with Oracle. I know some people that seem adamantly against applying short term fixes instead of addressing the underlying problem. But it seems to me that in certain circumstances (which seem to occur fairly often actually) Oracle Band-Aids are an appropriate response. A few of my favorites reasons for applying them are:

1. Buying time to figure out what’s really going on and how to fix it
2. Buying time until the system is decommissioned or a new version is rolled out
3. Buying time until the developers can get the real fix through change control (and as we all know, sometimes this takes a while)
4. Just decreasing the pain because it’s a packaged app that we can’t change

I do think we should be vigilant about not overdoing it though. Otherwise our databases end up like the guy in the picture below. Pretty messy and sometimes hard to even figure out what the original intent was.

Along the same lines, keep in mind that Band-Aids often leave a lot of left over trash:

Anyway, that’s my rambling for today.  Just for fun, here are a few more pictures of some unusual Band-Aids.

Feel free to let me know what you think about applying Band-Aids to Oracle systems. And what your favorite (or least favorite) Oracle Band-Aids are.