Search Results for: cost

SQL Performance Analyzer SPA常用脚本汇总

SPA常用脚本汇总

附件为 一个SPA报告 spa_buffergets_summary

 

SQL 性能分析器 SQL Performance Analyzer SPA

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

 

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

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

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

 

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

SQL 性能分析器:概要

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

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

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

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

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

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

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

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

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

Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3

 

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

 

执行方法如下:

 

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

 

 

 

 

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

 

 

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

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

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

==>过滤条件使用

 

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

 

 

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

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

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

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

  CLOSE cur;
END;
/

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

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

END;
/

 

 

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

 

 

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

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

 

 

 

将SQL TUNING SET Pack到表中:

 

 

set echo on
select name,statement_count from dba_sqlset;

drop table maclean.pack_sqlset purge;

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

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

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

 

 

 

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

 

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

 

 

创建SPA任务 并运行;

 

 

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

PL/SQL procedure successfully completed.

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

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

 

 

 

执行任务比较

 

 

 

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

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

比较实际执行时长 

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

比较物理读

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

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

 

 

 

获得SPA报告:

 

 

 

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

产生buffergets 比较report    

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

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

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

 

 

 

 

 

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

 

 

For 9i Upgrade to 10g

 

 

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

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

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

9i 

?/rdbms/admin/dbmssupp

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

exec dbms_support.stop_trace;

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

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

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

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

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

  close mycur;
end;
/

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

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

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

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

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

select sofar,totalwork from V$ADVISOR_PROGRESS where task_id=<TID>;

为什么AWR中的DB TIME /DB CPU/ CPU TIME 过大 过小? 不准确?

这是因为,oracle数据库实例作为一个操作系统用户始终需要使用操作系统提供的函数来给这些时间指标 DB TIME /DB CPU/ CPU TIME 测时, 而我们也说过 “自我观察总是容易出现问题”,例如自己给自己的短跑计时,那么可能总是不太准确。

其次由于不同的操作系统上不同的系统函数的表现不一样也会有区别。

对于CPU TIME 一般使用 getrusage() 函数来计时,该函数的粒度为10,000(10*1000) msec。

对于DB TIME 则会使用gettimeofday() 函数以外部计时的形式来计时。

在AIX 上存在

Bug 13846587  AIX: Unrealistic CPU Time / DB time with 11.2.0.3 for IBM AIX

This problem is introduced in 11.2.0.3 on AIX systems by the change from 
 
This is an issue where the value of a CPU time related statistics such as 
the DB time in an AWR or the Execute CPU time in a TKPROF shows an 
enlarged value in the scale of E11 seconds.
 
Note:
 There is also an AIX problem in that thread_cputime() may return an incorrect value in some 
   cases, leading to incorrect/unexpected DB CPU figures in Oracle - see Note:1631769.1.


Following is complete messahe Customer  given regarding to that :
===================================================================
We had a ticket and a call with IBM on Friday. IBM confirms that vac.cfg.61 
file delivered in their C++ compiler which is a separate package from the 
runtime compiler that comes by default with the OS upgrade. Our OS runtime 
compiler is  11.1.0.2 that is the latest and the greatest delivered with IBM 
AIX 6.1 version.
 
Therefore I confirm that we will not have or not able to get vac.cfg.61 file 
because (1) having compiler on the production server is against Bank of 
America IT policies (2) We do not have license for the compiler.
 
What am I expecting from Oracle? I am expecting Oracle to resolve this issue 
where patch installs on our database w/o any installation issues. This is SEV 
1 issue and I will really appreciate if this issue is resolved ASAP.
 

RELEASE NOTES:
]]If the value of a cpu time related statistics such as the DB time in an AWR o
]]r the Execute cpu time in a TKPROF shows an enlarged value in the scale of E1
]]1 seconds, then apply this patch to correct the problem.
 
 
REDISCOVERY INFORMATION:
An AWR report shows enlarged (E11 secs) "DB time" values.
A TKPROF report shows enlarged (E11 secs) Execute cpu time.
WORKAROUND:

Bug 7445289 – DB time reported too high when there are a lot of direct path reads (Doc ID 7445289.8)

 

Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 11.2
Versions confirmed as being affected
  • (None Specified)
Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in

Description

DB time shown in AWR report's Time Model section can be higher than 
the actual value when there are a large number of direct path wait 
events with small (<1ms) avg wait time.
 
eg: When waiting on "direct path read" there may be some element 
    of CPU time included in the wait time. 
    Hence adding DB CPU time to time in non-idle wait time to get
    a DB time figure can give an over high figure due to double 
    counting of any CPU time used during the wait itself.
 
Note: 
 This is only a problem in the reporting of the DB time.
 
Workaround
 Infer DB time from ASH report. time_period * avg_active_sessions

 

In some AWR reports, the db cpu is larger than db time for example:
DB Time:

 

1911984.1_a 1911984.1_b 1911984.1_c

  • The issue can also be verified by 10046 trace:
    SQL> alter session set events '10046 trace name context forever, level 12';
    DECLARE
     ABC DATE;
    BEGIN
     FOR I IN 1 .. 10000 LOOP
       SELECT SYSDATE INTO ABC FROM DUAL;
     END LOOP;
    END;
    /
    SQL> alter session set events '10046 trace name context off';
    

    On checking the trace:

    ********************************************************************************
    
    SQL ID: c749bc43qqfz3 Plan Hash: 1388734953
    
    SELECT SYSDATE
    FROM
    DUAL
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute  10000      0.38       0.37          0          0          0           0   <--- CPU is larger than elapsed.
    Fetch    10000      0.08       0.10          0          0          0       10000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    20001      0.46       0.48          0          0          0       10000
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
            1          1          1  FAST DUAL  (cr=0 pr=0 pw=0 time=3 us cost=2 size=0 card=1)
    
    ********************************************************************************
    

 

CHANGES

None

CAUSE

The issue is that CPU time and DB time are measured by different C routines.

The CPU time uses the getrusage() call which has a granularity of 10,000(10*1000) msec.
The DB Time uses the gettimeofday() call using the external operating system clocks which has no such issue.
I.e. getrusage() can measure only 0 or N*1000 microsec (N = 1,2,…) for a FETCH call but the real elapsed time may be somewhere in between the 2 values.
This means that getrusage tends to overestimate the CPU time so you can see a slightly higher value of “DB CPU” than “DB Time” especially when thousands of roundings are involved.

This is not a bug but development commented upon this in the following bugs:

Bug 19266484 awr reports db cpu greater than db time
Bug 16241074 awr shows wrong cpu information for some sqls when hyperthreading is enabled
Bug 19234247 incorrect cpu reported in awr report for sqls

 

SOLUTION

This is a reporting issue as opposed to an actual problem. Since the variance is likely to be low, the differences can be ignored.

REFERENCES

BUG:16241074 – AWR SHOWS WRONG CPU INFORMATION FOR SOME SQLS WHEN HYPERTHREADING IS ENABLED
BUG:19234247 – INCORRECT CPU REPORTED IN AWR REPORT FOR SQLS
BUG:19266484 – AWR REPORT DB CPU GREATER THAN DB TIME

 

Oracle 数据库完全破损/损坏对策

Block Corruption 造成的影响
行造成的直接影响

  1. 数据业务停止
  2. 数据恢复时间变长
  3. 修复工作错误,造成二次灾害
  4. 找原因的时间变长

数据坏的主要原因

并且有可能被分割的Layer风险

中数据保的机制

§ 单个系统中,考虑到了一些无法防御的的风险的机制。

–    例:因为人为错误删除数据时,在RAID结构中无法防御。

§ 复制数据中保护一致性与确实性的机制。

–    例:部分备份导致的数据完整性的欠缺。

§ 考虑到迅速切换以及确实的修复的机制。

–    例:由于灾害恢复训练不足,实际上无法切换,无法返回的备份。

了确地能提高工作的可持性需要什么?

Oracle Maximum Availability Architecture

§ Maximum Availability Architecture (MAA) 是基于oracle验证完成的高可用性的数码与成功事例的最佳实践。

§ MAA的目的

–    为了修复、查出、规避所有的停止的情况提供最优实践。

–    在样本中构成最优的高可用性的架构。

§ 不受硬件以及OS影响(不需要特定的高价的产品以及技术)

§ 马上就可以提供高可用性的解决策略(Oracle事先完成验证)

高可用性的数的最佳践。

Oracle MAA 的整体映像

Low-Cost, Integrated, Fully Active, High ROI

Oracle MAA Data Protection

Oracle Database 11g Release 2

Oracle Database
Data Protection功能

Type of Block Corruption

§ Data Block Corruption(Doc ID 840978.1)

–    Physical Block Corruption

–    Logical Block Corruption

§ Other Corruptions

–    Control file Corruption

§ Use control file mirror & Copy

–    Redo Corruption

§ ASM Mirroring / Use multiplexed log file

–    Dictionary Corruption(Doc ID 136697.1)

Doc ID 1088018.1 Master Note for Handling Oracle Database Corruption

KROWN#152523 [Master Notes] Corruption()

Data Block Corruption

§ 物理性的块内数据破损状态的块

–    破损例

•          Block Header不正确

•          Block Header与Footer信息不一致

•         数据缺失

•          Block配置地点不正确

•          0隐藏的Block

Physical Block Corruptions

Data Block Corruption

§ Block中结构发生理论破损的状态的块

–    物理性(Block Header以及Footer的信息、Checksum的计算结果)正确

–    破损例

•          行碎片的开始与结束位置不在块内。

•          行碎片直接发生重叠

•          锁定行碎片的ITL编号不正确

•          ITL展示的锁定中的行碎片的数量与实际不一致

•          Block中空白区域的尺寸不正确

•          Lost Write

Logical Block Corruptions

Oracle Database中最适合的破损检测

§ Oracle Database的Block并不是单独的bit的罗列,
明确地事先定义过的结构

à 正是有理解了Block的结构的Oracle Database,可以检测
Physical Corruption以及Logical Corruption。

à 并且,通过同时使用Oracle DatabaseTechnology,可以提高检测水平

–    OS、文件系统以及存储中,仅仅是和命令一样对块进行I/O,但无法判断块的结构作为一个数据块来说是否正确。Exadata Cell Storage Serverと、H.A.R.D Initiative 是可以检测的

Data Block Format

Oracle DatabaseData Protection

§ 控制检测范围、水平、破损类型的初始化参数

–    DB_BLOCK_CHECKSUM

–    DB_BLOCK_CHECKING

–    DB_LOST_WRITE_PROTECT

§ 定期检测功能

–    Oracle Recovery Manager(CHECK LOGICAL句 / VALIDATE 命令)

–    SQL> ANALYZE TABLE文(VALIDATE STRUCTURE CASCADE 选项)

提高检测水平的功能

DB_BLOCK_CHECKSUM

§ 利用Data Block中的Checksum的Physical Corruption检测的机制

–    向Disk写入Block之前

§ 将DBWR以及Direct Load的服务器进程、Checksum(从Block中所有数据为基础来计算出的数值)储存到Block Header之中。

–    从Disk中读出Block后

§ 读出Block的进程将重新计算的Checksum与储存到Block Header中的Checksum进行比较验证。

à 如果Checksum不一致为块内数据可能会可能判断Physical Block Corruption以及生了

概要

DB_BLOCK_CHECKSUM

中的每个操作

DB_BLOCK_CHECKSUM

§ 请注意设定值的不同造成的操作的不同

–    DB_BLOCK_CHECKSUM=TYPICAL

•          由于服务器进程的Block更新之后,Checksum为0

•          DBWR在向Disk写入时,计算Checksum再嵌入

à 每次更新,因不会Checksum而高效化

–    DB_BLOCK_CHECKSUM=FULL

•          由于服务器进程的Block更新之后,计算Checksum再嵌入

•          DBWR在向Disk写入时, 验证Checksum

à可以验证内存上的Block Corruption

Buffer Cache上被更新Data Block
Checksum
嵌入

DB_BLOCK_CHECKSUM

§ 每次发行时,请注意其中不同的操作

–    Release 11.1以降では、Redo BlockのChecksumの
将生成处理由生成了Redo的Foreground Process担当à LGWR

§ 但是,Release 11.1~11.2.0.1中,设定为FULL时,
LGWR在向磁盘写入Redo Block之前,会执行Foreground Process所生成的Checksum的完整性检查。

Redo BlockChecksum生成验证KROWN#155653

DB_BLOCK_CHECKSUM

检测出破的操作

DB_BLOCK_CHECKSUM

§ Primary Database’s Alert.log

Automatic Block Media Recovery(ABR)来自修复所参考的日志

DB_BLOCK_CHECKING

§ Buffer Cache变更Block之后,
通过检测理论性的完整性,可以检测Logical Corruption

–    即使是Checksum正确,可以检测理论性的不正确的状态。

–    变更后被标记的理由是由于DML数据更新以外,包含变更。

§ 例:伴随着DBWR的写出,变更Block Header的信息

–    不经过Buffer Cache的Direct Load Operation不在本次检测对象中

–    根据参数的设定值,可以控制检测的对象以及水平

概要

DB_BLOCK_CHECKING

§ 上位设定值包含下位设定值的检测

–     比如,「LOW」=OFF检查 + 所有的BlockBlock Header Check

 

 

 

 

 

 

–     基本上,Buffer Cache上的Block内容在变更后会执行检测,但
Block Header Check是RAC的实例之间的Block在传送后也会被执行

的操作

DB_BLOCK_CHECKING

–     包含没有commit的redo,发生错误之前的Block的状态

à 同一继续进行事的可能

检测出破后的操作DiskBlock是正常的情况

DB_BLOCK_CHECKING

§ Oracle Client

§ Alert.log

检测的参考日志DiskBlock正常的情况

DB_BLOCK_CHECKING

–     即使自动修复失败,请注意成功时,只会返回同样的错误。

§  之后,重新访问block时,会发生ORA-1578 或者ORA-600

à 需要手操作 Block Media RecoveryABR不会发动

检测出破后的操作DiskBlock也破的情况

DB_BLOCK_CHECKING

§ Oracle Client

§ Alert.log

检测时的参考日志DiskBlock生破的情况

Soft Corrupt

§ 检测出破损的(Oracle的破损与认识)Block中被加上的标记

–    访问这些被标记的块时,会发生ORA-1578。

不是与Physical Corruption / Logical Corruption同列的单词

DB_LOST_WRITE_PROTECT

§ 不管从存储装置中block的写入完成是否发出通知,实际上磁盘中没有被写入的事项。

–    因为Data Block的构造是正常的,
即使访问发生了Lost Write 的Block也没有发生错误

à 可能会有提供不正确的数据给顾客或者用风险

à 不正确的数据染可能会

§ Lost Write所影响的例子

–    不管是否有没有储备,都作为有储备来接收订单
–    不管是否接受订单都会变成没有接收订单

Lost Write是什么

DB_LOST_WRITE_PROTECT

§ Data Guard(Physical Standby Database)中检测出Lost Write的机制Primary Database中从磁盘中读出Block时,生成验证用的redo

§ Data File Number

§ Data Block AddressDBA

§ System Change NumberSCN

•          Data Guard的机制中,对Physical Standby Database传送Redo

•          比较验证Standby Database方的对象Block与Redo内的SCN

à 如果SCN不一致,可能Lost Write

概要

DB_LOST_WRITE_PROTECT

§ 需要Primary Database以及Standby Database两方面的设定

–    Primary 或者 Standby Database的两方面的定都是NONE无效

§ Primary因为没有生成验证用的redo无法用Standby来验证

§ 即使用Primary来生成Redo,用Standby也不能验证

各个的操作

DB_LOST_WRITE_PROTECT

§ 检测Lost Write的时机是?

–    从Primary Database中发生Lost Write的Block,从磁盘向Buffer读入时生成的Redo,Standby Database的MRP验证时

–         à 不是Lost WriteDisk的写入不足的瞬
  Lost WriteBlockDisk入的

 

