with as 和 绑定变量搭配的疑问

雪影舞剑

这是一例某环境由9.2.0.8升级到11.2.0.3产生的sql效率下降的问题
sql语句为单条update，内嵌三层WITH AS，使用绑定变量
1、带绑定变量的sql测试超过四小时没跑出来
2、将绑定变量:B1改为直接传值to_date('201212', 'yyyymm')的sql都在1分钟左右跑出
3、以上两种情况的sql使用了相同的执行计划
相关sql文本及trace文件请看附件
请各位大侠帮忙分析原因，谢谢！

Maclean Liu(刘相兵

貌似2个TRACE 都是11.2.0.3的？

雪影舞剑

对的，忘了交代，都是在11g平台上测试的

雪影舞剑

区别在于绑定变量与否

26856649

这种语句是否本就不该绑定变量？

雪影舞剑

其实这是pkg中的一部分，绑定变量是传参自动生成的，如下所示:

PKG_DA_RPT_IND_1_12
is
PROCEDURE DO_IND_ORGAN_AGENT_DESC(p_stat_date IN DATE, ---执行时间
p_work_flag IN NUMBER ---执行标志(0:补跑 1:正常执行)
) IS
v_begin_time DATE; ---系统时间
v_calc_date  date; ---操作时间
v_start_time DATE; --统计开始时间
v_end_time   DATE; --统计结束时间               
BEGIN
...
update DA_RPT_IND_ORGAN_ACCEPT_MONTH d  ...
  and nvl(t.end_date, date '2050-12-01') > v_end_time),        ...       
    and nvl(t.end_date, date '2050-12-01') > v_end_time) organ_id_3 ...

雪影舞剑

v_start_time := trunc(v_calc_date, 'mm'); --月初

雪影舞剑

v_end_time   := add_months(v_start_time, 1); --月末
这是pkg中的赋值，从传参来的

雪影舞剑

v_calc_date  := p_stat_date - 1;

