Search Results for: cost

Extract SQL Plan from AWR

之前有用户在AWR中定位到问题SQL语句后想要了解该SQL statement的具体执行计划,于是就用AWR报告中得到的SQL ID去V$SQL等几个动态性能视图中查询,但发现V$SQL或V$SQL_PLAN视图都已经找不到对应SQL ID的记录,一般来说这些语句已经从shared pool共享池中被替换出去了。

这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Oracle Automatic Workload Repository自动负载仓库中记录的SQL语句相关信息抽取出来,如:

SQL> set linesize 200 pagesize 2000;

SQL> select * from global_name;

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

SQL> select /* extract_me */ count(*) from maclean;

  COUNT(*)
----------
      9564

SQL> select sql_id from v$sql where sql_text like '%extract_me%' and sql_text not like '%like%';

SQL_ID
-------------
8vff23q8qp9fj

SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

QL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8vff23q8qp9fj, child number 0
-------------------------------------
select /* extract_me */ count(*) from maclean

Plan hash value: 1679547536

----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

18 rows selected.

/* 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,
     需要注意的是如果在AWR快照自动生成之前冲刷了共享池,那么可能丢失SQL的执行统计信息
 */

SQL> alter system flush shared_pool;
System altered.

SQL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID: 8vff23q8qp9fj, child number: 0 cannot be found

/* 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划 */

SQL>  select * from table(dbms_xplan.display_awr('8vff23q8qp9fj'));

PLAN_TABLE_OUTPUT
--------------------------------------------------
SQL_ID 8vff23q8qp9fj
--------------------
select /* extract_me */ count(*) from maclean

Plan hash value: 1679547536

----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

18 rows selected.

/* 这里可以代入'all'选项获取更细致的计划信息 */

SQL> select * from table(dbms_xplan.display_awr('8vff23q8qp9fj',null,null,'all'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 8vff23q8qp9fj
--------------------
select /* extract_me */ count(*) from maclean

Plan hash value: 1679547536

----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / MACLEAN@SEL$1

Note
-----
   - dynamic sampling used for this statement

24 rows selected.

/* 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句 */

SQL> select OPERATION,OPTIONS,OBJECT_NAME from dba_hist_sql_plan where sql_id='8vff23q8qp9fj';

OPERATION                      OPTIONS                        OBJECT_NAME
------------------------------ ------------------------------ -------------------------------
SELECT STATEMENT
SORT                           AGGREGATE
TABLE ACCESS                   FULL                           MACLEAN

【11g新特性】SPM SQL PLAN MANAGEMENT执行计划管理流程图

Oracle SPM SQL PLAN MANAGEMENT执行计划管理流程图如下:

 

 

Plan Baseline Capture - Automatic Plan Baseline Selection1 Plan Baseline Evolution1 SQL Management Base (SMB)

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

Table created.

SQL> analyze table mac_spm compute statistics;

Table analyzed.

SQL>    alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> 
SQL>    select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

SQL>    select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

	alter session set optimizer_capture_sql_plan_baselines=false;

	SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace on;
SQL>  select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

Execution Plan
----------------------------------------------------------
Plan hash value: 874020942

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     4 |   301   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |         |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| MAC_SPM | 75535 |   295K|   301   (1)| 00:00:04 |
------------------------------------------------------------------------------

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

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

SQL> create index ind_objd on mac_spm(object_id);

Index created.

oradebug setmypid		  
oradebug tracefile_name

SQL> alter system flush shared_pool;

System altered.

alter system set events 'trace[SQL_Plan_Management] disk highest';

select sum(object_id) from mac_spm;

*** 2013-04-11 09:28:49.628
SPM: statement found in SMB
SPM: planId's of plan baseline are: 3970369213
SPM: using qksan to reproduce, cost and select accepted plan, sig = 14462506969095103776
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3970369213
SPM: planId in plan baseline = 3970369213, planId of reproduced plan = 3970369213
SPM: best cost so far = 301.13, current accepted plan cost = 301.13
SPM: re-parse to use selected accepted plan, planId = 3970369213
SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan,  planId = 3970369213
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f8e30491298, pmExCtx=0xc7463800, ciP=0xc99f7e78, dtCtx=0xbaf7c20

SQL> select  /*+ index( MAC_SPM ind_objd) */ sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

Execution Plan
----------------------------------------------------------
Plan hash value: 45369511

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |   169   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE  |          |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| IND_OBJD | 75535 |   295K|   169   (1)| 00:00:03 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        168  consistent gets
        168  physical reads
          0  redo size
        536  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)
          1  rows processed
SQL> insert into mac_spm select * from mac_spm;

75535 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'MAC_SPM');

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> oradebug setmypid
Statement processed.
SQL> select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    5822911440