–    特定Block中,即使发生Lost Write,只要不使用那个Block

§ 搜索结果以及更新事务都正常

§ 不正确的数据不会传染到其他的块中

Lost Write生以及检测时机不一致

DB_LOST_WRITE_PROTECT

§ 验证用redo的生成仅限于向Buffer Cache读入Block时

–    不经过Buffer Cache的Direct Path Read中,不生成验证用的Redo

 

§ 非Data Guard环境中,用TYPICAL以上的设定来生成验证用Redo

–    Media recovery可以验证Lost Write

 

§ 可以验证Standby Database生的Lost Write

–    与Primary中生成验证用redo + Standby中验证这样的功能相同

–    仅限这种情况,但也可以用ASM Mirror以及ABR来自修复(后面将讲到)

動作の補足

DB_LOST_WRITE_PROTECT

Lost Write检测的流程PrimaryLost Write的情况

DB_LOST_WRITE_PROTECT

§ Primary中发生的Lost Write在Standby中被检测出来

–    记录Standby DatabaseのAlert.log中发生的ORA-752

–    为了保护Standby Database的数据,自动停止MRP进程

§ 终止以后的Redo适用,防止不正确数据导致的数据污染

–    PRxx程的Trace File(xxx_xxx_prxx_xxx.trc)中记录了Block的详细内容

§ 访问Primary中的对象block时所生成的Redo记录的Dump

§ Standby中所保存的对象Block的Dump

–    从这些信息中,确认以后会发生Lost Write

检测Lost Write后的操作PrimaryLost Write的情况

DB_LOST_WRITE_PROTECT

Wed Oct 23 19:18:08 2013

Hex dump of (file 7, block 131) in trace file /u01/app/oracle/diag/rdbms/orcls/orcls1/trace/orcls1_pr02_1401.trc

Reading datafile ‘+DATA/orcls/datafile/lw.281.829593935’ for corruption at rdba: 0x01c00083 (file 7, block 131)

Read datafile mirror ‘DATA_0004’ (file 7, block 131) found same corrupt data (logically corrupt)

Read datafile mirror ‘DATA_0006’ (file 7, block 131) found same corrupt data (logically corrupt)

STANDBY REDO APPLICATION HAS DETECTED THAT THE PRIMARY DATABASE

LOST A DISK WRITE OF BLOCK 131, FILE 7

NO REDO AT OR AFTER SCN 3987667 CAN BE USED FOR RECOVERY.

Recovery of Online Redo Log: Thread 1 Group 7 Seq 103 Reading mem 0

Slave exiting with ORA-752 exception

Errors in file /u01/app/oracle/diag/rdbms/orcls/orcls1/trace/orcls1_pr02_1401.trc:

ORA-00752: 由于恢复检测出数据的写入欠缺。

ORA-10567: Redo is inconsistent with data block (file# 7, block# 131, file offset is 1073152 bytes)

ORA-10564: tablespace LW

ORA-01110: 数据文件7: ‘+DATA/orcls/datafile/lw.281.829593935’

ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 87637

Wed Oct 23 19:18:12 2013

Recovery Slave PR02 previously exited with exception 752

Wed Oct 23 19:18:12 2013

MRP0: Background Media Recovery terminated with error 448

Errors in file /u01/app/oracle/diag/rdbms/orcls/orcls1/trace/orcls1_pr00_1395.trc:

ORA-00448: バックグラウンド・プロセスが正常終了しました。

MRP0: Background Media Recovery process shutdown (orcls1)

Lost WriteStandby DatabaseAlert.log出的一部分摘

PrimaryLost Write的情况

 

DB_LOST_WRITE_PROTECT

Hex dump of (file 7, block 131)

Dump of memory from 0x00000000F03B0000 to 0x00000000F03B2000

0F03B0000 0000A206 01C00083 003CC55A 04010000  [……..Z.<…..]

STANDBY REDO APPLICATION HAS DETECTED THAT THE PRIMARY DATABASE

LOST A DISK WRITE OF BLOCK 131, FILE 7

The block read on the primary had SCN 3977658 (0x0000.003cb1ba) seq 1 (0x01)

 while expected to have SCN 3982682 (0x0000.003cc55a) seq 1 (0x01)

The block was read at SCN 3987667 (0x0000.003cd8d3), BRR:

CHANGE #1 TYP:2 CLS:6 AFN:7 DBA:0x01c00083 OBJ:87637 SCN:0x0000.003cb1ba SEQ:1 OP:23.2 ENC:0 RBL:1

REDO RECORD – Thread:1 RBA: 0x000067.00000128.0010 LEN: 0x0034 VLD: 0x10

SCN: 0x0000.003cd8d3 SUBSCN:  1 10/23/2013 19:18:16

(LWN RBA: 0x000067.00000126.0010 LEN: 0003 NST: 0001 SCN: 0x0000.003cd8cf)

CHANGE #1 TYP:2 CLS:6 AFN:7 DBA:0x01c00083 OBJ:87637 SCN:0x0000.003cb1ba SEQ:1 OP:23.2 ENC:0 RBL:1

 Block Read – afn: 7 rdba: 0x01c00083 BFT:(1024,29360259) non-BFT:(7,131)

              scn: 0x0000.003cb1ba seq: 0x01

              flags: 0x00000006 ( dlog ckval )

PRxxTrace File出例Primary生了Lost Write的情况

DB_LOST_WRITE_PROTECT

Lost Write检测流程StandbyLost Write的案例

DB_LOST_WRITE_PROTECT

§ 在Standby Database检测出了 Lost Write。

§ 与Primary中发生的Lost Write不同,自动进行修复

–    Primary Database中因为保存了最新正常Block

      Data GuardAutomatic Block Media Recovery修复

–    如果自动修复的话就不会发生ORA-752(Alert.log中没有输出)

§ MRP进程正常继续运行

检测Lost Write的操作Standby生了Lost Write的情况

DB_LOST_WRITE_PROTECT

检测Lost Write后的操作总结

DB_ULTRA_SAFE Parameter

§ 通过变更DB_ULTRA_SAFE参数值,可以一起变更3个参数值

–    本参数的默认值是FALSE

–    明确地个别设定各个参数时,优先个别设定值

提高检测水平的3个参数的一致

OLTPWorkload性能

§ Exadata X2-2 Quarter Rack( 2node RAC结构)

§ Oracle Database 11g Release 11.2.0.4

§ Oracle Grid Infrastructure 11g Release 11.2.0.4

§ Exadata Storage Server Software 11g Release 11.2.3.2.1

–    Write Through Mode

验证环

OLTPWorkload性能

§ 在下面的用户脚步中反复执行泛用的SQL

WorkloadTransaction的定

OLTPWorkload性能

§ 验证变更了Transaction的比例的三个Workload

–    伴随着Test#的增加,将更新处理的比例

Transaction比例的模式

OLTPWorkload性能

§ 在前页的各个Workload中,验证下面四个设定模式

Parameter定模式

OLTPWorkload性能

検証結果) 全模式测试的吞吐量Transaction Per Sec

OLTPWorkload性能

验证结 全模式测试Response Time

OLTPWorkload性能

验证结 模式测试CPU使用率

Oracle DatabaseData Protection

§ Oracle Recovery Manager(RMAN)

–    VALIDATE评论Data File、Backup File(Image Copy / Backup Piece)的破损检查

–    CHECK LOGICAL句

§ 追加Physical Corruption的检测,检测Logical Corruption

§ 可以与BACKUP / VALIDATE / RECOVER 评论同时检测

§ ANALYZE TABLE <TableName> VALIDATE STRUCTURE CASCADE ;

–    可能检测出表与索引Block之间的不完整

提供定期破损检测的功能

RMAN> Validate Check Logical

RMAN> validate check logical datafile 18 ;

Starting validate at 28-AUG-13

using target database control file instead of recovery catalog

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00018 name=+DATA/orcl/datafile/tt.316.824637849

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

—- —— ————– ———— ————— ———-

18   OK     1              277          320             224873829

  File Name: +DATA/orcl/datafile/tt.316.824637849

  Block Type Blocks Failing Blocks Processed

  ———- ————– —————-

  Data       0              4

  Index      0              1

  Other      0              38

Finished validate at 28-AUG-13

参考出日志

SQL> ANALYZE TABLE VALIDATE STRUCTURE CASCADE ;

–    如果发生了Physical Block Corruption,那么就会发生ORA-1578

–    如上所示,发生ORA-1499的话,表以及索引之间发生不完整的状态。

§ 需要区分到底是在那个块中发生了故障。

–    例:FULL提示,或者使用NO_INDEX提示执行全表搜索

KROWN#68739 参考出日志

Oracle Database
Data Protection

Block Corruption 造成的影响
造成的直接

ü数据损伤造成的业务停止

ü修复时间变长

ü修复工作的人为错误、二次灾害

ü探究原因的时间变长

 

à 需要快速找出故障以及行正确的修复

Oracle MAA的数据保功能一

Oracle Database 11g Release 2

Oracle MAA

主要的修复功能

Oracle ASM / Mirroring

§ 需要Normal(二重化) 以及High Redundancy(三重化)的结构

§ 检测到破损时,参考Mirror数据自动进行修复

–    对于Oracle Client(ORA错误不会返回)

–    Primary中关于发生的Lost Write,不在此次对象中

§ Standby中发生Lost Write时,使用Mirror防止误检测

§ Redo Block破损时,参考Mirror数据

–    Normal/High Redundancy的ASM Diskgroup上配置Redo Log file

§ Doc ID 1274318.1

Oracle Client对块进行自修复

Automatic Block Media Recovery

由于Active Data Guard行穿透性的修复

Automatic Block Media Recovery

§ Standby中检测出块破损是,就会用逆向ABR进行自动修复

–    对象块破损

§ Standby中的Physical Block Corruption

–    用DB_BLOCK_CHECKSUM的功能检测出的项目
–    对Soft Corrupt以及标记完成的块进行访问时,不会有任何操作
(ABR至多在标记之前进行尝试修复。)

§ Standby中发生了Lost Write的Block

–    Primary中发上来Lost Write的Block不在此次对象中
(更新不正确的块时,生成的不正确的redo是无法修复的)

操作的追加信息

Automatic Block Media Recovery

§ Primary Database’s Alert.log

检测块以及用ABR行自修复的参考日志

Oracle Recovery Manager

§ 储存修复对象块的Data File只有在ONLINE状态下才可以执行

§ Lost Write导致的块破损不在此次对象中

–    修复指定块的命令

§ 一般而言,在V$DATABASE_BLOCK_CORRUPTION视图中,指定被表示的block(检测出破损的块)(执行时也需要检测破损)

–    将在V$DATABASE_BLOCK_CORRUPTION视图中表示的视图一起修复的命令。

RECOVER命令来对块进行修复

Oracle Recovery Manager

§ 对块单位的恢复来说,需要可以Restore的正常块

–    正常块的搜索地址的优先顺序如下所示

•          Active Data GuardPhysical Standby Database

•          Flashback Log Recovery行中的Database内)

•          RMAN Image Backup Recovery行中的Database内)

–    前述都是正常的块被Restore,用自动恢复来实现最新化

–    如果块单位中无法修复时,需要用数据文件单位的恢复

才会是可以成为块单位中恢复基准的正常

Oracle Enterprise Manager Cloud Control 12c

§ 根据Database环境状况,可以简单实现最适合的恢复

–    考虑块单位中的恢复是否可能的命令

–    Oracle Enterprise Manager可以简单地执行恢复能

§ 执行例

–    在下面的Database环境中,我们将介绍用Physical Block Corruption以及
EM(Data Recovery Advisor)来修复的顺序

§ 没有Active Data Guard环境

§ 有Flashback Log(但是是过了保存期限的状态)

§ 没有RMAN Image Copy Backup

Data Recovery Advisor中自生成修复命令

Oracle Enterprise Manager Cloud Control 12c

早期掌握Incident Manager的故障

Oracle Enterprise Manager Cloud Control 12c

Data Recovery Advisor

Oracle Enterprise Manager Cloud Control 12c

§ 考虑Database环境的状况(3页之前),
判断为块单位中的恢复是不可能的

Data Recovery Advisor

Oracle Enterprise Manager Cloud Control 12c

Recovery JobAdvisor果脚步

Flashback Technology

§ Flashback Database命令

–    人为错误(删除数据以及不合适的更新处理等)可以迅速恢复

–    因为Primary中发生Lost Write了,在Data Guard中执行Fail-Over之后,
将旧Primary作为新Standby环境来迅速修复的情况

§ Flashback Log

–    执行上述的Flashback Database命令时

–    前章中介绍过的,执行RMAN主导的块单位中的Media Recovery时

Flashback Database (Flashback Log)活用例

Oracle Data Guard

§ Oracle Data GuardのPhysical Standby是完全复制Database

–    将在Primary Database中生成的Redo同样地应用于Standby中

§ Primary中的块更新处理,也适用于redo的块。

à 一定会Fail-OverDatabase

 

§ 特别是在Primary Database中发生Lost Write时有效

–    使用正常数据迅速重新展开业务

–    可以不影响正常业务来调查Lost Write发生原因

§ 原因不明时,请考虑持续使用Primary的H/W的风险

 

Physical Standby DatabaseFail-Over

Oracle Data Guard

§ 从Lost Write检测开始到Fail-Over为止,被执行的事务会失去

–    Primary是Standby中检测出Lost Write也继续运行

à 由于业务事务,发生新变更的状况

à 然也有正确的更,但也混合了一些使用了Lost WriteBlock

–    Standby为了防止数据污染,检出以后的redo适用停止了。

–         à 重要的是如何迅速停止PrimaryFail-Over

  à Release 11.2.0.4以后
追加Data Guard BrokerPrimary Lost Write Action Property
检测Lost Write事可以自动对PrimaryABORT

检测Lost WriteFail-Over需要考的事情1

Oracle Data Guard

§ Fail-Over之后,Data Guard结构崩溃的状态

–    因为旧Primary与新Primary(原Standby)是在不同的道路上前进

à 需要Standby Database重新制成Primary Database

§ 重新制成的方法

检测Lost WriteFail-Over需要考的事情2

Oracles Data Protection
Conclusion

Data Protection

Oracle Database 11g Release 2

Data Protection

3个初始化参数的检测操作以及修复方法的概要

Conclusion
Data Protection

Oracle Database 可以提供保护数据的高可用性的解决方法。

l DB_ULTRA_SAFE Parameter

l Oracle Data Guard

l Oracle Recovery Manager

 Oracle Enterprise Manager

人工的にOracleデータブロックにロジックエラが起こったことをシミュレーションするORA-00600:[13013] [5001]一例

ORACLEデータベース によくあるエラ の解決策

プロのOracle Databaseの復旧サービスを提供
携帯番号: +86 13764045638 メール:service@parnassusdata.com

 

前週で、お客様からOracle Bugによって、テーブルデータブロックロジックエラでORA-00600:[13013], [5001]を引き起こした例をもらった。ここで、よりうまく説明できるように、人工的にそのエラをシミュレーションする発想が生み出した。

基礎的な知識

Oracleにテーブルのデータブロックはブロックへっだ、トランザクションスロット、行ディクショナリー、及び行データなどいろんな構造で組み立てた。行データは行数据(rowdata)いろんなrow piece によって組み立てる。各row pieceのヘッダにflag、locks、cols(cc)三つのマーク位置がある。

そのflagはrow pieceのタイプをマークした。そのflag位置は一つのバイトを占めて、違ったbit位置が違った意味を意味している。以下の通り:

 

 

ROW_CLUSTER_KEY = 0x80;              KDRHFK
ROW_CTABLE_NUMBER = 0x40;            KDRHFC
ROW_HEAD_PIECE = 0x20;               KDRHFH
ROW_DELETED_ROW = 0x10;              KDRHFD
ROW_FIRST_PIECE = 0x08;              KDRHFF
ROW_LAST_PIECE = 0x04;               KDRHFL
ROW_FROM_PREVIOUS = 0x02;            KDRHFP
ROW_CONTINUE_NEXT = 0x01;            KDRHFN

