精华内容
下载资源
问答
  • 直接使用: CheckOracle.py -H [remote_home] -u [oracle_user] -p [oracle_password] -s [oracle_SID] -i [information] ...
    直接使用:
        CheckOracle.py -H [remote_home] -u [oracle_user] -p [oracle_password] -s [oracle_SID] -i [information] [table_name]
       具体使用细节和例子,使用-h获取帮助         
    其他python调用:
        1 可以在实例化对象时指定参数
        2 调用的函数为OraInfo()
        3 返回结果集




    点击(此处)折叠或打开

    1. #!/usr/bin/env python3
    2. #_*_coding:utf-8_*_
    3. #Auth by raysuen
    4. #v1

    5. import cx_Oracle
    6. import sys,re,datetime


    7. class OracleConn(object): #封装类
    8.     #存放连接信息的
    9.     Info = {
    10.         "host":"127.0.0.1", #远端oracle的host
    11.         "port":1521, #远端oracle的port
    12.         "OraSID":None, #远端oracle的SID
    13.         "OraUser":None, #远端oracle的username
    14.         "OraPasswd":None, #远端oracle的username对应的密码
    15.         # "OraSchemas":None,
    16.         "OraInfo":None, #想要获取远端数据库的那种信息
    17.         "OraTable":None #有关系的oracle的表名
    18.             }

    19.     def __init__(self,host=None,port=None,sid=None,orauser=None,orapwd=None,orainfo=None,oratable=None):
    20.         if host != None:
    21.             self.Info["host"] = host
    22.         if sid != None:
    23.             self.Info["OraSID"] = sid
    24.         if port != None:
    25.             self.Info["port"] = port
    26.         if orauser != None:
    27.             self.Info["OraUser"] = orauser
    28.         if orapwd != None:
    29.             self.Info["OraPasswd"] = orapwd
    30.         if orainfo != None:
    31.             self.Info["OraInfo"] = orainfo
    32.         if oratable != None:
    33.             self.Info["OraTable"] = oratable


    34.     def Check_Info(self): #判断Info字典里面的key是否存在必要的值
    35.         if self.Info["OraUser"] == None:
    36.             print("You must specify a oracle username for connecting oracle.")
    37.             print("If you don't know how to specify the parameters.You can -h to get help")
    38.             exit(3)
    39.         if self.Info["OraPasswd"] == None:
    40.             print("You must specify a oracle password for connecting oracle.")
    41.             print("If you don't know how to specify the parameters.You can -h to get help")
    42.             exit(3)
    43.         if self.Info["OraSID"] == None:
    44.             print("You must specify a oracle SID for connecting oracle.")
    45.             print("If you don't know how to specify the parameters.You can -h to get help")
    46.             exit(3)
    47.         if self.Info["OraInfo"] == None:
    48.             print("You must specify a Information about oracle")
    49.             print("If you don't know how to specify the parameters.You can -h to get help")
    50.             exit(3)

    51.     def ConnectOracle(self): #封装连接数据库的连接,并返回连接对象
    52.         try:
    53.             tnsname = cx_Oracle.makedsn(self.Info["host"], self.Info["port"], self.Info["OraSID"])
    54.             ora = cx_Oracle.connect(self.Info["OraUser"], self.Info["OraPasswd"], tnsname)
    55.         except Exception as e:
    56.             print(e)
    57.             exit(4)
    58.         return ora

    59.     def CloseOracle(self,oraCon): #封装管理数据库连接的函数
    60.         oraCon.close()

    61.     def ExecSql(self,SqlStr): #封装数据执行sql的函数
    62.         try:
    63.             ora = self.ConnectOracle()
    64.             cursor = ora.cursor()
    65.             cursor.execute(SqlStr)
    66.             res = cursor.fetchall()
    67.             cursor.close
    68.             self.CloseOracle(ora)
    69.         except Exception as e:
    70.             print(e)
    71.             exit(5)
    72.         return res


    73.     def GetTableSpace(self): #获取tablespace信息的函数
    74.         sqlStr="""
    75.               select a.tablespace_name,
    76.                      round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
    77.                      round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
    78.                      round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
    79.                      round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||'%' Pct_Free,
    80.                      100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||'%' Pct_used,
    81.                      round(maxbytes/1048576,2) Max,
    82.                      round(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) / round((case maxbytes when 0 then a.bytes_alloc else maxbytes end)/1048576,2) * 100,2) || '%' "USED_MAX%"
    83.               from ( select f.tablespace_name,
    84.                              sum(f.bytes) bytes_alloc,
    85.                              sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
    86.                       from dba_data_files f
    87.                       group by tablespace_name) a,
    88.                     ( select f.tablespace_name,
    89.                              sum(f.bytes) bytes_free
    90.                       from dba_free_space f
    91.                       group by tablespace_name) b
    92.               where a.tablespace_name = b.tablespace_name (+)
    93.               union all
    94.               select h.tablespace_name,
    95.                      round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
    96.                      round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
    97.                      round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
    98.                      round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||'%' Pct_Free,
    99.                      100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||'%' pct_used,
    100.                      round(sum(f.maxbytes) / 1048576, 2) max,
    101.                      round(round(sum(nvl(p.bytes_used, 0))/ 1048576, 2)/round(sum(case f.maxbytes when 0 then (h.bytes_free + h.bytes_used) else f.maxbytes end) / 1048576, 2) * 100,2)||'%' "USED_MAX%"
    102.               from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
    103.               where p.file_id(+) = h.file_id
    104.               and p.tablespace_name(+) = h.tablespace_name
    105.               and f.file_id = h.file_id
    106.               and f.tablespace_name = h.tablespace_name
    107.               group by h.tablespace_name
    108.               ORDER BY 1
    109.             """

    110.         res = self.ExecSql(sqlStr)
    111.         return res

    112.     def PrintTablespace(self,res):
    113.         headStr = """|%s|%s|%s|%s|%s|%s|%s|%s|"""%("tablespace_name".center(30),"megs_alloc".center(15),"megs_free".center(15),"megs_used".center(15),"Pct_Free".center(15),"Pct_used".center(15),"Max_MB".center(15),"USED_MAX_PCT".center(15))
    114.         print("%s"%"".center(144,"-"))
    115.         print(headStr)
    116.         print("%s" % "".center(144, "-"))
    117.         for t in res:
    118.             print("|%s|%s|%s|%s|%s|%s|%s|%s|"%(t[0].center(30),str(t[1]).center(15),str(t[2]).center(15),str(t[3]).center(15),t[4].center(15),t[5].center(15),str(t[6]).center(15),t[7].center(15)))
    119.         print("%s" % "".center(144, "-"))

    120.     def GetAsmDiskGroup(self):
    121.         sqlStr="""select name,total_mb,free_mb from v$asm_diskgroup"""
    122.         res = self.ExecSql(sqlStr)
    123.         return res

    124.     def PrintAsmDiskGroup(self,res):
    125.         print("%s" % "".center(50, "-"))
    126.         print("|%s|%s|%s|"%("GROUP_NAME".center(20),"TOTAL_MB".center(15),"FREE_MB".center(15)))
    127.         print("%s" % "".center(50, "-"))
    128.         for t in res:
    129.             print("|%s|%s|%s|"%(t[0].center(20),str(t[1]).center(15),str(t[2]).center(15)))
    130.         print("%s" % "".center(50, "-"))

    131.     def GetRedo(self):
    132.         sqlStr="""
    133.             select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP# group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#
    134.             """
    135.         res = self.ExecSql(sqlStr)
    136.         return res

    137.     def PrintRedo(self,res):
    138.         print("%s" % "".center(148, "-"))
    139.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("GROUP".center(5),"SIZE_MB".center(7),"MEMBER".center(50),"THREAD".center(6),"SEQUENCE".center(8),"MEMBERS".center(7),"ARCHIVED".center(8),"STATUS".center(8),"FIRST_TIME".center(19),"NEXT_TIME".center(19)))
    140.         print("%s" % "".center(148, "-"))
    141.         for t in res:
    142.             if t[9] == None:
    143.                 tStr = "|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(str(t[0]).center(5),str(t[1]).center(7),t[2].center(50),str(t[3]).center(6),str(t[4]).center(8),str(t[5]).center(7),t[6].center(8),t[7].center(8),datetime.datetime.strftime(t[8],"%Y-%m-%d %H:%M:%S"),"None".center(19))
    144.             else:
    145.                 tStr = "|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|" % (
    146.                 str(t[0]).center(5), str(t[1]).center(7), t[2].center(50), str(t[3]).center(6), str(t[4]).center(8),
    147.                 str(t[5]).center(7), t[6].center(8), t[7].center(8),
    148.                 datetime.datetime.strftime(t[8], "%Y-%m-%d %H:%M:%S"),
    149.                 datetime.datetime.strftime(t[9], "%Y-%m-%d %H:%M:%S"))
    150.             print(tStr)
    151.         print("%s" % "".center(148, "-"))

    152.     def GetRedoShift(self):
    153.         sqlStr = """
    154.                   SELECT
    155.                   to_char(first_time,'YYYY-MM-DD') day,
    156.                   to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
    157.                   to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
    158.                   to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
    159.                   to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
    160.                   to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
    161.                   to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
    162.                   to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
    163.                   to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
    164.                   to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
    165.                   to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
    166.                   to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
    167.                   to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
    168.                   to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
    169.                   to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
    170.                   to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
    171.                   to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
    172.                   to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
    173.                   to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
    174.                   to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
    175.                   to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
    176.                   to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
    177.                   to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
    178.                   to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
    179.                   to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
    180.                   from
    181.                   v$log_history
    182.                   GROUP by
    183.                   to_char(first_time,'YYYY-MM-DD') order by day desc
    184.                  """
    185.         res = self.ExecSql(sqlStr)
    186.         return res

    187.     def PrintRedoShift(self,res):
    188.         print("%s" % "".center(132, "-"))
    189.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("DAY".center(10),"00".center(4),"01".center(4),"02".center(4),"03".center(4),"04".center(4),"05".center(4),"06".center(4),"07".center(4),"08".center(4),"09".center(4),"10".center(4),"11".center(4),"12".center(4),"13".center(4),"14".center(4),"15".center(4),"16".center(4),"17".center(4),"18".center(4),"19".center(4),"20".center(4),"21".center(4),"22".center(4),"23".center(4)))
    190.         print("%s" % "".center(132, "-"))
    191.         for t in res:
    192.             print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(t[0].center(10),t[1].center(4),t[2].center(4),t[3].center(4),t[4].center(4),t[5].center(4),t[6].center(4),t[7].center(4),t[8].center(4),t[9].center(4),t[10].center(4),t[11].center(4),t[12].center(4),t[12].center(4),t[13].center(4),t[14].center(4),t[15].center(4),t[16].center(4),t[17].center(4),t[18].center(4),t[19].center(4),t[20].center(4),t[21].center(4),t[22].center(4),t[23].center(4)))
    193.         print("%s" % "".center(132, "-"))


    194.     def GetExecNow(self):
    195.         sqlStr = """
    196.                 select distinct b.SID,b.SERIAL#,p.SPID,b.LAST_CALL_ET,a.sql_id, a.sql_text,b.status,b.event,b.MODULE, b.OSUSER,b.MACHINE from v$sql a,v$session b,v$process p where a.SQL_ID=b.SQL_ID and b.PADDR=p.ADDR and b.STATUS='ACTIVE' order by B.LAST_CALL_ET desc
    197.                 """
    198.         res = self.ExecSql(sqlStr)
    199.         return res

    200.     def PrintExecNow(self,res):
    201.         print("%s" % "".center(188, "-"))
    202.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("SID".center(6),"SERIAL".center(6),"SPID".center(5),"LAST_ET".center(7),"SQL_ID".center(15),"SQL_TEXT".center(60),"STATUS".center(7),"EVENT".center(30),"MODULE".center(15),"OSUSER".center(10),"MACHINE".center(15)))
    203.         print("%s" % "".center(188, "-"))
    204.         for t in res:
    205.             print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(str(t[0]).center(6),str(t[1]).center(6),t[2].center(5),str(t[3]).center(7),t[4].center(15),t[5].strip()[0:60].center(60),t[6].center(7),t[7][0:30].center(30),t[8][0:15].center(15),t[9].center(10),t[10][0:15].center(15)))
    206.         print("%s" % "".center(188, "-"))


    207.     def GetIndexInfo(self):
    208.         if self.Info["OraTable"] == None:
    209.             print("You must specify a oracle table to get indexs information of table.")
    210.             print("If you don't know how to specify the parameters.You can -h to get help")
    211.             exit(6)
    212.         sqlStr = """
    213.                 select
    214.                     table_name,
    215.                     TABLE_TYPE,
    216.                     INDEX_NAME,
    217.                     INDEX_TYPE,
    218.                     TABLE_OWNER,
    219.                     max(columns) columns
    220.                 from
    221.                 (SELECT
    222.                     ui.table_name,
    223.                     ui.TABLE_TYPE,
    224.                     ui.INDEX_NAME,
    225.                     ui.INDEX_TYPE,
    226.                     uic.TABLE_OWNER,
    227.                     to_char(wm_concat (uic.COLUMN_NAME)
    228.                         over(partition by ui.table_name,ui.TABLE_TYPE,ui.INDEX_NAME,ui.INDEX_TYPE,uic.TABLE_OWNER order by uic.COLUMN_POSITION)) columns
    229.                 FROM
    230.                     dba_indexes ui,
    231.                     dba_IND_COLUMNS uic
    232.                 WHERE
    233.                     ui.INDEX_NAME (+) = uic.INDEX_NAME
    234.                 AND ui.TABLE_NAME = UPPER ('%s'))
    235.                 GROUP BY
    236.                     table_name,
    237.                     TABLE_TYPE,
    238.                     INDEX_NAME,
    239.                     INDEX_TYPE,
    240.                     TABLE_OWNER
    241.                 """%self.Info["OraTable"]
    242.         res = self.ExecSql(sqlStr)
    243.         return res


    244.     def PrintIndexInfo(self,res):
    245.         print("%s" % "".center(142, "-"))
    246.         print("|%s|%s|%s|%s|%s|%s|"%("TABLE_NAME".center(20),"TABLE_TYPE".center(20),"INDEX_NAME".center(25),"INDEX_TYPE".center(15),"TABLE_OWNER".center(15),"COLUMNS".center(40)))
    247.         print("%s" % "".center(142, "-"))
    248.         for t in res:
    249.             print("|%s|%s|%s|%s|%s|%s|" % (t[0].center(20), t[1].center(20), t[2].center(25), t[3].center(15), t[4].center(15), t[5].center(40)))

    250.         print("%s" % "".center(142, "-"))


    251.     def GetPartInfo(self):
    252.         if self.Info["OraTable"] == None:
    253.             print("You must specify a oracle table to get indexs information of table.")
    254.             print("If you don't know how to specify the parameters.You can -h to get help")
    255.             exit(6)
    256.         sqlStr = """
    257.                 SELECT
    258.                     a.TABLE_OWNER,
    259.                     a.table_name,
    260.                     c. M,
    261.                     a.PARTITION_NAME,
    262.                     a.HIGH_VALUE,
    263.                     a.NUM_ROWS,
    264.                     a.TABLESPACE_NAME,
    265.                     b.COLUMN_NAME,
    266.                     A .LAST_ANALYZED
    267.                 FROM
    268.                     dba_TAB_PARTITIONS A,
    269.                     DBA_PART_KEY_COLUMNS b,
    270.                     (
    271.                         SELECT
    272.                             SUM (bytes / 1024 / 1024) M,
    273.                             segment_name,
    274.                             partition_name
    275.                         FROM
    276.                             dba_segments
    277.                         WHERE segment_type LIKE '%%TABLE%%'
    278.                         AND partition_name IS NOT NULL
    279.                         and segment_name = upper('%s')
    280.                         GROUP BY
    281.                             segment_name,
    282.                             partition_name
    283.                         ORDER BY
    284.                             segment_name,
    285.                             partition_name DESC
    286.                     ) c
    287.                 WHERE
    288.                     A .TABLE_NAME = b. NAME (+)
    289.                 AND A .table_name = c.SEGMENT_NAME
    290.                 AND A .partition_name = c.PARTITION_NAME
    291.                 AND A .table_name = upper('%s')
    292.                 ORDER BY
    293.                     A .TABLE_NAME,
    294.                     partition_name DESC
    295.                 """%(self.Info["OraTable"],self.Info["OraTable"])
    296.         res = self.ExecSql(sqlStr)
    297.         return res

    298.     def PrintPartInfo(self,res):
    299.         print("%s" % "".center(184, "-"))
    300.         print("|%s|" % "TABLE_OWNER".center(15), end='')
    301.         print("%s|" % "TABLE_NAME".center(25), end='')
    302.         print("%s|" % "MB".center(5), end='')
    303.         print("%s|" % "PARTITION_NAME".center(20), end='')
    304.         print("%s|" % "HIGH_VALUE".center(40), end='')
    305.         print("%s|" % "NUM_ROWS".center(10), end='')
    306.         print("%s|" % "TABLESPACE".center(20), end='')
    307.         print("%s|" % "CULUMN_NAME".center(20), end='')
    308.         print("%s|" % "LAST_ANALYZED".center(19))
    309.         print("%s" % "".center(184, "-"))

    310.         for t in res:
    311.             print("|%s|"%t[0].center(15),end='')
    312.             print("%s|"%t[1].center(25), end='')
    313.             print("%s|"%str(t[2]).center(5), end='')
    314.             print("%s|"%t[3].center(20), end='')
    315.             print("%s|"%t[4][0:40].center(40), end='')
    316.             print("%s|"%str(t[5]).center(10), end='')
    317.             print("%s|"%t[6].center(20), end='')
    318.             if t[7] == None:
    319.                 print("%s|"%"None".center(20))
    320.             else:
    321.                 print("%s|"%t[7].center(20), end='')
    322.             print("%s|"%(datetime.datetime.strftime(t[8],"%Y-%m-%d %H:%M:%S")))
    323.         print("%s" % "".center(184, "-"))

    324.     def OraInfo(self): #这个函数不格式化打印返回结果,只返回结果集
    325.         self.Check_Info()
    326.         if self.Info["OraInfo"].upper() == "TABLESPACE":
    327.             self.GetTableSpace()
    328.         elif self.Info["OraInfo"].upper() == "ASMDISKGROUP":
    329.             self.GetAsmDiskGroup()
    330.         elif self.Info["OraInfo"].upper() == "REDO":
    331.             self.GetRedo()
    332.         elif self.Info["OraInfo"].upper() == "REDOSHIFT":
    333.             self.GetRedoShift()
    334.         elif self.Info["OraInfo"].upper() == "EXECNOW":
    335.             self.GetExecNow()
    336.         elif self.Info["OraInfo"].upper() == "INDEXINFO":
    337.             self.GetIndexInfo()
    338.         elif self.Info["OraInfo"].upper() == "PARTITIONINFO":
    339.             self.GetPartInfo()
    340.         else:
    341.             print("Please enter valid value for -i")
    342.             exit(6)


    343.     def PrintOraInfo(self):
    344.         self.Check_Info()
    345.         if self.Info["OraInfo"].upper() == "TABLESPACE":
    346.             self.PrintTablespace(self.GetTableSpace())
    347.         elif self.Info["OraInfo"].upper() == "ASMDISKGROUP":
    348.             self.PrintAsmDiskGroup(self.GetAsmDiskGroup())
    349.         elif self.Info["OraInfo"].upper() == "REDO":
    350.             self.PrintRedo(self.GetRedo())
    351.         elif self.Info["OraInfo"].upper() == "REDOSHIFT":
    352.             self.PrintRedoShift(self.GetRedoShift())
    353.         elif self.Info["OraInfo"].upper() == "EXECNOW":
    354.             self.PrintExecNow(self.GetExecNow())
    355.         elif self.Info["OraInfo"].upper() == "INDEXINFO":
    356.             self.PrintIndexInfo(self.GetIndexInfo())
    357.         elif self.Info["OraInfo"].upper() == "PARTITIONINFO":
    358.             self.PrintPartInfo(self.GetPartInfo())
    359.         else:
    360.             print("Please enter valid value for -i")
    361.             exit(6)

    362. def fun_help():
    363.     helpStr = """
    364.     Name
    365.         CheckOracle.py display information which you want to know
    366.     
    367.     Synopsis
    368.         CheckOracle.py -H [romote_host] -u [oracle user] -p [oracle password] -s [oracle SID] -i [information] [table_name]
    369.     
    370.     Description
    371.         -H Specify a remote host,defaul 127.0.0.1.
    372.         -u Specify a oracle user
    373.         -p Specify a oracle password
    374.         -s Specify a oracle SID
    375.         -i Specify a action information what you want to know
    376.             Value:
    377.                 tablespace
    378.                 asmdiskgroup
    379.                 redo
    380.                 redoshift
    381.                 execnow
    382.                 indexinfo
    383.                     Table_name must be specified after indexinfo.
    384.                 partitioninfo
    385.                     Table_name must be specified after partitioninfo.
    386.     
    387.     Example
    388.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i execnow
    389.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i indexinfo dept
    390.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i partitioninfo dept
    391.     """
    392.     print(helpStr)

    393. if __name__=="__main__":
    394.     # print(sys.argv)
    395.     # print(len(sys.argv))

    396.     if len(sys.argv) > 1: #判断是否传递了参数
    397.         for i in range(1,len(sys.argv)): #循环参数下标
    398.             # print(sys.argv[i])
    399.             if sys.argv[i] == "-H": #判断参数
    400.                 if re.match("^-",sys.argv[i+1]) == None: #判断当先的下一个参数是否为-开头,-开头不是正取的value值
    401.                     i += 1 #下标加1
    402.                     OracleConn.Info["host"] = sys.argv[i] #获取参数值
    403.                 else:
    404.                     print("-H.You use this parameter,but no value are specified") #当前参数的下一个参数,不是正取的value值,报错退出
    405.                     exit(2)
    406.             elif sys.argv[i] == "-u":
    407.                 if re.match("^-", sys.argv[i+1]) == None:
    408.                     i += 1
    409.                     OracleConn.Info["OraUser"] = sys.argv[i]
    410.                 else:
    411.                     print("-u.You use this parameter,but no value are specified")
    412.                     exit(2)
    413.             elif sys.argv[i] == "-p":
    414.                 if re.match("^-", sys.argv[i+1]) == None:
    415.                     i += 1
    416.                     OracleConn.Info["OraPasswd"] = sys.argv[i]
    417.                 else:
    418.                     print("-p.You use this parameter,but no value are specified")
    419.                     exit(2)
    420.             elif sys.argv[i] == "-P":
    421.                 if re.match("^-", sys.argv[i+1]) == None:
    422.                     i += 1
    423.                     OracleConn.Info["port"] = sys.argv[i]
    424.                 else:
    425.                     print("-P.You use this parameter,but no value are specified")
    426.                     exit(2)
    427.             elif sys.argv[i] == "-S":
    428.                 if re.match("^-", sys.argv[i+1]) == None:
    429.                     i += 1
    430.                     OracleConn.Info["OraSchemas"] = sys.argv[i].upper()
    431.                 else:
    432.                     print("-S.You use this parameter,but no value are specified")
    433.                     exit(2)
    434.             elif sys.argv[i] == "-s":
    435.                 if re.match("^-", sys.argv[i+1]) == None:
    436.                     i += 1
    437.                     OracleConn.Info["OraSID"] = sys.argv[i]
    438.                 else:
    439.                     print("-S.You use this parameter,but no value are specified")
    440.                     exit(2)
    441.             elif sys.argv[i] == "-i":
    442.                 if re.match("^-", sys.argv[i+1]) == None:
    443.                     i += 1
    444.                     OracleConn.Info["OraInfo"] = sys.argv[i]
    445.                     if i+1 < len(sys.argv) and re.match("^-", sys.argv[i+1]) == None:
    446.                         i += 1
    447.                         OracleConn.Info["OraTable"] = sys.argv[i]
    448.                 else:
    449.                     print("-s.You use this parameter,but no value are specified")
    450.                     exit(2)
    451.             elif sys.argv[i] == "-h":
    452.                 fun_help()
    453.                 exit(0)
    454.     else:
    455.         print("Please enter right parameters")
    456.         exit(1)
    457.     oc = OracleConn() #实例化类对象
    458.     oc.PrintOraInfo() #调用类的接口函数,打印结果















    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28572479/viewspace-2153174/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/28572479/viewspace-2153174/

    展开全文
  • 10053事件提供了Oracle如何选择执行计划,为什么会得到这样的执行计划信息。 10053事件的trace文件,只能阅读原始的trace文件,不能使用tkprof工具来处理。 10053事件级别 Level 2 Column statistics Single ...

    【Oracle】获取执行计划之 10053 事件

    10053

    10053事件提供了Oracle如何选择执行计划,为什么会得到这样的执行计划信息。
    10053事件的trace文件,只能阅读原始的trace文件,不能使用tkprof工具来处理。
    

    10053 事件级别

    • Level 2
      Column statistics
      Single Access Paths
      Join Costs
      Table Joins Considered
      Join Methods Considered (NL/MS/HA)
    • Level 1
      Parameters used by the optimizer
      Index statistics
      Column statistics
      Single Access Paths
      Join Costs
      Table Joins Considered
      Join Methods Considered (NL/MS/HA)

    执行步骤

    1. 启用 10053 事件

      ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
      
    2. 执行目标 SQL

      select * from emp;
      
    3. 确定 trace 文件

      SELECT D.VALUE || '\' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||P.SPID || '.trc' AS "trace_file_name"
      FROM (SELECT P.SPID
      FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
              WHERE M.STATISTIC# = 1
              AND S.SID = M.SID
              AND P.ADDR = S.PADDR) P,
          (SELECT T.INSTANCE
              FROM V$THREAD T, V$PARAMETER V
              WHERE V.NAME = 'thread'
              AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
          (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
      
    4. 关闭 10053 事件

      ALTER SESSION SET EVENTS '10053 trace name context off';
      

    10053


    在这里插入图片描述

    展开全文
  • 关于获取执行计划的6种方法和各自区别大家在上一例子中已经大致明白了。  1. explain plan for获取;   2. set autotrace on ;  3. statistics_level=all;  4. 通过dbms_xplan.display_cursor输入sql_
    /*


    分结论3(执行计划,你是真实的吗):


    一.关于获取执行计划的6种方法和各自区别大家在上一例子中已经大致明白了。
        1. explain plan for获取; 
        2. set autotrace on ;    
        3. statistics_level=all;
        4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
        5. 10046 trace跟踪
        6. awrsqrpt.sql
        
    二. 执行计划中"真实执行计划” 是一个很重要的常识,这也就是方法1 和方法2 的最大缺陷了。 
        狠狠揪出本次即将被批斗的坏蛋:方法1的explain plan for和方法2的set autotrace on


    例子主要是针对:绑定变量窥视与直方图


    */


    ---构建T表,数据,及主键


    DROP TABLE t;
    CREATE TABLE t 
    AS 
    SELECT rownum AS id, rpad('*',100,'*') AS pad 
    FROM dual
    CONNECT BY level <= 1000;
    ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
    ---收集统计信息
    BEGIN
      dbms_stats.gather_table_stats(
        ownname          => user, 
        tabname          => 'T', 
        estimate_percent => 100, 
        method_opt       => 'for all columns size 254' 
      );
    END;
    /


    /*
      下面我们将会用多种方法来查看如下语句的执行计划
    VARIABLE id NUMBER
    COLUMN sql_id NEW_VALUE sql_id  
    EXECUTE :id := 990;
    SELECT count(pad) FROM t WHERE id < :id;
    EXECUTE :id := 10;
    SELECT count(pad) FROM t WHERE id < :id;


    */


    ----方法1(explain plan for 的方式。类似PLSQL DEVELOPE里的F5)
    set linesize 1000
    set pagesize 2000
    VARIABLE id NUMBER
    COLUMN sql_id NEW_VALUE sql_id
    EXECUTE :id := 990;
    explain plan for
    SELECT count(pad) FROM t WHERE id < :id;
    select * from table(dbms_xplan.display());



    --明明应该是走全表扫描合适,居然走了索引读,原因是,这个执行计划是假的。
    SQL> select * from table(dbms_xplan.display());


    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------
    Plan hash value: 4270555908
    -------------------------------------------------------------------------------------
    | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |      |     1 |   105 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE              |      |     1 |   105 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T    |    50 |  5250 |     3   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------


       3 - access("ID"<TO_NUMBER(:ID))


    已选择15行。




    ----方法2(set autotrace on 方式)
    /*set autotrace on 
     set autotrace traceonly  
     set autotrace traceonly explain 
     set autotrace traceonl statistics
    */
    set autotrace traceonly
    VARIABLE id NUMBER
    COLUMN sql_id NEW_VALUE sql_id
    EXECUTE :id := 990;
    SELECT count(pad) FROM t WHERE id < :id;


    执行计划
    ----------------------------------------------------------
    Plan hash value: 4270555908
    -------------------------------------------------------------------------------------
    | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |      |     1 |   105 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE              |      |     1 |   105 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T    |    50 |  5250 |     3   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       3 - access("ID"<TO_NUMBER(:ID))
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             19  consistent gets
              0  physical reads
              0  redo size
            426  bytes sent via SQL*Net to client
            415  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

         
             
    ----方法3(statistics level=all的方式)         
    set autotrace off          
    alter session set statistics_level=all ;
    VARIABLE id NUMBER
    COLUMN sql_id NEW_VALUE sql_id
    EXECUTE :id := 990;
    SELECT count(pad) FROM t WHERE id < :id;
    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));



    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------
    SQL_ID  asth1mx10aygn, child number 1
    -------------------------------------
    SELECT count(pad) FROM t WHERE id < :id


    Plan hash value: 2966233522
    -------------------------------------------------------------------------------------
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      19 |
    |   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      19 |
    |*  2 |   TABLE ACCESS FULL| T    |      1 |    988 |    989 |00:00:00.01 |      19 |
    -------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter("ID"<:ID)




    已选择19行。




    ----方法4(知道sql_id后,直接带入的方式)
    select * from table(dbms_xplan.display_cursor('asth1mx10aygn'));


    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------
    SQL_ID  asth1mx10aygn, child number 0
    -------------------------------------
    SELECT count(pad) FROM t WHERE id < :id
    Plan hash value: 2966233522
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |     7 (100)|          |
    |   1 |  SORT AGGREGATE    |      |     1 |   105 |            |          |
    |*  2 |   TABLE ACCESS FULL| T    |   988 |   101K|     7   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter("ID"<:ID)
    已选择19行。
      



    ----方法5(10046TRACE)
    set autotace off
    alter session set statistics_level=typical;     
    alter session set events '10046 trace name context  forever,level 12';
    VARIABLE id NUMBER
    COLUMN sql_id NEW_VALUE sql_id
    EXECUTE :id := 990;
    SELECT count(pad) FROM t WHERE id < :id;
       
    alter session set events '10046 trace name context off';   
    select d.value
    || '/'
    || LOWER (RTRIM(i.INSTANCE, CHR(0)))
    || '_ora_'
    || p.spid
    || '.trc' trace_file_name
    from (select p.spid
          from v$mystat m,v$session s, v$process p
          where  m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
          (select t.INSTANCE
           FROM v$thread t,v$parameter v
           WHERE v.name='thread'
           AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
           (select value
           from v$parameter
           where name='user_dump_dest') d;


    exit       
    tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_3144.trc    d:\10046_3.txt  sys=no sort=prsela,exeela,fchela       




    --观察发现,也是真实的执行计划,全表扫描!


    SQL ID: asth1mx10aygn
    Plan Hash: 2966233522
    SELECT count(pad) 
    FROM
     t WHERE id < :id




    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0         19          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0         19          0           1


    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 94  


    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  SORT AGGREGATE (cr=19 pr=0 pw=0 time=0 us)
        989   TABLE ACCESS FULL T (cr=19 pr=0 pw=0 time=494 us cost=7 size=103740 card=988)




    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       2        0.00          0.00
      SQL*Net message from client                     2        8.06          8.06
    ********************************************************************************



    展开全文
  • [Oracle] 获取执行计划的各方法总结

    千次阅读 2014-04-26 09:49:06
    获取执行计划的6种方法(详细步骤已经在每个例子的开头注释部分说明了): 1. explain plan for获取;  2. set autotrace on ;  3. statistics_level=all; 4. 通过dbms_xplan.display_cursor输入sql_id参数...

    总的结论:

    一.获取执行计划的6种方法(详细步骤已经在每个例子的开头注释部分说明了):
    1. explain plan for获取; 
    2. set autotrace on ; 
    3. statistics_level=all;
    4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
    5. 10046 trace跟踪
    6. awrsqrpt.sql

    二.适用场合分析

    1.如果某SQL执行非常长时间才会出结果,甚至慢到返回不了结果,这时候看执行计划就只能用方法1,或者方法4调用现成的;
    2.跟踪某条SQL最简单的方法是方法1,其次就是方法2;
    3.如果想观察到某条SQL有多条执行计划的情况,只能用方法4和方法6;
    4.如果SQL中含有多函数,函数中套有SQL等多层递归调用,想准确分析,只能使用方法5;
    5.要想确保看到真实的执行计划,不能用方法1和方法2;

    6.要想获取表被访问的次数,只能使用方法3;


    环境构造

    --研究Nested Loops Join访问次数前准备工作

    DROP TABLE t1 CASCADE CONSTRAINTS PURGE; 
    DROP TABLE t2 CASCADE CONSTRAINTS PURGE; 
    CREATE TABLE t1 (
         id NUMBER NOT NULL,
         n NUMBER,
         contents VARCHAR2(4000)
       )
       ; 
    CREATE TABLE t2 (
         id NUMBER NOT NULL,
         t1_id NUMBER NOT NULL,
         n NUMBER,
         contents VARCHAR2(4000)
       )
       ; 
    execute dbms_random.seed(0); 
    INSERT INTO t1
         SELECT  rownum,  rownum, dbms_random.string('a', 50)
           FROM dual
         CONNECT BY level <= 1000
          ORDER BY dbms_random.random; 
    INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
        ORDER BY dbms_random.random; 
    COMMIT; 
    CREATE INDEX t1_n ON t1 (n);
    CREATE INDEX t2_t1_id ON t2(t1_id);

    下面我们将会用多种方法来查看如下语句的执行计划
    SELECT  *
    FROM t1, t2
    WHERE t1.id = t2.t1_id
    AND t1.n in(18,19);

    方法1(explain plan for 的方式。类似PLSQL DEVELOPE里的F5)

      步骤1:explain plan for "你的SQL"
      步骤2:select * from table(dbms_xplan.display()); 

    set linesize 1000
    set pagesize 2000
    explain plan for
    SELECT  *
    FROM t1, t2
    WHERE t1.id = t2.t1_id
    AND t1.n in(18,19);
    select * from table(dbms_xplan.display());
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------
    Plan hash value: 3532430033
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |     2 |  8138 |     6   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                  |          |       |       |            |          |
    |   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 |
    |   3 |    INLIST ITERATOR             |          |       |       |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 |
    |*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 |
    |   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - access("T1"."N"=18 OR "T1"."N"=19)
       6 - access("T1"."ID"="T2"."T1_ID")
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    已选择24行。

    优点:  1.无需真正执行,快捷方便
    缺陷:  1.没有输出运行时的相关统计信息(产生多少逻辑读,多少次递归调用,多少次物理读的情况);
            2.无法判断是处理了多少行;
            3.无法判断表被访问了多少次。    
    确实啊,这毕竟都没有真正执行又如何得知真实运行产生的统计信息。

    方法2(set autotrace on 方式)

      步骤1:set autotrace on 
      步骤2:在此处执行你的SQL即可,后续自然会有结果输出
      
    另,有如下几种方式:
                         set autotrace on                 (得到执行计划,输出运行结果)
                         set autotrace traceonly          (得到执行计划,不输出运行结果)
                         set autotrace traceonly explain  (得到执行计划,不输出运行结果和统计信息部分,仅展现执行计划部分)
                         set autotrace traceonl statistics(不输出运行结果和执行计划部分,仅展现统计信息部分)
    set autotrace on 
    SELECT  *
    FROM t1, t2
    WHERE t1.id = t2.t1_id
    AND t1.n in(18,19);
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3532430033
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |     2 |  8138 |     6   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                  |          |       |       |            |          |
    |   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 |
    |   3 |    INLIST ITERATOR             |          |       |       |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 |
    |*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 |
    |   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - access("T1"."N"=18 OR "T1"."N"=19)
       6 - access("T1"."ID"="T2"."T1_ID")
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             12  consistent gets
              0  physical reads
              0  redo size
           1032  bytes sent via SQL*Net to client
            416  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              2  rows processed

    优点:1.可以输出运行时的相关统计信息(产生多少逻辑读,多少次递归调用,多少次物理读的情况);
            2.虽然必须要等语句执行完毕后才可以输出执行计划,但是可以有traceonly开关来控制返回结果不打屏输出。
                      
    缺陷:1.必须要等到语句真正执行完毕后,才可以出结果;
            2.无法看到表被访问了多少次。        

    方法3(statistics level=all的方式)  

      步骤1:alter session set statistics_level=all ;
      步骤2:在此处执行你的SQL
      步骤3:select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
      
     另注:
     
      1. 如果你用 /*+ gather_plan_statistics */的方法,可以省略步骤1,直接步骤2,3。
      2. 关键字解读(其中OMem、1Mem和User-Mem在后续的课程中会陆续见到): 
        Starts为该sql执行的次数。
        E-Rows为执行计划预计的行数。
        A-Rows为实际返回的行数。A-Rows跟E-Rows做比较,就可以确定哪一步执行计划出了问题。
        A-Time为每一步实际执行的时间(HH:MM:SS.FF),根据这一行可以知道该sql耗时在了哪个地方。
        Buffers为每一步实际执行的逻辑读或一致性读。
        Reads为物理读。
        OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小,
             这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的
        1Mem:当工作区大小无法满足操作所需的大小时,需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作,
             就称一次通过,One-Pass;否则为多次通过,Multi_Pass).该列数据为语句最后一次执行中,单次写磁盘所需要的内存
             大小,这个由优化器统计数据以及前一次执行的性能数据估算得出的
        User-Mem:语句最后一次执行中,当前操作所使用的内存工作区大小,括号里面为(发生磁盘交换的次数,1次即为One-Pass,
               大于1次则为Multi_Pass,如果没有使用磁盘,则显示OPTIMAL)
        OMem、1Mem为执行所需的内存评估值,0Mem为最优执行模式所需内存的评估值,1Mem为one-pass模式所需内存的评估值。
        0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存
      
    set autotrace off          
    alter session set statistics_level=all ;
    SELECT  *
    FROM t1, t2
    WHERE t1.id = t2.t1_id
    AND t1.n in(18,19);
    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------
    SQL_ID  1a914ws3ggfsn, child number 0
    -------------------------------------
    SELECT  * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
    
    Plan hash value: 3532430033
    -----------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |      1 |        |      2 |00:00:00.01 |      12 |
    |   1 |  NESTED LOOPS                  |          |      1 |        |      2 |00:00:00.01 |      12 |
    |   2 |   NESTED LOOPS                 |          |      1 |      2 |      2 |00:00:00.01 |      10 |
    |   3 |    INLIST ITERATOR             |          |      1 |        |      2 |00:00:00.01 |       5 |
    |   4 |     TABLE ACCESS BY INDEX ROWID| T1       |      2 |      2 |      2 |00:00:00.01 |       5 |
    |*  5 |      INDEX RANGE SCAN          | T1_N     |      2 |      1 |      2 |00:00:00.01 |       3 |
    |*  6 |    INDEX RANGE SCAN            | T2_T1_ID |      2 |      1 |      2 |00:00:00.01 |       5 |
    |   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |      2 |      1 |      2 |00:00:00.01 |       2 |
    -----------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - access(("T1"."N"=18 OR "T1"."N"=19))
       6 - access("T1"."ID"="T2"."T1_ID")
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    已选择29行。
    优点:1.可以清晰的从STARTS得出表被访问多少。
            2.可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数,从而可以准确判断Oracle评估是否准确。
            3.虽然没有专门的输出运行时的相关统计信息,但是执行计划中的BUFFERS就是真实的逻辑读的多少
                     
    缺陷:1.必须要等到语句真正执行完毕后,才可以出结果。
            2.无法控制记录输屏打出,不像autotrace有 traceonly 可以控制不将结果打屏输出。
            3.看不出递归调用的次数,看不出物理读的多少(不过逻辑读才是重点)
        

    方法4(知道sql_id后,直接带入的方式,简单,就步骤1)


    步骤1: select  * from table(dbms_xplan.display_cursor('&sq_id')); (该方法是从共享池里得到)


    注:
      1. 还有一个方法,select  * from table(dbms_xplan.display_awr('&sq_id'));(这是awr性能视图里获取到的)
      2. 如果有多执行计划,可以用类似方法查出
        select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
        select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));

    select * from table(dbms_xplan.display_cursor('1a914ws3ggfsn'));
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------
    SQL_ID  1a914ws3ggfsn, child number 0
    -------------------------------------
    SELECT  * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
    
    Plan hash value: 3532430033
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |       |       |     6 (100)|          |
    |   1 |  NESTED LOOPS                  |          |       |       |            |          |
    |   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 |
    |   3 |    INLIST ITERATOR             |          |       |       |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 |
    |*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 |
    |   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access(("T1"."N"=18 OR "T1"."N"=19))
       6 - access("T1"."ID"="T2"."T1_ID")
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)

    优点:1.知道sql_id立即可得到执行计划,和explain plan for 一样无需执行;
            2.可以得到真实的执行计划。(停,等等,啥真实的,刚才这几个套路中,还有假的执行计划的吗?)
            
                     
    缺陷:  1.没有输出运行时的相关统计信息(产生多少逻辑读,多少次递归调用,多少次物理读的情况);
            2.无法判断是处理了多少行;  
            3.无法判断表被访问了多少次。
            

    方法5(10046TRACE)

      步骤1:alter session set events '10046 trace name context  forever,level 12'; (开启跟踪)
      步骤2:执行你的语句
      步骤3:alter session set events '10046 trace name context off';   (关闭跟踪)
      步骤4:找到跟踪后产生的文件
      步骤5:tkprof  trc文件  目标文件  sys=no sort=prsela,exeela,fchela  (格式化命令)     

    set autotace off
    alter session set statistics_level=typical;     
    alter session set events '10046 trace name context  forever,level 12';
    
    SELECT  *
    FROM t1, t2
    WHERE t1.id = t2.t1_id
    AND t1.n in(18,19);   
       
    alter session set events '10046 trace name context off';   
    select d.value
    || '/'
    || LOWER (RTRIM(i.INSTANCE, CHR(0)))
    || '_ora_'
    || p.spid
    || '.trc' trace_file_name
    from (select p.spid
          from v$mystat m,v$session s, v$process p
          where  m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
          (select t.INSTANCE
           FROM v$thread t,v$parameter v
           WHERE v.name='thread'
           AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
           (select value
           from v$parameter
           where name='user_dump_dest') d;
    
    exit      
     
    tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc    d:\10046.txt  sys=no sort=prsela,exeela,fchela       
    
    SELECT  *
    FROM t1, t2
    WHERE t1.id = t2.t1_id
    AND t1.n in(18,19)
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0         12          0           2
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0         12          0           2
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 94  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          2  NESTED LOOPS  (cr=12 pr=0 pw=0 time=0 us)
          2   NESTED LOOPS  (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2)
          2    INLIST ITERATOR  (cr=5 pr=0 pw=0 time=16 us)
          2     TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2)
          2      INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621)
          2    INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622)
          2   TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       2        0.00          0.00
      SQL*Net message from client     

    优点:1.可以看出SQL语句对应的等待事件
            2.如果SQL语句中有函数调用,SQL中有SQL,将会都被列出,无处遁形。
            3.可以方便的看出处理的行数,产生的物理逻辑读。
            4.可以方便的看出解析时间和执行时间。
            5.可以跟踪整个程序包
                                     
    缺陷: 1.步骤繁琐,比较麻烦
            2.无法判断表被访问了多少次。
            3.执行计划中的谓词部分不能清晰的展现出来。 


    方法6. awrsqrpt.sql   

      步骤1:@?/rdbms/admin/awrsqrpt.sql
      步骤2:选择你要的断点(begin snap 和end snap)
      步骤3:输入你的sql_id     


    展开全文
  • 获取运行计划的6种方法(具体步骤已经在每一个样例的开头凝视部分说明了): 1. explain plan for获取; 2. set autotrace on 。 3. statistics_level=all; 4. 通过dbms_xplan.display_cursor输入sql_id參数直接...
  • oracle如何获取当前登录的用户名

    千次阅读 2007-05-30 00:09:00
    Microsoft Windows [版本 5.2.3790](C) 版权所有 1985-2003 Microsoft Corp.C:/>sqlplusSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 5月 30 00:04:26 2007Copyright (c) 1982, 2002, Oracle Corporation....
  • oracle提供了很多工具来查找最低效的SQL,但是,本质上就是通过如下SQL语句,从相关系统视图中获取最低效的SQL: select rownum as rank, a.* from (select PARSING_SCHEMA_NAME RUN_USER, --执行用户 SQL_...
  • 获取执行计划的6种方法(详细步骤已经在每个例子的开头注释部分说明了): 1. explain plan for获取; 2. set autotrace on ; 3. statistics_level=all; 4. 通过dbms_xplan.display_cursor输入sql_id参数...
  • 1.spid (system process id) 是操作系统层面的进程id . 2.pid(process id) 这个是基于oracle的进程id个人理解为就是oracle给自己的进程的一个编号。 3.sid.(这个就是session 的id) 这个个人理解为,就是有人在同...
  • 【方法整理】Oracle 获取trace跟踪文件名的几种常用方式 1 BLOG文档结构图 2 前言部分 2.1 导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~...
  • ORACLE 获取绑定变量值

    2016-06-17 14:39:31
    --- 查看进程正在执行的SQL select p.SPID,sql.SQL_TEXT,sql.sql_id,s.* from gv$process p , gv$session s,gv$sqlarea sql wh...
  • 获取执行计划的6种方法1. explain plan for获取;2. set autotrace on ;3. statistics_level=all;4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取5. 10046 trace跟踪6. awrsqrpt.sql适用场合分析1.如果某...
  • 获取oracle trace文件路径 10g: /* 11g依然有效 */ SELECT d.VALUE || ‘/’ || LOWER (RTRIM (i.instance, CHR (0))) || ‘ora’ || p.spid || ‘.trc’ trace_file_name FROM (SELECT p.spid FROM sys.vmystatm,...
  • SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.s...
  • Oracle获取登录者IP

    千次阅读 2016-11-24 14:10:42
    SELECT p.spid, v.program INTO v_pid, v_program FROM v$process p, v$session v WHERE p.addr = v.paddr AND v.sid = v_sid; v_os_user := sys_context('userenv', 'os_user'); dbms_application_info.read_module...
  • 获取默认文件名SQL如下:--第1种 SELECTu_Dump.Value||'/'||Lower(Db_Name.Value)||'_ora_'|| V$process.Spid|| Nvl2(V$process.Traceid,'_'||V$process.Traceid,NULL)||'.trc'"Trace...
  • Oracle获取session的trace

    2013-12-30 21:51:00
    11G 之前获取session对应的trace文件的完整路径 可以使用下面的SQL SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file  FROM (SELECT VALUE FROM v$parameter WHERE ...
  • 1.set autotrace on 相信这种方法是最简单的,也是最常用的一样方法,这种方法经常用到分析一条SQL,这里贴出语法,很简单 SQL> set autotrace Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]]...
  • SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace  FROM v$process a, v$session b, v$parameter c, v$instance d  WHERE a.addr = b.paddr  AND b.audsid = userenv('sessionid'...
  • 获取执行计划的6种方法(详细步骤已经在每个例子的开头注释部分说明了):1. explain plan for获取; 2. set autotrace on ; 3. statistics_level=all; 4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取 5. ...
  • 获取oracle跟踪文件名

    2007-12-26 12:34:00
    linux下SELECT d.VALUE || / || LOWER (RTRIM (i.INSTANCE, CHR (0))) || _ora_ || p.spid || .trc trace_file_name FROM (SELECT p.spid FROM v$
  • 获取执行计划的6种方法(详细步骤已经在每个例子的开头注释部分说明了):1. explain plan for获取;2. set autotrace on ;3. statistics_level=all;4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取5. ...
  • ST04-Oracle性能数据获取的感想 最近在整理一些关于SAP性能方面的信息,在涉及到数据库性能的时候,一般我们使用ST04来察看,虽然自己之前也做过ORACLE DBA相关的工作,但是就ST04也能给相...
  • ♣题目部分在Oracle中,如何获取trace文件的路径? ♣答案部分跟踪文件(Trace File)一般位于“user_dump_dest”参数所指定的目录中,...
  • 创建一个获取当前trace文件的函数 createorreplacefunctioncurrent_tracereturnvarchar2isv_filevarchar2(100);beginselectpar.value||'/'||ins.instance_name||'_ora_'||pro.spid||'.trc'intov_filef...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,168
精华内容 2,067
关键字:

oraclespid获取