Performance Tuning with AWR & ASH in #em12c

Oracle monitoring and performance tuning has grown over the years.  With every release of the Oracle database the monitoring capabilities that have been provided have evolved.  In Oracle11g, monitoring with statpack has given way to Automatic Workload Repository (AWR) and Active Session History (ASH) for near real time monitoring of items within the Oracle database.  Adding AWR and ASH to your performance tool kit can yield huge savings of time to tuning endeavors.

Note: AWR and ASH were also in Oracle10g, but not as robust as it currently is in Oracle11g.

Note: If you are interested in how to run AWR and ASH from the command line check out this post by a good friend of mine: here

Instead of focusing on the command line to run AWR and ASH, lets take a look at how these tools are used through Oracle Enterprise Manager 12c. 

Note: The AWR and ASH tools are covered under the Diagnostic and Tuning Management Packs for the database and OEM.  Before using these tools, make sure you have access to these management packs.

Automatic Workload Repository (AWR)

The Automatic Workload Repository (AWR) is used to automate database statistic gathering by collecting, processing, and maintaining performance statistics for database problem identification and self-tuning.  Part of the AWR is snapshots.  AWR Snapshots are gathered by default every hour and queried from memory.  Snapshots can be used to identify performance problems over time.

In order to use AWR, there are two initialization parameters that are relevant:

  • STATISTICS_LEVEL – set to TYPICAL (default) or AL, enables statistics gathering for AWR. setting it to BASIC will disable statistics gathering.
  • CONTROL_MANAGEMENT_PACK_ACCESS – set to DIAGNOSTIC+TUNING(default) or DIAGNOSTIC to enable diagnostic monitoring.  Setting to NONE will disable many database features including ADDM (not covered in this post).

Automatic Workload Repository (AWR) Report

With the AWR enabled for the database, an AWR report can be ran from within OEM.  While on a database home page within OEM, AWR report can be accessed from the Performance menu (Performance –> AWR –> AWR Report) (Image 1). 

Image 1:
image

Before you can run an AWR Report, OEM will ask you to log in to the database.  Like with anything in OEM, you can used Named Credentials or create a new one (Image 2). 

Image 2:
image

After logging in you will be asked if you want to use “By Baseline” or “By Snapshot”.  Most of the time, “By Snapshot” will be used (Image 3).  This step corresponds to the command line version of AWR Report when it asks what snapshots you would like to use.  Then click the Generate Report button to build the report.

Tip: Use the magnifine glass to lookup the snapshots.  This will open a separate window.

Image 3:
image

Once the report is generated; the report can be read in the OEM interface or saved to file for later reading or emailing to others.

Reading AWR Report

One thing to notice, the difference between the AWR Report from command line and OEM interface is that at the command line, text based report can be chosen.  When running the AWR Report from OEM, the only option is the report is automatically saved in HTML format.  Although there is a difference in report formats; the report is read the same way no matter what the format is.

The header of the report (Image 4) provides you with general information about the environment where there report was gathered from.  It also provides information about the snapshots used to generate the report and the time differences between the snapshots.

Image 4:image

The next section in the report is the Report Summary.  In the Report Summary, there are a few items in interest.  The first of which is the “Instance Efficiency Percentage” section (Image 5).  In this section, you can get a sense of how well the instance is managing it memory operations.  Ideally, the target should be 100%.

Note: The only time I’m concern about this area is if any of the values are below 90%. Often this is an indication that the SGA may be to small.

Image 5:
image

In the same section, I also look at the “Top 5 Time Foreground Event” section (Image 6).  This section of the report provides you with a list of the current wait events that are taking the most time waiting and what wait class they belong to.  At this time, I can see what I should be tuning the environment to prevent.  In the image provided, I can see that I have a wait dealing with high water mark contention (enq: HW – contention). 

From looking at these waits, it appears that I have an object that is possibly having a problem with extents be allocated.  What I need to do next is see if I can find the object that is causing the issue.

Image 6:
image

Since I know that I have a high water mark contention issue, this is telling me that I need to look at what objects are being written to in the database.  In the “Segments Statistics” section of the report, I want to look at the “Segments by Physical Writes” sub-section (Image 7).  From looking at this section, I can see that the problem is on a LOB object names SYS_LOB0000253792C00002$$.

Image 7:image

From looking at the AWR Report, I can quickly see the object that is causing the problem with the wait.  I can make an attempt to correct the problem and remove the wait by affecting the LOB object in an appropriate way.  In this case, that may be adding extents, looking at moving the LOB to a less accessed tablespace, etc. 

