Query Using Bind Variables Suddenly Starts to Perform Slowly

有时SQL语句在没有明显原因的情况下,突然变得执行缓慢,SQL语句所引用的对象的统计信息,对象数据,SQL都没有改变。

SQL语句的游标由于某些原因生成了新的执行计划。出现这种情况的原因至少为以下一种:
.数据库重新启动
.由于任何原因游标没有被使用,并且因为LRU算法被aged out
.SQL语句所引用的任何对象的统计信息发生改变
.SQL语句所引用的任何对象的结构发生改变
.对SQL语句所引用的对象授予/回收权限

如果游标被pinned(正被使用),那么清空共享池也不会将该游标删除。

可能造成SQL语句突然执行缓慢的原因一个可能主要的原因是使用了oracle 9i所引入的绑定变量窥视。使用这个功能,查询优化器将会在第一次调用一个游标时窥视用户定义绑定变量的值。这个功能能让优化器像使用literal值一样对待绑定变量的值来计算任何where子句条件的选择率。在后续调用游标时,不会执行绑定变量窥视,并且游标是共享,基于标准游标共享的标准,即使后续调用游标时使用不同的绑定变量值,还是会使用共享标。换句话说,这个游标被解析一次被多次使用。如果在SQL第一次被解析时所使用的绑定变量值不能代表大多数数据时,那么对于不同的绑定变量来说第一次生成的执行计划就很有可能不是最优的。

在SQL语句中使用绑定变量,并且游标是共享的,对不同的调用都将使用相同的执行计划。如果不同的调用使用不同的执行计划更有效,那么在SQL语句中使用绑定变量就不合适。

另外,绑定变量窥视已经被熟知在RAC的不同节点中会导致生成不同的执行计划,这是因为每个节点都有属于它自己的共享池。尽管相同的SQL,数据与统计信息,在SQL语句使用不同的绑定变量第一次在每个节点被解析时,对于每个节点将会选择不同的执行计划。

隐含参数_optim_peek_user_binds用来控制绑定变量窥视这个功能是否启用,它的缺省值是true。虽然禁用绑定变量窥视可以得到一个稳定的执行计划,但必须要了解对于所有绑定变量使用一种稳定的执行计划是否是最佳的选择。比如一个表有10000行记录并且col1列存在索引。
SELECT
FROM tablex
WHERE col1 BETWEEN :bind1 AND :bind2;

如果执行这个SQL,使用值123与124来从10000行记录中检索2行记录,那么使用索引绝对是最明智的选择。然而,如果使用绑定变量值123与9999来执行相同的SQL,那么查询将要检索表中绝大多数记录并且选择全表扫描应该是最合适的,但优化器不能意识到这一点,不会因此改变执行计划。

解决方法
要解决因绑定变量窥视而造成的SQL变慢的问题最好的方法是使用Oracle 11g引入的自适就游标共享,这个技术允许优化器对于不同的绑定变量值使用不同的执行计划。如果自适应游标不可以使用,一种可能的解决方法是修改应用程序并且对于上面的语句有两个单独的
模块/部分来表示,但这种改变(使用hint来修改)将会生成你预期的执行计划。因此编码绑定变量或可以使用cursor_sharing设置为similar或force是适合的,但必须要认识到绑定变量窥视基于在硬解析时提供给优化器的一组绑定变量值而生成非你预期的执行计划。使用hint与编码应用程序来允许使用合适版本的SQL或使用literal值来获得更优的执行计划。

为了禁用绑定变量窥视将_optim_peek_user_binds在spfile/pfile或会话中设置为false:
alter session set “_OPTIM_PEEK_USER_BINDS”=FALSE;

下面通过一个例子来理解绑定变量窥视

SQL> create table t1(t_id number(20),t_meal varchar2(20));

Table created.

SQL>begin
    for i in 1 .. 79998 loop
     insert into t1 values(i,'Mansaf');
    end loop;
     insert into t1 values(79999,'Kabab');
     insert into t1 values(80000,'Pasta');
     commit;
    end;
    /
PL/SQL procedure successfully completed.


SQL> create index idx_t1 on t1(t_meal);

Index created.

SQL> exec dbms_stats.gather_table_stats

(ownname=>null,tabname=>'T1',estimate_percent=>100,method_opt=>'for all indexed columns 

size auto',cascade=>true);

PL/SQL procedure successfully completed.

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

cursor_sharing参数为exact,这意味着只要查询的literal值不同游标就不能共享

SQL> select count(*) from t1 where t_meal='Mansaf';

  COUNT(*)
----------
     79998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  41wjq1qnk92wd, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 79998 |   546K|    44   (5)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T_MEAL"='Mansaf')


19 rows selected.

从上面的执计划可以看到执行计划使用了index fast full scan。

下面使用绑定变量来执行

SQL> var x varchar2(20)
SQL> exec :x:= 'Mansaf'

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where t_meal=:x;

  COUNT(*)
----------
     79998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  42s7s1vzsrbwk, child number 0
-------------------------------------
select count(*) from t1 where t_meal=:x

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 79998 |   546K|    44   (5)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T_MEAL"=:X)


19 rows selected.

当使用绑定变量后语义相同的语句的SQL_ID发生了改变。执行计划是使用index fast full scan

SQL> exec :x:='Pasta';

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where t_meal=:x;

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  42s7s1vzsrbwk, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 |     1 |     7 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)


19 rows selected.

当只检索一条记录时,因为查询语句相同,游标共享并且即使绑定变量值不同还是使用了第一次生成的执行计划,这时该执行计划对于这个绑定变量值来说不是最佳执行计划。

如果想要使游标失效并重新生成执行计划,有以下几种方法使游标失效。
1.执行alter system flush shared_pool;
2.删除或修改游标所引用对象的统计信息
3.对游标所引用的对象授予或回收相关权限
4.修改游标所引用对象
5.重启实例
6.使用dummy hint来改变语句的文本

这里使用dummy hint来改变语句的文本

SQL> select /*+ Hard parse me please */ count(*) from t1 where t_meal=:x;

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  crggg37d7jmrg, child number 0
-------------------------------------
select /*+ Hard parse me please */ count(*) from t1 where t_meal=:x

Plan hash value: 1970818898

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1 |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)


19 rows selected.

从执行计划可以看到SQL_ID也发生了改变,确实重新生成了执行计划

使用 DBMS_SQLDIAG诊断各种查询问题

这篇文章主要介绍通过dbms_sqldiag来解决与SQL相关的各种问题。dbms_sqldiag是与标准版本数据库软件一起发布,使用它并不需要额外的许可。
dbms_sqldiag可以用于以下问题类型的诊断:
.problem_type_performance 怀疑是性能问题
.problem_type_wrong_results 怀疑查询返回了不一致的结果
.problem_type_compilation_error 在编译时的错误
.problem_type_execution_error 在执行时的错误

诊断problem_type_performance
执行查询并将该语句来作为诊断的SQL语句

SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from t1 where c1=500000;

Elapsed: 00:00:03.43

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("C1")=500000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1646  consistent gets
          0  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select sql_text,sql_id from v$sqlarea where sql_text like 'select * from t1 where c1=500000';
 
SQL_TEXT                                                                         SQL_ID
-------------------------------------------------------------------------------- -------------
select * from t1 where c1=500000                                                 456naq6s2fcpq

创建诊断任务

SQL> set echo on
SQL> set linesize 132
SQL> set pagesize 999
SQL> set long 999999
SQL> set serveroutput on
SQL> declare
  2  v_sql_diag_task_id varchar2(100);
  3  begin
  4  --
  5  -- create diagnostic task
  6  --
  7  v_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
  8  sql_id=>'456naq6s2fcpq',
  9  problem_type => dbms_sqldiag.problem_type_performance,
 10  time_limit => 900,
 11  task_name => 'problem_type_performance_task' );
 12  --
 13  -- setup parameters for the task to give verbose output
 14  --
 15  dbms_sqltune.set_tuning_task_parameter(v_sql_diag_task_id,'_sqldiag_finding_mode',dbms_sqldiag.sqldiag_findings_filter_plans);
 16  end;
 17  /

PL/SQL procedure successfully completed.

有时sql_id因为一些原因可能在v$sql视图中找不到,因此在这时就需要使用sql_text来代替sql_id,用户想要执行诊断任务必须至少有advisor权限。task_name作为唯一键使用并且在相同用户使用相同任务名之前必须要删除。

检查任务是否创建成功

SQL> select distinct owner, task_name, advisor_name
from dba_advisor_tasks where advisor_name ='SQL Repair Advisor' and task_name like '%perf%'order by 1;  

OWNER                          TASK_NAME                      ADVISOR_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            problem_type_performance_task  SQL Repair Advisor      

执行论断任务

SQL> exec dbms_sqldiag.execute_diagnosis_task (task_name => 'problem_type_performance_task' );

PL/SQL procedure successfully completed.

生成报告

SQL> select dbms_sqldiag.report_diagnosis_task ('problem_type_performance_task' ) as recommendations from dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : problem_type_performance_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 900
Completion Status  : COMPLETED
Started at         : 05/30/2016 10:20:17
Completed at       : 05/30/2016 10:20:22

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 456naq6s2fcpq
SQL Text   : select * from t1 where c1=500000

-------------------------------------------------------------------------------
No SQL patch was found to resolve the problem.

-------------------------------------------------------------------------------

这上面的这个例子中,没有得到任何patch建议。然而,如果得到了patch,可以执行以下命令来接受patch

begin
 dbms_sqldiag.accept_sql_patch(
   task_name =>'problem_type_performance_task', 
   task_owner => 'sys', 
   replace => true);
end;

验证SQL Patch是否启用

SQL> SELECT name, status FROM dba_sql_patches WHERE name LIKE '%SYS%';
 
NAME                           STATUS
------------------------------ --------
SYS_SQLPTCH_                   ENABLED

删除任务

SQL> exec dbms_sqldiag.drop_diagnosis_task('problem_type_performance_task');

PL/SQL procedure successfully completed.

SQL> select distinct owner, task_name, advisor_name
  2  from dba_advisor_tasks where advisor_name ='SQL Repair Advisor' and task_name like '%perf%'order by 1;
 
OWNER                          TASK_NAME                      ADVISOR_NAME
------------------------------ ------------------------------ ------------------------------

删除SQL Patch

--- find the name of the sql patch
---
select name, status from dba_sql_patches where name like '%sys%';


---drop the sql patch.
---replace following patch name with actual name of the sql patch
--- from previous query output.
exec  dbms_sqldiag.drop_sql_patch (name=> 'sys_sqlptch_');
--- verify that the sql patch has been dropped.

select name, status from dba_sql_patches where name like '%sys%';

诊断problem_type_wrong_results
创建两个测试表

SQL> create table a_test
  2  (
  3  id number not null,
  4  clss number not null
  5  );

Table created.

SQL> create table as_test
  2  (
  3  as_id number
  4  );

Table created.

SQL> insert into a_test values(11,5);

1 row created.

SQL> insert into a_test values(1,5);

1 row created.

SQL> insert into as_test values(11);

1 row created.

SQL> commit;

Commit complete.

错误结果–返回0行记录

SQL> select 'working' as is_working,id
from   a_test a,
  2    3         as_test asi
  4  where  a.id=asi.as_id(+)
  5  and    a.clss in (1,3,4,5)
  6  and    a.clss = '5';

IS_WORK         ID
------- ----------
                                                                                               
SQL> select sql_id, sql_text from v$sql where sql_text like 'select%as is_working%';
 
SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
9a15z3d14krcm select 'working' as is_working,id from   a_test a,        as_test asi where  a.i

正确结果–返回2行记录

SQL> select 'working' as is_working,id
from   a_test a,
  2    3         as_test asi
  4  where  a.id=asi.as_id(+)
  5  and    a.clss in (1,3,4,5)
  6  and    a.clss = '5';

IS_WORK         ID
------- ----------
working         11
working          1
SQL> set echo on lines 132 pages 999 long 20000 serveroutput on;
SQL> declare
  2  l_sql_diag_task_id  varchar2(100); 
  3         
  4  begin
  5  --
  6  -- create diagnostic task
  7  --
  8      l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (
  9        sql_id => '9a15z3d14krcm',
 10        problem_type => dbms_sqldiag.problem_type_wrong_results, 
 11        task_name => 'test_wr_diagnostic_task' );
 12   
 13  --
 14  -- setup parameters for the task to give verbose output
 15  --
 16      dbms_sqltune.set_tuning_task_parameter(
 17        l_sql_diag_task_id,
 18        '_sqldiag_finding_mode',
 19        dbms_sqldiag.sqldiag_findings_filter_plans);
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name  => 'test_wr_diagnostic_task');

