Kerry Osborne's Oracle Blog

Here’s a very brief story in response to Doug Burns’ post at his blog calling for stories on Human Error.

My very first job out of college was with a large oil company that had Oracle running on bunch of VAX/VMS systems. We had a lot of code written in FORTRAN with OCI calls (this was back in the early 80’s, so no pre-compilers yet). I was working late one night from home, which in itself was an unusual thing because not too many people were able to work remotely at that time. We only had 1200 baud modems for crying outloud, so it was painful to do much of anything remotely. At any rate, I was working on a program with some kind of iterative processing which took a while to complete. So I’d make a few changes and run it, make a few changes and run it. Well I noticed that the execution time slowed down somewhat and so I went looking to see what else was running on the system. (brief digression: I had become a neophyte sys admin due to my being the Oracle DBA and needing to have some system privileges for doing upgrades and whatnot) So I had a look to see what might be slowing my program down and sure enough there was a batch job running that was really using a lot of cpu. Well I had learned about the ability of VMS to set process priorities and so I thought to myself, “that batch job has all night to run it shouldn’t be slowing me down right now”. So I determined to change the priorities so my program would not be competing so heavily with the batch job. Unfortunately, instead of lowering the priority of the batch process, I jacked the priority of my process way up. (you’ll see why I say “unfortunately” in a minute) So anyway, the priority change worked out great. My program executions began running even faster than they had prior to the batch job kicking off. So I went back to my programming routine. Make a few changes, execute the program, check the results, make a few changes, execute the program, check the results… until I executed the program and it didn’t come back. I remember thinking, “Uh oh, I think I messed up the check for getting out of that loop”. So I thought, “well I’ll just ctl-C out and fix it”. Unfortunately, in a stupendous example of Murphy’s Law,  it was at just this point that my modem lost it’s connection. Great! So I tried to reconnect. The modem was able to establish a connection, but the machine was so busy running the process with the insanely high priority that it didn’t have enough spare CPU to log another process in. (Unlike most systems today, VMS had a very hard priority system. The process with the highest priority basically stayed on the CPU as long as they wanted – oh and by the way, there was only the one CPU) So anyway, the program ended up running most of the night and only stopped because it filled up the disk with a log file that it was writing. Needless to say, the real sys admins were not too happy with me the next morning when I showed up at work.

The best Oracle conference in the galaxy.

I’ve attended the last 4 years in a row and I have to say that it’s been far and away the best conference or training event that I’ve ever participated in. Add to that the extremely high quality of the people in attendance and you get an awesome event. 

The 2009 Symposium will be held in Dallas (as always), March 8-12. Chris Date will be giving the key note address. He is one of the founding fathers of Relational Databases and I am very interested to hear what he has to say. 

I must say I am extremely honored to be able to present at this years symposium, and humbled to be in the company of the other presenters. Here’s a link to the speakers page for this years event. And here’s a link to the sign up page.

Hope to see you there!

Oracle uses the BITAND function heavily (over 1000 times in the catalog.sql script alone as of 10.2.0.4). It’s used to determine whether a particular bit is set or not.

Here’s a link to 11gR1 documentation on the BITAND function. The function takes 2 arguments and performs these basic steps:

1. Converts the 2 arguements to binary (n-bit two’s complement binary integer value)
2. Performs a standard bitwise AND operation on the two strings
3. Converts the binary result back to decimal

So what’s a standard bitwise AND operation actually do?

Well it basically does a logical AND of two bit strings. If the values in any position are both 1’s, then the result will have a 1 in that position, otherwise the result will have a 0 in that position. Here’s a link to the Wikipedia entry on bitwise operations in case you want more information.

Here’s an example:

   
    0101
AND 1001
  = 0001

Continue reading ‘Oracle BITAND function (bitwise AND)’ »

There is a fair amount of information available on this topic already, but I thought I’d add my two cents worth here.

