pl/sql developer 分析的執行計劃不可信

本來要測試一個功能的並行處理能力

select v_time,
round(WANGJIANYINGDA_NUM1 / WANGJIANYINGDA_NUM2, 2)
WANGJIANYINGDA_NUM1,
WANGJIANYINGDA_NUM2
from (SELECT /*+ PARALLEL(ZXCDR_ii, 4) */ SUBSTR(IAM_DATE, 1, 13) as v_time,
COUNT(case
when anm_date is null then
1
end) AS WANGJIANYINGDA_NUM1,
COUNT(1) AS WANGJIANYINGDA_NUM2
FROM ZXCDR_ii t
WHERE t.iam_date between
to_date('2009-04-08 10:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-04-08 19:00:00', 'yyyy-mm-dd hh24:mi:ss') and
OPC IN ('11-FF-19', '11-FF-42')
AND DPC IN ('11-0D-37', '11-27-45')
AND process_flg = 0
group by SUBSTR(IAM_DATE, 1, 13));

指定並行處理,

SELECT STATEMENT, GOAL = ALL_ROWS 7 7 1 65
SORT GROUP BY 7 7 1 65
SORT GROUP BY 7 7 1 65
PARTITION RANGE ALL
INLIST ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID HLHT ZXCDR_II 3 3 1 65
INDEX RANGE SCAN HLHT IDX_OPC 2 2 6659 "T"."OPC"='11-FF-19' OR "T"."OPC"='11-FF-42'

但結果總是不對.但自己檢查表的並行度,使用的索引idx_opc並行度,都沒有問題.

[@more@]

想不明白為什麼.

只好開啟sql_trace ,看看系統的內部處理,

透過分析oracle的trace結果,如下:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=65)
1 0 SORT* (GROUP BY) (Cost=7 Card=1 Bytes=65) :Q692001
2 1 SORT* (GROUP BY) (Cost=7 Card=1 Bytes=65) :Q692000
3 2 PARTITION RANGE* (ALL) :Q692000
4 3 INLIST ITERATOR* :Q692000
5 4 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'ZXCDR_II' (Cost=3 Card=1 Byte :Q692000
s=65)

6 5 INDEX* (RANGE SCAN) OF 'IDX_OPC' (NON-UNIQUE) (Cost=2 Card=6659) :Q692000

1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,COUNT(SYS_OP_CSR(
A1.C1,0)),COUNT(SYS_OP_CSR(A1.C1,1)) FROM :Q
692000 A1 GROUP BY A1.C0

2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ SUBSTR(A1.C3,1,13) C0,S
YS_OP_MSR(COUNT(*),COUNT(CASE WHEN A1.C4 IS
NULL THEN 1 END )) C1 FROM (SELECT /*+ NO_E
XPAND INDEX(A2 "IDX_OPC") */ A2.ROWID C0,A2.
"OPC" C1,A2."DPC" C2,A2."IAM_DATE" C3,A2."AN
M_DATE" C4,A2."PROCESS_FLG" C5 FROM "ZXCDR_I
I" PX_GRANULE(0, PARTITION, DYNAMIC) A2 WHE
RE (A2."OPC"='11-FF-19' OR A2."OPC"='11-FF-4
2') AND A2."IAM_DATE">=TO_DATE('2009-04-08 1
0:00:00', 'yyyy-mm-dd hh24:mi:ss') AND A2."I
AM_DATE"<=TO_DATE('2009-04-08 19:00:00', 'yy
yy-mm-dd hh24:mi:ss') AND (A2."DPC"='11-0D-3
7' OR A2."DPC"='11-27-45') AND A2."PROCESS_F
LG"=0) A1 GROUP BY SUBSTR(A1.C3,1,13)

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT

這才發現,實際上的執行結果,在資料庫端並沒有錯.而是

PL/SQL DEVELOPER工具顯示的有問題.