Using Automatic Database Diagnostic Monitor Manually

For Oracle after 10g the statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository(AWR). The Automatic Database Diagnostic Monitor(ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event.

This decision tree Oracle developed in years under ADDM improves root-cause analysis and after this amount of research and development you pay additional cost for this feature even you are using Enterprise Edition.

The goal of the analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by the database server in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

The background process Manageability Monitor Process(MMON) schedules the automatic running of the ADDM. You only need to make sure that the initialization parameters STATISTICS_LEVEL is set to TYPICAL or ALL, in order for the AWR to gather its cache of performance statistics. MMON schedules the ADDM to run every time the AWR collects its most recent snapshot. To view the ADDM’s findings:
* Use the OEM Database Control, the primary interface for diagnostic monitoring is the Oracle Enterprise Manager Database Control.
* Run the Oracle-provided script addmrpt.sql, very similar to statspack or awr reports.
* Run DBMS_ADVISOR APIs
Note: The DBMS_ADVISOR package requires the ADVISOR privilege.

Code Listing 112 – Manual ADDM Demo

ADDM analysis finding consists of the following four components:
* The definition of the problem itself
* The root cause of the performance problem
* Recommendation(s) to fix the problem
* The rationale for the proposed recommendations

Example ADDM Report

FINDING 1: 31% impact (7798 seconds)
————————————
SQL statements were not shared due to the usage of literals. This resulted in additional hard parses which were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 31% benefit (7798 seconds)
ACTION: Investigate application logic for possible use of bind variables
instead of literals. Alternatively, you may set the parameter “cursor_sharing” to “force”.
RATIONALE: SQL statements with PLAN_HASH_VALUE 3106087033 were found to be using literals. Look in V$SQL for examples of such SQL statements.

In order to determining optimal I/O performance, ADDM uses the default value of the parameter DBIO_EXPECTED, which is 10 milliseconds.

SELECT PARAMETER_VALUE
FROM DBA_ADVISOR_DEF_PARAMETERS
WHERE ADVISOR_NAME=’ADDM’
AND PARAMETER_NAME=’DBIO_EXPECTED’

Today’s hardwares can be significantly different, so you may set the parameter value one time for all subsequent ADDM executions of course for better advices. Since this is not an init.ora parameter, the parameter can be changed again with DBMS_ADVISOR supplied package;

DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(‘ADDM’,’DBIO_EXPECTED’, 8000);

There are two important views you will be checking for the results of the ADDM analysis:

1. DBA_ADVISOR_RECOMMENDATIONS: This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.

2. DBA_ADVISOR_FINDINGS: This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g at oracle-base.com
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 6 Automatic Performance Diagnostics

Everyday something new, never ending story

After completing my first 10 years with database technologies and 8 years with Oracle, I can guarantee this to newbies; after years of experience and reading, you still feel you are at the beginning. For me this feeling is special since if you like, love what you are doing than there you may go deeper and deeper as you wish through the years, no need to hurry. Especially the internal topics which are not documented officially will take extra effort, some dumping and reverse engineering most probably.

Of course it is really good to have below resources and sharing people around you during this journey –
tahiti – http://tahiti.oracle.com
asktom – http://asktom.oracle.com
otn – http://otn.oracle.com
forums – http://forums.oracle.com
metalink – http://metalink.oracle.com

Also some experts, aces who are voluntarily giving their best effort to blog, publish articles, prepare small demos to improve your understanding and reply your problems over forums. This way of learning is very different from 5 years before, much more efficient. I am working with 20 years old young engineers for the last 5 years and I have seen them developing so quickly under my guidance and using these resources, I may easily say within 3-4 years of consistent development they pass me and start to develop me :)

So here comes the feature who let me have above feelings and write all these; from 10g the RETURNING clause can be aggregated to return a single record or value from data affected by DML activity, did you know this one? Not a big one and there are workarounds for this need but check this, when you need it you will like to use it, I know –

Code Listing 110-Aggregating data with returning clause

Last words for the new kid on the block, 11g again, here is a forums thread which you may want to contribute for the new features of the new pop star of the Oracle Blogsphere :).

Also tahiti now has the 11g link :)

11g documentation

Okey, it is getting hot :)

