| Modifying a plan [message #388290] |
Tue, 24 February 2009 02:40  |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
EXPLAIN PLAN FOR
select
to_date(TO_CHAR(a.upd_tmstmp,'dd-mon-yy'))
, a.curr_app,a.dest_app,d.product_cde
,'999',b.identifier_cde,c.UID,'009',a.user_id,COUNT(*),USER,:p_start_dte
from object_item a,
channel b,
invoice_pge c,
product d
where a.upd_tmstmp between :v1_end_date and :v1_begin_date
and a.dest_app in ('A0Z1','B0Z2','C0Z2','Z034','L023')
and a.curr_app in ('A0Y1','A0Y2','C0Y2','C041','D065')
and a.item_cde = b.channel_cde
and a.item_cde = c.channel_cde
and a.sub_item_cde = c.channel_typ_nbr
and c.object_item_cde = d.object_item_cde
group by to_date(TO_CHAR(a.upd_tmstmp,'dd-mon-yy'))
, a.curr_app
,a.dest_app
,d.product_cde
,'999'
,b.identifier_cde
,c.UID
,'009'
,a.user_id
,user
,:p_start_dte;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3461810400
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 104 | 10 (10)| 00:00:01
| 1 | [COLOR=red]HASH GROUP BY[/COLOR] | | 1 | 104 | 10 (10)| 00:00:01
|* 2 | [COLOR=red]FILTER [/COLOR] | | | | |
| 3 | NESTED LOOPS | | 1 | 104 | 9 (0)| 00:00:01
| 4 | NESTED LOOPS | | 1 | 93 | 7 (0)| 00:00:01
| 5 | NESTED LOOPS | | 1 | 82 | 5 (0)| 00:00:01
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 6 | TABLE ACCESS BY INDEX ROWID| OBJECT_ITEM | 1 | 51 | 3 (0)| 00:
|* 7 | INDEX RANGE SCAN | X1_OBJECT_ITEM_01 | 1 | | 3 (0)| 00:00:01
| 8 | TABLE ACCESS BY INDEX ROWID| INVOICE_PAGE | 1 | 31 | 2 (0)| 00:00:01
|* 9 | INDEX UNIQUE SCAN | XPKINVOICE_PAGE | 1 | | 1 (0)| 00:00:01
| 10 | TABLE ACCESS BY INDEX ROWID | PRODUCT | 1 | 11 | 2 (0)| 00:00:01
|* 11 | INDEX UNIQUE SCAN | PK_PRODUCT | 1 | | 1 (0)| 00:00:01
| 12 | TABLE ACCESS BY INDEX ROWID | CHANNEL | 1 | 11 | 2 (0)| 00:00:01
|* 13 | INDEX UNIQUE SCAN | XPKCHANNEL | 1 | | 1 (0)| 00:00:01
----------------------------------------------------------------------------------------------------
Instead of the above plan I want a plan like below:
Id | Operation | Name |
-----------------------------------------------------------------------
0 | SELECT STATEMENT | |
1 | SORT GROUP BY | |
2 | NESTED LOOPS | |
3 | NESTED LOOPS | |
4 | NESTED LOOPS | |
* 5 | TABLE ACCESS BY INDEX ROWID| OBJECT_ITEM |
* 6 | INDEX RANGE SCAN | X1_OBJECT_ITEM_01 |
7 | TABLE ACCESS BY INDEX ROWID| INVOICE_PAGE |
* 8 | INDEX UNIQUE SCAN | XPKINVOICE_PAGE |
9 | TABLE ACCESS BY INDEX ROWID | PRODUCT |
* 10 | INDEX UNIQUE SCAN | PK_PRODUCT |
11 | TABLE ACCESS BY INDEX ROWID | CHANNEL |
* 12 | INDEX UNIQUE SCAN | XPKCHANNEL |
-----------------------------------------------------------------------
What hint need to be used to remove the 3 rd line in the first plane above.
[Updated on: Tue, 24 February 2009 02:44] Report message to a moderator |
|
|
|
|
|
| Re: Modifying a plan [message #388300 is a reply to message #388298] |
Tue, 24 February 2009 03:05  |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
The query is taking so much time with the plan 1. When I used rule hint it takes a few min (2-3) min to return data.
The 2nd plan was using rule hint. I want to modify the query or
use some other hint so that I can have similar plan.
Read somewhere that the Clustered Hash Group By algorithm can lead to significant wasted work on the part of the optimizer if it is chosen in an environment where data is being updated at the same time that queries are being executed
Please advice
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
