From small departmental applications to giant ERP packages, customers are now asserting their right to be provided with reliable documentation about the proper configuration for Oracle.

This is especially true for vendors of departmental applications that are growing their client base and moving from small simple databases (MySQL, SQL Server) to a robust database such as Oracle.

What goes wrong?

The most problematic issues are with vendors who fail to provide instructions for optimizing their SQL statements. No information is provided about the proper optimizer_mode, and their SQL execution plans are not stabilized with hints or stored outlines. The unsuspecting customer is then left with the formidable task of tuning SQL that they did not write and are not responsible for tuning. This can result in tens of thousands of dollars in unnecessary expenses.

These vendor oversights have become such a problem that Mike Ault, (a noted DBA expert), has written the book "Tuning Third-party Vendor Oracle Systems: Tuning when you can't touch the code," which discusses these issues in great detail.

The Oracle industry considers it the vendor's responsibility to provide installation configuration and optimization instructions and to ensure that their SQL has been optimized for the Oracle cost-based SQL optimizer.

Major ERP vendors (SAP, PeopleSoft, Oracle Applications) have long recognized this responsibility and go to great lengths to ensure that their software runs efficiently for Oracle. Sadly, many smaller vendors of applications that use Oracle are negligent in optimizing their systems to use Oracle, and their customers must sometimes spend hundreds of hours trying to understand the vendor Oracle schema.

If you are a victim of shoddy vendor optimization for Oracle, you should insist on your consumer rights. As a customer, it is not your responsibility to hire expensive consultants to configure and tune your vendor's Oracle package, and you should be entitled to a complete set of installation, configuration, and tuning guidelines for the Oracle component.

This has become a huge problem in the Oracle industry, especially among smaller vendors porting their applications from SQL Server to Oracle with little knowledge of Oracle optimization techniques. These vendor applications are often unbelievably suboptimal, with poorly-written SQL, insane default settings for Oracle parameters, and no guidance from the vendor on addressing poor performance issues.

What should you insist on?

You should insist that your third-party vendor provides the following documentation:

1 - Server configuration guide

• Required OS versions and patch levels
• Kernel parameter adjustments (registry settings for Windows)
• Disk configuration options (supported RAID levels)
• Disk load balancing requirements (preventing disk I/O bottlenecks)
• File standards for Oracle server files (names, locations, permissions)
• RAM swap configuration
• Server troubleshooting guide

2 - Configuration guide for Oracle

• Required version and patch levels
• Recommended init.ora settings
• Non-default object settings (PCTFREE, PCTUSED, FREELISTS)
• Tablespace options (locally-managed tablespaces, bitmap freelists)
• Configuration of rollback segments (undo logs) online redo logs and archived redo logs
• Recommended backup & recovery strategy
• List of key tables and indexes
• Partitioning and tablespace segregation strategy
• Sample tnsnames.ora, sqlnet.ora, and listener.ora files
• Statistics generation requirements (dbms_stats parameters, histogram columns)
• Failover (continuous availability) strategy

3 - Management guide for Oracle

• Procedures for addressing trace and dump file errors
• Procedures for reporting application code errors
• Escalation procedures (when to call vendor vs. calling Oracle technical support)
• Monitoring requirements and recommended tools

4- Tuning and troubleshooting guide

• List of critical Oracle metrics to monitor and their root cause
• Troubleshooting techniques
• Early warning techniques

In some cases, dissatisfied customers are forced to hire consultants to come in and tune the vendor's application and then insist that the vendor pay the charges for their shortcomings. There are many services that provide Oracle documentation and tuning for third-party Oracle vendors, so there is no excuse for suffering from poor performance and/or paying for expensive consulting services.

Insist on documentation

If you are the victim of a shoddy Oracle vendor implementation, insist on your right to be provided with complete and accurate Oracle documentation. Remember, it's your vendor's responsibility to provide you with the configuration and optimization details for their Oracle database. Also remember, you did not write the vendor's application, design their database schema, or write their SQL, so you should not be responsible for tuning it.

 

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.