一般的に、もっとも普通なrow pieceは普通スタックテーブル(heap table)に削除されていない。そして行転移/行リンクもない。そのflag位置は以下の通り

普通のrowのflagは

Single Row =
ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE= 0x20 + 0x08 + 0x04= 0x2c

===================================================================================

cluster keyのflagは

Cluster Key =
ROW_CLUSTER_KEY + ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE=
KDRHFL, KDRHFF, KDRHFH, KDRHFK =0x80 + 0x2c =  0xac

BBED> x /rn
rowdata[68]                                 @8166
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    1
mref@8171:    1
hrid@8173:0x01800014.0
nrid@8179:0x01800014.0

col    0[2] @8185: 10 

===================================================================================

Cluster Row =
ROW_CTABLE_NUMBER + ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE =
(KDRHFL, KDRHFF, KDRHFH, KDRHFC) = 0x6c 

BBED> x /rncc
rowdata[0]                                  @8098
----------
flag@8098: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8099: 0x00
cols@8100:   10

col    0[2] @8102: 200
col    1[8] @8105: Jennifer
col    2[6] @8114: Whalen
col    3[7] @8121: JWHALEN
col   4[12] @8129: 515.123.4444
col    5[7] @8142: w....
col    6[7] @8150: AD_ASST
col    7[2] @8158: 

                    col    8[0] @8161: *NULL*
col    9[3] @8162: .

ORA-00600:[13013], [5001]且Arg [f] Code =3 が現れて、row pieceのflag >0xc0と意味している,
つまり、そのrow pieceは同時にkeyとclustered(row is marked as both a Key and Clustered)とマークされた。その検証コードはcheck code 6251である。

flag >= 0xc0 の時にkdrchk: row is marked as both a Key and Clustered Block 12 failed with check code 6251が現れる

 0xac >flag >= 0xa0 の時に kdrchk: row is Key and Not only piece of key Block 12 failed with check code 6255が現れる

 flag = 0x43の時に kdrchk: C and neither of H or F Block 12 failed with check code 6263が現れる

 flag = 0x83 の時に  kdrchk: row is marked both as a Key and being continued Block 12 failed with check code 6254が現れる

Oracleプロセスがデータブロックをアクセスするときに、まずはブロックの合計値はテストする。そして、ブロックにあるCHECKSUM値と比べる。一致していれば、そのブロックに物理的なエラがないと意味している。ブロックを100%正確を確保するために、このテストだけでまだ足りない。それで、Oracleが一部の列ロジックテストを導入して、各ロジックテストは一つのテストコードに該当する (check code)row pieceのflag、cols(cc)状況テストなども含んでいる。

実際に、このロジック検証テストの関数は:kdbchk、kddummy_blkchk、kco_blkchk、kdBlkCheckError、kdrchkなどを含んでいる。

ここで、サビースプロセスがトラブルデータブロックをアクセスした、テストコードはそのflagがflag为0xff(KCHDFLPN)だと検出できた。そのflagはロジック的に衝突したから、テストコードはそのrow pieceにエラがあると認めている。さらに、updateによって、ORA-00600:[13013], [5001]あるクエリORA-600 [qertbFetchByRowID]内部エラを引き起こした。

ここで説明する必要があるのは、いろんな人がdbvツールで、ロジックエラを検出出来ないと認めているが、実際に、dbvもrmanもvalidate structureもbbed-verifyもある程度のロジックエラを検出できる。けど、一番頼りになるのが、db_block_checksum=trueの場合のvalidate structure [online]検証コマンドである。一方、普通のdbvは一つテストしかできないが。しかも、交換的なテストもできない、そして、テーブルとインディクスが不一致のトラブルを解決できる。けどvalidate structure onlineなら、案外できる。


正式シミュレーション

以上はORA-00600:[13013], [5001]内部エラがどうやって引き起こされたかを理解できた。次に人工的にそのエラをシミュレーションするのも難しくなくなる。ここで、bbedツールを使ってください。

次に実験用のtablespace,table,indexを作成する:

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com

/* 実験用テーブルスペースを作成する  */

SQL> create tablespace maclean datafile '/home/oracle/maclean.dbf' size 20M;

Tablespace created.

SQL> create table tv tablespace maclean as select rownum t1,'find me' t2 from
dba_tables where rownumcreate index ind_tv on tv(t1) tablespace users;

Index created.

SQL> update tv set t2='corrption here' where t1=200;
update tv set t2='corrption here' where t1=200
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TV"."T2" (actual: 14, maximum: 7)

SQL> alter table tv modify t2 varchar2(200);

Table altered.

SQL> update tv set t2='corruption here' where t1=200;

1 row updated.

SQL> commit;

Commit complete.

/* 以上はインスタンステーブルを作成した。そのなかt1=200の記録は後で人工的に修正する行である。             */

SQL> select dump(200,16) from dual;

DUMP(200,16)
-----------------
Typ=2 Len=2: c2,3

/* 16進数コードで、t1=200の記録行を容易く探し出せる */ 
SQL> alter system checkpoint;

System altered.

SQL> alter tablespace maclean read only;

Tablespace altered.

SQL> select dbms_rowid.rowid_block_number(rowid) bno ,dbms_rowid.rowid_relative_fno(rowid) fno from tv;

BNO FNO
---------- ----------
12 6

[oracle@rh2 ~]$ cp maclean.dbf maclean.dbf.bak


次にBBEDツールで、目標を探し出し、人工的に修正してください:

[oracle@rh2 ~]$ bbed filename=maclean.dbf mode=edit
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sun Sep 18 22:14:59 2011

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

BBED> set blocksize 8192
BLOCKSIZE 8192

BBED> set block 13
BLOCK# 13

BBED> map /v

File: maclean.dbf (0)
Block: 13 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18

struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
b2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44

struct kdbh, 14 bytes @124
ub1 kdbhflag @124
b1 kdbhntab @125
b2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
b2 kdbhavsp @134
b2 kdbhtosp @136

struct kdbt[1], 4 bytes @138
b2 kdbtoffs @138
b2 kdbtnrow @140

sb2 kdbr[200] @142

ub1 freespace[4725] @542

ub1 rowdata[2921] @5267

ub4 tailchk @8188

BBED> find /x c203

File: maclean.dbf (0)
Block: 13 Offsets: 5271 to 5782 Dba:0x00000000
------------------------------------------------------------------------
c2030f63 6f727275 7074696f 6e206865 72652c00 0202c203 0766696e 64206d65
2c000203 c2026407 66696e64 206d652c 000203c2 02630766 696e6420 6d652c00
0203c202 62076669 6e64206d 652c0002 03c20261 0766696e 64206d65 2c000203
c2026007 66696e64 206d652c 000203c2 025f0766 696e6420 6d652c00 0203c202
5e076669 6e64206d 652c0002 03c2025d 0766696e 64206d65 2c000203 c2025c07
66696e64 206d652c 000203c2 025b0766 696e6420 6d652c00 0203c202 5a076669
6e64206d 652c0002 03c20259 0766696e 64206d65 2c000203 c2025807 66696e64
206d652c 000203c2 02570766 696e6420 6d652c00 0203c202 56076669 6e64206d
652c0002 03c20255 0766696e 64206d65 2c000203 c2025407 66696e64 206d652c
000203c2 02530766 696e6420 6d652c00 0203c202 52076669 6e64206d 652c0002
03c20251 0766696e 64206d65 2c000203 c2025007 66696e64 206d652c 000203c2
024f0766 696e6420 6d652c00 0203c202 4e076669 6e64206d 652c0002 03c2024d
0766696e 64206d65 2c000203 c2024c07 66696e64 206d652c 000203c2 024b0766
696e6420 6d652c00 0203c202 4a076669 6e64206d 652c0002 03c20249 0766696e
64206d65 2c000203 c2024807 66696e64 206d652c 000203c2 02470766 696e6420
6d652c00 0203c202 46076669 6e64206d 652c0002 03c20245 0766696e 64206d65

t1=200の偏差値は5271と探し出した。

では、fbの偏差値は5271 -4 = 5267

BBED> set offset 5267

OFFSET 5267

BBED> d
File: maclean.dbf (0)
Block: 13 Offsets: 5267 to 5778 Dba:0x00000000
------------------------------------------------------------------------
2c020202 c2030f63 6f727275 7074696f 6e206865 72652c00 0202c203 0766696e
64206d65 2c000203 c2026407 66696e64 206d652c 000203c2 02630766 696e6420
6d652c00 0203c202 62076669 6e64206d 652c0002 03c20261 0766696e 64206d65
2c000203 c2026007 66696e64 206d652c 000203c2 025f0766 696e6420 6d652c00
0203c202 5e076669 6e64206d 652c0002 03c2025d 0766696e 64206d65 2c000203
c2025c07 66696e64 206d652c 000203c2 025b0766 696e6420 6d652c00 0203c202
5a076669 6e64206d 652c0002 03c20259 0766696e 64206d65 2c000203 c2025807
66696e64 206d652c 000203c2 02570766 696e6420 6d652c00 0203c202 56076669
6e64206d 652c0002 03c20255 0766696e 64206d65 2c000203 c2025407 66696e64
206d652c 000203c2 02530766 696e6420 6d652c00 0203c202 52076669 6e64206d
652c0002 03c20251 0766696e 64206d65 2c000203 c2025007 66696e64 206d652c
000203c2 024f0766 696e6420 6d652c00 0203c202 4e076669 6e64206d 652c0002
03c2024d 0766696e 64206d65 2c000203 c2024c07 66696e64 206d652c 000203c2
024b0766 696e6420 6d652c00 0203c202 4a076669 6e64206d 652c0002 03c20249
0766696e 64206d65 2c000203 c2024807 66696e64 206d652c 000203c2 02470766
696e6420 6d652c00 0203c202 46076669 6e64206d 652c0002 03c20245 0766696e

/* 指定した行アドレスは5267だど探し出せる。既存するflagはまともな0x2cである  */

BBED> x /rnc

rowdata[0] @5267
----------
flag@5267: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5268: 0x02
cols@5269: 2

col 0[2] @5270: 200
col 1[15] @5273: corruption here

flag を 0xff BBED> modify /x 0xffに修正してください。

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: maclean.dbf (0)
Block: 13 Offsets: 5267 to 5778 Dba:0x00000000
------------------------------------------------------------------------
ff020202 c2030f63 6f727275 7074696f 6e206865 72652c00 0202c203 0766696e
64206d65 2c000203 c2026407 66696e64 206d652c 000203c2 02630766 696e6420
6d652c00 0203c202 62076669 6e64206d 652c0002 03c20261 0766696e 64206d65
2c000203 c2026007 66696e64 206d652c 000203c2 025f0766 696e6420 6d652c00
0203c202 5e076669 6e64206d 652c0002 03c2025d 0766696e 64206d65 2c000203
c2025c07 66696e64 206d652c 000203c2 025b0766 696e6420 6d652c00 0203c202
5a076669 6e64206d 652c0002 03c20259 0766696e 64206d65 2c000203 c2025807
66696e64 206d652c 000203c2 02570766 696e6420 6d652c00 0203c202 56076669
6e64206d 652c0002 03c20255 0766696e 64206d65 2c000203 c2025407 66696e64
206d652c 000203c2 02530766 696e6420 6d652c00 0203c202 52076669 6e64206d
652c0002 03c20251 0766696e 64206d65 2c000203 c2025007 66696e64 206d652c
000203c2 024f0766 696e6420 6d652c00 0203c202 4e076669 6e64206d 652c0002
03c2024d 0766696e 64206d65 2c000203 c2024c07 66696e64 206d652c 000203c2
024b0766 696e6420 6d652c00 0203c202 4a076669 6e64206d 652c0002 03c20249
0766696e 64206d65 2c000203 c2024807 66696e64 206d652c 000203c2 02470766
696e6420 6d652c00 0203c202 46076669 6e64206d 652c0002 03c20245 0766696e

BBED> x /rnc

rowdata[0] @5267
----------
flag@5267: 0xff (KDRHFN, KDRHFP, KDRHFL, KDRHFF, KDRHFD, KDRHFH, KDRHFC, KDRHFK)
lock@5268: 0x02
cols@5269: 0
ckix@5270: 2

BBED> sum apply

Check value for File 0, Block 13:
current = 0x0000, required = 0x0000

私たち使っているbbedのverifyコマンドはデータブロックをテストして、トラブルflagを検出できる。

BBED> verify
DBVERIFY - Verification starting
FILE = maclean.dbf
BLOCK = 12

kdrchk: row is marked as both a Key and Clustered

prow=0x7f5335f05693 flag=0xff
Block Checking: DBA = 25165836, Block Type = KTB-managed data block
data header at 0x7f5335f0427c
kdbchk: bad row tab 0, slot 199
Block 12 failed with check code 6251

DBVERIFY - Verification complete

Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0

dbvツールもこのようなロジックエラを検出できる。

[oracle@rh2 ~]$ dbv file=maclean.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Sun Sep 18 22:27:49 2011

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

DBVERIFY - Verification starting : FILE = maclean.dbf
kdrchk: row is marked as both a Key and Clustered
prow=0x7f9ef25f7693 flag=0xff
Block Checking: DBA = 25165836, Block Type = KTB-managed data block
data header at 0x7f9ef25f627c
kdbchk: bad row tab 0, slot 199
Page 12 failed with check code 6251

DBVERIFY - Verification complete

Total Pages Examined : 2560
Total Pages Processed (Data) : 1
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2548
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 691691 (0.691691)


Sqlplusに戻って、前に修正したデータ行をアクセスする。ORA-600[13013] [5001]エラを引き起こす:


SQL> alter system flush buffer_cache;

System altered.

SQL> update tv set t2='correct here' where t1=200;
update tv set t2='correct here' where t1=200
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [13013], [5001], [52937],
[25165836], [199], [25165836], [3], []

PLAN_TABLE_OUTPUT
---------------------------------------------------------
Plan hash value: 568795662

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |        |     1 |   115 |     2   (0)| 00:00:01 |
|   1 |  UPDATE           | TV     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IND_TV |     1 |   115 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("T1"=200)

SQL> select * from tv where t1=200;
select * from tv where t1=200
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qertbFetchByRowID], [], [], [], [],
[], [], []

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1015724781

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   115 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TV     |     1 |   115 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TV |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("T1"=200)

ここで、トラブル行記録をupdateするときに、予想のとおり、ORA-00600:[13013], [5001]エラが現れた。けどACCESS BY INDEX ROWIDするときに、ORA-00600:[qertbFetchByRowID]エラになった。
解決策
1.バックアップがあれば、blockrecoveryを利用して、オンラインでトラブルブロックを解決できる:
RMAN> blockrecover datafile 6 block 12;
Starting blockrecover at 18-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 18-SEP-11
だが、そのロジックエラは確かにOracle Bugを引き起こしたとしたら、blockrecoverも無効になる。この場合に、二つ目の方法を使ってください。

2. 二つ目はバックアップもないデータベースあるいはリカバリデータブロックが使えなくなった場合に対して利用できる。10231トランザクションを設定し、そのテーブルをctasコピできる。けど、これによって、トラブルがあった行記録をなくなるかもしれない:
SQL> alter session set events ‘10231 trace name context forever, level 10’
SQL> Create table.TABLE_COPY as select * from TABLE;

より多くのkdrchk関数の情報はこちら:


Add check for continued row piece pointing to itself with
corruption description:

"kdrchk: Row piece pointing to itself"

DB_BLOCK_CHECKING = MEDIUM will check for row pieces where the
next rowid (nrid) points to itself (chained row points to itself).
It produces error ORA-600 [kddummy_blkchk] or ORA-600 [kdBlkCheckError]
with check code [6266] (3rd ORA-600 argument).

DBVERIFY reports the same corruption description if the block is corrupt on disk.