Active Session History (ASH)

Although I have identified the object that is causing the wait, I want to find the session that is causing this wait.  This is where I can use the Active Session History (ASH) Report. Before diving into the ASH Report, lets take a look at what Active Session History (ASH) actually is.

Active Session History provides detailed history of session activity by sampling database activity by sessions every second.  These samplings are taken from memory and then stored in persistent storage (tables).  The sampling that ASH does is done at the session level rather than the instance level.  The statistics that are captured is only for active sessions and is directly related to the work be performed by the sessions rather than the entire database.

Active Session History (ASH) Reports are used to analyzed transient performance problems within the database during specific time. 

Let’s take a look at how the ASH Report is ran and what it provides.

Active Session History (ASH) Report

In Oracle Enterprise Manager 12c (OEM), the ASH Report is accessed through the “Top Activity” menu (Performance –> Top Activity) (Image 8) for that database. 

Image 8:
image

Once on the “Top Activity” page, below the graph that is displayed there is a button labeled “Run ASH Report” (Image 9). 

Image 9:
image

Clicking on the “Run ASH Report” button will bring up the page to provide specific information about the ASH Report that you would like to run (Image 10).  Since the ASH samples so much data, per second, you can select a beginning date, ending date and associated times that you would like to look at in the report. 

Note: Most times, when I use generate an ASH report, I try to keep to the same timeframe as the AWR report.  Provides some consistency to findings.

Image 10:
image

Once I have selected the timeframe I want to use, the report can be generated.  Just like the AWR Report, the ASH Report can be viewed inside of OEM or saved to a file for later reference or sharing with others.

Active Session History (ASH) Report

Let’s read the report now.  As you recall, I found an issue with a high-water mark contention wait.  What I will be looking for in the ASH Report is similar information and possibly who is behind the wait.

Just like the AWR Report, the ASH Report has a header section where I can find all the information related to the database, database instance and timing of the report (Image 11).  This section is general in nature, but it also provides you similar information on SGA usage just like the AWR Report does.

Image 11:image

The next think I like to look at in the ASH Report is the “Top User Events” (Image 12).  If I have selected the same timeframe as the AWR Report, I expect to see similar wait events as I found earlier.  As you can see, the high-water contention wait is at the top of the list again.

Image 12:
image

Now that I have confirmed that the high-water contention wait is the top wait for the sessions during this timeframe, I want to confirm that it is the same object I found in the AWR Report.  Information on the object causing the wait can be found in the “Top Event P1/P2/P3 Values” (Image 13) section.

Image 13:
image

In order to confirm that it is the same object, I need to search DBA_EXTENTS with the values from P2 (data file) and P3 (block number).  Depending on how fast the system is, I can quickly identify the object being accessed that is causing the wait.

In any tuning that is being done, I want to find the SQL statement that the session is using.  This information can also be found in the ASH Report along with the top sessions and if there are any sessions being blocked.

Summary

