I created a new graph in my PythonDBAGraphs to show how a plan change affected execution time. The legend in the upper left is plan hash value numbers. Normally I run the equivalent as a sqlplus script and just look for plans with higher execution times. I used it today for the SQL statement with SQL_ID c6m8w0rxsa92v. It has been running slow since 10/11/2016.
Since I just split up my Python graphs into multiple smaller scripts I decided to build this new Python script to see how easy it would be to show the execution time of the SQL statement for different plans graphically. It was not hard to build this. Here is the script (sqlstatwithplans.py):
import myplot import util def sqlstatwithplans(sql_id): q_string = """ select to_char(sn.END_INTERVAL_TIME,'MM-DD HH24:MI') DATE_TIME, plan_hash_value, ELAPSED_TIME_DELTA/(executions_delta*1000000) ELAPSED_AVG_SEC 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','acrg0q0qtx3gr') mainquery = sqlstatwithplans(sql_id) mainresults = dbconnection.run_return_flipped_results(mainquery) util.exit_no_results(mainresults) date_times = mainresults plan_hash_values = mainresults elapsed_times = mainresults num_rows = len(date_times) # build list of distict plan hash values distinct_plans =  for phv in plan_hash_values: string_phv = str(phv) if string_phv not in distinct_plans: distinct_plans.append(string_phv) # build a list of elapsed times by plan # create list with num plans empty lists elapsed_by_plan =  for p in distinct_plans: elapsed_by_plan.append() # update an entry for every plan # None for ones that aren't # in the row for i in range(num_rows): plan_num = distinct_plans.index(str(plan_hash_values[i])) for p in range(len(distinct_plans)): if p == plan_num: elapsed_by_plan[p].append(elapsed_times[i]) else: elapsed_by_plan[p].append(None) # plot query myplot.xlabels = date_times myplot.ylists = elapsed_by_plan myplot.title = "Sql_id "+sql_id+" on "+database+ " database with plans" myplot.ylabel1 = "Averaged Elapsed Seconds" myplot.ylistlabels=distinct_plans myplot.line()
Having all of the Python code for this one graph in a single file made it much faster to put together a new graph. Pretty neat.
Thursday, Oct 20, 2016, 12:30 PM
Republic Services – 3rd Floor Conference Room
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ
16 AZORAS Attending
Change In Plans -Tim Gorman comes to Phoenix! Stephen Andert had a sudden business commitment making it impossible for him to speak at Thursday’s meeting.Fortunately, Tim Gorman of Delphix will be coming from Denver to speak instead. Tim is an internationally-renowned speaker, performance specialist, member of the Oak Table, Oracle Ace Director, …
Phoenix area readers – I just found out that Oracle performance specialist and Delphix employee Tim Gorman will be speaking at the Arizona User Group meeting tomorrow in Scottsdale. I am looking forward to it.
We share our skills to maximize your revenue!