Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Hang on explain plan

0 views
Skip to first unread message

astalavista

unread,
May 20, 2008, 3:05:18 AM5/20/08
to
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
>


ora...@msn.com

unread,
May 20, 2008, 9:04:07 AM5/20/08
to
On May 20, 2:05 am, "astalavista" <nob...@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     |
> ---------------------------------------------------------------------------­----------------------------
> ---------------------------------------------------------------------------­----------------------------

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

0 new messages