Link to home
Start Free TrialLog in
Avatar of Jay Roy
Jay RoyFlag for United States of America

asked on

explain plan for MERGE

hi experts

I ran a simple explain plan for my merge statement

explain plan for merge into product p
using product_changes pc ON (p.product_id = pc.product_id)
when matched then
update set p.product_type_id = pc.product_type_id, p.name = pc.name,p.description = pc.description,p.price = pc.price
when not matched then --new rows
insert (p.product_id,p.product_type_id, p.name,p.description,p.price) values (pc.product_id,pc.product_type_id, pc.name,pc.description,pc.price);


select * from table(dbms_xplan.display)

the result is attached.

Can anyone please explain me how i can improve the performance.
thanks
merge-explain-plan.bmp
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Please don't post screen shots.  Execution plans are best when attached as a .txt file or posted in a
code block like this

Open in new window


With a total cost of 7 and 7 total rows I don't think it can be faster.

What makes you want 'improve performance'?
you have no filters, only a join condition.

Since you only have 7 rows in each table (at least Oracle thinks so)  a full table scan on both is appropriate.
What is the issue?

Is your merge running slow?
Avatar of Jay Roy

ASKER

>>What makes you want 'improve performance'?

just wanted to learn how to do it..
Assuming this sql was performing badly (i dont know what the statistics would be
for bad performance) how would i go about improving it ?

thx
>>just wanted to learn how to do it..

So this is a general question on tuning in general not specific to that statement?

Highlevel:
Always keep database statistics up to date!!!!!

Look at the cost column as a general guide but JUST as a guide.  Lower cost does not mean faster execution.

Zero in on the pieces that have the higher costs.

Look at full table scans based on your where clause.  If you feel the data will benefit from it, create new indexes to try to eliminate the full scans.

Keep in mind that over-indexing can hurt performance and new indexes can hurt existing statements executed elsewhere.
>>i dont know what the statistics would be for bad performance

Honestly:  Users complaining.

Depending on what you monitor on a routine basis and with what (AWR/ADDM or other reports from OEM, etc...), you might uncover some poor performing SQL that users aren't aware could be faster and tune that.


You need to be aware of your database and how it is used.

For example a small database with a few users: Does it matter that un-tuned SQL takes 1 minute when tuned SQL takes 30 seconds?  Probably not

But take that to a large database with thousands of users:  That 30 seconds per call could be a life saver!
Avatar of Jay Roy

ASKER

>>If you feel the data will benefit from it, create new indexes to try to eliminate the full scans

so creation of new index woud avoid the full table scan ?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jay Roy

ASKER

>>Do you think it would be faster to use an index for:
select first_name, last_name from mytable where gender='F';

No
So you sort of answered your own question: so creation of new index woud avoid the full table scan ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
not much I can add, but I enjoyed getting here...

the metric "users complaining" is particularly interesting,
don't they always complain anyway? - sorry, back to the topic

the fact that you are here to ask about performance indicates you care, which is great.

here's a reference for you:
The Oracle Optimizer Explain the Explain Plan