OraFAQ Forum: Performance Tuning » Update Statement is too slow

Home » RDBMS Server » Performance Tuning » Update Statement is too slow

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update Statement is too slow [message #290413]

Fri, 28 December 2007 15:05

ashwin_tampa
Messages: 40
Registered: October 2005

Member

Hi Guys,

Following update statement is running extremely slow.

When it run for 121 records it takes following elapsed time.

SQL> UPDATE ADADANI.gtt_bh_rx@ODSDEV t
 2    SET (DEA,LICENSE,RX_NUM,PHARM_ID) = (
 3    SELECT rx_detail.prov_dea_no AS dea,
 4           SUBSTR (NVL (prov_master.user_id1, prov_credential.license_1),1,10) AS license,
 5           rx_detail.prescription_number AS rx_num,
 6           SUBSTR (rx_detail.pharmacy_number, 1, 7) AS pharm_id
 7      FROM wcdba.rx_detail@jdpprodx
 8           LEFT JOIN doris.prov_master@diamdev0     ON (rx_detail.pres_seq_prov_id = prov_master.seq_prov_id)
 9           LEFT JOIN doris.prov_credential@diamdev0 ON (rx_detail.prov_dea_no = prov_credential.dea_license)
10  WHERE rx_detail.claim_ref_number  = t.CLAIM_REF_NUM
11      and rx_detail.seq_memb_id  = t.SEQ_MEMB_ID
12       AND rx_detail.prov_dea_no IS NOT NULL
13       and rownum = 1)
14 /

121 rows updated.

Elapsed : 00:03:16.06
SQL>

But when it runs for all the records for a given quarter which has about 67,795 records
It runs forever! or may be about 30 hours if we see the time taken by 121 records and if I run it to finish!

Explain Plan
============
UPDATE STATEMENT REMOTE  CHOOSE Cost: 2  Bytes: 108  Cardinality: 1  					
	8 UPDATE ADADANI.GTT_BH_RX 				
	1 TABLE ACCESS FULL ADADANI.GTT_BH_RX Cost: 2  Bytes: 108  Cardinality: 1  			
	7 COUNT STOPKEY  			
	6 NESTED LOOPS OUTER  Cost: 192  Bytes: 233  Cardinality: 1  		
	4 NESTED LOOPS OUTER  Cost: 12  Bytes: 155  Cardinality: 1  	
	2 REMOTE  Cost: 11  Bytes: 118  Cardinality: 1  
	3 REMOTE  Cost: 1  Bytes: 37  Cardinality: 1  
	5 REMOTE  Cost: 180  Bytes: 234  Cardinality: 3  	

INDEXES ALREADY EXISTS ON :
===========================
         COLUMN_NAME       TABLE_NAME 

      1. SEQ_MEMB_ID    wcdba.rx_detail@jdpprodx
      2. SEQ_PROV_ID    doris.prov_master@diamdev0
      3. SEQ_PROV_ID    doris.prov_master@diamdev0
      4. SEQ_MEMB_ID    ADADANI.gtt_bh_rx
      5. CLAIM_REF_NUM  ADADANI.gtt_bh_rx

few more info:
==============
These tables and indexes are last analyzed on 12/23/2007.
TABLE to update i.e. ADADANI.gtt_bh_rx has got only 67,795 records
TABLE : rx_detail contains millions of rows.

Any suggestion, hints, guides,direction will be greatly appreciated!

Thanking you,

[Updated on: Fri, 28 December 2007 15:10]

Report message to a moderator

Re: Update Statement is too slow [message #290418 is a reply to message #290413]

Fri, 28 December 2007 15:51

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You are updating a table in 1 database based on the contents of 3 other tables in 3 separate databases. NONE OF WHICH IS THE DATABASE YOU ARE CONNECTED TO!!!!

How were you hoping Oracle would do this?

You need to get the data from the other 3 databases into the same database as the updated table. Investigate Materialized Views to replicate these tables.

Now all of the data is in one place (phew!). If you are going to submit the statement from another database, you will need a DRIVING_SITE hint to force execution of the SQL onto the remote database.

For the number of rows you are updating, you should be using MERGE instead of a subquery expression in the SET clause. Sub-query expressions nest, meaning they run a SEPARATE SQL for every row updated. MERGE can do it with a single SQL.

Ross Leishman

Report message to a moderator

Re: Update Statement is too slow [message #290476 is a reply to message #290413]

Sat, 29 December 2007 01:53

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

I agree with Ross.

Anyway - do you have an index on RX_DETAIL table with claim_ref_number and seq_memb_id as leading columns?

Michael

[Updated on: Sat, 29 December 2007 01:53]

Report message to a moderator

Re: Update Statement is too slow [message #290981 is a reply to message #290418]

Wed, 02 January 2008 08:15

ashwin_tampa
Messages: 40
Registered: October 2005

Member

Hello Ross! and Hello Michael!

Wish You Happy New Year!

Thanks a lot for your replies.

Now I got connected to ODSDEV and running following
query with your suggestion of using DRIVING_SITE :


UPDATE /*+ DRIVING_SITE(rx_detail) */ ADADANI.BH_RX_11_EAS_9 t
     SET (DEA,LICENSE,RX_NUM,PHARM_ID) = (
     SELECT rx_detail.prov_dea_no AS dea,
            SUBSTR (NVL (prov_master.user_id1, prov_credential.license_1),1,10) AS license,
            rx_detail.prescription_number AS rx_num,
            SUBSTR (rx_detail.pharmacy_number, 1, 7) AS pharm_id
       FROM wcdba.rx_detail@jdpprod
            LEFT JOIN doris.prov_master@diamdev0     ON (rx_detail.pres_seq_prov_id = prov_master.seq_prov_id)
            LEFT JOIN doris.prov_credential@diamdev0 ON (rx_detail.prov_dea_no = prov_credential.dea_license)
WHERE rx_detail.claim_ref_number  = t.CLAIM_REF_NUM
      and rx_detail.seq_memb_id  = t.SEQ_MEMB_ID
       AND rx_detail.prov_dea_no IS NOT NULL
       and rownum = 1)

but It runs for the same time with or without above hint!

I have tried following MERGE statement but the plan is even costlier than above statement:


MERGE INTO ADADANI.BH_RX_11_EAS_9 b
USING (
   SELECT   rx_detail.prov_dea_no AS dea,
            SUBSTR (NVL (prov_master.user_id1, prov_credential.license_1),1,10) AS license,
            rx_detail.prescription_number AS rx_num,
            SUBSTR (rx_detail.pharmacy_number, 1, 7) AS pharm_id,
            rx_detail.SEQ_MEMB_ID AS SEQ_MEMB_ID,
            rx_detail.CLAIM_REF_NUMBER AS CLAIM_REF_NUMBER
       FROM wcdba.rx_detail@jdpprod
            LEFT JOIN doris.prov_master@diamdev0     ON (rx_detail.pres_seq_prov_id = prov_master.seq_prov_id)
            LEFT JOIN doris.prov_credential@diamdev0 ON (rx_detail.prov_dea_no = prov_credential.dea_license)
WHERE  rx_detail.prov_dea_no IS NOT NULL   and rownum = 1) e
ON ( e.claim_ref_number  = b.CLAIM_REF_NUM  and e.seq_memb_id  = b.SEQ_MEMB_ID)
WHEN MATCHED THEN
  UPDATE SET b.DEA = e.DEA,b.LICENSE = e.LICENSE ,b.RX_NUM = e.RX_NUM,b.PHARM_ID = e.PHARM_ID
WHEN NOT MATCHED THEN
  INSERT (b.LICENSE) VALUES (null)

I have to try index on RX_DETAIL for CLAIM_REF_NUMBER as hinted by Micheal.

Please guide.

Thanking you both.

[Updated on: Wed, 02 January 2008 08:38]

Report message to a moderator

Re: Update Statement is too slow [message #291046 is a reply to message #290413]

Wed, 02 January 2008 17:22

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

when dealing with data that lives on several different databases, a main concern should be understanding how data is moved around bewteen databases to get the job done. These days, one would expect that Oracle understands this, but let us point out some details (maybe obvious details to some), and see where it takes us.

1) you are updating rows at database odsdev.
2) you are reading data from databases: jdpProdx and diAmdev0.
3) you are updating all rows on table AdAdAni.gtt_bh_rx@odsdev.

Silly as it is to point it out, data must move between these databases ending eventually at the updated database odsdev, in order for this update to get done. Most times, the best course of action is to move the least amount of data across database links.

#3 above is interesting because it suggests that there is little elimination of rows going to happen and thus you will likely be visiting most rows on all tables. If this is so, then the best course is usually to move the smallest tables to a single site, and let oracle join them together with HASH JOIN and temp segment scans. If we are lucky the update site will contain the largest rowset and thus we won't have to go back to it when we are done. This is of course just a gross concept at best. It may not be at all true for what you need, but it is a place to start.

Additionally, given the idea that moving the smallest amount of data around, usually results in best performance of distributed queries, can you tell us something about these tables? Specifically:

1) number of rows in each table
2) average row size of each table
3) number of bytes in each table's data segment

