Sql Performance Tuning with ASH & AWR: Real World Use Cases

Editor's Notes

• #6: How many of you know how to write SQL?How many of you know the difference between a Physical I/O and a Logical I/O?
• #8: Collects database performance statistics stored in memory and in database used by ADDM and Advisors (SQL Tuning, Segment, Undo)Show expensive SQL and its elapsed and CPU times…point that remaining is waitsShow that same SQLs others statsPoint that it could be IO and show wait events sectionAlso show ‘segment statistics’ section for objects of this SQLMention that AWR does NOT give wait or segment statistics by SQL or sessionTime to gather more data specific to our SQL or Session…Select * from dict where table_name like ‘DBA_HIST%SQL%’;TABLE_NAME COMMENTS------------------------------ --------------------------------------------------------------------------------DBA_HIST_COLORED_SQL Marked SQLs for snapshotsDBA_HIST_SQLBIND SQL Bind InformationDBA_HIST_SQLCOMMAND_NAME Sql command typesDBA_HIST_SQLSTAT SQL Historical Statistics InformationDBA_HIST_SQLTEXT SQL TextDBA_HIST_SQL_BIND_METADATA SQL Bind Metadata InformationDBA_HIST_SQL_PLAN SQL Plan InformationDBA_HIST_SQL_SUMMARY Summary of SQL StatisticsDBA_HIST_SQL_WORKAREA_HSTGRM SQL Workarea Histogram Historyselect distinct stat_name from V$SYS_TIME_MODEL ;STAT_NAME----------------------------------------------------------------DB CPUbackground elapsed timehard parse elapsed timeRMAN cpu time (backup/restore)DB timehard parse (bind mismatch) elapsed timeinbound PL/SQL rpc elapsed timerepeated bind elapsed timebackground cpu timeconnection management call elapsed timefailed parse elapsed timehard parse (sharing criteria) elapsed timePL/SQL compilation elapsed timefailed parse (out of shared memory) elapsed timeJava execution elapsed timeparse time elapsedsequence load elapsed timesql execute elapsed timePL/SQL execution elapsed time19 rows selected.
• #9: 5min max
• #10: 11g has SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION to further drilldown an SQL performance issue
• #11: Collects database performance statistics stored in memory and in database used by ADDM and Advisors (SQL Tuning, Segment, Undo)Show expensive SQL and its elapsed and CPU times…point that remaining is waitsShow that same SQLs others statsPoint that it could be IO and show wait events sectionAlso show ‘segment statistics’ section for objects of this SQLMention that AWR does NOT give wait or segment statistics by SQL or sessionTime to gather more data specific to our SQL or Session…Select * from dict where table_name like ‘DBA_HIST%SQL%’;TABLE_NAME COMMENTS------------------------------ --------------------------------------------------------------------------------DBA_HIST_COLORED_SQL Marked SQLs for snapshotsDBA_HIST_SQLBIND SQL Bind InformationDBA_HIST_SQLCOMMAND_NAME Sql command typesDBA_HIST_SQLSTAT SQL Historical Statistics InformationDBA_HIST_SQLTEXT SQL TextDBA_HIST_SQL_BIND_METADATA SQL Bind Metadata InformationDBA_HIST_SQL_PLAN SQL Plan InformationDBA_HIST_SQL_SUMMARY Summary of SQL StatisticsDBA_HIST_SQL_WORKAREA_HSTGRM SQL Workarea Histogram Historyselect distinct stat_name from V$SYS_TIME_MODEL ;STAT_NAME----------------------------------------------------------------DB CPUbackground elapsed timehard parse elapsed timeRMAN cpu time (backup/restore)DB timehard parse (bind mismatch) elapsed timeinbound PL/SQL rpc elapsed timerepeated bind elapsed timebackground cpu timeconnection management call elapsed timefailed parse elapsed timehard parse (sharing criteria) elapsed timePL/SQL compilation elapsed timefailed parse (out of shared memory) elapsed timeJava execution elapsed timeparse time elapsedsequence load elapsed timesql execute elapsed timePL/SQL execution elapsed time19 rows selected.
• #12: OEM performance pageAvailability – tools repository is down or the laptop which has the tool installed crashed
• #13: OEM performance pageAvailability – tools repository is down or the laptop which has the tool installed crashed
• #17: better explanation for using begin_interval_time one snapshot before
• #19: 11g has SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION to further drilldown an SQL performance issue
• #21: PIO - number of times the db went to disk to retrieve the dataPotential solutions:Increase memory- upgrade disk susbsytemredudce PIO
• #22: Standard approach pre-10g - trace
• #24: 92% in just 3 steps
• #25: We also see that ASH data confirms AWR that I/O is the bottleneck (99% AWR vs 98% ASH)
• #27: CBO trace (event 10053) – lots of work, option of last resortDo we have bad stats - E-Rows vs A-Rows (statistics_level=all,gather_plan_statistics, SQL trace (event 10046)Usually bad stats =>CBO underestimates the cardinality/timeIn our case it overestimates.optimizer settings => CBO trace
• #28: PIO - number of times the db went to disk to retrieve the data
• #29: optimizer_index_cost_adj = 10 (default is 100). index access cost = 10% of the normal cost.
• #30: CBO trace (event 10053) – lots of work, option of last resortDo we have bad stats - E-Rows vs A-Rows (statistics_level=all,gather_plan_statistics, SQL trace (event 10046)Usually bad stats =>CBO underestimates the cardinality/timeIn our case it overestimates.optimizer settings => CBO trace
• #31: Not spilling to temp because no column indicating it
• #33: 99% on IO, but static PIO and large variance in time => large variance in PIO latencyDisk subsytem problem?Contact SAs? Check if query needs that much PIO first.
• #34: Plan not in memory anymoreExplain plan_line_id =2 when display_awr shows only 0 & 1. I will explain it later.
• #35: Plan not in memory anymoreExplain plan_line_id =2 when display_awr shows only 0 & 1. I will explain it later.
• #39: Historical bind variable not available through OEM (at least 10g)
• #40: Display_awr does not show the load table step, but display_cursor does.Plan hash values are the same!