Performance Implications of database statistics computed on GTT* tables
Originally Published: 2015-03-12
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 remove unnecessary tables from database statistics analysis in RSA Identity Governance & Lifecycle Number of Views Explanation of GTT_ tables used in RSA Identity Governance & Lifecycle Number of Views RSA Governance & Lifecycle 7.5.2 Release Notes Number of Views Account summary table export includes the HTML tags that construct the account mapping button in RSA Identity Governance &… Number of Views Indirect Relationship Processing fails with 'ORA-12899 value too large for column' error in RSA Identity Governance & Life… Number of Views
Trending Articles
Downloading RSA Authentication Manager license files or RSA Software token seed records RSA MFA Agent 2.3.6 for Microsoft Windows Installation and Administration Guide Quick Setup Guide - Passwordless Authentication in Windows MFA Agent for Active Directory Mandatory Certificate Upgrade Required by 6th October 2025 for RSA MFA Agent for PAM, RSA MFA Agent for Apache, and Third … RSA Authentication Manager 8.9 Release Notes (January 2026)
Don't see what you're looking for?
