Kerry Osborne's Oracle Blog

In 11g, the DBMS_SHARED_POOL package has a couple of new procedures. One of them is the PURGE procedure which allows individual objects to be flushed from the shared pool. The create statement for this package can be found in the $ORACLE_HOME/rdbms/admin/dbmspool.sql script. I first ran across this about a year ago on Fairlie Rego’s Blog. The modification history shows that the PURGE procedure was written back in Aug, 2006 – so it’s been around for a while. The PURGE procedure can be used to dump objects from the shared pool including code (procedure/package/function), sequences, type definitions, triggers, and cursors (i.e. SQL statments). The ability to flush a single SQL statement is what caught my eye. Unfortunately, the option to purge cursors is not in the 11.1 documentation but it is documented (lightly) in the header of the package definition as you can see here.

Continue reading ‘Flush A Single SQL Statement’ »

I recently had a very positive experience with Oracle’s SQL Tuning Advisor. I was asked to assist with a 10gR2 RAC database that was performing badly. The database was spending a lot of time waiting on gc buffer busy waits and was doing a lot of physical i/o. These however turned out to be red herrings. A quick check showed transfer times on the inter-connect were not an issue. The disk access times were also not out of line. The real problem was that a handful of statements were doing an excessive amount of logical i/o.  We were able to identify the point in time when the performance issue began so we had a look at the AWR tables to see if there had been any plan changes for the “bad” statements around that time. Here is a snippet from the log showing changes in the plan_hash_value for one of the statements which produced substantially worse results than the previous plan(s).

SQL> @awr_plan_change
Enter value for sql_id: 2mkw0zbja4t6g

   SNAP_ID BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        ETIME          LIO
---------- ------------------------------ ------------- --------------- ------------ ------------
     35519 18-JUL-08 09.00.35.521 AM      2mkw0zbja4t6g      3485252631        .0288        748.4
     35519 18-JUL-08 08.59.49.706 AM      2mkw0zbja4t6g                        .0288        748.4
     35519 18-JUL-08 09.00.35.521 AM      2mkw0zbja4t6g      4125764560       6.8601     86,933.8
     35519 18-JUL-08 08.59.49.706 AM      2mkw0zbja4t6g                       3.3464     66,720.2
     35519 18-JUL-08 09.00.35.521 AM      2mkw0zbja4t6g       593567772        .1594      8,232.7
     35519 18-JUL-08 08.59.49.706 AM      2mkw0zbja4t6g                        .1594      8,232.7
     35519 18-JUL-08 09.00.35.521 AM      2mkw0zbja4t6g      4125764560       3.3464     66,720.2
     35519 18-JUL-08 08.59.49.706 AM      2mkw0zbja4t6g                       6.8601     86,933.8
     35538 18-JUL-08 06.29.58.999 PM      2mkw0zbja4t6g       593567772        .0420      8,012.1
     35538 18-JUL-08 06.30.44.800 PM      2mkw0zbja4t6g                        .0420      8,012.1
     35539 18-JUL-08 07.00.05.767 PM      2mkw0zbja4t6g                        .0426      8,016.7
     35539 18-JUL-08 07.00.51.579 PM      2mkw0zbja4t6g                        .0426      8,016.7
     35540 18-JUL-08 07.30.12.654 PM      2mkw0zbja4t6g                        .0429      8,024.2
     35540 18-JUL-08 07.30.58.419 PM      2mkw0zbja4t6g                        .0429      8,024.2
     35545 18-JUL-08 10.00.32.175 PM      2mkw0zbja4t6g      3526031842      92.8685    203,058.3
     35545 18-JUL-08 09.59.46.365 PM      2mkw0zbja4t6g                      53.5655    136,190.0
     35545 18-JUL-08 10.00.32.175 PM      2mkw0zbja4t6g                      53.5655    136,190.0
     35545 18-JUL-08 09.59.46.365 PM      2mkw0zbja4t6g                      92.8685    203,058.3
     35546 18-JUL-08 10.30.39.168 PM      2mkw0zbja4t6g                     108.2104    220,259.1
     35546 18-JUL-08 10.29.53.369 PM      2mkw0zbja4t6g                     138.8346    217,732.0
     35546 18-JUL-08 10.30.39.168 PM      2mkw0zbja4t6g                     138.8346    217,732.0
     35546 18-JUL-08 10.29.53.369 PM      2mkw0zbja4t6g                     108.2104    220,259.1
     35547 18-JUL-08 11.00.00.396 PM      2mkw0zbja4t6g                     121.4556    217,735.0
     35547 18-JUL-08 11.00.46.148 PM      2mkw0zbja4t6g                     121.4556    217,735.0
     35548 18-JUL-08 11.30.04.018 PM      2mkw0zbja4t6g                     108.4236    217,739.0
     35548 18-JUL-08 11.30.50.076 PM      2mkw0zbja4t6g                     108.4236    217,739.0
     35557 19-JUL-08 04.00.10.812 AM      2mkw0zbja4t6g                     115.0718    217,731.0
     35557 19-JUL-08 04.00.56.631 AM      2mkw0zbja4t6g                     115.0718    217,731.0
     35558 19-JUL-08 04.29.17.277 AM      2mkw0zbja4t6g                     127.8277    217,731.0
     35558 19-JUL-08 04.30.03.100 AM      2mkw0zbja4t6g                     127.8277    217,731.0
     35559 19-JUL-08 04.59.24.163 AM      2mkw0zbja4t6g                     112.7978    198,768.6
     35559 19-JUL-08 05.00.09.945 AM      2mkw0zbja4t6g                     112.7978    198,768.6
     35560 19-JUL-08 05.29.31.269 AM      2mkw0zbja4t6g                     107.6025    193,642.2
     35560 19-JUL-08 05.30.17.076 AM      2mkw0zbja4t6g                     107.6025    193,642.2
     35561 19-JUL-08 06.00.24.094 AM      2mkw0zbja4t6g                     184.9285    217,598.2
     35561 19-JUL-08 05.59.38.289 AM      2mkw0zbja4t6g                     145.1714    204,952.2