With this information, it may become obvious, how you should seek to move data, assuming Oracle has not itself figured this out for us; which in fact we hope it would have done so but who knows.

You may in the interim wish to try these indexes:

wcdba.rx_Detail@jdpProdx          (
 Claim_ref_Number
,seq_Memb_Id
,Prov_dea_No
,pres_seq_Prov_Id
,Prov_dea_No
,Prescription_Number
,Pharmacy_Number)

DorIs.Prov_Master@diAmdev0        (seq_Prov_Id,User_Id1)

DorIs.Prov_Credential@diAmdev0    (dea_License,License_1)

These indexes should reduce your query to index only access which might account for something. However, it is no excuse for not understanding the details of your distributed operation, so get us the numbers asked for above please.

Good luck, Kevin

Report message to a moderator

Re: Update Statement is too slow [message #291238 is a reply to message #291046]

Thu, 03 January 2008 08:59

ashwin_tampa
Messages: 40
Registered: October 2005

Member

Thanks a lot Kevin,

for this deep dive on my question.
You are absolutly right that to know
actual moving of data between remote database is critical.

Here is what I could gether information:


TABLE_NAME       ROWS                      AVG_ROW_LEN           SIZE_IN_BYTES


gtt_bh_rx       This is a GTT but it will have 67,795 records while it get populate.

rx_detail      +136 Million(by partitions)      162              could not see in statics/size of table    

prov_master     253,044                         203              could not see in statics/size of table    

prov_credential 155,804                         130              25,165,824

Please advise or if need more, let me know.

Thanking you again.

Report message to a moderator

Re: Update Statement is too slow [message #291281 is a reply to message #290413]

Thu, 03 January 2008 10:53

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

great info, thanks,

So... looking at the number of rows and row lengths, you should be able to answer these questions:

1) where do you think the best place to do all your joins will be given the update you are trying to do?

2) why do you think this?

