{"id":1513,"date":"2009-07-30T12:00:00","date_gmt":"2009-07-30T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1513"},"modified":"2009-07-30T12:00:00","modified_gmt":"2009-07-30T12:00:00","slug":"awr-differences-report","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2009\/07\/30\/awr-differences-report\/","title":{"rendered":"AWR Differences Report"},"content":{"rendered":"<p><em>Some features in this post require a Diagnostics Pack license.<\/em><\/p>\n<p>This morning I had an opportunity to use one of my favourite AWR tools, the differences report. Our system has a fairly involved overnight batch schedule consisting of multiple concurrent job streams that starts at 02:00 and usually completes at about 5:40. This morning it finished at 6:40 so I needed to work out what had gone wrong. (Better still, because this wasn&#8217;t a problem with specific SQL statements, I can blog about it without using any system-specific information that needs to be obscured and that doesn&#8217;t happen too often.)<\/p>\n<p>Because I don&#8217;t have access to the server to run $ORACLE_HOME\/rdbms\/admin\/awrddrpt.sql, I used a SQL-only approach. <\/p>\n<p>First find out the snapshots I&#8217;m interested in. <\/p>\n<pre>select snap_id, end_interval_time \nfrom dba_hist_snapshot \nwhere end_interval_time &gt; trunc(sysdate-1) \norder by snap_id; \n\n8945\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 12:00:18.555 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8946\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 1:00:30.647 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n<strong>8947<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<strong> 28\/07\/2009 2:00:42.740 AM<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8948\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 3:00:55.045 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8949\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 4:00:07.050 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8950\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 5:00:19.198 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n<strong>8951<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>28\/07\/2009 6:00:31.596 AM<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8952\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 7:00:43.751 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8953\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 8:00:55.820 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8954\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 9:00:07.679 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8955\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 10:00:19.891 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8956\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 11:00:31.994 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8957\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 12:00:44.094 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8958\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 1:00:56.239 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8959\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 2:00:08.123 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8960\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 3:00:20.204 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8961\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 4:00:32.556 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8962\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 5:00:44.682 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8963\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 6:00:56.788 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8964\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 7:00:08.679 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8965\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 8:00:20.759 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8966\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 9:00:32.851 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8967\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 10:00:45.004 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8968\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28\/07\/2009 11:00:57.713 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8969\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29\/07\/2009 12:00:09.744 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8970\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29\/07\/2009 1:00:21.840 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n<strong>8971<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>29\/07\/2009 2:00:33.939 AM\u00a0\u00a0<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8972\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29\/07\/2009 3:00:46.268 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8973\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29\/07\/2009 4:00:58.378 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8974\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29\/07\/2009 5:00:10.702 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n<strong>8975<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>29\/07\/2009 6:00:23.183 AM\u00a0\u00a0\u00a0\u00a0\u00a0<\/strong>\u00a0\u00a0\u00a0\u00a0 \u00a0\n8976\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29\/07\/2009 7:00:35.604 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n8977\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29\/07\/2009 8:00:48.040 AM\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/pre>\n<p>Now that I have those snapshot ids, I can generate the AWR differences report, passing in the DBID, Instance Number, Begin and End Snapshot IDs for the first period and then the second period. <\/p>\n<pre>select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(4034329550,1,8947,8951,\n                                                                  4034329550,1,8971,8975)); <\/pre>\n<p>The report shows information for both periods, layed out for easy comparison. What immediately jumped out at me was the much higher DB Time over the first period than the second, which is probably more easily expressed as Average Active Sessions over the period. (Some people might say <a href=\"http:\/\/www.perfvision.com\/docs\/JB_AAS.pdf\">AAS is the magic metric<\/a>)<\/p>\n<p><!-- s9ymdb:242 --><img loading=\"lazy\" decoding=\"async\" alt=\"\" class=\"serendipity_image_center\" height=\"131\" src=\"\/serendipity\/uploads\/xawrdd1.png.pagespeed.ic.alLLAYfLaI.png\" style=\"border: 0px none ; padding-right: 5px; padding-left: 5px\" width=\"585\"\/><\/p>\n<p>Yes, this is over a long 4 hour period but I have the benefit of knowing that this is the same batch process that runs every night and it&#8217;s performance and workload profile is more or less the same night after night. <em>There&#8217;s still no substitute for knowing your systems, after all.<\/em> So to have over 4 times the number of sessions active, averaged over a long period, is a sure sign that something weird is going on and I had an immediate suspicion about what it might be.<\/p>\n<p>Let&#8217;s look at the Top 5 Timed Events section. <\/p>\n<p><!-- s9ymdb:243 --><img loading=\"lazy\" decoding=\"async\" alt=\"\" class=\"serendipity_image_center\" height=\"200\" src=\"\/serendipity\/uploads\/awrdd2.png\" style=\"border: 0px none ; padding-right: 5px; padding-left: 5px\" width=\"640\"\/><\/p>\n<p>Yes, as I suspected the first period with many more active sessions on average is showing a lot of time spent on the parallel execution-related &#8220;PX Deq Credit: send blkd&#8217; event which doesn&#8217;t show up at all in the second period. In fact, that&#8217;s why it has an asterisk next to it in the first period numbers, to show it&#8217;s an event which doesn&#8217;t appear in the top 5 over the second period. That&#8217;s what I love about this report. It takes the particular strength of AWR\/Statspack &#8211; comparing &#8216;good&#8217; and &#8216;bad&#8217; periods and lays it out in readable format. No more sitting there at a desk with print-outs of two Statspack reports, poring over the details, or flicking between two vi sessions paging through the results. Those who&#8217;ve done that will know precisely what I mean &#128521;<\/p>\n<p>We have 4 times as many sessions active in the first period and there&#8217;s a lot of parallel wait time that doesn&#8217;t appear in the second period so I suspect that PX wasn&#8217;t being used properly in the second period. I can check various statistics for that. This is a small section of the Instance Activity section of the report.<\/p>\n<p><!-- s9ymdb:244 --><img loading=\"lazy\" decoding=\"async\" alt=\"\" class=\"serendipity_image_center\" height=\"138\" src=\"\/serendipity\/uploads\/awrdd3.png\" style=\"border: 0px none ; padding-right: 5px; padding-left: 5px\" width=\"578\"\/><\/p>\n<p>Yes, every parallel operation in the second period was downgraded to serial. You can see that the use of parallelism isn&#8217;t very successful in the first &#8216;good&#8217; period either, but that&#8217;s a whole other story and possibly a blog post or two. That&#8217;s where the big difference in DB Time has come from though &#8211; lots of parallel slave activity that wasn&#8217;t there last night.<\/p>\n<p>I think this is an interesting example because this particular report is showing a &#8216;good&#8217; period with higher DB Time than a &#8216;bad&#8217; period, which isn&#8217;t what you&#8217;d normally expect to see &#8211; that&#8217;s a side effect of looking at consolidated DB time &#8211; there&#8217;s lots of additional wait time from the various slaves but they&#8217;re actually helping push more work through the system, so they&#8217;re a good thing. To put it another way, the instance was much busier during the good period, that&#8217;s why it was able to push more work through and complete more quickly. Even though that might make sense, if we look at the graphical representation in OEM, here&#8217;s the good period<\/p>\n<p><!-- s9ymdb:245 --><img loading=\"lazy\" decoding=\"async\" alt=\"\" class=\"serendipity_image_center\" height=\"200\" src=\"\/serendipity\/uploads\/awrdd4.jpg\" style=\"border: 0px none ; padding-right: 5px; padding-left: 5px\" width=\"640\"\/><\/p>\n<p>&#8230; and here&#8217;s the bad <\/p>\n<p><!-- s9ymdb:246 --><img loading=\"lazy\" decoding=\"async\" alt=\"\" class=\"serendipity_image_center\" height=\"200\" src=\"\/serendipity\/uploads\/awrdd5.jpg\" style=\"border: 0px none ; padding-right: 5px; padding-left: 5px\" width=\"640\"\/><\/p>\n<p>I&#8217;m not convinced that people wouldn&#8217;t look at those two pictures and think &#8216;Mmmmm, things look much worse in the first period&#8217;. Oh, I should also point out that that bump at 22:00 each day is the auto stats gather job which ran for the entire maintenance window on the first night.<\/p>\n<p>The root cause was a little more difficult to find out, but I got there in the end. TOAD has a nasty habit of keeping hold of PX slaves long after a parallel query has finished. (Well, that&#8217;s my experience, using version 9.6.1.1, post a comment if you know different.) Unfortunately we have parallel DEGREE settings of 8 across our logging and monitoring tables (we shouldn&#8217;t) and so if I query those using TOAD and forget to disable parallel query at the session level, Oracle decides to run them with parallel plans. I had one such query which used 16 slaves &#8211; our parallel_max_servers setting &#8211; which meant that there were no slaves for any of our batch processes! Result &#8211; batch was delayed by an hour.<\/p>\n<p>The punchline, though, is that although the ETL process sets DEGREE to 1 on most of the tables on completion, it doesn&#8217;t set the indexes to 1 (which is an oversight). The end result is that our users use parallel query during the day for their reports and (this is definitely another blog post) it makes them run <strong><em>more slowly<\/em><\/strong>. So now that I have all slaves assigned to my query, it&#8217;s helped all the user queries run more quickly, so I&#8217;m sure they&#8217;re happy. In fact, maybe I should just run one of those queries each morning from TOAD &#128521;<\/p>\n<p>The AWR differences report is very handy sometimes and I know it can&#8217;t be that well known because when I had to request one prior to having the privileges myself, the DBA denied it&#8217;s existence! <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Some features in this post require a Diagnostics Pack license. This morning I had an opportunity to use one of my favourite AWR tools, the differences report. Our system has a fairly involved overnight batch schedule consisting of multiple concurrent job streams that starts at 02:00 and usually completes at about 5:40. This morning it&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2009\/07\/30\/awr-differences-report\/\">Continue reading <span class=\"screen-reader-text\">AWR Differences Report<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1513","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1719,"url":"http:\/\/orcldoug.com\/blog\/2014\/07\/24\/recurring-conversations-awr-intervals-part-2\/","url_meta":{"origin":1513,"position":0},"title":"Recurring Conversations: AWR Intervals (Part 2)","date":"July 24, 2014","format":false,"excerpt":"(Reminder, just in case we still need it, that the use of features in this post require Diagnostics Pack license.) Damn me for taking so long to write blog posts these days. By the time I get around to them, certain very knowledgeable people have commented on part 1 and\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1713,"url":"http:\/\/orcldoug.com\/blog\/2014\/07\/07\/recurring-conversations-awr-intervals-part-1\/","url_meta":{"origin":1513,"position":1},"title":"Recurring Conversations: AWR Intervals (Part 1)","date":"July 7, 2014","format":false,"excerpt":"I've seen plenty of blog posts and discussions over the years about the need to increase the default AWR retention period beyond the default value of 8 days. Experienced Oracle folk understand how useful it is to have a longer history of performance metrics to cover an entire workload period\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1403,"url":"http:\/\/orcldoug.com\/blog\/2008\/04\/17\/moving-awr-data\/","url_meta":{"origin":1513,"position":2},"title":"Moving AWR data","date":"April 17, 2008","format":false,"excerpt":"Note - features in this post require the Diagnostics Pack license[I originally had the first section at the end of the blog post, but then realised I might as well get the bad news out of the way to save you wasting your time if you're not interested]A small section\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1289,"url":"http:\/\/orcldoug.com\/blog\/2007\/07\/04\/awr-licensing\/","url_meta":{"origin":1513,"position":3},"title":"AWR Licensing","date":"July 4, 2007","format":false,"excerpt":"I thought I'd wait for a few days to see how the open letter to Larry Ellison on the subject of AWR licencing panned out.I love AWR, ASH and I even have an increasing, slightly grudging and cynical respect for ADDM. In fact, I'm in the course of putting together\u2026","rel":"","context":"With 15 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1504,"url":"http:\/\/orcldoug.com\/blog\/2009\/06\/28\/i-love-addm\/","url_meta":{"origin":1513,"position":4},"title":"I Love ADDM","date":"June 28, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.I'll get back to adaptive thresholds at some point but something's been bugging me.I'm not just trying to be controversial but I've a feeling I'm about to be, given that ADDM is the probably the most infamous of the 10g 'Automatic\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1618,"url":"http:\/\/orcldoug.com\/blog\/2010\/11\/21\/conference-update\/","url_meta":{"origin":1513,"position":5},"title":"Conference Update","date":"November 21, 2010","format":false,"excerpt":"The week before the UKOUG conference (or more accurately - UKOUG Conference Series Technology and E-Business Suite 2010. Maybe I'll just stick to UKOUG) and whilst it's an unusual year in as much as I'm not presenting some traditions hold true. I have a stinking cold - my third in\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1513"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1513\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1513"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}