Viewing the report you will be able to determine the columns that are being used in WHERE clauses. This includes Joins, Equity operators, Like predicate operations. This information can be invaluable in following three situations.[title type=”h4″ class=”tfuse”]1. Method_Opt =>’FOR ALL COLUMS SIZE AUTO’[/title]
If you are using the above method as input to your statistic collection job then the DBMS_STATS package will use the report_col_usage procedure to see on which columns statistics are required.[title type=”h4″ class=”tfuse”]2. Method_Opt =>’FOR ALL INDEXED COLUMNS SIZE AUTO’[/title]
If you are using the above as input to your statistic collection job then statistics will be collected only on indexed columns. You should be careful while setting this. The other way around to this problem will be to use the report_col_usage procedure and see which columns are being used heavily in WHERE clauses. Create indexes on all those columns.[title type=”h4″ class=”tfuse”]3. Drop Unused Indexes[/title]
There may be indexed columns which are not being used in WHERE clauses anymore due to some application design change. The procedure will report this fact and after careful analyzing this you can get rid of those unused indexes.
You can also view the column usage information of a specific table using the following command.
SELECT DBMS_STATS.REPORT_COL_USAGE('SCOTT','EMP') FROM DUAL;
Additionally there are other procedures that can help you manage the column usage. For example, reset_col_usage will reset all the values and thus the package assumes that a particular column has never been used. You have to be careful while using this because this can inversely affect your daily statistic collection job.
You can feed some column usage information if you want, for certain columns, to be available in the result of report_col_usage procedure and thus in statistic collection job. The seed_col_usage procedure will take a set of SQL statements and will seed the column usage information of all column appearing in those SQLs.
You can also import the column usage information from other databases as well. This can be really helpful if you are building a replica of your production server and want to simulate the same statistics in the new server as they were in old server. The merge_col_usage will help you do that. It will take a database link as a parameter which should be a link to your source database and will import column usage information. If the column usage information is already present for certain columns then the procedure will merge that information with the information being brought in from source database.