Search

Top 60 Oracle Blogs

Recent comments

AWR

Purging SQL Statements and Execution Plans from AWR

A previous blog discussed the problem posed by old execution plans that were previously captured by AWR being used by dbms_xplan.display_awr() to describe current executions plans for the same execution plan but displaying the old costs.

Working with AWR: Old Statistics and Costs in Current Execution Plans

This is a blog about a little trap, into which it is easy to fall when analysing performance problems using ASH data and execution plans from AWR.
I will illustrate it with a real example taken from a production PeopleSoft system. A number of sessions were waiting on blocking locks.  I used ASH data to find statements running while holding a blocking lock.  That led me to a particular SQL ID and plan have value.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SQL_ID        SQL_PLAN_HASH_VALUE   ASH_SECS
------------- ------------------- ----------
29210
5st32un4a2y92 2494504609 10670
652mx4tffq415 1888029394 7030
artqgxug4z0f1 8450529 580

Truncate AWR tables (unsupported)

When WRH$ tables grow too large so that they cannot be purged

This is no supported, please look at the My Oracle Support notes for a supported way to purge AWR when going too big, like re-creating AWR (needs to start the database in restricted mode) or purging with the normal procedure (can be long as it runs a delete). And do not copy-paste my statements as this is just an example.

When some tables grow too large, the purge job does not work correctly (because some things like the partitioning are done at the end). Then SYSAUX grows. And worse: the next upgrade may take hours if it has to change something on the AWR tables.

Upgrade time

Here is an example of an upgrade from 11g to 19c which took hours. Here is how I open the upgrade logs with “less” for the Top-10 longest statement execution:

AW-argh

This is another of the blog notes that have been sitting around for several years – in this case since May 2014, based on a script I wrote a year earlier. It makes an important point about “inconsistency” of timing in the way that Oracle records statistics of work done. As a consequence of being first drafted in May 2014 the original examples showed AWR results from 10.2.0.5 and 11.2.0.4 – I’ve just run the same test on 19.3.0.0 to see if anything has changed.

 

Importing and Working with Exported AWR/ASH data in an Oracle database in a VirtualBox VM

A lot of my performance tuning work involves analysis of ASH and AWR data.  Frequently, I do not have direct access to the databases in question.  Sometimes, I ask clients to run EDB360 on their databases and send me the results, but sometimes I also want to work directly with ASH or AWR metrics.  So, I ask for an export of their AWR repository.
Oracle distributes a pair of scripts in $ORACLE_HOME/rdbms/admin.

CPU percent

A recent post on the ODC General Database forum asked for an explanation of the AWR report values “%Total CPU” and “%Busy CPU” under the “Instance CPU” label, and how the “%Busy CPU “ could be greater than 100%.  Here’s a text reproduction of the relevant sample supplied:

Quick history on database growth

AWR collects segment statistics, and this can be used to quickly understand an abnormal database growth. Here is a script I use to get, from the AWR history, the segments that have grown by more than 1% of the database size, in one hour.

First I must mention that this uses only the part of AWR which is not subject to additional option. This even works in Standard Edition:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string NONE

Database Comparisons with AWR Warehouse- Part II, Comparison Period Report

There are two ways to compare one database to another in the AWR Warehouse.

Database Comparisons Using AWR Warehouse Part 1- ADDM Comparison

A lot of my ideas for blog posts come from questions emailed to me or asked via Twitter.  Today’s blog is no different, as I was asked by someone in the community what the best method of comparing databases using features within AWR when migrating from one host and OS to another.

Presentation “Real-Time SQL Tuning” at Hotsos Sym2014 and UTOUG TD2014

This presentation was delivered at the Hotsos Symposium 2014 and at the Utah Oracle Users Group (UTOUG) Training Days 2014.

It describes a PL/SQL package that I put together called “ash_xplan.sql” which can be used to display real-time information about a SQL statement currently executing.  This package combines information from the Active Session History (V$ACTIVE_SESSION_HISTORY) or “ASH” view with information from the DBMS_XPLAN package.

As of Oracle12c v12.1, nothing in Oracle displays elapsed time while the SQL statement is still executing, not even SQL Monitor.

The source code for the “ash_xplan.sql” script is on the Scripts page of this website, along with sample spooled output.