Optimizing Visualizer performance on Oracle databases
Chapter 4 Configuring, managing, and customizing Perceive 149
Optimizing Visualizer performance on Oracle databases
The analyzes.sql script included with Perceive takes advantage of cost-based
(statistical) optimization in Oracle. The script computes statistics on all Visualizer
tables by running the
SQL ANALYZE command. Because statistics are not gathered
automatically, BMC Software has found that running this optimization script
significantly improves the performance of all queries run by Perceive on Visualizer.
Once statistics are stored in the data directory, they might age and become unreliable.
Although the overall statistics of tables that are subject to significant updates do not
change drastically, you should re-compute these statistics on a regular basis.
However, depending on the size of the tables, computing statistics may take a long
time (up to several hours on large databases).
Because of this constraint, BMC Software suggests that you follow these tips when
running the optimization tool:
■ Do not use cost-based optimization on Oracle versions 7.3 or earlier.
■ Do not run the optimization script more than once a month, unless you have
added or deleted significant amounts of data during that period.
To run the optimization tool
1 Locate the analyzes.sql script in the Perceive product directory. For example, on
UNIX it might be:
$TOMCAT_HOME/webapps/qtv/scripts
2 Open the analyzes.sql script in a SQLPLUS -- the Oracle facility -- session.
3 In this session, type:
sqlplus <vis-db-username/vis-db-password>
4 Press Enter.
5 Type @analyzes.
