object_id和data_object_id区别与联系

Posted on 2011 年 09 月 12 日 by 惜分飞

其实object_id和data_object_id同样是表示数据库对象的一个唯一标志，但是object_id表示的是逻辑id，data_object_id表示的是物理id。如果一些object没有物理属性的话那它就不存在data_object_id，例如procedure,function,package,data type,db link,mv定义,view定义,临时表,分区表定义等等这些object都是没有对应着某个segment，因此它们的data_object_id都为空。

当表刚创建的时候它的object_id和data_object_id都是相等的，但是如果表经过move或truncate等，涉及到segment发生改变后data_object_id将会有变化。

DATA_OBJECT_ID was introduced in 8.0 to track versions of the same segment (certain operations change the version). It is used to discover stale ROWIDs and stale undo records.

SQL> create table xff as select * from dba_objects where rownum<100;
 
Table created
 
SQL> select object_id,data_object_id from user_objects where object_name='XFF';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    211325         211325
 
SQL> alter table xff move;
 
Table altered
 
SQL> select object_id,data_object_id from user_objects where object_name='XFF';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    211325         211326
 
SQL> truncate table xff;
 
Table truncated
 
SQL> create index ind_xff on xff(object_id);
 
Index created
 
SQL>  select object_id,data_object_id from user_objects where object_name='IND_XFF';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    211328         211328
 
SQL> ALTER INDEX IND_XFF REBUILD;
 
Index altered
 
SQL>  select object_id,data_object_id from user_objects where object_name='IND_XFF';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    211328         211329

Oracle 传输表空间

Posted on 2011 年 09 月 11 日 by 惜分飞

0、检查平台信息
所有tts支持平台
SELECT * FROM V$TRANSPORTABLE_PLATFORM;

当前系统平台情况
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

一、源端操作
检查是否符合TTS要求
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘ODU’, TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME=’ODU’;

COUNT(*)
———-
59

SQL> SELECT file_name from dba_data_files where tablespace_name=’ODU’;

FILE_NAME
————————————————–
/opt/oracle/oradata/chf/odu01.dbf
/opt/oracle/oradata/chf/odu02.dbf

需要传输表空间至于readonly模式
SQL> ALTER TABLESPACE ODU READ ONLY;

Tablespace altered.

导出表空间元数据
[oracle@node1 ~]$ exp userid=\’/ as sysdba\’ tablespaces=ODU file=/tmp/ODU.dmp transport_tablespace=y

Export: Release 10.2.0.4.0 – Production on Sun Sep 11 10:01:52 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata…
For tablespace ODU …
. exporting cluster definitions
. exporting table definitions
. . exporting table T_ODU_03
. . exporting table T_ODU_01
. . exporting table T_ODU
. . exporting table DB
. . exporting table NODE
. . exporting table CONF
. . exporting table DBINC
. . exporting table CKP
. . exporting table TS
. . exporting table TSATT
. . exporting table DF
. . exporting table DFATT
. . exporting table TF
. . exporting table TFATT
. . exporting table OFFR
. . exporting table RR
. . exporting table RT
. . exporting table ORL
. . exporting table RLH
. . exporting table AL
. . exporting table BS
. . exporting table BP
. . exporting table BCF
. . exporting table CCF
. . exporting table XCF
. . exporting table BSF
. . exporting table BDF
. . exporting table CDF
. . exporting table XDF
. . exporting table BRL
. . exporting table BCB
. . exporting table CCB
. . exporting table SCR
. . exporting table SCRL
. . exporting table CONFIG
. . exporting table XAL
. . exporting table RSR
. . exporting table FB
. . exporting table GRSP
. . exporting table ROUT
. . exporting table RCVER
. . exporting table F_DROP
. . exporting table T_QUERY
. . exporting table T_UNDO
. . exporting table A
. . exporting table T1
. . exporting table T2_1
. . exporting table T2
. . exporting table T_MV
. . exporting table TAB2
. . exporting table MLOG$_T_MV
. . exporting table T_N
. . exporting table T_M
. . exporting table MLOG$_T_N
. . exporting table T_1
. . exporting table T_2
. . exporting table T_3
. . exporting table T_4
. . exporting table T_5
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

SQL> alter tablespace odu read write;

Tablespace altered.

