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.