PL/SQL procedure successfully completed.


SQL> select dbms_sqldiag.report_diagnosis_task ('test_wr_diagnostic_task') as recommendations  from dual;


RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test_wr_diagnostic_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 05/30/2016 10:46:22
Completed at       : 05/30/2016 10:46:24

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 9a15z3d14krcm
SQL Text   : select 'working' as is_working,id
             from   a_test a,
                    as_test asi
             where  a.id=asi.as_id(+)
             and    a.clss in (1,3,4,5)
             and    a.clss = '5'

...省略...

诊断PROBLEM_TYPE_COMPILATION_ERROR & PROBLEM_TYPE_EXECUTION_ERROR
创建诊断任务

set echo on
set linesize 132
set pagesize 999
set long 999999
set serveroutput on

declare

v_sql_diag_task_id varchar2(100);


begin
---
--- create a diagnostic task. use any name you want under task_name argument.
---
v_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
sql_text => 'select distinct min(src.cf_table_group)
from ( select pc.cf_table_group, min(pc.cf_prg_next_run)
from testcase1 pc where nvl(pc.cf_prg_next_run, sysdate) < = sysdate
group by pc.cf_table_group
order by nvl(min(pc.cf_prg_next_run),sysdate) ) src',
problem_type => dbms_sqldiag.problem_type_execution_error,
time_limit => 3600,
task_name => 'error_diagnostic_task' );
--
-- setup parameters for the task to give verbose output
--
dbms_sqltune.set_tuning_task_parameter(v_sql_diag_task_id,'_sqldiag_finding_mode',dbms_sqldiag.sqldiag_findings_filter_plans);


end;

执行诊断任务

exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'error_diagnostic_task' );

生成报告

set long 9999999
select dbms_sqldiag.report_diagnosis_task ('error_diagnostic_task' ) as recommendations from dual;

recommendations
--------------------------------------------------------------------------------
general information section
-------------------------------------------------------------------------------
tuning task name : error_diagnostic_task
tuning task owner : tc2533
workload type : single sql statement
scope : comprehensive
time limit(seconds): 3600
completion status : completed
started at : 10/27/2011 22:35:07
completed at : 10/27/2011 22:35:07

-------------------------------------------------------------------------------
schema name: tc2533
sql id : 4k1tdq940wvpk
sql text : select distinct min(src.cf_table_group)
from ( select pc.cf_table_group, min(pc.cf_prg_next_run)
from testcase1 pc where nvl(pc.cf_prg_next_run, sysdate) < =
sysdate
group by pc.cf_table_group
order by nvl(min(pc.cf_prg_next_run),sysdate) ) src

-------------------------------------------------------------------------------
findings section (1 finding)
-------------------------------------------------------------------------------

1- sql patch finding (see explain plans section below)
------------------------------------------------------
a potentially better execution plan was found for this statement.

recommendation
--------------
- consider accepting the recommended sql patch.
execute dbms_sqldiag.accept_sql_patch(task_name =>
'error_diagnostic_task', task_owner => 'tc2533', replace => true);

rationale
---------
recommended plan with hash value 3673393522 has number of rows 1, check
sum 2342552567, execution time 0 and 6 buffer gets

接受建议

execute dbms_sqldiag.accept_sql_patch(task_name =>'error_diagnostic_task', task_owner => 'tc2533', replace => true);

删除诊断任务

exec dbms_sqldiag.drop_diagnosis_task ( task_name => 'error_diagnostic_task' );

Oracle Column Group Statistics

基数是CBO通过行资源或行资源组合评估出来的操作将产生的行数。在有些情况下,结果集的基数可能被评估错误。最常见的是使用复杂谓词且统计信息不能精确反映出谓词相关联的数据。例如:select ename from emp where sal >= 25000 and job = ‘PRESIDENT’;
在这个例子中,在sal与job列中的数据存在一种隐藏的关联。只有董事长才会挣的比$25,000多。没有其它的员工满足sal>=25000。优化器没有办法检查这种关联且会将这两个列单独对待。这将会对谓词产生一种不理解的选择率以及不精确的基数评估。

在Oracle 11g及以上版本通过对一组列创建扩展统计信息来解决这种问题。

SQL> create table t1(t_name varchar2(20) not null,t_country varchar2(20) not null,t_town varchar2(20) not null);

Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T_NAME                                    NOT NULL VARCHAR2(20)
 T_COUNTRY                                 NOT NULL VARCHAR2(20)
 T_TOWN                                    NOT NULL VARCHAR2(20)


SQL> begin
  2   for i in 1..50 loop
  3       insert into t1 values('JY','CHINA','QIHE');  
  4   end loop;
  5   for r in 51 .. 400 loop
  6     insert into t1 values('A'||r,'USA','NewYork');  
  7   end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

收集表t1的统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'JY',tabname=>'T1',estimate_percent=>100,method_opt=>'for all columns sizeauto',cascade=>true);

PL/SQL procedure successfully completed.

查询t_name=’JY’且t_country=’CHINA’的记录为50

SQL> select count(*) from t1 where t_name='JY' and t_country='CHINA';

  COUNT(*)
----------
        50

执行查询发现优化器评估的基数为7,与50相差很远

SQL> select * from t1 where t_name='JY' and t_country='CHINA';

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   119 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     7 |   119 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

创建组列

SQL> declare
  2  cg_name varchar2(30);
  3  begin
  4   cg_name :=  sys.dbms_stats.create_extended_stats(null,'t1', '(t_name,t_country)');
  5  end;
  6  /  

PL/SQL procedure successfully completed.

对创建的组列收集统计信息

SQL> exec dbms_stats.gather_table_stats(null,'t1',estimate_percent=>100,method_opt=>'for columns(t_name,t_country) size skewonly');

PL/SQL procedure successfully completed.


SQL> SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='T1';


EXTENSION_NAME                 EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUVJ3HB84$ZIMSQJE_3AMWZ_9 ("T_NAME","T_COUNTRY")


SQL> SELECT e.extension col_group, t.num_distinct, t.histogram
  2  FROM user_stat_extensions e, user_tab_col_statistics t
  3  WHERE e.extension_name=t.column_name
  4  AND e.table_name = t.table_name
  5  AND t.table_name='T1';

COL_GROUP                                                                        NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------------------- ------------ ---------------
("T_NAME","T_COUNTRY")                                                                    351 HEIGHT BALANCED

创建组列收集扩展统计信息后,其评估基数为47与实际的50相差很小,对于估算已经是很准确了

SQL> select * from t1 where t_name='JY' and t_country='CHINA';

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    47 |   846 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    47 |   846 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T_COUNTRY"='CHINA' AND "T_NAME"='JY')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1224  bytes sent via SQL*Net to client
        452  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

对于列之间存在关联关系的列创建Column Group Statistics可以让优化器进行更精确的基数评估从而选择最优执行计划来提高性能

Automatic SQL Tuning and SQL Profiles

SQL Profiles是在Oracle 10g中就引入的,并且通过dbms_sqltune包或EM来进行管理是自动SQL调整进程的一部分。

自动SQL调整
查询优化器会有时会因为缺少信息而对语句的一个属性产生不精确的评估,进而导致低效的执行计划传统上来说,用户不得不通过手动增加hint到代码中来修改这个剖从而指导优化器产生正确的决定。对于第三方应用程序,改变应用代码是不现实的。

自动SQL调整使用SQL Profile来处理这个问题。自动调整优化器将会为SQL语句创建一个SQL Profile,它是由语句相关的辅助统计信息组成。查询优化器在正常模式下对基数,选择率与成本的评估有时会有严重的差异而导致生成低效的执行计划。SQL Profile通过收集额外的信息,比如使用抽样与特定的执行技术来调整评估可以用来解决这个问题。

在自动SQL调整时,优化器也会使用SQL语句的历史执行信息来合理设置优化器参数,比如改变optimizer_mode,将其参数从all_rows改成first_rows。

分析报告的输出建议接受SQL Profile。一旦接受SQL Profile它将会永久存储在数据字典中。一个SQL Profile被指定到一个特定的查询。如果接受,优化器在正常模式下使用SQL Profile中的信息与常规的数据库统计信息来对SQL进行解析。SQL Profile所提供的额外信息可以用来生成更好的执行计划。

SQL Profile
SQL Profile是存储在数据字典中的信息集合能让查询优化器为SQL语句创建一个最优执行计划。SQL Profile包含了自动SQL调整所发现的低效优化器评估的修正。这种信息能提高优化器对基数与选择率评估精确度,从而导致优化器选择一个更好的执行计划。

SQL Profile不包含单独的执行计划,当选择执行计划时优化器有以下信息源:
1.环境,包含数据库配置,绑定变量值,统计信息,数据集等等
2.SQL Profile所提供了附加统计信息

重要的是SQL Profile不会冻结一个SQL语句的执行计划,这一点与stored outlines不一样。当表记录增加或增加与删除索引时,使用相同的SQL Profile执行计划也会发生改变。当数据分布或相关语句的访问路径发生改变,SQL Profile中存储的信息仍然与SQL语关联。然而,随着时间的推移,SQL Profile的内容将会过时并且不得不重新生成。可以再次执行自动SQL调整来重新生成SQL Profile。

如何控制SQL Profile的使用范围
SQL Profile的使用范围可以由CATEGORY属性来进行控制。这个属性决定那个用户会话可以应用这个SQL Profile。可以通过查询dba_sql_profiles视图的category列来查看SQL Profile的category属性

SQL> select name,category from dba_sql_profiles;
 
NAME                           CATEGORY
------------------------------ ------------------------------
SYS_SQLPROF_0152b233d518c007   DEFAULT
SYS_SQLPROF_015470e31c248001   DEFAULT
coe_bcyatm4910qb1_725332378    DEFAULT
coe_3yy1wbuvsxm93_1849931106   DEFAULT
SYS_SQLPROF_0152b11b33e6c006   DEFAULT
coe_a69pw2vj989zm_3709683508   DEFAULT
SYS_SQLPROF_0151ed60f3d28000   DEFAULT
coe_6rfqq1bjwcdx9_1360313219   DEFAULT
SYS_SQLPROF_0152b33048a8c009   DEFAULT
coe_36cbabzyq13gy_1849931106   DEFAULT
SYS_SQLPROF_015470e298fd0000   DEFAULT
SYS_SQLPROF_0152b0a82393c003   DEFAULT
SYS_SQLPROF_0152ba15c21e800b   DEFAULT
coe_6rfqq1bjwcdx9_1360313219_1 DEFAULT

缺省情况下,所有SQL Profile都是创建在DEFAULT目录中。这意味着当sqltune_category设置为default时所有的用户会话都能使用这个SQL Profile。

SQL> show parameter sqltune_category
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sqltune_category                     string      DEFAULT

通过修改SQL Profile的category属性,可以决定那个会话将使用所创建的SQL Profile。例如,通过将一个SQL Profile的category属性设置为DEV,那么只有当sqltune_category设置为DEV时这些会话才能使用这个SQL Profile。所有其它的会话将不能访问这个SQL Profile并且SQL语句的执行计划将不会受这个SQL Profile的影响。这种技术能在SQL Profile被其它会话使用之前让你在一个受限的环境下测试SQL Profile。

SQL Profile可以应用的语句类型
.select语句
.update语句
.insert语句(只包含select子句)
.delete语句
.create table语句(只包含as select子句)
.merge语句(update或insert操作)

SQL Profile的管理
SQL Profile可以通过EM或dbms_sqltune来进行管理

为了使用dbms_sqltune来管理SQL Profile,用户必须有create any sql_profile,drop any sql_profile与alter any sql_profile系统权限。

接受SQL Profile
使用dbms_sqltune.accept_sql_profile过程来接受由SQL调整指导所创建的SQL Profile。

DECLARE
 my_sqlprofile_name VARCHAR2(30);
 BEGIN
 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
 task_name => 'my_sql_tuning_task',
 name => 'my_sql_profile');
 END;

my_sql_tuning_task是SQL调整任务的名称,可以查询dba_sql_profiles视图来查看SQL Profile的信息

修改SQL Profile
使用dbms_sqltune.alter_sql_profile过程可以用来修改现有SQL Profile的status,name,description与category属性

BEGIN
 DBMS_SQLTUNE.ALTER_SQL_PROFILE(
 name => 'my_sql_profile', 
 attribute_name => 'STATUS', 
 value => 'DISABLED');
 END;
 /

在这个例子中,要修改名为my_sql_profile的SQL Profile,将它的status属性修改为disable这将意味着这个SQL Profile将不能在SQL编译时使用了。

