UTIL A / 8 (List Analyzed Tables/Indexes); "Oracle Optimizer Changes for ALEPH"

• Article Type: General
• Product: Aleph
• Product Version: 18.01

Description:
We just went to ALEPH 18 / Oracle 10. When we run UTIL A / 8 ("List Analyzed Tables / Indexes"), it says:

"This list should be empty. If not, please use UTIL A / 9 to delete statistics"

But it is our understanding that Oracle 10 (and, presumably, ALEPH 18) *require* that the Oracle Analyzed Tables / Statistics / Cost-Based Optimizer be used.

Which is correct? Should we run UTIL A / 9 to delete the statistics?

Resolution:
ALEPH version 17.01 rep_ver 009117 says: "ALEPH was written and is tuned to work with the Rule Based optimizer. Since in 9i when a customer gathers statistics, ALEPH does NOT WORK, and since Oracle 10g will no longer support the Rule Based optimizer, Ex Libris should adapt ALEPH to work with the Cost Based Optimizer, using hints where necessary."

Though it was our intention to add Oracle "hints" to all of the queries in io_znn.pc files in version 17 (and 18), there are certain cases where hints have not been added.

Though we have found that the lack of these hints is generally not a problem, there are some specific cases where it is. Three such cases are described in SKB's 8192-2730, 8192-2731, and 8192-3802.

The following fixes have been created to remove Oracle Statisics:

rep_change 916 (V18) [in the April 2007 Service Pack]
rep_ver 13913 (V19)

(Prior to these changes, you could run UTIL A / 9, but subsequent activities would cause the Statistics to be recreated.)

The rep_change 916 Description says: "In some cases Oracle Statistics slowed the system. Removing the Statistics improved the performance. Now when an Index/Table is created/changed the Oracle Statistics are automatically removed. It is recommended to delete all existing Oracle Statistics."

You will get the new $aleph_proc/p_delete_stat proc and the updated $aleph_proc/util_a_17_b and util_a_17_i for rep_change 916 as part of the April, 2007, Service Pack. This will assure that subsequent tables/indexes are built without the Statistics.

The attached document, "Oracle Optimizer Changes for ALEPH", describes how to delete the Statistics on existing tables. We suggest that all version 18 sites perform this operation. You may want to wait until rep_change 916 is in place.

Furthermore, you should use the following command (in Oracle 9i and Oracle 10) to disable the automatic gathering of Statistics:

SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB')

You can use the following command to check on the state of the statistics-gathering:

sql> SELECT STATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = ’GATHER_STATS_JOB’;

Note: This is changed in Oracle 11. The command to disable would be:

SQL> DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);