{"id":311,"date":"2008-10-10T09:43:22","date_gmt":"2008-10-10T07:43:22","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=311"},"modified":"2008-10-10T09:43:22","modified_gmt":"2008-10-10T07:43:22","slug":"awr-analysis-for-io-reporting","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2008\/10\/10\/awr-analysis-for-io-reporting\/","title":{"rendered":"AWR Analysis for IO Reporting"},"content":{"rendered":"<p>Oracle gathers a wealth of information in the Automatic Workload Repository (AWR). We can query this data to get very important IO access time information.<\/p>\n<ul>\n<li>Create a working table because depending of the size of the AWR, the queries could take a long time and cause load on the database.<\/li>\n<\/ul>\n<blockquote><p><code>drop table mdecker.filestatistics;<br \/>\ncreate table mdecker.filestatistics as select t.snap_id, t.begin_interval_time, filename, file#, tsname, phyrds, singleblkrds, readtim, singleblkrdtim, phyblkrd from<br \/>\ndba_hist_filestatxs f ,<br \/>\ndba_hist_snapshot t<br \/>\nwhere<br \/>\nt.snap_id = f.snap_id and (<br \/>\nbegin_interval_time between to_date('05.11.2007 09:00','DD.MM.YYYY HH24:MI') and<br \/>\nto_date('09.11.2007 17:00','DD.MM.YYYY HH24:MI')<br \/>\nOR<br \/>\nbegin_interval_time between to_date('12.11.2007 09:00','DD.MM.YYYY HH24:MI') and<br \/>\nto_date('16.11.2007 17:00','DD.MM.YYYY HH24:MI') );<\/code><\/p><\/blockquote>\n<ul>\n<li>Create a summary table<\/li>\n<\/ul>\n<blockquote><p><code>create table mdecker.filestatistics_summary as<br \/>\nselect snap_id, begin_interval_time, sum(phyrds) phyrds, sum(singleblkrds) singleblkrds from mdecker.filestatistics<br \/>\ngroup by snap_id, begin_interval_time<br \/>\norder by snap_id<\/code><\/p><\/blockquote>\n<ul>\n<li>Query the summary table<\/li>\n<\/ul>\n<blockquote><p><code>select snap_id,<br \/>\ny.begin_interval_time,<br \/>\nphyrds as prev_phyrds,<br \/>\nlead(phyrds) over(order by snap_id) current_physrds,<br \/>\nlead(phyrds) over(order by snap_id) - phyrds,<br \/>\nbegin_interval_time as prev_interval_time,<br \/>\nlead(begin_interval_time) over(order by snap_id) current_time,<br \/>\nlead(begin_interval_time) over(order by snap_id) - begin_interval_time,<br \/>\nextract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time) as sec,<br \/>\nextract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time) as min,<br \/>\n(extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time)*60)+extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time),<br \/>\n(lead(phyrds) over(order by snap_id) - phyrds)\/<br \/>\n((extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time)*60)+extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time))<br \/>\nfrom filestatistics_summary y<\/code><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Oracle gathers a wealth of information in the Automatic Workload Repository (AWR). We can query this data to get very important IO access time information. Create a working table because depending of the size of the AWR, the queries could take a long time and cause load on the database. drop table mdecker.filestatistics; create table [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13,5,37],"tags":[],"class_list":["post-311","post","type-post","status-publish","format-standard","hentry","category-10g","category-oracle-database","category-sql-oracle-database-2"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/311","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=311"}],"version-history":[{"count":1,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/311\/revisions"}],"predecessor-version":[{"id":312,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/311\/revisions\/312"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=311"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}