ActiveBase Tuning Robot™ 

• Overview

  Business applications suffer from overall performance degradation and long response times. Database experts estimate that about 70% of application performance issues can be resolved by SQL tuning, yet manual tuning of SQL requests is time consuming and requires extensive DBA expertise that is always in shortage.
  With ActiveBase Tuning Robot™  for Oracle you can save time tuning SQL code requests for maximum performance and making sure it performs well before end users are impacted and the business suffers. It improves performance of long-running SQL requests by x10-x100 while saving DBA time and expert resources.
  
  ActiveBase Tuning Robot is powerful and intuitive server-based SQL tuning software that delivers an automatic and continuous SQL tuning process on your production environments. It identifies top-SQL requests from Oracle AWR, analyzes the SQL and identifies quality execution alternatives. Alternatives are benchmarked serially in a tightly controlled manner, highlighting the best execution alternative for your environment optimization strategy (Elapsed time, CPU or IO). With a single mouse click, a SQL request fixing-rule is created in ActiveBase Performance™  proxy in production or pre-production environment, for immediate application SQL fixing (rewrite), without touching application source-code or database configurations.
  
  Examples

    ActiveBase Tuning Robot improved response-time of a Siebel application request from 10 minutes to less than 15 seconds by quickly identifying an Oracle Hint. Fixing the original SQL request in Siebel application to use the hint was performed by a rule in ActiveBase Performance™.
  
    It improved response-time of a proprietary application SQL request from 0.5 to 0.015 seconds. This request is executed a million time per day, resulting in substantial server resource savings.

• Features

  ActiveBase Tuning Robot™  is a powerful and intuitive server-based SQL tuning software that delivers an automatic and continuous application tuning process on your production environments.
  
  Through its automated SQL optimization process, ActiveBase Tuning Robot for Oracle:
    Automatically identifies long-running or resource intensive SQL statements from Oracle AWR or from an input file.
  
    Analyzes SQL statements for alternatives with unique execution plans using Hints while ensuring each alternative have identical result sets.
  
    Benchmarks relevant alternatives against a database environment (production or pre-production replication) to locate the most efficient execution alternative.
  
    Provides detailed execution statistics for comparison.
  
    Sorts benchmark results according to optimization objectives - best elapse time, CPU or I/O optimization.
  
    Enables to identify top SQL requests from production, while benchmarking on pre-production environment.
  
    Creates a summary XML rule file with all best SQL tuning improvements. The rule file can be imported into ActiveBase Performance™  in-line proxy in production or pre-production environment, for immediate application SQL fixing (rewrite), without touching application source-code or database configurations.

• FAQ

  Technical
   How can ActiveBase Tuning Robot help Siebel applications?
  Siebel suffers from long running user requests due to never-ending, resource draining un-tuned SQL requests (using rule based in common Siebel implementations).
  ActiveBase Tuning Robot for Siebel automatically identifies the best Oracle ‘hint’ to dramatically speed response time and reduce resource waste.
  Note:
  In the Tuning robot define: sesionParameters.optimizer_max_permutations=2000
  In case identifying qualified Oracle ‘hint’ alternatives is slow, run ‘ANALYZE’ on the schema with only 1% statistics.
  Analyzing the database schema will speed alternative identification, saving processing resources and time.
  
   Where should the ActiveBase Tuning Robot run benchmark?
  The ActiveBase Tuning Robot benchmark alternatives should be executed on a stable low-loaded pre-production environment, thus enabling to precisely compare the different executions and highlight the best alternative.
  When running the benchmark on a production environment, ensure low overhead by running the benchmark in a serial mode (like opening a single Toad user for testing different alternatives). You can schedule Tuning Robot to run only at low times (e.g., after 16:00 or in the early morning) and stop it whenever production load on the system increases. The benchmark results can be analyzed by importing XML file into ActiveBase Performance or by Excel template reporting.
  
   ActiveBase Tuning Robot relies only on Oracle hints for performance improvement. What about rewrites?
  The main reason for using hints is because rewrites can change the SQL result-set, where with Hints Oracle guarantees the same result set. Another reason for using hints is that the Oracle optimizer already performs many types of rewrites, making them useless.
  Testing possible hints ensures the best execution plan while saving hours of expert DBA time.
  
  Backup
   What should be the backup procedure for ActiveBase?
  You should backup the full installation files once in: home/active/java and home/active/[activebase Product].In addition, to backup full configuration and rules you need to regularly backup home/active/[ActiveBase Product]/cfg/directory.
  
    What processes should be monitored?
  All ActiveBase products run using a single OS process that is defined during product installation-during the administration setup.
  Default service name is the machine name, but it is always recommended to add a prefix: ab_[server_name] for easy identification. The specific process name can be identified in the machine top command.
  ActiveBase also provides a script (monitor.zip found in our FTP site) that verifies user experience by testing user connection time regularly, returning error code and triggering email/SMS notification.