Solved

Update statement is running very very slow

Posted on 2013-12-26
11
493 Views
Last Modified: 2014-01-19
UPDATE store sd
     SET sd.UN_NBR = 'Y'
   WHERE sd.un_nbr = 'N'
     AND sd.DISTRO in (SELECT unique ref_field_3
                             FROM pix pt
                            WHERE pt.sku_id = sd.SKU_ID
                              AND pt.TRAN_TYPE = '620'
                              AND pt.TRAN_CODE = '01'
                              AND pt.ACTN_CODE = '02'
                              AND pt.proc_stat_code = 20)

Can I make any changes so that it runs faster.  There is index on distro , un_nbr  columns.

pix  table contains 12millions rows
store table contains 7 million rows
0
Comment
Question by:oraclescsa
11 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39740463
You can change the IN to EXISTS clause and remove the UNIQUE keyword. Also If the datatype of TRAN_TYPE, TRAN_CODE and ACTN_CODE is INT, you can remove the quotes around 620, 01 and 02. Check the performance now.
UPDATE store sd
     SET sd.UN_NBR = 'Y'
   WHERE sd.un_nbr = 'N'
     AND exists  (SELECT 1
                             FROM pix pt
                            WHERE pt.sku_id = sd.SKU_ID
                              AND pt.TRAN_TYPE ='620'
                              AND pt.TRAN_CODE = '01'
                              AND pt.ACTN_CODE = '02'
                              AND pt.proc_stat_code = 20
							  And sd.DISTRO = pt.ref_field_3)

Open in new window

0
 
LVL 23

Expert Comment

by:David
ID: 39740625
What kind of index do you have on sd.un_nbr (bitmap, leaf, etc.), and how do you update the tables' statistics?

Since you are updating the indexed column, I infer you are having overhead in updating the index when the change commits.  I might pursue modifying the index to defer validation.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39740638
The index "on distro , un_nbr  columns" of the store table doesn't help much, if at all for this update.

An index that could help dramatically would be on the five columns of the pix table that your sub-query references.

Whether it helps or not to remove the single quotes around your bind variable values depends on the datatypes of those columns.  If they are CHAR ot VARCHAR2, you need the single quotes.  If they are NUBMER, then the quotes should be removed.
 
Whether it helps (or not) to use an "EXISTS" sub-query instead of "IN" depends on whether those columns are indexed or not.  If not, I would expect the "IN" query to be better.  Also, your server and storage hardware plus your init parameters also affect the performance of statements like this.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 39740853
What is your Oracle version?

How many of the 7 million rows are getting updated?

Can you post the execution plan of the inner select by itself?

I'm thinking an index on the joined column, sku_id on both tables might help.

Try a MERGE instead of an update (syntax is in the docs and all over the site if you look around).

Here's the first example I came across on this site:
http://www.experts-exchange.com/Database/Oracle/Q_28316481.html


How often is pix updated or inserted into?  Have you thought about a materialized view (MV) of just the select?  Then you can use the MV in the update.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39741641
How often does this update occur/has to be done?! If it's just a "once in a month or quarter" work, you might want to set up a DBMS_JOB (or scheduler) for it. The job could process the whole update "with little chunks" (i.e. by adding ... and rownum <= 1000 to your query).

But, nevertheless, you should definetely check your indexes and table/index statistics and histograms.
You might also check the possibility of partitioning your table and/or your indexes...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:oraclescsa
ID: 39744392
Sorry was off for 2 days so could not reply to your queries.

@sharath123: I have already used your query but no improvements.

@dvz : I am using bitmap index for the column un_nbr

@mark_geer : Oracle version 10g Rel 2. I am having index on 4 columns except actn_code. As the columns datatype is varchar2 so cannot remove the single quotes on the column.

@slightwv : Oracle 10g R2. This update job runs every 1 hour and it is taking almost 30 mins - 45 mins depending on the number of lines to be inserted.

I am attaching execution plan for the update statement.
Execution-Plan.xlsx
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 39745359
I asked for the execution plan of the inner select not the update statement itself.

Not sure what generated that plan but I really don't like it.

Can you post the result from the following:
explain plan for 
SELECT ref_field_3
                             FROM pix pt
                            WHERE pt.sku_id = sd.SKU_ID
                              AND pt.TRAN_TYPE = '620'
                              AND pt.TRAN_CODE = '01'
                              AND pt.ACTN_CODE = '02'
                              AND pt.proc_stat_code = 20;

SELECT * FROM TABLE(dbms_xplan.display);

Open in new window


I also asked several additional questions that I do not see answers for.
0
 

Author Comment

by:oraclescsa
ID: 39747566
Attached is the explain plan for the inner query .

How many of the 7 million rows are getting updated?
between 40000-70000 rows

Can you post the execution plan of the inner select by itself?
Yes available in the attachment

I'm thinking an index on the joined column, sku_id on both tables might help.
Indexes are available on both tables at skuid columns

How often is pix updated or inserted into?  Have you thought about a materialized view (MV) of just the select?  Then you can use the MV in the update.
Pix is updated every 2 hours.
explain.xls
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39749199
>>Indexes are available on both tables at skuid columns

This contradicts what you said in your original post:
There is index on distro , un_nbr  columns.

We need all information.

>>Attached is the explain plan for the inner query .
>>between 40000-70000 rows

The inner query explain plan is only showing about 1300 rows and a cost of 2 with indexes being used.  Granted the cost number is made up and really doesn't mean anything but based on that execution plan, I don't see how we can make it faster.

Are you sure that was on the same database as the original row numbers posted?
0
 

Author Closing Comment

by:oraclescsa
ID: 39791847
Solution Helped me
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39792973
Can I ask why the "B" penalty grade?

Please review:
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44

B is the grade given for acceptable solutions, or a link to an acceptable solution. A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now