#!/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)