Statspack Analyzer – Intelligent analysis of Oracle Statspack and AWR reports

After spending a couple days picking through Oracle statspack reports for clues on what could be causing some database latency I finally got the chance to try out the new, free Statspack Analyzer from Texas Memory Systems, Inc. and Burleson Consulting.

StatspackAnalyzer.com is a FREE SITE provided to the Oracle community by sponsor companies including Texas Memory Systems, Inc. and Burleson Consulting.

Our shared goal is that the advice provided by this website evolves as community feedback indicates that the heuristics should be updated. If you like what you see, please let us know. If you don’t like what you see, please tell us what you would do to improve the site and which if any decision rules should be updated.

Just paste your whole statspack or AWR report into the analyzer and it will do the heavy lifting and give you custom recommendations on what areas you can possibly tune to increase performance. I had the chance to run several reports through the analyzer this week and it came up with many of the same conclusions I did in a fraction of the time.

Tools like this aren’t about to replace the DBA but rather help take the edge off intensive tasks like tuning. Check out the sample report to get a better idea of what the analyzer output looks like, or better yet try it out! After all, it’s free!

oracle, database, dba, database administrator, database tuning, sql

Getting Started with the Sun Fire T2000

SunFireT2000After receiving my try-and-buy Sun Fire T2000 over a week ago it’s finally up and running. While we have yet to hit it with much of a load, here are some thoughts on the out-of-box experience:

Before even powering up there are a couple interesting observations about the system. The first is the lack of power button, switch, or key. In fact there is only one button on the system and that is an indicator button which just flashes an LED on both the front and back of the system to make it easier to locate (a nice feature, by the way.) You’ll quickly find the hot-swappable dual power supply and fan compartment. The adventurous will find and the big button on top of the system which allows you to gain access to RAM and other non-hot-swappable components.

All of these compartments and components are accessible without the need for tools. The hard drives are on hot-swappable sleds which pop out the front of the box. The hard drives are small (2.5″ I believe) allowing room for 4 bays in the front and still affording enough space for ventilation. I was somewhat surprised to find laptop-style drives in a Sun server, but I guess I can’t come up with any reason not to.

After giving the hardware a good once-over it was time to get the T2000 up and running. I have to say Sun came up a little short on the out-of-box experience at this point. I’m comfortable with, even partial to the absence of on-board video on a server, and Sun has chosen to include serial via RJ-45 on the T2000. Sun was on the right track including two cables appropriately shielded for use with this port, but neglected to include an adapter to go to the 9 pin serial we all have on the back of our PCs.

After rummaging around for the proper adapter to hook the T2000 to an old laptop we plugged it in. The system is surprisingly (read obnoxiously) loud, but hey, it’s a server not a desktop. After a few minutes of the regular hardware diagnostics stuff the system came up to an sc> prompt.

This is where those who are not familiar with Sun’s newer hardware will come to a complete stop. The only documentation included with the system is the Sun Fire T2000 Server Getting Started Guide and that does little more than tell you what the different lights mean and where you can find more documentation online. If you get online and grab the Sun Fire T2000 Server Installation Guide this will walk you through dealing with the SC serial management port. In my opinion Sun should have included this one in hard copy with the system as well.

After a couple more steps things start to feel familiar and the rest of the setup is much like the Solaris 7, 8, and 9 installs I’m used to. With just a few extra steps here you can (and I’d recommend) configure the SC Network Management port which will allow you the same functionality as the SC Serial Management port without the need for the serial cable and adapter. You will need an extra network drop and IP address to use network management, but then you’ll be able to telnet to it for administrative functions. Of course in a production environment you’d want this behind a firewall or on a private subnet.

Other than that the system seems quick. Our next step is to get Oracle on the system and throw some queries at it. I’ll share more as soon as we get some results.

UPDATE: I have now had the chance to test drive some Oracle jobs on this system. Check out my findings here.

database, database administration, database administrator, dba, dbms, rdbms, solaris, sun, sunfire, sysadmin, system administration, systems administration, t2000, try and buy, unix, oracle

SunFire T2000 Try and Buy

SunFireT2000After being tipped of on the Sun try-and-buy program by Alan Baker, a coworker and cohort, I figured I’d throw my hat into the ring for a chance to test drive a SunFire T2000… and today it arrived.

Here’s what Sun has to say about their program:

Toss your toughest workloads at the multithreaded Sun Fire T2000 server with the Solaris 10 Operating System, and watch it crank up your database and Web application performance.

We’re so confident in the quality and performance of the world’s first eco-responsible server, we’re offering a free 60-day trial, risk-free. If you’re not totally impressed, just send it back at our expense and owe us nothing.

Chances are that you will be dazzled by your trial server and come back for more. The new Sun Fire T2000 server will likely become your multithreaded workload energy-saving powerhouse of choice.