传输到目标段
[oracle@node1 ~]$ scp /opt/oracle/oradata/chf/odu0* 192.168.11.12:/opt/oracle/oradata/test
The authenticity of host ‘192.168.11.12 (192.168.11.12)’ can’t be established.
RSA key fingerprint is db:3c:b4:34:7f:d7:e4:97:ab:b6:8b:b0:ab:22:43:35.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.11.12’ (RSA) to the list of known hosts.
oracle@192.168.11.12’s password:
odu01.dbf 100% 100MB 3.3MB/s 00:30
odu02.dbf 100% 11GB 2.8MB/s 1:05:00
[oracle@node1 ~]$ scp /tmp/ODU.dmp 192.168.11.12:/tmp
oracle@192.168.11.12’s password:
Permission denied, please try again.
oracle@192.168.11.12’s password:
ODU.dmp 100% 456KB 456.0KB/s 00:00

二、目标端操作
导入元数据库
[oracle@ECP-UC-DB1 ~]$ imp userid=\’/ as sysdba\’ tablespaces=ODU file=/tmp/ODU.dmp transport_tablespace=y datafiles=/opt/oracle/oradata/test/odu01.dbf, /opt/oracle/oradata/test/odu02.dbf fromuser=chf touser=chf

Import: Release 10.2.0.4.0 – Production on Sun Sep 11 11:13:25 2011

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

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata…
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF’s objects into CHF
. . importing table “T_ODU_03”
. . importing table “T_ODU_01”
. . importing table “T_ODU”
. . importing table “DB”
. . importing table “NODE”
. . importing table “CONF”
. . importing table “DBINC”
. . importing table “CKP”
. . importing table “TS”
. . importing table “TSATT”
. . importing table “DF”
. . importing table “DFATT”
. . importing table “TF”
. . importing table “TFATT”
. . importing table “OFFR”
. . importing table “RR”
. . importing table “RT”
. . importing table “ORL”
. . importing table “RLH”
. . importing table “AL”
. . importing table “BS”
. . importing table “BP”
. . importing table “BCF”
. . importing table “CCF”
. . importing table “XCF”
. . importing table “BSF”
. . importing table “BDF”
. . importing table “CDF”
. . importing table “XDF”
. . importing table “BRL”
. . importing table “BCB”
. . importing table “CCB”
. . importing table “SCR”
. . importing table “SCRL”
. . importing table “CONFIG”
. . importing table “XAL”
. . importing table “RSR”
. . importing table “FB”
. . importing table “GRSP”
. . importing table “ROUT”
. . importing table “RCVER”
. . importing table “F_DROP”
. . importing table “T_QUERY”
. . importing table “T_UNDO”
. . importing table “A”
. . importing table “T1”
. . importing table “T2_1”
. . importing table “T2”
. . importing table “T_MV”
. . importing table “TAB2”
. . importing table “MLOG$_T_MV”
. . importing table “T_N”
. . importing table “T_M”
. . importing table “MLOG$_T_N”
. . importing table “T_1”
. . importing table “T_2”
. . importing table “T_3”
. . importing table “T_4”
. . importing table “T_5”
About to enable constraints…
Import terminated successfully without warnings.

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS
—————————— ———
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
XFF ONLINE
ODU READ ONLY

7 rows selected.

修改为readwrite模式（根据需求）
SQL> alter tablespace odu read write;

Tablespace altered.

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME=’ODU’;

COUNT(*)
———-
59

三、相关说明
1、如果平台字节顺序不同，需要使用rman convert转换
2、导出导入元数据可以使用data pump实现
3、检查视图、触发器、包、过程、函数等对象，如果没有需要使用exp/imp row=n导入或者人工建立

Oracle DDL 并行(10G)

Posted on 2011 年 09 月 09 日 by 惜分飞

1、CREATE INDEX 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR create index ind_t2 on t1(object_name) online nologging PARALLEL(degree 4);

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;

Session altered.

SQL> EXPLAIN PLAN FOR create index ind_t2 on t1(object_name) online nologging PARALLEL(degree 4);

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

2、REBUILD INDEX 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR alter  index ind_t2 rebuild online nologging PARALLEL(degree 4);

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT    |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;

Session altered.

SQL> EXPLAIN PLAN FOR alter  index ind_t2 rebuild online nologging PARALLEL(degree 4);

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT    |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

3、CREATE TABLE 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR create table t_6 parallel (degree 4)
  2    as select /*+ parallel (t1,4) */ * from t1 where rownum<5000000;

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2102891290

------------------------------------------------------------------------------
| Id  | Operation                    | Name     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |          |        |      |            |
|   1 |  PX COORDINATOR              |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ20001 |  Q2,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT            | T_6      |  Q2,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |  Q2,01 | PCWC |            |
|   5 |      PX RECEIVE              |          |  Q2,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN    | :TQ20000 |        | S->P | RND-ROBIN  |
|   7 |        COUNT STOPKEY         |          |        |      |            |
|   8 |         PX COORDINATOR       |          |        |      |            |
|   9 |          PX SEND QC (RANDOM) | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|  10 |           COUNT STOPKEY      |          |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;

