Performance Tuning :: Oracle Documentation On Details Of AWR Report
Jun 4, 2012
A coworker of mine asked if there was any documentation from Oracle that listed all of the parts of the AWR report and what each meant. I was taken back because I don't think there is. There are third party books that talk about AWR reports and their predecessor Statspack reports.
Oracle has some notes on their support site about reading an AWR or Statspack report. All I found in the official documentation was some basic information about how to run an AWR report and an overview of what it was. It would be nice to have some sort of documentation that lists out each section and explains the units and purpose.
I am trying to generate AWR report for database observation. But I am not getting any snapshot listed there. below is the output of my awrrpt.sql
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1140984076 AFCCV 1 afccv
Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 1140984076 1 AFCCV afccv SERVICEDB1
Using 1140984076 for database Id Using 1 for instance number
Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots.
Enter value for num_days: 3
Listing the last 3 days of Completed Snapshots
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:
elapsed time = time spent on waits + time CPU was used
Total time during snaps = Elapsed time + (may be) time waited for CPU...In AWR is it possible to draw such equation? I can see that the AWR report has following elements
1) End Snap time - Begin Snap time 2) DB time - as mentioned at the top of AWR report 3) DB CPU - in "Top 5 Timed Foreground Events" (I assume this is 'CPU used by sesson timing' in statspack) 4) Total of time for all Statistics in "Time Model Statistics" 5) BUSY_TIME + IDLE_TIME - "Operating System Statistics"
Time between 2 snapshots? or what else? Also for which seconds to multiply to 'DB Time(s)' per second and 'DB CPU(s)' per second in Load Profile to get the db time and CPU time?
I would like to generate HTML awr report and save it to my local machine.
After running $ORACLE_HOME/rdbms/admin/awrrpt.sql in sqlplus and specifying HTML in (Enter value for report_type: HTML) i hvae get numerous html code in my sqlplus prompt.. I want to save the html report in local machine and open it by double clicking on it.. it will be opened in a browser..
I was trying to generate AWR report, but the report which got generated consist most of the sections without data. Later i came to know that AWR report is not fully supported in 11g? Is that true?
I understand that when data is read from the disk, I/O is done..And When computations are done then CPU is used..Then where the following equation fits?
Is it possible to generate AWR report for the duration of 5 min? As we know that snapshots are generated for every 1 hour, which we specified in parameter.
By changing the parameter to 2 min, what could be the impact on database?
I ran an AWR report. The database looks fine, but a data load that loaded 1 Million rows an hour is now doing 500K per hour.
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time DB CPU 224 80.70 Other 2,668 0 28 10 9.99 System I/O 4,753 0 9 2 3.23 Administrative 1 0 6 5543 2.00 Commit 357 0 4 11 1.46
[code]....
The network value for wait: 630,601. What does this mean? Anything I should look at? When it was 1million per hour, the value was 4,563,000.
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class DB CPU 224 80.70 unspecified wait event 2,666 28 10 9.99 Other control file sequential read 4,753 9 2 3.23 System I/O switch logfile command 1 6 5543 2.00 Administrative log file sync 357 4 11 1.46 Commit
We are using 11.2.0.3.0 on solaris 10 facing slow performance, following are the Wait Events in AWR report, Also if any specific document to analyze AWR report and to pin point the performance bottleneck.
Foreground Wait Events ********************** Avg %Time Total Wait wait Waits % DB Event Waits -outs Time (s) (ms) /txn time -------------------------- ------------ ----- ---------- ------- -------- ------ direct path read 308,729 0 21,191 69 58.0 39.5 db file sequential read 208,754 0 3,742 18 39.2 7.0 cursor: pin S 19,541,899 0 2,561 0 3,668.5 4.8 [code]....
want to download full oracle 10gR2 documentation. I also navigated on [URL] but this link allowing me to download pdf one by one but I want to download all pdf's in single shot.
we have a admin wizard where in there are 850 officials whose details are like
emp id date of birth date of joining basic pay photo etc........
i have the photos of the officials out of the data base.i have created a report to generate the details of the officials. what i would like to have is to get the photo of each official from the file which is not in the data base.
i have the command in pre_form read_image_file('path where the photos are','msg.mcn');
this gives the same photo to all the officials. how do i be able to link the photos to their emp id and get it.
I have installed database in one server. I would like to enable AWR into it. Statistics_level is set to Typical. While running the below script to enable the AWR, its gives error -
SQL> exec dbms_scheduler.enable('GATHER_STATS_JOBS'); BEGIN dbms_scheduler.enable('GATHER_STATS_JOBS'); END;
* ERROR at line 1: ORA-27476: "SYS.GATHER_STATS_JOBS" does not exist ORA-06512: at "SYS.DBMS_ISCHED", line 4343 ORA-06512: at "SYS.DBMS_SCHEDULER", line 2802 ORA-06512: at line 1
I am running an Oracle 10.2.0.3 on Solaris 5.9 OS. Front end appplication is PeopleSoft v8.8.From my AWR report I have found below SQL which needs to be tuned:
Looking to understand the difference between instance tuning and database tuning.
What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.
However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?
Recently i have been working analyzing Oracle Row Chaining and Migration in the database. Is their any way to track the chaining & migration of rows as part of database health checkup.
Else we have analyze the table for detecting row Chaining and Migration.
We are re-designing our App and we have a critical question, what's the best way (in terms of performance) of using TIFF images (about 20K size) with Oracle.
Currently we have a Windows shared file server and we create the tiff images there under a huge directory structure (like /images/ddddmmyy/aa/bb/001, then /images/ddddmmyy/aa/bb/002, etc, etc). Our database is usually in LINUX version 10, 11 or 12. We create about 200,000 images per day, keep them for 60 days and then remove that structure.
Our Web app (developed with .NET) reads those images just to display them on a Web Session (IE).As you can see, what we are doing now works fine. But network sometimes is an issue and also it's hard to keep synchronization with our DR server, backups, etc.
Are we taking the correct approach? It would be better to have the images in CLOB or BLOBS for better performance? If so, As I mentioned, performance is the KEY FACTOR and the most important item to consider in this design.
