Know more about CBO Index Cost

近日偶读Joze Senegacnik(他是一名ACE)在OOW 2011上做的《Getting The Best From The Cost Based Optimizer》Presentation(这里可以下载),发现他总结的索引Unique Scan和Range Scan成本计算公式总结地很不错,贴出来共享:

 

Index Unique Scan Cost
INDEX UNIQUE SCAN COST = (BLEVEL (1-(OIC/100)) + 1) * (OICA/100)

 

Index Range Scan Cost
INDEX RANGE SCAN COST = (BLEVEL + FF*LFBL)*(1-(OIC/100))+ FF*CLUF)* (OICA/100)

 

 

formula does not include the CPU cost

  • BLEVEL = number of branch levels in index
  • add +1 for leaf block
  • FF = filtering factor – selectivity
  • LFBL = number of leaf blocks
  • CLUF = index clustering factor
  • OIC = optimizer_index_caching(default 0)
  • OICA = optimizer_index_cost_adj parameter(default=100)

CBO为什么不走索引?

原帖是Itpub上的网友提出一个CBO为什么不走索引的问题, 该问题的演示如下:

 

SQL> create table maclean1 as select * from dba_objects;

Table created.

SQL> update maclean1 set status='INVALID' where owner='MACLEAN';

2 rows updated.

SQL> commit;                                       

Commit complete.

SQL> create index ind_maclean1 on maclean1(status);

Index created.

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> explain plan for select * from maclean1 where status='INVALID';

Explained.

SQL> set linesize 140 pagesize 1400
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 987568083

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 11320 |  1028K|    85   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| MACLEAN1 | 11320 |  1028K|    85   (0)| 00:00:02 |
------------------------------------------------------------------------------

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

   1 - filter("STATUS"='INVALID')

13 rows selected.

10053 trace 

Access path analysis for MACLEAN1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for MACLEAN1[MACLEAN1]
  Column (#10): STATUS(
    AvgLen: 7 NDV: 2 Nulls: 0 Density: 0.500000
  Table: MACLEAN1  Alias: MACLEAN1
    Card: Original: 22639.000000  Rounded: 11320  Computed: 11319.50  Non Adjusted: 11319.50
  Access Path: TableScan
    Cost:  85.33  Resp: 85.33  Degree: 0
      Cost_io: 85.00  Cost_cpu: 11935345
      Resp_io: 85.00  Resp_cpu: 11935345
  Access Path: index (AllEqRange)
    Index: IND_MACLEAN1
    resc_io: 185.00  resc_cpu: 8449916
    ix_sel: 0.500000  ix_sel_with_filters: 0.500000
    Cost: 185.24  Resp: 185.24  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 85.33  Degree: 1  Resp: 85.33  Card: 11319.50  Bytes: 0

可以从以上10053中看到因为没有直方图存在,所以这里的Density = 0.5 是从 1/ NDV 算得的
也就意味着粗糙的统计信息显示STATUS='INVALID"的数据行占总行数的一半,
所以优化器选择做全表扫描是有道理的

 

 

以上符合”STATUS”=’INVALID’ condition的只有2行,且status列上建有索引,同时也使用了dbms_stats包收集表和索引上的统计信息,照理说CBO因该选择INDEX Range ind_maclean1,而避免全表扫描,但实际优化器opitimizer没有这样做。

 

 

实际上这个问题和统计信息收集时是否收集直方图有关系,只要收集了直方图,那么优化器就会了解到status=’INVALID’条件仅有少量的card满足,具有良好的选择性:

 

[oracle@vrh4 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 17 19:15:45 2011

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

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

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter optimizer_fea

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      11.2.0.2

SQL> select * from global_name;

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

SQL> drop table maclean;

Table dropped.

SQL>  create table maclean as select * from dba_objects;

Table created.

SQL> update maclean set status='INVALID' where owner='MACLEAN';

2 rows updated.

SQL>  commit;

Commit complete.

SQL> create index ind_maclean on maclean(status);

Index created.

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN',cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 2');

PL/SQL procedure successfully completed.

 

这里我们仅收集2个bucket的直方图, 就足以让优化器做出正确选择了。

得益于Quest公司的Guy Harrison所写的一个列出FREQUENCY直方图信息的脚本,以下为该脚本:

 

rem
rem Generate a histogram of data distribution in a column as recorded
rem  in dba_tab_histograms
rem
rem Guy Harrison Jan 2010 : www.guyharrison.net
rem
rem hexstr function is from From http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:707586567563 

set pagesize 10000
set lines 120
set verify off

col char_value format a10 heading "Endpoint|value"
col bucket_count format 99,999,999 heading "bucket|count"
col pct format 999.99 heading "Pct"
col pct_of_max format a62 heading "Pct of|Max value"
rem col endpoint_value format 9999999999999 heading "endpoint|value" 

CREATE OR REPLACE FUNCTION hexstr (p_number IN NUMBER)
    RETURN VARCHAR2
AS
    l_str      LONG := TO_CHAR (p_number, 'fm' || RPAD ('x', 50, 'x'));
    l_return   VARCHAR2 (4000);
BEGIN
    WHILE (l_str IS NOT NULL)
    LOOP
        l_return := l_return || CHR (TO_NUMBER (SUBSTR (l_str, 1, 2), 'xx'));
        l_str := SUBSTR (l_str, 3);
    END LOOP;

    RETURN (SUBSTR (l_return, 1, 6));
END;
/

WITH hist_data AS (
SELECT endpoint_value,endpoint_actual_value,
       NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value,
       endpoint_number,
       endpoint_number,
       endpoint_number
       - NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0)
           bucket_count
FROM dba_tab_histograms
JOIN dba_tab_col_statistics USING (owner, table_name,column_name)
WHERE     owner = '&owner'
      AND table_name = '&table'
      AND column_name = '&column'
      AND histogram='FREQUENCY')
