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