The log showed a couple of things:

1. The “bad” plan first showed up around 10:00 PM on July 18th (plan hash value 3526031842).
2. The “bad” plan did orders of magnitude more logical i/o than the previous plans.
3. The plan for this statement had been experiencing instability even prior to the problem (this was due to bind variable peeking).

Since this was a production system, the goal was to return to the original performance as quickly as possible. The major difference between the two plans was that the “bad” plan had a full table scan on a 14M row table while the good plan used an index. My first thought was to force Oracle to use the plan I wanted via hints as the problem was severely impacting the users and it was a relatively simple query. Also, the majority of the query was buried in a view so it would be a simple matter to modify the view without having to roll out any new code. While I started down that path, an enterprising member of the team (Rick Miners), created a SQL Tuning Set out of one of the queries and let Oracle recommend a “fix”. This was done with the DBMS_SQLTUNE package. The recommendation offered was to create a SQL Profile that resulted in the exact plan we wanted – it eliminated the full table scan in favor of the index just as the “good” plan had. We accepted the proposed SQL Profile and watched as the long running versions completed while the new executions picked up the new plan. The same process was repeated for several other troublesome queries – all with the same excellent results.

The success came as quite a shock to me as my previous experiences with this advisor had not been good. Specifically, I was involved in an incident where a DBA had implemented several recommendations which brought a production server to its knees. Note that OEM has an easy point and shoot interface to implement recommendations (often a SQL Profile) with the push of a button. Unfortunately it seems that the shooting sometimes occurs before the pointing. It’s easy to see how that can happen. Imagine yourself in a high pressure production support situation where you know a certain SQL statement is behaving really badly and you’re looking at an OEM screen that says – yeah that SQL statement is a problem and I know how to fix it. Not only that but here’s a bunch of technical sounding supporting data and a 6 digit estimate of the percentage improvement you’ll get if you just push this button. And there is the button … just begging to be clicked. And you think to yourself, “Well it can’t get much worse”, so you click the button.

At any rate, this most recent experience has made me completely rethink my position on Oracle’s Tuning Advisors. While they are certainly not perfect, I think that they can serve a purpose – an extra wrench in the old tool kit if you will.

The easiest/quickest way to use this advisor is of course with OEM/Grid control. However, I don’t always have access to that tool. Also, since I am a performance addict, I don’t really want to be tempted to click the button without doing my homework first. So here are the basic steps to do it from the command line (i.e. sqlplus):

1. Create a tuning task
   • Creating a task for a single sql statement makes the most sense to me.
   • There are actually 3 parts to this – creating the tuning task, executing it, and then reporting on it
     • DBMS_SQLTUNE.CREATE_TUNING_TASK
     • DBMS_SQLTUNE.EXECUTE_TUNING_TASK
     • DBMS_SQLTUNE.REPORT_TUNING_TASK
   • See create_tuning_task.sql
   • Note the default time limit for the execute part is 30 minutes so you may want to reduce that (the create_tuning_task script prompts you for a value in seconds). By the way, the reason it can take so long is that the advisor can actually execute parts of the statement to get better estimates of the number of rows returned by a particular step.
   • Primary recommendations are often to accept a SQL Profile but there may be other recommendations such as creating indexes, etc..
   • The report output shows the old plan and the proposed new plan.
2. Review recommendations
   • Never, ever, ever (I really mean it) blindly accept a recommendation – Duh!
   • Look at the new plan as proposed by REPORT_TUNING_TASK
   • Specifically – evaluate the proposed plan and if possible and time permits, test it
