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)
