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
Explanation of GTT_ tables used in RSA Identity Governance & Lifecycle Number of Views Every application restart loses the customerstrings.properties file in versions 7.0.2 and 7.1.0 of RSA Identity Governance… Number of Views RSA Announces RSA Authentication Manager 8.4 Patch 4 with New Features Making It Easier Than Ever to Adopt Modern MFA Auth… Number of Views How to remove unnecessary tables from database statistics analysis in RSA Identity Governance & Lifecycle Number of Views Review escalation workflows do not trigger if review is set to Hold by an escalation workflow after due date in RSA Identi… Number of Views
Trending Articles
RSA Authentication Manager 8.7 SP2 Setup and Configuration Guide RSA MFA Agent 2.5 for Microsoft Windows Installation and Administration Guide RSA SecurID software token .sdtid file fails to import into RSA SecurID Software Token 5.0 for Windows Customizing TLS Protocol Version How to manipulate imported RSA SecurID Software Token(s) on an iPhone or iPad device
Don't see what you're looking for?
