Kerry Osborne's Oracle Blog

My dad (Roddy Osborne) got inducted into the Texas A&M Sports Hall of Fame a couple of months ago. My dad played for Bear Bryant back in the late 50’s. I knew he was a quarterback, but I didn’t realize that he played fullback his sophmore year. Turns out he was also the punter and the punt return guy (who knew). Oh yeah, and did I mention, in Bear Bryant’s system, the quarterback played middle linebacker too! He got drafted by the Cleveland Browns and played a couple of seasons there. (I think there was a guy by the name of Jim Brown playing there then).

Anyway, here’s the video that the committee put together. They played it before his acceptance speach at the banquet. By the way, that’s John David Crow (Heisman Trophy winner in 1957) narrating. It has some good footage of my dad playing (I didn’t know he could pass!).

My dad didn’t talk much about football when we were growing up. He actually didn’t want my brother and me to play because it was so hard on his body. He had huge scars on his knees and one on his shoulder that went from the front arm pit all the way around to the back arm pit. That scar was like an inch wide with cross hatches every couple of inches. He was so beat up that when his Air Force company was shipped out to Vietnam, they wouldn’t allow him to go. Anyway, there are only a couple of stories that I remember him telling:

The first one occurred during a game between Texas A&M and Arkansas in 1957. The story has been repeated many times over the years (often incorrectly). I once heard a preacher tell the story in a sermon and he had it completely messed up. I had to straighten him out after the service. Anyway, here’s how I remember it:

The Aggies were ahead by one point with a couple of minutes to go in the game. They were driving and close to scoring. Back in those days, the coach didn’t call the plays. The quarterback was responsible for calling the plays. So coach Bryant sends in a substitute with a message to not throw the ball, just to run out the clock. My dad calls a roll out, expecting to burn some clock with a long, slow developing play, but not intending to throw the ball. So he rolls out and lo and behold, a receiver (I think he said it was Gene Stallings) is wide open in the end zone. He told me that the guy was so open he just couldn’t help himself and so he throws the ball. Well one of the Arkansas defensive backs (Donny Horton) is a sprinter on the track team and he breaks on the ball along the sidelines and intercepts it. And there is nobody between him and the end zone 90+ yards away. Well somehow, my dad catches this guy from behind, and my dad is reportedly one of the slowest guys on the Aggies team. That tackle saves their undefeated season (they were 7-0 at the time and #1 in the nation). Later Bear Bryant was asked about the play and he says, “The difference was that Horton was running for a touchdown. Osborne was running for his life.”

The second story he told that stuck with me was about the last day he played football.

After playing a couple of years in Cleveland, my dad was drafted and entered the Air Force. He was stationed in Bitburg Germany. Apparently they had some sort of semi-pro league there and my dad was a player coach. The story I remember went like this (I’ll tell it from his perspective):

Did I ever tell you about the last time I played football? Well I was a player coach in Bitburg, so I suited up, but never played because I was so out of shape. Well our first string quarterback got hurt and so I put in our second string guy. And I told him, what ever you do, don’t get yourself hurt. Because we don’t have another quarterback except me. And sure enough, on the second play, the guy takes off on a run and gets clobbered and I have to go in. Well we’re backed up close to our end zone and I call a pass play and I roll out to the right and it’s like the seas parting. There is no one between me and the end zone.

So I take off running…

And I’m running, and running, and running…

And I look over and the referee is keeping up with me…

Running backwards…

And on about the 10 yard line, a big old fat lineman catches me from behind…

And I roll over on the sidelines…

And I throw up…

And that’s the last time I played football.

Several other guys got inducted at the same time. Greg Hill and Quintin Coryatt were the two that made the biggest impression on me at the banquet. Greg Hill was a great running back in early 90’s that was drafted in the first round by Kansas City. Quintin Coryatt was a linebacker in the early 90’s. As part of the Wrecking Crew, he was probably most well known for “The Hit” which was aired repeatedly after a nationally televised game against TCU. He was drafted with the number 2 overall pick by the Indianapolis Colts. Here’s a video of “The Hit”.

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’ »