通过shell脚本生成数据统计信息的报表 (笔记65天)

对于统计信息的收集,不同的环境中使用的策略也会有很大的不同,有的按照一定的时间频率来收集,有的比较稳定的系统根据数据的增长频率来收集,用户比较稳定的系统,甚至都不再收集统计信息。

以下是使用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  

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-05-07

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏张高兴的博客

张高兴的 Windows 10 IoT 开发笔记:使用 Lightning 中的软件 PWM 驱动 RGB LED

3226
来自专栏杨建荣的学习笔记

探究AWR 第一篇

statspack相比awr算是比较通用,而且免费,可以在标准版,企业版中使用,awr是新企业版本中才有的,算是statspack的一个升级版,而且代码不公开。...

3377
来自专栏杨建荣的学习笔记

GoldenGate数据迁移的问题总结(二)(r10笔记第85天)

昨天使用GoldenGate同步数据,数据量玩得有些大了。最后发现很多小问题变得更加严峻,比如空间问题。 而且由于没有更多的经验,导致这个问题被我引入了另外一个...

3029
来自专栏杨建荣的学习笔记

巧用shell生成数据库检查脚本 (74天)

在生产环境中需要部署大量的数据变更。对于新增的表,需要注意权限和同义词等。但是手动去检查这些变更是否生效就很麻烦。而且也不易维护,比如写好了一个脚本,可能在过一...

2887
来自专栏Netkiller

CentOS 7 + MongoDB 3.4

节选自《Netkiller NoSQL 手札》 2.1.4. 从官网安装最新版本的 MongoDB 3.4 官网的rpm包是如下 [root@netkiller...

3524
来自专栏杨建荣的学习笔记

oracle工具集初探(r4笔记第8天)

今天无意中看了下ORACLE_HOME/bin下面的东西,发现里面还是存在不少的东西。除了常用的sqlplus,tnsping,rman,exp/expdp,i...

2504
来自专栏计算机视觉战队

Deeplab v2 安装及调试全过程

上期为大家带来的是从FCN到DeepLab V2的一些相关知识,今天我们就来和大家分享一些DeepLab V2的安装及调试全过程,希望可以为一些需要的科研小伙伴...

3994
来自专栏林德熙的博客

git 修改commit日期为之前的日期

我在之前修改了一个文件,但是没有commit,现在我想要commit,日期为那天的日期 git 修改日期的方法很简单,因为有一个命令--date 可以设置 gi...

501
来自专栏杨建荣的学习笔记

数据库文件的迁移

关于数据库中的文件迁移,需要考虑普通数据文件,redo日志文件(还需要考虑是否为current状态),undo表空间,临时表空间,system,sysaux表空...

3544
来自专栏Laoqi's Linux运维专列

MySQL高可用架构之MHA

1513

扫描关注云+社区