1. Download Oracle Database 11g Release 1 (11.1.0.6.0)
Standard Edition, Standard Edition One, and Enterprise Edition – Linux x86 (1.7 GB)

2. Oracle Database 11g Release 1 Documentation Library
Oracle® Database Concepts 11g Release 1 (11.1)
Oracle® Database Upgrade Guide 11g Release 1 (11.1)
Oracle® Database New Features Guide 11g Release 1 (11.1)
“What’s New in 11g” search on documentation

3. Oracle Database 11g: The Top New Features for DBAs and Developers by Arup Nanda Oracle ACE Director
Database Replay
Partitioning to Perfection
Schema Management
Adaptive Cursors and SQL Plan Management
Transaction Management

4. Installing 11g Release 1 on Xubuntu 7.04 by Howard Rogers

Welcome 11g

Yesterday Oracle Türkiye was our guest and we talked on the new features of the new release. Below you will find some presentations which summaries why you might want to migrate to 11g.

Creating test environments was one of our chronic problems and with 11g Standby Snapshots and Real Application Testing(RAT) features, managing testing quality will be much more easier for us. Also real time physical standby database feature for reporting purposes is another great feature.

11g on Linux will be download able from OTN this month and I also hope we will be enjoying a new free Express Edition(XE) version soon.

10g Release 2 is a great release for our needs and each day I continue to learn new things about it, but this is for today of course.

Also I still believe in second releases :) I hope you also enjoy the 11g new features –

11g General New Features
11g Application Development New Features
11g Manageability New Features
11g Very Large Database New Features
11g High Availability New Features
11g Grid and OLTP New Features
11g Real Application Testing New Features
Why 11g? (In Turkish)New Features

You may also want to visit these 11g references –

Oracle 11G database on OTN
Mr.Arup Nanda is again preparing one of his great top features series for 11g
Amis Blogs on 11g new features
psoug.org started publishing 11gR1 demos

Unload Data with External Tables and Data Pump

Before 10g External Tables were used to read external data into destination database but after Oracle 10g external tables can be created as a CTAS(Create Table As Select) operation, which enables a one-time unloading of inside data.

This is NOT a native data unloader(no more sqlplus spools, UTL_FILE writes, Pro*C unloaders etc). Oracle supplied a new access driver called “ORACLE_DATAPUMP” and Oracle will “unload” an external table to a format only usable by the Data Pump utility another new 10g feature to replace traditional imp and exp. So generating an ASCII or CSV file from external tables is NOT possible, at least for today :)

Below is a simple demonstration of how to dump the data from the ALL_SOURCE view to a file.

Code Listing 107-External Table Write Demo

The log file can be avoided using the NOLOGFILE keyword, but contains usefull information such as the time the file was opened and any error messages that occur during the creation or read-back of the external table.

For more information and examples please see this fantastic paper as usual by Oracle ACE Howard RogersCreating External Tables with Data Pump

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Oracle® Database Utilities 10g Release 2 (10.2) – Chapter 1 Overview of Oracle Data Pump – External Tables Part

Database Performance FAQ – DIAGNOSTICS Part

Below is a summary from one of my favorite metalink notes; Note:402983.1 – Database Performance FAQ Last Revision Date: 15-MAR-2007. Purpose of this document is a number of Frequently Asked Database Performance Questions for all Oracle DBAs. And below is the full content of the note –

Database Performance FAQ
* INVESTIGATING A DATABASE PERFORMANCE ISSUE
* DIAGNOSTICS
o AWR reports/Statspack reports
o 10046 Trace
o Querying V$Session_wait
o System State Dumps
o Errorstack
o PSTACK
o PLSQL Profiler
o Hanganalyze
* INTERPRETING THE RESULTS/TRACES
* TOP DATABASE PERFORMANCE ISSUES/PROBLEMS AND HOW TO RESOLVE THEM
o Library Cache/Shared Pool Latch waits
o High Version Counts
o Log File Sync waits
o Buffer Busy waits/Cache Buffers Chains Latch waits
o Enqueue waits
o WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!
o ORA-60 DEADLOCK DETECTED and enqueue hash chains latch

AWR reports/Statspack reports

AWR/Statspack reports provide a method for evaluating the relative performance of a database. In 10G, to check for general performance issues use the Automatic Workload Repository (AWR) and specifically the Automatic Database Diagnostic Monitor (ADDM) tool for assistance. This is covered in the following article: Note 276103.1 PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY FEATURES

