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

最新推荐文章于 2023-06-08 16:32:35 发布

转载最新推荐文章于 2023-06-08 16:32:35 发布 · 136 阅读

博主检查生产系统时发现建立的索引并行属性未关闭，导致执行计划不合理。文中展示了Oracle数据库环境信息，创建表和索引的操作，以及不同查询下的执行计划。提醒在工作中建立索引完成后应马上关闭并行属性。

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