ORACLE11g AWR脚本

set echo off Rem Rem $Header: awrinfo.sql 27-aug-2004.09:40:32 mlfeng Exp $ Rem Rem awrinfo.sql Rem Rem Copyright (c) 2003, 2004, Oracle. All rights reserved. Rem Rem NAME Rem awrinfo.sql - Script to output general AWR information Rem Rem DESCRIPTION Rem This script will output general Automatic Workload Repository Rem (AWR) information such as the size, data distribution, etc. in AWR Rem and SYSAUX. The intended use of this script is for diagnosing Rem abnormalities in AWR and not for diagnosing issues in the database Rem instance. Please look at addmrpt.sql and awrrpt.sql for diagnosing Rem database issues. Rem Rem The following information will be displayed: Rem Rem (I) AWR Snapshot Info gathering Rem (II) Advisor framework diagnostics Rem (III) AWR and ASH Usage Info Gathering Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem mlfeng 08/06/04 - change wrh$_sqlbind Rem veeve 06/04/04 - fix object_space_usage() Rem veeve 04/15/04 - use :select_valid_rows in the Advisor sections Rem mlfeng 02/03/04 - read dbid from dbms_swrf_internal Rem mlfeng 01/16/04 - create/drop package Rem mlfeng 01/06/04 - sysaux occupants information Rem veeve 12/22/03 - add some advisor framework diagnostics Rem veeve 12/11/03 - warning on ash_eflush_status Rem mlfeng 12/05/03 - mlfeng_temp_orderby_statid Rem veeve 12/04/03 - removed ROLLSTAT, added more warnings Rem mlfeng 12/04/03 - changed wrm$_wr_control Rem veeve 12/03/03 - added warnings at the top Rem veeve 12/02/03 - coalesce related sections Rem veeve 12/01/03 - Some optimizations, more info in I.1, pretty print Rem mlfeng 11/26/03 - Ask for report name, output sections. Rem mlfeng 11/26/03 - Created Rem set feedback off verify off timing off echo off; -- -- Prompt the User for the report file name (specify default), -- then begin spooling -- set termout off; column dflt_name new_value dflt_name noprint; select 'awrinfo.txt' dflt_name from dual; set termout on; prompt prompt This script will report general AWR information prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt prompt prompt Specify the Report File Name prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt The default report file name is &dflt_name.. To use this name, prompt press <return> to continue, otherwise enter an alternative. prompt set heading off; set feedback off; column report_name new_value report_name noprint; select 'Using the report name ' || nvl('&&report_name','&dflt_name') , nvl('&&report_name','&dflt_name') report_name from sys.dual; /******************************************* * Create package to be used by awrinfo.sql *******************************************/ create or replace package AWRINFO_UTIL as ----------------------------------------------- -- get_perc_usage - Get the percent space usage ----------------------------------------------- function get_perc_usage( segment_owner varchar2, segment_name varchar2, segment_type varchar2, partition_name varchar2 ) return number; ----------------------------------------------- -- get_type - Get the type for AWR table ----------------------------------------------- function get_type( segment_name varchar2 ) return varchar2; ----------------------------------------------- -- classify_count - Get the classify count ----------------------------------------------- function classify_count( cnt number ) return varchar2; end AWRINFO_UTIL; / show errors; -- Create package body create or replace package body AWRINFO_UTIL as ----------------------------------------------- -- get_perc_usage - Get the percent space usage ----------------------------------------------- function get_perc_usage( segment_owner varchar2, segment_name varchar2, segment_type varchar2, partition_name varchar2 ) return number is space_used number; space_allocated number; chain_pcent number; /* not used */ sample_control number := 100; begin dbms_space.object_space_usage(segment_owner, segment_name, segment_type, sample_control, space_used, space_allocated, chain_pcent, partition_name); return (space_used*100/space_allocated); exception /* skip table if error encountered */ when others then return null; end get_perc_usage; ----------------------------------------------- -- get_type - Get the type for AWR table ----------------------------------------------- function get_type( segment_name varchar2 ) return varchar2 is begin return (case when segment_name like '%ACTIVE_SESSION_HIST%' then 'ASH' when segment_name like 'WRH$_ASH_BL_PK' then 'ASH' when segment_name like '%EVENT%' then 'EVENTS' when segment_name like '%ENQ%' then 'EVENTS' when segment_name like '%LIBRARYCACHE%' then 'EVENTS' when segment_name like '%FILE%' then 'SPACE' when segment_name like '%TEMP%' then 'SPACE' when segment_name like '%TABLESPACE%' then 'SPACE' when segment_name like '%SEG%' then 'SPACE' when segment_name like '%SQL_BIND%' then 'SQLBIND' when segment_name like '%SQLTEXT%' then 'SQLTEXT' when segment_name like '%SQL_PLAN%' then 'SQLPLAN' when segment_name like '%SQL%' then 'SQL' when segment_name like '%OPTIMIZER%' then 'SQL' when segment_name like '%DLM%' then 'RAC' when segment_name like '%CR_BLOCK_SERVER%' then 'RAC' when segment_name like '%CURRENT_BLOCK_SERVER%' then 'RAC' when segment_name like '%CLASS_CACHE_TRANSFER%' then 'RAC' else 'FIXED' end); end get_type; ----------------------------------------------- -- classify_count - Get the classify count ----------------------------------------------- function classify_count( cnt number ) return varchar2 is blim number; elim number; begin blim := trunc(cnt/5) * 5; elim := (trunc((cnt + 5)/5) * 5) - 1; return( trim(to_char(blim, '0990')) || ' - ' || trim(to_char(elim, '0990')) ); end classify_count; end; / show errors; set termout on; spool &report_name set linesize 110 set pagesize 50 set serveroutput on execute dbms_output.enable(1000000); alter session set nls_date_format = 'HH24:MI:SS (MM/DD)'; alter session set nls_timestamp_format = 'HH24:MI:SS (MM/DD)'; set echo off variable dbid number; variable instid number; begin :dbid := dbms_swrf_internal.get_awr_dbid; select instance_number into :instid from v$instance; end; / set echo off column db_id format a12 just r; column name format a20 column platform_name format a30 column host_platform format a40 wrap column startup_time format a17 column inst format 9999 prompt ~~~~~~~~~~~~~~~ prompt AWR INFO Report prompt ~~~~~~~~~~~~~~~ set heading off select 'Report generated at', to_char(systimestamp, 'HH24:MI:SS "on" Mon DD, YYYY ( ') || trim(to_char(systimestamp, 'Day')) || to_char(systimestamp, ' ) "in Timezone" TZR') from dual; col attr_nl newline; select 'Warning: CATPROC Not Valid! ', '-------------------------------------------------------------------------------- ' attr_nl, rpad('Status: ', 30) || r.status || ' (1 => VALID) ' attr_