Home » RDBMS Server » Performance Tuning » query tuning (oracle,9.2.0.8,window)
query tuning [message #333764] Mon, 14 July 2008 07:07 Go to next message
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





Re: query tuning [message #333884 is a reply to message #333764] Mon, 14 July 2008 14:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: query tuning [message #333930 is a reply to message #333764] Mon, 14 July 2008 22:19 Go to previous messageGo to next message
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....

Re: query tuning [message #334096 is a reply to message #333930] Tue, 15 July 2008 07:42 Go to previous messageGo to next message
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
Re: query tuning [message #334168 is a reply to message #333764] Tue, 15 July 2008 11:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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)
Re: query tuning [message #334855 is a reply to message #334169] Fri, 18 July 2008 06:42 Go to previous messageGo to next message
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
Re: query tuning [message #334884 is a reply to message #333764] Fri, 18 July 2008 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 Go to previous message
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:
  


Current Time: Fri Dec 09 13:42:52 CST 2016

Total time taken to generate the page: 0.11225 seconds