删除SQL Profile
可以使用dbms_sqltune.drop_sql_profile过程来删除SQL Profile

begin
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
 end;
 /

下面介绍使用SQL Profile来优化SQL语句的例子
会话1

SQL> create table test(n number);

Table created.

SQL> declare
  2             begin
  3              for i in 1 .. 10000 loop
  4                  insert into test values(i);
  5                  commit;
  6              end loop;
  7             end;
  8   /

PL/SQL procedure successfully completed.

SQL> create index test_idx on test(n);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TEST');

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

会话2
创建一个SQL自动调整任务并运行报告调整任务并接受建议的SQL Profile

SQL> declare
  2     my_task_name VARCHAR2(30);
  3     my_sqltext CLOB;
  4     begin
  5        my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
  6        my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7        sql_text => my_sqltext,
  8        user_name => 'SCOTT',
  9        scope => 'COMPREHENSIVE',
 10        time_limit => 60,
 11        task_name => 'my_sql_tuning_task_1',
 12        description => 'Task to tune a query on a specified table');
 13   end;
 14   /

PL/SQL procedure successfully completed.

SQL>  begin
  2   DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
  3   end;
  4   /

PL/SQL procedure successfully completed.

SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> set heading off
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;
set heading on
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_1
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/27/2016 16:58:11
Completed at       : 05/27/2016 16:58:28

-------------------------------------------------------------------------------
Schema Name: SCOTT

SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 90.91%)
  ------------------------------------------

  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_1', task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .003464           .000405       88.3 %
  CPU Time (s):                 .003399           .000299       91.2 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes

  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

2- Using SQL Profile
--------------------

Plan hash value: 2882402178

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)


-------------------------------------------------------------------------------

SQL> DECLARE
  2   my_sqlprofile_name VARCHAR2(30);
  3   begin
  4   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  5   task_name => 'my_sql_tuning_task_1',
  6   name => 'my_sql_profile',
  7   force_match => true,
  8   replace =>true );
  9   end;
 10  /

PL/SQL procedure successfully completed.

会话1

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         13  consistent gets
          1  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到虽然我们指定了no_index来让优化器不使用索引test_idx,但由于使用了SQL Profile还是使用索引test_idx,通过SQL Profile改变了SQL语句的执行计划。

磁盘损坏造成RMAN备份文件有坏块的恢复案例

朋友客户的数据库由于磁盘损坏,从存储级别抽取出了RMAN的备份文件,但没有抽取出数据文件,联机重做日志,控制文件,参数文件等数据库文件。但抽取出来的备份文件中有数据文件,控制文件,参数文件的备份,归档重做日志备份因为没有磁盘空间没有执行完成。新安装Oracle软件后创建参数文件来使用RMAN备份来还原和恢复数据库。但在还原数据文件时出错,错误如下所示:

RMAN> run
2> {
3> allocate channel t1 device type disk;
4> restore controlfile from 'E:\ORABACK\ORCLBPM_9BQ7K30M_20150522.BAK';
5> alter database mount;
6> release channel t1;
7> }

使用目标数据库控制文件替代恢复目录
分配的通道: t1
通道 t1: SID=572 设备类型=DISK

启动 restore 于 29-5月 -15

通道 t1: 正在还原控制文件
通道 t1: 还原完成, 用时: 00:00:03
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL01.CTL
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL02.CTL
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL03.CTL
完成 restore 于 29-5月 -15

数据库已装载

释放的通道: t1

RMAN> run
2> {
3> allocate channel t1 device type disk;
4> restore database;
5> recover database;
6> release channel t1;
7> }

分配的通道: t1
通道 t1: SID=572 设备类型=DISK

启动 restore 于 29-5月 -15
启动 implicit crosscheck backup 于 29-5月 -15
已交叉检验的 200 对象
完成 implicit crosscheck backup 于 29-5月 -15

启动 implicit crosscheck copy 于 29-5月 -15
完成 implicit crosscheck copy 于 29-5月 -15

搜索恢复区中的所有文件
正在编制文件目录...
没有为文件编制目录


通道 t1: 正在开始还原数据文件备份集
通道 t1: 正在指定从备份集还原的数据文件
通道 t1: 将数据文件 00001 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
通道 t1: 将数据文件 00002 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
通道 t1: 将数据文件 00003 还原到 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
通道 t1: 将数据文件 00004 还原到 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
通道 t1: 将数据文件 00005 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
通道 t1: 将数据文件 00006 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
通道 t1: 将数据文件 00007 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
通道 t1: 将数据文件 00008 还原到 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
通道 t1: 将数据文件 00009 还原到 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
通道 t1: 将数据文件 00010 还原到 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF
通道 t1: 正在读取备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK
通道 t1: ORA-19870: 还原备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 时出错
ORA-19612: 数据文件 1 没有还原, 因为 missing or corrupt data

故障转移到上一个备份

释放的通道: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: restore 命令 (在 05/29/2015 17:03:44 上) 失败
RMAN-06026: 有些目标没有找到 - 终止还原
RMAN-06023: 没有找到数据文件1的副本来还原

从错误信息: ORA-19870: 还原备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 时出错,ORA-19612: 数据文件 1 没有还原, 因为 missing or corrupt data可以知道在执行还原数据文件1时备份片段丢失或有坏块,这里明显是有坏块,因为其它数据文件使用该备份片段已经还原成功了。这里通过设置事件让RMAN跳过坏块来还原数据文件。
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 5月 29 18:02:22 2015

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

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set event=’19548 trace name context forever’, ‘19549 trace name
context forever’ scope=spfile;

系统已更改。

SQL> shutdown immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size 2190736 bytes
Variable Size 3942646384 bytes
Database Buffers 3707764736 bytes
Redo Buffers 13750272 bytes
数据库装载完毕。

RMAN> restore database;

启动 restore 于 29-5月 -15
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=1141 设备类型=DISK

正在略过数据文件 2; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
正在略过数据文件 3; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
正在略过数据文件 4; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
正在略过数据文件 5; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
正在略过数据文件 6; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
正在略过数据文件 8; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
正在略过数据文件 9; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
正在略过数据文件 10; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00001 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
通道 ORA_DISK_1: 将数据文件 00007 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
通道 ORA_DISK_1: 正在读取备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK
通道 ORA_DISK_1: 段句柄 = E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 标记 = TAG2015052
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:02:45
完成 restore 于 29-5月 -15

下面对之前还原报错的数据文件SYSTEM01.DBF,HB_SY03.DBF进行验证
C:\Users\Administrator>dbv file=D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF blocksize
=8192

DBVERIFY: Release 11.2.0.1.0 – Production on 星期五 5月 29 18:29:19 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – 开始验证: FILE = D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF

DBVERIFY – 验证完成

检查的页总数: 280064
处理的页总数 (数据): 242009
失败的页总数 (数据): 0
处理的页总数 (索引): 13233
失败的页总数 (索引): 0
处理的页总数 (其他): 3322
处理的总页数 (段) : 1
失败的总页数 (段) : 0
空的页总数: 21500
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数 : 0
最高块 SCN : 87501555 (0.87501555)

C:\Users\Administrator>dbv file=D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF blocksize=
8192

DBVERIFY: Release 11.2.0.1.0 – Production on 星期五 5月 29 18:30:35 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – 开始验证: FILE = D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF

DBVERIFY – 验证完成

检查的页总数: 524288
处理的页总数 (数据): 256890
失败的页总数 (数据): 0
处理的页总数 (索引): 52837
失败的页总数 (索引): 0
处理的页总数 (其他): 196657
处理的总页数 (段) : 0
失败的总页数 (段) : 0
空的页总数: 17904
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数 : 0
最高块 SCN : 87500838 (0.87500838)

从上面的验证可以看到没有坏块,幸运哈哈。我们来检查一个控制文件中记录的数据文件检查点SCN与数据文件头SCN是否相同,如果不同可能需要恢复。

SQL> select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi
:ss') checkpoint_time from v$datafile;

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         1           87500284 2015-05-22 23:00:42
         2           87500284 2015-05-22 23:00:42
         3           87500284 2015-05-22 23:00:42
         4           87500284 2015-05-22 23:00:42
         5           87500284 2015-05-22 23:00:42
         6           87500284 2015-05-22 23:00:42
         7           87500284 2015-05-22 23:00:42
         8           87500284 2015-05-22 23:00:42
         9           87500284 2015-05-22 23:00:42
        10           87500284 2015-05-22 23:00:42

已选择10行。
SQL> select file#,checkpoint_change#,name from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ----------------------------------
         1           87499953 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
         2           87499953 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
         3           87499953 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
         4           87499953 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
         5           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
         6           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
         7           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
         8           87499953 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
         9           87499953 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
        10           87499953 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF


已选择10行。

可以看到所有数据文件头记录的检查点scn都一样,控制文件中记录的所有数据文件检查点scn都一样,但数据文件头检查点scn与控制文件中记录的数据文件检查点scn不一样,所以选择重建控制文件来执行恢复,这里将控制文件备份到跟踪文件中。

SQL> oradebug setmypid
已处理的语句
SQL> alter database backup controlfile to trace
  2  ;

数据库已更改。

SQL> oradebug tracefile_name
d:\app\administrator\diag\rdbms\orclbpm\orclbpm\trace\orclbpm_ora_1704.trc

下面用跟踪文件的控制文件创建语句来重新创建控制文件,这里要使用resetlogs方式来创建,因为RMAN的备份文件中没有联机重做日志文件。

SQL> shutdown immediate
ORA-01109: ??????


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCLBPM" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\ORCLBPM\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\ORCLBPM\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\ORCLBPM\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF',
 14    'D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF',
 15    'D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF',
 16    'D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF',
 17    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF',
 18    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF',
 19    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF',
 20    'D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF',
 21    'D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF',
 22    'D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF'
 23  CHARACTER SET ZHS16GBK
 24  ;

控制文件已创建。

检查一个控制文件中记录的数据文件检查点SCN与数据文件头SCN是否相同,数据文件头检查点scn与控制文件中记录的数据文件检查点scn一样,在没有联机重做日志和归档重做日志的情况下,我们只能将数据库恢复到scn:87499953时间点,并以open resetlogs选项来open数据库

SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1           87499953
         2           87499953
         3           87499953
         4           87499953
         5           87499953
         6           87499953
         7           87499953
         8           87499953
         9           87499953
        10           87499953

已选择10行。

SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1           87499953
         2           87499953
         3           87499953
         4           87499953
         5           87499953
         6           87499953
         7           87499953
         8           87499953
         9           87499953
        10           87499953

已选择10行。

这里需要设置隐含参数_allow_resetlogs_corruption=true来open数据库。

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

系统已更改。

SQL> shutdown immediate
ORA-01109: ??????


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
数据库装载完毕。
SQL> alter database open resetlogs;

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\ORCLBPM\TEMP01.DBF' R
EUSE;

表空间已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
数据库装载完毕。
数据库已经打开。

直此将数据库恢复到备份生成的时间点,但没有归档重做日志和联机重做日志丢失了半天的数据。

SQL Plan Management

任何数据库应用程序的性能都严重依赖于它的查询执行。当没有任何用户介入时Oracle优化器是完全能够评估最优执行计划的,SQL语句的执行计划因为各种原因,比如:重新收集优化器统计信息,改变优化器参数或方案/元数据定义而可能会出现改变。但是不能保证执行计划的改变总是会提高性能,因此会导致有些管理员会锁定SQL的执行计划(Stored Outline)或锁定统计信息。然而,这样做会导致不能使用新版优化器为了提供高SQL性能所提供的一些新特性或新的访问路径。

Oracle的每一个新版本中都包含了一些新特性用来提高查询的性能。然而某些应用(或部分应用)是静态的并且比起在特定情况下提高性能来说可预测的性能是更可取的。Oracle11G提供了SPM来完全透时的控制执行计划的演变。使用SPM优化器自动管理执行计划并且确保只有已经或被验证的执行计划才会被使用。当找到SQL语句的新执行计划时,但直到它被数据库进行验证或其性能比当前执行计划高才会被使用。

SQL Plan Management是一种预防机制,记录与评估SQL语句的执行计划。这种机制可以创建一个SQL执行计划基线,它是由一组已知性能良好的执行计划组成。不管系统发生何种改变,SQL执行计划基线被用来维护相关SQL的性能,提供了执行计划的稳定性。

SPM确保SQL的性能不会因为执行计划的改变而降低。为了保证这一点,只有SQL执行计划ACCEPTED为YES才会被优化器所使用。

