before.DBID = after.DBID and
after.value >= before.value 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 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)
else:
q_string += " = '"+subobject_name+"'"
q_string += """ and
so.OBJECT_TYPE='"""
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)
after.snap_id=before.snap_id+1 and
after.instance_number = """
q_string += instance_number
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)',
and
to_date('"""
q_string += end_time
q_string += """','DD-MON-YYYY HH24:MI:SS') and
session_state = 'ON CPU'
and a.INST_ID = """
q_string += instance_number
q_string += """
group by sample_time) cpu_time
where
all_time.sample_time=cpu_time.sample_time(+)
group by to_char(all_time.sample_time,'YYYY/MM/DD HH24:MI')
"""
return q_string
database,dbconnection = util.script_startup('ASH active session counts')
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')
# 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,instance_number)
dbconnection.run_return_no_results(dbacrtable);
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER and
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')
group by sn.END_INTERVAL_TIME
order by sn.END_INTERVAL_TIME"""
return q_string
database, dbconnection = util.script_startup(
'Run statistics for all sql statements')
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 = allsql(start_time, end_time, instance_number)
r = dbconnection.run_return_flipped_results(q)
Contact: [email protected] sigfour.py Plots executions, average elapsed, cpu percent and average single block IO time. """ import signatures import myplot import util database,dbconnection = util.script_startup('SQL statments by signature four plots') 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:
(select * from """
query_string += self.perm_table_name + ' order by '
query_string += day_column
query_string += """ desc)
where rownum < 41)
order by """ + day_column
return self.db_connection.run_return_all_results(query_string)
def get_column_names(self):
return self.db_connection.get_column_names()
# Main program starts here
database, dbconnection = util.script_startup(
'Database CPU by Application Area')
day = util.input_no_default('Enter day of week: ')
user = util.my_oracle_user
c = cpubymachine(day, 8, 17)
lines = util.read_config_file(util.config_dir, database + util.ashcpu_file)
for l in lines:
args = l.split()
if len(args) == 2:
c.add_machine(args[0], args[1])
querytext = c.build_query()
Contact: [email protected] sigselapctcpu.py Plots elapsed for a group of sql statements based on their signatures against percent CPU of the host. """ import myplot import util import signatures database,dbconnection = util.script_startup('SQL statments by signature Elapsed and CPU') 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:
stat.INSTANCE_NUMBER = snap.INSTANCE_NUMBER and
stat.STAT_NAME = 'logons current' and
snap.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
snap.END_INTERVAL_TIME"""
return q_string
database,dbconnection = util.script_startup('Graph connected session count')
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')
query = sessioncounts(start_time,end_time,instance_number)
results = dbconnection.run_return_flipped_results(query)
util.exit_no_results(results)
date_times = results[0]
session_counts = results[1]
(select * from
(select * from """
query_string +=self.perm_table_name+' order by '
query_string +=day_column
query_string +=""" desc)
where rownum < 41)
order by """+day_column
return self.db_connection.run_return_all_results(query_string)
def get_column_names(self):
return self.db_connection.get_column_names()
# Main program starts here
database,dbconnection = util.script_startup('Database CPU by Application Area')
day=util.input_no_default('Enter day of week: ')
user=util.my_oracle_user
c = cpubymachine(day,8,17)
lines = util.read_config_file(util.config_dir,database+util.ashcpu_file)
for l in lines:
args = l.split()
if len(args) == 2:
c.add_machine(args[0],args[1])
querytext = c.build_query()
where
my.SNAP_ID = sn.SNAP_ID 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 my.SNAP_ID
"""
return q_string
database, dbconnection = util.script_startup('Host CPU')
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 cpu busy, idle, and load for the selected instance
dbconnection.run_return_no_results_catch_error("drop table myoscpu")
crmyoscpu = myoscpu(instance_number)
q_string += """
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER and
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 snap_id,sql_id"""
return q_string
database,dbconnection = util.script_startup('Run statistics for one SQL id')
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
sql_id=util.input_with_default('SQL_ID','acrg0q0qtx3gr')
q = simplesqlstat(sql_id,start_time,end_time,instance_number);
r = dbconnection.run_return_flipped_results(q)
q_string += """
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER and
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 snap_id,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')
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')
mainquery = sqlstatwithplans(sql_id,start_time,end_time,instance_number)
mainresults = dbconnection.run_return_flipped_results(mainquery)
after.value >= before.value 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 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
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER and
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 snap_id,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')
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')
mainquery = sqlstatwithplans(sql_id, start_time, end_time, instance_number)
Contact: [email protected] sigscpuio.py Plots elapsed, cpu, and io for a group of sql statements based on their signatures. """ import myplot import util import signatures database,dbconnection = util.script_startup('SQL statments by signature CPU and IO') 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:
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')
group by sn.END_INTERVAL_TIME
order by sn.END_INTERVAL_TIME
"""
return q_string
database, dbconnection = util.script_startup(
'Elapsed, CPU, and IO for one SQL id')
sql_id = util.input_with_default('SQL_ID', 'acrg0q0qtx3gr')
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')
querytext = sqlstatcpuio(sql_id, start_time, end_time, instance_number)
results = dbconnection.run_return_flipped_results(querytext)
q_string += instance_number
q_string += """ and
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
Contact: [email protected] sigscpuio.py Plots elapsed, cpu, and io for a group of sql statements based on their signatures. """ import myplot import util import signatures database, dbconnection = util.script_startup( 'SQL statments by signature CPU and IO') queryobj = signatures.groupofsignatures() 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)) querytext = queryobj.build_query2() results = dbconnection.run_return_flipped_results(querytext) util.exit_no_results(results)
q_string += """' and
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')
group by sn.END_INTERVAL_TIME
order by sn.END_INTERVAL_TIME
"""
return q_string
database,dbconnection = util.script_startup('Elapsed, CPU, and IO for one SQL id')
sql_id=util.input_with_default('SQL_ID','acrg0q0qtx3gr')
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')
querytext = sqlstatcpuio(sql_id,start_time,end_time,instance_number)
results = dbconnection.run_return_flipped_results(querytext)
util.exit_no_results(results)
vt.NAME = dt.TABLESPACE_NAME and
snap.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')
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
Contact: [email protected] sigselapctcpu.py Plots elapsed for a group of sql statements based on their signatures against percent CPU of the host. """ import myplot import util import signatures database, dbconnection = util.script_startup( 'SQL statments by signature Elapsed and CPU') queryobj = signatures.groupofsignatures() 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)) querytext = queryobj.build_query3() results = dbconnection.run_return_flipped_results(querytext) util.exit_no_results(results)
select
to_char(sn.END_INTERVAL_TIME,'MM-DD HH24:MI') DATE_TIME,
ss.executions_delta,
ELAPSED_TIME_DELTA/(executions_delta*1000) ELAPSED_AVG_MS
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"
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')
and adirect.INSTANCE_NUMBER = """
q_string += instance_number
q_string += """
order by sn.END_INTERVAL_TIME
"""
return q_string
database, dbconnection = util.script_startup('NOLOGGING Write IO')
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')
querytext = nologging(start_time, end_time, instance_number)
results = dbconnection.run_return_flipped_results(querytext)
util.exit_no_results(results)
Contact: [email protected] sigfour.py Plots executions, average elapsed, cpu percent and average single block IO time. """ import signatures import myplot import util database, dbconnection = util.script_startup( 'SQL statments by signature four plots') queryobj = signatures.groupofsignatures() 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)) querytext = queryobj.build_query4() results = dbconnection.run_return_flipped_results(querytext) util.exit_no_results(results)
after.PHYWRTS >= before.PHYWRTS and
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')
group by sn.END_INTERVAL_TIME
order by sn.END_INTERVAL_TIME
"""
return q_string
database,dbconnection = util.script_startup('I/O Summary')
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')
query = iosummary(start_time,end_time,instance_number)
results = dbconnection.run_return_flipped_results(query)
util.exit_no_results(results)
date_times = results[0]
terabytes_read = results[1]
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')
group by sn.END_INTERVAL_TIME
order by sn.END_INTERVAL_TIME
"""
return q_string
database, dbconnection = util.script_startup('I/O Summary')
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')
query = iosummary(start_time, end_time, instance_number)
results = dbconnection.run_return_flipped_results(query)
util.exit_no_results(results)
after.event_name=before.event_name and
after.snap_id=before.snap_id+1 and
after.instance_number = """
q_string += instance_number
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
This shows the average elapsed time and total number of executions for
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:
DBA_HIST_SNAPSHOT sn
where
my.SNAP_ID = sn.SNAP_ID 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 my.SNAP_ID
"""
return q_string
database,dbconnection = util.script_startup('Host CPU')
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 cpu busy, idle, and load for the selected instance
dbconnection.run_return_no_results_catch_error("drop table myoscpu")
crmyoscpu = myoscpu(instance_number)
dbconnection.run_return_no_results(crmyoscpu);