RMAN when run with the CHECK LOGICAL option reports it as
     corruption_type=CORRUPT/LOGICAL in v$database_block_corruption.

"ANALYZE TABLE  VALIDATE STRUCTURE" produces error ORA-1498 and trace file
shows the same corruption description.

With this fix in place DBMS_REPAIR can be used to identify and mark the affected
block as Soft Corrupt producing error ORA-1578 and it can be skipped it for DML's
using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS.

 [CM][SG][event 1][domain Q423][mem 0] Joining shared group
  kdrchk: column length 0 but not null
            prow=0x2a97f4d9d6 flag=0x2c column=57
  Block Checking: DBA = 29635651, Block Type = KTB-managed data block
  data header at 0x2a97f4be7c
  kdbchk: bad row tab 0, slot 2
  data_block_dump,data header at 0x2a97d113d8
  data_block_dump,data header at 0x2a97d113d8

 kdrchk: found invalid symbol reference 48
  reference to delete symbol
  valid symbol range [0,78)
  Block Checking: DBA = 411055291, Block Type = KTB-managed data block
  data header at 0x68a3f4
  kdbchk: bad row tab 0, slot 4
  Page 13499 failed with check code 6265

kdrchk: C and neither of H or F
          prow=0x4282803ae flag=0x41
Block Checking: DBA = 322963095, Block Type = KTB-managed data block
data header at 0x42828007c

kdrchk: column length 0 but not null
          prow=0x10021035e flag=0x2c column=40
Block Checking: DBA = 25189259, Block Type = KTB-managed data block
data header at 0x10020fe7c
kdbchk: bad row tab 0, slot 0
Page 23435 failed with check code 6264
kdrchk: column length 0 but not null
          prow=0x1002122e5 flag=0x2c column=40
Block Checking: DBA = 25189260, Block Type = KTB-managed data block

kdrchk:  row is marked as both a Key and Clustered
prow=0xd2bfa981 flag=0xff
File#67, Block#74754

kdbchk: bad row tab 0, slot 0
kdrchk:  no columns, but has one of P or N
          prow=0x934fbffa flag=0x31

DIAGNOSTIC ANALYSIS:
====================
A look at the block dump in the analyze trace file revealed two very
suspicious looking rows:

tab 0, row 0, @0x1ede
tl: 2 fb: --HD---N lb: 0x0
tab 0, row 1, @0x1edc
tl: 2 fb: --HD---N lb: 0x0

The flag bytes in these rows look incorrect.


V$SQLCOMMAND SQL opcodes and names


SQL> select command_type,command_name from V$SQLCOMMAND;

COMMAND_TYPE COMMAND_NAME
------------ ----------------------------------------------------------
           0
           1 CREATE TABLE
           2 INSERT
           3 SELECT
           4 CREATE CLUSTER
           5 ALTER CLUSTER
           6 UPDATE
           7 DELETE
           8 DROP CLUSTER
           9 CREATE INDEX
          10 DROP INDEX
          11 ALTER INDEX
          12 DROP TABLE
          13 CREATE SEQUENCE
          14 ALTER SEQUENCE
          15 ALTER TABLE
          16 DROP SEQUENCE
          17 GRANT OBJECT
          18 REVOKE OBJECT
          19 CREATE SYNONYM
          20 DROP SYNONYM
          21 CREATE VIEW
          22 DROP VIEW
          23 VALIDATE INDEX
          24 CREATE PROCEDURE
          25 ALTER PROCEDURE
          26 LOCK TABLE
          27 NO-OP
          28 RENAME
          29 COMMENT
          30 AUDIT OBJECT
          31 NOAUDIT OBJECT
          32 CREATE DATABASE LINK
          33 DROP DATABASE LINK
          34 CREATE DATABASE
          35 ALTER DATABASE
          36 CREATE ROLLBACK SEG
          37 ALTER ROLLBACK SEG
          38 DROP ROLLBACK SEG
          39 CREATE TABLESPACE
          40 ALTER TABLESPACE
          41 DROP TABLESPACE
          42 ALTER SESSION
          43 ALTER USER
          44 COMMIT
          45 ROLLBACK
          46 SAVEPOINT
          47 PL/SQL EXECUTE
          48 SET TRANSACTION
          49 ALTER SYSTEM
          50 EXPLAIN
          51 CREATE USER
          52 CREATE ROLE
          53 DROP USER
          54 DROP ROLE
          55 SET ROLE
          56 CREATE SCHEMA
          57 CREATE CONTROL FILE
          58 ALTER TRACING
          59 CREATE TRIGGER
          60 ALTER TRIGGER
          61 DROP TRIGGER
          62 ANALYZE TABLE
          63 ANALYZE INDEX
          64 ANALYZE CLUSTER
          65 CREATE PROFILE
          66 DROP PROFILE
          67 ALTER PROFILE
          68 DROP PROCEDURE
          70 ALTER RESOURCE COST
          71 CREATE MATERIALIZED VIEW LOG
          72 ALTER MATERIALIZED VIEW LOG
          73 DROP MATERIALIZED VIEW  LOG
          74 CREATE MATERIALIZED VIEW
          75 ALTER MATERIALIZED VIEW
          76 DROP MATERIALIZED VIEW
          77 CREATE TYPE
          78 DROP TYPE
          79 ALTER ROLE
          80 ALTER TYPE
          81 CREATE TYPE BODY
          82 ALTER TYPE BODY
          83 DROP TYPE BODY
          84 DROP LIBRARY
          85 TRUNCATE TABLE
          86 TRUNCATE CLUSTER
          87 CREATE BITMAPFILE
          88 ALTER VIEW
          89 DROP BITMAPFILE
          90 SET CONSTRAINTS
          91 CREATE FUNCTION
          92 ALTER FUNCTION
          93 DROP FUNCTION
          94 CREATE PACKAGE
          95 ALTER PACKAGE
          96 DROP PACKAGE
          97 CREATE PACKAGE BODY
          98 ALTER PACKAGE BODY
          99 DROP PACKAGE BODY
         157 CREATE DIRECTORY
         158 DROP DIRECTORY
         159 CREATE LIBRARY
         160 CREATE JAVA
         161 ALTER JAVA
         162 DROP JAVA
         163 CREATE OPERATOR
         164 CREATE INDEXTYPE
         165 DROP INDEXTYPE
         166 ALTER INDEXTYPE
         167 DROP OPERATOR
         168 ASSOCIATE STATISTICS
         169 DISASSOCIATE STATISTICS
         170 CALL METHOD
         171 CREATE SUMMARY
         172 ALTER SUMMARY
         173 DROP SUMMARY
         174 CREATE DIMENSION
         175 ALTER DIMENSION
         176 DROP DIMENSION
         177 CREATE CONTEXT
         178 DROP CONTEXT
         179 ALTER OUTLINE
         180 CREATE OUTLINE
         181 DROP OUTLINE
         182 UPDATE INDEXES
         183 ALTER OPERATOR
         184 Do not use 184
         185 Do not use 185
         186 Do not use 186
         187 CREATE SPFILE
         188 CREATE PFILE
         189 UPSERT
         190 CHANGE PASSWORD
         191 UPDATE JOIN INDEX
         192 ALTER SYNONYM
         193 ALTER DISK GROUP
         194 CREATE DISK GROUP
         195 DROP DISK GROUP
         196 ALTER LIBRARY
         197 PURGE USER RECYCLEBIN
         198 PURGE DBA RECYCLEBIN
         199 PURGE TABLESPACE
         200 PURGE TABLE
         201 PURGE INDEX
         202 UNDROP OBJECT
         203 DROP DATABASE
         204 FLASHBACK DATABASE
         205 FLASHBACK TABLE
         206 CREATE RESTORE POINT
         207 DROP RESTORE POINT
         209 DECLARE REWRITE EQUIVALENCE
         210 ALTER REWRITE EQUIVALENCE
         211 DROP REWRITE EQUIVALENCE
         212 CREATE EDITION
         213 ALTER EDITION
         214 DROP EDITION
         215 DROP ASSEMBLY
         216 CREATE ASSEMBLY
         217 ALTER ASSEMBLY
         218 CREATE FLASHBACK ARCHIVE
         219 ALTER FLASHBACK ARCHIVE
         220 DROP FLASHBACK ARCHIVE
         222 CREATE SCHEMA SYNONYM
         224 DROP SCHEMA SYNONYM
         225 ALTER DATABASE LINK
         226 CREATE PLUGGABLE DATABASE
         227 ALTER PLUGGABLE DATABASE
         228 DROP PLUGGABLE DATABASE
         229 CREATE AUDIT POLICY
         230 ALTER AUDIT POLICY
         231 DROP AUDIT POLICY
         238 ADMINISTER KEY MANAGEMENT
         239 CREATE MATERIALIZED ZONEMAP
         240 ALTER MATERIALIZED ZONEMAP
         241 DROP MATERIALIZED ZONEMAP

Introduction to ORA-600/ORA-7445 Internal Error Analysis

APPLIES TO:

Oracle Database – Enterprise Edition – Version 8.1.7.4 and later
Information in this document applies to any platform.
***Checked for relevance on 30-Sep-2014***

PURPOSE

The purpose of this troubleshooting article is to provide an insight into key areas of internal
600/7445 trace files to assist in deriving to a known bug or to highlight what might be needed
by ORACLE Support to progress an issue further.  Whilst the article can be used to some
extent on 11g it is primarily written for versions <V10204 due to :-

a) The worked example is based on a bug fixed in V10204 and higher releases
b) The alert/trace file structure in 11g has changed

The article will make reference to other notes so that the traces for 11g can also
be proactively reviewed if wished.

The nature of Internal errors means no one approach can guarantee getting to a solution but again
the intention is to provide an indication on notes that support make available to customers and
a simple workflow process that assists for a vast majority of SRs. A simple example is illustrated
to generate a known bug that is harmless to the database when generated, that said it
should ONLY be used on a TEST database as it’s not encouraged to willingly raise such errors in
‘LIVE’ environments. This example will then be used to highlight various sections and how each
section can be useful for general 600/7445 analysis and again to provide the best chance of
identifying a known bugfix should one be available.

The article also makes links to a number of others for completeness including Note:232963.1
should a testcase be required as is the ideal case for all internal errors but understandably
is not always possible dependent on the problem area.  Tests were made on SUN but the
bug is not platform specific, it may however show slightly different messages within the alert
log and for Windows based platforms some tools/notes might not be applicable.  However this
does not distract from the primary aim of how a trace file can be analyzed.

An Internal Error whether ORA-00600 or ORA-07445 can fall into many categories :-

Issue reproducible and testcase can be provided
Issue reproducible but no simple testcase perhaps due to code being oracle SQL, 3rd party SQL
Issue not reproducible but persistent and formulates to some pattern e.g. once a day
Issue not reproducible and random pattern to occurrences

By definition if an issue is not reproducible at will in a customer environment a testcase may
be very difficult to obtain but should always be attempted where possible.

These are a simplified subset, depending on root cause there can be many 600/7445 errors.
Typically the argument(s) of the error when unique should mean each are different problems
but if occurring on same timestamp or one always soon follows another there might be some
relationship and this will be for support to determine.

TROUBLESHOOTING STEPS

Worked Example
——————
A real life example will follow and a working methodology will then be provided. Conclusions
will then be made at the end of the analysis and some standard questions will be commented
on that should always be appropriate to analysis of Internal errors and perhaps any SR raised
into support.  The bug number identified will be reported in a later section so as to show the
workflow/analysis methodology used without knowing the solution in advance.  This article
is only suitable for <V10204 databases in terms of the testcase as the bug it relates to
if fixed in the V10204PSR and higher RDBMS versions.  The article is still appropriate as an
introduction to analysis for 11g RDBMS and we will mention the main differences for when an internal error is seen in 11g.  Due to the nature of Internal errors it is possible that the error
is reported differently between versions/PSRs/platforms making analysis more complex.  The
example and trace results for this article came from V10201/64bitSUN but the same principles
apply to all.

sqlplus scott/tiger
drop table a;
drop table b;

create table A(col11 number, col12 number);
create table B(col21 number, col22 number);

insert into a values (-3,-7);
insert into a values (null,-1);
insert into b values ( -7,-3);

update a sET col11 =
(select avg(b.col22) keep (dense_rank first order by (col22)) FROM b where
b.col21= a.col12);

The UPDATE will fail at session level with ORA-03113 error. A trace file will also be written
to the UDUMP destination of the database and will now be highlighted, where appropriate
if a section can be found using keyword searches in the trace it will be mentioned.

Before any analysis of UDUMP/BDUMP traces take place there should be an understanding of
how the error(s) are reported in the alert log.

ALERT LOG
—————
As this is a worked example we know what to expect in advance but the alert and an understanding
of what actions take place around an internal error can be fundamental to resolution. It is
certainly possible for internal errors to be a consequence of a problem rather than the cause.

Errors in file xxxx/xxxx_ora_24779.trc:
ORA-07445: exception encountered: core dump [_memcpy()+592] [SIGSEGV]
[Address not mapped to object] [0xFFFFFFFF7B180000] [] []
ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds],
[0xFFFFFFFF7B179E98], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [_memcpy()+592] [SIGSEGV]
[Address not mapped to object] [0xFFFFFFFF7B180000] [] []
ORA-00600: internal error code, arguments: [kghGetHpSz1], [0xFFFFFFFF7B179EA8]

The errors continue in the alert and as we can see >1 error is raised but all are reported
to be within the same tracefile.
There will be cases where >1 error and >1 trace exist but this is
beyond the scope of this ‘Introduction’.

Various sections of the trace are now reported based on this example, not all 600/7445 traces
will allow for each section to be reviewed. Again this is a very simple example and often a
combination of traces will need to be understood which is beyond the scope of this article.

Section 1 : Trace header information
-------------------------------------

xxxx/xxxx_ora_24779.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = xxxxxx
System name: SunOS
Node name: xxxxxx
Release: 5.8
Version: Generic_117350-38
Machine: sun4u
Instance name: xxxxx
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 24779, image: oracle@xxxxx (TNS V1-V3)

This section gives general information on the machine/instance and version of RDBMS where the internal error has been seen, whilst important its certainly does not really contain much to narrow bug searches as this info is customer/machine specific where as a bug itself should really hold information generic to all customers.

 

Section 2 : The 600 or 7445 internal error
------------------------------------------

*** SESSION ID:(143.5) 2006-07-18 10:45:03.004
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0xffffffff7b280000,
PC: [0xffffffff7b700b58, memcpy()+1196]

*** 2006-07-18 10:45:03.008
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [memcpy()+1196] [SIGSEGV] [Address not mapped to object]
[0xFFFFFFFF7B280000] [] []

This article is not written to show the differences between ORA-00600/ORA-07445 errors in any detail.

The basic difference between these two errors, is that
an ORA-600 is a trapped error condition in the Oracle code that should not occur, whereasan ORA-7445 is an untrapped
error condition detected by the operating system.

 

Section 3 : Current SQL statement
---------------------------------

Current SQL statement for this session:
update a sET col11 =
(select avg(b.col22) keep (dense_rank first order by (col22)) FROM b where b.col21= a.col12);

Not all internal issues will show a current SQL statement and there is no simple reason why this is the case. If this does happen it is recommended to try and use all other sections to narrow the search space down. When present in an internal (600/7445) trace file it should always be locatable using a search in the trace file of 'Current SQL' and should be the first hit found.

In addition there maybe a 'PLSQL Call Stack' that pinpoints the schema.object and line number for an internal issue.

 

Section 4 : Call Stack Trace
-----------------------------

