[20190527]注意表与索引的并行属性.txt

[20190527]注意表与索引的并行属性.txt --//今天检查生产系统,才发现自己建立的索引并行属性没有关闭.导致执行计划不是很合理. --//自己在工作中应该引起注意,建立完成马上关闭. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 SCOTT@test01p> create table tx as select * from all_objects ; Table created. SCOTT@test01p> create index pk_tx on tx (object_id) parallel ( degree 4 ); Index created. SCOTT@test01p> select count(object_id) from tx  ; COUNT(OBJECT_ID) ----------------            18603 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  b2wsdt3h61tzk, child number 0 ------------------------------------- select count(object_id) from tx Plan hash value: 3775749644 ------------------------------------------------------------------------------------------------------------ | Id  | Operation                 | Name     | E-Rows | Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT          |          |        |    14 (100)|          |        |      |            | |   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    |          |  18603 |    14   (0)| 00:00:01 |  Q1,00 | PCWC |            | |*  6 |       INDEX FAST FULL SCAN| PK_TX    |  18603 |    14   (0)| 00:00:01 |  Q1,00 | PCWP |            | ------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    6 - SEL$1 / TX@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    6 - access(:Z>=:Z AND :Z<=:Z) SCOTT@test01p> select  count(object_id) from tx  ; COUNT(OBJECT_ID) ----------------            18603 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  7axzt5nf4p3xc, child number 0 ------------------------------------- select  count(object_id) from tx Plan hash value: 893863067 ------------------------------------------------------------------------ | Id  | Operation             | Name  | E-Rows | Cost (%CPU)| E-Time   | ------------------------------------------------------------------------ |   0 | SELECT STATEMENT      |       |        |    14 (100)|          | |   1 |  SORT AGGREGATE       |       |      1 |            |          | |   2 |   INDEX FAST FULL SCAN| PK_TX |  18603 |    14   (0)| 00:00:01 | ------------------------------------------------------------------------