Search Results for: cost

Gather more plan statistics by gather_plan_statistics hint

在10g以后我们可以通过利用gather_plan_statistics提示来了解更多的SQL执行统计信息,具体使用方法如下:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> set autotrace traceonly exp

SQL> select avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;

Execution Plan
----------------------------------------------------------
Plan hash value: 3294250112

---------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY		      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS		      | 	    |	106 |  2438 |	  4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	107 |	749 |	  3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	  1 |	 16 |	  1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	  1 |	    |	  0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

SQL> show parameter cursor_sharing

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

SQL>  show parameter statistics_level

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
statistics_level		     string	 TYPICAL

SQL> set autotrace off;

SQL> select /*+ gather_plan_statistics */   avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;

SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  avg(SALARY),DEPARTMENT_NAME from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME

Plan hash value: 3294250112
-------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |    106 |00:00:00.01 |	  106 |
-------------------------------------------------------------------------------------------------------

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

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

/* 可以从starts列看到某种操作执行了多少次,例如这里的INDEX UNIQUE SCAN为107次 */

/*也可以通过SQL_ID来定位计划信息 */


SQL> select t.* 
from v$sql s 
   , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID' ;
  

Enter value for sql_id: bctzu9xuxay18 

old   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID'
new   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = 'bctzu9xuxay18'

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where
e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME

Plan hash value: 3294250112

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |   621 |     5  (20)| 00:00:01 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |  2438 |     4	(0)| 00:00:01 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |   749 |     3	(0)| 00:00:01 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    16 |     1	(0)| 00:00:01 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |       |     0	(0)|	      |    106 |00:00:00.01 |	  106 |
---------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1

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

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DEPARTMENT_NAME"[VARCHAR2,30], AVG("SALARY")[22]
   2 - "SALARY"[NUMBER,22], "DEPARTMENT_NAME"[VARCHAR2,30]
   3 - "SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
   4 - "DEPARTMENT_NAME"[VARCHAR2,30]
   5 - "D".ROWID[ROWID,10]

SQL> alter session set statistics_level=ALL;
Session altered.


/* 在session级别设置statistics_level为ALL,可以为我们提供更为详尽的执行统计信息 */

利用DBMS_ADVISOR.TUNE_MVIEW包生成物化视图创建语句

不少人大概和我一样在创建物化视图的时候会犯头痛,怎样合理的改写SQL语句以及添加物化视图日志需要经过慎重精密的考虑。有了DBMS_ADVISOR.TUNE_MVIEW存储过程这个帮手后,极大地方便了DBA或应用设计人员创建和优化物化视图。该TUNE_MVIEW存储过程可以做到优化物化视图中的查询定义,修正物化视图日志的问题,此外它还能为原先不能refresh fast的物化视图提出建议以使得其可以快速刷新。


SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTS 
  2  USING INDEX REFRESH FAST ON DEMAND 
  3  ENABLE QUERY REWRITE 
  4  AS select distinct t1,t2 from MACLEAN.strb;
AS select distinct t1,t2 from MACLEAN.strb
                                      *
ERROR at line 4:
ORA-12015: cannot create a fast refresh materialized view from a complex query


/* 以select distinct查询语句为例,该语句本身不符合refresh fast的标准,但TUNE_MVIEW存储过程
    可以将这种查询变形使得满足快速刷新的条件 */

--    PROCEDURE DBMS_ADVISOR.TUNE_MVIEW
--    PURPOSE: Tune a Create Materialized View statement to
--    ADVISOR SUPPORT:        SQL Access Advisor
--    PARAMETERS:
--         TASK_NAME
--            The user can pass in a user-defined task name or
--            get a returned system-generated task name.
--         MV_CREATE_STMT
--            CREATE MATERIALIZED VIEW SQL statement to tune

procedure tune_mview (task_name      in out varchar2,
                      mv_create_stmt in     clob);

SQL> set serveroutput on;