The call stack within the trace file is seen
as follows :-

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+744         CALL     ksedst()             000000840 ?
FFFFFFFF7FFE7D5C ?
000000000 ?
FFFFFFFF7FFE4850 ?
FFFFFFFF7FFE35B8 ?
FFFFFFFF7FFE3FB8 ?
ssexhd()+1000        CALL     ksedmp()             000106000 ? 106324A04 ?
106324000 ? 000106324 ?
000106000 ? 106324A04 ?
sigacthandler()+44   PTR_CALL 0000000000000000     000380007 ?
FFFFFFFF7FFEB9A0 ?
000000067 ? 000380000 ?
00000000B ? 106324A00 ?
_memcpy()+592        PTR_CALL 0000000000000000     00000000B ?
FFFFFFFF7FFEB9A0 ?
FFFFFFFF7FFEB6C0 ?
FFFFFFFFFFFFFFFF ?
000000004 ? 000000000 ?
ksxb1bqb()+36        FRM_LESS __1cY__nis_writeCol  FFFFFFFF7B2F0000 ?
dStartFile6FpcpnNdi  FFFFFFFF7B35EE90 ?
rectory_obj__i_()+3  FFFFFFFFFFFE6DA0 ?
75                   FFFFFFFFFFFFFFFF ?
000000004 ? 000000000 ?
kxhrPack()+1088      CALL     ksxb1bqb()           FFFFFFFF7B0F7220 ?
FFFFFFFF7B354BBF ?
FFFFFFFFFFFF1070 ?
FFFFFFFF7B2E5D60 ?
FFFFFFFF7B0F7220 ?
000001FF0 ?
qescaInsert()+280    CALL     kxhrPack()           000000080 ?
FFFFFFFF7B34A5E8 ?

The stack function is the first column on each line, and so reads:

ksedmp ssexhd sigacthandler memcpy ksxb1bqb  .....

For clarity the full stack trace is summarised to :-

Function List (to Full stack) (to Summary stack)
ksedmp ssexhd sigacthandler memcpy ksxb1bqb kxhrPack
qescaInsert subsr3 evaopn2 upderh upduaw kdusru
kauupd updrow qerupRowProcedure qerupFetch updaul updThreePhaseExe
updexe opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv
sou2o opimai_real main start

There is no automated tool for customers to get into this
form.  Note:211909.1 reports a further example
of ORA-7445 stack analysis.

The call stack can be very powerful to narrow a problem down to known issues
but if used incorrectly will generate many hits totally unrelated to the
problem being encountered.

As the functions and their purpose are ORACLE proprietry this article can only give pointers towards good practice and these include :-

a) Ignore function names that are before the 600/7445 error so for this worked example searches on 'ksedmp','ssexhd' or 'sigacthandler' will not benefit.

The top most routines are for error handling, so this is
why the failing function 'memcpy()' is not at the top of
the stack, and why the top most functions can be ignored.

b) Ignore calls towards the end of the call stack

c) Try a number of different searches based on the failing function from the 600/7445 and 4-5 functions after

In this case a useful 'My ORACLE Support' (MOS) search would be : memcpy ksxb1bqb kxhrPack qescaInsert subsr3

 

Section 5 : Session Information
-------------------------------

SO: 392b5a188, type: 4, owner: 392a5a5b8, flag: INIT/-/-/0x00
(session) sid: 143 trans: 3912d2f78, creator: 392a5a5b8, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-000F-0000000F, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, sql: 38d8c05b0, psql: 38d8c0b20, user: 173/SCOTT
O/S info: user: xxxxxx, term: pts/21, ospid: 24482, machine: xxxxxx
program: sqlplus@xxxxxx (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
last wait for 'SQL*Net message from client' blocking sess=0x0 seq=282 wait_time=830 seconds since wait started=1

A 600/7445 trace should always have a 'Call stack Trace' whether it contains function names or not, after this follows 'PROCESS STATE' information and in this we can search on keyword 'session'.

The first hit should take us to the section of the trace file that shows where the error came from and the session/user affected (above they have been blanked to xxxxxx). In many cases we will see user, terminal and machine information that can often be useful for any issue that needs to be reproduced for further investigation. On many occasions an internal error will be reported in alert logs and the DBA will not necessarily have been informed by any users of the database/application that they encountered a problem.

This section can often also show 3rd party sessions e.g. TOAD/PLSQL or even where the client is another ORACLE product e.g. FORMS/APPS and even if just a client process e.g. EXP/RMAN. This can be another powerful method of narrowing a problem down, in the case of a problem coming from 3rd party software it is important to determine wherever possible if an issue can be reproduced in SQLPLUS.

 

Section 6 : Explain Plan Information
-------------------------------------

============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------+-----------------------------------+
| 0 | UPDATE STATEMENT | | | | 3 | |
| 1 | UPDATE | A | | | | |
| 2 | TABLE ACCESS FULL | A | 2 | 52 | 3 | 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 26 | | |
| 4 | TABLE ACCESS FULL | B | 1 | 26 | 3 | 00:00:01 |
---------------------------------------+-----------------------------------+

This section can be found in the 600/7445 trace (when present) using the case
sensitive search 'Explain plan' and will be the first hit unless the SQL for
example contained the same string.

In this section the 'Plan Table' for the failing SQL will be reported and
especially for CBO related internal errors might give a pointer to finding
a workaround. For this worked example nothing really can be used but we might
for example see the plan using a 'hash join' and if so a possible w/a to
try would be hash_join_enabled=false. Caution should be used here, permanent
workarounds should only be set in place if matching to a known bug and the
implications are understood.

 

Section 7 : Current Cursor Information
--------------------------------------

Current cursor: 4, pgadep: 0
Open cursors(pls, sys, hwm, max): 11(3, 7, 50, 50)
NULL 6 SYNTAX 0 PARSE 0 BOUND 5 FETCH 0 ROW 0
Cached frame pages(total, free):
4k(61, 58), 8k(10, 10), 16k(1, 1), 32k(0, 0)
pgactx: 38d2c9e98 ctxcbk: 38d2c9b30 ctxqbc: 0 ctxrws: 38e751110

This can be found using the case sensitive search in the trace file for 'Current cursor:' [do not include the quotes], this section will either tie up with the current SQL statement reported in the 600/7445 or when there is no SQL statement reported it 'might' assist in further analysis.

Once the current cursor is known the information on this can be found using the search 'Cursor#X' where X is the value shown e.g in this case 4.

However this is >=10.2 specific
and so it should be noted that prior to 10.2
use the search 'Cursor X '.

Cursor#4(ffffffff7b231828) state=BOUND curiob=ffffffff7b2456f8
curflg=4c fl2=0 par=0 ses=392b5a188
sqltxt(38d8c05b0)=update a sET col11 =
(select avg(b.col22) keep (dense_rank first order by (col22)) FROM b where b.col21= a.col12)
hash=e2ef469857c74a273b881c20276493b5
parent=38eabc4b8 maxchild=01 plk=38f664f50 ppn=nll

If a SQL statement is using BIND variables it is the cursor information that often proves useful at isolating the values used in the SQL which again can be the key to reproducing a problem if data specific.

 

ORA-00600/ORA-07445 Analysis : General Guidelines
--------------------------------------------------

Now that the sections have been explained we need to ensure we use as systematic a procedure as possible to determine if a known issue/bug exists. It should be clear that a 600/7445 trace contains far more information but if the above sections together with what follows cannot conclude to a known issue it is recommended that a SR is raised to support.

Three useful documents types can assist here :-

OERI notes
----------
An 'OERI' article is a document provided where possible to customers that provides a brief meaning of the error/impact and known bugs relating to the 600/7445. The quickest way to find this information from MOS is to
search as :

a) 7445 <1st argument> oeri
or
b) 600 <1st argument> oeri

So if the internal error was 600[12333] the search is:600 12333 oeri

So for this article and worked example the search would be : 7445 memcpy oeri

Equally the 600/7445 tool as per Note:153788.1 can be used and if the trace file is submitted into this tool an OERI note will be reported where published.  If the testcase is attempted on a WINDOWS platform the error reported in the alert and trace may not be the same and the tool might not return any hits.  Finding a suitable example for all platforms is beyond the scope of this document.

So for the latter search we should find Note:310531.1, it should be noted that not all bugs associated with an error will be tagged but ideally they would be.

It should also be clear from the notes seen in this type of search that ORA-600[x]/7445[y] does not mean only one bug can attribute to the Internal error.

BUG.8 notes
-----------
If the OERI or if a search gets a hit to a bug there maybe a link to a note where the MOS Noteid is <BUGNO>.8

For this worked example Note:5162852.8 exists, also to illustrate their purpose we can take for example Note:4451759.8

These articles are automatically generated when a bug becomes fixed and will typically include :-

--Product affected
--Range of releases the bug/error is believe appropriate to
--Known affected version(s)
--Fixed version

Together with the 'symptoms' of the bug and where possible workarounds. This information is also in the bug itself but the note provides this into a much clearer form.

Patch Set : List of Bugfixes by Problem Type Articles
-----------------------------------------------------

When a PSR is released ORACLE ensures there is an article that summarises all the bugfixes made into that PSR, this articles allows a quick search to customers to see if there is an ORA-600/ORA-7445 error of the same type in the PSR. As mentioned earlier in the OERI section just because there is a fix for the same type, it does not guarantee that the problem is the same.

For example if one of these PSR notes mentions ORA-00600[729] it means that the fix relates to one specifc way this error can be encountered, it does not stop 600[729] occurring for other reasons and even with the latest PSR applied to a given RDBMS release the internal error could occur due to a new unknown bug.

These documents can be found in MOS using a search with keywords like :-

<version> patch set bug fixes problem type

So if we are looking to see what fixes are made into the V92070 PSR we would use
the six keywords : 9.2.0.7 patch set bug fixes problem type

MOS will produce many hits but then the internet browsers search function can be used to search on the string '9.2.0.7' to quickly isolate the note which in this case is Note:308894.1

It is recommended to search always on the highest PSR available for the version you are running on since the note will then also contain links to the previous PSRs.

ORA-00600/ORA-07445 Analysis : Keyword Searches
-----------------------------------------------

This is the stage of the article where we need to use the research gathered from trace files and known MOS notes to see if we can use the MOS knowledge base to determine if the internal error is a known bug. It was commented that Section4 of a trace file can be very powerful here but again it is impossible to provide one unique way to search the knowledge bases. Instead we will use the worked example and will give some recommendations for 'bad' and 'good' search practices.

Bad Searches Practices
----------------------

a) Using first few calls of stack trace e.g ksedmp ssexhd sigacthandler memcpy
b) Use of MOS 'Advanced Search' and using the 'using any of the words' option

The reason this is a poor search method is that it is likely too many hits will be returned especially if keywords like 600/7445 are used. The default search functionality is an AND base search where all keywords must be present and this provides for better searches and fewer unrelated hits.

c) Using all of the stack trace. As explained above a default MOS search will find hits that contain all the keywords and using the whole stack allows a real risk of getting zero hits returned.

Good Searches Practices
------------------------

It should be noted that more than one search might actually be needed to get a feeling for what bugs/notes are relevant to the customers issue. The scope of what can be searched will depend on if a problem is reproducible.

a) Isolating a good section of the 'Call Stack' from Section4
b) If SQL is known then look at SQL to see if there is something special on the SQL e.g for this worked example we know dense_rank is being used and this is not common SQL
c) If a problem is reproducible and an explain plan is available look at the plan to see if there is something that can be changed at session level. An example was reported in Section6 and if a parameter is seen to make a query work this parameter can then become a very useful keyword for determining if a known bug exists.
d) Using the RDBMS version as keyword can sometimes show if any bugs exist that contain a oneoff fix for the same internal error argument but caution is needed as it is not a guarantee that the bug will cover the same cause.

With this information in mind the two searches :-

memcpy ksxb1bqb kxhrPack qescaInsert subsr3
memcpy dense_rank

both get very few bug hits and BUG:5162852 is quickly identified.

Worked Example for 11g
———————————

The general principles of internal 600/7445 error analysis holds
true for 11g but there are some differences in how traces get reported.

To illustrate Note.808071.1 provides an excellent testcase to reproduce
the error ORA-00600[16613]. As stated already if there is a desire to
reproduce this error so that the alert/trace can be looked at in a proactive
manner then it should ONLY be used on a TEST database.

The example will not be reported in the same detail but instead we can use
it to highlight the key differences.

After the error has been reproduced we need to look at the alert log which
can be located via :-

show parameter dump_dest

background_dump_dest string c:\app\diag\rdbms\orcl\orcl\trace
core_dump_dest               string c:\app\diag\rdbms\orcl\orcl\cdump
user_dump_dest               string c:\app\diag\rdbms\orcl\orcl\trace

It is the background and user destinations for the ‘trace’ directory that we will
need to look.

Tue Nov 15 09:33:59 2011
Errors in file c:\app\diag\rdbms\orcl\orcl\trace\orcl_ora_3896.trc (incident=74841):
ORA-00600: internal error code, arguments: [16613], [0x37D727F8], [2], [], [], [], [], []
Incident details in: c:\app\diag\rdbms\orcl\orcl\incident\incdir_74841\orcl_ora_3896_i74841.trc

For more details on how critical/internal errors are reported within 11g Note.443536.1
can be used. For further analysis of the ORA-00600 we are interested in the ‘Incident Trace’
and the full path to this file is still provided by the alert log. Such traces can also
be located via ‘ADRCI’ but is beyond the scope of this article.

If we look at the appropriate incident trace file we will see very similar traces to
the 10.2 example and the same analytical approach to these traces can be followed.

To provide 11g files to support it is vital to provide the appropriate incident traces
and Note.443529.1/Note.411.1 can be reviewed but again is outside of the scope
of how to try and diagnose the internal error itself.

ORA-00600/ORA-07445 Analysis : Key Questions
——————————————————————-
If trying to research an Internal error before raising a SR to ORACLE Support the following
questions will help avoid further questions from analysts.  These questions are asked in the
MOS template when raising a SR and if answered as clearly as possible this can greatly assist
in getting to a solution.

Is the error reproducible?

If an issue is reproducible where possible it should also be tested from SQLPLUS
(ref Section 5).  It is possible that a known bug/new bug might only occur on JDBC
based clients for example and time needs to be spent understanding the minimum
configuration needed to reproduce a problem.

If an issue is indeed reproducible in a controlled manner a testcase for the majority
of issues should be possible and if a SR or pre-analysis is not matched to a known bug
very rarely can a problem be fixed from a trace file alone.  For this reason please be
aware of Note:232963.1

Has this worked before?

If functionality that was working has just stopped it would tend to suggest either some
issue of data within one or more objects or some more significant change.  If a 600/7445
is seen within Development particularly on higher PSRs/releases it could well be that
you have found a new bug.  Again depending on reproducibility a testcase ideally would
be required.

Has any new change been made recently e.g new code/new PSR or DB version/higher DB load?

Rarely do 600/7445 errors just start to occur due to a database being open for a certain
amount of time.  Even a small change of init/spfile parameters can influence how the database operates
so if any change is known it is better to mention asap no matter how small.

What is frequency of the error, when did it start?

On many occasions a 600/7445 will be seen as a oneoff and not to be seen again.  If pre-analysis
cannot be matched to a known issue and if in any doubt about an error a SR should always
be raised.  It is however necessary to be aware that if the error is a oneoff or sporadic there is a
tradeoff between impact of error and further analysis, as covered below.

On other occasions we might see the error on the hour or some consistent period of time, in
these cases root cause might be some job that runs or linked to an ORACLE process that
gets invoked at certain times.  Finding some pattern can sometimes be vital to solving an issue
and knowing when an issue truly started is very important to analysis.

What is the known visible impact?

Of course any 600/7445 [or any issue for all product support] should be raised as a SR to
ORACLE if there is any concern.  Sometimes an Internal errors impact is very clear in that
we might see an error in a controlled manner only when a session logsoff the database or the
database closes down.  It is equally possible that a database may seem to be running stable
with no users reporting problems and only by proactively looking at the alert can errors be seen.

In all cases if a SR is raised to Support we will need to try and assess the impact of a given
error and to make a confirmation asap if linked to something serious within the database eg corruption.

