« March 2006 | Main | May 2006 »
April 27, 2006
Analyzing Oracle Objects and Creating Explain Plans
Today Dale and I got our hands dirty and started using some tuning tools on real database objects. Two tools we used are analyze and explain plans. The analyze tool is used to tell Oracle to collect statistics on a particular object (or you can do an entire schema all at once). Once the statistics are gathered, you can then request an explain plan to see how the optimizer intends to carry out the SQL request and what the cost of the operation will be. Read on to see a nice example.
The first thing I learned was that you must use a SQL Plus session, not PL/SQL Developer. The directives just won't work from PL/SQL Developer.
We decided to look at a fairly large table from SWAMP called LID (short for line item detail). We actually used lid_bak, a backup table, just in case... The first step is to analyze the table, like this:
analyze table fic30.lid_bak compute statistics;
Once this completes, we will set autotrace to display only the explain plan (and not actually run the SQL statement), like this:
set autotrace traceonly explain
Next, go ahead and write up your SQL statement. I used a really simple one:
select * from fic30.lid_bak where preq_no = 'PRQ135670';
And this is the output I received:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1654 Card=3 Bytes=43
2)
1 0 TABLE ACCESS (FULL) OF 'LID_BAK' (Cost=1654 Card=3 Bytes=4
32)
What the heck! It's wanting to perform a full table scan to look up the info on only one preq_no? Why? Well, looking at this backup table, I see there are no indexes on it.
I then created an index of the lid_bak table on the preq_no column.
Now let's try that SQL statement again (same exact statement):
select * from fic30.lid_bak where preq_no = 'PRQ135670';
And now look at the output:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=432)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'LID_BAK' (Cost=2 Card=3
Bytes=432)
2 1 INDEX (RANGE SCAN) OF 'BAKIND' (NON-UNIQUE) (Cost=1 Card
=3)
Ahhh, much better! Well, we accidentally learned something here - the value of a good index! Look at the Cost of using the index = 1 , compared to the cost without an index, or a full table scan = 1654. Very interesting.
Next, we turned our attention to a table Dale has been working with sir.raw_data. We ran the statistics on that one and then tried a couple SQL statements against it. One thing we noticed is that this table has about 6 indexes on it, and the optimizer only seems to be using one of them. This is true even if we request columns in our SQL statement that exactly match the columns the index is based on. This means these extra indexes are just excess baggage. Also this table has more than 1,000,000 rows so is a good candidate for partitioning. Next week we're going to fiddle around with this table and see if we can use some timing statistics to show improvement in queries against this table by using some of our new tuning techniques, such as removing unused indexes and partitioning.
Posted by rossm at 11:36 AM | Comments (0)
April 20, 2006
Parts Upgraded
One of the main uses for SWAMP is a detailed tracking of all parts kept in Stores. We keep track of all sorts of things such as price, quantity on hand, last cost, reorder point, how many we used over such and such a time, etc., etc. Every so often, Marsha cleans up a particular area that has become disorganized over time. For instance, certain parts are no longer needed as the equipment they were used on has become obsolete. Also, part numbers are added in to accommodate new stock items. Eventually, certain areas need to be reworked. One such area is the filters and other consumable parts for the lawn mowers and other grounds equipment. Marsha recently reorganized this entire area and assigned new part numbers to every part. It was my job to upgrade SWAMP data to reflect the new organization, without losing any of the historical data on each part. I'm glad to say it went very nicely and a new comprehensive parts list was generated today to show all the changes - it's 97 pages long and looks pretty neat! I always wanted to write a book...
Posted by rossm at 3:29 PM | Comments (0)
Oracle Database Tuning
Today Dale and I finished up the Oracle DB Tuning course offered by VTC. VTC is something I read about on Joe's blog and it has some useful material for us to use. This particular course/area is pretty dense, and there was no way to obtain the scripts used in the course, so Dale and I started taking screen shots of the most important scripts for later use.
We covered three important areas today. First, was dbms_stats. This is a built-in Oracle package that creates statistics. This package can be used for such things as exporting statistics from a production database to a test database which will give pretty good results on tuning even if all the data isn't in the test instance. This points out the main reason for collecting statistics in the first place - the Cost Based Optimizer (CBO) uses them to create the explain plan (that is, to decide how a particular query will be processed). The more statistics, the better.
A second way of creating statistics is by using the Analyze command. This is a way of controlling the collection of statistics from the command line, and is generally pretty straight forward to use.
The final thing we studied today was Oracle statspack. This is a way to collect statistics about the actual performance of a running database. The idea is to take a snapshot of the database, then take another one some time later (15 minutes, say), then use statspack to compare the two snapshots. This one gets pretty involved. I've run it on Aero a few times, but digesting the results is quite a challenge. In typical Oracle fashion there is more information there than you can sift through in a reasonable amount of time.
Next, we plan to implement these different items, and Oracle Enterprise Manager as well, to see if we can identify poorly tuned SQL queries or other bottlenecks on a database.
Posted by rossm at 3:02 PM | Comments (0)
April 18, 2006
Going Production With 2005.2 Upgrade
Last night and early this morning the 2005.2 MBS upgrade was applied to the AS/400 server. It went mostly pretty smoothly except for a glitch around CD #4. Both the upgrade and the addendum have been added and the GUI updates are on the server.
This upgrade is interesting in that MBS has now applied a Java GUI interface to it's main program - we use TextAid (as opposed to TA2). But, this interface doesn't work for the retail side (separate programs we also use). So now we have a Java/Web login for TextAid, the old GUI interface for our other programs, and a command login (called Green Screen) that works for everything. Somewhat confusing!
This upgrade also converted our website to use Java and the Apache web server. Hopefully, this will clear up a recurring order error we've been receiving.
Tonight I'll be doing a full system backup to protect all the work involved in the upgrade.
Posted by rossm at 2:21 PM | Comments (0)
April 12, 2006
Oracle 10g Development Takes a Hit
I found out this week that Troy is leaving WOU for greener pastures. I wish him the best, and it's easy to understand his reasons for leaving - such things as higher pay and cutting about 45 minutes off his daily commute, each way. Still, it's important to know that this is a significant hit to our development of Oracle (this isn't the only thing that will be impacted). Troy has developed some great skills in setting up servers to run Oracle, installing databases, and administering databases. We will miss you, Troy. I wish you good luck, smooth sailing, and following seas.
Posted by rossm at 10:07 AM | Comments (0)
Physical Plant Project Tracking Revamped
The newest and most dynamic SWAMP module is the Project tracking module. We use it as the primary tool for project planning and tracking at the Physical Plant. This means it is regularly revamped and upgraded to account for the myriad changes in this area. Over the last week a substantial rewrite was once again performed. In addition to modifying the data structure (table mods), Dave Morris and I worked on aligning the project budgets, SWAMP tracking, and Banner financial data. The end goal is to fairly accurately reflect budgetary status of major funding groups in a summary type display along with the projects. We made it work. It is still far from a finished product, which will probably never happen, but the financial reporting has taken a great step forward.
Posted by rossm at 9:58 AM | Comments (0)
Bookstore RUSH Week Goes Well, System Upgrade Scheduled
The week of April 3rd was Spring term rush week, and it went by quite well. This is the term where we place a register in the WUC lobby near the elevator to deal with commencement/graduation purchases. It requires the stringing of a pretty long cat5 cable back into the bookstore. In the past we have used WUC's lift to assist in placing the cable above the ceiling, but this year we improvised with a long pole (light bulb changer) and some luck. It went pretty smoothly. IBM has recently come out with a wireless cash register and I have placed one on my wish list - it would be perfect for this kind of application.
By the way, the long awaited 2005.2 upgrade is scheduled for Monday night, April 17th. This is a major upgrade touching pretty much every piece of the MBS software application. It also abandons the Sealms/seagull GUI interface in favor of a Java web-based GUI interface. A late night is in my future! The java interpreter and new web server have already been installed and have been in use for about a month. After a few "gotchas" things seem to have smoothed out.
Posted by rossm at 9:48 AM | Comments (0)