When you apply for the Try and Buy program you get the choice of a four, six, or eight core 1GHz UltraSPARC T1 processor. I chose the eight, not just because bigger is better, but also because it is closest to our production Oracle servers in capacity and price.

So once we can find the time we’ll get 64-bit Oracle installed on there and run it through the paces. On deck are some join, function, lookup intensive datamart creation scripts which currently crush our production server every evening. This should be fun.

Also of interest is Sun’s claim of this server being “the world’s first eco-responsible server”. While I am unlikely to bring in a kilowatt meter to verify these claims, we are a very green university and hey, everyone wants to save a few bucks on electric.

UPDATE: I have now had the chance to test drive some Oracle jobs on this system. Check out my findings here.

oracle, database, database administration, database administrator, dba, dbms, rdbms, sun, solaris, systems administration, system administration, sysadmin, unix, t2000, try and buy, sunfire

Top Oracle Blogs

OracleIn no particular order, here are some of the other folks blogging good stuff about Oracle. Most are more up on current topics than I am since I mostly write how-to stuff so I can find it when I need it.

Rittman.net – Mark Rittman’s Oracle Weblog is a fantastic resource with a focus on data warehousing and business intelligence. One of the best sites by an individual.

PeteFinnigan.com – Pete Finnigan has a lot of security related info. A great resource!

AskTom.Oracle.com – While Ask Tom is not really a blog, there is a wealth of information here on most Oracle topics.

OracleDoug.Blogspot.com – Doug has a bit more casual site and, like me, talks about tech and non-tech topics.

OracleToday.Blogspot.com – Not a frequent poster, but some great nuts-and-bolts SQL and PL/SQL stuff.

ThinkOracle.Blogspot.com – Another blog with a potpouri of Oracle topics.

TKyte.Blogspot.com – The Tom Kyte Blog. Tom works for Oracle (see AskTom.Oracle.com above) and some of his Oracle stuff and peripheral interests and opinions spill over into here.

More are out there. Feel free to comment with your favorites and also check out some of my favorite Oracle web resources

oracle, dba, database, database administrator, rdbms, database security, data warehouse

Compressing Extents in Oracle

While Oracle has been moving in the direction of locally managed extents in tablespaces several of us are still working with dictionary managed tablespaces. Here’s a good trick for adjusting table storage clauses in dictionary managed tablespaces.

Note: “compressing” extents is not a way to make data take up less space, but rather a way to take a fragmented (for lack of a better word) table and re-ogranize it into a more contiguous area of storage.

In the older dictionary managed method a storage clause in the table creation command controls how much space was initially allocated to that table (the initial extent) and how much would additionally be allocated if it filled the initial space (the next extent). If no storage clause was specified an often absurd default would be used.

This leaves us with two problems: First, we may have a very low value for the next extent. This can leave us creating extents of, for instance, 32KB even though each row may be 50KB.

Second, we may have tables which already contain a ridiculous number of extents and possibly chained rows. (Chained rows are a topic for another day.)

For my example I will use a table owned by system called session_audit.

To view the storage parameters on a table we can query the dba_tables view:

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
131072 65536

These numbers represent size in bytes, so dividing by 1024 we see that this table is configured to have an initial extent of 128KB and a next extent of 64KB.

We can also count the current number of extents whch make up this table with the following SQL command:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
77

It may seem the right method is to change the initial and next values with an ALTER TABLE command like this:

SQL> ALTER TABLE system.session_audit
STORAGE (INITIAL 10M NEXT 5M);
ALTER TABLE SYSTEM.SESSION_AUDIT
*
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

If we dropped the change to the initial extent we could use this command to change the next extent. If we want to update the initial extent we will need to get sneaky. We could create a new table with the desired storage settings, move the data, then rename the new table to the old name, but this would cause the table to be temporarily unavailable and have the unfortunate side-effect of invalidating any of this tables dependencies. Alternately we could export and import the table but this would lead to the same problem.

Instead, using the ‘ALTER TABLE MOVE’ command, we can tell Oracle to move this table into a different tablespace while at the same time sneaking a new storage clause in.

SQL> ALTER TABLE system.session_audit
MOVE TABLESPACE system
STORAGE (INITIAL 10M NEXT 5M);

Table altered.

We now have our table in the new tablespace with the desired storage clause. This also creates an initial extent of the specified size instead of the smaller extents we had previously. If we want to move the table back to it’s original tablespace we can just issue the same command with the original tablespace.

NOTE: I was also able to tell Oracle to “move” the table to the same tablespace it was originally in. This had the same affect of changing the storage clause and compressing the extents without needing a foster tablespace.

We can now re-check the number of extents and storage clause for the table:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
1

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
10485760 5242880

We see that the number of extents currently being used by the table has been reduced to 1 and our next extent will be 5MB in size.

oracle, database, database administrator, oracle database, dba, sql, database tuning