作者归档：惜分飞

sql profile改变hint的执行计划以及coe脚本实现sql id中无PLAN HASH VALUE的sql profile生成

发表于 2015 年 6 月 1 日 由 惜分飞

在sql profile中使用的过程中,有以下几个问题,这里通过测试确认了几个问题:
1.coe_xfr_sql_profile可以执行sql_id中无PLAN HASH VALUE的执行计划
2.在sql使用过程中,sql profile是否会覆盖hint,通过测试证明sqlprofile可以覆盖hint的执行计划
3.coe_load_sql_profile可以使用于通过修改hint(改变sql id,然后通过指定两次不同的sql id实现sql profile固定hint的sql的执行计划)

测试sqlprofile会影响hint

SQL> create table t_xifenfei as select object_id,object_name from user_objects;

Table created.

SQL> create index idx_t_xifenfei_id on t_xifenfei(OBJECT_ID);

Index created.

SQL> create index idx_t_xifenfei2_id on t_xifenfei(OBJECT_ID,1);

Index created.

---使用hint等方式确定三种方式执行计划
SQL> SET LINES 150
SQL> SET AUTOT  ON
SQL> SET PAGES 150
SQL> select * from t_xifenfei t where OBJECT_ID=10;    <---默认使用IDX_T_XIFENFEI_ID index

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 308895000

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

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

   2 - access("OBJECT_ID"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL> SET AUTOT OFF

SQL> SET LINES 150
SQL> SET AUTOT  ON
SQL> SET PAGES 150
SQL> select /*+ INDEX(T idx_t_xifenfei2_id)*/* from t_xifenfei t where OBJECT_ID=10;
  <---指定使用idx_t_xifenfei2_id index

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2143066642

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

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

   2 - access("OBJECT_ID"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL> SET AUTOT OFF

SQL> SET LINES 150
SQL> SET AUTOT  ON
SQL> SET PAGES 150
SQL> select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xifenfei t where OBJECT_ID=10;
  <---指定使用idx_t_xifenfei1_id index

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 308895000

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

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

   2 - access("OBJECT_ID"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL> SET AUTOT OFF

--查询三种情况下sql_id
SQL> col SQL_TEXT for a50
SQL> SELECT SQL_TEXT,SQL_ID FROM V$SQL WHERE sql_text like 'select%OBJECT_ID=10';

SQL_TEXT                                           SQL_ID
-------------------------------------------------- -------------
select /*+ INDEX(T idx_t_xifenfei2_id)*/* from t_x 5291sfrd2p35y
ifenfei t where OBJECT_ID=10

select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xi 143q33ff4f06w
fenfei t where OBJECT_ID=10

select * from t_xifenfei t where OBJECT_ID=10      b5zuac0zqm9nw

--使用sqlprofile固定其他两个未使用index IDX_T_XIFENFEI2_ID的sql使用该索引
SQL> DECLARE
  2   SQL_FTEXT CLOB;
  3   BEGIN
  4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'b5zuac0zqm9nw';
  5   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  6     SQL_TEXT => SQL_FTEXT,
  7     PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")'),
  8     NAME => 'PROFILE_b5zuac0zqm9nw',
  9     REPLACE => TRUE,
  10    FORCE_MATCH => TRUE
  11  );
 12   END;
 13   /

PL/SQL procedure successfully completed.

SQL> 
SQL> DECLARE
  2    SQL_FTEXT CLOB;
  3   BEGIN
  4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '143q33ff4f06w';
  5   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  6     SQL_TEXT => SQL_FTEXT,
  7     PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")'),
  8      NAME => 'PROFILE_143q33ff4f06w',
  9     REPLACE => TRUE,
 10     FORCE_MATCH => TRUE
 11   );
 12   END;
13   / 

PL/SQL procedure successfully completed.

--验证查询效果
SQL> SET LINES 150
SQL> SET AUTOT  ON
SQL> SET PAGES 150
SQL> select * from t_xifenfei t where OBJECT_ID=10;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2143066642

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

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

   2 - access("OBJECT_ID"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "PROFILE_b5zuac0zqm9nw" used for this statement  <--使用sql profile


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

SQL> select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xifenfei t where OBJECT_ID=10;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2143066642

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

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

   2 - access("OBJECT_ID"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "PROFILE_143q33ff4f06w" used for this statement 

<--使用sql profile,hint未被正常使用，证明sql profile影响hint，使得sql使用sql profile而不hint


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

这里可以知道,在有sqlprofile的情况下,可以影响以前的hint提示,使得hint无效，继续使用sql profile,感谢北京–weejar的试验证明

使用coe_load_sql_profile方式指定修改sql后的执行计划

SQL> @/tmp/coe_load_sql_profile.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: b5zuac0zqm9nw

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: 5291sfrd2p35y


     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
          2143066642                 .004

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 2143066642

Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "b5zuac0zqm9nw"
MODIFIED_SQL_ID: "5291sfrd2p35y"
PLAN_HASH_VALUE: "2143066642"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id.
       was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for modified SQL_ID &&modified_sql_id. and PHV &&plan_hash_value.
         was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>
SQL>SET ECHO OFF;
0001 BEGIN_OUTLINE_DATA
0002 IGNORE_OPTIM_EMBEDDED_HINTS
0003 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
0004 DB_VERSION('11.2.0.4')
0005 ALL_ROWS
0006 OUTLINE_LEAF(@"SEL$1")
0007 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")
0008 END_OUTLINE_DATA
dropping staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW"
creating staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW"
packaging new sql profile into staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW"

PROFILE_NAME
------------------------------
B5ZUAC0ZQM9NW_2143066642
SQL>REM
SQL>REM SQL Profile
SQL>REM ~~~~~~~~~~~
SQL>REM
SQL>SELECT signature, name, category, type, status
  2    FROM dba_sql_profiles WHERE name = :name;

           SIGNATURE NAME                           CATEGORY                       TYPE    STATUS
-------------------- ------------------------------ ------------------------------ ------- --------
 6715790053022671751 B5ZUAC0ZQM9NW_2143066642       DEFAULT                        MANUAL  ENABLED
SQL>SET ECHO OFF;

****************************************************************************
* Enter CHF password to export staging table STGTAB_SQLPROF_b5zuac0zqm9nw
****************************************************************************

Export: Release 11.2.0.4.0 - Production on Mon Jun 1 00:10:11 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table   STGTAB_SQLPROF_B5ZUAC0ZQM9NW          1 rows exported
Export terminated successfully without warnings.


If you need to implement this Custom SQL Profile on a similar system,
import and unpack using these commands:

imp CHF file=STGTAB_SQLPROF_b5zuac0zqm9nw.dmp tables=STGTAB_SQLPROF_b5zuac0zqm9nw ignore=Y

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => 'B5ZUAC0ZQM9NW_2143066642',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_b5zuac0zqm9nw',
staging_schema_owner => 'CHF' );
END;
/

updating: coe_load_sql_profile_b5zuac0zqm9nw.log (deflated 76%)
updating: STGTAB_SQLPROF_b5zuac0zqm9nw.dmp (deflated 89%)
  adding: coe_load_sql_profile.log (deflated 62%)

deleting: coe_load_sql_profile.log


coe_load_sql_profile completed.

SQL>SET LINES 150
SQL>SET AUTOT  ON
SQL>SET PAGES 150
select * from t_xifenfei t where OBJECT_ID=10;SQL>

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2143066642

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     6 |   474 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI         |     6 |   474 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI2_ID |     2 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=10)

Note
-----
   - SQL profile "B5ZUAC0ZQM9NW_2143066642" used for this statement   
<------sql直接使用coe_load_sql_profile固定执行计划成功


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

使用coe_load_sql_profile也可以sql_id中没有PLAN HASH VALUE的执行计划,另外还可以实现直接把sqlprofile直接迁移到其他库中

coe_xfr_sql_profile固定没有sql_id没有直接PLAN HASH VALUE的执行计划

SQL> @/tmp/coe_xfr_sql_profile

Parameter 1:
SQL_ID (required)

Enter value for 1: b5zuac0zqm9nw


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      308895000        .005

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2143066642     <---该PLAN_HASH_VALUE不存在该sql_id对应的PLAN_HASH_VALUE中

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "b5zuac0zqm9nw"
PLAN_HASH_VALUE: "2143066642"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not 
      found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value.
      was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql
on TARGET system in order to create a custom SQL Profile
with plan 2143066642 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql 11.4.4.4 2015/06/01 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM   carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM   coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM   This script is generated by coe_xfr_sql_profile.sql
SQL>REM   It contains the SQL*Plus commands to create a custom
SQL>REM   SQL Profile for SQL_ID b5zuac0zqm9nw based on plan hash
SQL>REM   value 2143066642.
SQL>REM   The custom SQL Profile to be created by this script
SQL>REM   will affect plans for SQL commands with signature
SQL>REM   matching the one for SQL Text below.
SQL>REM   Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM   None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM   SQL> START coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql;
SQL>REM
SQL>REM NOTES
SQL>REM   1. Should be run as SYSTEM or SYSDBA.
SQL>REM   2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM   3. SOURCE and TARGET systems can be the same or similar.
SQL>REM   4. To drop this custom SQL Profile after it has been created:
SQL>REM  EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_b5zuac0zqm9nw_2143066642');
SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM  for the Oracle Tuning Pack.
SQL>REM   6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM  Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM  By doing so you can create a custom SQL Profile for the original
SQL>REM  SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  PROCEDURE wa (p_line IN VARCHAR2) IS
  5  BEGIN
  6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
  7  END wa;
  8  BEGIN
  9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
 10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
 11  -- SQL Text pieces below do not have to be of same length.
 12  -- So if you edit SQL Text (i.e. removing temporary Hints),
 13  -- there is no need to edit or re-align unmodified pieces.
 14  wa(q'[select * from t_xifenfei t where OBJECT_ID=10]');
 15  DBMS_LOB.CLOSE(sql_txt);
 16  h := SYS.SQLPROF_ATTR(
 17  q'[BEGIN_OUTLINE_DATA]',
 18  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 19  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
 20  q'[DB_VERSION('11.2.0.4')]',
 21  q'[ALL_ROWS]',
 22  q'[OUTLINE_LEAF(@"SEL$1")]',
 23  q'[INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")]',
 24  q'[END_OUTLINE_DATA]');
 25  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 26  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
 27  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 28  sql_text    => sql_txt,
 29  profile     => h,
 30  name        => 'coe_b5zuac0zqm9nw_2143066642',
 31  description => 'coe b5zuac0zqm9nw 2143066642 '||:signature||' '||:signaturef||'',
 32  category    => 'DEFAULT',
 33  validate    => TRUE,
 34  replace     => TRUE,
 35  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). 
     FALSE:EXACT (similar to CURSOR_SHARING) */ );
 36  DBMS_LOB.FREETEMPORARY(sql_txt);
 37  END;
 38  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
  6715790053022671751


           SIGNATUREF
