def onewait(): # Get user input wait_event = util.input_with_default('wait event', 'db file sequential read') min_waits = int( util.input_with_default('minimum number of waits per hour', '0')) # Use my db login credentials user = util.my_oracle_user password = util.get_oracle_password(database) # Build and run query q = perfq.onewait(wait_event, min_waits) c = db.connection(user, password, database) r = c.run_return_flipped_results(q) # plot query title = "'" + wait_event + "' waits on " + database + " database, minimum waits=" + str( min_waits) top_label = "Number of events" bottom_label = "Averaged Elapsed Microseconds" date_time = r[0] num_events = r[1] avg_elapsed = r[2] myplot.frequency_average(title, top_label, bottom_label, date_time, num_events, avg_elapsed)
def simplesqlstat(): # Get user input sql_id = util.input_with_default('SQL_ID', 'acrg0q0qtx3gr') # Use my db login credentials user = util.my_oracle_user password = util.get_oracle_password(database) # Build and run query q = perfq.simplesqlstat(sql_id) c = db.connection(user, password, database) r = c.run_return_flipped_results(q) # plot query title = "Sql_id " + sql_id + " on " + database + " database" top_label = "Number of executions" bottom_label = "Averaged Elapsed Milliseconds" date_time = r[0] executions = r[1] avg_elapsed = r[2] myplot.frequency_average(title, top_label, bottom_label, date_time, executions, avg_elapsed)
def ashcount(): """ Shows ASH active session counts in time period. """ user = util.my_oracle_user start_time = util.input_with_default( 'Start date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-1900 12:00:00') end_time = util.input_with_default( 'End date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-2200 12:00:00') querytext = perfq.ashcputotal(start_time, end_time) user = util.my_oracle_user password = util.get_oracle_password(database) dbconn = db.connection(user, password, database) results = dbconn.run_return_flipped_results(querytext) if results == None: print "No results returned" return # plot query title = "ASH active session count for " + database + " database" y_label = "Sessions" number_of_plots = 2 plot_names = ["Total", "CPU"] myplot.plotmulti(title, y_label, number_of_plots, plot_names, results)
def script_startup(script_description): util.load_configuration() # global variable holding database name database = 'ORCL' parser = argparse.ArgumentParser( description=script_description, epilog="See README for more detailed help.") parser.add_argument('destination', choices=['file', 'screen'], help='Where to send the graph') parser.add_argument('database', default=None, nargs='?', help='Name of the database') parser.add_argument('showsql', choices=['Y', 'N'], help='Show SQL that was executed (Y or N)') parser.add_argument('showdata', choices=['Y', 'N'], help='Show data returned by query (Y or N)') args = parser.parse_args() if args.database != None: database = args.database.upper() else: database = util.input_with_default('database', 'ORCL') myplot.destination = args.destination db.showsql = args.showsql db.showdata = args.showdata user = util.my_oracle_user password = util.get_oracle_password(database) # Set locale so we can test in IDLE locale.setlocale(locale.LC_ALL, 'C') return database, db.connection(user, password, database)
def script_startup(script_description): util.load_configuration() # global variable holding database name database='ORCL' parser = argparse.ArgumentParser(description=script_description, epilog="See README for more detailed help.") parser.add_argument('destination', choices=['file', 'screen'], help='Where to send the graph') parser.add_argument('database',default=None,nargs='?', help='Name of the database') parser.add_argument('showsql', choices=['Y', 'N'], help='Show SQL that was executed (Y or N)') parser.add_argument('showdata', choices=['Y', 'N'], help='Show data returned by query (Y or N)') args = parser.parse_args() if args.database != None: database = args.database.upper() else: database=util.input_with_default('database','ORCL') myplot.destination = args.destination db.showsql = args.showsql db.showdata = args.showdata user=util.my_oracle_user password=util.get_oracle_password() # Set locale so we can test in IDLE locale.setlocale(locale.LC_ALL, 'C') return database,db.connection(user,password,database)
from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn where ss.sql_id = '""" q_string += sql_id q_string += """' and ss.snap_id=sn.snap_id and executions_delta > 0 and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER order by ss.snap_id,ss.sql_id""" return q_string database, dbconnection = util.script_startup('Run statistics for one SQL id') # Get user input sql_id = util.input_with_default('SQL_ID', 'acrg0q0qtx3gr') q = simplesqlstat(sql_id) r = dbconnection.run_return_flipped_results(q) # plot query myplot.title = "Sql_id " + sql_id + " on " + database + " database" myplot.ylabel1 = "Number of executions" myplot.ylabel2 = "Averaged Elapsed Milliseconds" myplot.xlabels = r[0] myplot.ylists = r[1:] myplot.line_2subplots()
between to_date('""" q_string += start_time q_string += """','DD-MON-YYYY HH24:MI:SS') and to_date('""" q_string += end_time q_string += """','DD-MON-YYYY HH24:MI:SS') group by snap.END_INTERVAL_TIME order by snap.END_INTERVAL_TIME""" return q_string database,dbconnection = util.script_startup('Tablespace usage') start_time=util.input_with_default('Start date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-2018 12:00:00') end_time=util.input_with_default('End date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-2200 12:00:00') # Get and run query for one system statistic querytext = spaceq(start_time,end_time) results = dbconnection.run_return_flipped_results(querytext) util.exit_no_results(results) # plot query myplot.xdatetimes = results[0] myplot.ylists = results[1:]
after.instance_number=1 and before.instance_number=after.instance_number and after.snap_id=sn.snap_id and after.instance_number=sn.instance_number and (after.total_waits-before.total_waits) > """ q_string += str(minimum_waits) q_string += """ order by after.snap_id """ return q_string database,dbconnection = util.script_startup('One wait event') # Get user input wait_event=util.input_with_default('wait event','db file sequential read') min_waits=int(util.input_with_default('minimum number of waits per hour','0')) # Build and run query q = onewait(wait_event,min_waits); r = dbconnection.run_return_flipped_results(q) # plot query myplot.title = "'"+wait_event+"' waits on "+database+" database, minimum waits="+str(min_waits) myplot.ylabel1 = "Number of events" myplot.ylabel2 = "Averaged Elapsed Microseconds" myplot.xdatetimes = r[0]
q_string += """ and before.instance_number=after.instance_number and after.snap_id=sn.snap_id and after.instance_number=sn.instance_number and (after.total_waits-before.total_waits) > """ q_string += str(minimum_waits) q_string += """ order by after.snap_id """ return q_string database,dbconnection = util.script_startup('One wait event') # Get user input wait_event=util.input_with_default('wait event','db file sequential read') min_waits=int(util.input_with_default('minimum number of waits per hour','0')) start_time=util.input_with_default('Start date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-1900 12:00:00') end_time=util.input_with_default('End date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-2200 12:00:00') instance_number=util.input_with_default('Database Instance (1 if not RAC)','1') # Build and run query q = onewait(wait_event,min_waits,start_time,end_time,instance_number); r = dbconnection.run_return_flipped_results(q)
between to_date('""" q_string += start_time q_string += """','DD-MON-YYYY HH24:MI:SS') and to_date('""" q_string += end_time q_string += """','DD-MON-YYYY HH24:MI:SS') order by sn.SNAP_ID""" return q_string database, dbconnection = util.script_startup('One System Statistic') start_time = util.input_with_default( 'Start date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-2018 12:00:00') end_time = util.input_with_default( 'End date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-2200 12:00:00') instance_number = util.input_with_default('Database Instance (1 if not RAC)', '1') stat_name = util.input_with_default('System Statistic', 'bytes received via SQL*Net from client') # Get and run query for one system statistic querytext = onesysstat(start_time, end_time, instance_number, stat_name) results = dbconnection.run_return_flipped_results(querytext)
before.instance_number=after.instance_number and after.snap_id=sn.snap_id and after.instance_number=sn.instance_number and (after.total_waits-before.total_waits) > """ q_string += str(minimum_waits) q_string += """ order by after.snap_id """ return q_string database, dbconnection = util.script_startup('One wait event') # Get user input wait_event = util.input_with_default('wait event', 'db file sequential read') min_waits = int( util.input_with_default('minimum number of waits per hour', '0')) start_time = util.input_with_default( 'Start date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-1900 12:00:00') end_time = util.input_with_default( 'End date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-2200 12:00:00') instance_number = util.input_with_default('Database Instance (1 if not RAC)', '1') # Build and run query
ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER and sn.END_INTERVAL_TIME between to_date('""" q_string += start_time q_string += """','DD-MON-YYYY HH24:MI:SS') and to_date('""" q_string += end_time q_string += """','DD-MON-YYYY HH24:MI:SS') order by ss.snap_id""" return q_string database,dbconnection = util.script_startup('Usage statistics for one segment') start_time=util.input_with_default('Start date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-1900 12:00:00') end_time=util.input_with_default('End date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-2200 12:00:00') instance_number=util.input_with_default('Database Instance (1 if not RAC)','1') # Get user input owner=util.input_with_default('OWNER','SYS') object_name=util.input_with_default('OBJECT_NAME','OBJ$') subobject_name=util.input_with_default('SUBOBJECT_NAME','') if subobject_name == '': subobject_name = None object_type=util.input_with_default('OBJECT_TYPE','TABLE')
q_string += end_time q_string += """','DD-MON-YYYY HH24:MI:SS') and event='""" q_string += wait_event q_string += """' group by to_char(sample_time,'YYYY/MM/DD HH24:MI') """ return q_string database, dbconnection = util.script_startup('ASH one wait event') # Get user input wait_event = util.input_with_default('wait event', 'db file sequential read') start_time = util.input_with_default( 'Start date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-1900 12:00:00') end_time = util.input_with_default( 'End date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-2200 12:00:00') # first get ash counts by minutes from dba view dbconnection.run_return_no_results_catch_error("drop table dbaashcount") dbacrtable = dbaashcount(start_time, end_time, wait_event) dbconnection.run_return_no_results(dbacrtable)
choices=['file', 'screen'], help='Where to send the graph') parser.add_argument('database', default=None, nargs='?', help='Name of the database') parser.add_argument('showsql', choices=['Y', 'N'], help='Show SQL that was executed (Y or N)') args = parser.parse_args() if args.database <> None: database = args.database.upper() else: database = util.input_with_default('database', 'ORCL') myplot.destination = args.destination db.showsql = args.showsql if args.reportname == 'ashcpu': ashcpu() elif args.reportname == 'onewait': onewait() elif args.reportname == 'simplesqlstat': simplesqlstat() elif args.reportname == 'allsql': allsql() elif args.reportname == 'groupsigs': groupsigs() elif args.reportname == 'sigscpuio': sigscpuio()
q_string += object_type q_string += """' and so.DBID = ss.DBID and so.TS# = ss.TS# and so.OBJ# = ss.OBJ# and so.DATAOBJ# = ss.DATAOBJ# and ss.snap_id=sn.snap_id and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER order by ss.snap_id""" return q_string database,dbconnection = util.script_startup('Usage statistics for one segment') # Get user input owner=util.input_with_default('OWNER','SYS') object_name=util.input_with_default('OBJECT_NAME','OBJ$') subobject_name=util.input_with_default('SUBOBJECT_NAME','') if subobject_name == '': subobject_name = None object_type=util.input_with_default('OBJECT_TYPE','TABLE') q = segstat(owner,object_name,subobject_name,object_type); r = dbconnection.run_return_flipped_results(q) util.exit_no_results(r) # plot query
from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn where ss.sql_id = '""" q_string += sql_id q_string += """' and ss.snap_id=sn.snap_id and executions_delta > 0 and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER order by ss.snap_id,ss.sql_id,plan_hash_value""" return q_string database, dbconnection = util.script_startup('Graph execution time by plan') # Get user input sql_id = util.input_with_default('SQL_ID', 'dkqs29nsj23jq') mainquery = sqlstatwithplans(sql_id) mainresults = dbconnection.run_return_flipped_results(mainquery) util.exit_no_results(mainresults) date_times = mainresults[0] plan_hash_values = mainresults[1] elapsed_times = mainresults[2] num_rows = len(date_times) """ There are multiple rows for a given date and time. Build list of distinct date times and build a list of
between to_date('""" q_string += start_time q_string += """','DD-MON-YYYY HH24:MI:SS') and to_date('""" q_string += end_time q_string += """','DD-MON-YYYY HH24:MI:SS') order by ss.snap_id""" return q_string database, dbconnection = util.script_startup( 'Usage statistics for one segment') start_time = util.input_with_default( 'Start date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-1900 12:00:00') end_time = util.input_with_default( 'End date and time (DD-MON-YYYY HH24:MI:SS)', '01-JAN-2200 12:00:00') instance_number = util.input_with_default('Database Instance (1 if not RAC)', '1') # Get user input owner = util.input_with_default('OWNER', 'SYS') object_name = util.input_with_default('OBJECT_NAME', 'OBJ$') subobject_name = util.input_with_default('SUBOBJECT_NAME', '') if subobject_name == '': subobject_name = None
a group of SQL statements defined by their force matching signature. A signature represents a group of queries that are the same except for their constants. The goal of this query is to pick some group of queries that we care about such as the main queries the users use every day and show their performance over time. It does hide the details of the individual queries but may have value if we choose the best set of signatures. """ import myplot import util import signatures database,dbconnection = util.script_startup('Stats for SQL statments by signature') start_time=util.input_with_default('Start date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-1900 12:00:00') end_time=util.input_with_default('End date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-2200 12:00:00') instance_number=util.input_with_default('Database Instance (1 if not RAC)','1') queryobj = signatures.groupofsignatures() queryobj.set_start_end_instance(start_time,end_time,instance_number) lines = util.read_config_file(util.config_dir,database+util.groupsigs_file) for line in lines: if len(line) > 0: queryobj.add_signature(int(line))
sn.END_INTERVAL_TIME between to_date('""" q_string += start_time q_string += """','DD-MON-YYYY HH24:MI:SS') and to_date('""" q_string += end_time q_string += """','DD-MON-YYYY HH24:MI:SS') order by sn.SNAP_ID""" return q_string database,dbconnection = util.script_startup('One System Statistic') start_time=util.input_with_default('Start date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-2018 12:00:00') end_time=util.input_with_default('End date and time (DD-MON-YYYY HH24:MI:SS)','01-JAN-2200 12:00:00') instance_number=util.input_with_default('Database Instance (1 if not RAC)','1') stat_name=util.input_with_default('System Statistic','bytes received via SQL*Net from client') # Get and run query for one system statistic querytext = onesysstat(start_time,end_time,instance_number,stat_name) results = dbconnection.run_return_flipped_results(querytext) util.exit_no_results(results)