SQL> oradebug tracefile_name
/s01/diag/rdbms/prodb/PRODB/trace/PRODB_ora_11126.trc

*** 2013-04-11 09:45:24.248
SPM: statement found in SMB
SPM: planId's of plan baseline are: 3970369213
SPM: using qksan to reproduce, cost and select accepted plan, sig = 14462506969095103776
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3970369213
SPM: planId in plan baseline = 3970369213, planId of reproduced plan = 3970369213
SPM: best cost so far = 623.28, current accepted plan cost = 623.28
SPM: re-parse to use selected accepted plan, planId = 3970369213
SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan,  planId = 3970369213
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f6066c949f8, pmExCtx=0xc8876e48, ciP=0xc86784c0, dtCtx=0xbaf7c20

 

 

 

 

qksan是负责SQL ANALYZE 的函数,如果看到 类似如下的信息 则说明 存在过 SQL ANALYZE

sql=/* SQL Analyze(168,0) */ select sum(object_id) from mac_spm
End parsing of cur#=4 sqlid=72ph25kpkkqhs
Semantic Analysis cur#=4 sqlid=72ph25kpkkqhs

Utilize Sql Tuning Advisor from Script

Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具,大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor;但如果系统中没有配置dbconsole或者Grid Control的话,我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本,具体脚本:


begin
  DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/



DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  my_sqlid varchar2(30);
BEGIN
  my_sqlid := '&sqlid';
  my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
                                                  scope => 'COMPREHENSIVE',
                                                  time_limit => 300,
                                                  task_name => '&task_name',
                                                  description => 'comment'                               
                                                  );
END;
/


BEGIN
  dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';

SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
set pages 60

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;

具体使用示例:

SQL> begin
  DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/
Enter value for task_name: newtask
old   3:   DBMS_SQLTUNE.drop_tuning_task('&task_name');
new   3:   DBMS_SQLTUNE.drop_tuning_task('newtask');
begin
*
ERROR at line 1:
ORA-13605: The specified task or object newtask does not exist for the current
user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2307
ORA-06512: at "SYS.DBMS_ADVISOR", line 172
ORA-06512: at "SYS.DBMS_SQLTUNE", line 751
ORA-06512: at line 3

SQL> DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  my_sqlid varchar2(30);
BEGIN
  my_sqlid := '&sqlid';
  my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
                                                  scope => 'COMPREHENSIVE',
                                                  time_limit => 300,
                                                  task_name => '&task_name',
                                                  description => 'comment'                               
                                                  );
END;
/ 
Enter value for sqlid: 17usubxchdf2w
old  11:   my_sqlid := '&sqlid';
new  11:   my_sqlid := '17usubxchdf2w';
Enter value for task_name: new_task 
old  19:						   task_name => '&task_name',
new  19:						   task_name => 'new_task',

SQL> BEGIN
  dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/  
Enter value for task_name: new_task
old   3:   dbms_sqltune.execute_tuning_task(task_name => '&task_name');
new   3:   dbms_sqltune.execute_tuning_task(task_name => 'new_task');

PL/SQL procedure successfully completed.

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';
Enter value for task_name: new_task
old   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name'
new   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'new_task'

STATUS
-----------
COMPLETED

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;
Enter value for task_name: new_task
old   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL
new   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('new_task') FROM DUAL

DBMS_SQLTUNE.REPORT_TUNING_TASK('NEW_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : new_task
Tuning Task Owner  : SYS
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at	   : 03/25/2011 00:14:41
Completed at	   : 03/25/2011 00:14:45

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID	   : 17usubxchdf2w
SQL Text   : select count(t1) from hashtab

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

1- Statistics Finding
---------------------
  Table "SYS"."HASHTAB" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
	    'HASHTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
	    method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

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

1- Original
-----------
Plan hash value: 485915006

------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     |	   1 |	  13 |	   2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	     |	   1 |	  13 |		  |	     |
|   2 |   TABLE ACCESS FULL| HASHTAB |	 102 |	1326 |	   2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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