ActiveBase the performance, manageability and security solution for Oracle based applications

Technical
How large is the download and what does it include?
The download for the ActiveBase SQL Expert client software is ~34MB and includes the SQL Expert installation and documentation.

How long will it take to install and configure SQL Expert on a single database?
In most cases, installation and configuration take less than half an hour.

What are the configuration steps required?
1. Define target database: Define database parameters for connecting to the database. ActiveBase Expert opens a jdbc connection to analyze and run serially (benchmark) the different execution alternatives. To analyze different statements, DBA user name requires the following privileges:
grant select any table to user_name
grant select any dictionary to user_name
grant alter session to user_name
grant resumable to user_name
grant become user to user_name

2. Enter SQL request and analyze possible alternatives: Copy and paste your long running Select SQL request into the text field or click Find Hotspots to copy and paste a Select request from Oracle SGA. Then click on Find Alternatives button.

3. Define Benchmark Execution Parameters:
> The Option parameters are used in the benchmark execution when finding the best hint.
> Options enable to set the number of times each alternative will be tested and total benchmark time.
> It is always recommended to set the automatic execution canceling for stopping alternatives running more that X% of the original time.

4. Run benchmark:
> Click on Select all to mark alternatives for execution.
> Click on the Cost column for sorting execution order based on increasing Oracle cost value. Click RUN to start the benchmark which executes the checked alternatives.
Completed alternatives are colored in green. Automatic cancelled alternatives are colored in red. The best time alternative is colored in blue.

5. Compare execution plan of alternatives: Click on Compare for comparing execution plans of different SQL Hint alternatives. Choose the Hint to compare or view the file: expert-[date/time].out for detailed execution statistics.
6. Get best alternative: To get the best alternative SQL text, go to the Best alternative line colored in blue and double click on the Hint column. A pop-up window is presented. Copy the Hint text and paste it to the beginning of your original SQL text. Next steps: > Implement a continuous and automated optimization process for your Oracle SQL statements using our ActiveBase Tuning Robot™ > Have ActiveBase Performance™ apply in real-time the improvements found by ActiveBase SQL Expert™ on any applications without modifying source-code

What to do if the ActiveBase SQL Expert does not present execution alternatives and returns ´Failed to parse/explain statement´?
If no alternatives are presented, perform the following checks:
1. Verify the ´Select´ statement SQL syntax and the parsing schema defined (for example, run the SQL statement in the schema using SQL*Plus).
2. Verify the Session Parameter values.
3. Check database connection and verify the database is up.
4. Existing plan_table table is too big - no more extends are possible, thus creating more plans is not possible.
In this version, ActiveBase supports only Select statement tuning (not insert, update or delete). The parsing process identifies the Select statement from clause objects and the correct location to add the hints inside the statement. In some cases, ActiveBase parser might fail to parse the statement syntax, and thus will not be able to provide alternatives. In this case, please send to support@active-base.com the original SQL statement for further instructions.

What to do if the ActiveBase SQL Expert presents execution alternatives slowly?
In previous Oracle versions that are still using Rule based optimizer (such as Siebel applications) ActiveBase SQL Expert slows down substantially the speed of identifying execution alternatives. This is caused by the optimizer mode and the fact that the schema has not been ever analyzed. In these environments, improve the speed of identifying alternatives by:
1. Analyzing the schema at least once.
2. Lowering system parameter optimizer max permutation to 2,000.

What are the system-requirements to install ActiveBase SQL Expert?
ActiveBase SQL Expert can be installed on any windows client. No Oracle client is required.

What are the Oracle versions supported by the ActiveBase SQL Expert?
Oracle versions range from Oracle8i until 11g on all platforms.

What type of SQL requests can be tuned?
Select SQL requests. Other DML commands will be possible in later versions. For tuning an Insert or Update command you need to manually manipulate the SQL request and cut the Select request into the SQL Expert.

How do you improve the SQL request performance?
ActiveBase SQL Expert analyzes the SQL with different qualitative Oracle Hints that change the execution plan of the original SQL. It benchmarks the unique execution plan alternatives to automatically identify the best Hint. We do not try to rewrite the SQL request as Oracle RDBMS engine provides extensive rewrite optimization techniques as well by doing SQL rewrite, the results returned by the database can be changed unintentionally. By tuning only using Hints guarantee that results are consistent at all times.

What are the resources ActiveBase SQL Expert consumes from the database server?
ActiveBase SQL Expert consumes database resources during two steps:
1. Finding alternatives: it uses a single JDBC connection (one session) to the database to retrieve different execution plans that are the result of applying different Oracle Hints on the SQL statement. This process, get explain plan, can retrieve explain plans for about 3,000 different hints and can take between a few minutes up to several hours to complete. The number of alternatives can be limited by reducing the analyzer level from 3 to 1, or manually stopping the analysis using the stop button.
2. Benchmarking alternatives: it uses a single JDBC connection (one session) to the database to execute serially all alternatives selected for benchmarking. This can consume up to one single CPU during the elapse time of the benchmark run. To limit the benchmark time, Stop benchmark after X hours can be defined in the benchmark options, as well as manually stopping the benchmark by clicking the Stop button. Another option is for parallel query environments: set the benchmark options parameter Parallel execution handling into Wait for PX servers or disable parallel query option.

How does ActiveBase SQL Expert compare alternative benchmark results in Parallel Query degree?
Set the benchmark options parameter Parallel execution handling into Wait for PX servers or set disable parallel query for benchmarking all alternatives serially.

How does ActiveBase SQL Expert benchmark SQL requests with bind values?
Before starting the benchmark run, when all different execution alternatives already are presented, a button Set binds is presented. Click it and enter the bind value. For example, for setting select * from emp where ename=:a into scott, enter in the Set statement parameter screen:a=scott.

How can ActiveBase SQL Expert 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 SQL Expert for Siebel automatically identifies the best Oracle ‘hint’ to dramatically speed response time and reduce resource waste.

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.

Overview

Features

FAQ