DBMS_SPACE.OBJECT_GROWTH_TREND

2013/04/18 by 1 Comment

SELECT *
FROM table(
DBMS_SPACE.OBJECT_GROWTH_TREND (
object_owner =>’SYS’,
object_name =>’SIZET’,
object_type =>’TABLE’,
start_time =>NULL,
end_time =>NULL,
interval =>to_dsinterval(‘180 00:00:00’) ,
skip_interpolated => ‘TRUE’,
timeout_seconds =>NULL,
single_datapoint_flag =>’TRUE’) )
order by 1
/

 

 

SQL> SELECT *
2 FROM
3 table(
4 DBMS_SPACE.OBJECT_GROWTH_TREND (
5 object_owner =>’BILLING’,
6 object_name =>’TBL_BACKUPSMTPFAX’,
7 object_type =>’TABLE’,
8 start_time =>NULL,
9 end_time =>NULL,
10 interval =>to_dsinterval(’10 00:00:00′) ,
11 skip_interpolated => ‘FALSE’,
12 timeout_seconds =>NULL,
13 single_datapoint_flag =>’TRUE’))
14 order by 1
15 /

TIMEPOINT SPAC
E_USAGE SPACE_ALLOC QUALITY
————————————————————————— —-
——- ———– ——————–
22-6月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
02-7月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
12-7月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
22-7月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
01-8月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
11-8月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
21-8月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
31-8月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
10-9月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
20-9月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
30-9月 -14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
10-10月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
20-10月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
30-10月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
09-11月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
19-11月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
29-11月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
09-12月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
19-12月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
29-12月-14 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
08-1月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
18-1月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
28-1月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
07-2月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
17-2月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
27-2月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
09-3月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
19-3月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
29-3月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
08-4月 -15 05.56.09.899000 下午 83
0497586 830497586 INTERPOLATED
18-4月 -15 05.56.09.899000 下午 644
7606206 6820659200 GOOD
28-4月 -15 05.56.09.899000 下午 648
1580653 6859524162 PROJECTED
08-5月 -15 05.56.09.899000 下午 651
5555100 6898389124 PROJECTED
18-5月 -15 05.56.09.899000 下午 654
9529548 6937254086 PROJECTED
28-5月 -15 05.56.09.899000 下午 658
3503995 6976119048 PROJECTED
07-6月 -15 05.56.09.899000 下午 661
7478442 7014984010 PROJECTED

已选择36行。

Filed Under: Oracle, Oracle脚本script

【脚本】检测CPU计算能力

2013/04/10 by Leave a Comment

以下脚本可以用于Oracle db系统上线前检测单颗CPu运算能力频率：



SET SERVEROUTPUT ON



SET TIMING ON

DECLARE
  n NUMBER := 0;
BEGIN
  FOR f IN 1..10000000
  LOOP
    n := MOD (n,999999) + SQRT (f);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR (n,'999999.99'));
END;
/

create table cpu_speed tablespace users pctfree 99 pctused 0 cache as select * from dba_objects where rownum<=30000;





alter session set optimizer_dynamic_sampling=0;

set autotrace on;
set timing on;
select sum(object_id) from cpu_speed;
select sum(object_id) from cpu_speed;


set autotrace off;

set serveroutput on;

alter session set nls_date_format='DD-MM-YY hh24:mi:ss';
exec DBMS_STATS.CREATE_STAT_TABLE ('SYS','sys_stats');



BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS ('interval',interval => 1, stattab => 'sys_stats', statid => 'OLTP');
END;
/


exec dbms_lock.sleep(60);

DECLARE
  STATUS VARCHAR2(20);
  DSTART DATE;
  DSTOP  DATE;
  PVALUE NUMBER;
  PNAME  VARCHAR2(30);
BEGIN
  PNAME := 'cpuspeed';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('status : ' || status);
  DBMS_OUTPUT.PUT_LINE('cpu in mhz : ' || pvalue);
  DBMS_OUTPUT.PUT_LINE('start :' || dstart);
  DBMS_OUTPUT.PUT_LINE('stop :' || dstop);
  PNAME := 'sreadtim';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('single block readtime in ms : ' || pvalue);
  PNAME := 'mreadtim';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('multiblock readtime in ms : ' || pvalue);
  PNAME := 'mbrc';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('average multiblock readcount: ' || pvalue);
END;
/
Filed Under: Oracle, Oracle脚本script

SQL Performance Analyzer SPA常用脚本汇总

2013/03/10 by 4 Comments

SPA常用脚本汇总

附件为 一个SPA报告 spa_buffergets_summary

 

SQL 性能分析器 SQL Performance Analyzer SPA

Oracle Database 11g 引入了 SQL 性能分析器；使用该工具可以准确地评估更改对组成工作量的 SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对 SQL 查询工作量的性能影响。这种功能可向 DBA 提供有关 SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。这样一来，您就可以执行诸如以下操作的操作：在测试环境中进行更改，以确定数据库升级是否会改进工作量性能。

 

  1. 11g 的新增功能
  2. 目标用户：DBA、QA、应用程序开发人员
  3. 帮助预测系统更改对 SQL 工作量响应时间的影响
  4. 建立不同版本的 SQL 工作量性能（即 SQL 执行计划和执行统计信息）
  5. 以串行方式执行 SQL（不考虑并发性）
  6. 分析性能差异
  7. 提供对单个 SQL 的细粒度性能分析
  8. 与 SQL 优化指导集成在一起以优化回归

SQL 性能分析器：使用情形
SQL 性能分析器可用于预测和防止会影响 SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：

  1. 数据库升级
  2. 实施优化建议
  3. 更改方案
  4. 收集统计信息
  5. 更改数据库参数
  6. 更改操作系统和硬件

 

DBA 甚至可以使用 SQL 性能分析器为最复杂的环境预测先期更改导致的 SQL 性能更改。例如，随着应用程序在开发周期中的变化，数据库应用程序开发人员可以测试对方案、 数据库对象和重写应用程序的更改，以减轻任何潜在的性能影响。
使用 SQL 性能分析器还可以比较 SQL 性能统计信息。

SQL 性能分析器：概要

1.  收集 SQL：在这个阶段中，将收集用于表示生产系统中的 SQL 工作量的 SQL 语句集。可以使用 SQL 优化集或自动工作量资料档案库 (AWR) 来捕获要传送的信息。因为 AWR 本质上是捕获高负载的 SQL，所以应考虑修改默认的 AWR 快照设置和捕获的顶级 SQL，以确保 AWR 捕获最大数量的 SQL 语句。这可以确保捕获更加完整的 SQL 工作量。

2.  传送：在这个阶段中，应将得到的工作量结果传送到测试系统。从生产系统导出 STS，然后将 STS 导入到测试系统。

3.  计算“之前版本”性能：在进行任何更改之前，执行 SQL 语句，收集评估将来的更改对工作量性能的可能影响所需的基线信息。在此阶段收集的信息给出了系统工作量当前状态的一个快照。性能数据包括：

-执行计划（如由解释计划生成的计划）
-执行统计信息（如由占用时间、缓冲获取次数、磁盘读取次数和已处理的行数组成的信息）

4. 进行更改：获得了之前版本数据后，可以实施计划的更改，然后开始查看对性能的影响。

5.  计算“之后版本”性能：在数据库环境中进行了更改之后才执行此步骤。SQL 工作量的每个语句都在虚拟执行（仅收集统计信息）模式下运行，收集与步骤 3 所捕获的信息相同的信息。

6.  比较和分析 SQL 性能：在获得了两个版本的 SQL 工作量性能数据后，可以通过比较之后版本与之前版本的数据来进行性能分析。比较的根据是执行统计信息，如所用时间、CPU 时间和缓冲区获取次数等。

7.  优化回归的 SQL：在此阶段中，已经准确地确认了哪些 SQL 语句在进行数据库更改时可能导致性能问题。在此阶段中可以使用任何一种数据库工具来优化系统。例如，可以对确认的语句使用 SQL 优化指导或访问指导，然后实施相应的建议。也可以使用在步骤 3 中捕获的计划植入 SQL 计划管理 (SPM) 以确保计划保持不变。在实施了任何优化操作后，应重复该过程来创建新的之后版本，然后分析性能差异以确保新的性能是可接受的。
默认情况下SPA若涉及到DML语句则只有查询部分Query会被执行，但是貌似是从11.2开始可以执行完全的DML了，需要加入参数EXECUTE_FULLDML，但是该参数目前有一些BUG:

Bug 10428438 : WITH EXECUTE_FULLDML ROWS IS ALWAYS SET TO 0 11.2.0.1

Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3

 