Note: If uploading reports to support, please ensure that they are in Text format
For 9i and 8i, statspack, rather than AWR, reports should be gathered. To gather a statspack report, please refer to: Note 94224.1 FAQ- Statspack Complete Reference

To interpret statspack output refer to:
http://www.oracle.com/technology/deploy/performance/pdf/statspack_tuning_otn_new.pdf

10046 Trace

10046 trace gathers tracing information about a session.

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events ‘10046 trace name context forever,level 12’;

— run the statement(s) to be traced —

select * from dual;
exit;

Querying V$Session_wait

The view V$Session_wait can show useful information about what a session is waiting for.
Multiple selects from this view can indicate if a session is moving or not. When wait_time=0 the session is waiting, any other value indicates CPU activity:

set lines 132 pages 999
column event format a30

select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;

See: Note 43718.1 VIEW “V$SESSION_WAIT” Reference Note

** Important ** – v$session_wait is often misinterpreted. Often people will assume we are waiting because see an event and seconds_in_wait is rising. It should be remembered that seconds_in_wait only applies to a current wait if wait_time =0 , otherwise it is actually “seconds since the last wait completed”. The other column of use to clear up the misinterpretation is state which will be WAITING if we are waiting and WAITED% if we are no longer waiting

Finding session id

This select is useful for finding the current session information for tracing later:

select p.pid,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.audsid = userenv(‘SESSIONID’)
/

System State Dumps

If the database is hung then we need to gather systemstate dumps to try to determine what is happening. At least 3 dumps should be taken as follows:

Login to sqlplus as the internal user:

sqlplus “/ as sysdba”

rem — set trace file size to unlimited:

alter session set max_dump_file_size = unlimited;

alter session set events ‘10998 trace name context forever, level 1’;
alter session set events ‘immediate trace name systemstate level 10’;
alter session set events ‘immediate trace name systemstate level 10’;
alter session set events ‘immediate trace name systemstate level 10’;

or

sqlplus “/ as sysdba”

alter session set max_dump_file_size = unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
alter session set events ‘immediate trace name systemstate level 266’;
alter session set events ‘immediate trace name systemstate level 266’;

If no connection is possible at all then please refer to the following article which describes how to collect systemstates in that situation: Note 121779.1 – Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.

Errorstack

Errorstack traces are Oracle Call Stack dumps that can be used to gather stack information for a process. Attach to the process and gather at least 3 errorstacks:

login to SQL*Plus:

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3

PSTACK

Pstack is an operating system tool that can be used to gather stack information on some unix platforms. Attach to the process and gather about 10 pstacks while the job is running.

% script pstacks.txt
% /usr/proc/bin/pstack pid
% exit

The PID is the o/s process id of the process to be traced. Repeat the pstack command about 10 times to capture possible stack changes. Further details of pstack are in: Note 70609.1 How To Display Information About Processes on SUN Solaris

PLSQL Profiler

The PL/SQL profiler provides information abour PL/SQL code with regard to CPU usage and other resource usage information. See: Note 243755.1 Implementing and Using the PL/SQL Profiler

Hanganalyze

Hanganalyze is often gathered for hang situations. Typically systemstates are more useful. The following describes how to gather hanganalyze dumps: Note 175006.1 Steps to generate HANGANALYZE trace files.

Log Buffer #56: a Carnival of the Vanities for DBAs

This is the 56th edition of Log Buffer a Carnival of the Vanities for DBAs, the weekly review of database blogs. Thanks to Dave Edwards of Pythian for helping me during the week especially for Non-Oracle blogs.

Log Buffer is published every Friday afternoon and if you want to contribute please get in touch with Dave. This is the the Log Buffer Guidelines for the Log Buffer editors. You may also consider subscribing to the Log Buffer Feed.

MySQL

  1. Jan Kneschke on MySQL Proxy learns R/W Splitting
  2. Colin Charles Agenda on MySQL-related resources that you will enjoy reading.
  3. Xaprb on Introducing MySQL Visual Explain
  4. Wave2.org on A certification worth having…

