Multiple AWR Reports
Pranav, March 26, 2012 - 2:36 pm UTC
Hi Tom,
Could you please let me know if there is a way to generate AWR reports automatically for every hour during a period of time? For ex., we have hourly snaps and let's say we want to generate the awr reports from 8AM-5PM. Instead of running awrrpt.sql for each snapshot can we pull the awr reports between 8AM-5PM at once? We don't have access to database server. So creating a directory and generating the AWR reports is not an option. We are connecting through sqlplus client(from Windows).
Thank you.
March 26, 2012 - 3:29 pm UTC
sure, just use
ops$tkyte%ORA11GR2> select *
2 from table( dbms_workload_repository.awr_report_text( l_dbid => 52489347, l_inst_num => 1, l_bid => 4574, l_eid => 4575 ) )
3 /
OUTPUT
--------------------------------------------------------------------------------
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORA11GR2 52489347 ora11gr2 1 17-Mar-12 17:52 11.2.0.2.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
localhost.locald Linux IA (32-bit) 4 4 2 .99
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 4574 26-Mar-12 12:00:36 23 1.5
End Snap: 4575 26-Mar-12 13:00:18 23 1.5
Elapsed: 59.71 (mins)
DB Time: 0.05 (mins)
.........
you can select the reports right out in text, html, whatever
check out:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_workload_repos.htm#ARPLS093
AWR report
Kunal, January 21, 2014 - 8:00 am UTC
Hello Sir,
I have been working as a plsql developer for couple of years in a company.Whenever we need to have performance bottlenecks for a perticular time ,say 10-12 EST ,we request our DBA(client side) to generate AWR report for that time frame.After getting the report we start analysising.
My question is
1) How long it would take to generate AWR report ? is it the hour difference for which the report has to b generated?
2)What are the important wait events to be focus & their interpretation in short.
If any URL could be mentioned having all the detail ,it would be highly solicited.
AWR Report and Dynamic and System Views
A reader, April 26, 2017 - 4:07 pm UTC
Oracle Enterprise manager 11g provide a GUI Report for Automatic workload repository but i want to know which dynamic and oracle system level view used for AWR Report. can user which have a sysdba role to see all tables used in AWR Report?
Like v$sql, v$sqlstat and so many..
April 27, 2017 - 6:32 am UTC
There's a *lot* of tables that may or may not get used for AWR.
Do a search on views that start with DBA_HIST_... to get an idea.
But yes, an authorised account can see them all - there's nothing "hidden" or "secret" about them.
AWR report Analysis
Dhananjay Sondur, October 26, 2017 - 3:29 pm UTC
Hi Tom,
How we can analyze activity by looking Load Profile in AWR report?
Load Profile
Per Second Per Transaction Per Exec Per Call
DB Time(s): 45.3 1.4 0.03 0.15
DB CPU(s): 18.2 0.6 0.01 0.06
Redo size: 241,679.7 7,450.3
Logical reads: 212,065.6 6,537.4
Block changes: 2,290.9 70.6
Physical reads: 3,016.3 93.0
Physical writes: 217.9 6.7
User calls: 308.8 9.5
Parses: 70.3 2.2
Hard parses: 23.6 0.7
W/A MB processed: 2.1 0.1
Logons: 0.4 0.0
Executes: 1,451.3 44.7
Rollbacks: 0.0 0.0
Transactions: 32.4
Thanks,
Dhananjay Sondur
October 27, 2017 - 3:36 am UTC
You could start by formatting it so we can read it.
AWR report Analysis
Dhananjay Sondur, October 27, 2017 - 5:36 am UTC
Hi Tom,
Thanks a lot for the prompt response.
please find the formatted output.i would like to request how we can conclude by looking Load profile. And can you please share the links so that we can read and find the AWR report analysis techniques.
Per Second Per Transaction Per Exec Per Call
DB Time(s): 45.3 1.4 0.03 0.15
DB CPU(s): 18.2 0.6 0.01 0.06
Redo size: 241,679.7 7,450.3
Logical reads: 212,065.6 6,537.4
Block changes: 2,290.9 70.6
Physical reads: 3,016.3 93.0
Physical writes: 217.9 6.7
User calls: 308.8 9.5
Parses: 70.3 2.2
Hard parses: 23.6 0.7
W/A MB processed: 2.1 0.1
Logons: 0.4 0.0
Executes: 1,451.3 44.7
Rollbacks: 0.0 0.0
Transactions: 32.4
Thanks,
Dhanjay
October 27, 2017 - 2:36 pm UTC
sigh...