---------------------
   445801536248906164


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_b5zuac0zqm9nw_2143066642 completed
SQL>set autot on
SQL>set lines 150
SQL>set pages 150
SQL>select * from t_xifenfei t where OBJECT_ID=10;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2143066642

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     6 |   474 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI         |     6 |   474 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI2_ID |     2 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=10)

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


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

SQL>

通过验证，证明在sql_id中没有对应的PLAN HASH VALUE之时，也可以通过coe_xfr_sql_profile指定PLAN HASH VALUE来固定某个sql_id的执行计划.
以前写过相关关于sql profile的文章:sql profile 使用,执行计划改变导致数据库负载过高

发表在 Oracle性能优化 | 标签为 , , , | 评论关闭

ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified 恢复思路

发表于 2015 年 5 月 29 日 由 惜分飞

今天一网友找到我,说数据库恢复在推scn的过程中遇到了ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified错误无法解决,让我给予支持.这不禁让我想起,现在由于数据库bug和dblink原因导致了很多数据库scn很大,距离天花板非常近,从而使得数据库恢复过程中无法直接简单的推scn,这里正好结合该例子,简单说明下ORA-01052故障的处理.类似文档以前也写过:ORA-01052发生原因的类似文章

由于坏块导致数据库进行实例恢复无法进行

Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 1901 KB redo, 276 data blocks need recovery
Started redo application at
 Thread 1: logseq 1004, block 172771
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1004 Reading mem 0
  Mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG
