Why are Oracle statistics in Aleph locked?
- Product: Aleph
- Product Version: 18,19,20,21,22,23
- Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care
Question
Why are Oracle statistics in Aleph locked?
I've found that all our tables has locked statistics
select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null
and owner like 'ABC%';
returns all tables.
Is it the normal configuration or something went wrong ?
Answer
Yes, indeed, this is normal.
Aleph is working with the RULE base optimizer, hence optimizer_mode is set to CHOOSE, and statistics are deleted and locked.
See also the article UTIL A / 8 (List Analyzed Tables/Indexes); "Oracle Optimizer Changes for ALEPH" .
Additional Information
Please see additional information from rep_ver #9117 of Aleph version 18....
Description: ALEPH was written and is tuned to work with the Rule Based optimizer.
Since in 9i when a customer gather 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.
*******
Please use util a/8. (List Analyzed Tables / Indexes) to identify analyzed tables
=================================================
This list should be empty
if not please use util a 9 to delete statistics
List of tables and indexes analyzed
=================================================
If the list is not empty, please delete them by using util a/9.
- Article last edited: 14-April-2016