Home » RDBMS Server » Performance Tuning » Modifying a plan (Oracle 10g)
Modifying a plan [message #388290] Tue, 24 February 2009 02:40 Go to next message
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 #388298 is a reply to message #388290] Tue, 24 February 2009 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Instead of the above plan I want a plan like below

Why?

Regards
Michel
Re: Modifying a plan [message #388300 is a reply to message #388298] Tue, 24 February 2009 03:05 Go to previous messageGo to next message
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
Re: Modifying a plan [message #388317 is a reply to message #388300] Tue, 24 February 2009 04:14 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member


I am inserting the populated data into a table .
Is it bad to have HASH GROUP BY (line 2) FILTER(Line 3) in a plan.
Why in Filter there is no value appears?


Thanks
Oli

[Updated on: Tue, 24 February 2009 04:20]

Report message to a moderator

Previous Topic: SQL tuning (merged)
Next Topic: blevel in bitmap index
Goto Forum:
  


Current Time: Fri Dec 09 05:46:53 CST 2016

Total time taken to generate the page: 0.14312 seconds