So why might you need to do this in the first place? Well generally it’s because some parameter has been set that someone later decides was a mistake. Or, as in my case, because Oracle comes out with a new way to handle a parameter making it attractive to let Oracle automatically handle it. db_file_multiblock_read_count is just such a parameter, but I’ll have to save that for another post. Anyway, there are a couple of key points to keep in mind:
 

1. Oracle does a lot of things automatically (if we don’t prevent it)
2. Some of the automatic things are good
3. Setting a parameter back to it’s default value IS NOT THE SAME as unsetting it

 
But first a few house keeping basics. Oracle has a view called V$PARAMETER which lists all the non-hidden parameters, their current values and a few fields which indicate whether the current value has been altered from the default value (ISMODIFIED and ISDEFAULT in particular). The view is based on X$KPPI and X$KSPPV. These X$ views contain the so called hidden parameters (those beginning with “_”) as well as the regular init.ora / spfile parameters, although the V$PARAMETER view doesn’t expose the hidden ones. There is a metalink note with all the fixed view definitions by the way (220021.1). But it’s easier just to pull the definition from V$FIXED_VIEW_DEFINITION (fixed_view_def.sql) and you’ll be sure you have the version that you’re actually running. Here is a link to the 11gR1 doc on the V$PARAMETER view.

One of the things that has always bugged me about the V$PARAMETER view is that the ISDEFAULT column is not updated when a parameter is dynamically changed. There’s another column, ISMODIFIED, that is changed when a parameter is changed with an alter system or alter session command. But it’s a pain to have to look at two columns. So I wrote this little script (parms.sql) to make it a little easier to deal with. It also displays hidden parameters if you ask for them. It’s based on the 10.2.0.4 definition of V$PARAMETER but it works in 9i and 11gR1 as well.

Continue reading ‘Reset Oracle init.ora / spfile parameters’ »

My Mac got a new shell (for my birthday). It’s candy apple red. Appropriate color I guess. It’s a hard, semi-transparent, plastic shell that snaps on to the Mac. You can get one at the Apple Store. Here’s a picture:

or two:

Anyway, I am starting to get the hang of using a Mac (I think). I like the less clicky keyboard. The single mouse button drives me a little crazy, but I have a blue tooth mouse that has two buttons and that helps (as long as I remember to take it with me). I had a presentation last week and of course the Mac has a non-standard video out connector – different than the rest of the world. So I had to get a little dongle to connect it to our projector (and every other projector in the world). Unfortunately, Apple has not one, but two non-standard video out connectors, so you guessed it, I ended up at the presentation with the wrong dongle. I hate dongles! I used to have a network card in a laptop, remember the days when laptops came without network cards, and you had to buy one to go in those slots in the sides of the laptops, and they all came with their own special dongle that would only work with that one card. I mean really only one card. I think the manufactures created a unique dongle for each individual card they manufactured. Probably as a security measure. But I digress. Where was I? Oh yeah, I was talking about my presentation. I was able to copy the power point file onto a thumb drive and use another guy’s PC. I was a little worried that the PC wouldn’t be able to read the file from the MAC, but it worked without a hitch.

I’ve made the switch to iCal now which works pretty well. Actually I’ve got a wiz bang tech guy that set up our email/calendar stuff on our server with Zimbra which means the data is stored on a linux box and I can access it from iCal on the MAC, Outlook in VMWare, or my phone. Seems to work pretty well. I haven’t tried changing things in Outlook since we set it up but I have looked at the the calendar events there. And changing events on the phone or in iCal definitely works. The phone even syncs wirelessly – no plugging it in to the computer. It’s pretty cool really. A bunch of guys at the office have iPhones and that’s suppose to work even better but honestly, I don’t like to change things unless I have to, and my Treo has about 100 books on it, so for now that works just fine.

