Re: Hang on explain plan

On May 20, 2:05 am, "astalavista" <nob..._at_nowhere.com> wrote:
> Hi,
>
> Oracle 10.2.0.3 on W2003
>
> I have the query below (with views)
> the explain plan hang when there are stats
> and is OK with no stats.
>
> Can you help me on this ?
> Thanks in advance
>
> > explain plan for
>
>   2  SELECT ALL b.r_object_id, dm_repeating1_0.state_name, a.object_name
>   3        FROM dm_policy_sp a, dm_sysobject_sp b, dm_policy_rp
> dm_repeating1_0
>   4       WHERE (    (a.r_object_id = b.r_policy_id)
>   5              AND (dm_repeating1_0.i_state_no = b.r_current_state)
>   6              AND b.r_object_id IN ('091e84818000591c')
>   7             )
>   8         AND a.i_is_deleted = 0
>   9         AND b.i_is_deleted = 0
>  10         AND dm_repeating1_0.r_object_id = a.r_object_id
>  11  /
>
> Explained.
>
> Elapsed: 00:00:00.00
>
> > select * from table(dbms_xplan.display);
>
> Plan hash value: 876504240
>
> ---------------------------------------------------------------------------­----------------------------
> | Id  | Operation                        | Name               | Rows  |
> Bytes | Cost (%CPU)| Time     |
> ---------------------------------------------------------------------------­----------------------------
> |   0 | SELECT STATEMENT                 |                    |     1 |
> 285 |     9   (0)| 00:00:01 |
> |*  1 |  TABLE ACCESS BY INDEX ROWID     | DM_POLICY_R        |     1 |
> 54 |     2   (0)| 00:00:01 |
> |   2 |   NESTED LOOPS                   |                    |     1 |
> 285 |     9   (0)| 00:00:01 |
> |   3 |    NESTED LOOPS                  |                    |     1 |
> 231 |     7   (0)| 00:00:01 |
> |   4 |     NESTED LOOPS                 |                    |     1 |
> 208 |     6   (0)| 00:00:01 |
> |   5 |      NESTED LOOPS                |                    |     1 |
> 198 |     5   (0)| 00:00:01 |
> |*  6 |       TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S     |     1 |
> 46 |     3   (0)| 00:00:01 |
> |*  7 |        INDEX RANGE SCAN          | D_1F1E84818000010A |     5 |
> |     2   (0)| 00:00:01 |
> |*  8 |       TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S     |     1 |
> 152 |     2   (0)| 00:00:01 |
> |*  9 |        INDEX RANGE SCAN          | D_1F1E84818000010A |     1 |
> |     1   (0)| 00:00:01 |
> |* 10 |      INDEX RANGE SCAN            | D_1F1E848180000133 |     1 |
> 10 |     1   (0)| 00:00:01 |
> |* 11 |     INDEX RANGE SCAN             | D_1F1E84818000010B |     3 |
> 69 |     1   (0)| 00:00:01 |
> |* 12 |    INDEX RANGE SCAN              | D_1F1E848180000039 |     1 |
> |     1   (0)| 00:00:01 |
> ---------------------------------------------------------------------------­----------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    1 - filter("WCB_"."I_STATE_NO"="JK_"."R_CURRENT_STATE" AND
>               "LK_"."I_POSITION"="WCB_"."I_POSITION")
>    6 - filter("JK_"."I_IS_DELETED"=0)
>    7 - access("JK_"."R_OBJECT_ID"='091e84818000591c')
>    8 - filter("JK_"."I_IS_DELETED"=0)
>    9 - access("JK_"."R_OBJECT_ID"="JK_"."R_POLICY_ID")
>   10 - access("JK_"."R_OBJECT_ID"="UCB_"."R_OBJECT_ID")
>   11 - access("LK_"."R_OBJECT_ID"="JK_"."R_OBJECT_ID")
>   12 - access("LK_"."R_OBJECT_ID"="WCB_"."R_OBJECT_ID")
>
> Note
> -----
>    - dynamic sampling used for this statement
>
> 36 rows selected.
>
> Elapsed: 00:00:00.00
>
>
>
> - Hide quoted text -
>
> - Show quoted text -

You've checked on Metalink for this behaviour, I presume? I can't reproduce such behaviour.

David Fitzjarrell Received on Tue May 20 2008 - 08:04:07 CDT