In many cases of analysis there soon becomes a tradeoff between seeing the error in the alert
and the further diagnostics needed to try and get to the root cause.  For this reason if an error
is a oneoff or very infrequent and determined not to visibly affect users a decision may need to
be made on how far to go especially if running on a desupported release or not on the latest PSR.

The above questions relate to Support asking this information from the customer, one of the key questions asked by customers to Support is :-

‘Why am I being asked to go to the latest PSR when a bug has not been identified?’

The primary answers for this are twofold :-

a) Note:209768.1 explains the backport policy for new bugs. So if Support could not match analysis to a known issue, a new bug can be filed. Sometimes Development can release ‘diagnostics patches’ to better analyze new issue but it’s recommended to use it on latest patchset. Also if on older PSRs it is far more likely for the customer to be running with >1 ‘oneoff’ fix placing the customer into a configuration that is probably not seen with many other customers.

b) Perhaps more importantly each PSR contains many fixes and can solve many issues without
Support being able to determine why.  For this reason the latest PSR is seen as a statistically faster
option to solve a problem when an error cannot be matched to a known bug.

Of course if considering the latest PSR appropriate backups and testing need to be made in advance.

Summary and Conclusions
—————————

The article can be summarised into key sections :-

1. Most importantly always raise a SR if anything is unclear
2. Consider the ORA600/7445 tool : Note:153788.1
3. Make use of the key articles surrounding internal errors ie OERI notes and BUGTAGS
4. Understand basic trace information to be able to use MOS better for ‘efficient’ searching
5. If a SR is to be be raised provide as much information as possible based on the MOS
template questions as these will often be vital to solving the SR.

In conclusion 600/7445 errors are not hopefully a common event to be seen by DBA’s and
analysis of these errors is not something that would be expected by themselves.  However it is often of
interest to many customers as to what is looked at within support and how we make a formal analysis.
Therefore the articles purpose is to highlight some of these processes with the hope that some
self analysis can be made and to assist customer/support interaction on these issues.

ORACLE SQL优化器HINT介绍

在适当的时候使用提示HINT

  • 为以下项指定提示HINT:
  • 优化程序模式
    • 查询转换
    • 访问路径
    • 联接顺序
    • 联接方法

 

优化程序提示HINT:概览

优化程序提示HINT:

  • 影响优化程序的决定
  • 示例:

SELECT /*+ INDEX(e empfirstname_idx) skewed col */ *

FROM employees e

WHERE first_name=’David’

  • 不到万不得已,不要使用提示HINT
  • 使用提示HINT时,同时添加有关该提示HINT的注释是一个好习惯。

 

通过提示HINT,您可以左右优化程序所做的决定。提示HINT提供了一种机制,用以指示优化程序基于特定标准选择特定查询执行计划。

例如,您可能知道某个索引对于特定查询更具可选性。基于此信息,您可能能够选择一个比优化程序推荐的计划更高效的执行计划。在这种情况下,应使用提示HINT强制优化程序使用最佳执行计划。幻灯片示例展示了这种情况,在示例中强制优化程序使用 EMPFIRSTNAME_IDX 索引检索数据。正如您所看到的,您可以在 SQL 语句中使用注释将指令传递给优化程序。

加号 (+) 会使系统将注释解释为提示HINT列表。加号必须紧跟在注释分隔符之后,不留空格。

应少用提示HINT,仅在收集了相关表的统计信息,并使用 EXPLAIN PLAN 语句评估了没有提示HINT的优化程序计划后使用。在后续版本中,变化的数据库条件以及查询性能增强对代码中的提示HINT如何影响性能产生了重要影响。

另外,使用提示HINT还涉及必须进行管理、检查和控制的额外代码。

 

提示HINT类型

单表提示HINT 指定用于一个表或一个视图
多表提示HINT 指定用于多个表或视图
查询块提示HINT 作用于单个查询块
语句提示HINT 应用于整个 SQL 语句

 

 

单表:单表提示HINT指定用于一个表或一个视图。INDEX 和 USE_NL 是单表提示HINT的示例。

多表:除了指定用于一个或多个表或视图,多表提示HINT类似于单表提示HINT。LEADING 是一个多表提示HINT的示例。

查询块:查询块提示HINT作用于单个查询块。STAR_TRANSFORMATION 和 UNNEST 是查询块提示HINT的示例。

语句:语句提示HINT应用于整个 SQL 语句。ALL_ROWS 是语句提示HINT的示例。

注: USE_NL(table1 table2) 不是多表提示HINT,因为它实际上是 USE_NL(table1) 和 USE_NL(table2) 的快捷方式。

 

指定提示HINT

提示HINT仅应用于一个语句块的优化:

  • 针对表的自包含 DML 语句
  • 顶层 DML 或子查询

oracle_sql_hint1

 

提示HINT仅应用于其所在语句块的优化。语句块包括:

  • 简单的 MERGE、SELECT、INSERT、UPDATE 或 DELETE 语句
  • 父语句或复杂语句的子查询
  • 使用集合运算符(UNION、MINUS、INTERSECT)的复合查询的一部分

例如,使用 UNION 运算符将两个组件查询组合在一起即构成一个复合查询,该复合查询有两个块,每个组件查询使用一个块。因此,第一个组件查询中的提示HINT仅应用于其自身的优化,而不应用于第二个组件查询的优化。

优化程序提示HINT语法

将提示HINT包在 SQL 语句的注释内。可以使用两种注释样式中的任意一种。提示HINT分隔符 (+) 必须紧跟在注释分隔符之后。如果它们之间留有空格,则优化程序无法识别包含提示HINT的
注释。

 

提示HINT规则

  • 提示HINT应紧跟在语句块的第一个 SQL 关键字之后。
  • 每个语句块只能有一个提示HINT注释,但该注释可以包含多个提示HINT。
  • 提示HINT仅应用于其所在语句块。
  • 如果语句使用别名,则提示HINT必须引用别名,而不是表名称。
  • 优化程序忽略没有正确指定的提示HINT,而不显示错误。
  • 必须使提示HINT注释紧跟在 SQL 语句块的第一个关键字(MERGE、SELECT、INSERT、DELETE 或 UPDATE)之后。
  • 一个语句块只能有一个包含提示HINT的注释,但可以在该注释内包含多个由空格分隔的
    提示HINT。
  • 提示HINT仅应用于其所在语句块,覆盖实例级或会话级的参数。
  • 如果 SQL 语句使用别名,则提示HINT必须引用别名,而不是表名称。

Oracle 服务器忽略没有正确指定的提示HINT。但是,请注意下列事实:

  • 您从不会收到错误消息。
  • 系统会考虑同一注释中的其它(正确指定的)提示HINT。
  • Oracle 服务器还忽略互相冲突的提示HINT的组合。

 

提示HINT建议

  • 请慎重使用提示HINT,因为它们会导致很高的维护负荷。
  • 当硬编码提示HINT的有效性降低时,请注意它们对性能的影响。
  • 不到万不得已,不使用提示HINT作为优化 SQL 语句的手段。
  • 提示HINT可能会阻止优化程序使用更好的执行计划。
  • 当数据库结构或内容发生变化时,提示HINT的有效性可能会降低(甚至无效)。

 

 

优化程序提示HINT语法:示例

UPDATE /*+ INDEX(p PRODUCTS_PROD_CAT_IX)*/  
products p 
SET   p.prod_min_price = 
        (SELECT  
         (pr.prod_list_price*.95) 
FROM products pr 
WHERE p.prod_id = pr.prod_id) 
WHERE p.prod_category = 'Men' 
AND   p.prod_status = 'available, on stock' 
/ 

 

显示了一个示例,其中的提示HINT建议基于成本的优化程序 (CBO) 使用索引。执行计划如下所示:

Execution Plan

———————————————————-

0      UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=3 …)

1    0   UPDATE OF ‘PRODUCTS’

2    1     TABLE ACCESS (BY INDEX ROWID) OF ‘PRODUCTS’ (TABLE) (Cost…)

3    2      INDEX (RANGE SCAN) OF ‘PRODUCTS_PROD_CAT_IX’ (INDEX)

(cost…)

4    1     TABLE ACCESS (BY INDEX ROWID) OF ‘PRODUCTS’ (TABLE) (Cost…)
5    4       INDEX (UNIQUE SCAN) OF ‘PRODUCTS_PK’ (INDEX (UNIQUE))
(Cost=0 …)

示例中显示的提示HINT只有在名为 PRODUCTS_PROD_CAT_IX 的索引存在于 PRODUCTS 表的 PROD_CATEGORY 列中才起作用。

 

 

提示HINT类别

提示HINT可用于以下项:

  • 优化方法和目标
  • 访问路径
  • 查询转换
  • 联接顺序
  • 联接操作
  • 并行执行
  • 其它提示HINT

 

 

优化目标和方法

ALL_ROWS 选择一个基于成本的方法以获得最佳吞吐量
FIRST_ROWS(n) 指示 Oracle 服务器优化单条 SQL 语句以进行快速响应

 

注:ALTER SESSION…SET OPTIMIZER_MODE 语句不影响在 PL/SQL 内运行的 SQL。

 

ALL_ROWS:ALL_ROWS 提示HINT显式选择一个基于成本的方法来优化一个语句块,目标是获得最佳吞吐量。即,使资源总消耗降到最低。

FIRST_ROWS(n):FIRST_ROWS(n) 提示HINT(其中 n 是任意正整数)指示 Oracle 服务器优化单条 SQL 语句以进行快速响应。它指示服务器选择可最高效地返回前 n 行的计划。FIRST_ROWS 提示HINT是为了实现向后兼容性并保持计划稳定而保留的,该提示HINT针对最佳计划进行优化以返回第一个单行。在包含任何阻塞操作(如排序或分组)的 SELECT 语句块中,优化程序会忽略此提示HINT。不会为了获得最佳响应时间而优化此类语句,因为 Oracle DB 在返回第一个行之前必须检索该语句访问的所有行。如果在任何此类语句中指定此提示HINT,则数据库会以获得最佳吞吐量为目标进行优化。

如果在 SQL 语句中指定 ALL_ROWS 或 FIRST_ROWS(n) 提示HINT,并且数据字典中没有有关该语句访问的表的统计信息,则优化程序会使用默认统计值估计缺少的统计信息,随后选择一个执行计划。

如果与 ALL_ROWS 或 FIRST_ROWS(n) 提示HINT一起指定了用于访问路径或联接操作的提示HINT,则优化程序会优先处理提示HINT指定的访问路径和联接操作。

注:FIRST_ROWS 提示HINT很可能是最有用的提示HINT。

 

访问路径的提示HINT

FULL 执行全表扫描
CLUSTER 通过聚簇扫描访问表
HASH 通过散列扫描访问表
ROWID 按 ROWID 访问表
INDEX 以升序扫描索引
INDEX_ASC 以升序扫描索引
INDEX_COMBINE 显式选择位图访问路径

 

 

只有某个索引存在从而确保指定访问路径的可用且访问路径位于 SQL 语句的语法结构中时,指定以上提示HINT之一才会促使优化程序选择指定的访问路径。如果提示HINT指定了一个不可用的访问路径,则优化程序会忽略它。您必须指定当出现在语句中时可以正确进行访问的表。如果语句使用表的别名,则提示HINT中应使用别名,而不是表名。如果语句中出现方案名称,则提示HINT中的表名不应包括方案名称。

FULL:FULL 提示HINT显式为指定表选择全表扫描。例如:

SELECT /*+ FULL(e) */ employee_id, last_name

FROM hr.employees e WHERE last_name LIKE ‘K%’;

Oracle 服务器对 employees 表执行全表扫描以执行此语句,即使 last_name 列上存在由 WHERE 子句中的条件启用的索引。

CLUSTER:CLUSTER 提示HINT指示优化程序使用聚簇扫描访问指定表。此提示HINT仅适用于聚
簇表。

HASH:HASH 提示HINT指示优化程序使用散列扫描访问指定表。此提示HINT仅适用于存储在表聚簇中的表。

ROWID:ROWID 提示HINT显式为指定表选择按 ROWID 扫描表。

 

INDEX:INDEX 提示HINT显式为指定表选择索引扫描。您可以为域、B* 树、位图和位图联接索引使用 INDEX 提示HINT。但是,最好为位图索引使用 INDEX_COMBINE 而不是 INDEX,因为前者是更通用的提示HINT。此提示HINT可根据需要指定一个或多个索引。

如果此提示HINT指定一个可用索引,则优化程序会按此索引执行扫描。优化程序不考虑执行全表扫描,也不按表中的其它索引执行扫描。

如果此提示HINT指定了一个可用索引列表,则优化程序会考虑按列表中的各个索引执行扫描的成本,然后执行成本最低的索引扫描。如果这样的访问路径具有最低成本,优化程序还会选择扫描此列表中的多个索引,并合并结果。优化程序不考虑执行全表扫描,也不按提示HINT中没有列出的索引进行扫描。

如果此提示HINT未指定任何索引,则优化程序会考虑按表的每个可用索引扫描的成本,然后执行成本最低的索引扫描。如果这样的访问路径具有最低成本,优化程序还会选择扫描多个索引,并合并结果。优化程序不考虑执行全表扫描。

INDEX_ASC:INDEX_ASC 提示HINT显式为指定表选择索引扫描。如果语句使用索引范围扫描,Oracle 服务器会按其索引值的升序扫描索引条目。由于服务器对范围扫描的默认行为是按其索引值的升序扫描索引条目,因此此提示HINT指定的内容不比 INDEX 提示HINT指定的多。但是,如果此默认行为发生更改,则您也许需要使用 INDEX_ASC 提示HINT显式指定升序的范围扫描。

INDEX_DESC:INDEX_DESC 提示HINT指示优化程序为指定表使用降序索引扫描。如果语句使用索引范围扫描,并且索引是升序的,则系统按其索引值的降序扫描索引条目。在分区索引中,结果按降序排列在各个分区中。对于降序索引,此提示HINT可有效消除降序,实现按升序扫描索引条目。

INDEX_COMBINE:INDEX_COMBINE 提示HINT显式为表选择位图访问路径。如果没有为 INDEX_COMBINE 提示HINT提供索引作为参数,则优化程序会为表使用具有最佳估计成本的位图索引布尔型组合。如果提供了某些索引作为参数,则优化程序会尝试使用这些特定位图索引的某种布尔型组合。

例如:

SELECT /*+INDEX_COMBINE(customers cust_gender_bix cust_yob_bix)*/ *

FROM customers WHERE cust_year_of_birth < 70 AND cust_gender = ‘M’;

注:INDEX、INDEX_FFS 和 INDEX_SS 具有反义提示HINT,分别为 NO_INDEX、NO_INDEX_FFS 和 NO_INDEX_SS,用于防止使用这些路径。

 

访问路径的提示HINT

INDEX_JOIN 指示优化程序使用索引联接作为访问路径
INDEX_DESC 为指定表选择索引扫描
INDEX_FFS 执行快速完全索引扫描
INDEX_SS 执行索引跳过扫描
NO_INDEX 不允许使用索引集
AND_EQUAL 合并单列索引

 

INDEX_JOIN:INDEX_JOIN 提示HINT显式地指示优化程序使用索引联接作为访问路径。为了让提示HINT产生积极的影响,索引的数目要尽可能少,但必须包括解析查询所必需的列的所有索引。

例如,下列查询使用索引联接访问 employee_id 和 department_id 列,这两列在员工表中都已被编制索引。

SELECT /*+index_join(employees emp_emp_id_pk emp_department_ix)*/ employee_id, department_id
FROM hr.employees WHERE department_id > 50;

INDEX_DESC:INDEX_DESC 提示HINT显式为指定表选择索引扫描。如果语句使用索引范围扫描,则 Oracle 服务器将按索引值的降序扫描索引条目。在分区索引中,结果按降序排列在各个分区中。

例如:

SELECT /*+ INDEX_DESC(a ord_order_date_ix) */ a.order_date, a.promotion_id, a.order_id
FROM oe.orders a WHERE a.order_date < ’01-jan-1985′;

 

