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
RSA Governance & Lifecycle Recipes: Dormant Account Process Number of Views RSA announces the upcoming release of RSA SecurID Access to accelerate and modernize MFA Number of Views ORA-01403 no data found error in Account Data Collectors (ADC) in Step 10/11 in RSA Governance & Lifecycle Number of Views RSA Governance & Lifecycle Varonis Implementation Blueprint Number of Views How to use SAW purge data script Number of Views
Trending Articles
RSA MFA Agent 2.5 for Microsoft Windows Installation and Administration Guide Download RSA SecurID Access Cloud User Event audit logs using Cloud Administration REST API CLU RSA Authentication Manager 8.9 Patches and Hotfixes Readme How to set up the REST RSA SecurID Authentication API for Authentication Manager 8.2 SP1 How to download full kits, service packs, and patches from RSA Link for RSA Identity Governance & Lifecycle
Don't see what you're looking for?
