Performance Implications of database statistics computed on GTT* tables
Originally Published: 2015-03-12
Last Modified: 2023-09-25
Article Number
000067464
Applies To
RSA Product Set: Identity Management and Governance

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
To check if your GTT* tables have database statistics computed run the following SQL

​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

Trending Articles

Don't see what you're looking for?