INDEX_FFS:INDEX_FFS 提示HINT会导致执行快速完全索引扫描,而不是全表扫描。

例如:

SELECT /*+ INDEX_FFS ( o order_pk ) */ COUNT(*)
FROM order_items l, orders o
WHERE l.order_id > 50 AND l.order_id = o.order_id;

INDEX_SS:INDEX_SS 提示HINT指示优化程序为指定表的指定索引执行索引跳过扫描。如果语句使用索引范围扫描,则系统将按索引值的升序扫描索引条目。在分区索引中,结果按升序排列在各个分区中。还有 INDEX_SS_ASC 和 INDEX_SS_DESC 提示HINT。

NO_INDEX:NO_INDEX 提示HINT显式禁止指定表使用索引集。

  • 如果此提示HINT指定了单个可用索引,则优化程序将不考虑按此索引执行扫描,但仍考虑其它没有指定的索引。
  • 如果此提示HINT指定了一个可用索引列表,则优化程序将不考虑按其中的任何指定索引执行扫描,但仍考虑列表中没有指定的其它索引。
  • 如果此提示HINT没有指定任何索引,则优化程序不考虑按表的任何索引执行扫描。这种行为与指定了表的所有可用索引列表的 NO_INDEX 提示HINT的行为相同。

NO_INDEX 提示HINT适用于基于函数的索引、B* 树索引、位图索引或域索引。如果 NO_INDEX 提示HINT和某个索引提示HINT(INDEX、INDEX_ASC、INDEX_DESC、 INDEX_COMBINE 或 INDEX_FFS)同时指定了相同的索引,则这些指定索引的 NO_INDEX 提示HINT和索引提示HINT都会被忽略,优化程序会考虑这些指定的索引。

例如:

SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id
FROM employees WHERE employee_id > 200;

AND_EQUAL:AND_EQUAL 提示HINT显式选择一个执行计划,该计划使用可合并几个单列索引的扫描的访问路径,在该路径中您可以指定:

  • 与要合并的索引相关联的表的名称或别名。
  • 要执行索引扫描的索引。必须至少指定两个索引,但不能多于五个。

 

INDEX_COMBINE 提示HINT:示例

SELECT –+INDEX_COMBINE(CUSTOMERS)

       cust_last_name

FROM  SH.CUSTOMERS

WHERE ( CUST_GENDER= ‘F’ AND

CUST_MARITAL_STATUS =  ‘single’)

OR     CUST_YEAR_OF_BIRTH BETWEEN ‘1917’ 
AND ‘1920’;

 

INDEX_COMBINE 提示HINT设计用于位图索引操作。请牢记以下内容:

  • 如果为提示HINT提供了某些索引作为参数,则优化程序会尝试使用这些特定位图索引的某种组合。
  • 如果提示HINT中没有列出任何索引,则认为所有索引都包含在提示HINT中。
  • 优化程序总是尝试使用提示HINT的索引,无论它们是否具有成本效益。

在幻灯片的示例中,假定幻灯片中语句的 WHERE 谓词引用的三个列(CUST_MARITAL_STATUS、CUST_GENDER 和 CUST_YEAR_OF_BIRTH)都有一个位图索引。启用 AUTOTRACE 时,语句的执行计划可以显示出来,如下一张幻灯片所示。

 

INDEX_COMBINE 提示HINT:示例

 

Execution Plan

—————————————————

|   0 | SELECT STATEMENT              |

|   1 |  TABLE ACCESS BY INDEX ROWID  | CUSTOMERS

|   2 |   BITMAP CONVERSION TO ROWIDS |

|   3 |    BITMAP OR                  |

|   4 |     BITMAP MERGE              |

|   5 |      BITMAP INDEX RANGE SCAN  | CUST_YOB_BIX

|   6 |     BITMAP AND                |

|   7 |      BITMAP INDEX SINGLE VALUE| CUST_MARITAL_BIX

|   8 |      BITMAP INDEX SINGLE VALUE| CUST_GENDER_BIX

 

 

查询转换的提示HINT

NO_QUERY_TRANSFORMATION 跳过所有查询转换
USE_CONCAT 将 OR 重写成 UNION ALL 并禁用 INLIST 处理
NO_EXPAND 阻止 OR 扩展
REWRITE 根据实体化视图重写查询
NO_REWRITE 关闭查询重写
UNNEST 将子查询主体合并到外围查询块中
NO_UNNEST 关闭取消嵌套

 

NO_QUERY_TRANSFORMATION:NO_QUERY_TRANSFORMATION 提示HINT指示优化程序跳过所有查询转换,包括但不限于 OR 扩展、视图合并、子查询取消嵌套、星形转换和实体化视图重写。

USE_CONCAT:USE_CONCAT 提示HINT使用 UNIONALL 集合运算符强制将查询的 WHERE 子句中的组合 OR 条件转换成复合查询。通常,只有当使用串联的查询成本比不使用串联的查询成本低时,才发生此转换。USE_CONCAT 提示HINT禁用 IN 列表处理。

NO_EXPAND:NO_EXPAND 提示HINT阻止基于成本的优化程序考虑为 WHERE 子句中有 OR 条件或 IN 列表的查询使用 OR 扩展。通常,优化程序会考虑使用 OR 扩展,如果它确定使用此方法的成本低于不使用此方法的成本,就会使用该方法。

REWRITE:REWRITE 提示HINT指示优化程序根据实体化视图在可能时重写查询,而不考虑成本。使用 REWRITE 提示HINT时,可以指定视图列表,也可以不指定视图列表。此课程不介绍实体化视图。

UNNEST:UNNEST 提示HINT允许优化程序在评估路径和联接时同时考虑子查询及其外围查询,指示优化程序对子查询取消嵌套,并将子查询主体合并到包围它的查询块的主体中。

 

查询转换的提示HINT 

MERGE 将复杂视图或子查询与其外围查询合并
NO_MERGE 阻止合并可合并的视图
STAR_TRANSFORMATION 让优化程序使用可在其中使用转换的最佳计划
FACT 指示应将被提示HINT表当作事实表
NO_FACT 指示不应将被提示HINT表当作事实表

 

MERGE:MERGE 提示HINT允许您为每个查询合并一个视图。如果某个视图的查询包含了 GROUP BY 子句或者在 SELECT 列表中使用了 DISTINCT 运算符,则只有启用了复杂视图合并时优化程序才能将视图的查询合并到正在访问的语句中。这是默认行为,但您可以使用 NO_MERGE 之类的提示HINT禁用此机制。也可以使用复杂合并将 IN 子查询合并到正在访问的语句中(如果该子查询没有关联)。

在不提供参数的情况下使用 MERGE 提示HINT时,应将该提示HINT放在视图查询块中。在提供视图名称作为参数的情况下使用 MERGE 时,应将该提示HINT放在外围查询中。

NO_MERGE:NO_MERGE 提示HINT会使 Oracle 服务器不合并可以被合并的视图。通过此提示HINT,用户可以对视图的访问方式施加更大的影响。在不提供参数的情况下使用 NO_MERGE 提示HINT时,应将该提示HINT放在视图查询块中。在提供视图名称作为参数的情况下使用 NO_MERGE 时,应将该提示HINT放在外围查询中。

 

STAR_TRANSFORMATION:STAR_TRANSFORMATION 提示HINT可使优化程序使用其中使用了转换的最佳计划。如果没有提示HINT,优化程序可能会基于成本作出决定,使用在没有转换时生成的最佳计划,而不使用已转换查询的最佳计划。

即使使用提示HINT,也不能保证转换一定会发生。优化程序仅在它认为合理时才生成子查询。如果没有生成子查询,则没有转换的查询,此时不管是否有提示HINT,都使用未转换查询的最佳计划。

FACT:FACT 提示HINT在星形转换上下文中使用,用于指示转换时应将被提示HINT表作为事实表。

NO_FACT:NO_FACT 提示HINT在星形转换上下文中使用,用于指示转换时不应将提示HINT表作为事实表。

 

联接顺序的提示HINT

ORDERED 使 Oracle 服务器按表在 FROM 子句中出现的顺序联接表
LEADING 将指定表用作联接顺序中的第一个表

 

下列提示HINT用于建议联接顺序:

ORDERED:ORDERED 使 Oracle 服务器按表在 FROM 子句中出现的顺序联接这些表。如果在执行联接的 SQL 语句中省略了 ORDERED 提示HINT,则优化程序会选择表的联接顺序。如果您知道某些信息(如从每个表选择多少行),而优化程序不了解这些信息,则可能需要使用 ORDERED 提示HINT指定联接顺序。以一个嵌套循环为例,最准确的方法是在 FROM 子句中按索引中的关键字的顺序排列这些表,大表放在最后。然后使用下列提示HINT:

/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */

这里,facts 是表,而 fact_concat 是索引。更常用的方法是使用 STAR 提示HINT。

LEADING:LEADING 提示HINT指示优化程序在执行计划中首先联接指定的表集。如果由于联接图中的相关性,不能首先按照指定的顺序联接指定表,则忽略 LEADING 提示HINT。如果针对不同的表指定了两个或多个 LEADING 提示HINT,则所有提示HINT都将被忽略。如果指定了 ORDERED 提示HINT,则将覆盖所有 LEADING 提示HINT。

 

联接操作的提示HINT

USE_NL 使用嵌套循环联接来联接指定表
NO_USE_NL 不使用嵌套循环执行联接
USE_NL_WITH_INDEX 类似于 USE_NL,但必须能够将一个索引用于联接
USE_MERGE 使用排序合并联接来联接指定表
NO_USE_MERGE 不为联接执行排序合并操作
USE_HASH 使用散列联接来联接指定表
NO_USE_HASH 不使用散列联接
DRIVING_SITE 指示优化程序在数据库选择的站点之外的站点执行查询

 

这里描述的每个提示HINT都建议了一种针对表的联接操作。在提示HINT中,指定表的方式必须与在语句中指定表的方式完全相同。如果语句使用表的别名,则在提示HINT中必须使用该别名,而不是表名。但是,提示HINT中的表名称不应包含方案名称(如果语句中包含方案名称)。建议将 USE_NL 和 USE_MERGE 提示HINT与 ORDERED 提示HINT组合使用。当引用的表被强制成为联接的内部表时,Oracle 服务器使用这些提示HINT;如果引用的表是外部表,则忽略提示HINT。

USE_NL:USE_NL 提示HINT促使 Oracle 服务器使用嵌套循环联接(将指定表用作内部表)将每一个指定表与另一个行来源相联接。如果需要优化语句以获得最佳响应时间,或使返回查询所选的第一行所需的用时最小,而不是为了获得最佳吞吐量,则可以通过使用 USE_NL 提示HINT,强制优化程序选择嵌套循环联接。

USE_NL_WITH_INDEX:USE_NL_WITH_INDEX 提示HINT类似于 USE_NL 提示HINT。但是,如果没有指定索引,优化程序必须能够使用某个索引,且该索引使用至少一个联接谓词作为索引关键字。如果指定了索引,优化程序必须能够使用该索引,且该索引使用至少一个联接谓词作为索引关键字。

NO_USE_NL:NO_USE_NL 提示HINT促使优化程序排除嵌套循环联接。但是,在某些情况下只能使用嵌套循环来联接表。在这类情况下,优化程序将忽略这些表的提示HINT。

 

在许多情况下,嵌套循环联接能比排序合并联接更快地返回第一行。在从一个表中读取第一个选定行,从另一个表中读取第一个匹配行,然后将它们组合在一起后,嵌套循环联接即可以返回第一个行。但是排序合并联接需要读取两个表的所有选定行并对它们进行排序,将每个已排序行来源的第一行组合在一起,所有这些完成后才能返回第一行。

在下面的语句中,通过一个提示HINT强制实施了嵌套循环,通过全表扫描访问 orders,并将 l.order_id = h.order_id 筛选条件应用于每个行。针对满足筛选条件的每个行,都会通过索引 order_id 访问 order_items。

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
FROM oe.orders h ,oe.order_items l
WHERE l.order_id = h.order_id;

在查询中添加 INDEX 提示HINT可以避免对 orders 执行全表扫描,这会导致执行计划类似于较大系统上使用的执行计划,即使在这里它可能并不是特别高效。

USE_MERGE:USE_MERGE 提示HINT促使 Oracle 服务器使用排序合并联接将每个指定表与另一个行来源联接,如下面的示例所示:

SELECT /*+USE_MERGE(employees departments)*/ * FROM employees, departments WHERE employees.department_id = departments.department_id;

NO_USE_MERGE:NO_USE_MERGE 提示HINT促使优化程序排除排序合并联接,并使用指定表作为内部表将每个指定表联接到另一个行来源。

USE_HASH:USE_HASH 提示HINT促使 Oracle 服务器使用散列联接将每个指定表与另一个行来源联接,如下例所示:

SELECT /*+USE_HASH(l l2) */ l.order_date, l.order_id,
l2.product_id, SUM(l2.unit_price*quantity)
FROM oe.orders l, oe.order_items l2
WHERE l.order_id = l2.order_id
GROUP BY l2.product_id, l.order_date, l.order_id;

下面是另一个示例:

SELECT /*+use_hash(employees departments)*/ *
FROM hr.employees, hr.departments
WHERE employees.department_id = departments.department_id;

NO_USE_HASH:NO_USE_HASH 提示HINT促使优化程序排除散列联接,并使用指定表作为内部表将每个指定表联接到另一个行来源。

DRIVING_SITE:此提示HINT指示优化程序在数据库选择的站点之外的站点执行查询。如果您使用分布式查询优化决定应在哪个站点执行联接,则此提示HINT很有用。

 

在许多情况下,嵌套循环联接能比排序合并联接更快地返回第一行。在从一个表中读取第一个选定行,从另一个表中读取第一个匹配行,然后将它们组合在一起后,嵌套循环联接即可以返回第一个行。但是排序合并联接需要读取两个表的所有选定行并对它们进行排序,将每个已排序行来源的第一行组合在一起,所有这些完成后才能返回第一行。

在下面的语句中,通过一个提示HINT强制实施了嵌套循环,通过全表扫描访问 orders,并将 l.order_id = h.order_id 筛选条件应用于每个行。针对满足筛选条件的每个行,都会通过索引 order_id 访问 order_items。

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
FROM oe.orders h ,oe.order_items l
WHERE l.order_id = h.order_id;

在查询中添加 INDEX 提示HINT可以避免对 orders 执行全表扫描,这会导致执行计划类似于较大系统上使用的执行计划,即使在这里它可能并不是特别高效。

USE_MERGE:USE_MERGE 提示HINT促使 Oracle 服务器使用排序合并联接将每个指定表与另一个行来源联接,如下面的示例所示:

SELECT /*+USE_MERGE(employees departments)*/ * FROM employees, departments WHERE employees.department_id = departments.department_id;

NO_USE_MERGE:NO_USE_MERGE 提示HINT促使优化程序排除排序合并联接,并使用指定表作为内部表将每个指定表联接到另一个行来源。

USE_HASH:USE_HASH 提示HINT促使 Oracle 服务器使用散列联接将每个指定表与另一个行来源联接,如下例所示:

SELECT /*+USE_HASH(l l2) */ l.order_date, l.order_id,
l2.product_id, SUM(l2.unit_price*quantity)
FROM oe.orders l, oe.order_items l2
WHERE l.order_id = l2.order_id
GROUP BY l2.product_id, l.order_date, l.order_id;

下面是另一个示例:

SELECT /*+use_hash(employees departments)*/ *
FROM hr.employees, hr.departments
WHERE employees.department_id = departments.department_id;

NO_USE_HASH:NO_USE_HASH 提示HINT促使优化程序排除散列联接,并使用指定表作为内部表将每个指定表联接到另一个行来源。

DRIVING_SITE:此提示HINT指示优化程序在数据库选择的站点之外的站点执行查询。如果您使用分布式查询优化决定应在哪个站点执行联接,则此提示HINT很有用。

 

