Tips for optimizing Oracle Database dictionary tables

Dictionary tables are commonly used. You can set these tables to use the keep pool. First, analyze the dictionary table in the solution to calculate the space. For simplicity, analyze all the tables and call

Dictionary tables are commonly used. You can set these tables to use the keep pool. First, analyze the dictionary table in the solution to calculate the space. For simplicity, analyze all the tables and call

I,
Dictionary tables are commonly used. You can set these tables to use the keep pool.
First, analyze the dictionary table in the solution for computing space. For simplicity, analyze all the tables and call the stored procedure:
Dbms_stats.gather_schema_stats (ownname => 'schema name ')
Calculate the required space:
Select sum (blocks) * 8 from user_tables where name like 'dict \ _ % 'escape '\';
Note: 8 is the size of the data block, 8 k, and 4 k. Assume that the dictionary table starts with DICT.
The size of the required space, in k. Set the size of the keep pool based on the statistical results. The default keep pool is 0 and is not enabled.
Alter system set db_keep_cache_size = 80 m;

II,
Generally, the dictionary tables in the system are relatively stable and are not often modified. Therefore, you can set pctfree to 0, which can save space and increase the access speed.

Based on the above two optimization ideas, I wrote a simple stored procedure and tested it on the test database only. If the data volume is large and the storage space is insufficient, please use it with caution.
Create or replace procedure KeepDictTab is
/*************************************** ***************************************
On, The pctfree of small tables was set to 0 and the keep pool was used to improve efficiency.
**************************************** **************************************/
Begin
For rec_tab in (select table_name from user_tables where table_name like 'dict \ _ % 'escape '\') loop
Execute immediate 'alter table' | rec_tab.table_name | 'pctfree 0 storage (buffer_pool keep )';
Execute immediate 'alter table' | rec_tab.table_name | 'move tablespace tbs2 ';
-- Change the tablespace and use the new storage feature to save the data. After the switchover, re-create the index. Otherwise, the data cannot be moved back.
For rec_idx in (select index_name from user_indexes where table_name = rec_tab.table_name) loop
Execute immediate 'alter Index' | rec_idx.index_name | 'rebuilt ';
End loop;
-- Switch back to the original tablespace and recreate the index.
Execute immediate 'alter table' | rec_tab.table_name | 'move tablespace tbs1 ';
For rec_idx in (select index_name from user_indexes where table_name = rec_tab.table_name) loop
Execute immediate 'alter Index' | rec_idx.index_name | 'rebuilt ';
End loop;
-- Analysis table
Execute immediate 'analyze table' | rec_tab.table_name | 'estimate statistics ';
End loop;

For rec_idx in (select index_name from user_indexes where table_name like 'dict \ _ % 'escape '\') loop
-- Analyze related indexes
Execute immediate 'analyze Index' | rec_idx.index_name | 'estimate statistics ';
End loop;
End KeepDictTab;
/

After setting, you can use the execution plan to check whether the number of data blocks read is reduced, the COST is reduced, and the speed is a little faster.