通过GET_SQL包抓SQL方法

2019独角兽企业重金招聘Python工程师标准>>>

1 运行机制#

适用于在生产环境抓取sql的情况，由于一个库可能存在多个人同时对不同的表抓取相关sql做影响分析或者性能对比。该存储过程从配置表读取需要抓取sql语句表的相关信息以及抓取次数，一个库上部署一个job。

1.1 表结构说明#

DBMGR用户下创建了三个表：my_sqltext，get_sql_configuate和，get_top_sql_log和一个临时表sql_temp（on commit preserve rows）

• 1）表：my_sqltext：结果表，存放获取的sql结果

my_sqltext 结果表

Column Name	Data Type	NOT Null?	Comment
Hash_value	Number	N	Hash_value
Child_number	Number	N	Child number
plan_hash_value	Number	N	plan_hash_value
parsing_schema_id	Number	N	parsing_schema_id
executions	Number	N	执行次数
cpu_per_exec	Number	N	每次消耗的cputime
gets_per_exec	Number	N	每次执行返回的记录数
sqllen	Number	N	Sql长度
sql_text	VARCHAR2（4000）	N	小于4000的sql语句，存放CLOB时表示此sql语句长度超过4000
sql_full_text	CLOB	N	长度超过4000的sql语句
outline_category	VARCHAR2（100）	N	Outline策略
Create_date	date	N	抓取日期

• 2. get_sql_configuate：配置表：记录需要抓取sql语句的表

get_sql_configuate 配置表

Column Name	Data Type	NOT Null?	Comment
Owner	VARCHAR2（30）	N	表的属主
table_name	VARCHAR2（30）	Y	表名，若为FULLDB表示全库都抓取
Cnt	Number	Default 1	抓取次数，默认为1

• 3. get_top_sql_log：日志表：记录抓取失败的错误信息

get_top_sql_log 配置表

Column Name	Data Type	NOT Null?	Comment
id_get_top_sql_log	Number	Y	主键使用seq_get_top_sql_log
Hash_value	Number	N	Hash_value
Error_date	Date	N	错误日期
Result_code	Number	N	错误信息号
Result_message	Varchar2(1000)	N	错误信息

• 4. sql_temp（on commit preserve rows）：过程处理中所需的临时表，主要存在结果的相关信息。

sql_temp 临时表

Column Name	Data Type	NOT Null?	Comment
Hash_value	Number	N	Hash_value
Child_number	Number	N	Child number
executions	Number	N	执行次数
cpu_per_exec	Number	N	每次消耗的cputime
gets_per_exec	Number	N	每次执行返回的记录数
sqllen	Number	N	Sql长度
sql_text	VARCHAR2（1000）	N	小于4000的sql语句，存放CLOB时表示此sql语句长度超过4000
parsing_schema_id	Number	N	parsing_schema_id
plan_hash_value	Number	N	plan_hash_value

1.2 存储过程说明：#

所有的存储过程都存放在package：dbmgr.admin_sql中，包含下面过程：

• 1. get_sqltext：函数方法，通过hash_value返回sql（CLOB），参数显示包含hash_value。
• 2. get_topsql ：程序主体，最终提供给job调用。这个存储过程没有输出参数，只有输入参数，错误信息会记录到日志表中，会根据配置表的信息抓取sql，抓一次，表中的次数会减少一次，直到为0时就停止抓取，若设置为fulldb，则会抓取全库的信息，其正在运行的表，也不会再继续抓取。

2 部署步骤#

• 1. 以sys 用户登录，按文件名顺序执行附件rar包中的脚本。分别是创建表，做必要授权，创建存储过程以及同义词。

• 2. 根据需要在配置表中配置相关信息：
  • 1. 首先配置表名，若全库抓取，则配置为FULLDB，其余配置正确的表名
  • 2. 次数配置：若job抓取时间间隔是1小时一次，抓取时间为连续2天，则次数为48
  • 3. 运行job
  • 4. 注意事项：
    • 1） 配置之前先使用下面语句检查，看此库时候有此job在运行：

Select count（*） from dba_jobs where upper(what) like ‘%GET_SQL%’

若结果为0，则需要按照上述说明部署相应的脚本
若结果不等于0 ，则检查配置表（get_sql_configuate）是否有表的记录，检查语句如下：

Select  count（*）  from get_sql_configuate where table_name like‘%XXXX%’

若有只需修改测试即可，若没有，继续下面语句查询

Select count（*）from get_sql_configuate where table_nam=‘FULLDB’ and cnt>0

若有则，看次数是否满足条件，若满足，直接再结果表中获取，若不满足，则根据条件计算次数，再修改次数
若没有就需要在配置表中增加相应的信息，即可。

• • • 2） 每个库只需配置一次，配置间隔一般设置为30分组抓取一次。
• 3. 附件脚本（get_sql.rar为部署脚本,get-clob.sql为将结果中的clob的结果输出到文件）

转载于:https://my.oschina.net/u/729507/blog/88624