OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » query tuning (oracle,9.2.0.8,window)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query tuning [message #333764]

Mon, 14 July 2008 07:07

vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune

Member

Hi All,

I am novice in oracle as well as in performance tuning. I am just doing R&D.

I have 3 tables :-
Table No of row

erp_system_items                         3889514
erp_avp_transactions                    50848645
erp_organizations                           105

my query is as

select
      i.inventory_item_id,
      i.organization_id,
      sysdate, -- creation_date
      sysdate, -- last_update_date
      i.part, -- inventory_item_name
      i.item_type,
      i.inventory_item_status_code,
      i.buyer_id,
      i.buyer_name
    from erp_system_items i,
         erp_avp_transactions t,
	  erp_organizations e
    where  e.organization_id=i.organization_id
     and   i.organization_id = t.organization_id
    and i.inventory_item_id = t.inventory_item_id

execution plan for this query

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		50 M	 	65557  	 	      	             	 
  HASH JOIN		50 M	3G	65557  	 	      	             	 
    NESTED LOOPS		3 M	201 M	12  	 	      	             	 
      INDEX FULL SCAN	CME.ERP_ORGANIZATIONS_N1	105  	420  	1  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	CME.ERP_SYSTEM_ITEMS	36 K	1 M	1  	 	      	             	 
        INDEX RANGE SCAN	CME.ERP_SYSTEM_ITEMS_NN	66 K	 	 	 	      	             	 
    VIEW	CME.index$_join$_002	51 M	490 M	44420  	 	      	             	 
      HASH JOIN		50 M	3G	65557  	 	      	             	 
        PARTITION RANGE ALL		  	 	 	 	      	            1	49
          BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
            BITMAP INDEX FULL SCAN	CME.ERP_AVP_TRANSACTIONS_BN1	  	 	 	 	      	            1	49
        PARTITION RANGE ALL		  	 	 	 	      	            1	49
          BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
            BITMAP INDEX FULL SCAN	CME.ERP_AVP_TRANSACTIONS_BN2	  	 	 	 	      	            1	49

This query is taking more time any can suggest me…how I can improve performance .

Thanks,
Sagar

Report message to a moderator

Re: query tuning [message #333884 is a reply to message #333764]

Mon, 14 July 2008 14:02

Kriptas
Messages: 118
Registered: June 2008

Senior Member

try this:

select /*+ ALL_ROWS USE_HASH(e i) USE_HASH(i t) */
      i.inventory_item_id,
      i.organization_id,
      sysdate, -- creation_date
      sysdate, -- last_update_date
      i.part, -- inventory_item_name
      i.item_type,
      i.inventory_item_status_code,
      i.buyer_id,
      i.buyer_name
from erp_organizations e
INNER JOIN erp_system_items i     ON e.organization_id = i.organization_id
INNER JOIN erp_avp_transactions t ON i.organization_id = t.organization_id 
				     AND i.inventory_item_id = t.inventory_item_id

query must take about 3 minutes tu execute

[Updated on: Mon, 14 July 2008 14:03]

Report message to a moderator

Re: query tuning [message #333927 is a reply to message #333884]

Mon, 14 July 2008 22:08

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Does the join always find matching rows in erp_avp_transactions, or only sometimes? If you are going to be joining to more than 10% of the rows in erp_avp_transactions, then I agree with Kriptas, you need to encourage a Hash Join.

The plan Oracle has selected is pretty strange though.
I suspect it is because you do not have statistics gathered on erp_avp_transactions. I cannot see any stats for that table in your plan.

Gather statistics with DBMS_STATS.GATHER_TABLE_STATS() and try again. You should not have to add hints to a query this simple.

However, if the join to erp_avp_transactions will result in joining no more than around 1-10% of the table, then an index on (organization_id, inventory_item_id) would help.

Ross Leishman

Report message to a moderator

Re: query tuning [message #333930 is a reply to message #333764]

Mon, 14 July 2008 22:19

Kriptas
Messages: 118
Registered: June 2008

Senior Member

execution plan is normal, I think there is range partitioning on erp_avp_transactions by some date value.
and local bitmap indexes on t.organization_id and t.inventory_item_id. Optimiser choses first rows plan....

Report message to a moderator

Re: query tuning [message #334096 is a reply to message #333930]

Tue, 15 July 2008 07:42

vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune

Member

Hi Kriptas,

You are right erp_avp_transactions have date range partition.query which u have provided it is taking around 25-30 minute.

could you suggest me some other way.

Thanks,
Sagar

Report message to a moderator

Re: query tuning [message #334168 is a reply to message #333764]

Tue, 15 July 2008 11:25

Kriptas
Messages: 118
Registered: June 2008

Senior Member

wow 30 min?
what is server machine specification, does it have raid, or storage array ?
post execution plan please,
what is hash_area_size value ?
there i possibility change optimiser mode to all_rows?
does statistics are gathered on that tables?
what was query time before remake?
does schatered_read is enabled?

where do you inserting resultset?
just do insert like "create table results as"
is table where you inserting has indexes, it may slow down the whole thing.
then you need to drop indexes on that table, or unusable local partition indexes and rebuild them after insert

[Updated on: Tue, 15 July 2008 11:44]

Report message to a moderator

Re: query tuning [message #334169 is a reply to message #333764]

Tue, 15 July 2008 11:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

please post explain plan & results from:

select
      i.inventory_item_id,
      i.organization_id,
      sysdate, -- creation_date
      sysdate, -- last_update_date
      i.part, -- inventory_item_name
      i.item_type,
      i.inventory_item_status_code,
      i.buyer_id,
      i.buyer_name
    from erp_system_items i
    where (i.organization_id,i.inventory_item_id)  in select t.organization_id, t.inventory_item_id from erp_avp_transactions t)
     and  i.organization_id in (select e.organization_id from erp_organizations e)

Report message to a moderator

Re: query tuning [message #334855 is a reply to message #334169]

Fri, 18 July 2008 06:42

vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune

Member

Hi anacedent,

query which u have provided took 20 minutes to execute.PSB explain plan for the same.

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	102466  	 	      	             	 
  NESTED LOOPS		1  	65  	102466  	 	      	             	 
    HASH JOIN SEMI		1  	61  	102465  	 	      	             	 
      TABLE ACCESS FULL	CME.ERP_SYSTEM_ITEMS	3 M	187 M	36961  	 	      	             	 
      VIEW	CME.index$_join$_002	51 M	492 M	44555  	 	      	             	 
        HASH JOIN		1  	61  	102465  	 	      	             	 
          PARTITION RANGE ALL		  	 	 	 	      	            1	49
            BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
              BITMAP INDEX FULL SCAN	CME.ERP_AVP_TRANSACTIONS_BN1	  	 	 	 	      	            1	49
          PARTITION RANGE ALL		  	 	 	 	      	            1	49
            BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
              BITMAP INDEX FULL SCAN	CME.ERP_AVP_TRANSACTIONS_BN2	  	 	 	 	      	            1	49
    INDEX UNIQUE SCAN	CME.ERP_ORGANIZATIONS_U1	1  	4

Thanks,
Sagar

Report message to a moderator

Re: query tuning [message #334884 is a reply to message #333764]

Fri, 18 July 2008 09:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please try a different variation

select
      i.inventory_item_id,
      i.organization_id,
      sysdate, -- creation_date
      sysdate, -- last_update_date
      i.part, -- inventory_item_name
      i.item_type,
      i.inventory_item_status_code,
      i.buyer_id,
      i.buyer_name
    from erp_system_items i
    where (i.organization_id,i.inventory_item_id)  in 
       (select t.organization_id, t.inventory_item_id 
        from erp_avp_transactions t 
        where t.organization_id in (select e.organization_id 
                                    from erp_organizations e
                                   )
       )

[Updated on: Fri, 18 July 2008 10:18] by Moderator

Report message to a moderator

Re: query tuning [message #334924 is a reply to message #333764]

Fri, 18 July 2008 12:11

Kriptas
Messages: 118
Registered: June 2008

Senior Member

can you post execution plan of my query, like I asked you?
that query must take 5-10 minutes, no more if result ser is about 50mln records

select /*+ ALL_ROWS USE_HASH(e i) USE_HASH(i t) */
      i.inventory_item_id,
      i.organization_id,
      sysdate, -- creation_date
      sysdate, -- last_update_date
      i.part, -- inventory_item_name
      i.item_type,
      i.inventory_item_status_code,
      i.buyer_id,
      i.buyer_name
from erp_organizations e
INNER JOIN erp_system_items i     ON e.organization_id = i.organization_id
INNER JOIN erp_avp_transactions t ON i.organization_id = t.organization_id 
				     AND i.inventory_item_id = t.inventory_item_id

and can use second query

select /*+ ALL_ROWS FULL(i) FULL(t) USE_HASH(e i) USE_HASH(i t) */
      i.inventory_item_id,
      i.organization_id,
      sysdate, -- creation_date
      sysdate, -- last_update_date
      i.part, -- inventory_item_name
      i.item_type,
      i.inventory_item_status_code,
      i.buyer_id,
      i.buyer_name
from erp_organizations e
INNER JOIN erp_system_items i     ON e.organization_id = i.organization_id
INNER JOIN erp_avp_transactions t ON i.organization_id = t.organization_id 
				     AND i.inventory_item_id = t.inventory_item_id

please post execution plans of both queries,
because bitmap index, and nested loops in this query is the problem.

If hash_area_size parameter value is small, then hash jon can provide slow results..
my simillar query from 2mln, 2mln, 300 mln rows tables runs about 10 minutes....

[Updated on: Fri, 18 July 2008 12:16]

Report message to a moderator

Previous Topic:	Effect on physical standby database
Next Topic:	user I/O and read by other session waits

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 24 16:31:15 CST 2025