Session altered.

SQL> EXPLAIN PLAN FOR create table t_6 parallel (degree 4)
  2    as select /*+ parallel (t1,4) */ * from t1 where rownum<5000000;

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2102891290

------------------------------------------------------------------------------
| Id  | Operation                    | Name     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |          |        |      |            |
|   1 |  PX COORDINATOR              |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ20001 |  Q2,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT            | T_6      |  Q2,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |  Q2,01 | PCWC |            |
|   5 |      PX RECEIVE              |          |  Q2,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN    | :TQ20000 |        | S->P | RND-ROBIN  |
|   7 |        COUNT STOPKEY         |          |        |      |            |
|   8 |         PX COORDINATOR       |          |        |      |            |
|   9 |          PX SEND QC (RANDOM) | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|  10 |           COUNT STOPKEY      |          |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------

4、说明
1）本次测试的数据库版本为10.2.0.4，Linux环境，其他版本可能有差异
2）关于INDEX的并行操作，并行度可能不会和指定相同（测试为2倍）
3）操作过程中，是否指定ddl 并发，效果相同。建议指定未佳：
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL DDL;
4）修改表结构操作，指定并发无效（待寻找方法）

Oracle DML并行

Posted on 2011 年 09 月 09 日 by 惜分飞

1、UPDATE 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR update /*+ parallel (t1,4) */ t1 set object_name='chengfei';

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 121765358

-----------------------------------------------------------------------
| Id  | Operation             | Name     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |        |      |            |
|   1 |  UPDATE               | T1       |        |      |            |
|   2 |   PX COORDINATOR      |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE  PARALLEL DML;

Session altered.

SQL> EXPLAIN PLAN FOR update /*+ parallel (t1,4) */ t1 set object_name='chengfei';

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3308547044

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | UPDATE STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE     | T1       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   6 |       UPDATE             | T1       |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

通过执行计划可以看出，只有执行了ALTER SESSION ENABLE PARALLEL DML后，UPDATE操作才真正的实现了并行操作，如果不执行该语句，只是执行了并发查询，并没有实现并发更新操作

2、DELETE 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR delete /*+ parallel (t1,3) */ from t1;

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3718066193

-----------------------------------------------------------------------
| Id  | Operation             | Name     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
|   0 | DELETE STATEMENT      |          |        |      |            |
|   1 |  DELETE               | T1       |        |      |            |
|   2 |   PX COORDINATOR      |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE  PARALLEL DML;

Session altered.

SQL> EXPLAIN PLAN FOR delete /*+ parallel (t1,3) */ from t1;

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2132458150

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | DELETE STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE     | T1       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   6 |       DELETE             | T1       |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

试验证明，也需要执行ALTER SESSION ENABLE PARALLEL DML，才能够实现真正的删除并发操作

3、INSERT 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR INSERT  /*+ parallel (t_1,4) */ INTO t_1 SELECT /*+ parallel (t1,4) */* FROM t1;

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2494645258

----------------------------------------------------------------------
| Id  | Operation            | Name     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------
|   0 | INSERT STATEMENT     |          |        |      |            |
|   1 |  PX COORDINATOR      |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
----------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE  PARALLEL DML;

Session altered.

SQL> EXPLAIN PLAN FOR INSERT  /*+ parallel (t_1,4) */ INTO t_1 SELECT /*+ parallel (t1,4) */* FROM t1;

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 783041698

-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       | T_1      |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 |  Q1,00 | P->P | RND-ROBIN  |
|   6 |       PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

通过这个执行计划可以发现，数据是被使用APPEND方式插入到表中，如果需要常规方式插入，需要加上noappend提示，同样设置了session 并行dml才能够实现真正意义上的插入并发操作

4、总结
通过上面的试验可以得出，如果要DML实现真正意义上的并发，在开始执行需要并发语句前，需要执行开启session并发
ALTER SESSION ENABLE PARALLEL DML;
在执行完语句后，需要执行关闭session并发
ALTER SESSION DISABLE PARALLEL DML;

Oracle直方图理解与实验

Posted on 2011 年 09 月 04 日 by 惜分飞

一.Oracle中直方图的作用
直方图是一种对被管理对象某一方面质量进行管理的描述工具，那么在Oracle中自然它也是对Oracle中某个对象质量的描述工具，这个对象就是Oracle中最重要的东西——“数据”。
在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。

二.Oracle中使用直方图的场合
在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。
通常情况下在以下场合中建议使用直方图：
（1）、当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于 WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）

（2）、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。

