Kerry Osborne's Oracle Blog

I was in a meeting yesterday and a guy asked if there was a way to track changes to database parameters (sometimes called init.ora parameters by us old guys). I thought for a second and said I didn’t think there was any sort of built in mechanism for doing that, no history table that I was aware of. Then one of the other guys in the meeting said, “oh yeah, we do that by looking at the AWR table that has the list of non-default parameter settings” (that table is WRH$_PARAMETER by the way).

Hmmm, interesting approach. AWR runs every hour by default. Not a bad idea. They had a script that prompted for a parameter name and did a dump of all entries for the specified parameter. So you could easily see where a change had occurred.

Looks like this:

SYS@LAB1024> @parm_hist 
Enter value for pname: star_transformation_enabled

   SNAP_ID TIME            PARAMETER_NAME                                     VALUE
---------- --------------- -------------------------------------------------- --------------------
      2232 25-SEP-09 00    star_transformation_enabled                        FALSE
      2233 25-SEP-09 01    star_transformation_enabled                        FALSE
      2234 25-SEP-09 02    star_transformation_enabled                        FALSE
      2235 25-SEP-09 03    star_transformation_enabled                        FALSE
      2376 01-OCT-09 00    star_transformation_enabled                        FALSE
      2377 01-OCT-09 01    star_transformation_enabled                        FALSE
      2378 01-OCT-09 02    star_transformation_enabled                        FALSE
      2379 01-OCT-09 03    star_transformation_enabled                        FALSE
      2380 01-OCT-09 04    star_transformation_enabled                        FALSE
      2381 01-OCT-09 05    star_transformation_enabled                        FALSE
      2382 01-OCT-09 06    star_transformation_enabled                        FALSE
      2383 01-OCT-09 07    star_transformation_enabled                        FALSE
      2384 01-OCT-09 08    star_transformation_enabled                        FALSE
      2385 01-OCT-09 09    star_transformation_enabled                        FALSE
      2386 01-OCT-09 10    star_transformation_enabled                        FALSE
      2387 01-OCT-09 11    star_transformation_enabled                        FALSE
      3900 02-DEC-09 23    star_transformation_enabled                        TRUE
      3901 03-DEC-09 00    star_transformation_enabled                        TRUE
      3902 03-DEC-09 01    star_transformation_enabled                        TRUE
      3903 03-DEC-09 02    star_transformation_enabled                        TRUE
      3904 03-DEC-09 03    star_transformation_enabled                        TRUE
      3905 03-DEC-09 04    star_transformation_enabled                        TRUE
      3906 03-DEC-09 05    star_transformation_enabled                        TRUE
      3907 03-DEC-09 06    star_transformation_enabled                        TRUE
...

Of course my first thought was “that’s nice, but most systems only have a few weeks of history in AWR”. I’ll come back to that issue in a minute, but for now let’s go on to the part where my brain started working on ways to make use of this idea and to maybe improve on it a little bit.

To begin with, I didn’t want to see a record for every snapshot if nothing had changed. I would prefer to just see a single record with both the old and new value when there was actually a change. Easy enough to do with the an analytic query using the lag function. I also thought I’d like to be able to wild card the parameter – no problem there. Then I decided I wanted it to be RAC aware and let me specify a single instance (since some of the parameters have different values depending on the instance). And finally, I found the calculated hidden parameters to be annoying (the ones that start with 2 underscores like “__shared_pool_size”). Several of them get reset on a regular basis, and I am not usually all that interested in those. So I added a switch to turn them off (or not). I called the script parm_mods.sql.

Here’s a quick example:

SYS@LAB1024> @parm_mods
Enter value for parameter_name: 
Enter value for instance_number: 
Enter value for show_calculated: 

  INSTANCE    SNAP_ID TIME            PARAMETER_NAME                      OLD_VALUE            NEW_VALUE
---------- ---------- --------------- ----------------------------------- -------------------- --------------------
         1       2376 01-OCT-09 00:00 db_recovery_file_dest_size          26843545600          42949672960
                 3900 02-DEC-09 23:00 db_file_multiblock_read_count       16                   128
                 3900 02-DEC-09 23:00 parallel_execution_message_size     2148                 8192
                 3900 02-DEC-09 23:00 hash_area_size                      131072               200000000
                 3900 02-DEC-09 23:00 large_pool_size                     0                    536870912
                 3900 02-DEC-09 23:00 workarea_size_policy                AUTO                 MANUAL
                 3900 02-DEC-09 23:00 shared_pool_reserved_size           12582912             24326963
                 3900 02-DEC-09 23:00 shared_pool_size                    0                    419430400
                 3900 02-DEC-09 23:00 sort_area_size                      65536                100000000
                 3900 02-DEC-09 23:00 star_transformation_enabled         FALSE                TRUE
                 4085 10-DEC-09 15:02 _spin_count                         2001                 2002




11 rows selected.

SYS@LAB1024> /
Enter value for parameter_name: 
Enter value for instance_number: 
Enter value for show_calculated: Y

  INSTANCE    SNAP_ID TIME            PARAMETER_NAME                      OLD_VALUE            NEW_VALUE
