ActiveBase Performance™ 

• Overview

  Business applications often suffer from overall performance degradation and long response time caused by untuned application source-code SQL requests and uncontrolled analysts, DBAs and developers' ´query-from-hell´ requests and jobs.
  ActiveBase Performance™ presents the first in-line SQL*Net network proxy solution, intercepting SQL requests before they reach the database and applying powerful performance rules. ActiveBase Performance rules include SQL rewrite, add Oracle ´Hints´, block while returning a clear message to the user or automatically offload analyst reports to replication/mirror or a historical database.
  ActiveBase software can be installed and configured within hours on the database server or on a dedicated server on Unix, Linux or Windows platforms.
  
  Benefits
  Applied on packaged and proprietary applications and reporting environments where modifying
  source-code is not possible:
    x10-100 response time improvements.
    Block offensive requests from reporting and development tools before they knock your database down.
    Improve productivity of your DBAs and efficiency of monitoring tools.
    Guarantee performance Service Levels.

• Features

  ActiveBase Performance unique SQL*Net Proxy software transparently intercepts long running SQL requests on-their-way to the databases, applying on them a powerful rule engine.
  
  Each rule is built from a ´matcher´ (identification criteria) and a ´action´.
  Rule matchers include Oracle partial execution plan matching, SQL pattern matching, full-scan on large tables, number of partitioned scans, syntax matching, time-of-day, Oracle cost, regular expression matching and user-defined functions. The broad identification capabilities enable a single rule to improve hundreds of similar SQL requests, reports and ad-hoc queries.
  
  When an incoming SQL request is identified by a matcher, an action is applied in-real-time.
  Rule actions include various SQL optimization techniques, including rewrites, SQL text ´search and replace´ actions, or adding Oracle ´Hints´.
  
  Other performance management actions include:
    Blocking action, returning a message back to the user (e.g., block full scan on tables like BILLING_ACCOUNT table)
    Redirecting the SQL request to a mirror/replication
  
             
  Solution Overview            Blocking Toad Return Message
  
  ActiveBase Performance includes three layers:
    A SQL*Net proxy that acts as an Oracle listener, where applications, reporting tools and development tools are configured to connect through it (by changing TNSNAMES.ORA file) or by simply switching the Oracle listener port with ActiveBase listener port).
    A switch receiving all incoming connections, to determine which connections will be routed through ActiveBase rules, which connections will be refused, which connections will be routed to an alternative database, and which will reconnect directly to the Oracle listener, bypassing ActiveBase completely.
    A Rule Engine automatically applying powerful performance rules

• Examples

    ActiveBase Performance improves response-time of a Siebel application request
  from 10 minutes to less than 15 seconds, without touching source-code.
  How: A rule was quickly defined to identify the long request using regular-expression matcher, applying an Oracle ´Hints´ action.
  
    ActiveBase Performance improves a billing application batch process from 14 hours to 20 minutes by rewriting a ´where´ clause condition.
  How: A rule identifies the long request using regular-expression matcher, applying a SQL rewrite to replace the condition with an equivalent condition that enforced faster index use.
  
    ActiveBase Performance identifies offensive analyst reports during peak-hours and offloads them to a mirror.
  How: During peak-hours, a rule identifies the long request using partition-range matcher exceeding 100 partitions on large fact tables, applying a SQL rewrite that adds a dblink and driving_site hint on the ´from´ clause objects, routing to a mirror.
  
    ActiveBase Performance blocks developers and DBAs from performing offensive activities during day-time. Activities include never-ending table scans and unauthorized DML commands.
  How: During day-time, a rule identifies all offensive requests using both SQL syntax matching (for DML commands) and partial execution plans, blocking them while returning a custom message back to the user.

