ASSM: A shark in the water?

1 Comment

My team came across the following earlier this month. There’s a shark in the water, waiting to byte users running through

One evening, a table with 10 million rows got several thousand rows updated and inserted via a nightly batch job. But the table grew from 500 MB to over 4 GB!

Well, how could that be…The tablespace where the table resides had recently been re-organized so that settings were now locally managed, uniform extent sizes and automatic segment space management (ASSM) turned on. All best practices. Oracle version was on Solaris.

We looked at the possibility of chained rows. There were none. We checked how the batch job worked. It was a Peoplesoft SQR job. It did not use parallelism. It only inserted and updated rows. It did not do deletes or truncate the table before inserts and updates.

I remembered about how the high water mark can be driven up high under certain circumstances, so I checked that using dbms_space.used_space. High water mark seemed fine. But that led me to dbms_space.space_usage. This procedure indicated that there thousands of “unformatted blocks”. This was something new for me. I forwarded this off to Oracle support, and sure enough, there is a bug affecting, and also 10g versions through See Metalink Note:469985.1: Sudden Increase in Unformatted Blocks.

The write up on this one is a little scary, just like a shark in the water. So be careful out there.

“Unexplained increases in unformatted blocks in a tablespace. Very few of the unformatted blocks ever get used. More space is added whenever a new block is requested resulting in even more unformatted blocks. The number of unformatted blocks varies from one database to another. Some databases only add a few thousand unformatted blocks at one time. Others add millions of unformatted blocks all at once. Once the problem starts the only way to prevent new unformatted blocks from being added is to rebuild the tablespace.”

Getting Rails Working: Vista, mongrel, sqlite

Leave a comment

For kicks I wanted to Ruby on Rails working. Here’s the combination of stuff I was using:

  • Windows laptop running Vista.
  • Mongrel web server which I think comes with default install of rails
  • sqlite database. I didn’t want to put mysql or something else on this machine quite yet.

Here are the steps I followed.

  1. Went to Clicked on the Download tab and followed the steps to download stuff.
  2. First item was ruby, which I installed to C:\ruby. Ruby comes with an installer. It was ruby186-26.exe for the version I downloaded.
  3. Then it was on to gems. You download the file, extract it, then run ruby setup.rb.
  4. I installed rails. That just involves typing in gem install rails –include-dependencies.
  5. Step 4 above might have been enough to install mongrel, a web server that rails can use. However, I ran gem install mongrel just to be sure.
  6. To get sqlite working, I downloaded sqlite from I put sqlite3.exe, sqlite3.dll and sqlite3.def in c:\sqlite. HOWEVER, to get things working with ruby and rails, I copied these three files to c:\ruby\bin. THIS was critical.
  7. Then install the ruby/sqlite interface. Do that with: gem install sqlite3 .
  8. At this point I was ready to run rails. I made a new app people by doing
    the following:

Yea!!! It worked.

11g: Alert Log is dead, long live ADR

Leave a comment

I’m studying Oracle 11g. Gots to keep up on the latest…

The venerable oracle alert log, long housed in the bdump directory, has undergone a major facelift and is now part of Automatic Diagnostic Repository (ADR). Oracle now has wayyy too many 3 and 4 letter abbreviations. ADR imposes a unified set of directories across all Oracle products, not just database. The xml file format is consistent across products, and so is the tool set needed to parse the xml file. A new init.ora parameter called diagnostic_dest controls the location of these directories. Then a whole mess of directories get created beneath that according to the pattern: ADR_base/diag/product_type/product_id/instance_id — so if you had installed the Oracle database software and had a RAC database with two instances, and the ADR_base was /u01/app/oracle, you would get

/u01/app/oracle/diag/rdbms/racname/node1 AND /u01/app/oracle/diag/rdbms//racname/node2.

And beneath each node directory you would find:

alert cdump hm incident incpkg ir lck metadata stage sweep trace

There’s new stuff getting tracked: incidents and problems. A critical error is a problem and each time it occurs is an incident.

You view all this stuff with a new command line tool, adrci. This command was made to be scripted, so probably most DBAs will end up making a few helper shell scripts to reduce typing long adrci commands. Or maybe not. Viewing the alert log was a matter of doing the following:

$ adrci

ADRCI: Release – Beta on Mon Jun 2 22:38:36 2008

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

ADR base = “/u01/app/oracle”
adrci> show alert
Choose the alert log from the following homes to view:

1: diag/clients/user_unknown/host_411310321_11
2: diag/clients/user_oracle/host_61728193_11
3: diag/rdbms/orcl/orcl
4: diag/tnslsnr/localhost/listener
Q: to quit

Then you type 3…of course you need to know that 3 is rdbms is your database alert log…

Oddly, the text-only alert log is still around, but it’s now in the trace directory within the diag sub-directory tree.

Lastly, adrci has a set of commands starting with “ips” which incident packaging. From the adrci prompt, you can issue ips commands to create zip files stuffed with trace data to ship off to Oracle support when bad things happen. This promises to streamline the information gathering process many a DBA has suffered through.