#!/usr/bin/env python # -*- encoding: utf8 -*- # Author: tracylling@gmail.cn # Created: 2011-8-16 import cx_Oracle import os import sys import re os.environ['NLS_LANG'] ='AMERICAN_AMERICA.ZHS16GBK' #input format : python oracle9i_liling.py -t table_name -o owner >> file_path/file_name.txt OWNER = sys.argv[4] TABLE_NAME = sys.argv[2] TABLE_NAME1 = TABLE_NAME.upper() OWNER1 = OWNER.upper() print("------------Welcome To Oracle 9i Statistics Tools----------------") print("") #--login--# def db_connect(): try: conn = cx_Oracle.connect('tp/tp@ocn_test') return conn except cx_Oracle.DatabaseError,exc: error,=exc.args print "db_connect error" print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message #--table information--# def table_info(cursor): try: cursor.execute(""" select owner,segment_name,bytes / 1024 / 1024 From dba_segments where segment_name = :table_name and segment_type = 'TABLE' and owner = :owner """,table_name = TABLE_NAME1,owner = OWNER1) for column_1,column_2,column_3 in cursor.fetchall(): print "*********************Table Info*********************" print "Version Table Name Owner Name Size(MB)" print "-"*52 print "%s %12s %16s %12d" %(conn.version,TABLE_NAME1,OWNER1,column_3) except cx_Oracle.DatabaseError, exc: error, = exc.args print "table_info error" print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message #--index infomation--# def index_info(cursor): try: print "" print "***********Index Info***********" print "INDEX_NAME SIZE(MB)" print "-"*30 cursor.execute(""" select owner,segment_name, bytes / 1024 /1024 From dba_segments where segment_name IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME = :table_name) and segment_type = 'INDEX' and owner = :owner """,table_name = TABLE_NAME1,owner = OWNER1) for column_1,column_2,column_3 in cursor.fetchall(): print "%-25s %d" % (column_2,column_3) except cx_Oracle.DatabaseError, exc: error, = exc.args print "index_info error" print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message #--SQL--# def sql_join(cursor): dict = {} #存放SQL连接并归一化后结果 dict_table = {} #存放全词匹配表名的SQL语句 count = {} #存放归一化语句条数,做除数 match_join='(\s*:[A-Za-z0-9]+\s*,?\s*)+' match_table = '[^A-Za-z0-9_]' + TABLE_NAME + '[^A-Za-z0-9_]' rx = re.compile(match_table,re.IGNORECASE) print "" print "***********SQL EXECUTIONS Info***********" try: #print "start" cursor.execute(""" select distinct hash_value From stats$sqltext """) hash_val = cursor.fetchall() for i in hash_val: cursor.execute(""" select sql_text from stats$sqltext where hash_value = :hash_value order by piece """,i) sql_piece = cursor.fetchall() sql_text = '' for j in sql_piece : sql_text = sql_text + "%s" %(j) sql_text = sql_text.replace('\n\r',' ') sql_text = " ".join(sql_text.split()) #SQL语句拼接并去除多余的空格 if rx.search(sql_text) is not None: #匹配表名,存储进映射KV关系 k是hash_val,v是对应完整的SQLTEXT dict[i] = sql_text.upper() for key in dict.keys(): dict[key] = re.sub(match_join,' :1 ',dict[key]) #归一化 #print dict[key] cursor.execute("""SELECT hash_value,case when max_exec > min_exec and min_exec > 0 then trunc((max_exec - min_exec) / decode(trunc(max_time)-trunc(min_time),0,1,trunc(max_time)-trunc(min_time))) when max_exec = min_exec and min_exec > 0 then max_exec else 0 end as exec_daily, trunc(max_time)-trunc(min_time) diff_days, to_char(min_time, 'yyyy-mm-dd') as begin_snap, to_char(max_time, 'yyyy-mm-dd') as end_snap fROM (select A.*, row_number() OVER(partition by hash_value order by max_time desc) rn from (select s.hash_value, min(t.snap_time) over(partition by hash_value order by s.snap_id) as min_time, max(t.snap_time) over(partition by hash_value order by s.snap_id) as max_time, min(s.executions) over(partition by hash_value order by s.snap_id) as min_exec, max(s.executions) over(partition by hash_value order by s.snap_id) as max_exec from stats$sql_summary s, stats$snapshot t where s.snap_id = t.snap_id and s.Hash_value = :arg) a)B WHERE B.RN =1 """,key) executions = cursor.fetchall() #print executions for line in executions: sql_hash_val = line[0] exec_daily = line[1] snap_time = line[4] if exec_daily is not None: if dict_table.has_key(dict[key]): dict_table[dict[key]] = dict_table[dict[key]] + exec_daily count[dict[key]] = count[dict[key]] + 1 else : dict_table[dict[key]] = exec_daily count[dict[key]] = 1 #else: #exec_daily = 0 print "" for keys in dict_table: dict_table[keys] = dict_table[keys]/count[keys] #print count[keys] #print dict_table[keys] for i in sorted(dict_table.items(),key=lambda e:e[1],reverse=True): print "** SQL EXECUTIONS: %d **" %i[1] print "%s" %i[0] print "" except cx_Oracle.DatabaseError, exc: error, = exc.args print "sql_join error" print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message #--main--# def main(cursor): db_table_info = table_info(cursor) db_index_info = index_info(cursor) db_sql_join = sql_join(cursor) if __name__ == '__main__': conn = db_connect() cursor = conn.cursor() main(cursor)
呃,有点弱了……都木有注意到诶
好吧,这个主题的评论,竟然没有“回复”的按钮啊。。
呃,自己记录一下嘛,还么考虑过搜索引擎排名的,以后你教教我好啦~
纯粹是代码,一点文字说明都没有。。。这样搜索引擎排名上不去的哦。。