---------- ---------- --------------- ----------------------------------- -------------------- --------------------
         1       2376 01-OCT-09 00:00 __shared_pool_size                  251658240            285212672
                 2376 01-OCT-09 00:00 db_recovery_file_dest_size          26843545600          42949672960
                 2376 01-OCT-09 00:00 __db_cache_size                     1275068416           1241513984
                 3900 02-DEC-09 23:00 db_file_multiblock_read_count       16                   128
                 3900 02-DEC-09 23:00 __shared_pool_size                  285212672            486539264
                 3900 02-DEC-09 23:00 __large_pool_size                   16777216             536870912
                 3900 02-DEC-09 23:00 workarea_size_policy                AUTO                 MANUAL
                 3900 02-DEC-09 23:00 __db_cache_size                     1241513984           520093696
                 3900 02-DEC-09 23:00 hash_area_size                      131072               200000000
                 3900 02-DEC-09 23:00 large_pool_size                     0                    536870912
                 3900 02-DEC-09 23:00 parallel_execution_message_size     2148                 8192
                 3900 02-DEC-09 23:00 shared_pool_reserved_size           12582912             24326963
                 3900 02-DEC-09 23:00 shared_pool_size                    0                    419430400
                 3900 02-DEC-09 23:00 sort_area_size                      65536                100000000
                 3900 02-DEC-09 23:00 star_transformation_enabled         FALSE                TRUE
                 4085 10-DEC-09 15:02 _spin_count                         2001                 2002




16 rows selected.

So back to the issue of AWR retention…

Continue reading ‘Tracking Parameter Changes’ »

I just noticed that 11g Release 2 for the Solaris Operating System is now available for download:

Oracle 11g R2 Download Page

It didn’t lag the Linux release by too long!

I re-did my Upgrade to 11gR2 talk at the DOUG meeting in Dallas this afternoon. (I originally did it at the Cowboys Stadium for an Oracle Tech Day around Halloween). I promised that I’d post a link to the presentation, so here it is (just click on the image):

It’s the same presentation as the one from the original talk, although I got twice as much time to do it this time (worked out a lot better). Here’s a link to the post about that original Tech Day event with a bunch of pictures of the stadium, including a couple of Jerry’s data center: Cowboy Stadium Pictures

I’ve written before on the change Oracle made to their Hint based mechanisms (Outlines/Profiles/Baselines) in 10g here: Why Isn’t Oracle Using My Outline / Profile / Baseline. To quickly recap, prior to 10g, the design goal for Outlines appears to have been to “lock” execution plans. That is to allow the optimizer as little flexibility as possible. With 10g and 11g, it appears the goal has swung away from the “locking” concept and towards allowing the optimizer more flexibility. I’ll show you an example of what I mean in a minute, but I must say that I find this decision to be irritating at best. It seems to me that the whole reason for implementing one of these objects is to try to keep the optimizer from changing its mind. After all, it was originally called “Plan Stability” by the Oracle marketing guys.

One of the main offenders in this regard is the use of a new format available for index hints as of 10g. Prior to 10g, the index looked basically like this:

INDEX(TABLE_NAME INDEX_NAME)

Translation: If possible, use this index on this object.

As of 10gR2, there is a new possible format which appears to be used (at least most of the time) when Outlines/Profiles/Baselines are created. The new format looks like this:

INDEX(TABLE_NAME (TABLE_NAME.COLUMN_NAME TABLE.NAME.COLUMN_NAME …))

Translation: If possible, use any available index on any of these columns.

As you can see, this format leaves a lot more to the optimizer’s discretion than the older format. (Note: the older format is still valid)

Update 01/03/11: Thanks to Tony Hasler for making me get the syntax right (see comments below).

In my previous post I published a script for changing an individual SQL Profile hint, but recently I had a situation where there was a SQL Profile that had 20+ index hints where the statement was suffering from plan instability, despite the fact that it was using a SQL Profile. Rather than manually look up the correct index names and change the hints one by one (a very error prone proposition), I decided to write a script that would automatically change all the INDEX hints from the “non-specific column oriented” format to the “specific index name” format.

But before I give you the script – a little history. Here are Outline hints for the same statement from 9.2.0.8, 10.1.0.?, 10.2.0.4, and 11.2.0.1:

Continue reading ‘Fixing Bad Index Hints in SQL Profiles (automatically)’ »

I went to the Dallas 100 awards banquet last night at the Morton Meyerson Symphony Center. The Dallas 100 is an annual award for the fastest growing privately held companies that are based in the Dallas / Fort Worth area. Enkitec was number 81 this year – not that great, but still not too bad considering the state of the economy over the last couple of years (we were 42nd last year by the way).

Sam Wyly was the guest speaker at the presentation. He didn’t actually give a speech, but rather gave an interview which was interesting. He’s best known as the founder of Michael’s Stores which sold for around $4,000,000,000 a few years back.

As usual, there was a dinner after the presentation. And as usual it was some kind of meat with asparagus. I guess asparagus must be one of those things that can be cooked a little bit ahead of time or kept warm without too much trouble because it seems to be standard fare at these types of events. Anyway, it’s always nice to have a dinner with asparagus because you get reminded of it the next morning. I won’t go into the details here but if you’re interested I highly recommend this article on the Discovery web site. Whew!