Maclean Liu(刘相兵

我看到的trace1:


SQL ID: a6c2mtx4ag89j Plan Hash: 0

UPDATE DA_RPT_IND_ORGAN_ACCEPT_MONTH D SET (D.TURN_MONTH3, D.AT3_MONTH,
  D.AT6_MONTH, D.AT13_MONTH, D.AT6_NEW_MONTH, D.AT13_NEW_MONTH) = (SELECT
  TEMP.TURN_MONTH3, TEMP.AT3_MONTH,TEMP.AT6_MONTH, TEMP.AT13_MONTH,
  TEMP.AT6_NEW_MONTH, TEMP.AT13_NEW_MONTH FROM ( WITH ORGAN_TEMP AS (SELECT
  DISTINCT DA.YEAR_MONTH YEAR_MONTH, DA.ATTACH_ORGAN_ID ORGAN_ID_5,
  NVL((SELECT DISTINCT T.PARENT_ORGAN FROM DA_RPT_IND_ORGAN_EXT_HISTORY T
WHERE
T.ORGAN_ID = DA.ATTACH_ORGAN_ID AND T.START_DATE <= :B1 AND NVL(T.END_DATE,
  DATE '2050-12-01') > :B1 ), (SELECT DISTINCT T.ORGAN_4 FROM T_RPT_ORGAN_EXT
  T WHERE T.ORGAN_ID = DA.ATTACH_ORGAN_ID)) ORGAN_ID_4, (SELECT DISTINCT
  T.PARENT_ORGAN FROM DA_RPT_IND_ORGAN_EXT_HISTORY T WHERE T.ORGAN_ID =
  NVL((SELECT DISTINCT T.PARENT_ORGAN FROM DA_RPT_IND_ORGAN_EXT_HISTORY T
  WHERE T.ORGAN_ID = DA.ATTACH_ORGAN_ID AND T.START_DATE <= :B1 AND
  NVL(T.END_DATE, DATE '2050-12-01') > :B1 ), (SELECT DISTINCT T.ORGAN_4 FROM
  T_RPT_ORGAN_EXT T WHERE T.ORGAN_ID = DA.ATTACH_ORGAN_ID)) AND T.START_DATE
  <= :B1 AND NVL(T.END_DATE, DATE '2050-12-01') > :B1 ) ORGAN_ID_3,
  SUBSTR((SELECT DISTINCT T.PARENT_ORGAN FROM DA_RPT_IND_ORGAN_EXT_HISTORY T
  WHERE T.ORGAN_ID = NVL((SELECT DISTINCT T.PARENT_ORGAN FROM
  DA_RPT_IND_ORGAN_EXT_HISTORY T WHERE T.ORGAN_ID = DA.ATTACH_ORGAN_ID AND
  T.START_DATE <= :B1 AND NVL(T.END_DATE, DATE '2050-12-01') > :B1 ), (SELECT
  DISTINCT T.ORGAN_4 FROM T_RPT_ORGAN_EXT T WHERE T.ORGAN_ID =
  DA.ATTACH_ORGAN_ID)) AND T.START_DATE <= :B1 AND NVL(T.END_DATE, DATE
  '2050-12-01') > :B1 ), 1, 3) ORGAN_ID_2, '1' ORGAN_ID_1 FROM
  DA_RPT_IND_DEPT_ACCEPT_MONTH DA WHERE 1 = 1 AND DA.DEPT_TYPE = 3 AND
  DA.YEAR_MONTH = TO_NUMBER(TO_CHAR(:B1 , 'yyyymm')) GROUP BY DA.YEAR_MONTH,
  DA.ATTACH_ORGAN_ID), TEMP_MONTHS AS (SELECT T.YEAR_MONTH YEAR_MONTH,
  T.ORGAN_ID_5 ORGAN_ID, 5 CLASS_ID, SUM(NVL(DO.TURN_MONTHS3, 0)) TURN_MONTH3,
   SUM(NVL(DO.RETAINED_MONTHS3, 0)) AT3_MONTH, SUM(NVL(DO.RETAINED_MONTHS6, 0)
  ) AT6_MONTH, SUM(NVL(DO.RETAINED_MONTHS13, 0)) AT13_MONTH,
  SUM(NVL(DO.RETAINED_NEW_MONTHS6, 0)) AT6_NEW_MONTH,
  SUM(NVL(DO.RETAINED_NEW_MONTHS13, 0)) AT13_NEW_MONTH FROM ORGAN_TEMP T,
  DA_RPT_IND_ORGAN_AGENT_DESC DO WHERE 1 = 1 AND T.ORGAN_ID_5 =
  DO.ATTACH_ORGAN_ID(+) AND T.YEAR_MONTH = DO.YEAR_MONTH(+) AND T.YEAR_MONTH =
   TO_NUMBER(TO_CHAR(:B1 , 'yyyymm')) GROUP BY T.YEAR_MONTH, T.ORGAN_ID_5),
  TEMP AS (SELECT * FROM TEMP_MONTHS DO UNION ALL SELECT T.YEAR_MONTH
  YEAR_MONTH, T.ORGAN_ID_4 ORGAN_ID, 4 CLASS_ID, SUM(DO.TURN_MONTH3)
  TURN_MONTH3, SUM(DO.AT3_MONTH) AT3_MONTH, SUM(DO.AT6_MONTH) AT6_MONTH,
  SUM(DO.AT13_MONTH) AT13_MONTH, SUM(DO.AT6_NEW_MONTH) AT6_NEW_MONTH,
  SUM(DO.AT13_NEW_MONTH) AT13_NEW_MONTH FROM ORGAN_TEMP T, TEMP_MONTHS DO
  WHERE 1 = 1 AND T.ORGAN_ID_5 = DO.ORGAN_ID AND T.YEAR_MONTH = DO.YEAR_MONTH
  AND T.YEAR_MONTH = TO_NUMBER(TO_CHAR(:B1 , 'yyyymm')) GROUP BY T.YEAR_MONTH,
   T.ORGAN_ID_4 UNION ALL SELECT T.YEAR_MONTH YEAR_MONTH, T.ORGAN_ID_3
  ORGAN_ID, 3 CLASS_ID, SUM(DO.TURN_MONTH3) TURN_MONTH3, SUM(DO.AT3_MONTH)
  AT3_MONTH, SUM(DO.AT6_MONTH) AT6_MONTH, SUM(DO.AT13_MONTH) AT13_MONTH,
  SUM(DO.AT6_NEW_MONTH) AT6_NEW_MONTH, SUM(DO.AT13_NEW_MONTH) AT13_NEW_MONTH
  FROM ORGAN_TEMP T, TEMP_MONTHS DO WHERE 1 = 1 AND T.ORGAN_ID_5 =
  DO.ORGAN_ID AND T.YEAR_MONTH = DO.YEAR_MONTH AND T.YEAR_MONTH =
  TO_NUMBER(TO_CHAR(:B1 , 'yyyymm')) GROUP BY T.YEAR_MONTH, T.ORGAN_ID_3
  UNION ALL SELECT T.YEAR_MONTH YEAR_MONTH, T.ORGAN_ID_2 ORGAN_ID, 2 CLASS_ID,
   SUM(DO.TURN_MONTH3) TURN_MONTH3, SUM(DO.AT3_MONTH) AT3_MONTH,
  SUM(DO.AT6_MONTH) AT6_MONTH, SUM(DO.AT13_MONTH) AT13_MONTH,
  SUM(DO.AT6_NEW_MONTH) AT6_NEW_MONTH, SUM(DO.AT13_NEW_MONTH) AT13_NEW_MONTH
  FROM ORGAN_TEMP T, TEMP_MONTHS DO WHERE 1 = 1 AND T.ORGAN_ID_5 =
  DO.ORGAN_ID AND T.YEAR_MONTH = DO.YEAR_MONTH AND T.YEAR_MONTH =
  TO_NUMBER(TO_CHAR(:B1 , 'yyyymm')) GROUP BY T.YEAR_MONTH, T.ORGAN_ID_2
  UNION ALL SELECT T.YEAR_MONTH YEAR_MONTH, T.ORGAN_ID_1 ORGAN_ID, 1 CLASS_ID,
   SUM(DO.TURN_MONTH3) TURN_MONTH3, SUM(DO.AT3_MONTH) AT3_MONTH,
  SUM(DO.AT6_MONTH) AT6_MONTH, SUM(DO.AT13_MONTH) AT13_MONTH,
  SUM(DO.AT6_NEW_MONTH) AT6_NEW_MONTH, SUM(DO.AT13_NEW_MONTH) AT13_NEW_MONTH
  FROM ORGAN_TEMP T, TEMP_MONTHS DO WHERE 1 = 1 AND DO.ORGAN_ID =
  T.ORGAN_ID_5 AND T.YEAR_MONTH = DO.YEAR_MONTH AND T.YEAR_MONTH =
  TO_NUMBER(TO_CHAR(:B1 , 'yyyymm')) GROUP BY T.YEAR_MONTH, T.ORGAN_ID_1)
  SELECT * FROM TEMP ) TEMP WHERE TEMP.YEAR_MONTH = D.YEAR_MONTH AND
  ((D.ORGAN_ID = TEMP.ORGAN_ID) OR (D.ORGAN_ID IS NULL AND TEMP.ORGAN_ID IS
  NULL)) AND TEMP.CLASS_ID = D.CLASS_ID )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 199  


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        3        0.00          0.00
  direct path write temp                       2519        0.00          0.30
  db file scattered read                       2606        0.00          0.24
  latch: row cache objects                        2        0.00          0.00
  db file sequential read                         2        0.00          0.00
********************************************************************************


且没有执行计划 的记录 TPL2A_ora_53215740.trc

Maclean Liu(刘相兵

TPL2A_ora_14745696.trc 有执行计划



SQL ID: 3dcjxvwqxyu58 Plan Hash: 3486841694

UPDATE DA_RPT_IND_ORGAN_ACCEPT_MONTH D SET (D.TURN_MONTH3, D.AT3_MONTH,
  D.AT6_MONTH, D.AT13_MONTH, D.AT6_NEW_MONTH, D.AT13_NEW_MONTH) = (SELECT
  TEMP.TURN_MONTH3, TEMP.AT3_MONTH,TEMP.AT6_MONTH, TEMP.AT13_MONTH,
  TEMP.AT6_NEW_MONTH, TEMP.AT13_NEW_MONTH FROM ( WITH ORGAN_TEMP AS (SELECT
  DISTINCT DA.YEAR_MONTH YEAR_MONTH, DA.ATTACH_ORGAN_ID ORGAN_ID_5,
  NVL((SELECT DISTINCT T.PARENT_ORGAN FROM DA_RPT_IND_ORGAN_EXT_HISTORY T
WHERE
T.ORGAN_ID = DA.ATTACH_ORGAN_ID AND T.START_DATE <= to_date('201212',
  'yyyymm') AND NVL(T.END_DATE, DATE '2050-12-01') > to_date('201212',
  'yyyymm') ), (SELECT DISTINCT T.ORGAN_4 FROM T_RPT_ORGAN_EXT T WHERE
  T.ORGAN_ID = DA.ATTACH_ORGAN_ID)) ORGAN_ID_4, (SELECT DISTINCT
  T.PARENT_ORGAN FROM DA_RPT_IND_ORGAN_EXT_HISTORY T WHERE T.ORGAN_ID =
  NVL((SELECT DISTINCT T.PARENT_ORGAN FROM DA_RPT_IND_ORGAN_EXT_HISTORY T
  WHERE T.ORGAN_ID = DA.ATTACH_ORGAN_ID AND T.START_DATE <= to_date('201212',
  'yyyymm') AND NVL(T.END_DATE, DATE '2050-12-01') > to_date('201212',
  'yyyymm') ), (SELECT DISTINCT T.ORGAN_4 FROM T_RPT_ORGAN_EXT T WHERE
  T.ORGAN_ID = DA.ATTACH_ORGAN_ID)) AND T.START_DATE <= to_date('201212',
  'yyyymm') AND NVL(T.END_DATE, DATE '2050-12-01') > to_date('201212',
  'yyyymm') ) ORGAN_ID_3, SUBSTR((SELECT DISTINCT T.PARENT_ORGAN FROM
  DA_RPT_IND_ORGAN_EXT_HISTORY T WHERE T.ORGAN_ID = NVL((SELECT DISTINCT
  T.PARENT_ORGAN FROM DA_RPT_IND_ORGAN_EXT_HISTORY T WHERE T.ORGAN_ID =
  DA.ATTACH_ORGAN_ID AND T.START_DATE <= to_date('201212', 'yyyymm') AND
  NVL(T.END_DATE, DATE '2050-12-01') > to_date('201212', 'yyyymm') ), (SELECT
  DISTINCT T.ORGAN_4 FROM T_RPT_ORGAN_EXT T WHERE T.ORGAN_ID =
  DA.ATTACH_ORGAN_ID)) AND T.START_DATE <= to_date('201212', 'yyyymm') AND
  NVL(T.END_DATE, DATE '2050-12-01') > to_date('201212', 'yyyymm') ), 1, 3)
  ORGAN_ID_2, '1' ORGAN_ID_1 FROM DA_RPT_IND_DEPT_ACCEPT_MONTH DA WHERE 1 = 1
  AND DA.DEPT_TYPE = 3 AND DA.YEAR_MONTH = TO_NUMBER(TO_CHAR(to_date('201212',
   'yyyymm') , 'yyyymm')) GROUP BY DA.YEAR_MONTH, DA.ATTACH_ORGAN_ID),
  TEMP_MONTHS AS (SELECT T.YEAR_MONTH YEAR_MONTH, T.ORGAN_ID_5 ORGAN_ID, 5
  CLASS_ID, SUM(NVL(DO.TURN_MONTHS3, 0)) TURN_MONTH3,
  SUM(NVL(DO.RETAINED_MONTHS3, 0)) AT3_MONTH, SUM(NVL(DO.RETAINED_MONTHS6, 0))
   AT6_MONTH, SUM(NVL(DO.RETAINED_MONTHS13, 0)) AT13_MONTH,
  SUM(NVL(DO.RETAINED_NEW_MONTHS6, 0)) AT6_NEW_MONTH,
  SUM(NVL(DO.RETAINED_NEW_MONTHS13, 0)) AT13_NEW_MONTH FROM ORGAN_TEMP T,
  DA_RPT_IND_ORGAN_AGENT_DESC DO WHERE 1 = 1 AND T.ORGAN_ID_5 =
  DO.ATTACH_ORGAN_ID(+) AND T.YEAR_MONTH = DO.YEAR_MONTH(+) AND T.YEAR_MONTH =
   TO_NUMBER(TO_CHAR(to_date('201212', 'yyyymm') , 'yyyymm')) GROUP BY
  T.YEAR_MONTH, T.ORGAN_ID_5), TEMP AS (SELECT * FROM TEMP_MONTHS DO UNION
  ALL SELECT T.YEAR_MONTH YEAR_MONTH, T.ORGAN_ID_4 ORGAN_ID, 4 CLASS_ID,
  SUM(DO.TURN_MONTH3) TURN_MONTH3, SUM(DO.AT3_MONTH) AT3_MONTH,
  SUM(DO.AT6_MONTH) AT6_MONTH, SUM(DO.AT13_MONTH) AT13_MONTH,
  SUM(DO.AT6_NEW_MONTH) AT6_NEW_MONTH, SUM(DO.AT13_NEW_MONTH) AT13_NEW_MONTH
  FROM ORGAN_TEMP T, TEMP_MONTHS DO WHERE 1 = 1 AND T.ORGAN_ID_5 =
  DO.ORGAN_ID AND T.YEAR_MONTH = DO.YEAR_MONTH AND T.YEAR_MONTH =
  TO_NUMBER(TO_CHAR(to_date('201212', 'yyyymm') , 'yyyymm')) GROUP BY
  T.YEAR_MONTH, T.ORGAN_ID_4 UNION ALL SELECT T.YEAR_MONTH YEAR_MONTH,
  T.ORGAN_ID_3 ORGAN_ID, 3 CLASS_ID, SUM(DO.TURN_MONTH3) TURN_MONTH3,
  SUM(DO.AT3_MONTH) AT3_MONTH, SUM(DO.AT6_MONTH) AT6_MONTH, SUM(DO.AT13_MONTH)
   AT13_MONTH, SUM(DO.AT6_NEW_MONTH) AT6_NEW_MONTH, SUM(DO.AT13_NEW_MONTH)
  AT13_NEW_MONTH FROM ORGAN_TEMP T, TEMP_MONTHS DO WHERE 1 = 1 AND
  T.ORGAN_ID_5 = DO.ORGAN_ID AND T.YEAR_MONTH = DO.YEAR_MONTH AND
  T.YEAR_MONTH = TO_NUMBER(TO_CHAR(to_date('201212', 'yyyymm') , 'yyyymm'))
  GROUP BY T.YEAR_MONTH, T.ORGAN_ID_3 UNION ALL SELECT T.YEAR_MONTH
  YEAR_MONTH, T.ORGAN_ID_2 ORGAN_ID, 2 CLASS_ID, SUM(DO.TURN_MONTH3)
  TURN_MONTH3, SUM(DO.AT3_MONTH) AT3_MONTH, SUM(DO.AT6_MONTH) AT6_MONTH,
  SUM(DO.AT13_MONTH) AT13_MONTH, SUM(DO.AT6_NEW_MONTH) AT6_NEW_MONTH,
  SUM(DO.AT13_NEW_MONTH) AT13_NEW_MONTH FROM ORGAN_TEMP T, TEMP_MONTHS DO
  WHERE 1 = 1 AND T.ORGAN_ID_5 = DO.ORGAN_ID AND T.YEAR_MONTH = DO.YEAR_MONTH
  AND T.YEAR_MONTH = TO_NUMBER(TO_CHAR(to_date('201212', 'yyyymm') , 'yyyymm')
  ) GROUP BY T.YEAR_MONTH, T.ORGAN_ID_2 UNION ALL SELECT T.YEAR_MONTH
  YEAR_MONTH, T.ORGAN_ID_1 ORGAN_ID, 1 CLASS_ID, SUM(DO.TURN_MONTH3)
  TURN_MONTH3, SUM(DO.AT3_MONTH) AT3_MONTH, SUM(DO.AT6_MONTH) AT6_MONTH,
  SUM(DO.AT13_MONTH) AT13_MONTH, SUM(DO.AT6_NEW_MONTH) AT6_NEW_MONTH,
  SUM(DO.AT13_NEW_MONTH) AT13_NEW_MONTH FROM ORGAN_TEMP T, TEMP_MONTHS DO
  WHERE 1 = 1 AND DO.ORGAN_ID = T.ORGAN_ID_5 AND T.YEAR_MONTH = DO.YEAR_MONTH
  AND T.YEAR_MONTH = TO_NUMBER(TO_CHAR(to_date('201212', 'yyyymm') , 'yyyymm')
  ) GROUP BY T.YEAR_MONTH, T.ORGAN_ID_1) SELECT * FROM TEMP ) TEMP WHERE
  TEMP.YEAR_MONTH = D.YEAR_MONTH AND ((D.ORGAN_ID = TEMP.ORGAN_ID) OR
  (D.ORGAN_ID IS NULL AND TEMP.ORGAN_ID IS NULL)) AND TEMP.CLASS_ID =
  D.CLASS_ID )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.03          0          0          0           0
Execute      1     28.29      86.94       3382     849263     278266      135184
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     28.31      86.98       3382     849263     278266      135184

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 199  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  DA_RPT_IND_ORGAN_ACCEPT_MONTH (cr=849320 pr=3382 pw=10 time=86942664 us)
    135184     135184     135184   TABLE ACCESS FULL DA_RPT_IND_ORGAN_ACCEPT_MONTH (cr=2830 pr=2679 pw=0 time=502696 us cost=562 size=3832930 card=132170)
      1999       1999       1999   VIEW  (cr=846424 pr=698 pw=10 time=74946713 us cost=90 size=53476 card=461)
      1999       1999       1999    TEMP TABLE TRANSFORMATION  (cr=846424 pr=698 pw=10 time=74725405 us)
         0          0          0     LOAD AS SELECT  (cr=21406 pr=554 pw=5 time=673632 us)
       878        878        878      SORT UNIQUE (cr=3251 pr=37 pw=0 time=115801 us cost=2 size=30 card=1)
       878        878        878       TABLE ACCESS BY INDEX ROWID DA_RPT_IND_ORGAN_EXT_HISTORY (cr=3251 pr=37 pw=0 time=105184 us cost=1 size=30 card=1)
      1751       1751       1751        INDEX RANGE SCAN IDX_IND_ORG_EXT_HIS_ORG_ID (cr=1766 pr=11 pw=0 time=26822 us cost=1 size=0 card=2)(object id 348022)
       878        878        878      SORT UNIQUE (cr=1764 pr=19 pw=0 time=82429 us cost=2 size=17 card=1)
       878        878        878       INDEX RANGE SCAN IDX_T_RPT_ORGAN_EXT_ORG (cr=1764 pr=19 pw=0 time=72581 us cost=1 size=17 card=1)(object id 356048)
       879        879        879      SORT UNIQUE (cr=7949 pr=8 pw=0 time=101233 us cost=4 size=600 card=20)
       879        879        879       TABLE ACCESS BY INDEX ROWID DA_RPT_IND_ORGAN_EXT_HISTORY (cr=7949 pr=8 pw=0 time=91202 us cost=1 size=30 card=1)
      1367       1367       1367        INDEX RANGE SCAN IDX_IND_ORG_EXT_HIS_ORG_ID (cr=6774 pr=0 pw=0 time=72151 us cost=1 size=0 card=2)(object id 348022)
       878        878        878         SORT UNIQUE (cr=3251 pr=0 pw=0 time=31182 us cost=2 size=30 card=1)
       878        878        878          TABLE ACCESS BY INDEX ROWID DA_RPT_IND_ORGAN_EXT_HISTORY (cr=3251 pr=0 pw=0 time=21762 us cost=1 size=30 card=1)
      1751       1751       1751           INDEX RANGE SCAN IDX_IND_ORG_EXT_HIS_ORG_ID (cr=1766 pr=0 pw=0 time=11582 us cost=1 size=0 card=2)(object id 348022)
       878        878        878         SORT UNIQUE (cr=1764 pr=0 pw=0 time=19172 us cost=2 size=17 card=1)
       878        878        878          INDEX RANGE SCAN IDX_T_RPT_ORGAN_EXT_ORG (cr=1764 pr=0 pw=0 time=9333 us cost=1 size=17 card=1)(object id 356048)
       879        879        879      SORT UNIQUE (cr=7949 pr=0 pw=0 time=89654 us cost=4 size=600 card=20)
       879        879        879       TABLE ACCESS BY INDEX ROWID DA_RPT_IND_ORGAN_EXT_HISTORY (cr=7949 pr=0 pw=0 time=79795 us cost=1 size=30 card=1)
      1367       1367       1367        INDEX RANGE SCAN IDX_IND_ORG_EXT_HIS_ORG_ID (cr=6774 pr=0 pw=0 time=71544 us cost=1 size=0 card=2)(object id 348022)
       878        878        878         SORT UNIQUE (cr=3251 pr=0 pw=0 time=31083 us cost=2 size=30 card=1)
       878        878        878          TABLE ACCESS BY INDEX ROWID DA_RPT_IND_ORGAN_EXT_HISTORY (cr=3251 pr=0 pw=0 time=21324 us cost=1 size=30 card=1)
      1751       1751       1751           INDEX RANGE SCAN IDX_IND_ORG_EXT_HIS_ORG_ID (cr=1766 pr=0 pw=0 time=11251 us cost=1 size=0 card=2)(object id 348022)
       878        878        878         SORT UNIQUE (cr=1764 pr=0 pw=0 time=19158 us cost=2 size=17 card=1)
       878        878        878          INDEX RANGE SCAN IDX_T_RPT_ORGAN_EXT_ORG (cr=1764 pr=0 pw=0 time=9440 us cost=1 size=17 card=1)(object id 356048)
       879        879        879      HASH GROUP BY (cr=493 pr=490 pw=0 time=252701 us cost=49 size=10560 card=660)
     12816      12816      12816       TABLE ACCESS BY INDEX ROWID DA_RPT_IND_DEPT_ACCEPT_MONTH (cr=493 pr=490 pw=0 time=77972 us cost=48 size=94512 card=5907)
     16648      16648      16648        INDEX RANGE SCAN IDX_IND_DEPT_ACCEPT_MONTH_YM (cr=40 pr=39 pw=0 time=25051 us cost=4 size=0 card=17760)(object id 347925)
         0          0          0     LOAD AS SELECT  (cr=142 pr=139 pw=5 time=172837 us)
       879        879        879      HASH GROUP BY (cr=142 pr=139 pw=0 time=168199 us cost=16 size=19147 card=467)
      9773       9773       9773       HASH JOIN OUTER (cr=142 pr=139 pw=0 time=104597 us cost=15 size=248091 card=6051)
       879        879        879        VIEW  (cr=8 pr=5 pw=0 time=1433 us cost=2 size=9240 card=660)
       879        879        879         TABLE ACCESS FULL SYS_TEMP_0FD9D681D_BCF52426 (cr=8 pr=5 pw=0 time=674 us cost=2 size=10560 card=660)
      9678       9678       9678        TABLE ACCESS BY INDEX ROWID DA_RPT_IND_ORGAN_AGENT_DESC (cr=134 pr=134 pw=0 time=77364 us cost=12 size=259200 card=9600)
      9678       9678       9678         INDEX RANGE SCAN IDX_IND_ORGAN_AGENT_DESC_YM (cr=25 pr=25 pw=0 time=33225 us cost=3 size=0 card=9600)(object id 348012)
      1999       1999       1999     VIEW  (cr=824876 pr=5 pw=0 time=73641588 us cost=24 size=53476 card=461)
      1999       1999       1999      UNION-ALL  (cr=824876 pr=5 pw=0 time=73460509 us)
       856        856        856       VIEW  (cr=811106 pr=5 pw=0 time=65961192 us cost=2 size=53012 card=457)
118826736  118826736  118826736        TABLE ACCESS FULL SYS_TEMP_0FD9D681E_BCF52426 (cr=811106 pr=5 pw=0 time=57772783 us cost=2 size=18737 card=457)
       854        854        854       SORT GROUP BY (cr=10290 pr=0 pw=0 time=2763688 us cost=6 size=128 card=1)
       862        862        862        FILTER  (cr=10290 pr=0 pw=0 time=1974903 us)
       862        862        862         HASH JOIN  (cr=10290 pr=0 pw=0 time=1789925 us cost=5 size=128 card=1)
       862        862        862          VIEW  (cr=5166 pr=0 pw=0 time=631315 us cost=2 size=23760 card=660)
    756819     756819     756819           TABLE ACCESS FULL SYS_TEMP_0FD9D681D_BCF52426 (cr=5166 pr=0 pw=0 time=393962 us cost=2 size=10560 card=660)
    750666     750666     750666          VIEW  (cr=5124 pr=0 pw=0 time=1027838 us cost=2 size=42044 card=457)
    750666     750666     750666           TABLE ACCESS FULL SYS_TEMP_0FD9D681E_BCF52426 (cr=5124 pr=0 pw=0 time=339629 us cost=2 size=18737 card=457)
       253        253        253       SORT GROUP BY (cr=3042 pr=0 pw=0 time=1181205 us cost=6 size=128 card=1)
       878        878        878        FILTER  (cr=3042 pr=0 pw=0 time=669811 us)
       878        878        878         HASH JOIN  (cr=3042 pr=0 pw=0 time=512724 us cost=5 size=128 card=1)
       878        878        878          VIEW  (cr=1524 pr=0 pw=0 time=175573 us cost=2 size=23760 card=660)
    223266     223266     223266           TABLE ACCESS FULL SYS_TEMP_0FD9D681D_BCF52426 (cr=1524 pr=0 pw=0 time=115345 us cost=2 size=10560 card=660)
    222387     222387     222387          VIEW  (cr=1518 pr=0 pw=0 time=304253 us cost=2 size=42044 card=457)
    222387     222387     222387           TABLE ACCESS FULL SYS_TEMP_0FD9D681E_BCF52426 (cr=1518 pr=0 pw=0 time=104876 us cost=2 size=18737 card=457)
        35         35         35       SORT GROUP BY (cr=426 pr=0 pw=0 time=685040 us cost=6 size=114 card=1)
       878        878        878        FILTER  (cr=426 pr=0 pw=0 time=278850 us)
       878        878        878         HASH JOIN  (cr=426 pr=0 pw=0 time=136033 us cost=5 size=114 card=1)
       878        878        878          VIEW  (cr=216 pr=0 pw=0 time=58421 us cost=2 size=14520 card=660)
     31644      31644      31644           TABLE ACCESS FULL SYS_TEMP_0FD9D681D_BCF52426 (cr=216 pr=0 pw=0 time=19059 us cost=2 size=10560 card=660)
     30765      30765      30765          VIEW  (cr=210 pr=0 pw=0 time=50379 us cost=2 size=42044 card=457)
     30765      30765      30765           TABLE ACCESS FULL SYS_TEMP_0FD9D681E_BCF52426 (cr=210 pr=0 pw=0 time=18544 us cost=2 size=18737 card=457)
         1          1          1       SORT GROUP BY (cr=12 pr=0 pw=0 time=586326 us cost=6 size=109 card=1)
       879        879        879        FILTER  (cr=12 pr=0 pw=0 time=145016 us)
       879        879        879         HASH JOIN  (cr=12 pr=0 pw=0 time=4620 us cost=5 size=109 card=1)
       879        879        879          VIEW  (cr=6 pr=0 pw=0 time=1034 us cost=2 size=11220 card=660)
       879        879        879           TABLE ACCESS FULL SYS_TEMP_0FD9D681D_BCF52426 (cr=6 pr=0 pw=0 time=528 us cost=2 size=10560 card=660)
       879        879        879          VIEW  (cr=6 pr=0 pw=0 time=1162 us cost=2 size=42044 card=457)
       879        879        879           TABLE ACCESS FULL SYS_TEMP_0FD9D681E_BCF52426 (cr=6 pr=0 pw=0 time=408 us cost=2 size=18737 card=457)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                       20        0.00          0.00
  db file sequential read                       695        0.02          0.40
  db file scattered read                        178        0.07          1.74
  direct path write temp                          2        0.00          0.00
  direct path sync                                1        0.00          0.00
  db file parallel read                           1        0.02          0.02
  latch: cache buffers chains                     2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

雪影舞剑

sorry 刘大，附件trc传的有问题，我又更新了
TPL2A_ora_53215740_3.txt

雪影舞剑

更新一下我的进度，在解决方案上的突破:

本来建议修改成不使用绑定变量，但是由于pl/sql代码引用变量默认会转成绑定变量，所以这里不像JDBC代码可以直接进行不使用绑定变量的修改

其源代码为PKG:
其PKG内容
PKG_DA_RPT_IND_1_12
is
PROCEDURE DO_IND_ORGAN_AGENT_DESC(p_stat_date IN DATE, ---执行时间
p_work_flag IN NUMBER ---执行标志(0:补跑 1:正常执行)
) IS
                v_begin_time DATE; ---系统时间
                v_calc_date  date; ---操作时间
                v_start_time DATE; --统计开始时间
                v_end_time   DATE; --统计结束时间
               
BEGIN
...
update DA_RPT_IND_ORGAN_ACCEPT_MONTH d  ...
  and nvl(t.end_date, date '2050-12-01') > v_end_time),        ...       
    and nvl(t.end_date, date '2050-12-01') > v_end_time) organ_id_3 ...