SPM有三个主要组件:
1.SQL执行计划基线捕获
创建SQL执行计划基线这代表对所有相关SQL语句授受这个执行计划。SQL执行计划基线被存储在SYSAUX表空间中的SQL Management BASE中的plan history中,可以通过dba_sql_plan_baselines视图进行查询。

2.SQL执行计划基线选择
确保只有ACCEPTED为YES的执行计划才会被使用,使用SQL执行计划基线并在plan history中跟踪所有新的执行计划。plan history由ACCEPTED为NO与ACCEPTED为YES的执行计划组成。ACCEPTED为NO的执行计划可能是没有被验证或者被拒绝(验证了但性能比当前使用的执行计划要差)

3.SQL执行计划基线演进
对plan history中的所有没有被验证的执行计划进行评估让其成为接受的或拒绝的执行计划

SQL执行计划基线捕获
为了让SPM起作用必须首先使用当前基于成本的执行计划来作为SQL Management Base,这将成为每个SQL语句的SQL执行计划基线。有两种不同的方法来创建SQL Management Base:
.自动捕获执行计划
.批量加载执行计划

自动捕获执行计划
自动捕获执行计划是由参数optimizer_capture_sql_plan_baselines=true来开启的(缺省值为false)当自动捕获执行计划被启用时,SPM档案库将会自动存储任何重复执行的SQL语句的执行计划。为了识别重复执行的SQL语句,优化器将会在每个SQL语句第一次被编译时进行标识,如果这个SQL语句再次被执行或编译,那么就会将这个SQL语句标记为重复执行的SQL,将会为这个SQL语句创建SQL plan history,它将包含生成的执行计划,比如:SQL文本,存储概要,绑当定变量与编译环境等信息。当前基于成本的执行计划将会作为SQL语句的第一个SQL执行计划基线并且这个执行计划的ACCEPTED会标记为YES,只有ACCEPTED为YES的执行计划才会被使用,如果将来对于这个SQL语句产生了新的执行计划,这个执行计划将会增加到plan history中并且将会被标记为验证过的。只有当它的性能比当前所使用的执行计划更好时才会将ACCEPTED标记为YES。

批量加载执行计划
批量加载执行计划在数据库从之前的版本升级致电Oracle 11g或部署新应用程序时很有用。批量加载可以与自动加载执行计划结合使用或者代替自动加载执行计划。执行计划被批量加载时会自动接受创建的新SQL执行计划基线或者向已经存在的增加一个。SQL anagement BASE可以使用三种不同的方法来批量加载执行计划。
1.从指定的SQL Tuning Set(STS)中加载执行计划

SQL> BEGIN
  2  DBMS_SQLTUNE.CREATE_SQLSET(
  3  sqlset_name => 'my_sql_tuning_set',
  4  description => 'TEST');
  5  END;
  6  /
 
PL/SQL procedure successfully completed

SQL> DECLARE
  2    l_cursor  DBMS_SQLTUNE.sqlset_cursor;
  3  BEGIN
  4    OPEN l_cursor FOR
  5      SELECT VALUE(p)
  6      FROM   TABLE (DBMS_SQLTUNE.select_cursor_cache (
  7                      NULL, -- basic_filter
  8                      NULL, -- object_filter
  9                      NULL, -- ranking_measure1
 10                      NULL, -- ranking_measure2
 11                      NULL, -- ranking_measure3
 12                      NULL, -- result_percentage
 13                      2,    -- result_limit
                         'all') --attribute_list    
 14                    ) p;
 15  
 16    DBMS_SQLTUNE.load_sqlset (
 17      sqlset_name     => 'my_sql_tuning_set',
 18      populate_cursor => l_cursor);
 19  END;
 20  /
 
PL/SQL procedure successfully completed

或者从awr中加载

SQL> DECLARE
  2    l_cursor  DBMS_SQLTUNE.sqlset_cursor;
  3  BEGIN
  4    OPEN l_cursor FOR
  5      SELECT VALUE(p)
  6      FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (
  7                      29027,  -- begin_snap
  8                      29028,  -- end_snap
  9                      NULL, -- basic_filter
 10                      NULL, -- object_filter
 11                      NULL, -- ranking_measure1
 12                      NULL, -- ranking_measure2
 13                      NULL, -- ranking_measure3
 14                      NULL, -- result_percentage
 15                      2,   -- result_limit
 16                      'all'--attribute_list
 17                      )
 18                    ) p;
 19  
 20    DBMS_SQLTUNE.load_sqlset (
 21      sqlset_name     => 'my_sql_tuning_set',
 22      populate_cursor => l_cursor);
 23  END;
 24  /
 
PL/SQL procedure successfully completed
SQL> select * from dba_sqlset where name='my_sql_tuning_set';
 
        ID NAME                           OWNER                          DESCRIPTION                                                                      CREATED     LAST_MODIFIED STATEMENT_COUNT
---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- ------------- ---------------
        15 my_sql_tuning_set              INSUR_CHANGDE                  TEST                                                                             2016/5/26 1 2016/5/26 12:               2
 
SQL> select * from dba_sqlset_statements where sqlset_name='my_sql_tuning_set';
 
SQLSET_NAME                    SQLSET_OWNER                    SQLSET_ID SQL_ID        FORCE_MATCHING_SIGNATURE SQL_TEXT                                                                         PARSING_SCHEMA_NAME            PARSING_SCHEMA_ID PLAN_HASH_VALUE BIND_DATA                                                                        BINDS_CAPTURED MODULE                                           ACTION                           ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED    FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV                                                                      PRIORITY COMMAND_TYPE FIRST_LOAD_TIME     STAT_PERIOD ACTIVE_STAT_PERIOD OTHER                                                                            PLAN_TIMESTAMP    SQL_SEQ
------------------------------ ------------------------------ ---------- ------------- ------------------------ -------------------------------------------------------------------------------- ------------------------------ ----------------- --------------- -------------------------------------------------------------------------------- -------------- ------------------------------------------------ -------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ -------------- -------------------------------------------------------------------------------- ---------- ------------ ------------------- ----------- ------------------ -------------------------------------------------------------------------------- -------------- ----------
my_sql_tuning_set              INSUR_CHANGDE                          15 2823agph489xc        1.702412129134E19 select id,name,password from gl_czy where name ='系统管理'                       ZW4001                                       248      2543410975                                                                                                 R9_AppSrv.EXE                                                                           124908      32105         324         48             0             21         42         21                 21              3 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544                       3                                                                                                                                     2016/2/16 10:5        717
 
my_sql_tuning_set              INSUR_CHANGDE                          15 1hfffsrmgqhwp      1.11129101236943E19 select nvl(catalog_center,center_id) as  catalog_center from bs_hospital_collate INSUR_CHANGDE                                211      2429242715                                                                                                 JDBC Thin Client                                                                        141372     141372       11475          0             0           3825       3825       3825               3825              1 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544                       3                                                                                                                                     2016/5/23 14:2        718
 
SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => ‘TABLE_SQLSET’,schema_name 

=> ‘JY’);

PL/SQL procedure successfully completed

SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 

‘my_sql_tuning_set’,staging_table_name => ‘TABLE_SQLSET’,staging_schema_owner =>’JY

’);

PL/SQL procedure successfully completed


导出存储STS

[IBMP740-1:oracle:/yb_oradata/RLZYbak]$expdp jy/jy directory=dump_RLZY dumpfile=stgtab_sqlset.dmp logfile=stgtab_sqlset.log tables=TABLE_SQLSET                         

                                < Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 26 May, 2016 16:57:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01":  insur_changde/******** directory=dump_RLZY dumpfile=stgtab_sqlset.dmp logfile=stgtab_sqlset.log tables=TABLE_SQLSET 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 576 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "INSUR_CHANGDE"."TABLE_SQLSET"              16.91 KB       2 rows
. . exported "INSUR_CHANGDE"."TABLE_SQLSET_CPLANS"       24.10 KB       5 rows
. . exported "INSUR_CHANGDE"."TABLE_SQLSET_CBINDS"       9.507 KB       0 rows
Master table "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for INSUR_CHANGDE.SYS_EXPORT_TABLE_01 is:
  /bak/dpdump/stgtab_sqlset.dmp
Job "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:25

导入STS

[oracle@jyrac1 ~]$ impdp jy/jy directory=dump_dir dumpfile=stgtab_sqlset.dmp remap_schema=insur_changde:jy remap_tablespace=hygeia:users

