Mike Ault's Blog

Why Can’t Johnny Use BMF?

Mike Ault — Tue, 29 Apr 2008 15:05:00 GMT

As many of you are aware Quest provides a tool known as Benchmark Factory, BMF for short. BMF is a load generation tool that also allows you to install small databases for testing. The tools out of the box can provide TPC-B, TPC-D, TPC-H, AS3AP and many other standard benchmarks as well as allowing goal and scalability tests from your own code, either manually entered or scanned from text or, in the case of Oracle, from active database memory. BMF does all this for several databases such as Oracle, SQL Server, DB2 and MySQL with no additional licenses required.

In the last year and a half I have been involved with 2-3 clients who wanted to do large scale testing with the BMF suite, like between 2000-20000 users and hundreds of gigabyte databases. Usually the conference call begins with “BMF doesn’t work” and goes down hill from there. However, BMF does work, but there are certain things that need to be addressed before BMF is used.

Johnny doesn’t understand the tool

The first thing to remember about BMF is that it is a load generation tool providing the capability to run benchmarks and other tests against a standard benchmark database or in the case of application specific code, against your own database. As a courtesy we provide the capability to generate the basic database structures and build small test environments for the standard benchmarks. BMF is not a benchmark artificially intelligent facilitator (BAiRF for short.) BMF cannot analyze your entire system including the disk setup and tell you how to reconfigure your disk array, partition the specific size of database tables and indexes and load a terabyte database in under 3 hours. Maybe someday we will come up with BARiF but for now we have BMF.

Johnny doesn’t understand the database

The next thing to remember is that BMF is not an analytical tool that will look at your database setup and make recommendations as to optimizing the parameters or configuration for a benchmark. Perhaps when we build BAiRF we can add in that functionality; however, now you must use Performance Analysis or Grid Control (with the appropriately licensed tuning and diagnostic packs) to give you tuning recommendations. Many database and perhaps system parameters will need to be adjusted to achieve a successful benchmark. A good source for what needs tweaking is the www.tpc.org website in the full disclosure documents. In a full disclosure document the TPC-C author needs to completely describe their system setup, including initialization parameters for Oracle, how they loaded the data, and, how their disk subsystem was organized.

Johnny doesn’t understand the IO subsystem

Another item to remember is that BMF is not an IO subsystem analyzer that can tell you if you have configured your RAID setup with enough disks, have enough host bus adapters or have utilized the proper form of RAID, all of this falls on your shoulders (at least until BAiRF). In order to get a good benchmark result you have to provide high IO bandwidth with maximum concurrent access. This is only achieved through large numbers of disks in a RAID10 configuration, in most cases. For example, in order to get decent TPC-H values (the data warehouse/DSS benchmark) the normal ratio of disk capacity to database size is 20-30 to 1, so if you want to run a test against a 1 terabyte database, you have better provided 20-30 terabytes of storage. This 20-30 to 1 ratio is based on the study of many TPC-H full disclosure documents available from www.tpc.org. This amount of disk space is needed not because of some need to pay large power bills, but because of the needed disk IO requirements of the TPC-H. There is a direct correlation between the number of disks and the success of the TPC-H. Of course having said this, you may notice some recent benchmarks where the disk capacity to database size ratio is much smaller. These are using advanced caching and in-memory database systems as well as solid state disks. A single 2 terabyte solid state disk array can provide 100,000 IO/sec and fits in a 4U rack space.

Johnny doesn’t understand the benchmarks

Finally, all that BMF provides for database benchmark physical build and load is the basic structures and capability to load a small database data set. In order to achieve good results with any of the large scale benchmarks you must add indexes, use partitioning and use advanced loading techniques such as external parallel tables with create-table-as-select (CTAS). I suggest if you need to build large datasets for a large scale test you use the dbgen tool which is available as a free download from, you guessed it, www.tpc.org. I also suggest you spend some time burning the midnight oil studying a few of the full disclosure reports, particularly the appendices where they describe in great and gory detail how they set up the database, system and disk arrays for their test. Why invent the wheel?

Summary

So, what can BMF do for you out of the box? It can install and run small standard benchmarks (however, you may have to add a stray index or two) and can be used to perform scalability tests on code as well as goal tests. The BMF tools can harvest code from text, trace files or active memory and provide the needed scripts to provide for random input of any of the needed variables. I myself have used BMF to test the applicability of partitioning, update on commit materialized views for testing, scalability tests using code from a users application to test new hardware, determine what additional hardware would be needed to support a larger user base and database data set and run TPC-C and TPC-D against solid state arrays in both single and RAC systems. I have also used BMF for harvesting code from single trace files, multiple trace files, active memory and text files as well as done manual entry.

As long as you understand the limitations of BMF, what you want to test and the benchmark or test you are doing, BMF can provide great benefits to your organization. On the other hand if you refuse to do the homework needed to properly layout your disk array and setup your database for the test you wish to run, then you will be faced with great frustrations. So unless you want to wait for BAiRF, just use BMF but do your homework and you should find it satisfactory.

Landmines

Mike Ault — Wed, 23 Apr 2008 13:20:00 GMT

Introduction

