Automatic Workload Repository (AWR) in Oracle Database 10g

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

• AWR Features

AWR Features

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.


• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.


• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.


• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.


• Object usage statistics.


• Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

• Automatic Database Diagnostic Monitor


• SQL Tuning Advisor


• Undo Advisor


• Segment Advisor

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 22,
    high_snap_id => 32);
END;
/

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing like:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 210,
    end_snap_id   => 220,
    baseline_name => 'batch baseline');
END;
/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline (
    baseline_name => 'batch baseline',
    cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;
/

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.


• V$METRIC - Displays metric information.


• V$METRICNAME - Displays the metrics associated with each metric group.


• V$METRIC_HISTORY - Displays historical metrics.


• V$METRICGROUP - Displays all metrics groups.


• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.


• DBA_HIST_BASELINE - Displays baseline information.


• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.


• DBA_HIST_SNAPSHOT - Displays snapshot information.


• DBA_HIST_SQL_PLAN - Displays SQL execution plans.


• DBA_HIST_WR_CONTROL - Displays AWR settings.

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

AWR Report: To generate a text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the number days for which you want to list snapshot Ids.

Enter value for num_days: 2

After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

Enter value for begin_snap: 100

Enter value for end_snap: 120

Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:

Using the report name awrrpt_1_100_120

The workload repository report is thus generated.

Below is a sample output of the AWR Report.

WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Release	Cluster	Host
TEST	118468335	TEST	1	10.1.0.4.0	NO	test-db.ora.hp

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	834	11-Jul-06 01:27:31	1732	177.7
End Snap:	836	11-Jul-06 02:27:36	2038	196.3
Elapsed:		60.08 (mins)
DB Time:		381.35 (mins)

Report Summary

Cache Sizes (end)

Buffer Cache:	1,376M	Std Block Size:	8K
Shared Pool Size:	1,856M	Log Buffer:	10,240K

Load Profile

	Per Second	Per Transaction
Redo size:	2,389,415.27	27,164.76
Logical reads:	108,397.97	1,232.35
Block changes:	15,015.45	170.71
Physical reads:	158.28	1.80
Physical writes:	247.04	2.81
User calls:	3,714.26	42.23
Parses:	797.76	9.07
Hard parses:	1.97	0.02
Sorts:	1,014.79	11.54
Logons:	0.77	0.01
Executes:	7,300.46	83.00
Transactions:	87.96

% Blocks changed per Read:	13.85	Recursive Call %:	76.60
Rollback per transaction %:	22.64	Rows per Sort:	12.46

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	99.99	Redo NoWait %:	100.00
Buffer Hit %:	99.86	In-memory Sort %:	100.00
Library Hit %:	99.98	Soft Parse %:	99.75
Execute to Parse %:	89.07	Latch Hit %:	99.57
Parse CPU to Parse Elapsd %:	84.64	% Non-Parse CPU:	96.72

Shared Pool Statistics

	Begin	End
Memory Usage %:	51.28	71.99
% SQL with executions>1:	85.40	93.22
% Memory for SQL w/exec>1:	78.16	85.79

Top 5 Timed Events

Event	Waits	Time(s)	Percent Total DB Time	Wait Class
CPU time		17,107	74.76
db file sequential read	498,214	2,773	12.12	User I/O
log file sync	129,031	2,010	8.78	Commit
log file parallel write	445,391	517	2.26	System I/O
latch: library cache	26,609	141	.62

A Unix/Linux "find" Command Tutorial

The find command is used to locate files on a Unix or Linux system.  find will search any set of directories you specify for files that match the supplied search criteria.  You can search for files by name, owner, group, type, permissions, date, and other criteria.  The search is recursive in that it will search all subdirectories too.  The syntax looks like this:

find where-to-look criteria what-to-do

All arguments to find are optional, and there are defaults for all parts.  (This may depend on which version of find is used.  Here we discuss the freely available GNU version of find, which is the version available on YborStudent.)  For example where-to-look defaults to . (that is, the current working directory), criteria defaults to none (that is, show all files), and what-to-do (known as the find action) defaults to -print (that is, display the names of found files to standard output).  Technically the criteria and actions are all known as find primaries.

For example:

find

will display the pathnames of all files in the current directory and all subdirectories.  The commands

find . -print
find -print
find .

do the exact same thing.  Here's an example find command using a search criterion and the default action:

find / -name foo

This will search the whole system for any files named foo and display their pathnames.  Here we are using the criterion -name with the argument foo to tell find to perform a name search for the filename foo.  The output might look like this:

/home/wpollock/foo
/home/ua02/foo
/tmp/foo

If find doesn't locate any matching files, it produces no output.

The above example said to search the whole system, by specifying the root directory (/) to search.  If you don't run this command as root, find will display a error message for each directory on which you don't have read permission.  This can be a lot of messages, and the matching files that are found may scroll right off your screen.  A good way to deal with this problem is to redirect the error messages so you don't have to see them at all:

find / -name foo 2>/dev/null

You can specify as many places to search as you wish:

find /tmp /var/tmp . $HOME -name foo

Advanced Features And Applications:

The -print action lists the names of files separated by a newline.  But it is common to pipe the output of find into xargs, which uses a space to separate file names.  This can lead to a problem if any found files contain spaces in their names, as the output doesn't use any quoting.  In such cases, when the output of find contains a file name such as foo bar and is piped into another command, that command sees two file names, not one file name containing a space.  Even without using xargs you could have a problem if the file name contains a newline character.

In such cases you can specify the action -print0 instead.  This lists the found files separated not with a newline but with a null (or NUL) character, which is not a legal character in Unix or Linux file names.  Of course the command that reads the output of find must be able to handle such a list of file names.  Many commands commonly used with find (such as tar or cpio) have special options to read in file names separated with NULs instead of spaces.

You can use shell-style wildcards in the -name search argument:

find . -name foo\*bar

This will search from the current directory down for foo*bar (that is, any filename that begins with foo and ends with bar).  Note that wildcards in the name argument must be quoted so the shell doesn't expand them before passing them to find.  Also, unlike regular shell wildcards, these will match leading periods in filenames.  (For example find -name \*.txt.)

You can search for other criteria beside the name.  Also you can list multiple search criteria.  When you have multiple criteria any found files must match all listed criteria.  That is, there is an implied Boolean AND operator between the listed search criteria.  find also allows OR and NOT Boolean operators, as well as grouping, to combine search criteria in powerful ways (not shown here.)

Here's an example using two search criteria:

find / -type f -mtime -7 | xargs tar -rf weekly_incremental.tar
gzip weekly_incremental.tar

will find any regular files (i.e., not directories or other special files) with the criteria -type f, and only those modified seven or fewer days ago (-mtime -7).  Note the use of xargs, a handy utility that coverts a stream of input (in this case the output of find) into command line arguments for the supplied command (in this case tar, used to create a backup archive).

Using the tar option -c is dangerous here;  xargs may invoke tar several times if there are many files found and each -c will cause tar to over-write the previous invocation.  The -r option appends files to an archive.  Other options such as those that would permit filenames containing spaces would be useful in a production quality backup script.

Another use of xargs is illustrated below.  This command will efficiently remove all files named core from your system (provided you run the command as root of course):

find / -name core | xargs /bin/rm -f
find / -name core -exec /bin/rm -f '{}' \; # same thing
find / -name core -delete                  # same if using Gnu find

(The last two forms run the rm command once per file, and are not as efficient as the first form.  However the first form is safer if rewritten to use -print0.)

One of my favorite of the find criteria is used to locate files modified less than 10 minutes ago.  I use this right after using some system administration tool, to learn which files got changed by that tool:

find / -mmin -10

(This search is also useful when I've downloaded some file but can't locate it, only in that case -cmin may work better.  Keep in mind neither of these criteria is standard; -mtime and -ctime are standard, but use days and not minutes.)

Another common use is to locate all files owned by a given user (-user username).  This is useful when deleting user accounts.

You can also find files with various permissions set.  -perm /permissions means to find files with any of the specified permissions on, -perm -permissions means to find files with all of the specified permissions on, and -perm permissions means to find files with exactly permissions.  Permissions can be specified either symbolically (preferred) or with an octal number.  The following will locate files that are writeable by others (including symlinks, which should be writeable by all):

find . -perm -o=w

(Using -perm is more complex than this example shows.  You should check both the POSIX documentation for find (which explains how the symbolic modes work) and the Gnu find man page (which describes the Gnu extensions).

When using find to locate files for backups, it often pays to use the -depth option (really a criterion that is always true), which forces the output to be depth-first—that is, files first and then the directories containing them.  This helps when the directories have restrictive permissions, and restoring the directory first could prevent the files from restoring at all (and would change the time stamp on the directory in any case).  Normally, find returns the directory first, before any of the files in that directory.  This is useful when using the -prune action to prevent find from examining any files you want to ignore:

find / -name /dev -prune | xargs tar ...

When specifying time with find options such as -mmin (minutes) or -mtime (24 hour periods, starting from now), you can specify a number n to mean exactly n, -n to mean less than n, and +n to mean more than n.

Fractional 24-hour periods are truncated!  That means that find -mtime +1 says to match files modified two or more days ago.

For example:

find . -mtime 0   # find files modified between now and 1 day ago
                  # (i.e., within the past 24 hours)
find . -mtime -1  # find files modified less than 1 day ago
                  # (i.e., within the past 24 hours, as before)
find . -mtime 1   # find files modified between 24 and 48 hours ago
find . -mtime +1  # find files modified more than 48 hours ago

find . -mmin +5 -mmin -10 # find files modified between
                          # 6 and 9 minutes ago

Using the -printf action instead of the default -print is useful to control the output format better than you can with ls or dir.  You can use find with -printf to produce output that can easily be parsed by other utilities or imported into spreadsheets or databases.  See the man page for the dozens of possibilities with the -printf action.  (In fact find with -printf is more versatile than ls and is the preferred tool for forensic examiners even on Windows systems, to list file information.)  For example the following displays non-hidden (no leading dot) files in the current directory only (no subdirectories), with an custom output format:

find . -maxdepth 1 -name '[!.]*' -printf 'Name: %16f Size: %6s\n'

-maxdepth is a Gnu extension.  On a modern, POSIX version of find you could use this:

find . -path './*' -prune ...

On any version of find you can use this more complex (but portable) code:

find . ! -name . -prune ...

which says to prune (don't descend into) any directories except ..

Note that -maxdepth 1 will include . unless you also specify -mindepth 1.  A portable way to include . is:

 find . \( -name . -o -prune \) ...

[This information posted by Stephane Chazelas, on 3/10/09 in newsgroup comp.unix.shell.]

As a system administrator you can use find to locate suspicious files (e.g., world writable files, files with no valid owner and/or group, SetUID files, files with unusual permissions, sizes, names, or dates).  Here's a final more complex example (which I saved as a shell script):

find / -noleaf -wholename '/proc' -prune \
     -o -wholename '/sys' -prune \
     -o -wholename '/dev' -prune \
     -o -wholename '/windows-C-Drive' -prune \
     -o -perm -2 ! -type l  ! -type s \
     ! \( -type d -perm -1000 \) -print

This says to seach the whole system, skipping the directories /proc, /sys, /dev, and /windows-C-Drive (presumably a Windows partition on a dual-booted computer).  The Gnu -noleaf option tells find not to assume all remaining mounted filesystems are Unix file systems (you might have a mounted CD for instance).  The -o is the Boolean OR operator, and ! is the Boolean NOT operator (applies to the following criteria).

So these criteria say to locate files that are world writable (-perm -2, same as -o=w) and NOT symlinks (! -type l) and NOT sockets (! -type s) and NOT directories with the sticky (or text) bit set (! \( -type d -perm -1000 \)).  (Symlinks, sockets and directories with the sticky bit set are often world-writable and generally not suspicious.)

A common request is a way to find all the hard links to some file.  Using ls -li file will tell you how many hard links the file has, and the inode number.  You can locate all pathnames to this file with:

  find mount-point -xdev -inum inode-number

Since hard links are restricted to a single filesystem, you need to search that whole filesystem so you start the search at the filesystem's mount point.  (This is likely to be either /home or / for files in your home directory.)  The -xdev options tells find to not search any other filesystems.

(While most Unix and all Linux systems have a find command that supports the -inum criterion, this isn't POSIX standard.  Older Unix systems provided the ncheck utility instead that could be used for this.)

Using -exec Efficiently:

The -exec option to find is great, but since it runs the command listed for every found file it isn't very efficient.  On a large system this makes a difference!  One solution is to combine find with xargs as discussed above:

  find whatever... | xargs command

However this approach has two limitations.  Firstly not all commands accept the list of files at the end of the command.  A good example is cp:

find . -name \*.txt | xargs cp /tmp  # This won't work!

(Note the Gnu version of cp has a non-POSIX option -t for this, and xargs has options to handle this too.)

Secondly filenames may contain spaces or newlines, which would confuse the command used with xargs.  (Again Gnu tools have options for that, find ... -print0 |xargs -0 ....)

There are POSIX (but non-obvious) solutions to both problems.  An alternate form of -exec ends with a plus-sign, not a semi-colon.  This form collects the filenames into groups or sets, and runs the command once per set.  (This is exactly what xargs does, to prevent argument lists from becoming too long for the system to handle.)  In this form the {} argument expands to the set of filenames.  For example:

find / -name core -exec /bin/rm -f '{}' +

This form of -exec can be combined with a shell feature to solve the other problem (names with spaces).  The POSIX shell allows us to use:

sh -c 'command-line' [ command-name [ args... ] ]

(We don't usually care about the command-name, so X, dummy, or inline cmd is often used.)  Here's an example of efficiently copying found files to /tmp, in a POSIX-compliant way (Posted on comp.unix.shell netnews newsgroup on Oct. 28 2007 by Stephane CHAZELAS):

find . -name '*.txt' -type f \
  -exec sh -c 'exec cp -f "$@" /tmp' find-copy {} +

Common Gotcha:

If the given expression to find does not contain any of the action primaries -exec, -ok, or -print, the given expression is effectively replaced by:

find \( expression \) -print

The implied parenthesis can cause unexpected results.  For example, consider these two similar commands:

$ find -name tmp -prune -o -name \*.txt
./bin/data/secret.txt
./tmp
./missingEOL.txt
./public_html/graphics/README.txt
./datafile2.txt
./datafile.txt

$ find -name tmp -prune -o -name \*.txt -print
./bin/data/secret.txt
./missingEOL.txt
./public_html/graphics/README.txt
./datafile2.txt
./datafile.txt

The lack of an action in the first command means it is equivalent to:

find . \( -name tmp -prune -o -name \*.txt \) -print

This causes tmp to be included in the output.  However for the second find command the normal rules of Boolean operator precedence apply, so the pruned directory does not appear in the output.

The find command can be amazingly useful.  See the man page to learn all the criteria and actions you can use.

Upgrade an Oracle Instance 10.2.0.3 to 10.2.0.4

To manually upgrade an Oracle Instance from 10.2.0.3 to 10.2.0.4 is pretty much a strait forward routine if one already have a 10.2.0.4 home on the machine.

Simply shutdown the instance and edit the /etc/oratab and the /usr/local/oraenv (if this has been changed from the original version) to make sure your instance now uses the new ORACLE_HOME.

Startup the instance in upgrade mode and run the pre-check script.

SQL>sqlplus / as sysdba
SQL>startup upgrade
SQL>spool upgrade_info.log
SQL>@?/rdbms/admin/utlu102i.sql
SQL>spool off

Check the log for possible problems and correct them if needed

Now run the upgrade script

SQL>sqlplus / as sysdba
SQL>spool patch.log
SQL>@?/rdbms/admin/catupgrd.sql
SQL>spool off

Check the log for errors. Most likely there will be an error ORA-00904 regarding OTHER_XML for the package body DBMS_SQLPA.

Also at the end of the log a scary message saying

Component Status Version HH:MM:SS
Oracle Database Server INVALID 10.2.0.4.0 00:06:56

Looks bad but in this case it’s not
A select COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; will show what really went wrong.
Oracle Database Packages and Types 10.2.0.4.0 INVALID

The fix for this problem is to drop the plan_table and re-create it (See MetaLink Note: 565600.1)

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

This should fix the problem and the upgrade to 10.2.0.4 is done.

what is your 10 common linux commands?

What is your regular command you use? I am sure you must thinking of ls and cd. Yeah, they are common for every users, but how about the rest of them? I have construct a combos of commands to help you identify your top ten linux command.



The idea is simple, we gather info from history, lets look at the command combo’s now.

history | awk '{CMD[$2]++;count++;}END { for (a in CMD)print CMD[a] " " CMD[a]/count*100 "% " a;}' | grep -v "./" | column -c3 -s " " -t | sort -nr | nl |  head -n10

The command line above may looks complected, let me briefly explain it part by part. Awk is the most important part in line above, It simply store the command and count the occurrence in history ( column 2, $2), at the end of operation, it prints the result accordingly. Check out awk examples for more illustration on awk.

With the result output it then passes to grep -v to filter out “./”, because ./something is not a linux command. After that, arrange the data in columns and sort it numerically. List only the top ten with numbers using head and nl.

I have also created a script, so you can download it and run.

The results might vary based on your daily activities and Linux distribution. I have two laptop, one i use it at home and another one I use it at office. Ok, here is my 10 common linux commands.

Linux Distro: kubuntu 7.10

Desc: My personal laptop.

Activities: Online, Blogging, and leisure stuff.

     1  97  19.4%  ls
     2  78  15.6%  cd
     3  46  9.2%   vi
     4  27  5.4%   sudo
     5  25  5%     apt-get
     6  15  3%     ps
     7  13  2.6%   rm
     8  13  2.6%   ll
     9  11  2.2%   man
    10  8   1.6%   mv

Linux Distro: Ubuntu 7.04

Desc: Office Laptop

Activities: coding in c/c++, testing on the modules coded.

     1  94  18.8%  vi
     2  83  16.6%  g++
     3  77  15.4%  ls
     4  69  13.8%  cd
     5  11  2.2%   ps
     6  10  2%     man
     7  8   1.6%   sudo
     8  6   1.2%   apt-get
     9  5   1%     cp
    10  4   0.8%   vis

From the result above, you may realized that, I use vi and g++ more than cd and ls during working.

p.s. vis is alias vis=’sudo vi’

List command line history with timestamp

History is a common command for shell to list out all the executed commands. It is very useful when it comes to investigation on what commands was executed that tear down the server. With the help of last command, you be able to track the login time of particular user as well as the the duration of the time he/she stays login.

last
...
mysurface    tty7         :0               Mon Oct  6 20:07 - down   (00:00)
reboot   system boot  2.6.24.4-64.fc8  Mon Oct  6 20:06          (00:00)
mysurface    pts/8        10.168.28.44     Mon Oct  6 17:42 - down   (01:58)
mysurface    pts/7        :0.0             Mon Oct  6 17:41 - 19:40  (01:59)
mysurface    pts/6        :0.0             Mon Oct  6 17:27 - 19:40  (02:13)
mysurface    pts/5        :0.0             Mon Oct  6 17:27 - 19:40  (02:13)
mysurface    pts/5        :0.0             Mon Oct  6 15:52 - 15:59  (00:07)
...

If the command line history could provides the date time of the commands being executed, that may really narrow down the scope of the user actions that cause the server malfunction. By default, history do not append with timestamp, but it is easy to configure it to display timestamp, you just need to set one environment variable HISTTIMEFORMAT.

HISTTIMEFORMAT takes format string of strftime. Check out the strftime manual to choose and construct the timestamp that suit your taste. My favorite is “%F %T “.

export HISTTIMEFORMAT="%F %T "

Execute history again and you will see the effect on the spot, bare in mind that the timestamp for command lines that executed at previous sessions may not valid, as the time was not tracked.

...
  994  2008-10-16 02:27:40 exit
  995  2008-10-16 01:12:20 iptables -nL
  996  2008-10-16 01:47:46 vi .bash_profile
  997  2008-10-16 01:47:55 history
  998  2008-10-16 01:48:03 . .bash_profile
  999  2008-10-16 01:48:04 history
 1000  2008-10-16 01:48:09 exit
 1001  2008-10-16 02:27:43 history
...

I would suggest you to put the export into ~/.bash_profile as well as /root/.bash_profile. In case you do not have .bash_profile, you can choose to put into ~/.bashrc.