Fri May 29 10:59:56 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19
Fri May 29 11:00:00 2015
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2048] [PC:0x6215134, __intel_new_memcpy()+260]
Fri May 29 11:00:12 2015
Trace dumping is performing id=[cdmp_20150529110012]
Fri May 29 11:00:12 2015
Slave exiting with ORA-1172 exception
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_p007_1612.trc:
ORA-01172: 线程 1 的恢复停止在块 439938 (在文件 19 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Fri May 29 11:00:27 2015
ORA-01578: ORACLE 数据块损坏 (文件号 19, 块号 450245)
ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF'
ORA-10564: tablespace PSTORE
ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 91642
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [_intel_new_memcpy()+260] [ACCESS_VIOLATION] [ADDR:0x2048] 
[PC:0x6215134] [UNABLE_TO_READ] []
Fri May 29 11:00:27 2015
Aborting crash recovery due to slave death, attempting serial crash recovery
RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19
Fri May 29 11:00:45 2015
Trace dumping is performing id=[cdmp_20150529110045]
Aborting crash recovery due to error 1172
ORA-1172 signalled during: alter database open...

设置_allow_resetlogs_corruption并resetlogs尝试打开数据库

Assigning activation ID 4272042346 (0xfea2316a)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 29 11:30:47 2015
SMON: enabling cache recovery
Fri May 29 11:30:47 2015
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc  (incident=181236):
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Incident details in: f:\app\administrator\diag\rdbms\XFF\XFF\incident\incdir_181236\XFF_ora_3004_i181236.trc
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc:
ORA-00704: ????????
ORA-00704: ????????
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc:
ORA-00704: ????????
ORA-00704: ????????
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 3004): terminating the instance due to error 704
Instance terminated by USER, pid = 3004
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (3004) as a result of ORA-1092

