Performance Tuning with ASH and AWR Data

Kellyn PotVin on 31 August 2012 with 19 comments

This video and set of resources is a follow up to the webinar broadcast by Red Gate on 30 August 2012.

The video above is accompanied by the following downloadable resources:

Summary

Since their introduction with Oracle 10g, AWR and ASH data has offered the Database professional enhanced performance reporting capabilities that previously were only gleaned via statspack and reporting queries.

In this webinar, Kellyn Pot’Vin, Senior Technical Consultant at Enkitec, will take you through the requirements, reporting basics and differences of AWR and ASH. You will learn:

reasons to use both reports.
a firm understanding of each report and its format.
knowledge of the differing versions of the AWR report and when best utilized.
the power of ASH Reporting in real-time trouble-shooting.
ability to utilize the reports at the command line and from Enterprise Manager.

A live Q&A session with Kellyn Pot’Vin follows the presentation.

Kellyn PotVin

Kellyn Pot’Vin, (pronounced Poet-Vaughn) is an Oracle ACE and Senior Technical Consultant at Enkitec, managing Oracle, SQL Server and MySQL. She specializes in performance tuning, working almost exclusively on mult-TB size databases, including Exadata and databases involving solid state disk configurations. She is the director of the Training Days 2013 conference for RMOUG and is heavily involved in this incredible regional Oracle group. She has spoken at KSCOPE, MOW, various SQLPass groups, numerous regional user groups and will soon be at the E-4 Expo and presenting at two symposiums at Oracle Open World 2012. She in a co-author on two books from Apress, one, “SQL Server Experts” is in post-production and a second has begun on Enterprise Manager 12c, both slotted to be available by the end of the year.

Comments
Trackbacks

19 Comments

Kellyn PotVin

31/08/2012 · Reply

Answers to a question from yesterday's session:
What is DBTime?
DB Time is approximated by multiplying sampling interval by the total count of the samples involved.
Math is:
T * (rows in ASH) = DBTime
Vakhtang Kvantaliani

31/08/2012 · Reply

Any security risk with AWR report content? Some vendors ussually ask AWR for HW/SW evaluation
Can we get AWR report without detailed SQL texts in it. Only sql IDs?
Kellyn PotVin

31/08/2012 · Reply

AWR reports do contain the first few lines of SQL statements in the reports. If a vendor requests an AWR report for this purpose and there is questions regarding this data showing, you have two options- You can "blackout" or erase the data in the test report or you can use queries directly against the tables, similar to the ones I have in the presentation to provide them the data based solely on SQL_ID.
oracleman consulting

31/08/2012 · Reply

great webinar
great site
great scripts

thanks for sharing with the rest of us
Pavan Kumar

02/09/2012 · Reply

Hi Kelly,

I was watching the video of the presentation, perhaps the PPT or slides are not scrolled down during the presentation. Is is possible to share across the ppt contents of seminar.

Thanks in Advance,
- All Things Oracle
  
  03/09/2012 · Reply
  
  Hi Pavan,
  
  The presentation slides can be downloaded in .pdf format above.
  
  Thanks,
  
  James
Todd Laubach

04/09/2012 · Reply

Great presentation, I appreciate you sharing. Any chance of providing a zip file of scripts? Our company does not allow access to sites like google docs as they are considered online backup or storage and a pathway for sensitive data to be outside our walls.

Thanks,
Todd
Kellyn PotVin

05/09/2012 · Reply

Yes, I would be glad to zip them up and email them to you. Email dbakevlar @ gmail and I will be glad to send a zip file over to you.
~Kellyn
Xavier

11/09/2012 · Reply

It's very useful for me, now that I'm just "tuning" to deliver an Oracle application for acceptance testing.

I'm infinitely grateful to you.
-Xavier
Narsimulu

20/10/2012 · Reply

Hi Kelly,

I missed to attend the Webinar, Is there any way i can download the webinar. is can see the recorded version but am not sure how to download.

-Narsimulu.
- All Things Oracle
  
  24/10/2012 · Reply
  
  Hi Narismulu,
  
  If you move your mouse pointer over the video you will see that some controls will pop up including a download icon in the top right corner.
  
  James
  - Narsimulu
    
    13/12/2012 · Reply
    
    Hi James,
    
    I tried to download in similar way, but iam able to download only first 2 minutes. Not sure what is the problem.
    
    -Narsimulu.
    - All Things Oracle
      
      13/12/2012 · Reply
      
      Hi Narismulu,
      
      I'm not sure why this happens, It seems that you can only download the video after it has played fully in the light box. I hope watching it online is okay for you.
      
      James
Ashish

11/12/2012 · Reply

Nice job !!! I have a query please help.

Which sql's/PLSQL are included in AWR and which are not ? Like i ran few procedures and sql's in 1 hour and took AWR report for same time but i see half of conents of sql/plsql not all executions.

I need execution time & detail for other sql's/proc also which are not shown in AWR report. How can i get those ?

Thanks in Advance
Kellyn Pot'Vin

12/12/2012 · Reply

Hi Ashish,
I would take the SQL_ID's, note the snapshot that they ran within and run the following:
$ORACLE_HOME/rdbms/admin/awrsqrpt.sql;
This script will ask for type of report, html or text, which is up to you, I prefer text since I commonly do not move the file from the server and do not have a browser for html viewing.
It will ask you for the beginning and ending snapshot, (along with days of snapshots you want to view at first if you need to get the actual snapshot ID for this report... )
Then it will ask you for the specific SQL_ID, enter it for the SQL you are searching for.
Name the report, I often choose to name it, AWR_<end_snapshot>_<sql_id>.txt

This report should give you the data on the sql you are searching for, including specific wait info and execution plans for any and all hash values involved.
Thank you,
Kellyn
Manzoor

18/01/2013 · Reply

Great presentation Kellyn.
Vaibhav

16/04/2013 · Reply

Dear All,

I have 2 awr reports in RAC environment.In RAC have 2 nodes.First awr reports have 2 nodes are up and take snapshot and second awr reports have 1 node is up and 2 node is down and take snapshot.Kindly give me suggestion how to compare and analysis performance of database using awr reports like cpu,I/O....and what parameters weare check in both reports to give a conclusion of both reports.

thanks in advance.
Vaibhav Jain
9953268495
Kellyn Pot'Vin

17/04/2013 · Reply

I'm not sure what you goal is of the two? Is it to see how the environment performance is when only one node is available? If so, I would look at elapsed time changes in the statements that are available in both reports, taking the number of executions into consideration and any other differing factors between the two reports.
Alessandro

25/04/2013 · Reply

I watched the video and I would like to know if you has some queries to show the top 3
sql_id responsible for high dbtime.
Example:
"SNAP_ID"           "DATE"              "DBTIME"
---------------------------------------------------------------
8010                24/april 10:00      1,200,00
8011                24/april 11:00      3,500,00
8012                24/april 12:00        600,00
"SNAP_ID"           "DATE"                "SQL_ID"           "DBTIME"            "DBTIME %"
--------------------------------------------------------------------------------------------------------
8010                24/april 10:00        didfasfdas           800,00               20%
8010                24/april 10:00        dfasfe3334           200,00               10%
8010                24/april 10:00        df44545454            50,00                5%
….
8011                24/april 11:00        didfadfef5           900,00               60%
8011                24/april 11:00        dfasf565gg           300,00               30%
8011                24/april 11:00        df44547879           350,00               25%

Thank you very much.