Sql Server

  1. Euan Garden on SQL Server 2008 – July CTP Available
  2. SQL Server Security, Performance & Tuning (SSQA.net) on
    Best and Worst of having indexed views in SQL 2005
    and on How to gain Disk related performance with few simple steps
  3. Mladen Prajdic of SQLTeam.com Weblog on Best way to Update row if exists, Insert if not
  4. Jeff’s SQL Server Weblog on SELECT * FROM TABLE — except for these columns

Oracle

  1. Luo Donghua, Oracle & Unix: Performance Tuning on How to make “like ‘%xxxx’ also using oracle index
  2. Chen Shapira on Iterations are not enough
  3. Niall Litchfield on An ACE Program
  4. Coskans Approach to Oracle on Nice RMAN command for double checkers or careless ones
  5. Flavio Casetta, Annals of Oracle’s Improbable Errors on A practical example of using global temporary tables within Apex: displaying dbms_output messages
  6. Bilal Hatipoğlu as a newbie to Oracle on Oracle performance analysis – Tracing and performance evaluation and Affect of gathering table stats to decision of CBO and on When the explanation doesn’t sound quite right…
  7. John Russell, Tahiti Views on PL/SQL Web Programming 1.0 – The URL is the API
  8. Hüsnü Şensoy after his Loading Oracle series on A Locking Mechanism in Oracle 10g for Web Applications
  9. Mennan Tekbir, Oracle Experience on How to Find Java(JVM) Version of Oracle Programatically
  10. Frank Zhou of OraQA(Oracle Question and Answer) on How to divide consecutive rows into groups based on the value of the smallest running total in SQL by using 10g model clause
  11. Paul M. Wright, Oracle Forensics on Forensic checksumming on all versions of supported Oracle databases
  12. Alejandro Vargas, as always a very well written document, on Log Miner Implementation Summary
  13. As Justin pointed out Oracle.com refreshed Oracle Events page, and Oracle AppsLab on Check out Oracle Events
  14. And Oracle® Database Express Edition Upgrade Guide 10 g Release 2 ( 10.2.0.3) is published.

Oracle 11g Special

  1. Chris Claterbos of Vlamis Software on Oracle 11g OLAP – What to Expect Part 1
  2. Jurgen Kemmelings of Amis on New in Oracle 11g: PL/SQL Function Result Cache and on New in Oracle 11g: the FOLLOWS Clause in Create Trigger Statement and on :) New in Oracle 11g: wait added to lock table syntax
  3. Yuri van Buren of Blogging About Oracle on Blogging about 11g – Part 4 – Invisible Indexes
  4. Augusto Bott of Pythian Group Blog on Installing Oracle 11g on Ubuntu Linux 7.04
  5. ApEx communities’ new Oracle ACE Patrick Wolf on Oracle 11g: Native Web Services

Oracle ACEs Special

  1. Lutz Hartmann on Oracle 11g New Features book
  2. Jonathan Lewis was on holiday, but still :) on NLS and on Diagnosis
  3. Syed Jaffar Hussain on An interesting situation with Standby database.
  4. Lewis Cunningham of An Expert’s Guide to Oracle Technology on PostgreSQL DB News: PostgreSQL vs MySQL
  5. Mark Rittman of Rittman Mead Consulting on Playing Around with Star Transformations and Bitmap Indexes
  6. Sergio Leunissen on Oracle releases Oracle Linux Test (OLT) Kit
  7. Eddie Awad on Videos on Flashback in Oracle Database 10g and 11g
  8. Doug Burns on Oracle Development Licensing and WorkLoad Metrics

From ArchiveLogs :)

  1. Kevin Closson on Application Server Benchmark Proves PostgreSQL Is The Best Enterprise Database Server. New SPECjAppServer2004 Cost Metric Introduced Too!
  2. Cem Kaner on A first look at the proposed Principles of the Law of Software Contracts
  3. Özgür Macit on Unit Testing
  4. Özay Akdora, another Oracle newbie on DBA vs. Developer
  5. Devrim’s PostgreSQL Diary on Tutorial:PostgreSQL,lighttpd and Serendipity on Ubuntu

And a NoLogging Comment

Small things will make a change, so let’s get into action against the most important treat our world ever seen.

Thanks for reading my Log Buffer and I hope you enjoyed it. Looking forward to meet you again in the Oracle Blogsphere :)