• FAQ

  Technical
   How is ActiveBase Performance different from native DBMS performance features and tools?
  Native DBMS performance monitoring tools typically provide SQL optimization recommendations, with no capability of implementing the recommendations (requires manually changing the application source-code, when possible). This is not feasible when the customer has no control over the application source-code (in packaged OLTP applications), and in large DW and BI environments - where reports and ad-hoc queries are created and changed daily. Oracle's DBMS Outline/Profile feature does not support applications using dynamic SQL requests, provides limited functionality (e.g., cannot apply 'from' amd 'where' clause rewrite), and is impossible to administer and manage in the long run. ActivePerformance provides unlimited SQL rewrite capabilities with easy and centralized management, including audit trail and predefined reports.
  
   How does ActiveBase Performance improve ad-hoc query and reporting in environments defined by large multitudes of requests?
  AB*Performance includes several mechanisms that have been specifically designed to improve ad-hoc queries:
  1. More powerful: as ActiveBase performance rules identify and fix reports based on PARTIAL explain plan matching, PARTIAL SQL text identification, number of partitioned scanned, rules identify performance problems and fix ALL AD-HOC QUERIES WITH SIMILAR PERFOMANCE PROBLEMS!, where a single ActiveBase rule using partial explain plan pattern identification fixes tens and hundreds of reports and ad-hoc daily with similar performance problems.
  Naturally there will always be other ad-hoc queries that will not be identified and fixed by ActiveBase rules, yet the powerful rules puts in place a clear and efficient methodology that makes the current process a much more efficient one. For example, the following identification:
  
  Is applying a hint /*+NO_INDEX*/ to all reports and ad-hoc queries that include within their execution plan the following three actions: Partition range, Table access and index range scan INSTEAD OF PARTITION RANGE SCAN, extremely inefficient as there is a relevant partition key in this table. The rule automatically adds the hint /*+NO_INDEX*/ which causes Oracle to use the partition correctly. As the customer experienced many reports and ad-hoc queries with this problem, the rule is extremely efficient, running minutes instead of hours.
  2. More efficient: ActiveBase central rule management enables to manage and reuse all tuning effort done by the DBA team. No time is wasted at tuning ad-hoc queries that have been already tuned in the past.
  3. Faster: ActiveBase provides an SQL Expert utility that saves DBA time by automatically tuning the SQL request
  
   Why is your blocking by ActiveBase Performance better and safer then blocking provided by other solutions?
  Only ActiveBase software enables to block a specific SQL requests in any application (2, 3 or n-tier applications) without killing sessions or touching application code, while returning a customized notification to the user (multi-language supported).
  Application connections are not torn and sessions are not killed – only the specific request is blocked - protecting productivity. Other requests from different users using the same connection (using connection pool) continue with no application abstraction whatsoever.
  
   How can ActiveBase 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).
  With ActiveBase Performance, performance experts can finally tune Siebel source-code to dramatically speed response time and reduce resource waste.
  
   What load does the additional ActiveBase server impose, if ActiveBase server is installed on the database server?
  ActiveBase server imposes about 1% CPU load, which is regarded as negligible by our customers. It can also be installed on the application server or on a stand-alone machine with no database server overhead.
  
   How can ActiveBase ensures that it is not adding a single point of failure?
  As ActiveBase inline acts as an Oracle listener, the Oracle built-in transparent failover client-feature ensures that ActiveBase does not add a single point of failure.
  In addition, ActiveBase can be installed as a high-availability cluster for failover and load balance with large Billing, Datawarehouse and large scale enterprise application production environments since early 2005.
  
   Installed inline, what latency does the additional routing impose on database requests?
  ActiveBase adds less than a fraction of a millisecond (~150 Microsecond) to each statement on its way to the database server in massive OLTP environments. The results from the database return to the client through ActiveBase, yet propagation is negligible, since no processing is involved.
  
   What platforms does ActiveBase support?
  ActiveBase is currently available for Oracle8.0 and higher running on Windows, UNIX (HP, Sun or IBM AIX) and Linux Operating Systems.
  
   When will ActiveBase be available for DBMSs other than Oracle?
  Support for SQL Server and IBM DB2 is planned for 2010. If you are interested in ActiveBase availability for a specific platform/environment please contact us at support@active-base.com
  
  Installation
   What exactly do I need to install?
  In order to run ActiveBase products, you need to install the ActiveBase server software. It can be installed on a dedicated server or on the database server, or on one of the application servers with Unix HP/Solaris, AIX, Linux or Windows OS. For testing purposes, even a desktop is enough (though we would not recommend it for a production environment).
  
   How large is the download and what does it include?
  The download for the server software comprises 35mb and includes documentation and management console installation.
  
   How long will it take to install and configure ActiveBase software on a single database?
  Installation only takes a few minutes. You will first install the server software, followed by the database configuration wizards and rule-pack sample. Predefined rules will be available immediately, and you will be able to define custom rules using a wizard. For most applications, creating initial rules should not take more than an hour.
  
   Do I need to change my applications or database?
  Absolutely not. ActiveBase Performance is completely transparent to both applications and databases. No client side installation is required. No database configuration change is required.
  To configure ActiveBase Performance software listener:
  1. Add a new entry to the Oracle client centralized routing flat file, (tnsnames.ora or oranames) with ActiveBase server host and listener port number. This file is centrally managed for all clients, thus a single and simple configuration change immediately affects all clients to be routed via the ActiveKnowledge server. For testing purposes, a tnsnames.ora copy can be saved on a local directory on your client.
  OR...
  2. Change the database listener port to listen to another port (hidden) and configure ActiveBase listener port to listen to the primary port (e.g. Oracle port 1521).
  ActivePerformance includes specific functionality to support this type of configuration by enabling selective bypass to specific clients (clients are identified by an include/exclude list of program/host name and OS users).
  This bypass enables all clients to connect to ActivePerformance listener port, where specific clients will be routed through ActivePerformance rules, while other applications will be routed directly to the hidden database listener, bypassing ActivePerformance rules (e.g. ETL processes that do not require performance or auditing).
  
   What permissions do I need in order to install ActiveBase?
  You need sufficient access to allow installation of software on the server. No DBA privileges are required for the database itself.
  
   My company has a strict policy of not installing any 3rd party software on our database servers. Does that mean I cannot install ActiveBase?
  No. You can install ActiveBase on a dedicated server or on any application server, supporting Windows, UNIX (HP, Sun or IBM AIX) and Linux.
  
  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.