If you use a vendor package with Oracle you may be one of the thousands
of shops that struggle with optimizing and tuning a vendor systems that
you did not write, and cannot change.
From small departmental application 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.
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 great problem that Mike Ault,
(noted DBA
expert) has written the book Tuning Third-party Vendor Oracle
Systems: Tuning when you can't touch the code:
http://www.rampant-books.com/ebook_vendor_tune.htm
It is considered the vendor's responsibility to provide Oracle
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.
If you are a victim of shoddy vendor optimization for Oracle, insist
upon your rights. As a customer, it is not your responsibility to
hire expensive consultants to configure and tune your vendor's package.
In sum, you should insist that your vendor provide 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
-
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 & Troubleshooting Guide
-
List of critical Oracle metrics to monitor and their root cause
-
Troubleshooting techniques
-
Early Warning techniques
There are many services that provide this service for third-party Oracle
vendors, so there is no excuse for suffering from poor performance and
paying for expensive consulting services:
http://www.remote-dba.net/vendor_support.htm
If you are the victim of a shoddy Oracle vendor implementation, insist
on your rights to be provided with complete and accurate Oracle
documentation. Remember, it's the vendors responsibility!