By default, only the query portion of DMLs is executed. Using APIs, you can execute the full DML by using the EXECUTE_FULLDML task parameter.EXECUTE_FULLDML when set to TRUE executes DML statement fully, including acquiring row locks and modifying rows; When EXECUTE_FULLDML is set to FALSE (the default value is false) to execute only the query part of the DML without modifying data. When TRUE, SQL Performance Analyzer will issue a rollback following DML execution to prevent persistent changes from being made by the DML. So SPA does not make make any change to the data in the tables.

 

执行方法如下：

 

execute DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name   => 'TASK_21137', -
                                               parameter   => 'EXECUTE_FULLDML', -
                                               value       => 'TRUE');

 

 

 

 

从cursor cache中收集tuning set, 持续12分钟，间隔5秒钟

 

 

begin
DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'MAC_SPA');
dbms_sqltune.capture_cursor_cache_sqlset(
sqlset_name => 'MAC_SPA' ,
time_limit => 12*60,
repeat_interval => 5);
end ;
/

basic_filter=> q'# module like 'DWH_TEST%' and sql_text not like '%applicat%' and parsing_schema_name in ('APPS') #'

basic_filter   => 'sql_text LIKE ''%my_objects%'' and parsing_schema_name = ''SPA_TEST_USER''',

==>过滤条件使用

 

从当前cursor cache中匹配条件 获得SQLset ROW

 

 

SELECT sql_id, sql_text 
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500')) 
ORDER BY sql_id;