Import: Release 11.2.0.4.0 - Production on Thu May 26 17:53:08 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "JY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JY"."SYS_IMPORT_FULL_01":  jy/******** directory=dump_dir dumpfile=stgtab_sqlset.dmp remap_schema=insur_changde:jy remap_tablespace=hygeia:users 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "JY"."TABLE_SQLSET"                         16.91 KB       2 rows
. . imported "JY"."TABLE_SQLSET_CPLANS"                  24.10 KB       5 rows
. . imported "JY"."TABLE_SQLSET_CBINDS"                  9.507 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "JY"."SYS_IMPORT_FULL_01" successfully completed at Thu May 26 17:59:33 2016 elapsed 0 00:06:05

unpack STS

SQL> begin
  2    DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name          => 'my_sql_tuning_set',
  3                                      sqlset_owner         => 'JY',
  4                                      replace              => true,
  5                                      staging_table_name   => 'TABLE_SQLSET',
  6                                      staging_schema_owner => 'JY');
  7  END;
  8  /
PL/SQL procedure successfully completed

从STS加载SQL执行计划基线

SQL> declare
  2  ret number;
  3  begin
  4  ret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'my_sql_tuning_set',sqlset_owner => 'INSUR_CHANGDE');
  5  end;
  6  /
  
 
PL/SQL procedure successfully completed

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where parsing_schema_name in('ZW4001','INSUR_CHANGDE');
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_c4a3ce0d598be4e4           SQL_PLAN_c98yf1pcsrt74ebcd9fdf MANUAL-LOAD    YES     YES      select id,name,password from gl_czy where name ='系统管理'
 
SQL_b9bc48036c46e780           SQL_PLAN_bmg280dq4dtw0e85be7cc MANUAL-LOAD    YES     YES      select nvl(catalog_center,center_id) as  catalog_center from 

bs_hospital_collate
 

2.使用Cursor Cache中的执行计划
可以从cursor cache中直接加载SQL语句的执行计划到SQL Management Base中。可以通过module名,方案或SQL_ID来进过滤,可以用来标记想要捕获的SQL语句或一组SQL语句。

SQL> set autotrace traceonly
SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------



SQL> select sql_text,sql_id,plan_hash_value from v$sqlarea where sql_text like '%c1>4076%';
 
SQL_TEXT                                                                         SQL_ID        PLAN_HASH_VALUE
-------------------------------------------------------------------------------- ------------- ---------------
select * from t1 where c1>4076                                                   0006gg4zsmmcg      3617692013


SQL> var n number
SQL> begin
  2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0006gg4zsmmcg', plan_hash_value=>'3617692013', fixed =>'NO', enabled=>'YES');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076


3.从staging table中unpack SQL执行计划基线
在原系统中创建staing表

SQL> set autotrace traceonly 
SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement


SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY');
 
PL/SQL procedure successfully completed

将SQL执行计划基线从SQL Management Base中加载到staing table中

SQL> declare
  2  x number;
  3  begin
  4   x:=dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY',creator =>'JY');
  5  end;
  6  /
  
PL/SQL procedure successfully completed

SQL> declare
  2  cursor c is select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where parsing_schema_name in('JY');
  3  x number;
  4  begin
  5   for i in c loop
  6    x:=dbms_spm.drop_sql_plan_baseline(sql_handle => ''||i.sql_handle,plan_name => ''||i.plan_name);
  7   end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed

SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1">4076)


导出staing table并在目标数据库中导入,这里省略了相关操作。在目标数据库将SQL执行计划基线从staging table中加载到SQL Management Base中

SQL> declare
  2  x number;
  3  begin
  4   x:=dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY');
  5  end;
  6  /
 
PL/SQL procedure successfully completed


SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement

SQL执行计划基线选择
每次SQL被编译时,优化器首先使用传统的基于成本的方法来创建一个最优执行计划。如果参数optimizer_use_plan_baselines设置为true(缺省值),那么在基于成本的执行计划被执行之前,优化器将尝试在SQL语句的SQL执行计划基线中找到一个匹配的执行计划,这是作为内存操作来执行的,如果找到了匹配的执行计划将使用该执行计划。否则,将生成一个新的执行计划并增加到plan history中,在执行计划被授受之前必须被验证。优化器将从该SQL所关联的所有执行计划中选择一个成本最低的来执行。然而,如果系统的改变造成所有已经接受的执行计划不能重现,那么优化器将会使用新的执行计划。

SQL执行计划基线也可能影响优化器对执行计划的选择。SQL执行计划基线可以被标记为固定(fixed),Fixed的SQL执行计划基线指示优化器会优先选择。如果优化器正在评估SQL执行计划基线的成本并且有一个执行计划是fixed,并且fixed的执行计划可以重现,那么优化器只会评估fixed执行计划的成本。如果fixed的执行计划不可以重现,那么优化器将会评估其它的SQL执行计划基线并从其中选择一个成本最低的执行计划。评估一个执行计划的成本远不及硬解析那样昂贵。优化器不会考虑所有可能的访问方法,只会考虑特定的访问方法。

SQL执行计划的演进
当优化器为SQL语句找到一个新执行计划时,这个新的执行计划会作为未被接受的执行计划被增加到plan history中,在它成为接受(ACCEPTED=YES)的执行计划之前需要被验证。可以通过使用EM或执行dbms_spm.evolve_sql_plan_baseline来演进。使用这些方法有三种选择:
1.只有新执行计划比现有的SQL执行计划基线性能更好才将ACCEPTED设置为YES
2.不进行性能验证就将ACCEPTED设置为YES
3.运行性能比较并生成一个报告但不演进新的执行计划

如果选择选项1,它将触发新的执行计划被评估来比较是否它的性能要比被选择的执行计划好。如果是,那么新的执行计划会被增加到SQL执行计划基线并且ACCEPTED设置为YES。否则将新执行计划的ACCEPTED设置为NO并增加到plan history中,但它的last_verified属性将更新为当前时间戳,并且会返回一个格式化的文本报告,它包含了新执行计划与原执行计划的性能统计数据。

如果选择选项2,新执行计划将会增加到SQL执行计划基线中,而不对性能进行验证并将ACCEPTED设置为YES,同样也会生成报告。

如果选择选项3,将会验证新执行计划的性能是否比已选择的执行计划好,但是就算性能更好也会不自动将新执行计划的ACCEPTED设置为YES,在评估之后只会生成报告。

SQL Management Base的使用与管理
有两个参数用来控制SPM
1.optimizer_capture_sql_plan_baselines:对重复执行的SQL语句是否自动捕获新的SQL执行计划基线。缺省值是false。

2.optimizer_use_sql_plan_baselines:控制优化器是否使用SQL执行计划基线。当启用时,优化器在编译SQL语句时会查找SQL执行计划基线中的执行计划。如果找到,优化器将会从SQL执行计划基线中选择一个成本最低的执行计划。缺省值是true。

SQL Management Base空间消耗的管理
statment log,plan histories与SQL执行计划基线都被存储在SQL Management Base中。SQL Management Base是数据库数据字典的一部分存储在SYSAUX表空间中。缺省情况下,SQL Management Base的空间限制不会超过SYSAUX表空间的10%。但是也可以使用dbms_spm.configure来进行修改让其空间限制处于SYSAUX表空间的1%到50%之间。每周后台进程都会检测SQL Management Base所占用的空间大小,并且当使用空间超过设置的限制,进程就会向alert.log记录一条警告信息。也有一个每周调度清除作业来管理SQL Management Base所占用的空间。在维护窗口内这个任务会自动运行并且任何超过53周没有被使用过的执行计划将会被清除,因此确保只有SQL语句一旦运行就能保证一年之内相关执行计划是可以使用的。可以使用dbms_spm.configure来将没使用的执行计划保留周期修改为5到523周这个范围之内。

因为SQL Management Base使用SYSAUX表侬间来存储执行计划,如果SYSAUX表空间不能使用,那么SPM也将不能使用。

通过DBA视图监控SPM
dbs_sql_plan_baselines视图显示了关于当前为特定SQL语句所创建的SQL执行计划基线。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text,fixed,autopurge from dba_sql_plan_baselines where parsing_schema_name in('JY') and 

sql_handle='SQL_67b129b37635284b';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT                                                                  

       FIXED AUTOPURGE
------------------------------ ------------------------------ -------------- ------- -------- 

-------------------------------------------------------------------------------- ----- ---------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076                                            

       NO    YES
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    NO      YES      select * from t1 where c1>4076                                            

       NO    YES

为了查看任何SQL执行计划基线的详细执行计划可以执行dbms_xplan.display_sql_plan_baseline

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_67b129b37635284b',plan_name => 'SQL_PLAN_6gc99qdv3aa2bdbd90e8e'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_67b129b37635284b
SQL text: select * from t1 where c1>4076
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6gc99qdv3aa2bdbd90e8e         Plan id: 3688435342
Enabled: NO      Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   924 |  3696 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   924 |  3696 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1">4076)
 
24 rows selected
 
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_67b129b37635284b',plan_name => 'SQL_PLAN_6gc99qdv3aa2bbeaed07c'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_67b129b37635284b
SQL text: select * from t1 where c1>4076
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6gc99qdv3aa2bbeaed07c         Plan id: 3199127676
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1">4076)
 
24 rows selected

还可以通过使用v$sql视图来查看SQL语句是否使用了SQL执行计划基线。如果SQL语句正使用SQL执行计划基线,那么dba_sql_plan_baselines中的plan_name就会与v$sql中的sql_plan_baseline有相同的值。

select sql_handle, plan_name,origin, enabled, accepted,a.sql_text,fixed,autopurge from dba_sql_plan_baselines a,v$sql b 
where a.parsing_schema_name in('JY') and  a.plan_name=b.sql_plan_baseline and a.signature=b.exact_matching_signature
and a.sql_text like '%select * from t1 where c1>4076%';

SQL执行计划在以下情况可以用来提高或维护SQL性能:
1.数据库升级
数据库升级会安装新版的优化器,这通常会改变一小部分SQL的执行计划,大部分发生改变的执行计划性能没有变化或者有所提高。然而,特定的执行计划发生改变可能造成性能的衰减。使用SQL执行计划基线可以有效的最小化由数据库升级而引起的性能衰减。

2.系统/数据发生变化
当系统/数据发生变化时可能会影响某些SQL的执行计划,潜在造成性能衰减。使用SQL执行计划能够最小化性能衰减并且稳定SQL性能。

3.应用程序升级
开发新的应用程序意味着引入新的SQL语句。在标准测试环境中对于新SQL语句应用程序可能会使用合适的执行计划。如果你的系统配置不同于测试环境,SQL执行计划基线可以被用来产生更好的性能。

如果在cursor缓存中有性能良好的执行计划,那么可以将其加载到SPM中,因此你可以使用SQL执行计划基数来维护SQL的性能。

SQL> set autotrace traceonly
SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------



SQL> select sql_text,sql_id,plan_hash_value from v$sqlarea where sql_text like '%c1>4076%';
 
SQL_TEXT                                                                         SQL_ID        PLAN_HASH_VALUE
-------------------------------------------------------------------------------- ------------- ---------------
select * from t1 where c1>4076                                                   0006gg4zsmmcg      3617692013


SQL> var n number
SQL> begin
  2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0006gg4zsmmcg', plan_hash_value=>'3617692013', fixed =>'NO', enabled=>'YES');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076



SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement


Statistics
----------------------------------------------------------
         26  recursive calls
         17  db block gets
         89  consistent gets
          0  physical reads
       3060  redo size
      13042  bytes sent via SQL*Net to client
       1091  bytes received via SQL*Net from client
         63  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        924  rows processed

可以看到SQL plan baseline “SQL_PLAN_6gc99qdv3aa2bdbd90e8e” used for this statement信息,说明对于该SQL语句以后的执行都将会使用所创建的SQL执行计划基线。

在启用SPM后,对于这些有了SQL执行计划基线的SQL_ID,数据库将不会再收集新的执行计划(即使在SPM被禁用的情况下也不会再收集新的执行计划)。

SPM缺省值就是启用,自动捕获SQL执行计划基线是禁用的

SQL> show parameter sql_plan_baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

启用自动捕获SQL执行计划基线

SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=both sid='*';

System altered.


SQL> create index idx_t1 on t1(c1);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'t1',estimate_percent=>100,method_opt=>'for all columns size repeat',cascade=>true);

PL/SQL procedure successfully completed.


SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement



SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076
 

可以看到优化器还是使用原来手动创建的SQL执行计划基线,并没有生成新的执行计划,还是执行的全表扫描,并没有使用我们创建的索引。

禁用SPM,启用自动捕获SQL执行计划基线

SQL> alter system set optimizer_use_sql_plan_baselines=false scope=both sid='*';

System altered.

SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   957 |  3828 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |   957 |  3828 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

可以看到执行计划使用了对索引idx_t1的范围扫描,而不是之前的全表扫描了。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     NO       select * from t1 where c1>4076
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076

可以看到ORIGN为AUTO-CAPTURE的SQL执行计划基线就是自动捕获的,ENABLED为YES,但是ACCEPTED为NO,这说明虽然创建了新的SQL执行计划基线但是优化器不能使用。下面启用SPM,看优化器是否使用新创建的SQL执行计划基线。

SQL> alter system set optimizer_use_sql_plan_baselines=true scope=both sid='*';

System altered.

SQL> show parameter sql_plan_baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE

SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement

从SQL plan baseline “SQL_PLAN_6gc99qdv3aa2bdbd90e8e” used for this statement信息可以看到优化器还是使用我们手动创建的SQL执行计划基线,对表t1执行全表扫描。想要使用执行索引扫描的SQL执行计划基线就要将其对应的ACCEPTED设置为YES并将使用全表扫描的SQL执行计划基线的ACCEPTED设置为NO。但是11gr1与11gr2在修改SQL执行计划基线的ACCEPTED属性的方法是不同的。另外,一旦在11gr2中将SQL执行计划基线的ACCEPTED设置为YES,就不能再将其设置为NO。为了不使用某个SQL执行计划基线,只能将该SQL执行计划基线的ENABLED设置为NO。

在11gr1中修改SQL执行计划基线的ACCEPTED属性值的方法如下:

SQL> DECLARE
  2 x number;
  3  BEGIN
  4  x := dbms_spm.alter_sql_plan_baseline('SQL_67b129b37635284b','SQL_PLAN_6gc99qdv3aa2bbeaed07c',attribute_name=> 'ACCEPTED',attribute_value=> 'YES');
  5  END;
  6  / 
  PL/SQL procedure successfully completed.

如果在11gr2中使用这种方法,将会得到错误信息:

SQL> DECLARE
  2 x number;
  3  BEGIN
  4  x := dbms_spm.alter_sql_plan_baseline('SQL_67b129b37635284b','SQL_PLAN_6gc99qdv3aa2bbeaed07c',attribute_name=> 'ACCEPTED',attribute_value=> 'YES');
  5  END;
  6  / 

ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2532
ORA-06512: at line 4

那么如何在11gr2中修改SQL执行计划基线的ACCEPTED值,可以使用dbms_spm.evolve_sql_plan_baseline与dbms_spm.alter_sql_plan_baseline。

SQL> SET SERVEROUTPUT ON
SQL> SET LONG 10000
SQL> DECLARE
  2      x clob;
  3  BEGIN
  4      x := dbms_spm.evolve_sql_plan_baseline('SQL_67b129b37635284b', 'SQL_PLAN_6gc99qdv3aa2bbeaed07c',verify=>'NO' ,commit=>'YES');
  5      DBMS_OUTPUT.PUT_LINE(x);
  6  END;
  7  /
-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_67b129b37635284b
  PLAN_NAME  = SQL_PLAN_6gc99qdv3aa2bbeaed07c
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = NO
  COMMIT     = YES

Plan: SQL_PLAN_6gc99qdv3aa2bbeaed07c
------------------------------------
  Plan was changed to an accepted plan.

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 0
Number of plans accepted: 1


PL/SQL procedure successfully completed.

这里的verify参数在修改ACCEPTED为YES之前是否需要执行相应的执行计划并与当前可以使用的执行计划比较性能。如果ACCEPTED为NO的执行计划的性能有所提高,那么将执行计划基线的ACCEPTED设置为YES。当这个参数设置为”YES”时,如果执行计划的性能有所提高就将ACCEPTED修改为YES。当这个参数设置为”NO”时,直接将ACCEPTED修改为YES。

参数commit指定是否将ACCEPTED值从NO修改为YES。当设置为YES时,执行计划基线会将ACCEPTED设置为YES并生成一个报告。当设置为NO时,一个报告会生成但不会改变ACCEPTED值。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076
 

SQL> DECLARE
  2      x number;
  3  BEGIN
  4      x := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_67b129b37635284b',plan_name =>'SQL_PLAN_6gc99qdv3aa2bdbd90e8e',attribute_name 

=>'ENABLED',attribute_value =>'NO');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    NO      YES      select * from t1 where c1>4076

现在手动创建的使用全表扫描的SQL执行计划基线的ENABLED为NO,新生成的使用索引范围扫描的SQL执行计划基线的ENABLED与ACCEPTED值为YES,说明优化器可以使用。

SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   957 |  3828 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |   957 |  3828 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement

可以看到优化器确实使用了使用索引范围扫描的SQL执行计划基线。

dbms_sqltune.create_stgtab_sqlset需要注意的问题

今天在使用dbms_sqltune.create_stgtab_sqlset来创建staging table时由于表名使用的是小写,发现创建之后,并不能查询到该表,但通过视图进行查看发现又确实存在这个表,这个问题在oracle 10.2..0.5与11.2.0.4中都存在,其它的版本没有测试过。

SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => 'stgtab_sqlset',schema_name => 

'JY');
 
PL/SQL procedure successfully completed

SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 

'my_sql_tuning_set',staging_table_name => 'stgtab_sqlset',staging_schema_owner =>'JY');
 
PL/SQL procedure successfully completed


SQL> select * from jy.stgtab_sqlset;
 
select * from stgtab_sqlset
 
ORA-00942: table or view does not exist

SQL> desc jy.stgtab_sqlset
Object jy.stgtab_sqlset does not exist.

查询dba_objects视图确能找到该表stgtab_sqlset

SQL> select * from dba_objects where owner='JY' and OBJECT_NAME in('stgtab_sqlset');
 
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
JY                             stgtab_sqlset                                                                                                        92747          92747 TABLE               2016/5/26 1 2016/5/26 16: 2016-05-26:16:04:49 VALID   N         N         N                  1 

如是表名使用大写

SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => ‘TABLE_SQLSET’,schema_name 

=> ‘JY’);

PL/SQL procedure successfully completed

SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 

‘my_sql_tuning_set’,staging_table_name => ‘TABLE_SQLSET’,staging_schema_owner =>’JY

’);

PL/SQL procedure successfully completed

SQL> select * from table_sqlset;
 
NAME                           OWNER                          DESCRIPTION                                                                      SQL_ID        FORCE_MATCHING_SIGNATURE SQL_TEXT                                                                         PARSING_SCHEMA_NAME            BIND_DATA                                                                        BIND_LIST MODULE                                           ACTION                           ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED    FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV                                                                      PRIORITY COMMAND_TYPE FIRST_LOAD_TIME     STAT_PERIOD ACTIVE_STAT_PERIOD OTHER                                                                            PLAN_HASH_VALUE PLAN     SPARE1     SPARE2 SPARE3 SPARE4
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------- ------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- --------- ------------------------------------------------ -------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ -------------- -------------------------------------------------------------------------------- ---------- ------------ ------------------- ----------- ------------------ -------------------------------------------------------------------------------- --------------- ---- ---------- ---------- ------ --------------------------------------------------------------------------------
my_sql_tuning_set              JY                             TEST                                                                             2823agph489xc        1.702412129134E19 select id,name,password from gl_czy where name ='系统管理'                       ZW4001                                                                                                            R9_AppSrv.EXE                                                                           124908      32105         324         48             0             21         42         21                 21              3 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544                       3                                                                                                                                          2543410975  
 
my_sql_tuning_set              JY                             TEST                                                                             1hfffsrmgqhwp      1.11129101236943E19 select nvl(catalog_center,center_id) as  catalog_center from bs_hospital_collate INSUR_CHANGDE                                                                                                     JDBC Thin Client                                                                        141372     141372       11475          0             0           3825       3825       3825               3825              1 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544                       3                                                                                                                                          2429242715  


查询视图dba_objects发现这两个表的相关属性除了表名与创建时间,对象ID之外没有差异

SQL> select * from dba_objects where owner='JY' and OBJECT_NAME in('stgtab_sqlset','STGTAB_SQLSET');
 
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
JY                             STGTAB_SQLSET                                                                                                        92781          92781 TABLE               2016/5/26 1 2016/5/26 16: 2016-05-26:16:22:55 VALID   N         N         N                  1 
JY                             stgtab_sqlset                                                                                                        92747          92747 TABLE               2016/5/26 1 2016/5/26 16: 2016-05-26:16:04:49 VALID   N         N         N                  1 

SQL> desc jy.stgtab_sqlset
Name                        Type           Nullable Default Comments 
--------------------------- -------------- -------- ------- -------- 
ID                          NUMBER         Y                         
NAME                        VARCHAR2(30)   Y                         
OWNER                       VARCHAR2(30)   Y                         
DESCRIPTION                 VARCHAR2(256)  Y                         
SQL_ID                      VARCHAR2(13)   Y                         
SQL_SEQ                     NUMBER         Y                         
FORCE_MATCHING_SIGNATURE    NUMBER         Y                         
SQL_TEXT                    CLOB           Y                         
PARSING_SCHEMA_NAME         VARCHAR2(30)   Y                         
BIND_DATA                   RAW(2000)      Y                         
BINDS_CAPTURED              CHAR(1)        Y                         
BIND_POSITION               NUMBER         Y                         
BIND_VALUE                  ANYDATA        Y                         
MODULE                      VARCHAR2(64)   Y                         
ACTION                      VARCHAR2(64)   Y                         
ELAPSED_TIME                NUMBER         Y                         
CPU_TIME                    NUMBER         Y                         
BUFFER_GETS                 NUMBER         Y                         
DISK_READS                  NUMBER         Y                         
DIRECT_WRITES               NUMBER         Y                         
ROWS_PROCESSED              NUMBER         Y                         
FETCHES                     NUMBER         Y                         
EXECUTIONS                  NUMBER         Y                         
END_OF_FETCH_COUNT          NUMBER         Y                         
OPTIMIZER_COST              NUMBER         Y                         
OPTIMIZER_ENV               RAW(2000)      Y                         
PRIORITY                    NUMBER         Y                         
COMMAND_TYPE                NUMBER         Y                         
FIRST_LOAD_TIME             VARCHAR2(19)   Y                         
STAT_PERIOD                 NUMBER         Y                         
ACTIVE_STAT_PERIOD          NUMBER         Y                         
OTHER                       CLOB           Y                         
PLAN_HASH_VALUE             NUMBER         Y                         
PLAN_STATEMENT_ID           VARCHAR2(30)   Y                         
PLAN_PLAN_ID                NUMBER         Y                         
PLAN_TIMESTAMP              DATE           Y                         
PLAN_REMARKS                VARCHAR2(4000) Y                         
PLAN_OPERATION              VARCHAR2(30)   Y                         
PLAN_OPTIONS                VARCHAR2(255)  Y                         
PLAN_OBJECT_NODE            VARCHAR2(128)  Y                         
PLAN_OBJECT_OWNER           VARCHAR2(30)   Y                         
PLAN_OBJECT_NAME            VARCHAR2(30)   Y                         
PLAN_OBJECT_ALIAS           VARCHAR2(65)   Y                         
PLAN_OBJECT_INSTANCE        NUMBER         Y                         
PLAN_OBJECT_TYPE            VARCHAR2(30)   Y                         
PLAN_OPTIMIZER              VARCHAR2(255)  Y                         
PLAN_SEARCH_COLUMNS         NUMBER         Y                         
PLAN_ID                     NUMBER         Y                         
PLAN_PARENT_ID              NUMBER         Y                         
PLAN_DEPTH                  NUMBER         Y                         
PLAN_POSITION               NUMBER         Y                         
PLAN_COST                   NUMBER         Y                         
PLAN_CARDINALITY            NUMBER         Y                         
PLAN_BYTES                  NUMBER         Y                         
PLAN_OTHER_TAG              VARCHAR2(255)  Y                         
PLAN_PARTITION_START        VARCHAR2(255)  Y                         
PLAN_PARTITION_STOP         VARCHAR2(255)  Y                         
PLAN_PARTITION_ID           NUMBER         Y                         
PLAN_DISTRIBUTION           VARCHAR2(30)   Y                         
PLAN_CPU_COST               NUMBER         Y                         
PLAN_IO_COST                NUMBER         Y                         
PLAN_TEMP_SPACE             NUMBER         Y                         
PLAN_ACCESS_PREDICATES      VARCHAR2(4000) Y                         
PLAN_FILTER_PREDICATES      VARCHAR2(4000) Y                         
PLAN_PROJECTION             VARCHAR2(4000) Y                         
PLAN_TIME                   NUMBER         Y                         
PLAN_QBLOCK_NAME            VARCHAR2(30)   Y                         
PLAN_OTHER_XML              CLOB           Y                         
PLAN_EXECUTIONS             NUMBER         Y                         
PLAN_STARTS                 NUMBER         Y                         
PLAN_OUTPUT_ROWS            NUMBER         Y                         
PLAN_CR_BUFFER_GETS         NUMBER         Y                         
PLAN_CU_BUFFER_GETS         NUMBER         Y                         
PLAN_DISK_READS             NUMBER         Y                         
PLAN_DISK_WRITES            NUMBER         Y                         
PLAN_ELAPSED_TIME           NUMBER         Y                         
PLAN_LAST_STARTS            NUMBER         Y                         
PLAN_LAST_OUTPUT_ROWS       NUMBER         Y                         
PLAN_LAST_CR_BUFFER_GETS    NUMBER         Y                         
PLAN_LAST_CU_BUFFER_GETS    NUMBER         Y                         
PLAN_LAST_DISK_READS        NUMBER         Y                         
PLAN_LAST_DISK_WRITES       NUMBER         Y                         
PLAN_LAST_ELAPSED_TIME      NUMBER         Y                         
PLAN_POLICY                 VARCHAR2(10)   Y                         
PLAN_ESTIMATED_OPTIMAL_SIZE NUMBER         Y                         
PLAN_ESTIMATED_ONEPASS_SIZE NUMBER         Y                         
PLAN_LAST_MEMORY_USED       NUMBER         Y                         
PLAN_LAST_EXECUTION         VARCHAR2(10)   Y                         
PLAN_LAST_DEGREE            NUMBER         Y                         
PLAN_TOTAL_EXECUTIONS       NUMBER         Y                         
PLAN_OPTIMAL_EXECUTIONS     NUMBER         Y                         
PLAN_ONEPASS_EXECUTIONS     NUMBER         Y                         
PLAN_MULTIPASSES_EXECUTIONS NUMBER         Y                         
PLAN_ACTIVE_TIME            NUMBER         Y                         
PLAN_MAX_TEMPSEG_SIZE       NUMBER         Y                         
PLAN_LAST_TEMPSEG_SIZE      NUMBER         Y                         
SPARE1                      NUMBER         Y                         
SPARE2                      NUMBER         Y                         
SPARE3                      BLOB           Y                         
SPARE4                      CLOB           Y                         
SPARE5                      NUMBER         Y                         
SPARE6                      NUMBER         Y                         
SPARE7                      CLOB           Y                         
SPARE8                      CLOB           Y

从plsql对dbms_sqltune.create_stgtab_sqlset的参数table_name的描述中提到了大小写敏感,但是从实际情况来看只能用大写才能方便执行迁移。

db file async I/O submit等待事件的故障诊断

1

2

3

4

朋友公司一个erp系统业务办理不了,但从前台等待事件来看 DB CPU占了%DB time的91.81%,这个awr报告采样时间是两个小时,总的DB time是810分种服务器显示有24个逻辑CPU,每个CPU的耗时是33.75分钟,CPU的使用率也不是很高,那么我们来看一下后台等待事件。

5
后台等待事件排在第一的是 db file async I/O submit,这是一个异步IO相关的等待事件,而LNS wait on SENDREQ,LGWR-LNS wait on channel是与DG相关的日志传输相关的等待事件。这里的操作系统是Linux,而根据Doc ID 1274737.1文档描述,当disk_asynch_io=true时,而filesystemio_options=none,那么正常的文件系统在Linux系统支持异步I/O的情况下Oracle也不能使用异常I/O。

我们先来检查一下Linux系统中是否执行过异步I/O操作

[oracle@ErpOracle01 ~]$ cat /proc/slabinfo | grep kio
kioctx               376    640    384   10    1 : tunables   54   27    8 : slabdata     64     64      2
kiocb                  0      0    256   15    1 : tunables  120   60    8 : slabdata      0      0      0

可以看到kiocb的前两列为0说明没有执行异步I/O操作,用于存储Oracle数据文件的就是正常的文件系统

[root@ErpOracle01 ~]# fdisk -l

Disk /dev/sda: 598.9 GB, 598879502336 bytes
255 heads, 63 sectors/track, 72809 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000a66fb

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1         131     1048576   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2             131        8486    67108864   82  Linux swap / Solaris
/dev/sda3            8486       72810   516684800   83  Linux

在这种情况下要让Oracle使用异步I/O将参数filesystemio_options设置为’asynch’。

SQL> show parameter disk_asynch_io

NAME                                 TYPE                               VALUE
------------------------------------ ---------------------------------  ------------------------------
disk_asynch_io                       boolean                            TRUE

SQL> show parameter filesystemio_options

NAME                                 TYPE                               VALUE
------------------------------------ ---------------------------------  ------------------------------
filesystemio_options                 string                             none
SQL> alter system set  filesystemio_options='asynch' scope=spfile;

System altered.

LNS wait on SENDREQ是DG的RFS I/O时间和网络时间的总计,而它可能是由网络带宽或备库的I/O性能引起的。而这经确认是备库的I/O性能引起的,再加上在这期间后台维护人员在主库做大批量的数据更新。在主库中出现了Log file sync和log file parallel write等待事件。LGWR-LNS wait on channel等待事件是日志写进程或网络服务器进程在KSR通道上等待接受消息所花的时间.LNS wait on SENDREQ, Log file sync, log file parallel write, LGWR-LNS wait on channel通过调整备库I/O性能和对主库增加online and standby redo logs来改善。然后在中午休息时间重启了数据库,经过三天的运行,业务没有出现办理不了的情况了。

如何修改oracle自动统计信息收集所使用的参数

这里主要介绍如何来修改Oracle 10g与11g统计信息收集所使用的缺省参数以及Oracle对这些参数提供的一个参考值。当使用一个维护窗口来自动收集统计信息时这些参数是非常有用的,这些缺省参数定义了如何来收集统计信息。在Oracle10g中使用dbms_stats.set_param与dbms_stats.get_param来完成缺省参数的修改,但在Oracle 11g中dbms_stats.set_param与dbms_stats.get_param已经被丢弃,进而使用dbms_stats.set_global_prefs与dbms_stats.get_prefs来进行缺省参数的修改。

在Oracle 10g中收集统计信息的缺省参数可以执行dbms_stats.set_param过程来进行修改。如果想要修改多个参数,那么对于每个参数都需要执行一次dbms_stats.set_param过程,语法如下:
DBMS_STATS.SET_PARAM ( pname IN VARCHAR2, pval IN VARCHAR2);
pname是参数名,pval是参数值。

可以使用dbms_stats.set_param进行修改的缺省参数
cascade:控制在相同时间索引是否被分析,缺省值为:true,可选值:true|false,由dbms_stats.set_param所设置的cascade的缺省值不能用于统计信息的导出或导入过程,只能用于统计信息的收集过程

SQL> select dbms_stats.get_param('cascade') from dual;

DBMS_STATS.GET_PARAM('CASCADE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE


SQL> exec dbms_stats.set_param('cascade','true');   

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('cascade') from dual;

DBMS_STATS.GET_PARAM('CASCADE')
--------------------------------------------------------------------------------
TRUE

degree:并行度,缺省值是NULL,如果设置为auto_degree,那么将会自动判断并行度。该参数可选的参数值为null/integer(所有对象所使用的并行度为integer

SQL> select dbms_stats.get_param('degree') from dual;

DBMS_STATS.GET_PARAM('DEGREE')
--------------------------------------------------------------------------------
NULL

SQL> exec dbms_stats.set_param('degree','4');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('degree') from dual;

DBMS_STATS.GET_PARAM('DEGREE')
--------------------------------------------------------------------------------
4

estimate_percent:行数采样的百分比,缺省值为dbms_stats.auto_sample_size,可选的参数值:一个有效范围[0.000001,100]/null使用100%来进行计算/dbms_stats.auto_sample_size。使用dbms_stats.auto_sample_size时根据数据库的版本,采样百分比会有所不同,10g中的采样百分比要比11g所使用的小。

SQL> select dbms_stats.get_param('estimate_percent') from dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> exec dbms_stats.set_param('estimate_percent','null');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('estimate_percent') from dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
--------------------------------------------------------------------------------
NULL

method_opt:用于收集列统计信息,缺省值为:for all columns size auto,可选参数值:
for all [indexed| hidden] columns [size_clause]
for columns [size_clause] column|attribute [size_clause][,column|attribute

[size_clause]…]

size_clause:=SIZE{integer |repeat |auto |skewonly}
integer:指定histogram的桶数,范围[1,254]
repeat:只对已经存在直方图的列收集直方图
auto:Oracle根据列中数据的分布与列的工作负载来决定是否收集直方图
skewonly:Oracle将根据列中数据的分布来决定是否收集直方图

SQL> select dbms_stats.get_param('method_opt') from dual;        

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> exec dbms_stats.set_param('method_opt','for all columns size 1');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('method_opt') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 1

no_invalidate:判断是否将依赖的游标设置为无效,缺省参数值是dbms_stats.auto_invalidate,可选参数:
dbms_stats.auto_invalidate Oracle决定是否将依赖于统计信息的游标设置为无效
true 依赖于统计信息的游标将不会失效
false 依赖于统计信息的游标将会失效

SQL> select dbms_stats.get_param('no_invalidate') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

SQL> exec dbms_stats.set_param('no_invalidate','false');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('no_invalidate') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------------------
FALSE

granularity: 统计信息收集的粒度,缺省参数’auto’,可选参数:
‘auto’ 基于分区类型来决定粒度
‘all’ 收集所有统计信息(子分区,分区与全局)
‘global’ 收集全局统计信息
‘global and partition’ 收集全局与分区级别的统计信息。即使是一个复合分区对象,那么子分区
级统计信息不会被收集。
‘partition’ 收集分区级别统计信息
‘subpartition’ 收集子分区级别统计信息

‘default’已经过时了。这个选项只能收集全局与分区级别的统计信息。

SQL> exec dbms_stats.set_param(‘granularity’,’all’);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param(‘granularity’) from dual;

DBMS_STATS.GET_PARAM(‘GRANULARITY’)
——————————————————————————–
ALL

autostats_target:这个参数只能用于自动统计信息收集,这个参数控制着那些对象会被收集统计信息
缺省值是’auto’,可选参数值:
‘auto’ oracle将会决定那些对象将被收集统计信息
‘all’ 对系统中的所有对象收集统计信息
‘oracle’ 对所有oracle所拥有的对象收集统计信息,这个选项将会限制自动统计信息收集作业所选择的方案列表,将对Oracle组件系统方案列表收集统计信息,例如SYS,SYMAN,WMSYS与EXFSYS方案

SQL> select dbms_stats.get_param('autostats_target') from dual;                     

 

DBMS_STATS.GET_PARAM('AUTOSTATS_TARGET')
--------------------------------------------------------------------------------
AUTO

SQL> exec dbms_stats.set_param('autostats_target','all');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('autostats_target') from dual;

DBMS_STATS.GET_PARAM('AUTOSTATS_TARGET')
--------------------------------------------------------------------------------
ALL

dbms_stats.set_param的使用
为了执行这个过程,用户必须有sysdba或analyze any dictionary与analyze any系统权限。

varchar2
类型的参数与值都需要用引号括起来,就算他们代表数字也是一样。注意NULL与’NULL’是不同的:当设置为NULL,没有引号,设置为Oracle的建议值,当设置为’NULL’时,参数等于NULL

下面介绍如何来修改Oracle 11g统计信息收集操作的缺省参数并对这些参数提供一个参考值,及如何对其进行修改。这些缺省参数将会用来于在维护窗口中自动统计信息收集来收集统计信息。

在Oracle 11g中收集统计信息主要有以下四个过程来修改缺省参数:
set_global_prefs
set_schema_prefs
set_database_prefs
set_table_prefs

根据你需要修改的级别来选择特定的过程来修改这些缺省参数。例如,如果想要对单个表修改缺省参数据,那么应该使用set_table_prefs过程,set_database_prefs用来维护数据库级别的设置。对于每一个要修改的参数都需要执行一次修改过程。

set_global_prefs
能够改变dbms_stats.gather_*_stats过程的缺省参数,在没有表级特定设置的情况下来对数据库中任何对象收集统计信息。对于全局设置所有参数都使用缺省值,除非表级特定设置或者在dbms_stats.gather_*_stats命令中显式设置参数。通过这个过程进行的改变将会影响改变之后所创建的任何对象。新对象将对所有参数使用global_pref值。
语法:
dbms_stats.set_global_prefs(pname in varchar2,pvalue in varchar2);
下面的语句将对所有对象在全局级设置no_invalidate为false:

SQL> select dbms_stats.get_prefs('no_invalidate') from dual;       

DBMS_STATS.GET_PREFS('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

SQL> exec dbms_stats.set_global_prefs(pname=>'no_invalidate',pvalue=>'false');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('no_invalidate') from dual;

DBMS_STATS.GET_PREFS('NO_INVALIDATE')
--------------------------------------------------------------------------------
FALSE

set_table_prefs
可以用来修改dbms_stats.gather_*_stats过程收集统计信息的缺省参数,但仅限于表级别。
语法:
dbms_stats.set_table_prefs(ownname in varchar2,tabname varchar2,pname in varchar2,pvalue

in varchar2);

例如,下面的语句将对scott用户的emp表进行设置,因此索引统计信息不会在收集表统计信息时而被收集:



SQL> select table_name,num_rows,blocks,last_analyzed from dba_tables where 

owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZE
------------------------------ ---------- ---------- ------------
DEPT                                    4          5 02-DEC-15
EMP                                 30014         80 03-MAY-16
SALGRADE                                5          5 02-DEC-15
BONUS                                   0          0 02-DEC-15
T2                                      0          0 03-MAY-16

SQL> select 

table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from 

dba_indexes where owner='SCOTT' and table_name='EMP';

TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS 

DISTINCT_KEYS   NUM_ROWS LAST_ANALYZE
------------------------------ ------------------------------ ---------- ----------- 

------------- ---------- ------------
EMP                            PK_EMP                                  1          57     

    30014      30014 03-MAY-16
EMP                            EMP_EMPNO_DEPTNO                        1          72     

    30014      30014 03-MAY-16


SQL> exec dbms_stats.gather_table_stats

(ownname=>'scott',tabname=>'emp',estimate_percent=>100,method_opt=>'for all columns size 

repeat');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,last_analyzed from dba_tables where 

owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZE
------------------------------ ---------- ---------- ------------
DEPT                                    4          5 02-DEC-15
EMP                                 30014         80 19-MAY-16
SALGRADE                                5          5 02-DEC-15
BONUS                                   0          0 02-DEC-15
T2                                      0          0 03-MAY-16

SQL> select 

table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from 

dba_indexes where owner='SCOTT' and table_name='EMP';

TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS 

DISTINCT_KEYS   NUM_ROWS LAST_ANALYZE
------------------------------ ------------------------------ ---------- ----------- 

------------- ---------- ------------
EMP                            PK_EMP                                  1          57     

    30014      30014 03-MAY-16
EMP                            EMP_EMPNO_DEPTNO                        1          72     

    30014      30014 03-MAY-16

但如果在dbms_stats.gather_table_stats过程指定cascade为true还是会在收集表统计信息的同时收集索引统计信息

SQL> exec dbms_stats.gather_table_stats

(ownname=>'scott',tabname=>'emp',estimate_percent=>100,method_opt=>'for all columns size 

repeat',cascade=>true);        

PL/SQL procedure successfully completed.

SQL>  select table_name,num_rows,blocks,last_analyzed from dba_tables where 

owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZE
------------------------------ ---------- ---------- ------------
DEPT                                    4          5 02-DEC-15
EMP                                 30014         80 19-MAY-16
SALGRADE                                5          5 02-DEC-15
BONUS                                   0          0 02-DEC-15
T2                                      0          0 03-MAY-16

SQL> select 

table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from 

dba_indexes where owner='SCOTT' and table_name='EMP';

TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS 

DISTINCT_KEYS   NUM_ROWS LAST_ANALYZE
------------------------------ ------------------------------ ---------- ----------- 

------------- ---------- ------------
EMP                            PK_EMP                                  1          57     

    30014      30014 19-MAY-16
EMP                            EMP_EMPNO_DEPTNO                        1          72     

    30014      30014 19-MAY-16

set_schema_prefs
用来修改dbms_stats.gather_*_stats过程的缺省参数值,只对指定方案中的所有已经存在的对象生效。这个过程将会对指定方案中的每个表调用一次set_table_prefs过程。因为set_schema_prefs过程会对方案中的每个表调用一次set_schema_prefs过程,所以不会对该过程执行以后所创建的任何对象产生影响,新创建的对象将对所有参数使用global_pref值。
语法:
dbms_stats.set_schema_prefs(ownname in varchar2,pname in varchar2,pvalue in varchar2);
例如,以下命令设置当表中的记录数发生改变的百分比超过一定阈值就认为表的统计信息老旧了,并且应该对scott方案中的所有对象重新收集统计信息:

exec dbms_stats.set_schema_prefs(ownname=>'scott',pname=>'stale_percent',pvalue=>'5');

set_database_prefs
用来修改dbms_stats.gather_*_stats过程的缺省参数值,对数据库中所有用户定义的方案生效。通过设置add_sys参数为true可以包含sys与system方案。这个过程会对数据库中的每个表调用一次set_table_prefs过程。所以在过程执行后所创建的新对象,所有参数都将使用global_pref值。
语法:
dbms_stats.set_databasae_prefs(pname in varchar2,pvalue in varchar2,add_sys in boolean

default false);

例如,下面的命令对所有方案中表中数据分布存在倾斜的列收集直方图:
exec dbms_stats.set_database_prefs(pname=>’METHOD_OPT’,pvalue=>’FOR ALL COLUMNS SIZE

SKEWONLY’,add_sys=>TRUE)

相对于10g,11g对于统计信息收集新增了以下参数:
publish:这个参数决定在统计信息收集操作完成后是否发布新收集的统计信息。从11gr1开始,用户可以收集统计信息但不立即发布使用统计信息。在发布使用新收集的统计信息之前DBA可以测试新收集的统计信息。缺省值:true,可选值为true|false

incremental:这个参数决定在每次对分区收集统计信息时是否收集分区的全局统计信息。缺省值为:false,可选值true|false

stale_percent:这个参数判断表中记录发生改变的百份比达到指定阈值后,表的统计信息将会被识为过时应该重新收集统计信息,缺省值:10%,可选值:正数

将参数还原成缺省值
为了将参数设置成为缺省值,可以将参数值设置为null:
exec dbms_stats.set_global_prefs(‘no_invalidate’,null);

查看参数值的方法
为了查看参数的当前值可以使用dbms_stats.get_prefs过程:
dbms_stats.get_prefs,这个函数将返回指定参数的缺省值,其语法如下:
dbms_stats.get_prefs(pname in varchar2,ownname in varchar2 default null,tabname in

varchar2 default null)

当指定owner(ownname)与table_name(tabname)时将返回对于该表的特定设置,其它情况下返回的是全局设置。

例如:

SQL>  select dbms_stats.get_prefs('CASCADE','SCOTT','EMP') from dual;

 DBMS_STATS.GET_PREFS('CASCADE','SCOTT','EMP')
 --------------------------------------------------------------------------------
 FALSE

 

SQL> select dbms_stats.get_prefs('STALE_PERCENT') from dual;

 DBMS_STATS.GET_PREFS('STALE_PERCENT')
 --------------------------------------------------------------------------------
 10

在收集统计信息之前可以根据需要与测试结果来对这些参数进行设置,这样在使用Oracle统计信息收集job来收集统计信息时就可以满足你的需要。

logminer来恢复在表DDL之前被删除的数据

做这个测试是因为前同事(开发人员)在客户现场做维护误删除了一张表的记录,但在删除表之后修改了表的结构(修改了字段的精度),发现误删除记录后,想通对表执行闪回查询来恢复被删除的记录发现不能闪回了,因为表结构发现了修改(ORA-01466: unable to read data – table definition has changed)。而且这个数据库没有备份,只有归档。简单的方法就是通过logminer来挖掘归档日志来进行恢复。

一.创建测试表t1,并插入两条记录

SQL> create table t1(t_id number,t_name varchar2(50));

Table created.

SQL>  alter table t1 add t_salary number(8);

Table altered.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T_ID                                               NUMBER
 T_NAME                                             VARCHAR2(50)
 T_SALARY                                           NUMBER(8)

SQL> insert into t1 values(1,'jy',10000);

1 row created.

SQL> insert into t1 values(2,'wj',8000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

      T_ID T_NAME                                               T_SALARY
---------- -------------------------------------------------- ----------
         1 jy                                                      10000
         2 wj



二.删除表中记录
SQL> alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;

Session altered.

SQL> select sysdate from dual;

SYSDATE
——————–
2016-05-13 11:07:02

删除表t1中t_id=2的记录,通过logminer来恢复的记录就是它

SQL> delete from t1 where t_id=2;

1 row deleted.

SQL> commit;

Commit complete.

在表结构没有发生改变之前,尝试使用闪回查询执行成功

SQL> select *  from t1     as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss');

      T_ID T_NAME                                               T_SALARY
---------- -------------------------------------------------- ----------
         1 jy                                                      10000
         2 wj                                                       8000

修改表结构,这里只是简单的修改了字段类型的长度

SQL> alter table t1 modify t_salary number(10);

Table altered.

在表结构发生改变之后,尝试使用闪回查询执行报错

SQL> select *  from t1     as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss');
select *  from t1     as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss')
               *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

三.使用logminer来挖掘归档重做日志
查看当前的归档日志文件

SQL> set long 900
SQL> set linesize 900
SQL> set pagesize 900
SQL> col name for a100
SQL> select name,sequence#,first_change# from v$archived_log ;

NAME                                                                                                  SEQUENCE# FIRST_CHANGE#
---------------------------------------------------------------------------------------------------- ---------- -------------
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_3_bkl8b4to_.arc                      3        559310
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_4_bkl92rxm_.arc                      4        590316
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_5_bkl94mv3_.arc                      5        622788
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_6_bkl9612c_.arc                      6        645078
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_7_bkl97n1p_.arc                      7        656708
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_8_bkl99bb9_.arc                      8        670463
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_9_bkl9c4v2_.arc                      9        688685
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_10_bkl9dvf0_.arc                    10        705191
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_11_bkl9gjpd_.arc                    11        715113
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_12_bkl9hpqq_.arc                    12        731487
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_13_bkl9m1gk_.arc                    13        737140
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_14_bkl9mlg9_.arc                    14        749330
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_15_bkl9n5ky_.arc                    15        757183
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_16_bkl9o2p1_.arc                    16        766296
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_17_bkl9oyz0_.arc                    17        773422
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_18_bkl9qcpf_.arc                    18        779449
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_19_bkl9rhv4_.arc                    19        790719
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_20_bkl9ssv4_.arc                    20        804844
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_21_bkl9w2xt_.arc                    21        814648
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_22_bkl9xjx8_.arc                    22        826389
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_23_bkl9ym63_.arc                    23        834321
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_24_bklb0gh1_.arc                    24        846100
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_25_bklb17f6_.arc                    25        863715
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_26_bklb23gw_.arc                    26        874870
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_27_bklb3pmz_.arc                    27        885043
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_28_bklb5fl3_.arc                    28        898767
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_29_bklb6xr6_.arc                    29        912954
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_30_bklb89wn_.arc                    30        923000
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_31_bklb97v9_.arc                    31        929385
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_32_bklbb8yk_.arc                    32        936055
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_33_bklbccbm_.arc                    33        946964
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_34_bklbd33q_.arc                    34        952518
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_35_bklbf7v8_.arc                    35        955561
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_36_bklbg9mf_.arc                    36        966403
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_37_bklcon42_.arc                    37        977840
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_38_bkldl4pl_.arc                    38        996480
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_39_bklfrzhj_.arc                    39       1018201
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_40_bklor7cf_.arc                    40       1019655
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_04_13/o1_mf_1_41_cjvtwjm2_.arc                    41       1036392
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc                    42       1055116

40 rows selected.

强制日志切换将当前使用的联机重做日志文件时行归档

SQL> alter system switch logfile;

System altered.

SQL> select name,sequence#,first_change# from v$archived_log ;

NAME                                                                                                  SEQUENCE# FIRST_CHANGE#
---------------------------------------------------------------------------------------------------- ---------- -------------
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_3_bkl8b4to_.arc                      3        559310
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_4_bkl92rxm_.arc                      4        590316
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_5_bkl94mv3_.arc                      5        622788
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_6_bkl9612c_.arc                      6        645078
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_7_bkl97n1p_.arc                      7        656708
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_8_bkl99bb9_.arc                      8        670463
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_9_bkl9c4v2_.arc                      9        688685
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_10_bkl9dvf0_.arc                    10        705191
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_11_bkl9gjpd_.arc                    11        715113
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_12_bkl9hpqq_.arc                    12        731487
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_13_bkl9m1gk_.arc                    13        737140
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_14_bkl9mlg9_.arc                    14        749330
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_15_bkl9n5ky_.arc                    15        757183
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_16_bkl9o2p1_.arc                    16        766296
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_17_bkl9oyz0_.arc                    17        773422
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_18_bkl9qcpf_.arc                    18        779449
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_19_bkl9rhv4_.arc                    19        790719
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_20_bkl9ssv4_.arc                    20        804844
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_21_bkl9w2xt_.arc                    21        814648
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_22_bkl9xjx8_.arc                    22        826389
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_23_bkl9ym63_.arc                    23        834321
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_24_bklb0gh1_.arc                    24        846100
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_25_bklb17f6_.arc                    25        863715
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_26_bklb23gw_.arc                    26        874870
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_27_bklb3pmz_.arc                    27        885043
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_28_bklb5fl3_.arc                    28        898767
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_29_bklb6xr6_.arc                    29        912954
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_30_bklb89wn_.arc                    30        923000
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_31_bklb97v9_.arc                    31        929385
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_32_bklbb8yk_.arc                    32        936055
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_33_bklbccbm_.arc                    33        946964
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_34_bklbd33q_.arc                    34        952518
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_35_bklbf7v8_.arc                    35        955561
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_36_bklbg9mf_.arc                    36        966403
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_37_bklcon42_.arc                    37        977840
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_38_bkldl4pl_.arc                    38        996480
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_39_bklfrzhj_.arc                    39       1018201
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_40_bklor7cf_.arc                    40       1019655
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_04_13/o1_mf_1_41_cjvtwjm2_.arc                    41       1036392
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc                    42       1055116
/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_43_cmbkro1j_.arc                    43       1083648

41 rows selected.

向logminer增加需要分析的归档重做日志文件

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc',options=>dbms_logmnr.NEW);

PL/SQL procedure successfully completed.

继续向logminer增加需要分析的归档重做日志文件

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_43_cmbkro1j_.arc',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

执行分析

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

将分析的内容存储在临时表logmnr_contents中

SQL> create table logmnr_contents as select * from v$logmnr_contents ;

Table created.

终止分析操作

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

查询T1所产生的日志记录

SQL> select username,scn,timestamp,sql_redo,sql_undo from logmnr_contents where seg_name='T1';

USERNAME                              SCN TIMESTAMP   SQL_REDO                                                                                                              SQL_UNDO
------------------------------ ---------- ---------   -------------------------------------------------------------------------------------------------------------------   ------------------------------------------------------------------------------------------------------------------------
UNKNOWN                           1102088 13-MAY-16  create table t1(t_id number,t_name varchar2(50));

JY                                1102114 13-MAY-16  alter table t1 add t_salary number(8);

UNKNOWN                           1102129 13-MAY-16  insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('1','jy','10000');                                           delete from "JY"."T1" where "T_ID" = '1' and "T_NAME" = 'jy' and "T_SALARY" = '10000' and ROWID = 'AAANc6AAEAAAAGEAAA';

UNKNOWN                           1102822 13-MAY-16  insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000');                                            delete from "JY"."T1" where "T_ID" = '2' and "T_NAME" = 'wj' and "T_SALARY" = '8000' and ROWID = 'AAANc6AAEAAAAGFAAA';

UNKNOWN                           1103738 13-MAY-16  delete from "JY"."T1" where "T_ID" = '2' and "T_NAME" = 'wj' and "T_SALARY" = '8000' and ROWID = 'AAANc6AAEAAAAGFAAA'; insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000');

JY                                1103789 13-MAY-16  alter table t1 modify t_salary number(10);

从查询结果来看删除操作对应的sql_redo为:delete from “JY”.”T1″ where “T_ID” = ‘2’ and “T_NAME” = ‘wj’ and “T_SALARY” = ‘8000’ and ROWID = ‘AAANc6AAEAAAAGFAAA’,对应的sql_undo为:insert into “JY”.”T1″(“T_ID”,”T_NAME”,”T_SALARY”) values (‘2′,’wj’,’8000′);为了恢复删除的t_id=2的记录,只需要执行sql_undo为insert into “JY”.”T1″(“T_ID”,”T_NAME”,”T_SALARY”) values (‘2′,’wj’,’8000′)的语句就能恢复。