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 Go to next message](/forum/theme/orafaq/images/down.png) |
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: query tuning [message #333930 is a reply to message #333764] |
Mon, 14 July 2008 22:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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)
|
|
|
Re: query tuning [message #334855 is a reply to message #334169] |
Fri, 18 July 2008 06:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:23:38 CDT 2024
|