3. Prepare a script to disable the SQL Profile that will be created.
   • You do have a back out plan right?
   • It is very easy to disable a profile, however once a plan is selected it will not be changed. That execution will continue to use that plan until it finishes, or hell freezes over, which ever happens first.
   • See disable_sql_profile.sql
4. Accept the SQL Profile
   • DBMS_SQLTUNE.ACCEPT_TUNING_TASK
   • See accept_tuning_task.sql (or accept_sql_profile.sql)
5. Confirm the performance improvement
   • Existing active cursors will continue processing
   • A new child will be created using the profile and subsequent executions will use it
   • You can compare the performance of the old plan and the new
   • See find_sql.sql, find_sql_stats.sql, dplan.sql
6. Consider creating an Outline for the statement
   • SQL Profiles do not lock the execution plan
   • Outlines do lock the plan
   • You can create an outline on a plan that is using a profile
   • You should disable the profile after enabling the outline
   • See create_outline.sql
7. Figure out why the plan went crazy in the first place

Jonathan Lewis has a couple of notes about SQL Profiles on his site here SQL Profiles (10g) and here SQL Profiles – 2 that are worth a look. I totally agree with his advice to make sure you know what the profile is going to do before implementing it. You can do this by looking at the proposed plan and the hints to be applied to get there (see tuning_task_hints.sql or sql_profile_hints.sql). Also be aware that profiles will tend to sour on you over time. As things change, the estimates that the CBO comes up with for particular operations can and will change. One of the main techniques used by profiles is to modify cardinality estimates of operations by applying a scaling factor. Once the factor is defined it remains fixed, however, the CBO’s estimate may change for many reasons – new stats, histograms, etc… Therefore, I don’t believe that profiles are a good choice as a long term solution.

Kerry Osborne

So I decided to finally join the 21^st century. For the past several years I haven’t listened to any music to speak of. The radio went fuzzy in my car about three years ago and since I have a high tolerance for pain and was basically always busy doing things that were more important to me, I never got it fixed. Also, I moved a couple of years ago and still haven’t hooked up any sort of stereo at the new house. So this year I got a new car with a working radio. It’s one of those Toyota FJ cruisers that look like the old Land Cruisers.

Toyota FJ Cruiser

It was a demo unit that the dealership had put a fancy radio / GPS system in. I really only bought it for the radio. It was pretty expensive for a radio, but it has a nice blue paint job and doesn’t take up any space in the new house. But I digress, the fancy unit in the dash has AM/FM and satellite (XM) radio, a cd player, a DVD player, blue tooth phone integration, traffic information, and a griddle for making french toast while you’re driving. You know, basically everything the 21^st century has to offer. It even has a hard disk recorder that can automatically record CD’s while you’re playing them so you can play the songs later in case you loose the CD I guess. Oh, and the ability to load up your own image files to display when you reach a favorite location (remember the GPS) or just for a pretty background while listening to the radio. The best part about it is the XM radio though. Of course I can’t hear it if I go in a parking garage or if it gets really cloudy, because it looses reception, but other than that it’s great. No commercials, except for XM’s own plugs for themselves, but that’s to be expected. No harm in a little self-promotion I guess. Although, you’d think the people hearing the ads would already be subscribers.

Anyway, after a few years of not listening to any music I found out that I really like listening to the stuff on the way to and from work. For several weeks I’d come busting into the office and grab the first person I’d see and tell them about the cool music I heard on the way to work. Usually they would take the ear buds out of their ears and then give me that look like I was from another planet. I imagine it’s the look Rip Van Winkle got after his 20 year nap. After a while I stopped telling people about it and just kept the joy to myself.

Here’s a picture of one of my co-workers who’s pissed because he had to take his ear buds out to listen to me drone on about music.

While I was at it I decided to buy an ipod. Wow! It’s a lot better than the Walkman I had back in the 80’s! My new car stereo let me hook it up so I could control it and play it through the system. So I basically keep it in the glove box all the time. I mostly listen to it when I’m in a garage, or it’s really cloudy, or when I can’t find anything good on the 170 or so stations provided by the satellite radio guys. Or when I just have a particular song or artist I want to listen to, but that doesn’t happen that often.

Anyway, now that I have joined the 21^st century from a music stand point, I decided that maybe I should check out this blog thing. At first I didn’t see much that interested me, but lately I have started to see some value in it. I plan on mainly using it for work related stuff. I work for a small consulting company that focuses on Oracle databases (Enkitec) and I am fortunate to get to work on interesting things (for me anyway) on a fairly regular basis. I’m hoping this blog will provide a convenient way to share some of the stuff that I’ve learned and get some feedback as well. So, here’s to the 21^st century.