Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

Tuning 3rd Party Vendor Systems

Feb 2, 2015 - Donald Burleson

If you use a vendor package with Oracle, you may be one of the thousands of shops that struggle with optimizing and tuning vendor systems that you did not write and cannot change. Oracle shops are now using giant enterprise resource planning (ERP) solutions such as Oracle Applications, SAP, and Peoplesoft, plus there are thousands of application vendors that are using Oracle as their database.

There are three ways to change SQL execution plans for packages vendor applications where you cannot modify the source code:

  • Swap stored outlines:  You can swap stored outlines and change the execution plan for a SQL statement.

  • SQL Profiles:  For Oracle 10g and beyond, you can tune SQL remotely by swapping SQL Profiles.

  • The dbms_sqldiag_internal package:  See dbms_sqldiag_internal.


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.



 

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational