Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Advisor Central - ADDM
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Some of the named advisors are actual advisors. Some of the advisors are management interfaces to specific settings. How does one access these advisors? The advisors and Checkers can be reached via the Advisor Central link on the home page of Database Control (look near the bottom center of the home page).

 

At Advisor Central, links at the top of the page will send the DBA to the top-level pages for the selected topic. The advisors include:

  • ADDM – analyze current or past performance

  • SQL Advisors – SQL Access, SQL Tuning, and SQL Repair Advisors

  • Memory Advisors – SGA and PGA advisors

  • Automatic Undo Management – Undo Advisor (retention and tablespace sizing advice)

  • MTTR Advisor – advice on instance recovery, media recovery and flash recovery

  • SQL Performance Analyzer – Optimizer Upgrade Simulation, Parameter Change, Guided Workflow, and existing tasks

  • Data Recovery Advisor – view and manage failures

  • Segment Advisor – Automatic Segment Advisor information, get advice on tablespaces or schema objects

ADDM

ADDM, or the Automatic Database Diagnostic Monitor, works in conjunction with the Automatic Workload Repository, or AWR. The AWR stores performance statistics, and these statistics are then used for problem detection and self-tuning. Much like STATSPACK, which is essentially what it is but better, snapshots are taken at regular intervals.

 

After the performance data has been collected, ADDM analyzes it. AWR is the hunter-gatherer, and ADDM is the thinker. The built-in functionality includes automated tasks which run in maintenance windows. One well-known maintenance window is the Oracle after-hours GATHER_STATS_JOB. The name of the job is GATHER_STATS_JOB .

 

Referencing the Performance Tuning Guide, this job is “created automatically at database creation time and is managed by the Scheduler. The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.”

 

The automated tasks infrastructure, known as AutoTask, schedules routine maintenance tasks. These tasks include steps needed to perform or update optimizer statistics gathering, the Automatic Segment Advisor, and the SQL Tuning Advisor. A list of common problems ADDM can detect include:

  • CPU bottlenecks

  • Poor connection management

  • Excessive parsing

  • Lock contention

  • I/O capacity

  • Undersizing of Oracle memory structures

  • High load SQL statements

  • High PL/SQL and Java time

  • High checkpoint load and causes

  • RAC-specific issues

Running an ADDM or AWR report (the difference in the names is analogous to how users blur the difference between database and instance at times) can be accomplished via Database Control click and point functionality or by manually running one or more SQL scripts located in $ORACLE_HOME/rdbms/admin. 

 

The Diagnostic Pack scripts, of which ADDM falls under for licensing, can all be run as a command-line API. Many will prompt for input related to instance, dates, times, output format and snapshot coverage. The script file names and purposes are shown below. All scripts are .sql files.

 

Name

Purpose

awrrpt

Creates the main AWR report, based on STATSPACK

awrrpti

Comparison between snapshots

addmrtp

Runs ADDM analysis on pair of AWR snapshots

addmrpti

Same as above, but used for RAC instances

ashrpt

Runs the Active Session History report, calls ashrpti

ashrpti

Worker script that supports ashrpt

awrddrpt

Runs Workload Respository Compare Periods report

awrddrpi

Worker script that supports awrddrpt

awrsqrpt

Runs Workload for a particular SQL statement

awrsqrpi

Worker script that supports awrsqrpt

awrextr

Extracts AWR info, for use with/by Oracle Support

awrload

Loads AWR data, for use with/by Oracle Support

awrinfo

Outputs general AWR info

spawrrac

Server Performance RAC report

Table 8.2:  ADDM Scripts

 

The pattern on the scripts is that a script ending in -rpt typically defaults to the current DBID and instance, collects information via prompts, and passes that input to its corresponding -rpi script. The -rpi scripts can be run directly, but one will have to sort out the required/expected parameters. Some of the scripts are for overall and some are for comparison between snapshots or the repository. Many of the scripts also expect to be run as the SYS user.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.