这里可以看到数据库通过设置_allow_resetlogs_corruption参数，进行不完全恢复,跳过数据库启动的实例恢复,然后强制拉库,然后遭遇大家熟悉的ORA-600[2662]错误,使得恢复失败,根据经验,通过推scn来绕过该错误

使用_minimum_giga_scn尝试推SCN

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
--------------------------------
*._minimum_giga_scn=13443
--------------------------------
 
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
 
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

通过运行Oracle Database Recovery Check检查发现数据库的scn已经非常大,距离天花板较近
_minimum_giga_scn
这里最大允许的推进的scn为13442.7,但是常规的最小的推scn的方法最小值为1024*1024*1024的倍数,因此这里遇到麻烦.
这里数据库遭遇了ORA-01052错误,导致推scn不成功,数据库无法正常启动.出现这类情况,由于scn可以增加的空间非常小,因此可以使用使用oradebug修改数据库scn或者直接修改控制文件scn的方式来精确控制推scn的值(可以实现任何值的scn增加,只要不超过天花板),也可以通过方法修改数据库scn距离天花板的距离,从而实现大幅度使用_minimum_giga_scn来推scn.另外还有一种解决方法:由于ORA-01052是由于scn过大导致(超过了数据库现在的天花板scn),因此出现了ORA-01052.所以另外一种变通的方法,就是通过调整数据库的天花板scn,从而使得_minimum_giga_scn可以继续推scn.在本次恢复中使用最为简单的增加天花板scn的方式来恢复(不过该方法恢复之后需要重建库,其实已经使用了隐含参数屏蔽redo恢复,本身就建议重建库保证数据字典一致性)

发表在 ORA-xxxxx, 非常规恢复 | 标签为 , , , , | 评论关闭

在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数

发表于 2015 年 5 月 27 日 由 惜分飞

在12C RAC的in memory测试中由于未正确启用并行,导致测试结果flush buffer cache后,总是出现大量并行，通过ORACLE 各方努力最终确定是由于parallel_degree_policy=AUTO和parallel_force_local=false开始未设置正确导致.在rac中需要imdb的朋友请注意这两个参数.

设置表存放中inmemory

 
SQL> alter table CHF.XIFENFEI_888 inmemory;

Table altered.

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;

  COUNT(*)          
----------          
  16883988          


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1642441725
                    
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 |  2566   (8)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M|  2566   (8)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M|  2566   (8)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
                    
Note                
-----               
   - automatic DOP: Computed Degree of Parallelism is 2  
   - parallel scans affinitized for inmemory      


