Hang on explain plan

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

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
>
Received on Tue May 20 2008 - 02:05:18 CDT