Also, given these rowcounts, the update you are trying to do, if all data was at only one site, should not take hours and hours to complete. I am thinking maybe 20 minutes tops. OK this is a gross guess, but based on my experiences, updating 70 thousand rows should be pretty fast.

... (waiting for you to think about the questions above) ...
... (waiting for you to think about the questions above) ...
... (waiting for you to think about the questions above) ...

OK, so now that you have thought about the questions above and have your own answer, here is what I might try if my goal was to get this update to go real fast.

create table temp_prov_master
nologging
as
select distinct seq_Prov_Id,User_Id1
from DorIs.Prov_Master@diAmdev0
/

create unique index temp_i1 on temp_prov_master (seq_Prov_Id,User_Id1)
nologging
/

--
-- you can even try using a GTT for these tables
-- possibly "organization index" tables too if you want
-- and doing inserts rather than "create table as" in order to avoid the implicit commits
--

create table temp_Prov_Credential
nologging
as
select distinct dea_License,License_1
from DorIs.Prov_Credential@diAmdev0
/

create unique index temp_i1 on temp_Prov_Credential (dea_License,License_1)
nologging
/

--
-- here is where it gets a little interesting
--

create table temp_gtt_bh_rx
nologging
as
select *
from ADADANI.gtt_bh_rx@ODSDEV
/