For work stuff the Mac has a terminal session utility called iTerm which works great. I occasionally have to switch to Windows under VMWare to use some flavor of VPN. When that happens I revert to putty under Windows, but there’s not much difference between iTerm and Putty, so switching back and forth is not too painful. And there is the side benefit of being able to stay connected to the internet on the Mac side even when connected to a client that restricts access (it’s pretty common these days for companies  to block access to most websites and especially email from servers outside their domain).

There are still certain things that seem counter intuitive to a guy who has been using Windows since the stone age, but I haven’t touched my old laptop for over a week, so I guess I’m adapting.

I guess the biggest issue I have is that the Mac is too smart for it’s own good sometimes. Like when I wanted to save a plain text file in TextEdit (I was surprised they didn’t call it iText by the way). There’s a dozen options for saving files as rtf, rtfd, xml, html, doc, docx, and some I never heard of, but can I please just save it as an ascii text file for crying out loud? (if anyone knows of an easy way to do that please let me know) I figured out that if I open a text file that I created on my old PC, it gives the option to save as Western (MAC OS Romain), whatever that is. It’s still not quite a plain text file but at least it uses a fixed width font and I can email to my PC using friends.

So that’s it for now. I still haven’t had time to set up iTunes or do anything with the bazillion images from my camera, but that’s supposed to be the Mac’s strong suit, so I’m sure that will work fine when I get around to it.

Oracle 10g came with a new version of Statspack – they called it AWR, but basically it’s the same old estat/bstat report. There are of course a few new things in it and the snapshot process is automatically configured to collect data once per hour when you create a 10g instance. Unfortunately, this “new” feature is part of the separately licensed Diagnostics Pack, despite the fact the there is no easy way to disable the data collection. I posted earlier on Oracle Management Packs and associated licensing here, by the way.

But Statspack is still available and still runs fine if you choose to install it. As a matter of fact, it still exists in 11gR1 and has in fact been updated to accommodate some of the data dictionary changes in 11g. It looks a little like an after thought though as there are a couple of problems with it. The script to set it up is still in the $ORACLE_HOME/rdbms/admin directory and it’s still called spcreate.sql. This script creates the PERFSTAT user and the tables and necessary code objects.

The Statspack report looks very similar to the AWR report. It has basically the same Header Section, the same Wait Events Section, the same SQL Statements Ordered By XXX Sections, the same Tablespace and File I/O Sections, the same Buffer Pool Advisory Section, and the same Non-Default INIT.ORA Parameters Section at the bottom. Hey, this is the same thing!

The best things about Statspack:

1. It is very, very similar to AWR
2. It’s Open Source (sort of) – AWR is wrapped, but Statspack isn’t. You can modify it to suit your own taste. 
3. It’s still free!

  Continue reading ‘Statspack Still Works in 11g’ »

Well I did the early voting thing last week. It’s the first time I’ve voted in a presidential election since I was 18. I voted for Ronald Regan in that election. I think he did pretty well. This year’s election has been interesting.

Boy, John McCain sure has an expressive face!

Here’s a comparison to a lizard of some sort.

And then there’s Tina Fey. I think she makes a better Sarah Palin than Sarah Palin. Here are a couple of her best shots. The first one is her imitating McCain, but the second one is just her talking.

I can’t seem to find any bad pictures of Obama (so I had to make do with this cartoon of Obama O’s – with Hope in Every Bowl!). 

Same goes for Biden (he does kind of look like the grinch though).

Anyway, all this buzz about tongues made me think of another a couple of other guys with famous tongues. Remember this guy?

But the one I was really thinking about was MJ. We always thought he was pretty cool, even though his tongue was hanging out about half the time.

There has been quite a bit of confusion about the licensing of Oracle Management Packs and about their functionality. Here are a couple of links to the Oracle Docs which describe what’s not allowed without licensing these packs.

10gR2 Management Pack Licensing

11gR1 Management Pack Licensing

A couple of things to be aware of.

1. They are only available with Enterprise Edition
2. The Tuning Pack depends on the Diagnostic Pack 