SELECT * 
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j'''));

 DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value(P) 
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;

  -- Process each statement (or pass cursor to load_sqlset).

  CLOSE cur;
END;
/

 -- create the tuning set
EXEC DBMS_SQLTUNE.CREATE_SQLSET('MAC_SPA');
-- populate the tuning set from the cursor cache
DECLARE
 cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN cur FOR
   SELECT VALUE(P)
     FROM table(
       DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
         'parsing_schema_name <> ''SYS'' AND elapsed_time > 5000000',
          NULL, NULL, NULL, NULL, 1, NULL,
         'ALL')) P;

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MAC_SPA',
                        populate_cursor => cur);

END;
/

 

 

从AWR快照中加载SQLset ROW到SQL TUNING SET

 

 

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(dbms_sqltune.select_workload_repository(4146,4161)) P;

  -- Process each statement (or pass cursor to load_sqlset)
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MAC_SPA',
                        populate_cursor => cur);
  CLOSE cur;
END;
/

 

 

 

将SQL TUNING SET Pack到表中：

 

 

set echo on
select name,statement_count from dba_sqlset;

drop table maclean.pack_sqlset purge;

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('PACK_SQLSET','MACLEAN');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET('MAC_SPA','SYS','PACK_SQLSET','MACLEAN');

SQL> desc maclean.pack_sqlset;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 OWNER                                              VARCHAR2(30)
 DESCRIPTION                                        VARCHAR2(256)
 SQL_ID                                             VARCHAR2(13)
 FORCE_MATCHING_SIGNATURE                           NUMBER
 SQL_TEXT                                           CLOB
 PARSING_SCHEMA_NAME                                VARCHAR2(30)
 BIND_DATA                                          RAW(2000)
 BIND_LIST                                          SQL_BIND_SET
 MODULE                                             VARCHAR2(48)
 ACTION                                             VARCHAR2(32)
 ELAPSED_TIME                                       NUMBER
 CPU_TIME                                           NUMBER
 BUFFER_GETS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 ROWS_PROCESSED                                     NUMBER
 FETCHES                                            NUMBER
 EXECUTIONS                                         NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
 OPTIMIZER_COST                                     NUMBER
 OPTIMIZER_ENV                                      RAW(1000)
 PRIORITY                                           NUMBER
 COMMAND_TYPE                                       NUMBER
 FIRST_LOAD_TIME                                    VARCHAR2(19)
 STAT_PERIOD                                        NUMBER
 ACTIVE_STAT_PERIOD                                 NUMBER
 OTHER                                              CLOB
 PLAN_HASH_VALUE                                    NUMBER
 PLAN                                               SQL_PLAN_TABLE_TYPE
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             BLOB
 SPARE4                                             CLOB

 

 

 

将测试对应 schema的数据和 上述PACK TABLE 导出导入到 目标测试库中：

 

set echo on
exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('MAC_SPA','SYS',TRUE,'PACK_SQLSET','MACLEAN');
alter system flush buffer_cache;
alter system flush shared_pool;

 

 

创建SPA任务 并运行;

 

 

var sts_task varchar2(64);
exec :sts_task:= dbms_sqlpa.create_analysis_task(task_name => '10g_11g_spa',description => 'experiment for 10gR2 to 11gR2 upgrade',sqlset_name=> 'MAC_SPA');

PL/SQL procedure successfully completed.

var exe_task varchar2(64);
exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'10g_11g_spa',execution_name=>'10g_trail',execution_type=>'CONVERT SQLSET',execution_desc=>'10g sql trail');

var exe_task varchar2(64);
exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'10g_11g_spa',execution_name=>'11g_trail',execution_type=>'TEST EXECUTE',execution_desc=>'11g sql trail');

 

 

 

执行任务比较

 

 

 

比较CPU_TIME
EXEC dbms_sqlpa.execute_analysis_task( -
  task_name => '10g_11g_spa', -
  execution_name => 'compare_10g_112_cpu', -
  execution_type => 'COMPARE PERFORMANCE', -
  execution_params => dbms_advisor.arglist('COMPARISON_METRIC','CPU_TIME','EXECUTION_NAME1','10g_trail','EXECUTION_NAME2','11g_trail'), -
  execution_desc => 'Compare 10g SQL Trace Performance to 11g Test-Execute for CPU_TIME')
  /

比较BUFFER_GETS
EXEC dbms_sqlpa.execute_analysis_task( -
  task_name => '10g_11g_spa', -
  execution_name => 'compare_10g_112_buffergets', -
  execution_type => 'COMPARE PERFORMANCE', -
  execution_params => dbms_advisor.arglist('COMPARISON_METRIC','BUFFER_GETS','EXECUTION_NAME1','10g_trail','EXECUTION_NAME2','11g_trail'), -
  execution_desc => 'Compare 10g SQL Trace Performance to 11g Test-Execute for BUFFER_GETS')
  /

比较实际执行时长 

begin 
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( 
task_name => 'SPA_TEST', 
execution_type => 'COMPARE PERFORMANCE', 
execution_name => 'Compare_elapsed_time', 
execution_params => dbms_advisor.arglist('execution_name1', '10g_trail', 'execution_name2', '11g_trail', 'comparison_metric', 'elapsed_time') ); 
end; 
/

比较物理读

begin 
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( 
task_name => '10g_11g_spa', 
execution_type => 'COMPARE PERFORMANCE', 
execution_name => 'Compare_physical_reads0', 
execution_params => dbms_advisor.arglist('execution_name1', '10g_trail', 'execution_name2', '11g_trail', 'comparison_metric', 'disk_reads') ); 
end; 
/

Set the comparison_metric parameter to specify an expression of execution 
statistics to use in the performance impact analysis. Possible values include 
the following metrics or any combination of them: elapsed_time (default), 
cpu_time, buffer_gets, disk_reads, direct_writes, and optimizer_cost.

 

 

 

获得SPA报告:

 

 

 

set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off 
spool spa_report_elapsed_time.html 
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time') FROM dual; 
spool off

产生buffergets 比较report    

set heading off long 100000000 longchunksize 10000 echo off;
set linesize 1000 trimspool on;
spool buffergets_summary.html
select xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa',
                                                'html',
                                                'typical',
                                                'all',
                                                null,
                                                100,
                                                'compare_10g_112_buffergets')).getclobval(0,0)
from dual;
spool off

产生errors比较report 
spool errors_summary.html
select xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa',
                                                'html',
                                                'errors',
                                                'summary',
                                                null,
                                                100,
                                                '11g_trail')).getclobval(0,0)
from dual;
spool off

产生unsupport比较report 
spool unsuppor_all.html
select xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa',
                                                'html',
                                                'unsupported',
                                                'all',
                                                null,
                                                100,
                                                '11g_trail')).getclobval(0,0)
from dual;
spool off

 

 

 

 

 

execution_type
Type of the action to perform by the function. If NULL it will default to the value of the DEFAULT_EXECUTION_TYPE parameter. Possible values are:
[TEST] EXECUTE – test-execute every SQL statement and collect its execution plans and execution statistics. The resulting plans and statistics will be stored in the advisor framework. This is default.
EXPLAIN PLAN – generate explain plan for every statement in the SQL workload. This is similar to the EXPLAIN PLAN command. The resulting plans will be stored in the advisor framework in association with the task.
COMPARE [PERFORMANCE] – analyze and compare two versions of SQL performance data. The performance data is generated by test-executing or generating explain plan of the SQL statements. Use this option when two executions of type EXPLAIN_PLAN or TEST_EXECUTE already exist in the task
CONVERT SQLSET – used to read the statistics captured in a SQL Tuning Set and model them as a task execution. This can be used when you wish to avoid executing the SQL statements because valid data for the experiment already exists in the SQL Tuning Set.

 

 

For 9i Upgrade to 10g

 

 

exec dbms_stats.gather_system_stats(gathering_mode=>'NOWORKLOAD');

alter system set "_optim_peek_user_binds"=false;           ==> 禁用BIND PEEK特性，该特性在10g中有

exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' );
commit;

9i 

?/rdbms/admin/dbmssupp

exec dbms_support.start_trace(binds=>TRUE, waits=> FALSE);

exec dbms_support.stop_trace;

exec dbms_support.start_trace_in_session(sid=>sid,serial=>ser, binds=>TRUE, waits=>FALSE);

select sid,serial# from v$SESSION WHERE ... ;

exec dbms_support.stop_trace_in_session(sid=>SID,serial=>ser);

create table mapping_table tablespace USERS as
select object_id id, owner, substr(object_name, 1, 30) name
  from dba_objects
 where object_type not in ('CONSUMER GROUP',
                           'EVALUATION CONTEXT',
                           'FUNCTION',
                           'INDEXTYPE',
                           'JAVA CLASS',
                           'JAVA DATA',
                           'JAVA RESOURCE',
                           'LIBRARY',
                           'LOB',
                           'OPERATOR',
                           'PACKAGE',
                           'PACKAGE BODY',
                           'PROCEDURE',
                           'QUEUE',
                           'RESOURCE PLAN',
                           'SYNONYM',
                           'TRIGGER',
                           'TYPE',
                           'TYPE BODY')
union all
select user_id id, username owner, null name from dba_users;

declare
  mycur dbms_sqltune.sqlset_cursor;
begin
  dbms_sqltune.create_sqlset('9i_prod_wkld');
    open mycur for
      select value(p)
      from table(dbms_sqltune.select_sql_trace(
                   directory=>'SPADIR',
                   file_name=>'%trc',
                   mapping_table_name => 'MAPPING_TABLE',
                   select_mode => dbms_sqltune.single_execution)) p;
  dbms_sqltune.load_sqlset(
    sqlset_name => '9i_prod_wkld',
    populate_cursor => mycur,
    commit_rows => 1000);

  close mycur;
end;
/

create user spadba identified by oracle;
grant dba to spadba;
grant all on dbms_sqlpa to spadba;

create public database link to10g connect to spadba identified by oracle using 'STRINGS';

var sts_task varchar2(64);
exec :sts_task:= dbms_sqlpa.create_analysis_task(task_name => '9i_11g_spa1',description => 'experiment for 9i to 11gR2 upgrade',sqlset_name=> '9i_prod_wkld');

var exe_task varchar2(64);
exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'9i_11g_spa1',execution_name=>'9i_trail1',execution_type=>'CONVERT SQLSET',execution_desc=>'9i sql trail generated from sts');

dbms_sqlpa.execute_analysis_task(task_name=>'9i_11g_spa1',execution_name=>'10g_trail1',execution_type=>'TEST EXECUTE',execution_desc=>'10g trail test',-
execution_params=>dbms_advisor.arglist('DATABASE_LINK','DBLINKNAME'));

select sofar,totalwork from V$ADVISOR_PROGRESS where task_id=<TID>;
Filed Under: Oracle, Oracle 11g新特性, Oracle SQL性能调优, Oracle脚本script Tagged With: ,

Script:挖掘AWR实现查询SCN历史增长走势

2012/11/18 by 2 Comments

AWR中记录了快照时间内calls to kcmgas的统计值，calls to kcmgas的意义在于通过递归调用获得一个新的SCN，该统计值可以看做SCN增长速度的主要依据，通过挖掘AWR可以了解SCN的增长走势，对于我们诊断SCN HEADROOM问题有所帮助：

 

 

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."

WITH sysstat AS
 (select sn.begin_interval_time begin_interval_time,
         sn.end_interval_time end_interval_time,
         ss.stat_name stat_name,
         ss.value e_value,
         lag(ss.value, 1) over(order by ss.snap_id) b_value
    from dba_hist_sysstat ss, dba_hist_snapshot sn
   where  ss.snap_id = sn.snap_id
     and ss.dbid = sn.dbid
     and ss.instance_number = sn.instance_number
     and ss.dbid = (select dbid from v$database)
     and ss.instance_number = (select instance_number from v$instance)
     and ss.stat_name = 'calls to kcmgas')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/

 

示例输出如下， 可以看到最近快照中SCN增速迅速变大：

 

 


DATE_TIME                                STAT_NAME                    PER_SEC
---------------------------------------- ------------------------- ----------
11/27/09_01_00_02_01                     calls to kcmgas                    0
11/27/09_02_01_03_00                     calls to kcmgas                    0
11/27/09_03_00_04_00                     calls to kcmgas                    0
11/27/09_04_00_05_00                     calls to kcmgas                    0
11/27/09_05_00_06_00                     calls to kcmgas                    0
11/27/09_06_00_07_00                     calls to kcmgas                    0
11/27/09_07_00_08_00                     calls to kcmgas                    0
11/27/09_08_00_09_00                     calls to kcmgas                    0
11/27/09_09_00_10_00                     calls to kcmgas                    0
11/27/09_10_00_11_00                     calls to kcmgas                    0
11/27/09_11_00_12_00                     calls to kcmgas                    0
11/27/09_12_00_13_00                     calls to kcmgas                    0
11/27/09_13_00_14_00                     calls to kcmgas                    0
11/27/09_14_00_15_00                     calls to kcmgas                    0
11/27/09_15_00_16_00                     calls to kcmgas                    0
11/27/09_16_00_17_00                     calls to kcmgas                    0
11/27/09_17_00_18_00                     calls to kcmgas                    0
11/27/09_18_00_19_00                     calls to kcmgas                    0
11/27/09_19_00_20_00                     calls to kcmgas                    0
11/27/09_20_00_21_00                     calls to kcmgas                    0
11/27/09_21_00_22_00                     calls to kcmgas                    1
11/27/09_22_00_23_00                     calls to kcmgas                    0
11/27/09_23_00_00_00                     calls to kcmgas                    0
11/28/09_00_00_01_00                     calls to kcmgas                    0
11/28/09_01_00_02_00                     calls to kcmgas                    0
11/28/09_02_00_03_00                     calls to kcmgas                    0
11/28/09_03_00_04_00                     calls to kcmgas                    0
11/28/09_04_00_05_00                     calls to kcmgas                    0
11/28/09_05_00_06_00                     calls to kcmgas                    1
11/28/09_06_00_07_00                     calls to kcmgas                    0
11/28/09_07_00_08_00                     calls to kcmgas                    0
11/28/09_08_00_09_00                     calls to kcmgas                    0
11/28/09_09_00_10_00                     calls to kcmgas                    0
11/28/09_10_00_11_00                     calls to kcmgas                    1
11/28/09_11_00_12_00                     calls to kcmgas                    0
11/28/09_12_00_13_00                     calls to kcmgas                    0
11/28/09_13_00_14_00                     calls to kcmgas                    0
11/28/09_14_00_15_00                     calls to kcmgas                    0
11/28/09_15_00_16_00                     calls to kcmgas                    0
11/28/09_16_00_17_00                     calls to kcmgas                    0
11/28/09_17_00_18_00                     calls to kcmgas                    0
11/28/09_18_00_19_00                     calls to kcmgas                    0
11/28/09_19_00_20_00                     calls to kcmgas                    0
11/28/09_20_00_21_00                     calls to kcmgas                    0
11/28/09_21_00_22_00                     calls to kcmgas                    0
11/28/09_22_00_23_00                     calls to kcmgas                    1
11/28/09_23_00_00_00                     calls to kcmgas                    0
11/29/09_00_00_01_00                     calls to kcmgas                    0
11/29/09_01_00_02_00                     calls to kcmgas                    0
11/29/09_02_00_03_00                     calls to kcmgas                    0
11/29/09_03_00_04_00                     calls to kcmgas                    0
11/29/09_04_00_05_00                     calls to kcmgas                    0
11/29/09_05_00_06_00                     calls to kcmgas                    1
11/29/09_06_00_07_00                     calls to kcmgas                    0
11/29/09_07_00_08_00                     calls to kcmgas                    0
11/29/09_08_00_09_00                     calls to kcmgas                    0
11/29/09_09_00_10_00                     calls to kcmgas                    0
11/29/09_10_00_11_00                     calls to kcmgas                    1
11/29/09_11_00_12_00                     calls to kcmgas                    0
11/29/09_12_00_13_00                     calls to kcmgas                    0
11/29/09_13_00_14_00                     calls to kcmgas                    0
11/29/09_14_00_15_00                     calls to kcmgas                    1
11/29/09_15_00_16_00                     calls to kcmgas                    0
11/29/09_16_00_17_00                     calls to kcmgas                    0
11/29/09_17_00_18_00                     calls to kcmgas                    0
11/29/09_18_00_19_00                     calls to kcmgas                    0
11/29/09_19_00_20_00                     calls to kcmgas                    0
11/29/09_20_00_21_00                     calls to kcmgas                    0
11/29/09_21_00_22_00                     calls to kcmgas                    0
11/29/09_22_00_23_00                     calls to kcmgas                    1
11/29/09_23_00_00_00                     calls to kcmgas                    0
11/30/09_00_00_01_00                     calls to kcmgas                    0
11/30/09_01_00_02_00                     calls to kcmgas                    0
11/30/09_02_00_03_00                     calls to kcmgas                    0
11/30/09_03_00_04_00                     calls to kcmgas                    0
11/30/09_04_00_05_00                     calls to kcmgas                    0
11/30/09_05_00_06_00                     calls to kcmgas                    0
11/30/09_06_00_07_00                     calls to kcmgas                    0
11/30/09_07_00_08_00                     calls to kcmgas                    0
11/30/09_08_00_09_00                     calls to kcmgas                    0
11/30/09_09_00_10_00                     calls to kcmgas                    0
11/30/09_10_00_11_00                     calls to kcmgas                    0
11/30/09_11_00_12_00                     calls to kcmgas                    0
11/30/09_12_00_13_00                     calls to kcmgas                    0
11/30/09_13_00_14_00                     calls to kcmgas                    0
11/30/09_14_00_15_00                     calls to kcmgas                    0
11/30/09_15_00_16_00                     calls to kcmgas                    0
11/30/09_16_00_17_00                     calls to kcmgas                    0
11/30/09_17_00_18_00                     calls to kcmgas                    0
11/30/09_18_00_19_00                     calls to kcmgas                    0
11/30/09_19_00_20_00                     calls to kcmgas                    0
11/30/09_20_00_21_00                     calls to kcmgas                    0
11/30/09_21_00_22_00                     calls to kcmgas                    1
11/30/09_22_00_23_00                     calls to kcmgas                    1
11/30/09_23_00_00_00                     calls to kcmgas                    0
12/01/09_00_00_01_00                     calls to kcmgas                    0
12/01/09_01_00_02_01                     calls to kcmgas                    0
12/01/09_02_01_03_00                     calls to kcmgas                    0
12/01/09_03_00_04_00                     calls to kcmgas                    0
12/01/09_04_00_05_00                     calls to kcmgas                    0
12/01/09_05_00_06_00                     calls to kcmgas                    0
12/01/09_06_00_07_00                     calls to kcmgas                    0
12/01/09_07_00_08_00                     calls to kcmgas                    0
12/01/09_08_00_09_00                     calls to kcmgas                    0
12/01/09_09_00_10_00                     calls to kcmgas                    0
12/01/09_10_00_11_00                     calls to kcmgas                    0
12/01/09_11_00_12_00                     calls to kcmgas                    0
12/01/09_12_00_13_00                     calls to kcmgas                    0
12/01/09_13_00_14_00                     calls to kcmgas                    0
12/01/09_14_00_15_00                     calls to kcmgas                    0
12/01/09_15_00_16_00                     calls to kcmgas                    0
12/01/09_16_00_17_00                     calls to kcmgas                    0
12/01/09_17_00_18_00                     calls to kcmgas                    0
12/01/09_18_00_19_00                     calls to kcmgas                    0
12/01/09_19_00_20_00                     calls to kcmgas                    0
12/01/09_20_00_21_00                     calls to kcmgas                    0
12/01/09_21_00_22_00                     calls to kcmgas                    0
12/01/09_22_00_23_00                     calls to kcmgas                    1
12/01/09_23_00_00_00                     calls to kcmgas                    0
12/02/09_00_00_01_00                     calls to kcmgas                    0
12/02/09_01_00_02_00                     calls to kcmgas                    0
12/02/09_02_00_03_00                     calls to kcmgas                    0
12/02/09_03_00_04_00                     calls to kcmgas                    0
12/02/09_04_00_05_00                     calls to kcmgas                    0
12/02/09_05_00_06_00                     calls to kcmgas                    0
12/02/09_06_00_07_00                     calls to kcmgas                    0
12/02/09_07_00_08_00                     calls to kcmgas                    0
12/02/09_08_00_09_00                     calls to kcmgas                    0
12/02/09_09_00_10_00                     calls to kcmgas                    0
12/02/09_10_00_11_00                     calls to kcmgas                    0
12/02/09_11_00_12_00                     calls to kcmgas                    0
12/02/09_12_00_13_00                     calls to kcmgas                    0
12/02/09_13_00_14_00                     calls to kcmgas                    0
12/02/09_14_00_15_00                     calls to kcmgas                    0
12/02/09_15_00_16_00                     calls to kcmgas                    0
12/02/09_16_00_17_00                     calls to kcmgas                    0
12/02/09_17_00_18_00                     calls to kcmgas                    0
12/02/09_18_00_19_00                     calls to kcmgas                    0
12/02/09_19_00_20_00                     calls to kcmgas                    0
12/02/09_20_00_21_00                     calls to kcmgas                    0
12/02/09_21_00_22_00                     calls to kcmgas                    1
12/02/09_22_00_23_00                     calls to kcmgas                    1
12/02/09_23_00_00_00                     calls to kcmgas                    0
12/03/09_00_00_01_00                     calls to kcmgas                    1
12/03/09_01_00_02_00                     calls to kcmgas                    0
12/03/09_02_00_03_00                     calls to kcmgas                    0
12/03/09_03_00_04_00                     calls to kcmgas                    0
12/03/09_04_00_05_00                     calls to kcmgas                    0
12/03/09_05_00_06_00                     calls to kcmgas                    0
12/03/09_06_00_07_00                     calls to kcmgas                    0
12/03/09_07_00_08_00                     calls to kcmgas                    0
12/03/09_08_00_09_00                     calls to kcmgas                    0
12/03/09_09_00_10_00                     calls to kcmgas                    0
12/03/09_10_00_11_00                     calls to kcmgas                    0
12/03/09_11_00_12_00                     calls to kcmgas                    0
12/03/09_12_00_13_00                     calls to kcmgas                    0
12/03/09_13_00_14_00                     calls to kcmgas                    0
12/03/09_14_00_15_00                     calls to kcmgas                    0
12/03/09_15_00_16_00                     calls to kcmgas                    0
12/03/09_16_00_17_00                     calls to kcmgas                    0
12/03/09_17_00_18_00                     calls to kcmgas                    0
12/03/09_18_00_19_00                     calls to kcmgas                    0
12/03/09_19_00_20_00                     calls to kcmgas                    0
12/03/09_20_00_21_00                     calls to kcmgas                    0
12/03/09_21_00_22_00                     calls to kcmgas                    0
12/03/09_22_00_23_00                     calls to kcmgas                    1
12/03/09_23_00_00_00                     calls to kcmgas                    0
12/04/09_00_00_01_00                     calls to kcmgas                    1
12/04/09_01_00_02_00                     calls to kcmgas                    0
12/04/09_02_00_03_00                     calls to kcmgas                    0
12/04/09_03_00_04_00                     calls to kcmgas                    0
12/04/09_04_00_05_00                     calls to kcmgas                    0
12/04/09_05_00_06_00                     calls to kcmgas                    0
12/04/09_06_00_07_00                     calls to kcmgas                    0
12/04/09_07_00_08_00                     calls to kcmgas                    0
12/04/09_08_00_09_00                     calls to kcmgas                    0
12/04/09_09_00_10_00                     calls to kcmgas                    0
12/04/09_10_00_11_00                     calls to kcmgas                    0
12/04/09_11_00_12_00                     calls to kcmgas                    0
12/04/09_12_00_13_00                     calls to kcmgas                    0
12/04/09_13_00_14_00                     calls to kcmgas                    0
12/04/09_14_00_15_00                     calls to kcmgas                    0
12/04/09_15_00_16_00                     calls to kcmgas                    0
12/04/09_16_00_17_00                     calls to kcmgas                    0
12/04/09_17_00_18_00                     calls to kcmgas                    0
12/04/09_18_00_19_00                     calls to kcmgas                    0
12/04/09_19_00_20_00                     calls to kcmgas                    0
12/04/09_20_00_21_00                     calls to kcmgas                    0
12/04/09_21_00_22_00                     calls to kcmgas                    1
12/04/09_22_00_23_00                     calls to kcmgas                    0
12/04/09_23_00_00_00                     calls to kcmgas                    0
12/05/09_00_00_01_00                     calls to kcmgas                    1
12/05/09_01_00_02_00                     calls to kcmgas                    0
12/05/09_02_00_03_00                     calls to kcmgas                    0
12/05/09_03_00_04_00                     calls to kcmgas                    0
12/05/09_04_00_05_00                     calls to kcmgas                    0
12/05/09_05_00_06_00                     calls to kcmgas                    1
12/05/09_06_00_07_00                     calls to kcmgas                    0
12/05/09_07_00_08_00                     calls to kcmgas                    0
12/05/09_08_00_09_00                     calls to kcmgas                    0
12/05/09_09_00_10_00                     calls to kcmgas                    0
12/05/09_10_00_11_00                     calls to kcmgas                    1
12/05/09_12_00_12_37                     calls to kcmgas                    9
12/05/09_12_37_12_38                     calls to kcmgas                  581
Filed Under: Oracle, Oracle脚本script Tagged With:

从此SQLPLUS有了Top命令

2012/08/08 by 9 Comments

Jagjeet Singh为我们提供了这样一个sqlplus下的RAC dashboard，实时的性能工具，看上去和top或者nmon命令的界面很相似，借助于这个工具我们将可以实时监控RAC的性能指标而不依赖于图形界面。

 

点击这里下载Jagjeet Singh的sqlplus dashboard for rac工具。

 

具体的安装方法如下：

 

 

解压zip包 

[oracle@vrh1 ~]$ unzip Pck.zip 
Archive:  Pck.zip
   creating: Pck/
  inflating: Pck/1_grants.sql        
  inflating: Pck/2_Types.sql         
  inflating: Pck/3_pkg.sql           
  inflating: Pck/drop.sql            
  inflating: Pck/howtoinstall.sql    
  inflating: Pck/outputexplained.pdf  
  inflating: Pck/sampleoutput.png    

[oracle@vrh1 ~]$ cd Pck

[oracle@vrh1 Pck]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 8 02:08:49 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

1.  执行授权文件1_grants.sql        

SQL> start 1_grants.sql
Enter value for _usr: maclean

需要指定安装在哪个schema下

2. 登录上面指定的schema，并执行 2_Types.sql 创建type

SQL> conn maclean/maclean
Connected.
SQL> 
SQL> 
SQL> start 2_Types.sql

Type created.

Type created.

Type created.

Type created.

Type created.

Type created.

Type created.

Type created.

Type created.

Type created.

3. 执行3_pkg.sql 创建包体

SQL> start 3_pkg.sql

Package created.

No errors.

Package body created.

 若想删除该工具吗，卸载的方法 

SQL> connect maclean/maclean
SQL> start drop.sql

 

 

 

具体使用的方法如下：

 

 

 SQL>set lines 190 pages 0 arraysize 50
 SQL>select * from table(jss.gtop(50,10)) ;

50代表 arraysize 一般不用修改
10代表 10s采样一次，采样间隔

 

示例输出：

 

这很酷，仿佛文字版的spotlight  FOR RAC

Filed Under: Oracle, Oracle RAC, Oracle脚本script

Maclean Liu的脚本工具盒

2012/08/08 by Leave a Comment

以下是Maclean Liu的脚本工具盒的部分脚本：

 

Script:收集Enterprise Manager Grid Control/Agent/Database Control诊断信息
Script:收集Exadata诊断信息
Script:收集RAC诊断信息
Script:收集自动SGA内存管理ASMM诊断信息
Script:Collect vip resource Diagnostic Information
11g新特性:hangdiag.sql实例hang诊断脚本
Script:verify Oracle Object timestamp discrepancy
Script:SQL调优健康检查脚本
Script:列出本会话的细节信息
利用rowid分块实现非分区表的并行update与delete
Script:计算Oracle Streams进程所占用的内存大小
利用RMAN检测数据库坏块的脚本
Script:利用外部表实现SQL查询Oracle告警日志Alert.log
Script: 收集RAC DRM 诊断信息
Script:10g中不用EM显示Active Session Count by Wait Class
Script:数据库最近的性能度量
Script:收集数据库中用户的角色和表空间等信息
Script:收集介质恢复诊断信息
Script:收集Flashback Database Log诊断信息
Script:列出Oracle每小时的redo重做日志产生量
Script:收集11g Oracle实例IO性能信息
Script:检查数据库当前是否有备份操作在执行中
Script:List Schema/Table Constraints
Script:RAC Failover检验脚本loop.sh
Script:Diagnostic Resource Manager
Script:List Grid Control Jobs
Script:GridControl Repository Health Check
Script:诊断Scheduler信息
Script:优化crs_stat命令的输出
Script:Diagnostic Oracle Locks
Script:列出用户表空间的定额
Backup Script:Expdp Schema to ASM Storage
Script:Speed Up Large Index Create or Rebuild
Script:列出失效索引或索引分区
Script:列出数据库中5%以上链式行的表
Script:列出没有主键或唯一索引的表
Script:收集ASM诊断信息
Script:收集Oracle备份恢复信息
监控一个大事务的回滚
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
Script:partition table into rowid extent chunks
Script:Oracle EBS数据库初始化参数健康检查脚本
Script:Monitoring Memory and Swap Usage to Avoid A Solaris Hang
SQL脚本:监控当前重做日志文件使用情况
Streams Health Check on 10g Release 2
从视图查询表分区的相关信息
Script To Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over Time
Script:收集RAC性能诊断信息
Script:收集UNDO诊断信息
Script:列出数据库中子表上没有对应索引的外键
Script: Listing Memory Used By All Sessions
Collecting Diagnostic Data for OCFS2 Issues
Script to Identify Objects and Amount of Blocks in the Buffer Pools – Default, Keep, Recycle, nK Cache
Script:Generate A DDL Script For A Table
SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES
如何找出Oracle中需要或值得重建的索引
Script:Diagnostic ORA-01000 maximum open cursors exceeded
ORA-4030 PGA Usage Diagnostic Script
Script:Tune Very Large Hash Join
Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)
Script:List Buffer Cache Details
Script:List NLS Parameters and Timezone
Script:List SORT ACTIVITY
Script:List OBJECT DEPENDENT
Script:Logfile Switch Frequency Map
Script:Tablespace Report
Script:收集数据库安全风险评估信息
脚本:格式化的V$SQL_SHARED_CURSOR报告
脚本:监控并行进程状态
脚本:监控数据库中的活跃用户及其运行的SQL
脚本:监控临时表空间使用率
Script to show Active Distributed Transactions
Gather DBMS_STATS Default parameter
Script:Datafile Report
Script to Collect Data Guard Diagnostic Information
Script:To Report Information on Indexes
ORA-4031 Common Analysis/Diagnostic Scripts
Script:when transaction will finish rollback
Script: Computing Table Size
Script to Detect Tablespace Fragmentation
“hcheck.sql” script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g
Script to Prevent Excessive Spill of Message From the Streams Buffer Queue To Disk
Oracle Systemstate dump analytic tool: ASS.AWK V1.09
SCRIPT TO GENERATE SQL*LOADER CONTROL FILE

 

 

更多脚本可以去博客的脚本分类中找到： Oracle Script Class

Filed Under: Oracle, Oracle脚本script

Script:诊断SYSAUX表空间使用情况

2012/06/01 by 1 Comment

Script:以下脚本可以用于诊断SYSAUX表空间使用情况

 

./opatch lsinventory -detail

@?/rdbms/admin/awrinfo

select dbms_stats.get_stats_history_retention from dual;

select dbms_stats.get_stats_history_availability from dual;

select min(SAVTIME), max(SAVTIME) from WRI$_OPTSTAT_TAB_HISTORY;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_ind_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histhead_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histgrm_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_tab_history;

select count(*) from sys.wri$_optstat_ind_history;

select count(*) from sys.wri$_optstat_histhead_history;

select count(*) from sys.wri$_optstat_histgrm_history;

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

 

 

示例输出：

 

~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
08:57:12 on Jun 01, 2012 ( Friday ) in Timezone -04:00

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days

       DB_ID DB_NAME   HOST_PLATFORM                             INST STARTUP_TIME      LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 195600696  PROD      maclean1.oracle.com - Linux x86 64-bit       1 07:42:19 (06/01)         65130 YES
  195600696  PROD      maclean2.oracle.com - Linux x86 64-bit       2 07:41:20 (06/01)         65226 YES

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                        193.6 MB ( 1% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema  SYS          occupies            101.1 MB (  52.2% )
| Schema  SYSMAN       occupies             74.5 MB (  38.5% )
| Schema  SYSTEM       occupies             13.7 MB (   7.1% )
| Schema  WMSYS        occupies              3.5 MB (   1.8% )
| Schema  DBSNMP       occupies              0.8 MB (   0.4% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| EM                   SYSMAN                        74.5 MB
| SM/AWR               SYS                           56.2 MB
| LOGMNR               SYSTEM                        12.3 MB
| SM/ADVISOR           SYS                            8.9 MB
| SM/OPTSTAT           SYS                            7.7 MB
| SM/OTHER             SYS                            6.9 MB
| WM                   WMSYS                          3.5 MB
| SQL_MANAGEMENT_BASE  SYS                            1.7 MB
| PL/SCOPE             SYS                            1.6 MB
| AO                   SYS                            1.5 MB
| XSOQHIST             SYS                            1.5 MB
| LOGSTDBY             SYSTEM                         1.4 MB
| STREAMS              SYS                            1.0 MB
| EM_MONITORING_USER   DBSNMP                         0.8 MB
| JOB_SCHEDULER        SYS                            0.5 MB
| SMON_SCN_TIME        SYS                            0.5 MB
| AUTO_TASK            SYS                            0.3 MB
| AUDIT_TABLES         SYS                            0.0 MB
| EXPRESSION_FILTER    EXFSYS                         0.0 MB
| ORDIM                ORDSYS                         0.0 MB
| ORDIM/ORDDATA        ORDDATA                        0.0 MB
| ORDIM/ORDPLUGINS     ORDPLUGINS                     0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA             0.0 MB
| SDO                  MDSYS                          0.0 MB
| STATSPACK            PERFSTAT                       0.0 MB
| TEXT                 CTXSYS                         0.0 MB
| TSM                  TSMSYS                         0.0 MB
| ULTRASEARCH          WKSYS                          0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST                        0.0 MB
| XDB                  XDB                            0.0 MB
| XSAMD                OLAPSYS                        0.0 MB
|
| Others (Unaccounted space)                         12.9 MB
|

******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************

| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
|
| Total space                                0.0 MB
|

*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM           12.9 MB
|
| Total space                               12.9 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
|    AWR size/day                          674.3 MB (28,768 K/snap * 24 snaps/day)
|    AWR size/wk                         4,719.8 MB (size_per_day * 7) per instance
|    AWR size/wk                         9,439.5 MB (size_per_day * 7) per database
|
| Estimates based on 1 snaps in past 24 hours:
|    AWR size/day                          533.8 MB (28,768 K/snap and 1 snaps in past 1.3 hours)
|    AWR size/wk                         3,736.5 MB (size_per_day * 7) per instance
|    AWR size/wk                         7,472.9 MB (size_per_day * 7) per database
|

**********************************
(3a) Space usage by AWR components (per database)
**********************************

COMPONENT        MB  % AWR  KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED          35.8   63.7       18,336      340.2     2,381.5    56% : 44%
EVENTS          5.8   10.3        2,976       55.2       386.5    43% : 57%
SQL             4.0    7.1        2,048       38.0       266.0    72% : 28%
SPACE           3.9    7.0        2,016       37.4       261.8    65% : 35%
SQLPLAN         2.3    4.1        1,184       22.0       153.8    86% : 14%
RAC             1.3    2.2          640       11.9        83.1    65% : 35%
SQLTEXT         0.6    1.0          288        5.3        37.4    89% : 11%
ASH             0.6    1.0          288        5.3        37.4    67% : 33%
SQLBIND         0.3    0.6          160        3.0        20.8    60% : 40%

**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED           3.0 WRH$_SYSMETRIC_HISTORY_INDEX                                  -  33%  INDEX
FIXED           3.0 WRH$_SYSMETRIC_HISTORY                                        -   6%  TABLE
FIXED           2.0 WRH$_LATCH.WRH$_LATCH_195600696_0                             -  56%  TABLE PARTITION
FIXED           2.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_195600696_0                       -  53%  INDEX PARTITION
FIXED           0.9 WRH$_LATCH_PK.WRH$_LATCH_195600696_0                          -  78%  INDEX PARTITION
FIXED           0.8 WRH$_SYSSTAT.WRH$_SYSSTA_195600696_0                          -  85%  TABLE PARTITION
FIXED           0.7 WRH$_PARAMETER_PK.WRH$_PARAME_195600696_0                     -  80%  INDEX PARTITION
FIXED           0.7 WRH$_PARAMETER.WRH$_PARAME_195600696_0                        -  85%  TABLE PARTITION
FIXED           0.6 WRH$_SYSMETRIC_SUMMARY                                        -   8%  TABLE
FIXED           0.6 WRH$_WAITCLASSMETRIC_HIST_IND                                 -  29%  INDEX
FIXED           0.6 WRH$_WAITCLASSMETRIC_HISTORY                                  -  13%  TABLE
EVENTS          2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__195600696_0               -  59%  INDEX PARTITION
EVENTS          0.9 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__195600696_0                  -  85%  TABLE PARTITION
EVENTS          0.5 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_195600696_0                     -  63%  TABLE PARTITION
SQL             2.0 WRH$_SQLSTAT.WRH$_SQLSTA_195600696_0                          -  47%  TABLE PARTITION
SPACE           0.6 WRH$_SEG_STAT.WRH$_SEG_ST_195600696_0                         -  80%  TABLE PARTITION
SQLPLAN         2.0 WRH$_SQL_PLAN                                                 -  65%  TABLE
SQLTEXT         0.5 WRH$_SQLTEXT                                                  -  74%  TABLE

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR         4.0 SYSMAN.MGMT_MESSAGES                                                  TABLE
NON_AWR         4.0 SYSTEM.SYS_LOB0000001147C00009$$                                      LOBSEGMENT
NON_AWR         2.0 SYSMAN.MGMT_METRICS                                                   TABLE
NON_AWR         2.0 SYSMAN.MGMT_MESSAGES_PK                                               INDEX
NON_AWR         2.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                     INDEX
NON_AWR         1.0 SYS.SYS$SERVICE_METRICS_TAB                                           TABLE
NON_AWR         1.0 SYS.SYS_LOB0000006339C00038$$                                         LOBSEGMENT
NON_AWR         0.9 SYSMAN.MGMT_METRICS_PK                                                INDEX
NON_AWR         0.9 SYS.SYS_LOB0000005097C00005$$                                         LOBSEGMENT
NON_AWR         0.9 SYSMAN.MGMT_METRICS_IDX_03                                            INDEX
NON_AWR         0.9 SYSMAN.MGMT_METRICS_IDX_01                                            INDEX
NON_AWR         0.8 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY                                     TABLE
NON_AWR         0.8 SYSMAN.SYS_LOB0000015321C00004$$                                      LOBSEGMENT
NON_AWR         0.6 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS                                     TABLE
NON_AWR         0.6 SYSMAN.MGMT_METRICS_RAW_PK                                            INDEX
NON_AWR         0.6 SYSMAN.MGMT_POLICIES                                                  TABLE
NON_AWR         0.6 SYS.I_WRI$_OPTSTAT_HH_ST                                              INDEX
NON_AWR         0.5 SYSMAN.MGMT_JOB_STEP_PARAMS                                           TABLE
NON_AWR         0.5 SYSMAN.MGMT_POLICY_ASSOC_CFG                                          TABLE
NON_AWR         0.5 SYSMAN.PK_MGMT_JOB_STEP_PARAMS                                        INDEX
NON_AWR         0.5 SYS.SYS_LOB0000006331C00004$$                                         LOBSEGMENT

**********************************
(5a) AWR snapshots - last 50
**********************************

Total snapshots in DB 195600696 Instance 2 = 1
Total snapshots in DB 195600696 Instance 1 = 1

      DBID    SNAP_ID  INST FLUSH_ELAPSED        ENDTM             STARTUP_TIME      STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
 195600696         20     1 +00000 00:00:13.4    07:52:14 (06/01)  07:42:19 (06/01)       0      0
 195600696         20     2 +00000 00:00:11.8    07:52:15 (06/01)  07:41:20 (06/01)       0      0

**********************************
(5b) AWR snapshots with errors or invalid
**********************************

no rows selected

**********************************
(5c) AWR snapshots -- OLDEST Non-Baselined snapshots
**********************************

      DBID  INST    SNAP_ID ENDTM             STATUS ERROR_COUNT
---------- ----- ---------- ----------------- ------ -----------
 195600696     1         20 07:52:14 (06/01)       0           0

**********************************
(6) AWR Control Settings - interval, retention
**********************************

       DBID  LSNAPID LSPLITID LSNAPTIME      LPURGETIME      FLAG INTERVAL          RETENTION         VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
  195600696       20        0 06/01 07:52:28 06/01 08:11:13     2 +00000 01:00:00.0 +00008 00:00:00.0    5

**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************

   SNAP_ID  INST        ASH        SQL      SQBND      FILES      SEGST     SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
        20     1          3         99        257          5         55        155
        20     2         12        105        300          5         58        156

**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************

SNAP_COUNT  INST        ASH    SQLSTAT    SQLBIND      FILES    SEGSTAT   SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
         1     2         12        105        300          5         58        156
         1     1          3         99        257          5         55        155

**********************************
(7c) AWR total item counts - names, text, plans
**********************************

   SQLTEXT    SQLPLAN   SQLBMETA     SEGOBJ   DATAFILE   TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
       407       4506       1267        297          5          1

########################################################
(II) Advisor Framework Info
########################################################

**********************************
(1) Advisor Tasks - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       07:52:03 (05/20)                       AUTO  INITIAL
SYS/ADDM       24/ADDM:195600696_3              11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       12/ADDM:195600696_1_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       23/ADDM:195600696_2_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       13/ADDM:195600696_1_4            12:00:23 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       25/ADDM:195600696_2_4            12:00:24 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       26/ADDM:195600696_4              12:00:24 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       14/ADDM:195600696_1_5            13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       28/ADDM:195600696_5              13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       27/ADDM:195600696_2_5            13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       29/ADDM:195600696_2_6            14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       15/ADDM:195600696_1_6            14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       16/ADDM:195600696_6              14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       17/ADDM:195600696_1_7            15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       30/ADDM:195600696_2_7            15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       18/ADDM:195600696_7              15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       20/ADDM:195600696_8              16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       19/ADDM:195600696_1_8            16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       31/ADDM:195600696_2_8            16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       32/ADDM:195600696_2_9            17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       42/ADDM:195600696_9              17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       41/ADDM:195600696_1_9            17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       33/ADDM:195600696_2_10           18:00:51 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       44/ADDM:195600696_10             18:01:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       43/ADDM:195600696_1_10           18:01:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       45/ADDM:195600696_1_11           19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       34/ADDM:195600696_2_11           19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       35/ADDM:195600696_11             19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       36/ADDM:195600696_2_12           20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       46/ADDM:195600696_1_12           20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       47/ADDM:195600696_12             20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       37/ADDM:195600696_2_13           21:00:09 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       48/ADDM:195600696_1_13           21:00:09 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       49/ADDM:195600696_13             21:00:09 (05/20)          9          9 AUTO  COMPLETED
SYS/ADDM       50/ADDM:195600696_1_14           22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       38/ADDM:195600696_2_14           22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       51/ADDM:195600696_14             22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       39/ADDM:195600696_2_15           23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       52/ADDM:195600696_1_15           23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       53/ADDM:195600696_15             23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       54/ADDM:195600696_1_16           00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       40/ADDM:195600696_2_16           00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       55/ADDM:195600696_16             00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       57/ADDM:195600696_17             01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       56/ADDM:195600696_1_17           01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       61/ADDM:195600696_2_17           01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       62/ADDM:195600696_2_18           02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       59/ADDM:195600696_18             02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       58/ADDM:195600696_1_18           02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       71/ADDM:195600696_19             03:00:41 (05/21)          1          1 AUTO  COMPLETED
SYS/ADDM       60/ADDM:195600696_1_19           03:00:41 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       63/ADDM:195600696_2_19           03:00:41 (05/21)          0          0 AUTO  COMPLETED

**********************************
(2) Advisor Task - Oldest 5
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM       11/ADDM:195600696_1_2            10:00:13 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       21/ADDM:195600696_2_2            10:00:13 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       22/ADDM:195600696_2              10:00:14 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       23/ADDM:195600696_2_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       12/ADDM:195600696_1_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       24/ADDM:195600696_3              11:00:19 (05/20)          0          0 AUTO  COMPLETED

**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
TASK_DESC
--------------------------------------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------------------------------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       07:52:03 (05/20)                       AUTO  INITIAL
Description: Automatic SQL Tuning Task
Error Msg  :

########################################################
(III) ASH Usage Info
########################################################

**********************************
(1a) ASH histogram (past 3 days)
**********************************

**********************************
(1b) ASH histogram (past 1 day)
**********************************

**********************************
(2a) ASH details (past 3 days)
**********************************

**********************************
(2b) ASH details (past 1 day)
**********************************

**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************

Foreground %
Background %
MMNL %

End of Report
Report written to awrinfo.txt
SQL>
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> SQL>
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
20-MAY-12 07.52.20.633129000 AM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.27.091525 AM -04:00
20-MAY-12 06.01.15.033444 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.28.548542 AM -04:00
20-MAY-12 06.01.15.071814 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.27.103629 AM -04:00
20-MAY-12 06.01.15.048213 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.53.50.386756 AM -04:00
20-MAY-12 07.57.17.388624 AM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.57.47.200604 AM -04:00
20-MAY-12 07.57.47.205012 AM -04:00

SQL> SQL>
  COUNT(*)
----------
      2191

SQL> SQL>
  COUNT(*)
----------
      2631

SQL> SQL>
  COUNT(*)
----------
     21962

SQL> SQL>
  COUNT(*)
----------
      5206

SQL> SQL>
  COUNT(*)
----------
        18

SQL> SQL>
  COUNT(*)
----------
       126

 

 

若发现statistics统计信息占用了SYSAUX上的大量空间，则可以考虑 使用dbms_stats.purge_stats过程实施清理

 

Modify retention period: DBMS_STATS.ALTER_STATS_HISTORY_RETENTION
Purge old statistics: DBMS_STATS.PURGE_STATS This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.

1. Stats Retention is set to 31 days. By Default it is 7 days. I suggest you can consider reducing the retention days to 10.

2. The number of rows exits in the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY is 42 Million Rows , where the data exists from “09-JUN-11 01.52.06.895132 PM -05:00 ” to “22-AUG-11 02.53.34.754747 PM -05:00”

3. For other tables ie: SYS. WRI$_OPTSTAT_TAB_HISTORY, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY, the data exists for a month, this is just because the retention set to 31.

ACTION PLAN:
=========

A) Purge the Snapshot . Retain data for 10 days and then purge all the other data. (The number of days data to be kept depends on your Business needs)

SQL> SPOOL CHECK1.OUT

1) Try to force the execution of the purge operations :

SQL> alter session set “_swrf_test_action” = 72;

2) Purging snapshots :

SQL> exec dbms_stats.purge_stats(sysdate-&days);

using &days = n, n-1, n-2, …, n-x

3) Then again execute the below set of SQL’s and upload the spool output file

SQL> SELECT MIN(SAVTIME),MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SQL> SPOOL OFF

B) Change the No. of Retention days from 31 to 10. For performing the same, use the below command : (The new retention time is specified in minutes.)

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>14400);

NOTE : The parameter value is in minutes so 10 daysx 24 hours x 60 minutes = 14400 minutes

C) Check the value of STATISTICS_LEVEL

If the above value is Set to ALL, then consider changing to TYPICAL.

The reason for requesting to change it to TYPICAL , is because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space.

Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.

Once the above actions performed, please keep us posted on the status of the same

Filed Under: Oracle, Oracle脚本script Tagged With: , ,

Script:查找表或索引增长的历史信息

2012/03/22 by 7 Comments

有同学在Oracle ALL STARS群中提问 如何通过AWR来查找一段时间内，数据库段对象(堆表、索引)等的空间增长信息。

 

在Oracle 10g开始awr自动负载仓库引入了dba_hist_seg_stat视图，该视图记录了快照时间内segment-level段级的历史统计信息：DBA_HIST_SEG_STAT displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

 

我们可以通过以下SQL脚本来列出相关段对象在 快照时间内的使用空间的历史变化信息：

 

 

column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

select   obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
         sum(a.db_block_changes_delta) block_increase
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in ('SYS','SYSTEM')
and      end_interval_time between to_timestamp('01-JAN-2000','DD-MON-RRRR')
         and to_timestamp('02-FEB-2013','DD-MON-RRRR')
group by obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name
/

 

 

使用示例：

 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn maclean/maclean
Connected.
SQL>
SQL>
SQL>
SQL> create table check_size tablespace users as select  * from dba_objects where rownum=0;

Table created.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> insert into check_size select * from dba_objects;

75536 rows created.

SQL> insert into check_size select * from check_size;

75536 rows created.

SQL> /

151072 rows created.

SQL> commit;

Commit complete.

SQL> insert into check_size select * from check_size;

302144 rows created.

SQL> insert into check_size select * from check_size;

604288 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> @seg_hist

OWNER            OBJECT_NAME                          START_DAY   BLOCK_INCREASE
---------------- ------------------------------------ ----------- --------------
DBSNMP           BSLN_STATISTICS                      2012-MAR-18            224
DBSNMP           BSLN_STATISTICS_PK1                  2012-MAR-18            192
MACLEAN          CHECK_SIZE                           2012-MAR-22          96176
SH               CUSTOMERS                            2012-MAR-17              0

SQL> select data_object_id from dba_objects where object_name='CHECK_SIZE';

DATA_OBJECT_ID
--------------
         78062

SQL> select seg.snap_id,
  2         seg.ts#,
  3         seg.space_used_total,
       seg.space_allocated_total,
       seg.SPACE_ALLOCATED_DELTA
  from dba_hist_seg_stat seg
 where seg.DATAOBJ#=78062
   /  4    5    6    7    8  

   SNAP_ID        TS# SPACE_USED_TOTAL SPACE_ALLOCATED_TOTAL SPACE_ALLOCATED_DELTA
---------- ---------- ---------------- --------------------- ---------------------
       354          4         30909079              36700160              36700160
       355          4        123645655             149946368             113246208

SPACE_USED_DELTA  	Delta value for space used
SPACE_ALLOCATED_DELTA   Delta value for space allocated
Filed Under: Oracle, Oracle脚本script Tagged With: , , ,

Script:ASM修复脚本，寻找LISTHEAD和Kfed源数据

2012/03/18 by Leave a Comment

以下脚本用于ASM修复disk header时：

 

 

1. dd各种有用的metadata block :

 

#! /bin/sh
rm /tmp/kfed_DH.out /tmp/kfed_FS.out /tmp/kfed_BK.out /tmp/kfed_FD.out /tmp/kfed_DD.out /tmp/kfed_PST.out
for i in `ls /dev/asm-disk*`
do
echo $i >> /tmp/kfed_DH.out
kfed read $i >> /tmp/kfed_DH.out
echo $i >> /tmp/kfed_FS.out
kfed read $i blkn=1 >> /tmp/kfed_FS.out
echo $i >> /tmp/kfed_BK.out
kfed read $i aun=1 blkn=254 >> /tmp/kfed_BK.out
echo $i >> /tmp/kfed_FD.out
kfed read $i aun=2 blkn=1 >> /tmp/kfed_FD.out
echo $i >> /tmp/kfed_DD.out
kfed read $i aun=2 blkn=2 >> /tmp/kfed_DD.out
echo $i >> /tmp/kfed_PST.out
kfed read $i aun=1 blkn=2 >> /tmp/kfed_PST.out
done

 

 

 

kfed_DH.out ==>KFBTYP_DISKHEAD      aun=0 blkn=0

kfed_FS.out ==>  KFBTYP_FREESPC      aun=1 blkn=0

kfed_BK.out  ==> KFBTYP_DISKHEAD DISK HEAD BACKUP   aun=1 blkn=254

kfed_FD.out  ==> KFBTYP_FILEDIR   aun=2  blkn=1

kfed_DD.out  ==> KFBTYP_FILEDIR  aun=2 blkn=2

kfed_PST.out ==> KFBTYP_PST_NONE aun=1 blkn=2

 

2 . Query ASM header from SQL:

 

 

spool asm_info.html
set pagesize 1000
set linesize 250
set feedback off
col bytes format 999,999,999,999
col space format 999,999,999,999
col gn format 999
col name format a20
col au format 99999999
col state format a12
col type format a12
col total_mb format 999,999,999
col free_mb format 999,999,999
col od format 999
col compatibility format a12
col dn format 999
col mount_status format a12
col header_status format a12
col mode_status format a12
col mode format a12
col failgroup format a20
col label format a12
col path format a45
col path1 format a40
col path2 format a40
col path3 format a40
col bytes_read format 999,999,999,999,999
col bytes_written format 999,999,999,999,999
col cold_bytes_read format 999,999,999,999,999
col cold_bytes_written format 999,999,999,999,999

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' ) current_time from dual;
select group_number gn, name, allocation_unit_size au, state, type, total_mb, free_mb, offline_disks od, compatibility from v$asm_diskgroup;
select group_number gn,disk_number dn, mount_status, header_status,mode_status,state, total_mb, free_mb,name, failgroup, label, path,create_date, mount_date from v$asm_disk order by group_number, disk_number;

break on g_n skip 1
break on failgroup skip 1
compute sum of t_mb f_mb on failgroup
compute count of failgroup on failgroup

select g.group_number g_n,g.disk_number d_n,g.name , g.path , g.total_mb t_mb,g.free_mb f_mb,g.failgroup from v$asm_disk g order by g_n, failgroup, d_n;
SET MARKUP HTML ON
set echo on
select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
select * from v$asm_diskgroup;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT * FROM V$ASM_CLIENT;
select * from V$ASM_ATTRIBUTE;
select * from v$asm_operation;
select * from v$version;
show parameter
show sga
spool off
exit

 

 

 

AMDU result:

 

 

Placeholder for AMDU binaries and using with ASM 10g (Doc ID 553639.1)

amdu -diskstring '/dev/asm-disk*' -dump 'MACLEAN_DG' -noimage

 

 

 

4. 脚本查找LISTHEAD

 

 

#!/bin/bash
# Usage: scan.sh   <path> <AU size> <disk size in AU>
i=0
size=0
asize=$2
rm list.txt
echo AUSZIE=$asize
while [ 1 ]
do
kfed read $1 ausz=$asize aunum=$i blknum=0 | grep LISTHEAD > list.txt
size=$(stat -c %s list.txt)
if [ $size -gt 0 ]; then
  echo LISTHEAD is found in AU=$i FILE=lhAU$i.txt
  kfed read $1 ausz=$asize aunum=$i blknum=0 text=lhAU$i.txt
fi
i=$[$i+1]
if [ $i -eq $3 ]; then
  echo $3 AUs scanned
  exit 0
fi
done

 

 

使用方法：

 

[grid@vmac1 tmp]$ ./scan.sh /dev/asm-diskb 1048576 10
AUSZIE=1048576
LISTHEAD is found in AU=2 FILE=lhAU2.txt
10 AUs scanned

Filed Under: Oracle, Oracle ASM, Oracle脚本script

Script:找出ASM中的Spfile参数文件

2012/01/30 by 3 Comments

以下脚本可以用于找出ASM存储中的Spfile参数文件，因为使用asmcmd去查找很不方便，而spfile丢失又是很头大的事情， 所以有一个脚本代劳可以省不少功夫呢！

 

 

--- listspfiles.sql
--- Purpose: Sample script to list spfiles kept in ASM instance
--- Usage: This should be run against an ASM instance,
--- not a database instance.
---
--- cut here --%<----%<----%<----%<----%<----%<--

--list all spfiles

set lines 120
col full_path for a110
SELECT full_path, dir, sys
FROM
(SELECT
CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM
(SELECT g.name gname,
a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir,
a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc)
WHERE UPPER(full_path) LIKE '%SPFILE%'
/

Sample output:

FULL_PATH                                                                                                      D S
-------------------------------------------------------------------------------------------------------------- - -
+DATA/Aspfile.ora                                                                                              N N
+DATA/VPROD/PARAMETERFILE/spfile.273.766620265                                                                 N Y
+DATA/VPROD/PARAMETERFILE/spfile.365.773976489                                                                 N Y
+DATA/VPROD/spfileVPROD.ora                                                                                    N N
Filed Under: Oracle, Oracle脚本script Tagged With: ,

沪ICP备14014813号-2

沪公网安备 31010802001379号