At the peak there were an estimated 65 million landmines planted in killing fields world wide. In the 1990’s it was estimated that while 2.5 million landmines were being deployed per year only 80,000 were being removed. According to current estimates this may have turned around in recent years with more being removed than have been deployed per year. Of course with 250 million of the nasty devices stockpiled in arsenals around the world we have a long way to go to rid the world of landmines that usually end up killing and maiming more innocents than enemies. (All of these statistics taken from the “Landmine Monitor Report 1999 - Toward a Mine-Free World”, http://www.icbl.org/lm/1999/english/exec/.)

Now you may be asking, what in the world does this have to do with Oracle? It seems we have some landmines of our own inside Oracle, what I call license landmines. When you install Oracle using the Database Configuration Assistant (DBCA) these insidious features are automatically installed and turned on, just waiting for an innocent DBA to trip them, resulting in license fee explosions that could maim or cripple the budget.

Identifying License Landmines

So, what are these license landmines? Here is a list of their top level names:

Data Masking Pack
Provisioning Pack
Database Change Management Pack
Database Configuration Management Pack
Database Diagnostics Pack
Database Tuning Pack

When you install Grid or Database control you get one quick warning about these (much like the EULA documents we all read when installing software) and then nothing else even if you trigger one of them. Of course internally Oracle monitors usage of licensed features and logs whenever a licensable feature is used. Then, at your most vulnerable point, when you are filing an SR for a problem and send off a RDA report to support, the RDA report has a section on used features for Oracle to use against you.

Disarming License Landmines

However, at least in Grid and Database control it is easy to disarm these license landmines. In release 4 of Grid Control you use the Setup Menu – Management Pack Access option to turn on, or off, any of the management packs for all monitored targets. In the Management Packs Access page you will un-select the packs you do not have a license for and check those you are allowed to use. Now, if you don’t have release 4 of Grid Control, Oracle has provided a way to turn off AWR (no mention of the other packs) with the dbmsnoawr.plb download from Metalink. Once you have the download, you simply do the following to disable AWR:

SQL> @dbms_awr.plb

To execute the package use the command:

SQL> begin dbms_awr.disable_awr(); end;

What Will This Do To My Grid Control?

Now, what exactly happens when you turn off the various packs? Let’s take a quick look at just the top level items that are controlled by various packs.

Data Masking Pack

When the Data Masking Pack is deselected you will lose the following items from the Administration Page from the Database Control area of Grid Control under the Data Masking header:

Definitions
Format Library

Provisioning Pack

When just the Provisioning Pack is deselected you will lose the following item from the Maintenance Page from the Database Control area of Grid Control under the Move Database File Header:

Clone Database

However, the Clone Database option from the Deployments screen is still available.

Database Change Management Pack

When the Database Change Management Pack is deselected you will lose the following items from the Administration Page from the Database Control area of Grid Control under the Configuration Management header:

Dictionary Baselines
Dictionary Comparisons

Database Configuration Management Pack

When the Database Configuration Management Pack is deselected you will lose the following item from the Maintenance Page from the Database Control area of Grid Control under the Software Deployment-Configuration header:

Compare Configuration

Database Diagnostics Pack

From the DBA point of view the most damaging pack to not have a license for is the Database Diagnostics Pack. Without this pack the entire Performance page of Grid and Database control is lost for the monitored databases. In addition to losing the entire Performance page, from the Home page (and all others) you also lose the following links:

User Defined Metrics
Alert History
Blackouts
Metric Baselines

From the Advisor Central page you lose:

ADDM

Database Tuning Pack

The Database Tuning Pack, when disabled, disables the following Advisor Central links:

SQL Access Advisor
SQL Tuning Advisor

For a more complete list of the features in each pack have a look at:

http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/toc.htm

What Now?

Well, first let me retract what I have been saying in some of my presentations (that it may be okay to use strictly the features provided in OEM at the basic level), given my findings during research for this blog I would have to say that the only way to not be responsible for possible license charges regarding these License Landmines is to not use any of their dependent features. I say this because of the fact that when you disable the license pack, the dependent features are also totally disabled in Grid and Database control.

So, if you are like most folks, you probably don’t know what you may have been given or licensed from Oracle when you purchased your database software. I suggest a detailed read of the purchase contract including the fine print, then use the Grid Control (in version 4) to turn off the packs for which you don’t have licenses. If Oracle finds you in violation of license terms, at a minimum they will charge you around $3000/CPU/Host/ Pack for every instance host where the packs have been used. They will also assume all of them monitored by Grid Control have had the executed packs run against them.

Please don’t be responsible for causing a budget maiming, disable those Oracle License Landmines today!

Goal! Success through Storage

Mike Ault — Thu, 03 Apr 2008 14:29:00 GMT

On March 26, 2008 I gave a webcast on physical tuning named “Goal! Success through Storage.” Due to some delays starting it overran a bit so I wasn’t able to answer questions afterward as I usually do. So, here are the answers to all the questions that weren’t handled by Darren Mallet (who handles the questions online when I am webcasting, thanks Darren!)

During the webcast I discussed topics such as choosing the right blocksize, the effects of blocksize on indexes and tables, use of partitioning and parallel query, matching blocksize and stripe depth for RAID, the problems with fragmented indexes and the use of Automatic Storage Management (ASM) in Oracle.

The entire webcast can be found here: Goal! Success through Storage

Session Questions and Answers:

Sai: In a Data warehouse environment, do you prefer to analyze the index online? The table is around 250 GB.

Yes I would use the online option

Pon Venkat: Does rebuild index need an outage in a 9i database?

Starting in 9i, Oracle uses the online index to rebuild a copy and the index is only unavailable during the very small amount of time it takes it to internally swap the old index with the new index. In 9i, an index rebuild should not require an outage, but it will require that space be available for the additional copy of the index. During the rebuild process any locked rows are skipped and retired in subsequent passes. Once all rows are accounted for the needed data dictionary changes are made (the index rename) and the old index is dropped.

Naku Mayo: Would fragmented indexes cause deadlocks to occur on the DB?

No, a fragmented index should not cause deadlocks. A fragmented index will cause long running operations which may hold row locks longer than usual and block other operations from being able to access those rows, but that is not a deadlock it is normal locking behavior.

Satya Sridhar: What's an AU?

In Automatic Storage Management an AU is an allocation unit. In Pre-11g ASM, the AU size was fixed at 1 megabyte for data and 128 kilobytes for non-data. The AU specifies how the data was striped against the underlying disks. In Oracle11g the AU can be set for a diskgroup and internally Oracle will increase the AU as objects get bigger. This increasing AU size is to help reduce the impact of tracking extents on the shared pool.

Craig H. Steblen: Why would someone coalesce indexes instead of rebuild indexes?

An index coalesce is a less intrusive operation that preserves the available space in an index. A rebuild usually collapses the index in size.

Malak: What about the I/O chunk size?

For the purposes of this webcast, stripe width per disk is equivalent to what some experts call I/O chunk size. Usually we refer to stripe depth as the amount of the stripe on each disk and stripe width as the entire stripe size so a non-redundant ASM stripe across 6 disks using the pre-11g AU for data of 1 megabyte would result in a stripe depth of 1 megabyte and a stripe width of 6 megabytes. This would equate to an IO chunk size of 1 megabyte.

An Analysis of Database Issues

Mike Ault — Fri, 21 Mar 2008 16:58:00 GMT

In my ten plus years as a database consultant before joining Quest Software I had the opportunity to visit hundreds of client sites. At client sites I performed analysis of their environments to help them improve the performance of their database systems. In this blog I want to talk about the top ten performance-related issues that I saw over and over again.

The following table lists them in order of occurrence:

Problem	Solution	7/8i	9i	10g	Totals
Initialization Parameters	Adjust init parameters(tune memory)	39	25	9	73
Literal SQL	Use bind variables	21	12	5	38
Shared pool incorrect	Properly size/flush pool	19	15	2	36
Missing/improper indexes	Revise indexes	8	7	5	20
PGA_AGGREGATE_TARGET not used/tuned	Use PAT/Tune		14	5	19
No advanced indexes	Use advanced indexes	6	8	4	18
Improper SQL	Rewrite SQL	8	3	2	13
Indexes wide	Rebuild indexes	8	5		13
No automatic object analysis	Implement Object analysis	7	6		13
RAID improper/not used	Use RAID10 if possible	2	6	4	12

As you can see a majority of shops didn’t have initialization parameters set correctly. Since initialization parameters control memory, options available, how disks are accessed and a number of other performance related areas, bad setpoints cause a plethora of issues.

Of course, the next big item was the use of literals in SQL. This use of literals means that the developers of the application did not use bind variables but instead used the actual literal value in the SQL statement in WHERE clauses. Of course non-use of literals leads to excess CPU, excess latching, re-parsing and thrashing of the shared pool.

The issue of the shared pool incorrectly sized, while related to initialization parameter settings, deserves its own place in the top ten list. The shared pool holds the PL/SQL and SQL libraries as well as a multitude of other items. In 9i the list of shared pool areas was able to easily fit on a single printed page with room to spare, in Oracle11g it takes 17 printed pages. Sizing the shared pool properly is critical, and just because 10g and 11g offer some automation, believe me, setting the base size of the pool is still important.

I would hope that number four on our little hit parade is fairly self-explanatory, if you have missing or improper indexes the optimizer will use full table scans or other less optimal access schemes to get at data. Proper indexes are vital to performance.

In 9i, the PGA_AGGREGATE _TARGET, PAT for short, initialization parameter was introduced. The PAT parameter was going to solve all of our sort, hash and temporary table memory issues. Well, if set properly, PAT does solve many issues, but, we all have seen that one set of processes, a report, an index build, or others, that defy a one-stop-shop approach to sort or hash area tuning. I’m afraid we must still use sort and hash area settings on special occasions. Make sure you set the PAT such that at 5% per process it will handle hashes and sorts.

The issue with ‘no advanced indexes’ refers to not using function-based indexes, bitmap indexes, reverse key indexes or index-only-tables when they should be used. Oracle has given us a multitude of indexes that, when used properly, can greatly enhance our database performance.

‘Improper SQL’ means using too complex, over-written SQL, or, writing SQL that doesn’t take into consideration Oracle’s built-in limitations. For example, doing over a 7 table join in 9i and above means you may have to monkey with table order in the FROM clause. Why? Because in 9i and above the MAX_OPTIMIZER_PERMUTATIONS parameter becomes a hidden parameter set at 2000 since the join paths equate to n! (a factorial of the number n) where n is the number of objects in the join. As such, 6! is 720 and 7! is 5040 which means Oracle may not get to table 7 moving left to right evaluating paths before it reaches the limit!

The ‘indexes wide issue’ actually points to the whole rebuild/don’t rebuild indexes issue. I am not going to argue that point right now. However, if there are excessive empty slots in your monotonically increasing indexes (meaning they will not be refilled) then your indexes may need coalescing or rebuilding and if those indexes are used for index scanning operations you will see a performance improvement. Enough said.

The ‘no automatic analysis issue’ kind of falls by the wayside in 10g and 11g, however, since many of you are still on 9i and some on 8i it bears mentioning. The cost-based optimizer runs on statistics. Bad statistics lead to bad execution plans. However, there are certain tip-over points, a sort of cost-based optimizer no-man’s land, where the old plan is better than the new plan. I suggest an automated plan that first exports critical table statistics and then performs analysis so that you have a retreat path. You will be happy to know that in Oracle11g you can have automated analysis but it can be setup so that the new statistics are only used after you approve them.

The ‘RAID issues’ are a reflection of the whole RAID10 verses RAID5 issue. I like RAID10 because it provides a higher IO bandwidth using modern dual read/write controllers that can read or write on either disk in a mirror, than a RAID5 setup for the same storage volume. Of course you JBOD users who don’t use RAID could gain enormous benefit from either form of RAID.

So those were my top ten issues I would see over and over again at various sites. Sometimes sites would have almost all of the top ten and many of the other issues as well. Hopefully this list will get you looking in your environment to see how many you can eliminate.

First, don’t Panic!

Mike Ault — Tue, 12 Feb 2008 16:28:00 GMT

I was working the other day to rebuild my Linux 32 bit cluster in preparation for doing some tests between OEM and PAO for some internal training. I planned to use raw for my cluster configuration and voting files for CRS and ASM for the rest of the shared files. The 32 bit cluster had been up and operating fine and I had completed the testing I was doing so I retasked the drives to build a 64 bit cluster. I dropped the database and turned off ASM and retasked the disks.

Now comes this week and I want to rebuild the 32 bit cluster. I reassign the drives to the 32 bit zone and re-partition them to clear any flags set during the (unsuccessful) attempt at a 64 bit cluster using RedHat 5.0. I then attempt to restart CRS. Well, nothing worked so I reinstalled CRS from scratch. I could get one side to see the raws but then the other couldn’t. After battling the raws for a day or so and filing a Sev 2 SR (still no response by the way) I decided to chuck it all and use OCFS2, this would also allow me to retask several drives back into the ASM array as well. I set up the OCFS2 shared drives (2 of them) with no problems other than the normal first time issues which were resolved within a day. I then tried to build a database.

First I checked out the headers of the various drives and mapped SDA-SDS (skipping SDM and SDO as these were my OCFS2 system) drives as ASM devices by marking the headers using /etc/init.d/oracleasm. Those of you with rudimentary math skills should have an alarm going off right about now, as a hint, realize I only had 18 drives in my disk array. Next, I ran the DBCA and created an ASM RAC instance using the ASM marked drives in 3 disk groups, DATA, INDEX and RECOVERY.

Now I attempted to create a RAC instance. As soon as I started I realized something was amiss. The DBCA splash screen jumped over the choice screen for RAC verses normal databases. Thinking perhaps Oracle had changed the order of screens I went ahead and followed through getting all the way to the last screen and still no RAC choice. After several unsuccessful attempts at troubleshooting I decided to just reload the database software, so I deinstalled using the OUI and then cleaned out the residual files, followed by a reboot.

Nothing is as heart wrenching to a Linux SA as expecting a normal startup and getting the “grub:>” prompt instead. Getting the “grub:>” bootloader prompt means for some reason the system recognizes that it should be a Linux system but can’t mount the needed filesystem to make it so. Searching through my mounds of CDs I found the first RedHat 4.0 install disk and rebooted into Linux Rescue mode, sure enough it couldn’t mount the filesystem that contained the boot systems for Linux. Looking over at where it loaded the system as best it could I noted the /boot directory was empty. My first thought was that I maybe ran the “rm –rf *” command on the wrong directory. I used a scp copy to restore the files form my second 32 bit node and rebooted, still a “grub:>” prompt. Back into Linux Rescue and still it complained that it couldn’t mount the file system. Then the alarm bells started going off as the little voice in the back of my head got louder and louder. I had corrupted the header of the boot filesystem by marking it as an ASM disk. Counting from device SDA-SDR yields a count of 18, SDS makes it 19 and was actually the first internal system drive I had marked as DISK8 for use with ASM…oops. I felt a cold chill. How to recover the partition I had corrupted? Looking at it under Linux Rescue all the files were still there, but it wouldn’t mount. First, I went into fdisk and dropped and recreated the first primary partition. No go. It still wouldn’t boot and the files were still there (thank goodness). But what now?

Thank goodness for Google. I found the solution at:

http://linux.omnipotent.net/article.php?article_id=12488

First, I tried the e2fsck with no arguments against the SDS volume:

# e2fsck /dev/sds1

It complained about a bad superblock, so I used the mke2fs with the “-n” argument to read the alternate superblocks:

# mke2fs –n /dev/sds1

Armed with a list of alternative superblocks I then used the e2fsck command specifying it use an alternative superblock:

# e2fsck –b 32788 /dev/sds1

And viola! It repaired the various things wrong with my filesystem. I then was able to reboot and everything came up ok. Let me tell you I was not looking forward to reloading RedHat 4.0, then downloading several years of patches and applying them and re-downloading and installing Oracle11g from scratch -- so I was glad it worked.

Maybe next week I will be able to do my testing before I run off to RMOUG. If you are going to RMOUG, come by my talk on “The New Tuning Universe of Oracle 11g”. See you there!

Q&A from Webcast on using Indexes to Boost Performance

Mike Ault — Thu, 24 Jan 2008 21:20:00 GMT

I recently gave a webcast about using indexes to boost your application performance, of course at the end of the webcast we took questions, unfortunately I wasn’t able to anser all of the questions so I pulled the ones I felt were most important and decided to answer them here in my blog. For the full webcast see : index webcast

Here are the questions from the webcast:

Jayesh (AFL) Asked: if you have high db file sequential read and db file scattered read does that indicate lack of index?

They aren’t related, you can have both on the same database/table

DB_file_sequential_read indicates index reads
Db_file_scattered_read indicates full table scans

If you look at the individual SQL and you have high db_file_scattered_reads, or a full table scan in indicated in v$sql_plan then full table/index scans are occurring when the SQL runs. If the reads are due to a full table scan, then the table may be an indexing candidate.

haj Asked: how do you know you've too many indexes on your table? and how do you know whether those indexes are used?

Too many indexes will show up as excessive insert, update or delete times, high db_file_sequential_reads during (Insert, Update and Delete) IUD, large numbers of physical reads/writes during IUD. You can review the v$object_usage VPD to determine if an object has been used. The v$sql_plan table will show if the index is being used in your current execution plans.

Michael Sew Asked: index monitoring only shows a boolean, whether it has been 'ever' used, doesn't it? not number of uses or frequency of use.

Sorry, you are correct, the earlier versions had accumulators as I recall, the latest versions don’t.

SQL> desc v$object_usage
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 INDEX_NAME                                NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 MONITORING                                         VARCHAR2(3)
 USED                                               VARCHAR2(3)
 START_MONITORING                                   VARCHAR2(19)
 END_MONITORING                                     VARCHAR2(19)

JayR Asked: Any performance proactive measures/procedures can be taken for Oracle domain indexes?

The biggest thing with domain indexes is memory, according to Oracle’s tuning guide:

“You can set your index memory with the system parameters DEFAULT_INDEX_MEMORYand MAX_INDEX_MEMORY. You can also set your index memory at run time with the CREATE INDEXmemoryparameter in the parameter string.

You should aim to set the DEFAULT_INDEX_MEMORYvalue as high as possible, without causing paging.

You can also improve Indexing performance by increasing the SORT_AREA_SIZEsystem parameter.

Experience has shown that using a large index memory setting, even into hundreds of megabytes, will improve the speed of indexing and reduce the fragmentation of the final indexes. However, if set too high, then the memory paging that occurs will cripple indexing speed.

With parallel indexing, each stream requires its own index memory. When dealing with very large tables, you can tune your database system global area (SGA) differently for indexing and retrieval. For querying, you are hoping to get as much information cached in the system global area's (SGA) block buffer cache as possible. So you should be allocating a large amount of memory to the block buffer cache. But this will not make any difference to indexing, so you would be better off reducing the size of the SGA to make more room for a large index memory settings during indexing.”

Steve Asked: When mentioning proper storage parameter can you give examples of what proper means?

The three parameters that affect index storage performance the most are pctfree, initrans and block size. The pctfree parameter determines how much space the index reserves fro updates to index values. If pctfree is insufficient then block splitting can occur. The initrans parameter determines ITL list structure, if there are insufficient ITL spots in a block due to the block being full, then any transaction needing an ITL slot will serialize and cause performance issues. Block size determines index density, for non-RAC installation the larger the block for a B-tree the better storage efficiency you get. For bitmap indexes that lock at the node level, a larger block can result in more locking issues.

By ensuring you have enough pctfree to handle updates and initrans to handle transactions you can eliminate many of the wait situations. In later Oracle releases these parameters have been automated to some extent. The problem with monotonically increasing index values combined with large deletes causing index “white space” usually cannot be fixed with storage parameters.

RobC Asked: Can multiple bitmap join indexes be created between two tables?

No, since a bitmap join index goes between the foreign key columns and primary key of the related table it cannot have more than one. That being said, if you have surrogate keys with unique constraints against them, in addition to the PK columns, you might be able to create a bitmap join between that column and another table, however, that would be a non-3^rd normal form condition, and I have not tested that possibility.

Cindy Ullery Asked: Does a large # of full table scan create busy buffer waits.

Only if the system has memory issues as well, in that case the full table scans tie up buffers that should be used by other processes causing buffer busy waits.

Lily Asked: How to query V$SQL_PLAN to review the plan? What are the most important columns you look at?

I use:

select
 a.hash_value,a.object_owner,a.object_name, rtrim(a.operation) operation,
 a.options, sum(b.executions) executions, c.bytes, 
 (sum(b.executions)*c.bytes)/(1024*1024) fts_meg
from
 v$sql_Plan a, v$sqlarea b, dba_segments c
where
(a.object_owner=c.owner
and a.object_name=c.segment_name)
and a.address=b.address
 and a.operation IN ('TABLE ACCESS')
 and nvl(a.options,'NULL') in ('FULL','NULL')
 and a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS')
 --and b.executions>1
group by a.hash_value,a.object_owner, a.object_name, operation, options, c.bytes
order by a.object_owner,a.object_name,operation, options, executions desc
/

Bob Asked: Would you recommend dropping an index prior to loading it, then re-creating it after the load or setting the index to unusable prior to the load, then just rebuild it?

I would test both scenarios as it would depend on the percentage of the index entries being added, deleted or updated.

Sanjay Makwana Asked: What is impact of optimizer_index_cost_adj on explain plan?

It only affects full index scan type access paths. However it can be used to increase the likelihood of index scans or, decrease them depending on its setting.

Todd Adams Changes Question To: in your research, have you found a soft limit on number of indexes before performance is effected? also in addition, how about the number of columns in an index before it is effected.

Here is a quick test:

It isn’t the number of columns it is the number of IOs needed to support the indexes.

antony Changes Question To: using bind variable of a SQL chooses different index when the bind variable value changed. why?

This could be caused by skewed data with the use of histograms. With bind variable peaking the path can change based on the histograms telling the optimizer it is better to use a full table scan due to the amount of data being returned.

Li Mei Lin Changes Question To: when upgrading from 9i to 10g, do we need to change the indexes?

No.

antony Changes Question To: how column order of an index affects the plan?

Let’s look at an example:

SQL> create index test2 on tables (table_name,tablespace_name);

Index created.

SQL> select table_name,status from tables where table_name='TABLES'
SQL> /

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3115918026

--------------------------------------------------------------------------------
| Id | Operation                    | Name   | Rows  | Bytes |Cost(%CPU)|  Time|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    23 | 3 (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID | TABLES |     1 |    23 | 3 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN          | TEST2  |     1 |       | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------- 
   2 - access("TABLE_NAME"='TABLES')

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          2 consistent gets
          0 physical reads
          0 redo size
        335 bytes sent via SQL*Net to client
        370 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> drop index test2;

Index dropped.

SQL> create index test2 on tables (tablespace_name,table_name);

Index created.

SQL> select table_name,status from tables where table_name='TABLES'
 2 /

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 624263108

--------------------------------------------------------------------------------
| Id | Operation                    | Name   | Rows  | Bytes | Cost(%CPU)|  Time|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    23 | 7 (0) | 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID | TABLES |     1 |    23 | 7 (0) | 00:00:01 |
| * 2 |   INDEX SKIP SCAN           | TEST2  |     1 |       | 6 (0) | 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TABLE_NAME"='TABLES')
       filter("TABLE_NAME"='TABLES')

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          7 consistent gets
          6 physical reads
          0 redo size
        335 bytes sent via SQL*Net to client
        370 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

So the skip scan was a more expensive operation than the direct scan, in this case by a factor of over 100 percent, simply by reversing the order of the index we get better performance.

Bryan Marsh Asked: Are artificial keys a bad index strategy for OLTP systems?

There is a constant debate about the use of artificial (i.e. sequences) keys verses use of “natural” keys. If a “reasonable” natural key exists, use it, however, if the “natural” key results in an unwieldy monstrosity of a character based composite key, then use the artificial key. You don’t want to have to carry over a three column, 250 character three column key into multiple related tables if a single integer value artificial key will work just as well.

Milan Nikolic Asked: What is typical performance overhead of index monitoring, in your experience?

Not measurable.

Gia Huynh Asked: A design question: Should I create an index on a customer address column defined as varchar 255 for online look up?

It may be advisable to look at deriving a signature on the varchar then use the signature for lookup if it is a full lookup scenario such as address matching. If individual components of the 255 character field need to be searched, look at using domain indexes instead.

Fuad Arshad pointed out that the requirements for using Cost based optimizer and the query rewrite parameters may be incorrect for use of function based indexes, so test in your environment if you wish to use them. In fact they are probably usable under rule based optimization.

Deepak Asked: Quest optimizer has a way to monitor, identify and analyze current performance of indexes and does it advice how to build new indexes (scripts for new sets of indexes) to achieve optimized performance?

Answered: Quest SQL Optimizer has a module to recommend new indexes along with analysis of the execution plan on additional SQL's of creating that index

The tools used during the webcast included:

Quest Performance Analysis for Oracle
Quest SQL Optimizer for Oracle
Quest Benchmark Factory

Questions from the Instant Replay Webcast

Mike Ault — Wed, 16 Jan 2008 17:56:00 GMT

Well, here I sit at 30,000 feet over the Atlantic heading for London and then on to Abu Dhabi. It seems like just yesterday I was doing a webcast for the Performance Allstars series on Instant Replay and the use of history to find problems, oh wait, that was yesterday!

During the webcast many excellent questions where asked concerning historical data capture and usage so I thought I would answer some of the more prevalent ones in this blog entry. The star of the webcast (no it wasn’t me) was the Performance Analysis tool that Quest provides, the tool supplied virtually all of the screen shots used to demonstrate the topics discussed.

Performance Analysis for Oracle (PAO for short) can either execute on the same host and in the same database (not recommended) or in a different database on the same host (better) or on a separate host and instance (the best option). PAO uses a multi-tier architecture using agents to gather and report statistics back to the PAO repository. Data in the PAO environment is gathered through a low impact memory peeking methodology and is throttled at 3% of the CPU on the host on which the agent is running, though usually it runs at less than 1 percent.

Data in the PAO repository can be kept as long as you wish, for comparison, data in the Oracle OEM is generally kept for only 7 days unless you specifically alter the parameters that control collection of statistics. PAO only uses the stealth collection technology mentioned while OEM uses a combination of technology but mostly brute force queries against underlying V$ and base data tables. In OEM you would never run with the performance repository in the same database as your production database (unless all that you where running was Database control, which must run n the same database.) In my limited laptop environment I have tried it and under high load data collection will halt, leaving gaps in OEM statistics, not to mention the high overhead. Usually OEM also uses a multi-tier structure consisting of an agent based technology and separate repository. Both PAO and OEM can monitor multiple databases.

PAO consists of both statistical and performance data as well as change control tracking data such as index and table alterations, statistics collections or changes, database parameter changes and changes in execution plans. Each time change control is run (by default once every day) it examines the entire database and reports on these changes. By default OEM will track and report statistics usually retaining seven days worth, however, where the boundaries between licenseware and safe use run is never quite clear. In OEM change tracking is a cost plus with the change control pack. Detailed analysis using AWR, ADDM and ASH are also considered cost plus with the Database Performance pack. The general user of OEM needs to be aware of the many license landmines waiting to trip up the casual user as the only warning concerning what is licensed and what is not is a single screen that shows up during the install process never to seen or heard of again.

In general here is what is in some of the various packs for OEM, if you are using any of these features, watch out!

Database Diagnostic Pack
- Automatic Workload Repository
- ADDM
- Performance Monitoring
- Event management
- Event history
- Blackouts
- Dynamic metric baselines
- Memory performance monitoring
Database Tuning Pack
- SQL Access Advisor
- SQL Tuning Advisor
- SQL Tuning Sets
- Reorganization
Database Configuration Pack
- Database and Host-configuraiton
- Deployments
- Database-patches and patch-sets
- Patch download via Metalink
- Clone database
- Clone ORACLE_HOME
- Configuration compare

According to Oracle 75% of you don’t buy management packs, but I’ll bet you use them! Many of the questions concerned the cost of PAO verses the cost of OEM with the needed licenses to meet the same functionality, I am afraid I will have to defer such questions to the customer service representatives, however the standard license costs of Oracle add-on packs is $3000/pack/CPU so for a single CPU monitoring instance we are talking at least several thousand dollars.

One interesting note is that you can actually utilize the same instance to store the OEM, PAO, and RMAN (Oracle recovery manager) repositories, I usually set things up that way when I was doing DBA work, it didn’t make much sense to have several instances for these management repositories.

One item demonstrated was the ability to compare two time slices, for example a time slice for a process set that is performing badly verses one where the system was performing properly. This capability allows you to see exactly what changed in the statistics making problem resolution much easier. As far as I know, OEM doesn’t have this capability, if anyone has seen it OEM please tell me.

OEM uses a web-based display while PAO uses a GUI client. The GUI client allows a richer graphics set to be utilized providing better graphs and data representations. PAO provides many reports, including management high-level performance overviews and detailed performance history reports including the capability to compare two time-slices. The reports can be printed, emailed or posted to a shared folder.

Hopefully this blog has cleared up some of the questions raised during the webcast. In my next blog I’ll provide some of the interesting results for the various pools we have presented in the two webcasts in this series so far. Well, my rubber chicken dinner is arriving so I’ll sign off for now, this is Mike at 30,000 feet saying so long for now!

Further Travels in Betaland

Mike Ault — Fri, 28 Sep 2007 22:13:00 GMT

Well, I decided that Linuxtown wasn’t going anywhere very fast and with the all the SRs I was leaving in my wake I soon would not be welcome by the locals so I decided I would take it on the lamb over to Windowsville for a while. Besides it was looking like the local disk array was going to pin a power supply failure on me.

Let me tell you, they don’t have it much better. If you are expected to be surrounded by happy smiling Oracle applications holding hands in Windowsville on Oracle11g you may have a surprise. My first wakeup call in Windowsville came at the end of the OUI Install phase when the local DBCA refused to hook up with the instance when it was trying to make a move on the DBControl install. Two SRs (am I going to see a repeat of Linuxville? Will I have to move to Solariston?) later I had something like a working instance. Definitely not a marriage made in heaven let me tell you. Eventually we had to go in by the back door using a set of jimmies and the scripts I coerced DBCA to create. After manually finagling in some passwords and killing the zombie instance we had unwittingly created (note: A direct jolt from DBCA failed to kill it, I had to pull a knife trick with SQLPLUS and DROP DATABASE) I got a working instance running, however, I’m afraid dbcontrol/console or whatever alias it is using this week was still DOA he would get right to the point of climbing off the table, clutch his heart and fall over with a bad security certificate and a failure to connect, RIP.

So right now I’ve pulled out my handy Benchmark Factory Swiss army knife and am currently putting the instance through its tricks on some hard hitting TPCC tests. It breezed through a 20 user test, so I upped the anti to 100 users. Once I determine if this instance is just stringing me along waiting to spring some odd errors and failures or is the least bit stable, mayby I’ll spring some Real Application Testing on it. I’ll follow that up with a little SQL Replay action. I’ll keep you posted.

Remember I am still on the 32 bit side of the tracks with only 1 gigabyte to my name so I am pushing the limits here. Maybe if I get some more stake money I’ll move over to the 64 bit side of Windowsville with some serious gig.

Messing With 11g 32bit RAC

Mike Ault — Wed, 05 Sep 2007 17:25:00 GMT

Well, the 32 bit 11g beta is officially over with the GA of the 11g 32 bit release on the Oracle download site. I replaced my beta copy with the production release and started playing about 2 weeks ago. I thought you all might like to know some of the “gotchas” I have run into so far.

Granted, some of these may be my fault and yes, I have filed SRs on them and Oracle has been responsive in troubleshooting them, however on the off chance they are bona fide bugs I would like to give you all a heads up.

The install went nearly flawlessly, the new OUI interface looks classier than the old one and seems to do a good job. I installed the CRS (cluster ready services) first and it went very smoothly. One little change, the OUI requires a separate CRS home so be prepared, in fact, it requires it not to be a part of the ORACLE_BASE location so I just suggest adding a /home/crs area (on UNIX/Linux) for it. There are also new users (crs, asmadmin) and new groups suggested (crsadmin, asmadmin) to allow finer control over who manages what.

After the CRS is installed then you install the database software. Since I was doing a RAC install I installed ASM as well. I had to manually download and install the ASMLIB utility (at least I couldn’t find oracleasm when the install was complete) and before the system would recognize my ASM disks, brand them using the oracleasm utility. I ended up doing a software only install then going back with the database creation assistant (dbca) to perform the actual installation of the ASM and database instances due to the missing oracleasm utility and the inability of the dbca utility to recognize that non-formatted, non-mounted disks where ASM fodder.

I installed the ASM instance first (of course, rather hard to install the database first) as a part of the database install and it allowed me to only create a single diskgroup, DATA, I then backed out and just did an ASM maintenance run of DBCA to add a second diskgroup, RECOVERY (more about this later).

I then re-ran dbca to create the actual RAC instance. Everything seemed to go well. At the conclusion of the install I attempted to log in with SQLPLUS, it reported the memory area was not present. Attempts to access the ASM instance also failed even though both showed the normal background processes and srvctl reported them up and operating. The asmcmd utility also could not access the ASM instance. I shutdown both the ASM and database instances and then restarted ASM manually. After a manual restart via SQLPLUS I could log in to ASM with SQLPLUS, but srvctl would not log on reporting placement errors. In addition asmcmd could see the ASM instance, diskgroups, disks, etc. after the manual startup. I started ASM on the second node using srvctl, it seemed to start ok, but again I couldn’t access it, I shut it down and restarted it manually and everything seemed to be working fine.

After verifying the ASM was working (at least for SQLPLUS and ASMCDM) I manually started the database instance, it errored out saying it couldn’t see the RECOVERY diskgroup and thus couldn’t see the control files. I checked with asmcmd and sure enough, no RECOVERY diskgroup. I checked the init.ora for the ASM instances and in the ASM_DISKGROUPS parameter, only the DATA diskgroup was listed. After adding the entry for the RECOVERY diskgroup and bouncing the instances asmcmd showed both diskgroups and the database was able to startup manually, viola! I could see it from SQLPLUS and log in remotely.

I tried several times to start the ASM and database using the srvctl routine, each time I could only see it via the srvctl commands, but any attempt with remote or local SQLPLUS login met with failure.

Next, I tried to create an additional tablespace. No matter what I tried, I couldn’t create more than a 8 gigabyte (actually just less than that) datafile for my tablespace. Using a 8K blocksize and normal (small) datafiles I should have been able to create a datafile just a little smaller than 32 gigabyes in size. Finally I checked the df –k for my regular filesystems, sure enough the ORACLE_HOME filesystem report zero space available, a quick check of $ORACLE_HOME/dbs showed Oracle had created the datafile not in +DATA on ASM where it should have but in the default location when the “db_create_file_dest” parameter is not set. I checked the database initialization parameters and sure enough, DBCA had not set the default location for file creation. I reset the parameter for both instances and then had no more issues with file creation.

After creating the needed data and index tablespaces I began loading a 300 gigabyte TPCH export. Man was it going slow! During a particular large burst of activity the database fell over and one node restarted. Whoa! This is not supposed to happen! I looked at the alert log for the ASM instance…hmm…normal startup right up to the point I saw:

“WARNING: No cluster interconnect has been specified. Depending on
           the communication driver configured Oracle cluster traffic
           may be directed to the public interface of this machine.
           Oracle recommends that RAC clustered databases be configured
           with a private interconnect for enhanced security and
           performance.”

And a bit further down:

“Cluster communication is configured to use the following interface(s) for this instance
192.168.1.103”

Yep, routing of an interconnect over a pubic Ethernet will cause reboots sure as shooting. I looked at the cluster configuration file via an OCRDUMP and it says it is using the private interconnect, I double check the hosts file and the private interconnect it properly configured to a 1 gigabyte dedicated NIC configured as 10.1.1.1. Strange doings. I set up the interconnect to use the 10.1.1.1 NIC (node 1) and 10.1.1.2 NIC (node 2) by using the cluster_interconnects parameters and then restart ASM, yep, it likes that better.

I restart the load assuming it was an ASM problem, however, I look at the related RAC waits, sure enough the normal instance interconnect latencies are sky high and climbing. I kill the load and look at their alert logs, see the same problem, even though, once again, the OCRDUMP shows they are using the private interconnect. I reset their cluster_interconnects parameter and restart.

I restart the load, only to have it fail at 06:05 the next morning. I look at the alert log and see that the automatic maintenance kicked in at 06:00 and set up a resource group…I file an SR but when the load once again fails when nothing is happening I recheck and see some IO errors…the problem is tracked to a bad fibre cable.

Finally, I am loading my 300 GB TPCH instance, it should be interesting to see what comes up next!

The Joys of Beta Testing

Mike Ault — Mon, 30 Jul 2007 16:48:00 GMT

I am participating in the Oracle11g beta. The next time I tell you I am going to do a beta please lock me up until the fit passes. I have subjected myself to this abuse since Oracle8, usually tied with writing or updating a book. Maybe I am getting smarter as I get older as I am not (currently) involved in a book project although there may be one or two more in the pipeline in the future.

To begin with you need to apply for the program which basically means writing down a detailed project plan including all participants, features you plan on testing and a time schedule for the testing project as well as a commitment to give your first born applications over to Oracle at the conclusion of the test (well, not really, but they do ask how soon you will be releasing a product using the new release.) You must also sign waivers and promises not to discuss anything about the features you test without approval signed in blood from Oracle’s lawyers.

Once you are approved, they allow you to download the software, then the real fun begins. Oddly, it seems the least tested part of the beta is always the loader. In my test an important part was the upgrade from 10 to 11, unfortunately, the upgrade for the CRS (from 10 to 10.2) failed and I ended up rebuilding my test database and the migration testing will have to wait. I then created a 200 gigabyte TPCH database and commenced testing. Of course I found some issues and reported them. Some required upgrade to dump 4, then dump 5 of the software, of course then the SRs from the dump 3 requested additional testing on dump 3…oops. It also seems there is no internal upgrade from dump to dump (boy did they name those correctly!) so at the end of the “load this dump” and “load that dump” I ended up with a 200 gig, non-functional database...oh well, it only took three days to build it. Luckily I took an export before I started upgrading (see, I do learn some things.)

Finally, the 64 bit version was released and I then had to standup the two 64 bit servers that had been waiting patiently in the garage. This involved unpacking the servers, lugging them into my office, loading RedHat on them and bringing my old disk array back from retirement for the 32 bit servers to play with. Of course plugging in all this equipment to one 20 amp line tripped the circuit so I had to run a heavy duty extension cord into the room for a separate circuit. Once everything was up and humming the room temperature soon reached close to 90 degrees Fahrenheit, so it was off to Lowes to get a portable air conditioner (a DeLonghi Pinqunio 15,000 btu) and rigging it to exhaust outside. Note that duct tape will not work on a warm exhaust pipe from a portable air conditioner, the adhesive gets warm and gooey rather than sticky, however, a flexible, self adhesive ace bandage worked tops.

Did I mention the trip to Fryes to get more Ethernet cables, GigE cards for the 32 bit servers to allow use for the Network block devices and GigE switches? I then discovered that you need to get the clustering option to get full network block device support and that the old server I had intended to use as the NBD server may not like GigE cards…so it was off to Ebay again and I found a NexStor 8f fibre channel array chassis (sans disk drives) and put in a bid…I got it for 1 cent plus $50 shipping. I have several 18g fibre channel drives and found 5-74g fibre channel drives on Ebay for about $80 (with shipping) so I will probably not do the NBD configuration I described in my last blog, but will instead have both 32 bit and 64 bit clusters running on (albeit old) NexStor fibre channel arrays (an 8f and a 18f.)

Unfortunately time has also marched past my NexStor 18f fibre channel array and the fibre channel cards (LC) in the new servers where not able to work with it, the final straw was the LSI connectCom cards (HSSDC) I had used in the 32 bit testing where not compatible with the new DELL servers so I had to go on Ebay and buy some QLA2200’s with HSSDC copper type connectors (about $120 with shipping), more delays of course. After loading the Redhat 4.0 ES release that had just been sent me a couple of weeks ago, I find that the kernel has to be at a level that is only available as a download. After 3 hours of downloading the ISO images and burning the CDs I am finally loading the proper OS version on to the servers. Maybe I’ll have my 64 bit RAC cluster up by this afternoon…right.

From somewhere in beta land, single handedly supporting Georgia Power…this is Mike Ault signing off.