其它提示HINT

APPEND 启用直接路径 INSERT
NOAPPEND 启用常规 INSERT
ORDERED_PREDICATES 强制优化程序保留谓词计算顺序
CURSOR_SHARING_EXACT 阻止用绑定变量替换文字
CACHE 覆盖表的默认高速缓存规范
PUSH_PRED 将联接谓词推入视图
PUSH_SUBQ 首先计算未合并的子查询
DYNAMIC_SAMPLING 控制动态采样以改善服务器性能

 

APPEND:APPEND 提示HINT允许您在数据库以串行模式运行时启用直接路径 INSERT。如果使用的不是企业版,则数据库处于串行模式。常规 INSERT 是串行模式下的默认设置,而直接路径 INSERT 是并行模式下的默认模式。在直接路径 INSERT 中,数据被附加到表的末尾,而不使用当前分配给表的现有空间。因此,直接路径 INSERT 比常规 INSERT 快很多。

NOAPPEND:NOAPPEND 提示HINT通过在 INSERT 语句执行期间禁用并行模式来启用直接路径 INSERT。(常规 INSERT 是串行模式下的默认设置,而直接路径 INSERT 是并行模式下的默认模式。)

ORDERED_PREDICATES:ORDERED_PREDICATES 提示HINT强制优化程序保留谓词的计算顺序,但谓词被用作索引关键字时除外。可在 SELECT 语句的 WHERE 子句中使用此提示HINT。

 

如果不使用 ORDERED_PREDICATES 提示HINT,Oracle 服务器将按下列顺序计算所有谓词:

  1. 首先按照 WHERE 子句中指定的顺序计算不带用户定义函数、类型方法或子查询的
    谓词。
  2. 接下来按照成本的升序计算带用户定义函数的谓词和带具有用户计算成本的类型方法的谓词。
  3. 随后按 WHERE 子句中指定的顺序计算带用户定义函数的谓词和带没有用户计算成本的类型方法的谓词。
  4. 然后计算 WHERE 子句中没有指定的谓词(例如,优化程序生成的过渡性谓词)。
  5. 最后按 WHERE 子句中指定的顺序计算不带子查询的谓词。

CURSOR_SHARING_EXACT:Oracle 服务器可以用绑定变量替换 SQL 语句中的文字(如果这样做是安全的)。此操作是由 CURSOR_SHARING 启动参数控制的。CURSOR_SHARING_EXACT 提示HINT可禁用此行为。换句话说,Oracle 服务器执行 SQL 语句时将不尝试用绑定变量替换文字。

CACHE:CACHE 提示HINT指示优化程序在执行全表扫描时将为表检索的块放在缓冲区高速缓存的相应热部分中。此提示HINT对于小型查找表很有用。

如 V$SYSSTAT 数据字典视图中所示,CACHE 和 NOCACHE 提示HINT会影响系统统计信息表扫描(长表)和表扫描(短表)。

PUSH_PRED:PUSH_PRED 提示HINT指示优化程序将联接谓词推到视图中。

PUSH_SUBQ:PUSH_SUBQ 提示HINT指示优化程序在执行计划中尽可能早地计算未合并的子查询。如果按常规,在执行计划中最后才会执行未合并的子查询。如果执行子查询的成本相对便宜,并且可以显著减少行的数量,则较早计算子查询可以改善性能。如果子查询应用于远程表,或应用于使用合并联接联接的表,则此提示HINT不会产生任何影响。

DYNAMIC_SAMPLING:DYNAMIC_SAMPLING 提示HINT使您可以通过确定更准确的选择性和基数估计值来控制动态采样,从而改善服务器性能。可以将 DYNAMIC_SAMPLING 的值设置为 0 到 10 之间的值。级别越高,编译人员对动态采样施加的影响越大,应用的范围越广。采样默认使用游标级别,除非您指定了一个表。

请看以下示例:

SELECT /*+ dynamic_sampling(1) */ * FROM …

如果满足所有下列条件,此示例将启用动态采样:

  • 查询中有多个表。
  • 至少一个表未被分析,且没有索引。
  • 优化程序确定需要对尚未进行分析的表执行一个成本相对较高的表扫描。

 

其它提示HINT

MONITOR 强制实施实时查询监控
NO_MONITOR 禁用实时查询监控
RESULT_CACHE 高速缓存查询或查询片段的结果
NO_RESULT_CACHE 为查询或查询片断禁用结果高速
缓存
OPT_PARAM 针对查询持续时间设置初始化参数

 

MONITOR:MONITOR 提示HINT为查询强制实施实时 SQL 监控,即使语句不是长时间运行的。只有在 CONTROL_MANAGEMENT_PACK_ACCESS 参数设置为 DIAGNOSTIC+TUNING 时,此提示HINT才有效。

NO_MONITOR:NO_MONITOR 提示HINT为查询禁用实时 SQL 监控。

RESULT_CACHE:RESULT_CACHE 提示HINT指示数据库将当前查询或查询片断的结果高速缓存在内存中,然后在今后执行查询或查询片断时使用已缓存的结果。

NO_RESULT_CACHE:如果将 RESULT_CACHE_MODE 初始化参数设置为 FORCE,则优化程序会将查询结果高速缓存在结果高速缓存中。在这种情况下,NO_RESULT_CACHE 提示HINT会为当前查询禁用此类高速缓存。

OPT_PARAM:OPT_PARAM 提示HINT使您可以仅针对当前查询的持续时间设置初始化参数。此提示HINT仅对以下参数有效:OPTIMIZER_DYNAMIC_SAMPLING、OPTIMIZER_INDEX_CACHING、OPTIMIZER_INDEX_COST_ADJ、OPTIMIZER_SECURE_VIEW_MERGING 和 STAR_TRANSFORMATION_ENABLED

 

提示HINT和视图

  • 不要使用视图中的提示HINT。
  • 使用视图优化技术:

–语句转换

–像访问表一样访问结果

  • 可以使用关于可合并视图和不可合并视图的提示HINT。

由于可以在一个上下文中定义视图,然后将其用于其它上下文中,因此不应使用视图中的提示HINT或使用关于视图的提示HINT;这样的提示HINT可能会导致意外的计划。尤其要注意的是,根据视图是否可合并到顶层查询中,视图中的提示HINT的处理方式不同于关于视图的提示HINT的处理
方式。

视图优化

该语句通常会转换成访问视图基表的等效语句。优化程序可以使用下列技术之一转换语句:

  • 将视图的查询合并到访问语句中的引用查询块中。
  • 将引用查询块的谓词推到视图中。

当上述转换不可能发生时,将执行视图的查询,并像访问表一样访问结果。这发生在执行计划的 VIEW 步骤。

 

可合并视图

如果视图定义不包含以下项,则优化程序可以将视图合并到引用查询块中:

  • 集合运算符(UNION、UNION ALL、INTERSECT、MINUS)
  • CONNECT BY 子句
  • ROWNUM 伪列
  • 在选择列表中有组函数(AVG、COUNT、MAX、MIN、SUM)

提示HINT和可合并视图

优化方法和目标提示HINT可能出现在顶层查询或视图中:

  • 如果顶层查询中存在这样的提示HINT,则不管视图中是否包含此类提示HINT,都使用此提示HINT。
  • 如果没有顶层优化程序模式提示HINT,只要视图中的所有模式提示HINT是一致的,就使用被引用视图中的模式提示HINT。
  • 如果被引用视图中有两个或多个模式提示HINT相互冲突,则放弃视图中的所有模式提示HINT,而使用默认的或用户指定的会话模式。

关于被引用视图的访问方法提示HINT和联接提示HINT将被忽略,除非视图只包含一个表(或引用只包含一个表的另一视图)。对于这样的单表视图,关于视图的访问方法提示HINT或联接提示HINT将应用于视图中的表。

访问方法提示HINT和联接提示HINT也可能出现在视图定义中:

  • 如果视图是子查询(即,如果视图出现在 SELECT 语句的 FROM 子句中),则当视图与顶层查询合并时将保留视图中的所有访问方法提示HINT和联接提示HINT。
  • 如果视图不是子查询,则仅当顶层查询没有引用其它表或视图时(即,如果 SELECT 语句的 FROM 子句仅包含此视图),才会保留视图中的访问方法提示HINT和联接提示HINT。

提示HINT和不可合并视图

使用不可合并视图时,视图中的优化程序模式提示HINT将被忽略。由顶层查询来决定优化模式。

由于不可合并视图是独立于顶层查询单独进行优化的,因此视图中的访问方法提示HINT和联接提示HINT总是被保留。出于同样的原因,顶层查询中关于视图的访问方法提示HINT将被忽略。

但是,由于(在这种情况下)不可合并视图与表类似,因此会保留顶层查询中关于视图的联接提示HINT。

 

全局表提示HINT

  • 扩展的提示HINT语法使您可以为出现在视图中的表指定提示HINT
  • 使用递归点表示法在提示HINT中引用表名称

CREATE view city_view AS

SELECT *

FROM   customers c

WHERE  cust_city like ‘S%’;

 

SELECT /*+ index(v.c cust_credit_limit_idx) */

    v.cust_last_name, v.cust_credit_limit

FROM   city_view v

WHERE  cust_credit_limit > 5000;

 

常规情况下,指定了表的提示HINT引用此提示HINT所在的 DELETE、SELECT 或 UPDATE 查询块中的表,而不是语句所引用的视图内的表。如果需要为视图内的表指定提示HINT,建议您使用全局提示HINT,而不要将提示HINT嵌入到视图中。

如幻灯片所示,通过使用扩展的表指定语法(该语法包括视图名称和表名称),可将表提示HINT转换成全局提示HINT。另外,在指定表之前还可以选择指定一个查询块名称。

例如,通过使用全局提示HINT结构,就不需要在视图主体中指定索引提示HINT,从而避免修改视图。

注:如果全局提示HINT引用了在同一查询中使用了两次的表名称或别名(例如,在 UNION 语句中),则提示HINT仅应用于表(或别名)的第一个实例。

 

 

在提示HINT中指定一个查询块

explain plan for

select /*+ FULL(@strange dept) */ ename

from emp e, (select /*+ QB_NAME(strange) */ * 

             from dept where deptno=10) d

where e.deptno = d.deptno and d.loc = ‘C’;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, ‘ALL’));

 

Plan hash value: 615168685

—————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost(%CPU)|

—————————————————————
|   0 | SELECT STATEMENT   |      |     1 |    41 |     7 (15)|

|*  1 |  HASH JOIN         |      |     1 |    41 |     7 (15)|

|*  2 |   TABLE ACCESS FULL| DEPT |     1 |    21 |     3  (0)|
|*  3 |   TABLE ACCESS FULL| EMP  |     3 |    60 |     3  (0)|
—————————————————————

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SEL$DB579D14

   2 – SEL$DB579D14 / DEPT@STRANGE

   3 – SEL$DB579D14 / E@SEL$1

 

在许多提示HINT中都可以指定一个可选的查询块名称,以指定提示HINT所应用于的查询块。此语法使您可以在外层查询中指定一个应用于内嵌视图的提示HINT。

查询块变量的语法采用 @queryblock 形式,其中 queryblock 是一个标识符,用于指定查询中的一个查询块。queryblock 标识符可以是系统生成的,也可以是用户指定的。在查询块自身中指定一个提示HINT,也可以将提示HINT应用于该查询块,并不一定要指定 @queryblock 语法。

幻灯片给出了一个示例。可以看到 SELECT 语句使用了一个内嵌视图。通过使用 QB_NAME 提示HINT,为相应查询块指定名称 strange。

本示例假设,DEPT 表的 DEPTNO 列有一个索引,这样优化程序通常可以选择该索引来访问 DEPT 表。不过,由于您指定 FULL 提示HINT应用于主查询块中的 strange 查询块,所以优化程序不使用上述索引。可以看到执行计划显示出对 DEPT 表执行了一个全表扫描。另外,计划的输出还清楚显示了原始查询中的各个查询块的系统生成名称。

 

指定完整的提示HINT集

SELECT /*+ LEADING(e2 e1) USE_NL(e1)
   INDEX(e1 emp_emp_id_pk) USE_MERGE(j) FULL(j) */

    e1.first_name, e1.last_name, j.job_id,
     sum(e2.salary) total_sal

FROM hr.employees e1, hr.employees e2, hr.job_history j

WHERE e1.employee_id = e2.manager_id

AND e1.employee_id = j.employee_id

AND e1.hire_date = j.start_date

GROUP BY e1.first_name, e1.last_name, j.job_id

ORDER BY total_sal;

 

使用提示HINT时,您有时可能需要指定一个完整的提示HINT集,以保证使用最佳执行计划。例如,如果您有一个包含许多表联接的非常复杂的查询,并且,如果您仅为给定表指定 INDEX 提示HINT,则优化程序需要自己确定要使用的访问路径以及相应的联接方法等等。因此,即使您给出 INDEX 提示HINT,优化程序也不一定使用该提示HINT,因为优化程序可能已确定它选定的联接方法和访问路径不能使用请求的索引。

在本例中,LEADING 提示HINT指定了要使用的确切联接顺序。同时还指定了对不同的表要使用的联接方法。

 

 

如何验证SQL PROFILE的性能?

如何检验sql profile的性能

10g以后的sql tuning advisor(可以通过Enterprise Manager或DBMS_SQLTUNE包访问)会给出对于SQL的建议包括以下四种:

1. 收集最新的统计信息
2. 彻底重构该SQL语句
3. 创建推荐的索引
4. 启用SQL TUNING ADVISOR找到的SQL PROFILE

这里我们要注意的是在production环境中显然不可能让我们在没有充分测试的前提下随意为SQL接受一个PROFILE,因为这可能为本来就性能糟糕而需要调优的系统引来变化。 但是如果恰巧没有合适的TEST环境,而你的SQL PROFILE又可能是性能压力的救命稻草时,我们可以使用以下方法在production环境中局部测试SQL PROFILE,仅在session级别生效:

 

 

Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table profile_test tablespace users as select * from dba_objects;

Table created.

SQL> create index ix_objd on profile_test(object_id);

Index created.

SQL> set linesize 200 pagesize 2000
SQL>  exec dbms_stats.gather_table_stats('','PROFILE_TEST');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;

SQL>  select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;

Execution Plan
----------------------------------------------------------
Plan hash value: 663678050

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

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

   1 - filter("OBJECT_ID"=5060)

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

		  f3v7dxj4bggvq

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_226
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 11/30/2012 13:13:27
Completed at       : 11/30/2012 13:13:30

-------------------------------------------------------------------------------
Schema Name   : SYS
Container Name: CDB$ROOT
SQL ID        : f3v7dxj4bggvq
SQL Text      :  select /*+ FULL( profile_test) */ * from profile_test where
                object_id=5060

-------------------------------------------------------------------------------
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: 99.79%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_226',
            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):             .005407           .000034      99.37 %
  CPU Time (s):                 .004599                 0        100 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     1470                 3      99.79 %
  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: 663678050

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

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

   1 - filter("OBJECT_ID"=5060)

2- Using SQL Profile
--------------------
Plan hash value: 2974300728

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |   113 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PROFILE_TEST |     1 |   113 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX_OBJD      |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=5060)

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

    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_226',task_owner => 'SYS', replace => TRUE,category=>'MACLEAN_TEST');		  

SQL> set autotrace on;
SQL> select /*+ FULL( profile_test) */ * from profile_test where
  2                  object_id=5060;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2974300728

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |   113 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PROFILE_TEST |     1 |   113 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX_OBJD      |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=5060)

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

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

SQL> 
SQL> alter session set sqltune_category=DEFAULT;

Session altered.

SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 663678050

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

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

   1 - filter("OBJECT_ID"=5060)

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

 

 

以上我们通过sqltune_category将SQL PROFILE的作用域限定在session级别,实现了对SQL PROFILE的性能测试。

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569