SELECT nvl(endpoint_actual_value,endpoint_value) endpoint_value ,
       bucket_count,
       ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT,
       RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max
  FROM hist_data;

WITH hist_data AS (
SELECT endpoint_value,endpoint_actual_value,
       NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value,
       endpoint_number,
       endpoint_number,
       endpoint_number
       - NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0)
           bucket_count
FROM dba_tab_histograms
JOIN dba_tab_col_statistics USING (owner, table_name,column_name)
WHERE     owner = '&owner'
      AND table_name = '&table'
      AND column_name = '&column'
      AND histogram='FREQUENCY')
SELECT hexstr(endpoint_value) char_value,
       bucket_count,
       ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT,
       RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max
  FROM hist_data
ORDER BY endpoint_value;

 

使用该脚本,我们可以更直观的感受FREQUENCY直方图信息:

 

 

这里dbms_stats 包收集到的STATUS=’INVALID’ bucket count=9 percent = 0.04 ,可以和之后的10053 trace中的信息对比以下:

 

SQL> explain plan for select * from maclean where status='INVALID';

Explained.

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

PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 3087014066

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

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

   2 - access("STATUS"='INVALID')

以上可以看到只要收集了直方图CBO就会认识到满足STATUS=’INVALID’的cardnality很少 , 该条件具有良好的选择性 ,使用index range scan而非Full table scan。

我们进一步来看看有直方图情况下的10053 trace:

SQL> alter system flush shared_pool;

System altered.

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug event 10053 trace name context forever ,level 1;
Statement processed.

SQL> explain plan for select * from maclean where status='INVALID';