SQL> declare
  2    tn varchar2(200);
  3  begin
  4    DBMS_ADVISOR.TUNE_MVIEW(tn,
  5                            mv_create_stmt => 'CREATE MATERIALIZED VIEW MACLEAN.STRMTS 
  6                     USING INDEX REFRESH FAST ON DEMAND 
  7                     ENABLE QUERY REWRITE 
  8                     AS select distinct t1,t2 from MACLEAN.strb');
  9    dbms_output.put_line(tn);
 10  end;
 11  /
TASK_484

PL/SQL procedure successfully completed.

SQL> select script_type,statement
  2    from dba_tune_mview
  3   where task_name = 'TASK_484'
  4   order by action_id;
 
SCRIPT_TYPE    STATEMENT

IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRB" WITH ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRB" ADD ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW MACLEAN.STRMTS USING INDEX  REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT MACLEAN.STRB.T2 C1, MACLEAN.STRB.T1 C2, COUNT(*) M1 FROM MACLEAN.STRB GROUP BY MACLEAN.STRB.T2, MACLEAN.STRB.T1
UNDO           DROP MATERIALIZED VIEW MACLEAN.STRMTS

/* 可以看到TUNE_MVIEW存储过程将原查询变形为SELECT...GROUP BY的形式 */

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRB" WITH ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES;
Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRB" ADD ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES;
Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTS USING INDEX  REFRESH FAST WITH ROWID 
ENABLE QUERY REWRITE AS SELECT MACLEAN.STRB.T2 C1, MACLEAN.STRB.T1 C2, COUNT(*) M1 
FROM MACLEAN.STRB GROUP BY MACLEAN.STRB.T2, MACLEAN.STRB.T1;

Materialized view created.

针对那些确实无法快速刷新的复杂查询,TUNE_MVIEW过程也可能给出将一个查询分解为多个物化视图达到快速刷新和查询重写的目的:



SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTD 
  2  USING INDEX REFRESH FAST
  3  ON DEMAND ENABLE QUERY REWRITE AS 
  4  select t2,t3,count(*) from strc group by t2,t3 
  5  union all
  6  select t2,t3,count(*) from strd group by t2,t3;
select t2,t3,count(*) from strd group by t2,t3
                           *
ERROR at line 6:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL> set serveroutput on;
SQL> declare
  2    tn varchar2(200);
  3  begin
  4    DBMS_ADVISOR.TUNE_MVIEW(tn,
  5                            mv_create_stmt => 'CREATE MATERIALIZED VIEW MACLEAN.STRMTC
  6                                                   USING INDEX REFRESH FAST
  7                                                   ON DEMAND ENABLE QUERY REWRITE AS
  8                                                   select t2,t3,count(*) from strc group by t2,t3
  9                                                   union all
 10                                                  select t2,t3,count(*) from strd group by t2,t3');
 11    dbms_output.put_line(tn);
 12  end;
 13  /
TASK_547

PL/SQL procedure successfully completed.

SQL> select statement
  2    from dba_tune_mview
  3   where task_name = 'TASK_547'
  4   order by action_id;

CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRC" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRC" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRD" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRD" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB1 USING INDEX  REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2, COUNT(*) M1 FROM MACLEAN.STRC GROUP BY MACLEAN.STRC.T3, MACLEAN.STRC.T2
DROP MATERIALIZED VIEW MACLEAN.STRMTC$SUB1
CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB2 USING INDEX  REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2, COUNT(*) M1 FROM MACLEAN.STRD GROUP BY MACLEAN.STRD.T3, MACLEAN.STRD.T2
DROP MATERIALIZED VIEW MACLEAN.STRMTC$SUB2
CREATE MATERIALIZED VIEW MACLEAN.STRMTC USING INDEX  REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS  (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")
DROP MATERIALIZED VIEW MACLEAN.STRMTC
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('MACLEAN.STRMTC$RWEQ','select t2,t3,count(*) from strc group by t2,t3
                                                 union all
                                                select t2,t3,count(*) from strd group by t2,t3',' (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")',600916906)
 
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('MACLEAN.STRMTC$RWEQ')

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRC" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRC" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRD" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRD" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB1 USING INDEX  REFRESH FAST WITH ROWID 
ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2, COUNT(*) M1 
FROM MACLEAN.STRC GROUP BY MACLEAN.STRC.T3, MACLEAN.STRC.T2;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB2 USING INDEX  REFRESH FAST WITH ROWID 
ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2, COUNT(*) M1 
FROM MACLEAN.STRD GROUP BY MACLEAN.STRD.T3, MACLEAN.STRD.T2;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC USING INDEX  REFRESH FORCE WITH ROWID 
ENABLE QUERY REWRITE AS  
(SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") 
UNION ALL  
(SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2");

Materialized view created.

declare
  v_state varchar2(2000);
begin
  select statement
    into v_state
    from dba_tune_mview
   where task_name = 'TASK_547'
     and action_id = 15;
  v_state := 'begin ' || v_state || '; end;';
  dbms_output.put_line(v_state);
  execute immediate v_state;
end;
PL/SQL procedure successfully completed.

SQL> set linesize 200 pagesize 1400;
SQL> select t2,t3,count(*) from strc group by t2,t3 
  2  union all
  3  select t2,t3,count(*) from strd group by t2,t3;
no rows selected

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    74 |     4  (50)| 00:00:01 |
|   1 |  UNION-ALL                    |             |       |       |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| STRMTC$SUB1 |     1 |    37 |     2   (0)| 00:00:01 |
|   3 |   MAT_VIEW REWRITE ACCESS FULL| STRMTC$SUB2 |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

/* 可以看到查询成功被rewrite为对2个物化视图的扫描 */

Oracle Advanced Security:Column Encryption Overhead

在Oracle 10g中出现了column encryption列加密特性,通过对列上的数据加密实现数据安全性的目的。当然实现这一加密特性是有代价的,一方面会导致所加密列数据每行所占磁盘空间字节数增长,另一方面会消耗更多的cpu和内存资源。

当使用Oracle的TDE(transparent data encryption)加密数据表的某一列时将导致该表上每行数据所占用的空间大致增加33-51个字节,这几十个字节用作以下用途:

  • 其中20个字节用以对加密值的完整性检查,该部分可以通过’nomac’选项来省略
  • 同时加密会填补加密值到16个字节(如果列本身长度不够的话),举例来说如果是9个字节长度的number类型,那么加密该number字段时就会需要将该数据填补到16个字节,也就是额外地多用了7个字节
  • 加密时默认采用salt选项(default),salt是指一串长度为16个字节的随机string,在数据被正式加密前这串string将会被添加到列上,这种做法使得黑客无法通过比对已知的密文来匹配加密值(steal patterns of ciphertext to known ciphertext);salt总是位于加密数据的末尾;该部分可以通过no salt选项来省略。

注意默认使用salt选项加密的列是不能创建索引的,所以强烈建议加密列时强制使用no salt选项!加密列上的索引不支持范围扫描操作(Range scans on encrypted columns can’t use index),而加密表空间(encryption tablespace)没该限制。

SQL> create table enctab (t1 int encrypt);
Table created.

SQL>  create index ind_enc on enctab(t1);
create index ind_enc on enctab(t1)
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt

SQL> create table news (t1 varchar2(1) encrypt);
Table created.

/*以默认的salt和mac选项创建示例用表 */

SQL> insert into news values('1');
1 row created.

SQL> commit;
Commit complete.

/* 该列本身的长度为1个字节 */

SQL> select dump(t1,16) from news;

DUMP(T1,16)
--------------------------------------------------------------------------------
Typ=1 Len=1: 31

/* 以下为该行的dump信息,可以看到加密值增长到了52字节 */
col  0: [52]
de 76 08 74 2a c0 e3 94 89 e6 a8 3b 22 54 ca e5 af 4d eb a0 26 a7 e5 c2 f5
c0 e5 3a a0 09 9a 08 fa 56 2a 92 a0 83 b3 7f 0b 99 03 ad 12 78 d4 03 ec 6e
b3 c2

针对加密列可以使用’nomac’和no salt选项来减少性能损耗,其中’no mac’选项用以允许Oracle省略在加密数据中产生和存放message authentication code(MAC,信息验证代码),如上文所述的这部分代码用以对加密值的完整性检查,会占用20个字节的空间。通过使用’nomac’选项可以有效较少加密和后续操作所额外消耗的cpu周期,同时为加密值的存储减少20个字节的开销。
另外no salt选项用以省略加密中加入的16个字节的随机字符串(string),在能保证列值都唯一的情况下(攻击者无法通过已知密文比对的方式来解密),使用该选项可以有效减少cpu周期和每个单元16字节的空间开销。

oracledatabase12g.com>create table Maclean (t1 varchar2(16) encrypt no salt 'nomac');

/* 注意这里的nomac要被单引号括起来 */

oracledatabase12g.com>alter table table_name modify column_name encrypt [using ] [no salt] ['nomac'];

此外目前列加密不支持外键约束,造成这种限制的原因是每张表都有其唯一的密钥(encryption key);而表空间加密则不存在这种限制,即便某个从属表不在加密表空间上。

SQL> create table man (t1 int primary key );
Table created.

SQL> create table woman(t1 int encrypt);
Table created.

SQL>  alter table woman add constraint fk foreign key(t1) references man(t1);
 alter table woman add constraint fk foreign key(t1) references man(t1)
                                                 *
ERROR at line 1:
ORA-28335: referenced or referencing FK constraint column cannot be encrypted

列加密特性对于表连接(table joining)来说是透明的,即便作为连接条件的列被加密了也是如此(join tables is transparent,even if the columns for join condition are encrypted)。同时分区键是不能作为加密列的,否则将出现ORA-28346: an encrypted column cannot serve as a partitioning column错误。
此外加密列索引存在诸多限制,总结加密列索引(Indexes On Encrypted Columns)的几个restrictions:

  1. 只有使用no salt选项加密的列上才允许创建索引
  2. 加密列上不支持位图索引
  3. 加密列不支持外键
  4. 加密列上创建的索引只能做等式查询,因为不能做Range scan所以如between,like等非等式查询是不支持的;这种限制是由于索引中的数据也被加密了,所以实际上数据是以加密后的形式来排序的。所有非等式查询的条件均无法利用到索引,而使用全表扫描。
  5. 如果应用不使用等式查询的话,那么建议不要在加密列上创建索引,因为这样无益与性能,反而会增加性能开销。

而加密表空间(TDE Tablespace Encryption)不存在以上关于索引的限制,甚至在加密表空间上的表的索引在非加密表空间上也不会影响其使用,包括Range Scans;显然这一点出乎许多人的意料:

SQL> select tablespace_name,ENCRYPTED from dba_tablespaces  where tablespace_name in ('ENC','USERS');
TABLESPACE_NAME                ENC
------------------------------ ---
ENC                            YES
USERS                          NO

SQL> create table tv tablespace enc as select * from dba_objects;
Table created.

SQL> create index pk_tv on tv(object_id) tablespace enc;
Index created.

SQL> set autotrace on;
SQL> select 1 from tv where object_id=9999;
         1
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 2009574168
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PK_TV |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=9999)

/* 将该索引移动至非加密表空间上 */

SQL> alter index pk_tv rebuild tablespace users;
Index altered.

SQL> select 1 from tv where object_id=9999;

         1
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 2009574168
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PK_TV |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=9999)

/* 可以看到虽然索引被移动到非加密表空间上但仍可以被利用到并range scan */

SQL> create bitmap index obj_typ on tv (object_type) tablespace users;
Index created.

/* 创建位图索引也没有问题 */

当然表空间加密也仍然存在一些限制:

  • 加密表空间不能使用传统的exp/imp工具导入导出,而只能使用datapump工具
  • 显然External Large Objects (BFILEs)这种存储在数据库外的外部大对象也不受支持。

Exadata上的分页查询性能测试

Exadata上的分页查询性能测试,Exadata上的SMART SCAN STORAGE FULL FIRST ROWS对于没有索引情况下的分页查询可以而节约大量处理时间:

 

SQL> create table larget tablespace users as select rownum t1, rpad(‘M’,99,’A’) t2, rpad(‘M’,99,’A’) t3, rpad(‘M’,99,’A’) t4 from dual connect by level<=9999999;

Table created.

select llv.* from
(
select rownum rn, ll.* from
(select * from larget order by t1 ) ll
where rownum<=200) llv
where
llv.rn<10;

 

Elapsed: 00:00:07.01

Execution Plan
———————————————————-
Plan hash value: 3494307830

———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 200 | 35800 | | 483K (1)| 00:00:08 |
|* 1 | VIEW | | 200 | 35800 | | 483K (1)| 00:00:08 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 10M| 1586M| | 483K (1)| 00:00:08 |
|* 4 | SORT ORDER BY STOPKEY | | 10M| 1586M| 1739M| 483K (1)| 00:00:08 |
| 5 | TABLE ACCESS STORAGE FULL FIRST ROWS| LARGET | 10M| 1586M| | 118K (1)| 00:00:02 |
———————————————————————————————————–

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

1 – filter(“LLV”.”RN”<10)
2 – filter(ROWNUM<=200)
4 – filter(ROWNUM<=200)

Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
0 recursive calls
0 db block gets
432908 consistent gets
432901 physical reads
0 redo size
1513 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed

SQL> alter session set cell_offload_plan_display=never;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set cell_offload_processing=false;

Session altered.

 

select llv.* from
(
select rownum rn, ll.* from
(select * from larget order by t1 ) ll
where rownum<=200) llv
where
llv.rn<10;

 

Elapsed: 00:00:17.57

Execution Plan
———————————————————-
Plan hash value: 3494307830

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 200 | 35800 | | 483K (1)| 00:00:08 |
|* 1 | VIEW | | 200 | 35800 | | 483K (1)| 00:00:08 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 10M| 1586M| | 483K (1)| 00:00:08 |
|* 4 | SORT ORDER BY STOPKEY| | 10M| 1586M| 1739M| 483K (1)| 00:00:08 |
| 5 | TABLE ACCESS FULL | LARGET | 10M| 1586M| | 118K (1)| 00:00:02 |
——————————————————————————————-

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

1 – filter(“LLV”.”RN”<10)
2 – filter(ROWNUM<=200)
4 – filter(ROWNUM<=200)

Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
8 recursive calls
0 db block gets
434970 consistent gets
434783 physical reads
0 redo size
1513 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed

SQL> create index pk_ind on larget(t1) tablespace users;
Index created.

select llv.* from
(
select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=20) llv
where
llv.rn>=1;

 

Execution Plan
———————————————————-
Plan hash value: 3843929721

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 200 | 35800 | 12 (0)| 00:00:01 |
|* 1 | VIEW | | 200 | 35800 | 12 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 10M| 1586M| 12 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 10M| 1586M| 12 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PK_IND | 200 | | 3 (0)| 00:00:01 |
—————————————————————————————–

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

1 – filter(“LLV”.”RN”<10)
2 – filter(ROWNUM<=200)
5 – filter(“T1″ IS NOT NULL)

Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1513 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

select llv.* from
(
select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=2200) llv
where
llv.rn>2000;

 

Execution Plan
———————————————————-
Plan hash value: 3843929721

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 2200 | 384K| 104 (0)| 00:00:01 |
|* 1 | VIEW | | 2200 | 384K| 104 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 10M| 1586M| 104 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 10M| 1586M| 104 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PK_IND | 2200 | | 8 (0)| 00:00:01 |
—————————————————————————————–

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

1 – filter(“LLV”.”RN”>2000)
2 – filter(ROWNUM<=2200)
5 – filter(“T1″ IS NOT NULL)

Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
7 recursive calls
0 db block gets
310 consistent gets
91 physical reads
4776 redo size
6389 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed

SQL> select llv.* from
2 (
3 select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=22000) llv
where
llv.rn>21800; 4 5 6 7

200 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3843929721

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 22000 | 3845K| 1009 (0)| 00:00:01 |
|* 1 | VIEW | | 22000 | 3845K| 1009 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 10M| 1586M| 1009 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 10M| 1586M| 1009 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PK_IND | 22000 | | 54 (0)| 00:00:01 |
—————————————————————————————–

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

1 – filter(“LLV”.”RN”>21800)
2 – filter(ROWNUM<=22000)
5 – filter(“T1″ IS NOT NULL)

Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
0 recursive calls
0 db block gets
1035 consistent gets
0 physical reads
0 redo size
6789 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed

select llv.* from
(
select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=220000) llv
where
llv.rn>219800;
Statistics
———————————————————-
0 recursive calls
0 db block gets
10085 consistent gets
0 physical reads
0 redo size
6787 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed

alter table larget add primary key (t1);
CREATE MATERIALIZED VIEW LOG ON larget with primary key;

CREATE MATERIALIZED VIEW FIRST_PAGE REFRESH FAST with primary key ON demand AS
select llv.* from (select rownum rn, ll.* from (select /*+ index( larget pk_ind) */ * from larget where t1 is not null order by t1 ) ll
where rownum<=200) llv where llv.rn<10;

 

Oracle内部错误:ORA-00600[15801], [1]一例

一套Sparc Solaris上的11.1.0.7系统,在创建索引时频繁出现ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []内部错误,日志信息如下:

Tue Aug 17 17:34:21 2010
WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages
Tue Aug 17 17:34:21 2010
Errors in file /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/trace/ORAHCMU_p023_22262.trc (incident=12505):
ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/incident/incdir_12505/ORAHCMU_p023_22262_i12505.trc
Tue Aug 17 17:34:21 2010
Errors in file /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/trace/ORAHCMU_p021_22258.trc (incident=12489):
ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/incident/incdir_12489/ORAHCMU_p021_22258_i12489.trc

Errors in file /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/trace/ORAHCMU_p015_9328.trc (incident=19909):
ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []
Errors in file /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/trace/ORAHCMU_p043_9388.trc (incident=20133):
ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []
Mon Aug 23 14:43:42 2010
Errors in file /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/trace/ORAHCMU_p087_9668.trc (incident=20485):
ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []
Mon Aug 23 14:43:42 2010
Errors in file /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/trace/ORAHCMU_p012_9322.trc (incident=19885):
ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/incident/incdir_19789/ORAHCMU_ora_8602_i19789.trc
Mon Aug 23 14:43:43 2010
WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages

Dump continued from file: /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/trace/ORAHCMU_ora_8602.trc
ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []

*** 2010-08-23 14:43:42.974
----- Current SQL Statement for this session (sql_id=00abhfx460qm9) -----
CREATE UNIQUE iNDEX PS_HM_BEN_GP_STG ON PS_HM_BEN_GP_STG (CAL_ID, GP_PAYGROUP, 
EMPLID, EMPL_RCD, HM_INCURRED_BY, HM_SUM_ASSURED) TABLESPACE PSINDEX STORAGE 
(INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING

----- Call Stack Trace -----
ksedst1 ksedst dbkedDefDump dbgexPhaseII dbgexProcessError dbgePostErrorKGE kgeade kgerem
kxfpProcessError kxfpqidqr kxfpqdqr kxfxgs kxfxcp qerpxSendParse kxfpValidateSlaveGroup kxfpgsg
kxfrAllocSlaves kxfrialo kxfralo qerpx_rowsrc_start qerpxStart kdicrws kdicdrv opiexe opiosq0
kpooprx kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o main



SO: 0x3bf0bbf20, type: 4, owner: 0x3bf5452d0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x3bf5452d0, name=session, file=ksu.h LINE:10719 ID:, pg=0
(session) sid: 217 ser: 767 trans: 0x3bc0660f8, creator: 0x3bf5452d0
flags: (0x8000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x44008) DDLT1/-
DID: , short-term DID:
txn branch: 0x0
oct: 9, prv: 0, sql: 0x3b5d14510, psql: 0x3b6d59820, user: 31/SYSADM
ksuxds FALSE at location: 0
service name: ORAHCMU
client details:
O/S info: user: Administrator, term: UJWALTPVM, ospid: 304:2892
machine: WORKGROUP\UJWALTPVM program: pside.exe
client info: ujwal,Administrator,UJWALTPVM,,pside.exe,
application name: pside.exe, hash value=2824484291
Current Wait Stack:
Not in wait; last wait ended 2.475286 sec ago
Wait State:
auto_close=0 flags=0x21 boundary=0x0/-1
Session Wait History:
0: waited for 'lient'
=c8, =1, =0
wait_id=10483 seq_num=10484 snap_id=1
wait times: snap=0.168502 sec, exc=0.168502 sec, total=0.168502 sec
wait times: max=2.000000 sec
wait counts: calls=1 os=1
occurred after 0.000903 sec of elapsed time
1: waited for ' waiting for ruleset'
=10010063, =1, =0
wait_id=10482 seq_num=10483 snap_id=1
wait times: snap=0.008580 sec, exc=0.008580 sec, total=0.008580 sec
wait times: max=2.000000 sec
wait counts: calls=1 os=1
occurred after 0.000731 sec of elapsed time
2: waited for ' waiting for ruleset'
=1001004f, =4, =0
wait_id=10481 seq_num=10482 snap_id=1
wait times: snap=0.000132 sec, exc=0.000132 sec, total=0.000132 sec
wait times: max=2.000000 sec
wait counts: calls=1 os=1
occurred after 0.000074 sec of elapsed time
3: waited for ' waiting for ruleset'
=1001004f, =3, =0
wait_id=10480 seq_num=10481 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=2.000000 sec
wait counts: calls=1 os=1
occurred after 0.000065 sec of elapsed time

----- Session Cursor Dump -----
Current cursor: 1, pgadep=0

Open cursors(pls, sys, hwm, max): 3(0, 2, 64, 300)
NULL=1 SYNTAX=0 PARSE=0 BOUND=1 FETCH=0 ROW=1
Cached frame pages(total, free):
4k(14, 14), 8k(1, 1), 16k(1, 1), 32k(0, 0)

----- Current Cursor -----


----- Plan Table -----

============
Plan Table
============
----------------------------------------------------+-----------------------------------+-------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
----------------------------------------------------+-----------------------------------+-------------------------+
| 0 | CREATE INDEX STATEMENT | | | | 2 | | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 82 | 4510 | | |:Q1001| P->S |QC (ORDER) |
| 3 | INDEX BUILD UNIQUE | PS_HM_BEN_GP_STG| | | | |:Q1001| PCWP | |
| 4 | SORT CREATE INDEX | | 82 | 4510 | | |:Q1001| PCWP | |
| 5 | PX RECEIVE | | 82 | 4510 | 2 | 00:00:01 |:Q1001| PCWP | |
| 6 | PX SEND RANGE | :TQ10000 | 82 | 4510 | 2 | 00:00:01 |:Q1000| P->P |RANGE |
| 7 | PX BLOCK ITERATOR | | 82 | 4510 | 2 | 00:00:01 |:Q1000| PCWC | |
| 8 | TABLE ACCESS FULL | PS_HM_BEN_GP_STG| 82 | 4510 | 2 | 00:00:01 |:Q1000| PCWP | |
----------------------------------------------------+-----------------------------------+-------------------------+


----------------------------------------
Cursor#1(0xffffffff7ce31928) state=BOUND curiob=0xffffffff7ce57d28
curflg=4c fl2=0 par=0x0 ses=0x3bf0bbf20
----- Dump Cursor sql_id=00abhfx460qm9 xsc=0xffffffff7ce57d28 cur=0xffffffff7ce31928 -----
Dump Parent Cursor sql_id=00abhfx460qm9 phd=0x3b5d14510 plk=0x3b0bb3318
sqltxt(0x3b5d14510)=CREATE UNIQUE iNDEX PS_HM_BEN_GP_STG ON PS_HM_BEN_GP_STG 
(CAL_ID, GP_PAYGROUP, EMPLID, EMPL_RCD, HM_INCURRED_BY, HM_SUM_ASSURED) 
TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) 
PCTFREE 10 PARALLEL NOLOGGING
hash=616eaa631fc21f4c0029707748605a69
parent=0x3ae539590 maxchild=01 plk=0x3b0bb3318 ppn=n
cursor instantiation=0xffffffff7ce57d28 used=1282545779 exec_id=16777216 exec=1
child#0(0x3b5d05e10) pcs=0x3b678c128
clk=0x3b7e200d0 ci=0x3b5b204c8 pn=0x39955d2b8 ctx=0x3b86ee988
kgsccflg=0 llk[0xffffffff7ce57d30,0xffffffff7ce57d30] idx=0
xscflg=c0102276 fl2=c000400 fl3=2202008 fl4=100
Frames pfr 0xffffffff7ce67098 siz=85976 efr 0xffffffff7ce66fb8 siz=85960
Cursor frame dump
enxt: 7.0x00000168 enxt: 6.0x00008000 enxt: 5.0x00008000 enxt: 4.0x00003978
enxt: 3.0x00000490 enxt: 2.0x000000b8 enxt: 1.0x00000fa0
pnxt: 1.0x00000010
kxscphp=0xffffffff7dd80a18 siz=984 inu=312 nps=312
kxscwhp=0xffffffff7ddd2cc8 siz=8136 inu=6264 nps=3968
kxscefhp=0xffffffff7ce51468 siz=88456 inu=86128 nps=86128


FileName
----------------
ORAHCMU_ora_8602.trc

FileComment
----------------------


Oracle Support - August 27, 2010 6:13:39 PM GMT+08:00 [ODM Data Collection]
Name
--------
=== ODM Data Collection ===

=== ODM Data Collection ===

Trace file /u04/app/oracle/diag/rdbms/orahcmu/ORAHCMU/trace/ORAHCMU_p012_9322.trc


*** 2010-08-23 14:43:00.472
WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages
startup image information
iid info sz=245752512 inode=65458 ts=0x4c6df668
current process image information
iid info sz=245750720 inode=65427 ts=0x4c7204b0
set _disable_image_check = TRUE to disable this check
qksceLinearToCe error

*** 2010-08-23 14:43:42.974
*** SESSION ID:(220.111) 2010-08-23 14:43:42.974
*** CLIENT ID:(ujwal) 2010-08-23 14:43:42.974
*** SERVICE NAME:(ORAHCMU) 2010-08-23 14:43:42.974

DDE: Problem Key 'ORA 600 [15801]' was flood controlled (0x6) (incident: 19885)
ORA-00600: internal error code, arguments: [15801], [1], [], [], [], [], [], [], [], [], [], []
kxfxdss
KXFXSLAVESTATE dump [0, 0]
(pgakid: 0 oercnt: 0 oerrcd: -2224892588)
kxfxdss
no current cursor context.
kxfxdss
no cursors.

关于binary no match的问题已知是由于在实例启动情况下relink导致的;这个case提交了SR,metalink认为ORA-600 15801一般由QC与服务子进程通信问题引起:

The ORA-600 15801 is reporting a communication problem between QC and slaves related with messages sent/received.
Alert log reports several of the following error on the ASM instance:
ORA-600: internal error code, arguments: [15801], [1], [], [], [], [], [], 
[]

last wait was for 'eq: Msg Fragment' 

DIAGNOSTIC ANALYSIS:
--------------------
There were also several of the following message in the alert log:
WARNING: Oracle executable binary mismatch detected.
 Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these 
messages

So, I asked the customer to set the "_disable_image_check" = true 
This had no impact on the ora-600 errors as expected.

ORA-600 [15801] is signalled when a message overflow occurs between  PQ 
processes.

WORKAROUND:
-----------
none 
RELATED BUGS:
-------------
none
REPRODUCIBILITY:
----------------
intermittent but frequently - occurs at all different times of the day.
STACK TRACE:
------------
*** ID:(29.2904) 2006-07-05 15:50:57.972
qksceLinearToCe error
*** 15:50:58.233
ksedmp: internal or fatal error
ORA-600: internal error code, arguments: [15801], [1], [], [], [], [], [], 
[]
----- Call Stack Trace -----

kxfxGeter qks3tttdefReceive kxfxsui kxfxsp kxfxmai kxfprdp 

    SO: 0x67977018, type: 4, owner: 0x6793f208, flag: INIT/-/-/0x00
    (session) sid: 29 trans: (nil), creator: 0x6793f208, flag: (c0000041) 
USR/- BSY/-/-/-/-/-
              DID: 0000-0012-0000FADB, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              oct: 3, prv: 0, sql: (nil), psql: (nil), user: 0/SYS
    O/S info: user: oracle, term: , ospid: 4558, machine: 
    last wait for 'eq: Msg Fragment' blocking sess=0x(nil) seq=2 
wait_time=4441 seconds since wait started=3
                ct path write=1002ffff, ct path write temp=2, Network=0
    Dumping Session Wait History
     for 'eq: Msg Fragment' count=1 wait_time=4441
                ct path write=1002ffff, ct path write temp=2, Network=0
     for 'eq: Msg Fragment' count=1 wait_time=31
                ct path write=1002ffff, ct path write temp=1, Network=0
    temporary object counter: 0

最后这个case通过设置10235和10501事件后错误不再产生了:

event = "10235 trace name context forever, level 2"  

10235, 00000, "check memory manager internal structures" 

event = "10501 trace name context forever, level 1"
  
10501, 00000, "periodically check selected heap"
// *Cause:
// *Action:
//    Level:  0x01 PGA
//            0x02 SGA
//            0x04 UGA
//            0x08 current call
//            0x10 user call
//            0x20 large allocation pool

Oracle闩:Cache Buffers chains

Latch cache buffers chains大约是Oracle中child latch数量最多,使用最为频繁的闩锁了。其子闩总数受到初始化参数(8i中的db_block_buffers/4)的影响,Oracle中有大量kernel函数有机会接手持有cache buffer chains latch:

col parent_name for a25
col location for a40

SELECT t1.ksllasnam "parent_name",
       t2.ksllwnam  "location"
  FROM x$ksllw t2, x$kslwsc t1
 WHERE t2.indx = t1.indx
   AND ksllasnam = 'cache buffers chains';

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbw_activate_granule
cache buffers chains      kcbw_first_buffer_free
cache buffers chains      kcbwxb
cache buffers chains      kcbw_examine_granule
cache buffers chains      kcbw_next_free
cache buffers chains      kcbw_first_buffer_free_2
cache buffers chains      kcbbckb
cache buffers chains      kcbbioe
cache buffers chains      kcbbic1
cache buffers chains      kcbbcwd
cache buffers chains      kcbbxsv

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbbwdb
cache buffers chains      kcbbic2
cache buffers chains      kcbkzs
cache buffers chains      kcbrmf2so
cache buffers chains      kcbget: exchange rls
cache buffers chains      kcbralloc
cache buffers chains      kcbgcur: deadlock
cache buffers chains      kcbgcur: buf pinned
cache buffers chains      kcbgtcr
cache buffers chains      kcbchg: no fast path
cache buffers chains      kcbchg: apply change

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbrra: buf exists
cache buffers chains      kcbrra: update buf flags
cache buffers chains      kcbema: find buf
cache buffers chains      kcbtema: find buf
cache buffers chains      kcbget: prewarm wait
cache buffers chains      kcbrfrebuf
cache buffers chains      kcbsod1
cache buffers chains      kcbrbrl
cache buffers chains      kcbgcur: kslbegin
cache buffers chains      kcbgtcr: kslbegin shared
cache buffers chains      kcbrls: kslbegin

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbchg: kslbegin: bufs not pinned
cache buffers chains      kcbchg: kslbegin: call CR func
cache buffers chains      kcbnlc
cache buffers chains      kcbget: exchange
cache buffers chains      kcbget: pin buffer
cache buffers chains      kcbnew: new latch again
cache buffers chains      kcbgkcbcr
cache buffers chains      kcbget: in cur_read
cache buffers chains      kcbesc: escalate
cache buffers chains      kcblbi
cache buffers chains      kcbcge

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbfrl
cache buffers chains      kcbzsc
cache buffers chains      kcbibr
cache buffers chains      kcbnew_1
cache buffers chains      kcbema
cache buffers chains      kcbsrbd
cache buffers chains      kcbso1: set no access
cache buffers chains      kcbtema
cache buffers chains      kcbso1: in done_clr
cache buffers chains      kcbsod2
cache buffers chains      kcbzcg

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbzre1
cache buffers chains      kcbrlb1
cache buffers chains      kcbchkrsod
cache buffers chains      kcbxbh
cache buffers chains      kcbzsck
cache buffers chains      kcbgtcr: fast path
cache buffers chains      kcbgtcr: kslbegin excl
cache buffers chains      kcbgtcrf
cache buffers chains      kcbfdgd
cache buffers chains      kcbdng
cache buffers chains      kcbbufaddr2hdr

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbo_ivd_process
cache buffers chains      kcbo_write_process
cache buffers chains      kcbo_exam_buf
cache buffers chains      kcb_pre_apply: kcbhq61
cache buffers chains      kcb_post_apply: kcbhq62
cache buffers chains      kcb_post_apply: kcbhq63
cache buffers chains      kcbnew : new esc failed
cache buffers chains      kcbesc : escalate failed
cache buffers chains      kcb_private_owner
cache buffers chains      kcb_is_private
cache buffers chains      kcb_unprivatize

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcb_restore_block_headers
cache buffers chains      kcb_flush_undo_buffers
cache buffers chains      kcbgcur - DEADL
cache buffers chains      kcbtbd
cache buffers chains      kcbzwc
cache buffers chains      kcbzwx
cache buffers chains      kcbrmflx
cache buffers chains      kcbzwb
cache buffers chains      kcbzgb: get latch after post
cache buffers chains      kcbzgb: scan from tail. nowait
cache buffers chains      kcbzgb: exit_loop

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbzib: multi-block read: nowait
cache buffers chains      kcbzib: finish free bufs
cache buffers chains      kcbzcb
cache buffers chains      kcbzdh
cache buffers chains      kcbdpr
cache buffers chains      kcbcxx
cache buffers chains      kcbzrn
cache buffers chains      kcbdpd: for specific dba
cache buffers chains      kcbdpd: dump all buffers
cache buffers chains      kcbzib: exchange rls
cache buffers chains      kcbzpnd: dump buffers

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbzhngcbk1: get hash chain latch no wai
                          t

cache buffers chains      kcbo_cxx
cache buffers chains      kcbz_check_obj_reuse_sanity
cache buffers chains      kcbzib_grlk
cache buffers chains      kcbz_force_maps
cache buffers chains      kcbrldflx: recover in-flux bufs
cache buffers chains      kcbra1fbuf: recover in-flux bufs
cache buffers chains      kcbrafb: flashback bufs:1
cache buffers chains      kcbrafb: flashback bufs:2

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbr_media_apply: find buffer
cache buffers chains      kcbr_issue_read: alloc buffer
cache buffers chains      kcbr_issue_read: retry alloc
cache buffers chains      kcbr_validate_read: mark corrupt
cache buffers chains      kcbr_apply_change: after apply
cache buffers chains      kcbr_mapply_change
cache buffers chains      kcbr_mrcv_clear_fgda
cache buffers chains      kclwlr
cache buffers chains      kclebs_1
cache buffers chains      kclcls
cache buffers chains      kclcsr_1

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclpred
cache buffers chains      kclcls_1
cache buffers chains      kclple_1
cache buffers chains      kclple_2
cache buffers chains      kclcls_2
cache buffers chains      kcllwr
cache buffers chains      kclwcrs
cache buffers chains      kclcrs_1
cache buffers chains      kclcsr
cache buffers chains      kclrls
cache buffers chains      kclwcrs_1

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclfbst_1
cache buffers chains      kclpdc_1
cache buffers chains      kclwcrs_2
cache buffers chains      kclwcrs_3
cache buffers chains      kclfpdb
cache buffers chains      kclfpdb_2
cache buffers chains      kclpdc_2
cache buffers chains      kcllkopb
cache buffers chains      kclgrantlk
cache buffers chains      kclwrt
cache buffers chains      kcllkopb_1

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclwcrs_4
cache buffers chains      kcllkopb_2
cache buffers chains      kclcls_4
cache buffers chains      kclpred_1
cache buffers chains      kclrclr_2
cache buffers chains      kclrecbst
cache buffers chains      kclgrantlk_1
cache buffers chains      kclcls_5
cache buffers chains      kclrwrite_1
cache buffers chains      kclrwrite_2
cache buffers chains      kclcopy

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclswrite
cache buffers chains      kclchash
cache buffers chains      kclcfusion
cache buffers chains      kclfchk_1
cache buffers chains      kclcfusion_1
cache buffers chains      kclblkdone
cache buffers chains      kclcfusion_2
cache buffers chains      kclrenounce
cache buffers chains      kclbla
cache buffers chains      kclpto_1
cache buffers chains      kclgrantlk_2

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclcomplete
cache buffers chains      kclshrshr
cache buffers chains      kclclaim
cache buffers chains      kclhngcbk1
cache buffers chains      kclblkdone_1
cache buffers chains      kclgvlk
cache buffers chains      kclblkdone_2
cache buffers chains      kclcclaim
cache buffers chains      kclrechk_1
cache buffers chains      kclrechk_2
cache buffers chains      kclbr

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclpto
cache buffers chains      kclpdcl
cache buffers chains      kclpdc_3
cache buffers chains      kclpdc_4
cache buffers chains      kclgcr_1
cache buffers chains      kclcls_6
cache buffers chains      kclevict
cache buffers chains      kcldle
cache buffers chains      kclrcopy
cache buffers chains      kclenter
cache buffers chains      kclrbast

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclexpand
cache buffers chains      kclcls_3
cache buffers chains      kclverify
cache buffers chains      kclaffinity
cache buffers chains      kclassert
cache buffers chains      kclobj
cache buffers chains      kclobj_1
cache buffers chains      kclobj_2
cache buffers chains      kclgclk
cache buffers chains      kclwcrs_5
cache buffers chains      kclscrs

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclstalepi
cache buffers chains      kclstalepi_1
cache buffers chains      kclstalepi_2
cache buffers chains      kclgvlk_1
cache buffers chains      kclgclk_1
cache buffers chains      kclgclk_2
cache buffers chains      kclcsr_2
cache buffers chains      kcbvm

204 rows selected.

我们一般认为Latch结构是Mostly exclusive access的,也就是极少会有共享访问闩的机会。但Oracle一般对外宣称读取数据时服务进程是以共享模式使用cache buffers chains闩,这就造成了许多人误以为读读是不会出现latch: cache buffers chains争用的。
但是实际上查询语句大部分情况下仍需要以exclusive模式持有该类子闩(有时会以SHARED模式持有,这取决于读取时是使用kcbgtcr: kslbegin shared还是kcbgtcr: kslbegin excl;kcbgtcr是Oracle rdbms中重要的获取一致性读的函数,其含义为Kernal Cache Buffer GeT Cosistents Read,显然该函数存在两种获取cache buffers chains的方式即kslbegin shared和excl。与之相对应的是kcbgcur: kslbegin,kcbgcur的含义为Kernel Cache Buffer Get Current,该函数用以获取当前块以便修改,也就是”写”;很显然kcbgcur: kslbegin函数只有以excl排他方式持有child cache buffers chains latch的必要),原因在于虽然是查询语句但同样需要修改buffer header结构,譬如修改tch抢手度、holder list的hash变量us_nxt、us_prv以及waiter list的hash变量wa_prv、wa_nxt等。换而言之读读是会引起Latch free:cache buffers chains等待的,而非许多人认为的仅有读写、写写会导致缓存链闩争用。

这个问题我们再用实验证明一遍:

SQL> drop table maclean;
drop table maclean
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table maclean tablespace users as select * from dba_objects;
Table created.

SQL> select count(*) from maclean;

  COUNT(*)
----------
     51944

SQL> /

  COUNT(*)
----------
     51944

SQL> /

  COUNT(*)
----------
     51944

SQL> select spid from v$process where addr =(select paddr from v$session where sid=(select distinct sid from v$mystat));

SPID
------------
6023

另开一个命令行窗口:

[maclean@rh2 ~]$ gdb $ORACLE_HOME/bin/oracle 6023
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-23.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later 
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
...
Reading symbols from /s01/10gdb/bin/oracle...(no debugging symbols found)...done.
Attaching to program: /s01/10gdb/bin/oracle, process 6023
Reading symbols from /s01/10gdb/lib/libskgxp10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libskgxp10.so
Reading symbols from /s01/10gdb/lib/libhasgen10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libhasgen10.so
Reading symbols from /s01/10gdb/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libskgxn2.so
Reading symbols from /s01/10gdb/lib/libocr10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocr10.so
Reading symbols from /s01/10gdb/lib/libocrb10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocrb10.so
Reading symbols from /s01/10gdb/lib/libocrutl10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocrutl10.so
Reading symbols from /s01/10gdb/lib/libjox10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libjox10.so
Reading symbols from /s01/10gdb/lib/libclsra10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libclsra10.so
Reading symbols from /s01/10gdb/lib/libdbcfg10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libdbcfg10.so
Reading symbols from /s01/10gdb/lib/libnnz10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libnnz10.so
Reading symbols from /usr/lib64/libaio.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
0x00000039f280d8e0 in __read_nocancel () from /lib64/libpthread.so.0
(gdb) break kcbgtcr
Breakpoint 1 at 0x108c72c

回到原sqlplus窗口再次执行查询语句,会hang住:
SQL> select count(*) from maclean;

在gdb窗口:
(gdb) break kslfre
Breakpoint 2 at 0x7a133c

Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.

Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.

Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.

Breakpoint 2, 0x00000000007a133c in kslfre ()
(gdb) c
Continuing.

多次continue直到出现kslfre内核函数,开一个新的sqlplus窗口:
SQL> set autotrace on;
SQL> select count(*) from v$latchholder;

  COUNT(*)
----------
         3

Execution Plan
----------------------------------------------------------
Plan hash value: 1575818826

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    13 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |    13 |            |          |
|*  2 |   FIXED TABLE FULL| X$KSUPRLAT |     1 |    13 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------

v$latchholder的数据来源于X$KSUPRLAT内部视图,因为v$latchholder不带mode字段,所以我们直接观察X$KSUPRLAT

SQL> select KSUPRLAT "address",KSUPRLNM "name",KSUPRLMD "mode" from X$KSUPRLAT;

address          name                      mode
---------------- ------------------------- ---------------
00000000FCE40040 cache buffers chains      EXCLUSIVE
00000000FA696978 simulator lru latch       EXCLUSIVE
00000000FA6CDCE0 simulator hash latch      EXCLUSIVE

/* 可以看到即便是查询语句也是以EXCLUSIVE mode持有child cache buffers chains latch */

(gdb) quit
A debugging session is active.

        Inferior 1 [process 6235] will be detached.

Quit anyway? (y or n) y
Detaching from program: /s01/10gdb/bin/oracle, process 6235

/* 可以通过以下statistics可以看到以上读取为纯粹的逻辑读,没有物理读取的部分干扰*/

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

Oracle优化器:星型转换

Oracle 8i中引入了星型转换(star transformation)的优化器新特性以便更有效地处理星型查询。星型查询语句多用于基于星型模型设计的数据仓库应用中。星型模型的称谓源于该种模型以图形化表现时看起来形似一颗海星。这颗星的中央会由一个或多个事实表(fact tables)组成,而各个触角上则分布着多个维度表(dimension tables),如下图:

星型转换的基本思路是尽量避免直接去扫描星型模式中的事实表,因为这些事实表总会因为存有大量数据而十分庞大,对这些表的全表扫描会引起大量物理读并且效率低下。在典型的星型查询中,事实表总是会和多个与之相比小得多的维度表发生连接(join)操作。典型的事实表针对每一个维度表会存在一个外键(foreign key),除去这些键值(key)外还会存在一些度量字段譬如销售额度(sales amount)。与之对应的键值(key)在维度表上扮演主键的角色。而事实表与维度表间的连接操作一般都会发生在事实表上的外键和与之对应的维度表的主键间。同时这类查询总是会在维度表的其他列上存在限制十分严格的过滤谓词。充分结合这些维度表上的过滤谓词可以有效减少需要从事实表上访问的数据集合。这也就是星型转换(star transformation)的根本目的,仅访问事实表上相关的、过滤后精简的数据集合。

Oracle在Sample Schema示例模式中就存有星型模型的Schema,譬如SH:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------
www.oracledatabase12g.com

SQL> conn maclean/maclean
Connected.

SQL> select table_name,comments
  2    from dba_tab_comments
  3   where owner = 'SH'
  4     and table_name in ('SALES', 'CUSTOMERS', 'CHANNELS', 'TIMES');

TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------------------------------------------
CHANNELS                       small dimension table
CUSTOMERS                      dimension table
SALES                          facts table, without a primary key; all rows are uniquely identified by the comb
TIMES                          Time dimension table to support multiple hierarchies and materialized views

可以从以上各表的注释(comment)中看到,SALES表是SH模式下一个没有主键的事实表,而CHANNELS、CUSTOMERS、TIMES三个小表充当维度表的角色。我们试着构建以下星型查询语句,该查询用以检索出从1999年12月至2000年2月间Florida州所有城市直销形式的每月销售额。

SQL> col name for a35
SQL> col description for a45
SQL> col value for a8
SQL> select name,value,description from v$system_parameter where name='star_transformation_enabled';

NAME                                VALUE    DESCRIPTION
----------------------------------- -------- ---------------------------------------------
star_transformation_enabled         FALSE    enable the use of star transformation

/* 初始化参数star_transformation_enabled用以控制如何启用星型转换,
    默认为FALSE,该参数可以动态修改
*/

SELECT c.cust_city,
       t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
  FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id
   AND s.cust_id = c.cust_id
   AND s.channel_id = ch.channel_id
   AND c.cust_state_province = 'FL'
   AND ch.channel_desc = 'Direct Sales'
   AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
 GROUP BY c.cust_city, t.calendar_quarter_desc;

SQL> select * from table(dbms_xplan.display_cursor(format => 'IOSTATS'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

SQL_ID  ddjm7k72b8p2a, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */ c.cust_city,
t.calendar_quarter_desc,        SUM(s.amount_sold) sales_amount   FROM
sh.sales s, sh.times t, sh.customers c, sh.channels ch  WHERE s.time_id
= t.time_id    AND s.cust_id = c.cust_id    AND s.channel_id =
ch.channel_id    AND c.cust_state_province = 'FL'    AND
ch.channel_desc = 'Direct Sales'    AND t.calendar_quarter_desc IN
('2000-01', '2000-02','1999-12')  GROUP BY c.cust_city,
t.calendar_quarter_desc

Plan hash value: 382868716

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |     24 |00:00:00.62 |    1735 |   1726 |
|   1 |  HASH GROUP BY                 |           |      1 |     24 |     24 |00:00:00.62 |    1735 |   1726 |
|*  2 |   HASH JOIN                    |           |      1 |   1580 |   6015 |00:00:00.42 |    1735 |   1726 |
|*  3 |    TABLE ACCESS FULL           | CUSTOMERS |      1 |   2438 |   2438 |00:00:01.73 |    1459 |   1455 |
|*  4 |    HASH JOIN                   |           |      1 |   4575 |  74631 |00:00:00.18 |     276 |    271 |
|   5 |     PART JOIN FILTER CREATE    | :BF0000   |      1 |    227 |    182 |00:00:00.04 |      59 |     60 |
|   6 |      MERGE JOIN CARTESIAN      |           |      1 |    227 |    182 |00:00:00.04 |      59 |     60 |
|*  7 |       TABLE ACCESS FULL        | CHANNELS  |      1 |      1 |      1 |00:00:00.01 |       3 |      6 |
|   8 |       BUFFER SORT              |           |      1 |    227 |    182 |00:00:00.02 |      56 |     54 |
|*  9 |        TABLE ACCESS FULL       | TIMES     |      1 |    227 |    182 |00:00:00.02 |      56 |     54 |
|  10 |     PARTITION RANGE JOIN-FILTER|           |      1 |    117K|    117K|00:00:00.09 |     217 |    211 |
|  11 |      TABLE ACCESS FULL         | SALES     |      2 |    117K|    117K|00:00:00.07 |     217 |    211 |
---------------------------------------------------------------------------------------------------------------

可以看到在以上不使用星型转换的执行计划中对事实表SALES执行了全表扫描,这是我们不希望发生的。因为SALES表中每一行记录都对应于一笔销售记录,因此其可能包含数百万行记录。但实际上这其中仅有极小部分是我们在查询中指定的季度在弗罗里达州直销的纪录。若我们启用星型转换,执行计划是否有所改善?

SQL> alter session set star_transformation_enabled=temp_disable;
Session altered.

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

在我们的理想当中星型变化会将原查询语句转换成如下形式:

SELECT c.cust_city,
       t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
  FROM sh.sales s, sh.times t, sh.customers c
 WHERE s.time_id = t.time_id
   AND s.cust_id = c.cust_id
   AND c.cust_state_province = 'FL'
   AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
   AND s.time_id IN
       (SELECT time_id
          FROM sh.times
         WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
   AND s.cust_id IN
       (SELECT cust_id FROM sh.customers WHERE cust_state_province = 'FL')
   AND s.channel_id IN
       (SELECT channel_id
          FROM sh.channels
         WHERE channel_desc = 'Direct Sales')
 GROUP BY c.cust_city, t.calendar_quarter_desc;

/* 以添加AND..IN的形式明确了利用组合过滤谓词来减少需要处理的数据集 */

通过10053优化trace我们可以了解Oracle优化器是如何真正产生这部分过度谓词的:

FPD: Considering simple filter push in query block SEL$C3AF6D21 (#1)
"S"."CHANNEL_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "CH"."CHANNEL_ID" FROM "SH"."CHANNELS" "CH")
AND "S"."CUST_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "C"."CUST_ID" FROM "SH"."CUSTOMERS" "C") AND
"S"."TIME_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "T"."TIME_ID
FPD: Considering simple filter push in query block SEL$ACF30367 (#4)
"T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02' OR "T"."CALENDAR_QUARTER_DESC"='1999-12'
try to generate transitive predicate from check constraints for query block SEL$ACF30367 (#4)
finally: "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02' OR "T"."CALENDAR_QUARTER_DESC"='1999-12'

FPD: Considering simple filter push in query block SEL$F6045C7B (#3)
"C"."CUST_STATE_PROVINCE"='FL'
try to generate transitive predicate from check constraints for query block SEL$F6045C7B (#3)
finally: "C"."CUST_STATE_PROVINCE"='FL'

FPD: Considering simple filter push in query block SEL$6EE793B7 (#2)
"CH"."CHANNEL_DESC"='Direct Sales'
try to generate transitive predicate from check constraints for query block SEL$6EE793B7 (#2)
finally: "CH"."CHANNEL_DESC"='Direct Sales'

try to generate transitive predicate from check constraints for query block SEL$C3AF6D21 (#1)
finally: "S"."CHANNEL_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "CH"."CHANNEL_ID" FROM "SH"."CHANNELS" "CH")
AND "S"."CUST_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "C"."CUST_ID" FROM "SH"."CUSTOMERS" "C")
AND "S"."TIME_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "T"."TIME_ID

Final query after transformations:******* UNPARSED QUERY IS *******

最终转换后的查询语句:

SELECT "C"."CUST_CITY" "CUST_CITY",
       "T"."CALENDAR_QUARTER_DESC" "CALENDAR_QUARTER_DESC",
       SUM("S"."AMOUNT_SOLD") "SALES_AMOUNT"
  FROM "SH"."SALES" "S", "SH"."TIMES" "T", "SH"."CUSTOMERS" "C"
 WHERE "S"."CHANNEL_ID" = ANY (SELECT /*+ SEMIJOIN_DRIVER */
         "CH"."CHANNEL_ID" "ITEM_1"
          FROM "SH"."CHANNELS" "CH"
         WHERE "CH"."CHANNEL_DESC" = 'Direct Sales')
   AND "S"."CUST_ID" = ANY (SELECT /*+ SEMIJOIN_DRIVER */
         "C"."CUST_ID" "ITEM_1"
          FROM "SH"."CUSTOMERS" "C"
         WHERE "C"."CUST_STATE_PROVINCE" = 'FL')
   AND "S"."TIME_ID" = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         "T"."TIME_ID" "ITEM_1"
          FROM "SH"."TIMES" "T"
         WHERE "T"."CALENDAR_QUARTER_DESC" = '2000-01'
            OR "T"."CALENDAR_QUARTER_DESC" = '2000-02'
            OR "T"."CALENDAR_QUARTER_DESC" = '1999-12')
   AND "S"."TIME_ID" = "T"."TIME_ID"
   AND "S"."CUST_ID" = "C"."CUST_ID"
   AND "C"."CUST_STATE_PROVINCE" = 'FL'
   AND ("T"."CALENDAR_QUARTER_DESC" = '2000-01' OR
       "T"."CALENDAR_QUARTER_DESC" = '2000-02' OR
       "T"."CALENDAR_QUARTER_DESC" = '1999-12')
 GROUP BY "C"."CUST_CITY", "T"."CALENDAR_QUARTER_DESC"

/* 要比我们想想的复杂一些,子查询将IN语句化解了,
    并且AND...ANY的形式追加了过度谓词条件
*/

------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                              | Name             | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                       |                  |       |       |  1710 |           |       |       |
| 1   |  HASH GROUP BY                         |                  |  1254 |   77K |  1710 |  00:00:21 |       |       |
| 2   |   HASH JOIN                            |                  |  1254 |   77K |  1283 |  00:00:16 |       |       |
| 3   |    HASH JOIN                           |                  |  1254 |   45K |   877 |  00:00:11 |       |       |
| 4   |     TABLE ACCESS FULL                  | TIMES            |   227 |  3632 |    18 |  00:00:01 |       |       |
| 5   |     PARTITION RANGE SUBQUERY           |                  |  1254 |   26K |   858 |  00:00:11 | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 6   |      TABLE ACCESS BY LOCAL INDEX ROWID | SALES            |  1254 |   26K |   858 |  00:00:11 | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 7   |       BITMAP CONVERSION TO ROWIDS      |                  |       |       |       |           |       |       |
| 8   |        BITMAP AND                      |                  |       |       |       |           |       |       |
| 9   |         BITMAP MERGE                   |                  |       |       |       |           |       |       |
| 10  |          BITMAP KEY ITERATION          |                  |       |       |       |           |       |       |
| 11  |           BUFFER SORT                  |                  |       |       |       |           |       |       |
| 12  |            TABLE ACCESS FULL           | CHANNELS         |     1 |    13 |     3 |  00:00:01 |       |       |
| 13  |           BITMAP INDEX RANGE SCAN      | SALES_CHANNEL_BIX|       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 14  |         BITMAP MERGE                   |                  |       |       |       |           |       |       |
| 15  |          BITMAP KEY ITERATION          |                  |       |       |       |           |       |       |
| 16  |           BUFFER SORT                  |                  |       |       |       |           |       |       |
| 17  |            TABLE ACCESS FULL           | TIMES            |   227 |  3632 |    18 |  00:00:01 |       |       |
| 18  |           BITMAP INDEX RANGE SCAN      | SALES_TIME_BIX   |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 19  |         BITMAP MERGE                   |                  |       |       |       |           |       |       |
| 20  |          BITMAP KEY ITERATION          |                  |       |       |       |           |       |       |
| 21  |           BUFFER SORT                  |                  |       |       |       |           |       |       |
| 22  |            TABLE ACCESS FULL           | CUSTOMERS        |  2438 |   38K |   406 |  00:00:05 |       |       |
| 23  |           BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX   |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 24  |    TABLE ACCESS FULL                   | CUSTOMERS        |  2438 |   62K |   406 |  00:00:05 |       |       |
------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
2 - access("S"."CUST_ID"="C"."CUST_ID")
3 - access("S"."TIME_ID"="T"."TIME_ID")
4 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02'))
12 - filter("CH"."CHANNEL_DESC"='Direct Sales')
13 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
17 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02'))
18 - access("S"."TIME_ID"="T"."TIME_ID")
22 - filter("C"."CUST_STATE_PROVINCE"='FL')
23 - access("S"."CUST_ID"="C"."CUST_ID")
24 - filter("C"."CUST_STATE_PROVINCE"='FL')

从以上演示中可以看到,星型转换添加了必要的对应于维度表约束的子查询谓词。这些子查询谓词又被称为位图半连接谓词(bitmap semi-join predicates,见SEMIJOIN_DRIVER hint)。通过迭代来自于子查询的键值,再通过位图(bitmap)的AND、OR操作(这些位图可以源于位图索引bitmap index,但也可以取自普通的B*tree索引),我们可以做到仅仅访问事实表上的查询相关记录。理想状况下维度表上的过滤谓词可以帮我们过滤掉大量的数据,这样就可以使执行计划效率大大提升。当我们获取到事实表上的相关行后,这部分结果集可能仍需要同维度表使用原谓词重复连接(join back)。某些情况下,重复连接可以被省略,之后我们会提到。

如上演示中列出了星型转换后的查询语句的执行计划。这里可以看到Oracle是使用”TABLE ACCESS BY LOCAL INDEX ROWID”形式访问SALES事实表的,而非全表扫描。这里我们仅关心7-23行的执行计划,服务进程分别在(12,17,22)行从维度表中取得各维度表的相关键值(key value),同时对部分结果集执行了BUFFER SORT操作;在(13,18,23)行的’bitmap index range scan’操作中服务进程从事实表的三个对应于维度表外键的位图索引上(SALES_CHANNEL_BIX,SALES_TIME_BIX,SALES_CUST_BIX)获取了最原始的位图。位图上的每一个bit都对应于事实表上的一行记录。若从子查询中获取的键值(key values)与事实表上的值一致则bit置为1,否则为0。举例而言位图bitmap:[1][0][1][1][0][0][0]..[0](之后都为零)表示事实表上仅有第一、三、四行匹配于由子查询提供的键值。我们假设以上位图是由times表子查询提供的众多键值中的一个(如’2000-01′)的对应于事实表的位图表达式。

接着在执行计划的(10,15,20)行上的’bitmap key iteration’操作会迭代每一个由子查询提供的键值并获取相应的位图。我们假设times表子查询提供的另外2个键值’2000-02’和’1999-12’分别对应的位图为[0][0][0][0][0][1]..[0]和[0][0][0][0][1][0]…[0]即每键值都只有一行符合。

毫无疑问ITERATION迭代操作会为我们生成众多位图,接下来需要对这些不同键值对应的位图进行位图合并操作(BITMAP MERGE,相当于对位图做OR操作),可以看到在上例执行计划中为(9,14,19)行;以我们假设的times表子查询位图合并而言,会生产一个简单的位图[1][0][1][1][1][1][0][0]..[0],这个位图对应事实表上的第一、三、四、五、六行,是对’2000-01′,’2000-02′,’1999-12’三个键值对应位图的合并。

在获得最后位图前我们还需要对来自于三个子查询的位图进一步处理,因为原始查询语句中各约束条件是AND与的形式,因此我们还要对这些已合并的位图执行AND与操作,如执行计划中的第八行”BITMAP AND”,因为是AND与操作所以这步又会过滤掉大量记录。我们假设最终获得的位图是[1][0][1][0]…[0],即仅有第一、三行。

通过最终bitmap位图Oracle可以极高效地生成事实表的ROWID,此步骤表现为第七行的”BITMAP CONVERSION TO ROWIDS”,我们使用这些ROWID来访问事实表取得少量的”绝对”相关记录。以我们的假设而言最终位图仅有2位为1,只需要用这2行的ROWID从事实表上直接fetch2条记录即可,从而避免了低效的全表扫描。

省略重复连接

因为子查询及位图树只是通过维度表上的过滤条件为事实表过滤掉大量的数据,所以从事实表上获取的相关数据仍可能需要重复一次和维度表的连接。省略重复连接的前提是维度表上所有的谓词都是半连接谓词子查询的一部分,And 由子查询检索到的列均唯一(unique) And 维度表的列不被select或group by涉及。在上例中无需对CHANNELS表再次连接的理由是没有select(或group by)CHANNEL表上的列,且channel_id列是唯一的。

临时表转换

若在已知星型转换中重复连接维度表无法被省略的话,Oracle可以将对维度表的子查询结果集存储到内存中的全局临时表(global temporary table)上以避免重复扫描维度表。此外,因为将子查询的结果集物化了,故而若使用并行查询则每个并行子进程(slave)可以直接从物化结果集的临时表中获得数据,而不需要反复执行子查询。

试看以下示例,了解Oracle是如何利用物化临时表避免反复连接的:

SQL> alter session set star_transformation_enabled=true;
Session altered.

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

SELECT "T1"."C1" "CUST_CITY",
       "T"."CALENDAR_QUARTER_DESC" "CALENDAR_QUARTER_DESC",
       SUM("S"."AMOUNT_SOLD") "SALES_AMOUNT"
  FROM "SH"."SALES"                      "S",
       "SH"."TIMES"                      "T",
       "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" "T1"
 WHERE "S"."CUST_ID" = ANY (SELECT /*+ SEMIJOIN_DRIVER CACHE_TEMP_TABLE ("T1") */
         "T1"."C0" "C0"
          FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" "T1")
   AND "S"."CHANNEL_ID" = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         "CH"."CHANNEL_ID" "ITEM_1"
          FROM "SH"."CHANNELS" "CH"
         WHERE "CH"."CHANNEL_DESC" = 'Direct Sales')
   AND "S"."TIME_ID" = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         "T"."TIME_ID" "ITEM_1"
          FROM "SH"."TIMES" "T"
         WHERE "T"."CALENDAR_QUARTER_DESC" = '2000-01'
            OR "T"."CALENDAR_QUARTER_DESC" = '2000-02'
            OR "T"."CALENDAR_QUARTER_DESC" = '1999-12')
   AND "S"."TIME_ID" = "T"."TIME_ID"
   AND "S"."CUST_ID" = "T1"."C0"
   AND ("T"."CALENDAR_QUARTER_DESC" = '2000-01' OR
       "T"."CALENDAR_QUARTER_DESC" = '2000-02' OR
       "T"."CALENDAR_QUARTER_DESC" = '1999-12')
 GROUP BY "T1"."C1", "T"."CALENDAR_QUARTER_DESC"

以上为启用临时表后的星型转换后的查询语句,相应的执行计划如下:
---------------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                               | Name                     | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
---------------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                        |                          |       |       |   911 |           |       |       |
| 1   |  TEMP TABLE TRANSFORMATION              |                          |       |       |       |           |       |       |
| 2   |   LOAD AS SELECT                        |                          |       |       |       |           |       |       |
| 3   |    TABLE ACCESS FULL                    | CUSTOMERS                |  2438 |   62K |   406 |  00:00:05 |       |       |
| 4   |   HASH GROUP BY                         |                          |  1254 |   64K |   506 |  00:00:07 |       |       |
| 5   |    HASH JOIN                            |                          |  1254 |   64K |   479 |  00:00:06 |       |       |
| 6   |     HASH JOIN                           |                          |  1254 |   45K |   475 |  00:00:06 |       |       |
| 7   |      TABLE ACCESS FULL                  | TIMES                    |   227 |  3632 |    18 |  00:00:01 |       |       |
| 8   |      PARTITION RANGE SUBQUERY           |                          |  1254 |   26K |   456 |  00:00:06 | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 9   |       TABLE ACCESS BY LOCAL INDEX ROWID | SALES                    |  1254 |   26K |   456 |  00:00:06 | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 10  |        BITMAP CONVERSION TO ROWIDS      |                          |       |       |       |           |       |       |
| 11  |         BITMAP AND                      |                          |       |       |       |           |       |       |
| 12  |          BITMAP MERGE                   |                          |       |       |       |           |       |       |
| 13  |           BITMAP KEY ITERATION          |                          |       |       |       |           |       |       |
| 14  |            BUFFER SORT                  |                          |       |       |       |           |       |       |
| 15  |             TABLE ACCESS FULL           | CHANNELS                 |     1 |    13 |     3 |  00:00:01 |       |       |
| 16  |            BITMAP INDEX RANGE SCAN      | SALES_CHANNEL_BIX        |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 17  |          BITMAP MERGE                   |                          |       |       |       |           |       |       |
| 18  |           BITMAP KEY ITERATION          |                          |       |       |       |           |       |       |
| 19  |            BUFFER SORT                  |                          |       |       |       |           |       |       |
| 20  |             TABLE ACCESS FULL           | TIMES                    |   227 |  3632 |    18 |  00:00:01 |       |       |
| 21  |            BITMAP INDEX RANGE SCAN      | SALES_TIME_BIX           |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 22  |          BITMAP MERGE                   |                          |       |       |       |           |       |       |
| 23  |           BITMAP KEY ITERATION          |                          |       |       |       |           |       |       |
| 24  |            BUFFER SORT                  |                          |       |       |       |           |       |       |
| 25  |             TABLE ACCESS FULL           | SYS_TEMP_0FD9D660E_1DF5D6|  2438 |   12K |     4 |  00:00:01 |       |       |
| 26  |            BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX           |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 27  |     TABLE ACCESS FULL                   | SYS_TEMP_0FD9D660E_1DF5D6|  2438 |   36K |     4 |  00:00:01 |       |       |
---------------------------------------------------------------------------+-----------------------------------+---------------+

Predicate Information:
----------------------
3 - filter("C"."CUST_STATE_PROVINCE"='FL')
5 - access("S"."CUST_ID"="C0")
6 - access("S"."TIME_ID"="T"."TIME_ID")
7 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02'))
15 - filter("CH"."CHANNEL_DESC"='Direct Sales')
16 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
20 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02'))
21 - access("S"."TIME_ID"="T"."TIME_ID")
26 - access("S"."CUST_ID"="C0")

从以上trace中可以看到系统命名的临时表SYS_TEMP_0FD9D660E_1DF5D6缓存CUSTOMERS表,之后原先CUSTOMERS表被SYS_TEMP_0FD9D660E_1DF5D6所取代,原CUSTOMERS表上的cust_id和cust_city列均被替换为别名为T1的临时表的C0和C1列。实际上该临时表也仅需要这2列即可满足计划的需求,所以该临时表以如下查询语句填充:

ST: Subquery text:******* UNPARSED QUERY IS *******
SELECT "C"."CUST_ID" "ITEM_1","C"."CUST_CITY" "ITEM_2" FROM "SH"."CUSTOMERS" "C" WHERE "C"."CUST_STATE_PROVINCE"='FL'
Copy query block qb# -1 () : SELECT /*+ CACHE_TEMP_TABLE(T1) */  "C0" FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" T1
ST: Subquery (temp table) text:******* UNPARSED QUERY IS *******
SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "T1"."C0" "C0" FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" "T1"
Copy query block qb# -1 () : SELECT /*+ CACHE_TEMP_TABLE(T1) */  "C0", "C1" FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" T1
ST: Join back qbc text:******* UNPARSED QUERY IS *******
SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "T1"."C0" "C0","T1"."C1" "C1" FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" "T1"

可以从以上执行计划中看到第一、二、三行的”TEMP TABLE TRANSFORMATION LOAD AS SELECT TABLE ACCESS FULL CUSTOMERS”看到Oracle是如何将子查询物化为临时表的。在第25行,Oracle直接以该临时表替代了子查询来构建我们所需要的位图。到第27行Oracle直接利用该临时表来重复连接,避免再次扫描customers表。因为我们在构建临时表时已经使用谓词条件(如上面的红字语句),故而我们无需对临时表再次过滤。

如何启用星型查询

星型转换由初始化参数star_transformation_enabled控制,该参数可以有三种选项:

  • TRUE: Oracle优化器自动识别语句中的事实表和约束维度表并进行星型转换。这一切优化尝试都在CBO的藩篱内,优化器需要确定转换后的执行计划成本要低于不转换的执行计划;同时优化器还会尝试利用物化的临时表,如果那样真的好的话。
  • False: 优化器不会考虑星型转换。
  • TEMP_DISABLE:当一个维度表超过100个块时,”如果简单地设置star_transformation_enabled为TRUE来启用星型变换,那么会话会创建一个内存中的全局临时表(global temporary table)来保存已过滤的维度数据,这在过去会造成很多问题;”这里说的100个块其实是隐式参数_temp_tran_block_threshold(number of blocks for a dimension before we temp transform)的默认值,此外隐式参数_temp_tran_cache(determines if temp table is created with cache option,默认为TRUE)决定了这类临时表是否被缓存住;为了避免创建全局临时表可能带来的问题,就可以用到TEMP_DISABLE这个禁用临时表的选项,让优化器不再考虑使用物化的临时表。

默认该参数为False,若要问这是为什么?因为星型转换适用的场景是数据仓库环境中具有星型模型的模式,而且需要事实表的各个连接列上均有良好的索引时才能发挥其优势。如果能确定以上因素,那么我们可以放心的使用星型转换了,把star_transformation_enabled改为true或temp_disable吧!

总结

星型转换可以有效改善大的事实表与多个具有良好选择率的维度表间连接的查询。星型转换有效避免了全表扫描的性能窘境。它只fetch那些事实表上的”绝对”相关行。同时星型转换是基于CBO优化器的,Oracle能很好地认清使用该种转换是否有利。一旦维度表上的过滤无法有效减少需要从事实表上处理的数据集和时,那么可能全表扫描相对而言更为恰当。

以上我们力图通过一些简单的查询和执行计划来诠释星型转换的基本理念,但现实生产环境中实际的查询语句可能要复杂的多;举例而言如果查询涉及星型模型中的多个事实表的话,那么其复杂度就大幅提高了;如何正确构建事实表上的索引,收集相关列上的柱状图信息,在Oracle优化器无法正确判断的情况下循循善诱,都是大型数据仓库环境中DBA所面临的难题。

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给出的索引评估大小与实际索引占用空间差别不大 */