三.Oracle直方图的种类
Oracle利用直方图来提高非均匀数据分布的选择率和技术的计算精度。但是实际上Oracle会采用另种不同的策略来生成直方图：其中一种是针对包含很少不同值的数据集；另一种是针对包含很多不同的数据集。Oracle会针对第一种情况生成频率直方图，针对第二种情况生成高度均衡直方图。通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而当BUCTET > 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图

四、试验证明（有直方图执行计划更加准确）

SQL> create table t_xff
  2  as select * from dba_objects;
 
Table created
 
 
SQL> create index ind_t_xff on t_xff(object_id) online nologging;
 
Index created

SQL> SELECT MAX(object_id),MIN(object_id) FROM t_xff;
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         76800              2

SQL>  UPDATE t_xff SET object_id=1000 WHERE object_id>100 AND object_id<76000;
 
72965 rows updated
 
SQL> commit;
 
Commit complete
 
SQL> 
SQL>   BEGIN
  2      DBMS_stats.gather_table_stats(cascade => TRUE,degree => 2
  3      ,estimate_percent => 100,force => TRUE,ownname => USER,tabname => 'T_XFF');
  4   END;
  5  /
 
PL/SQL procedure successfully completed

SQL> SELECT * FROM user_histograms WHERE table_name='T_XFF' AND column_name='OBJECT_ID';
TABLE COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
----- ---------- --------------- -------------- ----------
T_XFF OBJECT_ID            1          2
T_XFF OBJECT_ID            2          3
……
T_XFF OBJECT_ID            73205          76789
T_XFF OBJECT_ID            73206          76800

SQL>   SELECT COLUMN_NAME,HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME='T_XFF' AND column_name='OBJECT_ID';
 
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      FREQUENCY
--在gather_table_stats方法中，默认的method_opt值为：FOR ALL COLUMNS SIZE AUTO，所以也是会收集直方图的统计信息（和oracle版本相关）
--注意：ENDPOINT_NUMBER ，ENDPOINT_VALUE 的分布情况

SQL> set autot trace exp stat
SQL> select object_name from t_xff where object_id=100;


执行计划
----------------------------------------------------------
Plan hash value: 2950241517

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

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

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

|   0 | SELECT STATEMENT            |           |     1 |    29 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_XFF     |     1 |    29 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T_XFF |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"=100)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        432  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select object_name from t_xff where object_id=1000;

已选择72965行。


执行计划
----------------------------------------------------------
Plan hash value: 667573674

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 72965 |  2066K|   292   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T_XFF | 72965 |  2066K|   292   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=1000)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5833  consistent gets
         16  physical reads
          0  redo size
    2487154  bytes sent via SQL*Net to client
      53920  bytes received via SQL*Net from client
       4866  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72965  rows processed
--观察发现，因为有直方图的存在，oracle会只能的选择使用index或者全表扫描


SQL>   BEGIN
  2      DBMS_stats.gather_table_stats(cascade => TRUE,degree => 2
  3      ,estimate_percent => 100,force => TRUE,ownname => USER,tabname => 'T_XFF',method_opt => 'FOR ALL COLUMNS SIZE 1');
  4   END;
  5  /
 
PL/SQL procedure successfully completed
--删除直方图，设置method_opt：FOR ALL COLUMNS SIZE 1即可

SQL>  SELECT * FROM user_histograms WHERE table_name='T_XFF' AND column_name='OBJECT_ID';

TABLE COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
----- ---------- --------------- -------------- ----------
T_XFF OBJECT_ID                0              2
T_XFF OBJECT_ID                1          76800

SQL> SELECT COLUMN_NAME,HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME='T_XFF' AND column_name='OBJECT_ID';
 
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      NONE

SQL> select object_name from t_xff where object_id=100;


执行计划
----------------------------------------------------------
Plan hash value: 2950241517

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

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

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

|   0 | SELECT STATEMENT            |           |   303 |  8787 |     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_XFF     |   303 |  8787 |     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T_XFF |   303 |       |     2   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"=100)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        432  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select object_name from t_xff where object_id=1000;

已选择72965行。


执行计划
----------------------------------------------------------
Plan hash value: 2950241517

--------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |           |   303 |  8787 |     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_XFF     |   303 |  8787 |     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T_XFF |   303 |       |     2   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"=1000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5833  consistent gets
          0  physical reads
          0  redo size
    2487154  bytes sent via SQL*Net to client
      53919  bytes received via SQL*Net from client
       4866  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72965  rows processed
--没有了直方图，oracle傻瓜的选择也使用index
--虽然两次逻辑读一样，但是全表扫描涉及到一次可以读多块，但是index扫描一次只能读一个数据块