Statistics
----------------------------------------------------------                      
        213  recursive calls                      
          0  db block gets 
     435058  consistent gets                      
         40  physical reads
      61180  redo size     
        545  bytes sent via SQL*Net to client     
        552  bytes received via SQL*Net from client      
          2  SQL*Net roundtrips to/from client    
          5  sorts (memory)
          0  sorts (disk)  
          1  rows processed

SQL> set autot off
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';

no rows selected

SQL>  select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';

INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------ 
      1 CHF   XIFENFEI_888          TABLE CHF_DATA            469827584   3571449856          2853101568 STARTED   NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 
      2 CHF   XIFENFEI_888          TABLE CHF_DATA            332267520   3571449856          3040182272 STARTED   NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 

SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';

INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID 
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------ 
      1 CHF   XIFENFEI_888          TABLE CHF_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 
      2 CHF   XIFENFEI_888          TABLE CHF_DATA           1068433408   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0

这里可以看到表加载到inmemory需要时间,不是全表扫描一遍之后里面全表载入到in memory中.

查看执行计划确实走inmemory

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;

  COUNT(*)          
----------          
  16883988          


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1642441725
                    
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
                    
Note                
-----               
   - automatic DOP: Computed Degree of Parallelism is 2  
   - parallel scans affinitized for inmemory      


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

flush buffer cache后,inmemory执行计划中出现大量物理读

SQL> set autot off
SQL> alter system flush buffer_cache;

System altered.

SQL> /

System altered.

SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';

INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID 
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------ 
      1 CHF   XIFENFEI_888          TABLE CHF_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 
      2 CHF   XIFENFEI_888          TABLE CHF_DATA           1068433408   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;

  COUNT(*)          
----------          
  16883988          


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1642441725
                    
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
                    
Note                
-----               
   - automatic DOP: Computed Degree of Parallelism is 2  
   - parallel scans affinitized for inmemory      


Statistics
----------------------------------------------------------                      
          6  recursive calls                      
          0  db block gets 
     177413  consistent gets                      
     176358  physical reads
      23456  redo size     
        545  bytes sent via SQL*Net to client     
        552  bytes received via SQL*Net from client      
          2  SQL*Net roundtrips to/from client    
          0  sorts (memory)
          0  sorts (disk)  
          1  rows processed

SQL> set autot off

再次查询物理读消失

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;

  COUNT(*)          
----------          
  16883988          


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1642441725
                    
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
                    
Note                
-----               
   - automatic DOP: Computed Degree of Parallelism is 2  
   - parallel scans affinitized for inmemory      


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

SQL> set autot off

这里有奇怪点,启用inmemory之后,flush buffer cache后,物理读非常大(基本上和逻辑读一样),不符合常理,因为inmemory和buffer cache是两个独立的东西,就算是flush buffer cache,也不应该导致in memory内的东西失效(而且从v$im_segments中查询是正常的),对于该问题百思不得其解,最后只好寻求inmemory邮件组和GCS帮忙.最终是由于并行相关参数配置导致该问题

SQL> alter system set parallel_force_local=false sid='*';

System altered.

SQL> alter system set parallel_degree_policy=AUTO sid='*'; 

System altered.

修改parallel_force_local和parallel_degree_policy后继续测试

SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888'; 

INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
      1 CHF   XIFENFEI_888          TABLE RPT_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
      2 CHF   XIFENFEI_888          TABLE RPT_DATA           1069481984   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0


SQL> set autot on 
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t; 
set autot off 


  COUNT(*)
----------
  16883988


Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory


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


SQL> alter system flush buffer_cache
  2  ;

System altered.

SQL> /

System altered.

SQL> 

SQL> set autot on 
select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t; 
set autot off 
SQL> 

  COUNT(*)
----------
  16883988


Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory


Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
        776  consistent gets
          2  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> spool off

通过测试证明,在RAC环境中,如果要使用IN MEMORY特性,需要设置parallel_degree_policy=AUTO和parallel_force_local=false之后,才能够真正意义上的启动IM特性,不然只是执行计划中的启用,可能是假象

发表在 ORACLE 12C | 标签为 , , , | 评论关闭