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
数据库装载完毕。
数据库已经打开。

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

Proudly powered by WordPress | Indrajeet by Sus Hill.