create unique index temp_i3 on  temp_gtt_bh_rx (whatever the pK is, you didn't say)
nologging
/

create index temp_i3 on wcdba.rx_detail
(
 Claim_ref_Number
,seq_Memb_Id
,Prov_dea_No
,pres_seq_Prov_Id
,Prov_dea_No
,Prescription_Number
,Pharmacy_Number
)
/

UPDATE temp_gtt_bh_rx t
  SET (DEA,LICENSE,RX_NUM,PHARM_ID) = (
  SELECT rx_detail.prov_dea_no AS dea,
         SUBSTR (NVL (temp_prov_master.user_id1, temp_prov_credential.license_1),1,10) AS license,
         rx_detail.prescription_number AS rx_num,
         SUBSTR (rx_detail.pharmacy_number, 1, 7) AS pharm_id
    FROM wcdba.rx_detail
         LEFT JOIN temp_prov_master     ON (rx_detail.pres_seq_prov_id = temp_prov_master.seq_prov_id)
         LEFT JOIN temp_prov_credential ON (rx_detail.prov_dea_no = temp_prov_credential.dea_license)
WHERE rx_detail.claim_ref_number  = t.CLAIM_REF_NUM
    and rx_detail.seq_memb_id  = t.SEQ_MEMB_ID
     AND rx_detail.prov_dea_no IS NOT NULL
     and rownum = 1)
/

--
-- switching back to the original location
--

UPDATE temp_gtt_bh_rx x
  SET (DEA,LICENSE,RX_NUM,PHARM_ID) =
      (
        select DEA,LICENSE,RX_NUM,PHARM_ID
        from temp_gtt_bh_rx@jdpProdx y
        where y.pk = x.pk
      )
/

if you are following the sequence of events above, then you get the basic idea behind the plan. Wether or not we actually have to do all these steps will depend on what version of oracle you are using and how comfortable you are with letting Oracle handle the distributed movement of data. But the basic intent should be clear.

If you are still confused, then ask yourself these questions:

1) where did I put all the data?
2) why did I pick this spot?

We move the smallest amount of data possible to one common location, do an update at this common location, then move results of this update to our destination location, and update are real data. Yes, this actually means we update the same data twice in a sense, but in the end, you should find this basic approach much better than what you have now even if there are lots of steps involved. The net result is two things:

1) smallest amount of data moved around
2) smallest amount of network traffic created

I should point out that ideally, Oracle should be able to figure out all these basic steps on its own and do the work for us. And in many distributed scenarios it does. But even so, we still sometimes need to hand feed certain operations, and this may be one of them.

So, what do you think? do you get the basic idea?

Kevin

Report message to a moderator

Re: Update Statement is too slow [message #291304 is a reply to message #291281]

Thu, 03 January 2008 14:23

ashwin_tampa
Messages: 40
Registered: October 2005

Member

Kevin,

I have no word for you explanations.
It is totally striking!
It is totally understandable now.

Literally you walk me through as how the operation works.

Your efforts are much much appreciated!

I will get back to you once I perform the above steps.

Again thank you very much indeed.

Report message to a moderator

Previous Topic:	Group by clause
Next Topic:	11g SQL PERFORMANCE ANALYZER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:12:54 CDT 2024