对于统计信息的收集，不同的环境中使用的策略也会有很大的不同，有的按照一定的时间频率来收集，有的比较稳定的系统根据数据的增长频率来收集，用户比较稳定的系统，甚至都不再收集统计信息。
以下是使用shell生成的统计信息报表效果，可以在备份库中进行这些信息的收集,可以看到哪些表的查询耗费的时间较多，当前数据条数和统计信息中的数据条数。
#############################################################################
table_name volumn_size Elapsed_time acutal_cnt stat_cnt
#############################################################################
TEST_AUDIT_BALANCE SMALL 00:00:00.06 0 0
TEST_FILE_SYSTEM_DEF SMALL 00:00:00.00 114 114
TEST_SITEID SMALL 00:00:00.00 112 112
TEST_MISSING_SEQ SMALL 00:00:00.00 0 0
TEST_GL_DETAILED_DATA HUGE 00:00:18.24 315001510 310553395
TEST_INVOICE SMALL 00:00:02.97 3024771 3024729
TEST_TAX_ITEM MEDIUM 00:00:23.45 25826165 25825805
TEST_TRANSACTION_LOG MEDIUM 00:00:13.71 15642936 15407469
使用的shell脚本如下，简单对脚本的实现做了解释。
##############################################
首先使用sqlplus来生成数据的当前数据条数，这个地方可以使用动态sql来生成，或者使用指定的sql语句，我就不重复贴了。
需要使用两个参数，一个是连接的用户名/密码，一个是指定的脚本来执行得到数据的条数。
print "
WHENEVER SQLERROR EXIT 5
set pages 0
set timing on
set feedback off
connect $1 \n
@@$2
" | sqlplus -s /nolog > tab_stat.log
输出的内容格式如下所示：
TEST_AUDIT_BALANCE SMALL 0
Elapsed: 00:00:00.09
TEST_FILE_SYSTEM_DEF SMALL 114
Elapsed: 00:00:00.09
TEST_SITEID SMALL 112
Elapsed: 00:00:00.09
TEST_MISSING_SEQ SMALL 0
Elapsed: 00:00:00.09
TEST_GL_DETAILED_DATA HUGE 315001510
Elapsed: 00:00:18.24
TEST_INVOICE SMALL 3024771
Elapsed: 00:00:02.97
TEST_TAX_ITEM MEDIUM 25826165
Elapsed: 00:00:23.45
TEST_TRANSACTION_LOG MEDIUM 15642936
Elapsed: 00:00:13.71
下面进行数据拆分，得到两个文件,一个文件得到所有表的数据条数，另外一个文件得到执行的时间
#print 2 files with table data counts and the other for elapsed time
awk 'NR%2==1' tab_stat.log |cat -n >tab_cnt_lst.log
awk 'NR%2==0' tab_stat.log |awk '{print $2}' |cat -n >tab_stat_time.log
然后把两个文件内容merge起来
#print file with elapsed time as one column
join tab_cnt_lst.log tab_stat_time.log |awk '{print $2,$3,$5,$4}' |sort > tab_stat_act_cnt.log
显示的内容如下：
TEST_AUDIT_BALANCE SMALL 00:00:00.06 0
TEST_FILE_SYSTEM_DEF SMALL 00:00:00.00 114
TEST_SITEID SMALL 00:00:00.00 112
TEST_MISSING_SEQ SMALL 00:00:00.00 0
TEST_GL_DETAILED_DATA HUGE 00:00:18.24 315001510
TEST_INVOICE SMALL 00:00:02.97 3024771
TEST_TAX_ITEM MEDIUM 00:00:23.45 25826165
TEST_TRANSACTION_LOG MEDIUM 00:00:13.71 15642936
然后从当前的统计信息中得到数据的条数。
print "
WHENEVER SQLERROR EXIT 5
set pages 0
set feedback off
connect $1 \n
SELECT
TABLE_NAME,NUM_ROWS
FROM
USER_TABLES
ORDER BY 1;
" | sqlplus -s /nolog |sort > tab_stat_cnt.log
输出格式类似下面所示。
TEST_AUDIT_BALANCE 0
TEST_FILE_SYSTEM_DEF 114
TEST_SITEID 112
TEST_MISSING_SEQ 0
TEST_GL_DETAILED_DATA 315001510
TEST_INVOICE 3024771
TEST_TAX_ITEM 25826165
TEST_TRANSACTION_LOG 15642936
最后把文件的内容merge到一起，输出报表。
#print report with table actual data counts and stat data counts
join tab_stat_act_cnt.log tab_stat_cnt.log|awk '
BEGIN{
print "#############################################################################"
printf "%-30s %-10s %11s %-15s %-15s \n", "table_name","volumn_size","Elapsed_time","acutal_cnt","stat_cnt"
print "#############################################################################"
}
{printf "%-30s %-10s %11s %-15s %-15s \n",$1,$2,$3,$4,$5}' |tee final_tab_stat_rpt.lst
得到了最终的报表内容，可以在此基础上进行分析和统计，如果统计值和实际的数据条数相差比较大，就可以针对性的进行统计信息收集。
#############################################################################
table_name volumn_size Elapsed_time acutal_cnt stat_cnt
#############################################################################
TEST_AUDIT_BALANCE SMALL 00:00:00.06 0 0
TEST_FILE_SYSTEM_DEF SMALL 00:00:00.00 114 114
TEST_SITEID SMALL 00:00:00.00 112 112
TEST_MISSING_SEQ SMALL 00:00:00.00 0 0
TEST_GL_DETAILED_DATA HUGE 00:00:18.24 315001510 310553395
TEST_INVOICE SMALL 00:00:02.97 3024771 3024729
TEST_TAX_ITEM MEDIUM 00:00:23.45 25826165 25825805
TEST_TRANSACTION_LOG MEDIUM 00:00:13.71 15642936 15407469