Explained.

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for MACLEAN[MACLEAN]
  Column (#10):
    NewDensity:0.000199, OldDensity:0.000022 BktCnt:22640, PopBktCnt:22640, PopValCnt:2, NDV:2

  这里的NewDensity= bucket_count / SUM(bucket_count) /2

   Column (#10): STATUS(
    AvgLen: 7 NDV: 2 Nulls: 0 Density: 0.000199
    Histogram: Freq  #Bkts: 2  UncompBkts: 22640  EndPtVals: 2
  Table: MACLEAN  Alias: MACLEAN
 Card: Original: 22640.000000 Rounded: 9 Computed: 9.00 Non Adjusted: 9.00
  Access Path: TableScan
    Cost:  85.30  Resp: 85.30  Degree: 0
      Cost_io: 85.00  Cost_cpu: 10804625
      Resp_io: 85.00  Resp_cpu: 10804625
  Access Path: index (AllEqRange)
    Index: IND_MACLEAN
    resc_io: 2.00  resc_cpu: 20763
    ix_sel: 0.000398  ix_sel_with_filters: 0.000398
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: IND_MACLEAN
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 9.00  Bytes: 0

 

上例中我们手动指定收集2 bucket的直方图后CBO优化器才能做出正确的选择,那么岂不是要人工干预来收集列的统计信息,默认的dbms_stats.DEFAULT_METHOD_OPT方式不能为我们提供有效的直方图收集方式吗?

实际上dbms_stats的自动决定直方图的收集与否及收集的桶数受到col_usage$基本中列充当predicate的历史记录影像,关于col_usage$详见<了解你所不知道的SMON功能(四):维护col_usage$字典基表>

 

假设在统计表上信息的dbms_stats存储过程执行之前,col_usage$中已经存有表上相关列充当predicate的记录,那么dbms_stats存储过程就会考虑为该列收集直方图信息, 如:

 

SQL> drop table maclean;

Table dropped.

SQL>  create table maclean as select * from dba_objects;

Table created.

SQL> update maclean set status='INVALID' where owner='MACLEAN';

2 rows updated.

SQL> commit;

Commit complete.

SQL> create index ind_maclean on maclean(status);

Index created.

使用dbms_stats默认method_opt收集maclean表

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN');

PL/SQL procedure successfully completed.

@histogram.sql 

Enter value for owner: SYS
old  12:    WHERE owner = '&owner'
new  12:    WHERE owner = 'SYS'
Enter value for table: MACLEAN
old  13:      AND table_name = '&table'
new  13:      AND table_name = 'MACLEAN'
Enter value for column: STATUS
old  14:      AND column_name = '&column'
new  14:      AND column_name = 'STATUS'

no rows selected

因为缺少col_usage$列使用信息,所以依然没有收集status列的直方图

    declare
    begin
    for i in 1..500 loop
	execute immediate ' alter system flush shared_pool';
	DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
    execute immediate 'select count(*)  from maclean where status=''INVALID'' ' ;
    end loop;
    end;
    /

PL/SQL procedure successfully completed.

SQL> select obj# from obj$ where name='MACLEAN';

      OBJ#
----------
     97215
SQL> select * from  col_usage$ where  OBJ#=97215;

       OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------
     97215          1              1              0                 0           0          0          0 17-OCT-11
     97215         10            499              0                 0           0          0          0 17-OCT-11

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN');

PL/SQL procedure successfully completed.

@histogram.sql 

Enter value for owner: SYS
Enter value for table: MACLEAN
Enter value for column: STATUS

Endpoint        bucket         Pct of
value            count     Pct Max value
---------- ----------- ------- --------------------------------------------------------------
INVALI               2     .04
VALIC3           5,453   99.96  *************************************************

何时会发生db file sequential read等待事件?

很多网友对系统内频繁发生的db file sequential read等待事件存有疑问,那么到底在那些场景中会触发该单块读等待事件呢?

在我之前写的一篇博文<SQL调优:Clustering Factor影响数据删除速度一例>中总结了db file sequential read等待事件可能发生的场景,在这里再share以下:

”db file sequential read”单块读等待是一种最为常见的物理IO等待事件,这里的sequential指的是将数据块读入到相连的内存空间中(contiguous memory space),而不是指所读取的数据块是连续的。该wait event可能在以下情景中发生:

  1. 最为常见的是执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN,此时出现”db file sequential read”等待是预料之中的,一般不需要我们去特别关注
  2. 当执行计划包含了INDEX RANGE SCAN-(“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”), 服务进程将按照”访问索引->找到rowid->访问rowid指定的表数据块并执行必要的操作”顺序访问index和table,每次物理 读取都会进入”db file sequential read”等待,且每次读取的都是一个数据块;这种情况下clustering_factor将发挥其作用,需要我们特别去关注,本例中提及的解决方法对 这种情景也有效
  3. Extent boundary,假设一个Extent区间中有33个数据块,而一次”db file scattered read”多块读所读取的块数为8,那么在读取这个区间时经过4次多块读取后,还剩下一个数据块,但是请记住多块读scattered read是不能跨越一个区间的(span an extent),此时就会单块读取并出现”db file sequential read”。这是一种正常现象,一般不需要额外关注
  4. 假设某个区间内有8个数据块,它们可以是块a,b,c,d,e,f,g,h,恰好当前系统中除了d块外的其他数据块都已经被缓存在buffer cache中了,而这时候恰好要访问这个区间中的数据,那么此时就会单块读取d这个数据块,并出现”db file sequential read”等待。注意这种情况不仅于表,也可能发生在索引上。这是一种正常现象,一般不需要额外关注
  5. chained/migrated rows即链式或迁移行,这里我们不介绍链式行的形成原因,chained/migrated rows会造成服务进程在fetch一行记录时需要额外地单块读取,从而出现”db file sequential read”。这种现象需要我们特别去关注,因为大量的链式/迁移行将导致如FULL SCAN等操作极度恶化(以往的经验是一张本来全表扫描只需要30分钟的表,在出现大量链式行后,全表扫描需要数个小时),同时也会对其他操作造成不那么 明显的性能影响。可以通过监控v$sysstat视图中的”table fetch continued row”操作统计来了解系统中链式/迁移行访问的情况,还可以通过DBA_TBALES视图中的CHAIN_CNT来了解表上的链式/迁移行情况,当然这 要求定期收集表上的统计信息;如果没有定期收集的习惯,那么可以配合@?/rdbms/admin/utlchain脚本和analyze table list chained rows 命令来获取必要的链式行信息
  6. 创建Index entry,显然当对表上执行INSERT操作插入数据时,虽然在执行计划中你看不到过多的细节,但实际上我们需要利用索引来快速验证表上的某些约束是否 合理,还需要在索引的叶子块中插入相关的记录,此时也可能出现”db file sequential read”等待事件,当然这还和具体的插入的方式有关系。这是一种正常现象,一般不需要额外关注
  7. 针对表上的UPDATE/DELETE,不同于之前提到的”INDEX RANGE SCAN-UPDATE/DELETE”,如果我们使用rowid去更新或删除数据时,服务进程会先访问rowid指向的表块(注意是先访问table block)上的行数据,之后会根据该行上的具体数据去访问索引叶子块(注意Oracle并不知道这些leaf block在哪里,所以这里同样要如range-scan/unique-scan那样去访问index branch block),这些访问都将会是单块读取,并会出现’db file sequential read’,完成必要的读取后才会执行更新或删除的实际EXEC操作,如下例:
以下trace中,obj#=1307547为sample表,而obj#=1307549为sample表上的唯一一个索引 

PARSING IN CURSOR #10 len=58 dep=0 uid=64 oct=6 lid=64 tim=1275805024007795 hv=505118268 ad='d387e470'
update sample set t2=t2+1 where rowid='AAE/OzAAEAAANUEAAQ'
END OF STMT
PARSE #10:c=1999,e=3016,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=1,tim=1275805024007787
WAIT #10: nam='db file sequential read' ela= 314 file#=4 block#=54532 blocks=1 obj#=1307547 tim=1275805024008308
WAIT #10: nam='db file sequential read' ela= 206 file#=6 block#=20 blocks=1 obj#=1307549 tim=1275805024009235
WAIT #10: nam='db file sequential read' ela= 206 file#=6 block#=742 blocks=1 obj#=1307549 tim=1275805024009496
WAIT #10: nam='db file sequential read' ela= 207 file#=6 block#=24 blocks=1 obj#=1307549 tim=1275805024009750
EXEC #10:c=2000,e=2297,p=6,cr=2,cu=8,mis=0,r=1,dep=0,og=1,tim=1275805024010210   --实际的UPDATE发生在这里

当大量执行这类UPDATE/DELETE操作时将需要频繁地交叉访问表和索引,如果恰好表上的某个索引有较高的 clustering_factor的话,那么就会形成本例中的这种性能问题了。实际上当表上有较多索引时,使用rowid来批量 update/delete数据这种方式是不被推荐的,仅当表上没有索引时才可能十分高效。如果你坚持要这样做,那么可以参照上面提到的建议。

 

8.BUG!BUG!已知在9i RAC及10g中使用ASM的情况下,存在引发在适用情况下不使用”scattered read”多块读而去使用”sequential read”的BUG。如果你的问题和上述情景都不匹配,但又有大量的”db file sequential read”等待事件,那么你有可能遇到bug了。在这里列出部分已知bug:

Bug# Version Affected
Bug 7243560 – High “db file sequential read” IO times when using ASM 10.2.0.4/11.1.0.7
Bug 7243560: RAPID INCREASE IN DB FILE SEQUENTIAL READ AFTER MOVING TO ASM 10.2.0.3
Bug 9711810: EXCESSIVE DB FILE SEQUENTIAL READS WITH NON COMPLIANT BUFFER CACHE ON RAC 9.2.0.8
Bug 9276739: INSERT STATEMENT HAS SLOW PERFORMANCE WITH DB FILE SEQUENTIAL READ 10.2.0.4
Bug 8625100: EXCESSIVE DB FILE SEQUENTIAL READ ON UNDO 10.2.0.4
Bug 8669544: HIGH DB FILE SEQUENTIAL READ AND GC CR DISK READ WAIT EVENTS DURING FULL SCAN 10.2.0.4
Bug 7427133: AN INSERT CAUSES LOTS OF ‘DB FILE SEQUENTIAL READ’ WAITS FOR THE INDEX BLOCKS 9.2.0.8
Bug 8493139: INCREASE IN DB FILE SEQUENTIAL READ WAITEVENT AFTER MIGRATING TO 10 RAC/ASM 10.2.0.4
Bug 5882268: PERFORMANCE ISSUE WITH ‘DB FILE SEQUENTIAL READ’ 10.2.0.2
Bug 7415702: LOTS OF ‘DB FILE SEQUENTIAL READ’ ON UNDO 10.2.0.3
Bug 5607724: 10202 DB FILE SEQUENTIAL READ THRICE AFTER UPGRADE FROM 9I 10.2.0.2

Script:Speed Up Large Index Create or Rebuild

以下脚本可以用于加速大表索引的创建或重建

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

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

-- Script Tested above 10g
-- Create a new temporary segment tablespace specifically for creating the index.
-- CREATE TEMPORARY TABLESPACE tempindex tempfile 'filename' SIZE 20G ;
-- ALTER USER username TEMPORARY TABLESPACE tempindex;

REM PARALLEL_EXECUTION_MESSAGE_SIZE can be increased to improve throughput.
REM but need restart instance,and should be same in RAC environment
REM this doesn't make sense,unless high parallel degree

-- alter system set parallel_execution_message_size=65535 scope=spfile;

alter session set workarea_size_policy=MANUAL;
alter session set workarea_size_policy=MANUAL;

alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;

--In conclusion, in order to have the least amount of direct operations and
--have the maximum possible read/write batches these are the parameters to set:

alter session set events '10351 trace name context forever, level 128';

REM set sort_area_size to 700M or 1.6 * table_size
REM 10g bug need to set sort_area_size twice
REM remember large sort area size doesn't mean better performance
REM sometimes you should reduce below setting,and then sort may benefit from disk sort
REM and attention to avoid PGA swap

alter session set sort_area_size=734003200;
alter session set sort_area_size=734003200;

REM set sort area first,and then set SMRC for parallel slave
REM Setting this parameter can activate our previous setting of sort_area_size
REM and we can have large sort multiblock read counts.

alter session set "_sort_multiblock_read_count"=128;
alter session set "_sort_multiblock_read_count"=128;

alter session enable parallel ddl;

create [UNIQUE] index ...     [ONLINE] parallel [Np] nologging;

alter index .. logging;
alter index .. noparallel;

--TRY below underscore parameter while poor performance 

--alter session set "_shrunk_aggs_disable_threshold"=100; 

REM   _newsort_type=2 only works if the patch for bug:4655998 has been applied
REM   The fix for bug:4655998 has been included in the 10.2.0.4 patchset.
REM   got worse in most cases

--alter session set "_newsort_type" = 2; 
OR  
--alter session set "_newsort_enabled"=false;                        then use Sort V1 algorithm,got worse in most cases

rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!IMPORTANT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
rem If the performance of a query has degraded and the majority of the
rem time is spent in the function kghfrempty, and the function that called
rem kghfrempty was kxsfwa called from kksumc, then you may be encountering
rem this problem.
rem Workaround:
rem Reducing sort_area_size may help by reducing the amount of memory that
rem each sort allocates, particularly if the IO subsystem is underutilized.
rem The performance of some queries that involved large sorts degraded due
rem to the memory allocation pattern used by sort.
rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

REM setting below parameter only if you are loading data into new system
REM you should restore them after loading
--alter session set db_block_checking=false;
--alter system set db_block_checksum=false;

Script:列出失效索引或索引分区

以下脚本可用于列出数据库中的失效的索引、索引分区、子分区:


REM list of the unusable index,index partition,index subpartition in Database 

Select owner, index_name, status
  From dba_indexes
 where status = 'UNUSABLE'
   and owner not in ('SYS','SYSTEM',
                     'SYSMAN',
                     'EXFSYS',
                     'WMSYS',
                     'OLAPSYS',
                     'OUTLN',
                     'DBSNMP',
                     'ORDSYS',
                     'ORDPLUGINS',
                     'MDSYS',
                     'CTXSYS',
                     'AURORA$ORB$UNAUTHENTICATED',
                     'XDB',
                     'FLOWS_030000',
                     'FLOWS_FILES')
 order by 1, 2 
/

select index_owner, index_name, partition_name
  from dba_ind_partitions
 where status ='UNUSABLE'
   and index_owner not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES') order by 1,2
/

Select
       Index_Owner
     , Index_Name
     , partition_name
     , SUBPARTITION_NAME
 From 
       DBA_IND_SUBPARTITIONS
Where
       status = 'UNUSABLE'  
       and index_owner not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES') order by 1, 2
/

SQL调优:Clustering Factor影响数据删除速度一例

事情是这样的,客户有一套核心的10g业务数据库,需要针对个别大表删除2年前的归档数据,这些表都是普通的堆表(heap table),没有使用分区或其他技术。因为考虑到不能影响在线业务,所以不能使用insert append/rename的方式来加速删除,只能老老实实地在匿名PL/SQL块里通过rowid批量删除数据,虽然慢一些但还是能接受的,具体的PL/SQL块如下:

DECLARE
   CURSOR table_name_cur IS SELECT /*+ FULL(a) */ a.rowid from table_name a  where time_column<required_date
   table_name_rec table_name_cur%ROWTYPE;
   row_number number;
BEGIN
   row_number :=0;
   OPEN table_name_cur;
   LOOP
      FETCH table_name_cur INTO table_name_rec;
      IF table_name_cur%NOTFOUND
      THEN
      commit;
         EXIT;
      END IF;
      delete from table_name WHERE rowid = table_name_rec.rowid;
      row_number := row_number + 1;
           if (mod (row_number,1000) =0) then
           insert into delete_rows values (row_number);
           commit;
           end if;
   END LOOP;
  insert into delete_rows values (row_number);
   commit;
   CLOSE table_name_cur;
END;
/

可以看到以上使用一个游标FULL SCAN目标数据表取出所需删除行的rowid,之后在循环中不断fetch出rowid并实际删除数据。

问题出在一张不是非常大的LG表上(不超过10GB),删除这张LG表消耗的时间超过10个小时,而其他更大的表删除也仅用2-3个小时。

针对这一反常现象,客户对删除操作做了10046 level8的跟踪,因为整个删除过程比较长,所以仅 trace了一小段时间,因为这个trace并不完整所以没有办法使用tkprof工具分析该trace。没办法,就分析裸trace信息吧。

从trace内容来看,该时段内主要的等待是db file sequence read(简称DFSR)即数据文件单块读事件,一开始以为是表上有链式行/迁移行造成了大量的DFSR,但客户日常有对该表执行chained rows analyze,没有发现该表上有明显的chained/migrated rows问题。

具体观察该DFSR事件的p1/p2 obj#参数发现这些数据文件单块读主要是针对该LG表的2个索引的,而且最为奇怪的是其中一个索引单块读的频率远多于另外一个索引,比例大约为60:1。这2个索引的差异表现,让我意识到得问题的所在,查看dba_indexes索引视图发现最近一次分析是在4/18日,而2个索引统计信息间最大的差异不在于索引大小,而在于clustering_factor也就是我们说的聚集因子, LG表上大约有6000万条数据,索引A的clustering_factor为170万,而索引B的clustering_factor达到了3400万,即2个索引的聚集因子差20倍,显然这时因为索引A上column更为有序(可能是sequence)而索引B上的字段较为随机造成了这种反差。

因为一开始使用FULL SCAN目标数据表来获取所需的ROWID,所以在实际删除前相关数据行所在的表数据块已经被FULL SCAN读取到buffer cache中了,FULL SCAN使用scattered read多块读,在这里不是主要的性能瓶颈。最主要的问题在于,假设一个table data block中有20行数据,对于clustering_factor较低的索引A而言可能这20行数据都映射到索引的一个leaf block中,而对于clustering_factor很高的索引B而言可能这20行数据需要映射到20个leaf block中,那么如果我们要删除这个数据块中的20行数据,就需要访问索引A上的1个leaf块和索引B上的20个leaf块,因为这些都是历史归档数据,所以日常已经没有业务访问这些old leaf block了,所以这部分的叶子块几乎不会在buffer cache中,服务进程需要把它们”一一”(这里真的是一一,一次读一块)从disk上读取到缓存中;最糟糕的还不止于此,因为实例的buffer cache有限,索引B上的leaf block在读入后可能很快被踢出buffer cache,而因为table与索引B间数据的无序性,这些leaf block可能需要在后续的删除中再次访问,这将对索引B的物理读取代价大大放大了。这种代价反映在Oracle wait interface上就是用户频繁地看到针对某个索引的”db file sequential read”等待事件。

我们通过还原现场,来深入了解一下clustering factor对于删除的影响:

首先要构建一张有一列极端有序,而一列极端无序的数据表

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> create table sample nologging tablespace users as select rownum t1  from dual  connect by level<=900000; 

Table created. 

SQL> alter table sample add t2 number;
Table altered.

SQL> update sample set t2=dbms_random.value(1,999999999999999);
900000 rows updated.

SQL> commit;
Commit complete.

SQL> create index ind_t1 on sample(t1) nologging;
Index created.

SQL> create index ind_t2 on sample(t2) nologging;
Index created.

SQL> exec dbms_stats.gather_table_stats('MACLEAN','SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.

SQL> select blocks,NUM_ROWS from dba_tables where table_name='SAMPLE';

    BLOCKS   NUM_ROWS
---------- ----------
     13213     900000

SQL> select CLUSTERING_FACTOR,LEAF_BLOCKS,DISTINCT_KEYS,index_name from dba_indexes where table_name='SAMPLE';

CLUSTERING_FACTOR LEAF_BLOCKS DISTINCT_KEYS INDEX_NAME
----------------- ----------- ------------- ------------------------------
           899782        4148        896896 IND_T2
            14975        2004        900000 IND_T1

/* 以上构建了一张90万行的数据表,共13213个block
    T1列通过序列产生,较为有序
    T2列通过随机数产生,无序
    ind_t1索引构建在t1列上,clustering_factor较低14975,接近表上数据块的总数
    ind_t2索引构建在t2列上,clustering_factor为899782,接近表上数据行的总数
*/  

SQL> alter session set events '10046 trace name context forever,level 8';
Session altered.

SQL> set timing on;

DECLARE
   CURSOR table_name_cur IS SELECT /*+ FULL(a) */ a.rowid from sample a  where t1<=900000;
   table_name_rec table_name_cur%ROWTYPE;
   row_number number;
BEGIN
   row_number :=0;
   OPEN table_name_cur;
   LOOP
      FETCH table_name_cur INTO table_name_rec;
      IF table_name_cur%NOTFOUND
      THEN
      commit;
         EXIT;
      END IF;
      delete from sample WHERE rowid = table_name_rec.rowid;
      row_number := row_number + 1;
           if (mod (row_number,1000) =0) then
           insert into delete_rows values (row_number);
           commit;
           end if;
   END LOOP;
  insert into delete_rows values (row_number);
   commit;
   CLOSE table_name_cur;
END;
/

Elapsed: 00:03:28.52 

观察其trace文件,可以发现在多次EXEC/FETCH后就会紧跟一个db file sequential read等待事件 

众所周知db file sequential read等待事件在如v$session/V$session/v$active_session_history
等动态性能视图中的p1代表file号,p2为block号,p3为读取block总数,一般为1
而在10046 trace中可以直接看到file#,block#,blocks和obj#,为了分辨单块读的对象,可以直接从obj#了解

SQL> select object_id,object_name,object_type from dba_objects where object_name in ('SAMPLE','IND_T1','IND_T2');

 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- -------------------- -------------------
   1307548 IND_T1               INDEX
   1307549 IND_T2               INDEX
   1307547 SAMPLE               TABLE

WAIT #3: nam='db file sequential read' ela= 283 file#=6 block#=3311 blocks=1 obj#=1307549 tim=1275797217728516
EXEC #3:c=999,e=349,p=1,cr=2,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728552
FETCH #2:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728578
EXEC #3:c=0,e=49,p=0,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728641
FETCH #2:c=0,e=4,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728663
EXEC #3:c=0,e=36,p=0,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728712
FETCH #2:c=0,e=3,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728732
WAIT #3: nam='db file sequential read' ela= 205 file#=6 block#=3956 blocks=1 obj#=1307549 tim=1275797217728979
EXEC #3:c=0,e=265,p=1,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217729010
FETCH #2:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217729036

[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|wc -l
72395

[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|grep 1307549|wc -l
67721

[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|grep 1307548|wc -l
3878

/* 以上object_id 1307549对应为较高clustering_factor的索引IND_T2,
    该索引发生了绝大多数db file sequential read等待
    而object_id 1307548对应为较低clustering_factor的索引IND_T1,
    该索引发生了较少量的db file sequential read等待
*/

SQL> select sql_id,executions,disk_reads,user_io_wait_time/1000000,elapsed_time/1000000
  2  from v$sql
  3  where sql_text='DELETE FROM SAMPLE WHERE ROWID = :B1 ';

SQL_ID        EXECUTIONS DISK_READS USER_IO_WAIT_TIME/1000000 ELAPSED_TIME/1000000
------------- ---------- ---------- ------------------------- --------------------
31m4m2drt2t5m     900000      74936                 67.862581           147.743482

[oracle@rh2 udump]$ tkprof g10r2_ora_5190.trc 5190.tkf sys=no

DELETE FROM SAMPLE
WHERE
 ROWID = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 900000     78.67     147.73      74936     916440    6401613      900000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   900001     78.67     147.73      74936     916440    6401613      900000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       21.99         21.99
  db file sequential read                     72362        0.05         67.60
  db file scattered read                        543        0.00          0.25
  log file switch completion                     29        0.97          5.81
  free buffer waits                             268        0.01          2.83
  latch: cache buffers lru chain                  3        0.00          0.00
  latch: object queue header operation            5        0.00          0.00
  log file switch (checkpoint incomplete)        22        0.97          8.46
  latch: In memory undo latch                     2        0.00          0.00
  latch: cache buffers chains                     1        0.00          0.00

可能是受限于固化的思维,在我的潜意识中总是觉得clustering_factor聚集因子只会影响select查询语句,而忽略了其对update/delete操作的影响;事实是clustering_factor(注意它只是一个统计信息指标,而非参数)反映了数据在表中的随机分布程度,当表上的数据分布无序时表和索引间的交叉访问将显得很糟糕,这种交叉访问并不局限于查询语句(一个典型可能是INDEX RANGE SCAN-TABLE ACCESS BY INDEX ROWID),也可能发生在DML操作所隐含的维护索引操作中。

显然除了通过以某些列的顺序整理表外没有太好的方法来降低clustering_factor,但实际上这样做是不可能的。首先定期有序化整理的成本过高了,其次如果表上有多个单列上的不同索引,如我们上述演示中的t1、t2列,如果以t2列的顺序整理表那么一个很可能的结果是t1列上的索引的clustering factor猛增,如:

SQL> create table ordered_sample nologging tablespace users as select * From sample order by t2;
Table created.

SQL> truncate table sample;
Table truncated.

SQL> insert /*+ append */ into sample select * from ordered_sample;
900000 rows created.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats('MACLEAN','SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.

SQL> select clustering_factor,index_name from dba_indexes where table_name='SAMPLE';

CLUSTERING_FACTOR INDEX_NAME
----------------- ------------------------------
           899782 IND_T1
             3983 IND_T2

/* 可以看到ind_t2所以的clustering_factor的确下降了,换得的是ind_t1对应的增长 */

针对由该clustering_factor引起的低效率批量delete/update操作,我们可以通过以下措施减少”db file sequential read”等待的出现:

  1. 通过keep cache保留池等技术将clustering_factor过高的索引缓存住,以避免频繁地单块物理读,从而提高性能
  2. 如果你正在执行一个大作业,那么可以暂时将clustering_factor过高的索引drop掉,在完成操作后再重建该索引,这样起到加速作业的目的

rebuild重建索引在以上案例的情景中获益并不大。

另外”db file sequential read”单块读等待是一种最为常见的物理IO等待事件,这里的sequential指的是将数据块读入到相连的内存空间中(contiguous memory space),而不是指所读取的数据块是连续的。该wait event可能在以下情景中发生:

  1. 最为常见的是执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN,此时出现”db file sequential read”等待是预料之中的,一般不需要我们去特别关注
  2. 当执行计划包含了INDEX RANGE SCAN-(“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”),服务进程将按照”访问索引->找到rowid->访问rowid指定的表数据块并执行必要的操作”顺序访问index和table,每次物理读取都会进入”db file sequential read”等待,且每次读取的都是一个数据块;这种情况下clustering_factor将发挥其作用,需要我们特别去关注,本例中提及的解决方法对这种情景也有效
  3. Extent boundary,假设一个Extent区间中有33个数据块,而一次”db file scattered read”多块读所读取的块数为8,那么在读取这个区间时经过4次多块读取后,还剩下一个数据块,但是请记住多块读scattered read是不能跨越一个区间的(span an extent),此时就会单块读取并出现”db file scattered read”。这是一种正常现象,一般不需要额外关注
  4. 假设某个区间内有8个数据块,它们可以是块a,b,c,d,e,f,g,h,恰好当前系统中除了d块外的其他数据块都已经被缓存在buffer cache中了,而这时候恰好要访问这个区间中的数据,那么此时就会单块读取d这个数据块,并出现”db file sequential read”等待。注意这种情况不仅于表,也可能发生在索引上。这是一种正常现象,一般不需要额外关注
  5. chained/migrated rows即链式或迁移行,这里我们不介绍链式行的形成原因,chained/migrated rows会造成服务进程在fetch一行记录时需要额外地单块读取,从而出现”db file sequential read”。这种现象需要我们特别去关注,因为大量的链式/迁移行将导致如FULL SCAN等操作极度恶化(以往的经验是一张本来全表扫描只需要30分钟的表,在出现大量链式行后,全表扫描需要数个小时),同时也会对其他操作造成不那么明显的性能影响。可以通过监控v$sysstat视图中的”table fetch continued row”操作统计来了解系统中链式/迁移行访问的情况,还可以通过DBA_TBALES视图中的CHAIN_CNT来了解表上的链式/迁移行情况,当然这要求定期收集表上的统计信息;如果没有定期收集的习惯,那么可以配合@?/rdbms/admin/utlchain脚本和analyze table list chained rows 命令来获取必要的链式行信息
  6. 创建Index entry,显然当对表上执行INSERT操作插入数据时,虽然在执行计划中你看不到过多的细节,但实际上我们需要利用索引来快速验证表上的某些约束是否合理,还需要在索引的叶子块中插入相关的记录,此时也可能出现”db file sequential read”等待事件,当然这还和具体的插入的方式有关系。这是一种正常现象,一般不需要额外关注
  7. 针对表上的UPDATE/DELETE,不同于之前提到的”INDEX RANGE SCAN-UPDATE/DELETE”,如果我们使用rowid去更新或删除数据时,服务进程会先访问rowid指向的表块(注意是先访问table block)上的行数据,之后会根据该行上的具体数据去访问索引叶子块(注意Oracle并不知道这些leaf block在哪里,所以这里同样要如range-scan/unique-scan那样去访问index branch block),这些访问都将会是单块读取,并会出现’db file sequential read’,完成必要的读取后才会执行更新或删除的实际EXEC操作,如下例:
以下trace中,obj#=1307547为sample表,而obj#=1307549为sample表上的唯一一个索引 

PARSING IN CURSOR #10 len=58 dep=0 uid=64 oct=6 lid=64 tim=1275805024007795 hv=505118268 ad='d387e470'
update sample set t2=t2+1 where rowid='AAE/OzAAEAAANUEAAQ'
END OF STMT
PARSE #10:c=1999,e=3016,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=1,tim=1275805024007787
WAIT #10: nam='db file sequential read' ela= 314 file#=4 block#=54532 blocks=1 obj#=1307547 tim=1275805024008308
WAIT #10: nam='db file sequential read' ela= 206 file#=6 block#=20 blocks=1 obj#=1307549 tim=1275805024009235
WAIT #10: nam='db file sequential read' ela= 206 file#=6 block#=742 blocks=1 obj#=1307549 tim=1275805024009496
WAIT #10: nam='db file sequential read' ela= 207 file#=6 block#=24 blocks=1 obj#=1307549 tim=1275805024009750
EXEC #10:c=2000,e=2297,p=6,cr=2,cu=8,mis=0,r=1,dep=0,og=1,tim=1275805024010210   --实际的UPDATE发生在这里

当大量执行这类UPDATE/DELETE操作时将需要频繁地交叉访问表和索引,如果恰好表上的某个索引有较高的clustering_factor的话,那么就会形成本例中的这种性能问题了。实际上当表上有较多索引时,使用rowid来批量update/delete数据这种方式是不被推荐的,仅当表上没有索引时才可能十分高效。如果你坚持要这样做,那么可以参照上面提到的建议。

 

8.BUG!BUG!已知在9i RAC及10g中使用ASM的情况下,存在引发在适用情况下不使用”scattered read”多块读而去使用”sequential read”的BUG。如果你的问题和上述情景都不匹配,但又有大量的”db file sequential read”等待事件,那么你有可能遇到bug了。在这里列出部分已知bug:

Bug# Version Affected
Bug 7243560 – High “db file sequential read” IO times when using ASM 10.2.0.4/11.1.0.7
Bug 7243560: RAPID INCREASE IN DB FILE SEQUENTIAL READ AFTER MOVING TO ASM 10.2.0.3
Bug 9711810: EXCESSIVE DB FILE SEQUENTIAL READS WITH NON COMPLIANT BUFFER CACHE ON RAC 9.2.0.8
Bug 9276739: INSERT STATEMENT HAS SLOW PERFORMANCE WITH DB FILE SEQUENTIAL READ 10.2.0.4
Bug 8625100: EXCESSIVE DB FILE SEQUENTIAL READ ON UNDO 10.2.0.4
Bug 8669544: HIGH DB FILE SEQUENTIAL READ AND GC CR DISK READ WAIT EVENTS DURING FULL SCAN 10.2.0.4
Bug 7427133: AN INSERT CAUSES LOTS OF ‘DB FILE SEQUENTIAL READ’ WAITS FOR THE INDEX BLOCKS 9.2.0.8
Bug 8493139: INCREASE IN DB FILE SEQUENTIAL READ WAITEVENT AFTER MIGRATING TO 10 RAC/ASM 10.2.0.4
Bug 5882268: PERFORMANCE ISSUE WITH ‘DB FILE SEQUENTIAL READ’ 10.2.0.2
Bug 7415702: LOTS OF ‘DB FILE SEQUENTIAL READ’ ON UNDO 10.2.0.3
Bug 5607724: 10202 DB FILE SEQUENTIAL READ THRICE AFTER UPGRADE FROM 9I 10.2.0.2

Oracle内部错误:ORA-00600:[6033]一例

一套HP-UX上的9.2.0.8系统,某条查询语句执行时出现ORA-00600: internal error code, arguments: [6033], [], [], [], [], [], [], []内部错误,错误trace信息如下:

*** SESSION ID:(583.18281) 2010-12-20 22:49:01.364
*** 2010-12-20 22:49:01.364
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [6033], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT INTERFACE_HEADER_ID, DOCUMENT_SUBTYPE, AGENT_ID, VENDOR_SITE_ID FROM PO_HEADERS_INTERFACE WHE
RE WF_GROUP_ID = :B1 ORDER BY INTERFACE_HEADER_ID
----- PL/SQL Call Stack -----
object line object
handle number name
c0000001067e3328 4332 package body APPS.PO_AUTOCREATE_DOC
c0000000fd267060 1 anonymous block
c000000108fe4d60 1979 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 1745 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 1099 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 560 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 1863 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 1099 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 560 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 1863 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 1099 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 560 package body APPS.WF_ENGINE_UTIL
c000000108fe4d60 1863 package body
PL/SQL call stack truncated after 1024 bytes.
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+184 ? ksedst() C0000000CEB36420 ?
400000000147994B ?

已知的ORA-00600:[6033]错误一般和索引逻辑讹误相关,metalink上有相关的Note建议在出现该错误后运行analyze table validate structure cascade语句以验证表与索引间的数据正确性。

ORA-600 [6033] "null value retrieved from index leaf lookup" [ID 45795.1]
Modified 03-JUN-2010 Type REFERENCE Status PUBLISHED
Note: For additional ORA-600 related information please read Note:146580.1

PURPOSE:
This article represents a partially published OERI note.
It has been published because the ORA-600 error has been
reported in at least one confirmed bug.
Therefore, the SUGGESTIONS section of this article may help
in terms of identifying the cause of the error.
This specific ORA-600 error may be considered for full publication
at a later date. If/when fully published, additional information
will be available here on the nature of this error.
SUGGESTIONS:
Run the ANALYZE command on any tables and indexes in the
trace file:
Example: ANALYZE TABLE
 VALIDATE STRUCTURE CASCADE;
Rebuild any corrupted indexes.
Index corruption.
Known Bugs

NB Bug Fixed Description
6401576 9.2.0.8.P22 OERI[ktbair1] / ORA-600 [6101] index corruption possible
5845232 9.2.0.8.P06 Block corruption / errors from concurrent dequeue operations
2718937 9.2.0.4, 10.1.0.2 OERI:6033 from SELECT on IOT with COMPRESSED PRIMARY KEY
1573283 8.1.7.2, 9.0.1.0 OERI:6033 from ALTER INDEX .. REBUILD ONLINE PARAMETERS ('OPTIMIZE FULL')
Certain index operations can lead to block corruption / memory corruption with varying symptoms such as ORA-600 [6033], ORA-600 [6101] , ORA-600 [ktbair1] , ORA-600 [kcbzpb_1], ORA-600 [4519] and ORA-600 [kcoapl_blkchk] if DB_BLOCK_CHECKING is enabled. Concurrent dequeue operations can lead to block corruption / memory corruption with varying symptoms such as ORA-600 [6033], ORA-600 [6101] and ORA-600 [kcoapl_blkchk] if DB_BLOCK_CHECKING is enabled. Note: This issue was previously fixed under bug 5559640 but that fix had a serious problem which could lead to SGA memory corruption. This fix supercedes the fix for bug 5559640. The problem with patch 5559640 is alerted in Note:414109.1 This fix is superceeded by the fix for bug 6401576.

通过analyze table validate structure cascade命令验证索引后若存在问题则会进一步产生相关的trace文件,一般这类索引逻辑讹误的问题可以通过drop-recreate索引来解决。

11g新特性:Note raised when explain plan for create index

这是一个挺实用的小特性,在11g r2中使用explain plan for create index时Oracle会提示评估的索引大小(estimated index size)了:

SQL> set linesize 200 pagesize 1400;
SQL> explain plan for create index ind_t23 on maclean(t2,t3);
Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 2510282917
----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |         |  1000K|  9765K|  1267   (1)| 00:00:16 |
|   1 |  INDEX BUILD NON UNIQUE| IND_T23 |       |       |            |          |
|   2 |   SORT CREATE INDEX    |         |  1000K|  9765K|            |          |
|   3 |    TABLE ACCESS FULL   | MACLEAN |  1000K|  9765K|   760   (2)| 00:00:10 |
----------------------------------------------------------------------------------

Note
-----
   - estimated index size: 24M bytes

14 rows selected.

SQL> create index ind_t23 on maclean(t2,t3);
Index created.

SQL> select bytes/1024/1024 from dba_segments where segment_name='IND_T23';
BYTES/1024/1024
---------------
             23

SQL> analyze index ind_t23 validate structure;
Index analyzed.

SQL> select btree_space/1024/1024,used_space/1024/1024 from index_stats;
BTREE_SPACE/1024/1024 USED_SPACE/1024/1024
--------------------- --------------------
           22.3849487           20.0912952

/* 可以看到 explain plan给出的索引评估大小与实际索引占用空间差别不大 */

沪公网安备 31010802001379号

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