Overall, these two reports (AWR and ASH) provide a wide range information for anyone to use in performance diagnosing and tuning.   These reports can be ran from the command line or from OEM depending on your comfort levels with the tools; however, I recommend that any DBA looking to keep a database in good working order and top performance learn how to use and read these reports.  II t will save a lot of time in resolving issues.

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like:
Comments
  • They find the straps well-made and comfortable alongside
    the shoulders and again without pinching. The straps relieve
    pressure from lifting, making them an important tool. The location of your
    shoulder pain may assist inform which shoulder joint is affected.
    If you expertise shoulder pain at evening, attempt to adjust your sleeping position so you’re not resting instantly on your shoulder.

    Doing so creates a better degree of tension on the biceps to stimulate muscle development.
    Your triceps have several necessary capabilities that permit you to do many things together with your higher physique.
    Trying your greatest is a welcome and simple facet of developing stronger arms
    and shoulders. The straps don’t grip nicely, in order you raise the load the
    straps would slip and lengthen. Some find the straps confusing
    to make use of at first, saying they wouldn’t stay tight.
    Overall, prospects really feel the straps do not work well for their needs.
    Your end result is dependent upon your age, your exercise degree and the
    categories and range of actions you make with your arm and shoulder.

    That means you’ll still be your strongest when doing delt exercises even though you’ve already
    skilled your again. Evaluate that to the popular chest+shoulder exercise routine where your shoulders are
    heavily concerned when you practice your chest and drained when it’s time to hit them.
    A shoulder superset exercise is a high-intensity routine
    involving two or extra consecutive workout routines that activate the identical shoulder muscle groups.

    Shoulder superset exercises goal the three main muscle tissue of the shoulder—the deltoid, trapezius, and
    rotator cuff.
    People with instability might expertise pain with
    transferring their arm or keep away from
    motions that make the shoulder really feel prefer it wants to slip misplaced.
    Shoulder accidents are incessantly brought on by athletic actions that
    involve extreme, repetitive, overhead motion, corresponding to swimming, tennis,
    pitching, and weightlifting. Injuries also can happen during on a daily basis family actions such washing walls,
    hanging curtains, and gardening.
    If you’re on the lookout for easy barbell shoulder workout routines to add
    to your house routine, Overhead Press are an excellent staple train to get you began. This part will introduce greater than 15 renowned barbell shoulder exercises, with
    detailed descriptions and illustrative pictures. I’ve rounded up the 12 greatest exercises
    that bolster every shoulder muscle in addition to the upper lure and rotator
    cuff. If you’ve entry to a set of dumbbells however not a
    full gym, you possibly can nonetheless get a very effective shoulder workout.
    For instance, in a Facet Lateral Elevate, the arm stops proper on the facet of the body, so the shoulder muscles are never stretched.

    Push-ups and bench presses are more challenging on the backside of the range of
    motion, so I suspect they rival the overhead press for
    building bigger front delts. This is dependent upon what
    number of shoulder workouts you may be doing per
    session. According to studies, it’s the complete weekly training volume, rather than the exercise frequency, that
    matters most for muscle progress [2].
    The difference is there isn’t any room for error and the execution should be
    spot on. This could be prevented by ending the motion right before hyperextension.
    Keeping a slight bend in the arms will still recruit the proper muscles for the contraction. Also, changing
    the grip between underhand, reverse, and impartial
    curls can strengthen the bicep brachii from different
    angles. Individuals who experienced rotator cuff harm in the past were noticeably weaker in core stability workout routines.
    This might apply to any neuromuscular damage, but they have been examined
    in full-body actions specializing in rotator cuff
    activation (3).
    Regularly adjusting your routine based mostly on progress and
    suggestions from professionals is key to a successful recovery.
    If yow will discover a method to comfortably do upright rows, you can do a couple of units of
    8–15 reps per week, adding even more quantity in your side delts.
    Upright rows are a great compound accessory raise is certainly one of the very best lifts for constructing
    broader shoulders and for bettering our overall aesthetics.
    However, they’re also finicky and surprisingly
    controversial, with some individuals finding them uncomfortable and the more fearful experts advising towards ever doing them.
    When dumbbell overhead pressing becomes simple,
    you can progress to the barbell. To do drop sets, begin with
    a weight you are capable of do 10–30 reps with, do a set, strip off round 30% of the weight—you don’t must be precise—and then immediately
    do one other set.
    Indicators of muscle fatigue include cramps,
    localized pain, muscle twitching, trembling, and
    a weak grip. Your chest, lats, and rotator cuff
    are some of the different muscles that cross your shoulder joint.
    Not giving your shoulders sufficient time to relaxation means they
    won’t have the prospect to grow. You may find yourself
    burning muscle instead of fat by training too exhausting, too usually.
    The ultimate addition to our record of the best compound exercises for greater
    shoulders is the landmine shoulder press, which requires a landmine attachment.

    This train can help you enhance the power, size, and definition of your shoulders while preserving your lower back protected.

    He’s personally gained 70 pounds and has over a decade of
    expertise serving to over 10,000 skinny people
    bulk up. You will want a barbell for this one and, ideally, a landmine anchor
    (or another approach to securely anchor one side of the barbell to
    the floor). If you’re unsure what a barbell landmine is or the place to find one, they’re usually hooked up to
    the bottom of the squat/power rack in the health
    club (check out What’s A Barbell Landmine?).
    Your shoulder is amongst the more delicate areas of your arm and is extra prone to
    accidents. 10 to 12 pounds is a good start line should you haven’t already
    been coaching your shoulders. Seated dumbbell presses isolate
    and have interaction the anterior (front), lateral
    (side), and posterior (rear) deltoids.
    The upright row is a versatile shoulder train that targets the delts and trapezius.

    This easy motion is very effective at stimulating hypertrophy, improving pulling mechanics, and constructing energy for
    rows, deadlifts, and pull-ups. The shoulder muscles are strengthened by upright pressing motion.
    Particularly, the entrance a half of the shoulder (anterior deltoid), however
    additionally, you will use some lateral delt and traps.

    The standing cable rear delt row with rope is a really efficient and
    practical train for building muscle and strength in the posterior deltoids.

    The dumbbell entrance elevate to lateral
    increase is an train that mixes two exercises that construct and strengthen the center deltoids
    and the anterior deltoids of the shoulders. The seated neutral-grip
    overhead dumbbell press is a incredible exercise for 
    building power and definition in your shoulders,
    significantly your anterior deltoids and lateral deltoids.

    Shoulder alternative surgery can considerably enhance the quality of life for people
    who undergo from debilitating shoulder pain and restricted mobility.
    Restoration milestones include complete tissue therapeutic, achieving a full vary of movement, and regaining full energy in shoulder muscles.
    These milestones are critical earlier than beginning weightlifting after shoulder surgical procedure.
    Patients ought to await substantial medical enhancements,
    which may take 6 to 12 months, depending on particular person circumstances.
    One of the crucial elements of recovery is the rotator cuff,
    a group of muscles and tendons that stabilize
    the shoulder joint.
    You can carry out it with different leg positions (seated, kneeling, standing) and grip
    variations (neutral, pronated) to add problem and goal different muscle
    teams. This modifications the angle of the motion and 
    puts more emphasis on the front deltoids and upper chest.
    The more upright your torso is, the extra emphasis you’ll place in your lateral deltoids as an alternative of
    in your posterior deltoids.
    It is a highly efficient exercise with the extra benefit of minimizing decrease again strain. Unlike bent-over barbell rows, the standing barbell row provides more rear delt emphasis by permitting you to tug the barbell again in the course of your upper chest rather than your
    lower ribs. This weblog publish will provide a comprehensive overview of 75 standard shoulder workout routines,
    using straightforward names and descriptions. Use dumbbells heavy enough for no less than six reps and a most of twelve.

    A onerous enough hit can stretch or tear the ligaments, causing the bones
    of the AC joint to separate. Pain may finish up when tendons don’t have sufficient space to move properly.

    Upset abdomen, indigestion, and headaches are the most common side effects of oral anti-inflammatory
    drugs. However, taking these drugs after meals or with
    meals can help reduce abdomen upset.
    Since we use our shoulder muscular tissues a lot, they’re
    prone to injuries. You can forestall most shoulder ache by resting your muscular tissues once
    they really feel tired and maintaining them strong and conditioned.
    It usually happens because of overworking your delts on the fitness
    center, like with too many or too heavy rear delt flys or shoulder presses.
    I do need you to begin out with mild weights in your overhead urgent workouts.
    You have loads of time to progress, and starting light will guarantee consistent progress whereas strengthening
    and preparing your stabilizing muscles and shoulder stability for larger hundreds.
    Often, lifters do the alternative, utilizing gentle weights to practice their shoulders.
    For instance, folks hardly ever carry out heavy triples
    (3 units of three reps) on the overhead press.

    You might expertise minor “clicks” and aches or have persistent,
    debilitating pain and extreme restriction of movement. Utilizing dumbbells as a part of a shoulder rehabilitation program is typically recommended to minimize strain on the joint and maximize the benefits of the exercises.
    It is mostly really helpful to restrict lifting
    to not more than 40 kilos with the affected arm to avoid overloading
    the shoulder.
    I primarily design workout plans and share science-based and sensible data that may
    allow you to turn out to be stronger, practical, and more healthy.
    The incline row involves lying susceptible on a 45-degree incline bench and adducting
    your arms. Hitting all these muscle tissue from multiple angles isn’t possible with just one piece of apparatus, except dumbbells.
    Resistance bands are the perfect tool for training the shoulders at house,
    and they’re extremely transportable too!
    These muscles and tendons hold your higher arm bone (humerus) inside your shoulder socket (glenoid).
    Gentle resistance workout routines like pull-aparts, dislocates,
    and presses could be advantageous for shoulder recovery whereas
    avoiding excessive strain on the joint. Nevertheless,
    it’s of utmost significance to abide by your physician’s recommendation and avoid heavy lifting or workout
    routines that could amplify the dangers tied to weightlifting post-surgery.
    Patients can anticipate resuming mild activities as soon as pain-free, with regular exercise expected to resume
    around 4-6 months post-surgery.

    References:

    best steroids for muscle gain without side effects (demilked.com)

Leave a Reply

Your email address will not be published. Required fields are marked *

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.