雪影舞剑

思考过后，估计改成动态SQL可能不使用绑定变量，但由于修改较繁琐，想到了另一个方案，解决了此问题:

将最外层的WITH AS临时块存储为实体表(可以理解为临时表)，然后再与update的表作关联

测试仅需1分钟

SQL> begin yxd_test2(to_date('201212','yyyymm'),0); end;
  2  /

PL/SQL procedure successfully completed

Executed in 40.266 seconds

SQL> rollback;

Rollback complete

PROCEDURE中将UPDATE拆分，修改为:

INSERT INTO WITH_RESULT_YXD
WITH ORGAN_TEMP AS(...),TEMP_MONTHS AS(...),TEMP AS()
SELECT * FROM TEMP ;

commit;

    UPDATE DA_RPT_IND_ORGAN_ACCEPT_MONTH D
   SET (D.TURN_MONTH3,
        D.AT3_MONTH,
        D.AT6_MONTH,
        D.AT13_MONTH,
        D.AT6_NEW_MONTH,
        D.AT13_NEW_MONTH) =
       (SELECT TEMP.TURN_MONTH3,
               TEMP.AT3_MONTH,
               TEMP.AT6_MONTH,
               TEMP.AT13_MONTH,
               TEMP.AT6_NEW_MONTH,
               TEMP.AT13_NEW_MONTH
          FROM with_result_yxd TEMP
                  WHERE TEMP.YEAR_MONTH = D.YEAR_MONTH
            AND ((D.ORGAN_ID = TEMP.ORGAN_ID) OR
                (D.ORGAN_ID IS NULL AND TEMP.ORGAN_ID IS NULL))
            AND TEMP.CLASS_ID = D.CLASS_ID
        );

雪影舞剑

虽然问题暂时通过绕过的方式解决了，还是希望大侠们继续分析下原始问题:
即使用绑定变量和不使用的情况下，执行计划一致，逻辑读/物理读相差千倍，从而导致执行时间变慢的原因。
谢谢！

26856649

临时表啊 ，试试这个呢 /*+ materialize */
http://blog.csdn.net/robinson1988/article/details/8860026

雪影舞剑

使用 /*+ materialize */  提示，问题还是一样，执行计划相同，运行了十几分钟，buffer get达到6800万，已取消

ShineCQY

  direct path write temp                     109816        1.03         15.95
执行的时候监控下临时表空间的使用情况。或者看下sort in disk 的情况。
使用with as 会导致pga 不够用，产生大量磁盘排序。

低调小马哥

我觉得是不是有可能使用绑定变量了之后，with语句不能进行先做成临时表然后利用此临时表进行关联，因为绑定变量是不确定的，oracle没法先用with语句固定出来一个临时表，估计是个bug吧，明天在虚拟机测试下再说