Avatar of royjayd
royjayd
 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
Oracle Database

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
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'?
Sean Stuber

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.
sventhan

What is the issue?

Is your merge running slow?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
royjayd

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
slightwv (䄆 Netminder)

>>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.
slightwv (䄆 Netminder)

>>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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
royjayd

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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
royjayd

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

No
slightwv (䄆 Netminder)

So you sort of answered your own question: so creation of new index woud avoid the full table scan ?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

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