Performance Implications of database statistics computed on GTT* tables
Originally Published: 2015-03-12
Last Modified: 2023-09-25
Article Number
Applies To
Issue
Database Statistics are used by the Oracle optimizer to produce the best query search plan when a SQL statement involves more that 1 table.
Within the Aveksa schema there are a number of tables which have the prefix of GTT, for example GTT_ACCOUNTS and GTT_APPLICATIONS. Aveksa does not generate statistics on the GTT* tables as the table content is highly transient. The GTT* table are used by collections, unification, rules processing, review generation, etc.
If statistics are computed for the GTT* tables, significant performance degradation may be encountered. For statistics to be computed on GTT* tables this would have to have been done manually outside the normal Aveksa operations.
Resolution
select table_name, last_analyzed from user_tables where table_name like 'GTT%' and last_analyzed is not null;
the above SQL should return 0 rows of results.
If the SQL returns results, then the statistics should be removed from said GTT table. This can be accomplished by running
select 'execute DBMS_STATS.delete_table_stats(''AVUSER''' || ',' || '''' || table_name || '''' || ',no_invalidate=>false);' from user_tables where table_name like 'GTT%' and last_analyzed is not null;
This will produce 1 line of output for each GTT* table which currently has statistics computed. The output will be similar to
execute DBMS_STATS.delete_table_stats('AVUSER','GTT_APPLICATIONS',no_invalidate=>false);
execute DBMS_STATS.delete_table_stats('AVUSER','GTT_ACCOUNTS',no_invalidate=>false);
If one then takes this output and reruns it will remove database statistics for each of the GTT* tables.
Further one can also lock the table from ever having database statistics computed by the same process above in that by running
select 'execute DBMS_STATS.lock_table_stats(''AVUSER''' || ',' || '''' || table_name || ''');' from user_tables where table_name like 'GTT%';
This will produce 1 line of output for each GTT* table which is to be locked from having database statistics computed. The output will be similar to
execute DBMS_STATS.lock_table_stats('AVUSER','GTT_APPLICATIONS');
execute DBMS_STATS.lock_table_stats('AVUSER','GTT_ACCOUNTS');
If one then takes this output and reruns it will lock all GTT* tables from having database statistics computed.
If one then tries to compute database statistics on a locked table the following error will be encountered
SQL> execute DBMS_STATS.gather_table_stats('AVUSER','GTT_APPLICATIONS');
BEGIN DBMS_STATS.gather_table_stats('AVUSER','GTT_APPLICATIONS'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
Commencing with version 6.9.1 all GTT* tables will already be locked from having database statistics computed.
Related Articles
How to use SAW purge data script 11Number of Views RSA Announces the August Release of RSA SecurID Access 7Number of Views RSA Governance & Lifecycle Recipes: Report - AD Group Summary 15Number of Views RSA Governance & Lifecycle Recipes: Dormant Account Process 52Number of Views Service Brief Quick Start Implementation for RSA Governance & Lifecycle 27Number of Views
Trending Articles
RSA Authentication Manager 8.9 Patches and Hotfixes Readme RSA MFA Agent 2.5 for Microsoft Windows Installation and Administration Guide RSA-2026-24: RSA Authentication Manager Security Update for Third-Party Component Vulnerabilities IDR SSO - Step 3: Deploy the Identity Router RSA Authentication Manager 8.9 Setup and Configuration Guide
Don't see what you're looking for?