[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 |
------------------------------------------------------------------------