11gR1 includes a new parameter (CONTROL_MANAGEMENT_PACK_ACCESS) which controls access to features based on the Diagnostic and Tuning Packs at the database level. The valid values for this parameter are NONE, DIAGNOSTIC, and DIAGNOSTIC+TUNING. Setting this parameter to NONE disables several features at the database level including AWR (which is the only feature I’ll discuss in this post). By the way, the default is TUNING+DIAGNOSTICS, so AWR is still enabled by default. Also, note that snapshots are still created, even if the parameter is set to NONE, but they don’t contain any data. So they are not just limiting access to the reporting capability, but also disabling the collection of data. Here’s a quick example on a 11.1.0.7 database:

SQL> @test_cmpa
SQL> --
SQL> -- Test setting control_management_pack_access=NONE
SQL> --
SQL> select name,value from v$parameter where name like '%pack%';

NAME                                                                             VALUE
-------------------------------------------------------------------------------- ------------------------------
control_management_pack_access                                                   DIAGNOSTIC+TUNING

SQL> select snap_id, begin_interval_time, snap_level, snap_flag from dba_hist_snapshot
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);

   SNAP_ID BEGIN_INTERVAL_TIME       SNAP_LEVEL  SNAP_FLAG
---------- ------------------------- ---------- ----------
      3826 27-OCT-08 08.12.32.405 PM          1          1

SQL> select count(*) from dba_hist_sysstat
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);

  COUNT(*)
----------
       500

SQL> alter system set control_management_pack_access=NONE;

System altered.

SQL> select name,value from v$parameter where name like '%pack%';

NAME                                                                             VALUE
-------------------------------------------------------------------------------- ------------------------------
control_management_pack_access                                                   NONE

SQL> @awr_snap
SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> select snap_id, begin_interval_time, snap_level, snap_flag from dba_hist_snapshot
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);

   SNAP_ID BEGIN_INTERVAL_TIME       SNAP_LEVEL  SNAP_FLAG
---------- ------------------------- ---------- ----------
      3827 27-OCT-08 08.13.10.553 PM          1          5

SQL> select count(*) from dba_hist_sysstat
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);

  COUNT(*)
----------
         0

Continue reading ‘Oracle Management Packs’ »

Here’s a link to the presentation materials from a talk I did last night. It was an internal consultants meeting for Enkitec. The zip file contains a power point file and two text files with examples of a couple of new features (the new results cache feature and the so-called Real-Time SQL Monitoring). Anyway, the presentation is a modified version of one I did last week for an Oracle Tech day (it’s a little more focused than that one). 

So anyway, here’s the link: 11g Advanced Features

Comments are always welcome.

One of the interesting new features of 11gR1 is the automatic tracking of long running SQL statements. Oracle calls this new feature Real-Time SQL Monitoring. There a couple of parameters that must be set to enable this behavior.

• Parameter STATISTICS_LEVEL must be set to ALL or TYPICAL (the default)
• Parameter  CONTROL_MANAGEMENT_PACK_ACCESS must be set to  DIAGNOSTIC+TUNING (the default)

Not all statements are tracked. Only statements that are consider long running invoke this new facility. The following two conditions qualify a statement for tracking:

• The statement must take more than 5 seconds (or so) to execute
•  Or the statement is executed in parallel mode

There are a couple of new views that have been added to expose this feature. They are V$SQL_MONITOR and V$SQL_PLAN_MONITOR. The data appears to be updated once per second and it contains very detailed information including row counts for each step in the plan. These views contain a record of each execution of the qualifying statements. While it is possible to query these views directly, a function (REPORT_SQL_MONITOR) has been provided in the DBMS_SQLTUNE package that produces a nicely formated report in html, xml, or (my favorite) text format . Here is a script that queries the V$SQL_MONITOR view (sql_monitor.sql) and another that executes the afore mentioned function (report_sql_monitor.sql). Here is an example of their usage:

  Continue reading ‘Oracle 11g Real Time SQL Monitoring’ »