This discussion is archived
8 Replies Latest reply: Jan 31, 2013 3:57 AM by 823085 RSS

How to generate AWR compare period report that compares no continues period

823085 Newbie
Currently Being Moderated
Hi Guys,

I am taking an AWR snapshot of the database at every hour for a week.

Now I would like to compare the snapshots collected during the day time (between 8am to 4am) to the snapshots collected during the night time (between 6pm to 6am) for not just a single day but the whole week.

Just want to ask how may I generate an AWR compare period report with such snapshots?

Do I have to use some special AWR baseline template or something?
  • 1. Re: How to generate AWR compare period report that compares no continues period
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    If you want to compare two different periods you can use the AWRDDRPT.sql script to do that. You'll have to specify which snapshots you want to use. So, you could compare 4am to 8am on Monday with 4am to 8am on Monday of the following week to see what changes there were.

    If you want to create a baseline, look at this package in the Oracle docs: DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE. You should keep a baseline of a known good/bad/interesting time that you want to make comparisons to and not lose the data.

    Here is a little more information about [url http://www.ora00600.com/articles/oracle-awr.html]Oracle AWR reports, with the more interesting parts about generating and reading reports towards the bottom of the page. The Oracle docs are also a good place to start.

    Hope that helps,
    Rob
  • 2. Re: How to generate AWR compare period report that compares no continues period
    723719 Newbie
    Currently Being Moderated
    you can look at dbtrends tool which enables you to generate AWR compare reports for any time periods and compare them in GUI and charts. see dbtrends here http://www.spviewer.com/dbtrends.html

    cheers
  • 3. Re: How to generate AWR compare period report that compares no continues period
    823085 Newbie
    Currently Being Moderated
    Hi Rob,

    Thank you so much for the advice. The problems here is that I have to summerize the performace data collected at 8am to 4pm each day of the week, then compare them with the data collected at 6pm to 6am each week.

    So it is 14 time periods, 7 for the day, 7 for the night. Then I have to average the data collected for the day to compare the average data collected for the night.

    I don't think this feature is natively provide from the AWR. So I will just create 14 baselines. Then summarize the data to a database, then write some queries to get the data processed.

    I wish oracle could implement this feature, because there are so many database system these days are for global users. So it is ideal if we have a feature that we could summarize & segment performance data according to timezones.
  • 4. Re: How to generate AWR compare period report that compares no continues period
    baskar.l Pro
    Currently Being Moderated
    Hi Eric,
    Thank you so much for the advice. The problems here is that I have to summerize the performace data collected at 8am to 4pm each day of the week, then compare them with the data collected at 6pm to 6am each week.
    
    So it is 14 time periods, 7 for the day, 7 for the night. Then I have to average the data collected for the day to compare the average data collected for the night.
    
    I don't think this feature is natively provide from the AWR. So I will just create 14 baselines. Then summarize the data to a database, then write some queries to get the data processed.
    
    I wish oracle could implement this feature, because there are so many database system these days are for global users. So it is ideal if we have a feature that we could summarize & segment performance data according to timezones. 
     
    Can you please tell me what is the bussiness need of this comparsion ? After taking the snapshot of the periods how are you going to summarize it..

    thank you,
    baskar.l
  • 5. Re: How to generate AWR compare period report that compares no continues period
    823085 Newbie
    Currently Being Moderated
    Hi Baskar,

    The requirement is quite simple. We have a database system that is used by global users from different timezones. So we need to have a overview of the workload according to timezone. Because we can only compare of the AWR snapshots that are in conitnues time interval, so this is the diffculty here.

    I know it is a bit confusing so here is an exmaple.

    For 7 days, I take 2 baselines each day, the first baseline inlcude the performance data collected from 8am to 5pm. The second includes data from 6pm to 6am. So I will be having 14 baselines at the end of the week. See below:

    Baseline collected during day: day_baseline_1, day_baseline_2, day_baseline_3, day_baseline_4, day_baseline_5, day_baseline_6, day_baseline_7
    Baseline collected during night night_baseline_1, night_baseline_2, night_baseline_3, night_baseline_4, night_baseline_5, night_baseline_6, night_baseline_7

    I would like to summerize the data collected in "day_baseline_1 to day_baseline_7" then compare it against the summerized data from "night_baseline_1 to night_baseline_7".

    I don't think this can be achieved with Oracle native features. So I guess I have to develop my own queries to do the job.

    What I plan to do here is to run period compare report between "day_baseline_1" and "night_baseline_1", "day_baseline_2" and"night_baseline_2", etc. Then sum the data and calculate the average.
  • 6. Re: How to generate AWR compare period report that compares no continues period
    baskar.l Pro
    Currently Being Moderated
    Hi Eric,
    I would like to summerize the data collected in "day_baseline_1 to day_baseline_7" then compare it against the summerized data from "night_baseline_1 to night_baseline_7".
    Thanks Eric. Yes the above part of comparing the summarized data of day baselines to night baselines is huge. I dont know now about how it can be done. But i will do some search on the same.

    Baskar.l
  • 7. Re: How to generate AWR compare period report that compares no continues period
    JohnWatson Guru
    Currently Being Moderated
    Eric.Zhou wrote:
    For 7 days, I take 2 baselines each day, the first baseline inlcude the performance data collected from 8am to 5pm. The second includes data from 6pm to 6am. So I will be having 14 baselines at the end of the week. See below:

    Baseline collected during day: day_baseline_1, day_baseline_2, day_baseline_3, day_baseline_4, day_baseline_5, day_baseline_6, day_baseline_7
    Baseline collected during night night_baseline_1, night_baseline_2, night_baseline_3, night_baseline_4, night_baseline_5, night_baseline_6, night_baseline_7

    I would like to summerize the data collected in "day_baseline_1 to day_baseline_7" then compare it against the summerized data from "night_baseline_1 to night_baseline_7".
    A step in this direction would be to export the AWR (there is a script awrextr.sql that may help with this) then drop the snapshots for the daytime baselines. You can then generate a report covering the whole week of nights. Import the AWR (there is script for this, too),drop the nighttime baselines, and generate a report for the week of days. You will have to compare them by hand, but you will have the full week in each report.
    Any good?
  • 8. Re: How to generate AWR compare period report that compares no continues period
    823085 Newbie
    Currently Being Moderated
    I think this is good, too.

    Accually we have a new plan that we are going to collect only the day time statistics at week 1, then collect only the night time statistics at week 2, then compare. I think this will also work.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points