A couple of days ago I mentioned as a passing comment that you could take AWR snapshots at the start and end of the overnight batch processing so that if you ever had to answer the question: “Why did last night’s batch overrun by two hours?” you had the option of creating and comparing the AWR report from the latest batch run with the report from a previous batch run (perhaps the corresponding night the previous week) and check for any significant differences. Moreover, Oracle supplies you with the code to compare and report such differences from 10.2 (at least) using the script $ORACLE_HOME/rdbms/admin/awrddrpt.sql
The thing I didn’t mention at the time was how to take a snapshot on demand. It’s very easy if you have the appropriate execute privilege on package dbms_workload_repository.
execute dbms_workload_repository.create_snapshot('TYPICAL');
The single input parameter can be ‘TYPICAL’ (the default) or ‘ALL’.
I keep this one liner in a little script called awr_launch.sql – because I can never remember the exact name of the package without looking it up. (And sometimes I run it with sql_trace enabled so that I can see how much work goes into a snapshot as we change versions, features, configuration, workload and platforms.)
Oracle Scratchpad 
Jonathan, do you mean *just* in 11.2? That script has been around much longer I think – 10.2.x ?
Comment by Doug Burns — February 24, 2011 @ 7:18 am GMT Feb 24,2011 |
Doug,
Temporary brain glitch – there are other bits that appeared in 11.2 but you’re right that this one has been in since (at least) 10.2. I’ve corrected the note.
Comment by Jonathan Lewis — February 24, 2011 @ 9:00 am GMT Feb 24,2011 |
Hi Jonathan,
With your permission, I’ll add a little warning for those that will use only one AWR report that covers a long overnight batch processing.
In the ‘SQL ordered by’ sections, AWR shows only the statements that are present in the ending snapshot. And a snapshot gets SQL statements from the shared pool (v$sql).
So if we have a 3 hours batch process where an expensive running query was executed only during the first hour, then there is a chance that it has left the shared pool before the end. In that case, we will not see that expensive query within our 3 hours report.
Fortunately, the ‘Time Model Statistics’ section shows the ‘sql execute elapsed time’ that covers the whole duration. And the ‘SQL ordered by Elapsed Time’ section shows the DB time for each statement.
Then it is always a good idea to check that the sum of SQL ‘DB time’ are covering a relevant part of the ‘sql execute elapsed time’.
If not, then we know that we see only a partial picture, and that an intermediate snapshot should have been useful.
Regards,
Franck.
Comment by Franck Pachot — February 24, 2011 @ 2:11 pm GMT Feb 24,2011 |
Franck,
Thanks for that observation – you’re absolutely right.
I should have made it clear that the idea is to run the “start/end” snapshots in addition to the typical hourly snapshots.
Comment by Jonathan Lewis — February 24, 2011 @ 6:32 pm GMT Feb 24,2011 |
[…] Snapshots: https://jonathanlewis.wordpress.com/2011/02/25/awr-snapshots/ Categories: Oracle Tags: AWR, Oracle, Oracle Database, Performance, SQL LikeBe the first to […]
Pingback by AWR Reports and Snapshots – Two Very Useful Posts by Jonathan Lewis « Ralph Cavalier's Oracle Blog — March 7, 2011 @ 3:19 pm GMT Mar 7,2011 |
[…] couple of weeks ago I listed a number of scripts from 11.2.0.2 relating to AWR reports – it seems only sensible to publish a corresponding […]
Pingback by Statspack Reports « Oracle Scratchpad — March 10, 2011 @ 2:01 pm GMT Mar 10,2011 |
It will be cool , if there is a way to generate a comparison report following the db restores. For example, we run stress tests and then save the AWR reports , restore the db and then again save the AWR reports for manual comparison. Is it possible to take an export of AWR data and then import it in after the db restore and then generate this comparison report using $ORACLE_HOME/rdbms/admin/awrddrpt.sql?
Thanks
Suman
Comment by Suman Kumar — May 27, 2011 @ 5:57 pm BST May 27,2011 |
Suman,
I published a list of the various AWR scripts a little while ago – this includes options to export and import repositories.
I can see that there would be probably be some difficulty with two repositories having the same database id, but possibly you could play around with those scripts to see if they could get you what you want.
Comment by Jonathan Lewis — June 2, 2011 @ 8:25 pm BST Jun 2,2011 |