Home » RDBMS Server » Performance Tuning » Procedure taking long time to execute  () 1 Vote
Procedure taking long time to execute [message #306105] Wed, 12 March 2008 17:30 Go to next message
anandhi80
Messages: 19
Registered: December 2007
Location: Chennai
Junior Member
PROCEDURE get_missing_claimsstage_vals IS

/* this is the core version and without logging */

BEGIN
write_status('Start get missing claimsstage values');
write_status('Insert into table claimsstage_tmp');
INSERT /*+ append */ INTO claimsstage_tmp(SELECT NVL(cx.corn,'0'),
cx.batch_type,
cx.payer_id,
cx.destination,
cx.provider_id,
cx.provider_sub_id,
cx.submitter_id,
cx.acceptance_flg,
cx.returned_flg
FROM claimsstage cs,
CIDXWALK.CLAIM_ID_XWALK@ptdrc cx
WHERE cs.claim_tcn_id = cx.corn(+));
COMMIT;



The procedure, get_missing_claimsstage_vals, in the package takes over 6 hours to execute and I have never had it complete yet


Please advise if we need to tune the procedure. This procedure is embedded inside a package.
Re: Procedure taking long time to execute [message #306106 is a reply to message #306105] Wed, 12 March 2008 17:41 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ok, firstly, get an explain plan and post it here, with row counts from both tables.

Secondly, why use an outer join when you are not storing anything from cs where there is no cx.

You will just get tons of zeros in your table.
Re: Procedure taking long time to execute [message #307037 is a reply to message #306106] Mon, 17 March 2008 11:46 Go to previous messageGo to next message
anandhi80
Messages: 19
Registered: December 2007
Location: Chennai
Junior Member
please find the below information u asked for

select count(*) from claimsstage;

COUNT(*)
----------
124250




INSERT /*+ append */ INTO claimsstage_tmp(SELECT NVL(cx.corn,claimsstage_tmp_s.NEXTVAL),
cx.batch_type,
cx.payer_id,
cx.destination,
cx.provider_id,
cx.provider_sub_id,
cx.submitter_id,
cx.acceptance_flg,
cx.returned_flg
FROM claimsstage cs,
CIDXWALK.CLAIM_ID_XWALK@ptdrc cx
WHERE cs.daily_stats_load_flag = 'Y'
AND cs.claim_tcn_id = cx.corn);


Explain plan for the query.



Plan
INSERT STATEMENT CHOOSECost: 3,064,745 Bytes: 242,376,724 Cardinality: 1,954,651
5 LOAD AS SELECT
4 SEQUENCE STAGE.CLAIMSSTAGE_TMP_S
3 HASH JOIN Cost: 3,064,745 Bytes: 242,376,724 Cardinality: 1,954,651
1 TABLE ACCESS FULL STAGE.CLAIMSSTAGE Cost: 4,158 Bytes: 35,183,718 Cardinality: 1,954,651
2 REMOTE Cost: 183,474 Bytes: 17,734,036,380 Cardinality: 167,302,230




and its doing a full table scan of Claimsstage table.


Please help.


Re: Procedure taking long time to execute [message #307081 is a reply to message #306105] Mon, 17 March 2008 15:45 Go to previous messageGo to next message
shlomi_bt
Messages: 16
Registered: August 2005
Location: Canada
Junior Member

What indexes do you have on those two tables?
can you try doing the following and run the command again:
set autotrace traceonly explain statistics;
run the command and upload the result.
another option i would try is to trace it using 10046 level 12
in order to see if you're waiting for other factors such as net or
io ...

Re: Procedure taking long time to execute [message #307082 is a reply to message #307081] Mon, 17 March 2008 16:02 Go to previous messageGo to next message
anandhi80
Messages: 19
Registered: December 2007
Location: Chennai
Junior Member
When i execute this insert statment it is taking a long time to complete it. I will not be able to upload the results. it will run for more than 6 hours.

i am receiving this error message when i run this command.

INSERT /*+ append */ INTO claimsstage_tmp(SELECT NVL(cx.corn,claimsstage_tmp_s.NEXTVAL),
cx.batch_type,
cx.payer_id,
cx.destination,
cx.provider_id,
cx.provider_sub_id,
cx.submitter_id,
cx.acceptance_flg,
cx.returned_flg
FROM claimsstage cs,
CIDXWALK.CLAIM_ID_XWALK@ptdrc cx
WHERE cs.daily_stats_load_flag = 'Y'
AND cs.claim_tcn_id = cx.corn)


Error at line 1
ORA-02049: timeout: distributed transaction waiting for lock






table claimsstage has the following indexes.


CLAIMSSTAGE_CREATE_DT_NUK, CLAIMSSTAGE_DLYSTATLOADFLG_NUK, CLAIMSSTAGE_PAYER_NUK, CLAIMSSTAGE_PK, CLAIMSSTAGE_PK, CLAIMSSTAGE_PROVIDER_NUK, CLAIMSSTAGE_SUBMITTER_NUK


second table has the following indexes.


NUQ_CLAIM_ID_XWALK_1, NUQ_CLAIM_ID_XWALK_1, NUQ_CLAIM_ID_XWALK_2, NUQ_CLAIM_ID_XWALK_2, NUQ_CLAIM_ID_XWALK_3, NUQ_CLAIM_ID_XWALK_4, NUQ_CLAIM_ID_XWALK_5, NUQ_CLAIM_ID_XWALK_FN_5, XPKCLAIM_ID_XWALK
Re: Procedure taking long time to execute [message #307083 is a reply to message #306105] Mon, 17 March 2008 16:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Does this perform any differently?
INSERT /*+ append */ INTO claimsstage_tmp(SELECT NVL(cx.corn,claimsstage_tmp_s.NEXTVAL),
       cx.batch_type,
       cx.payer_id,
       cx.destination,
       cx.provider_id,
       cx.provider_sub_id,
       cx.submitter_id,
       cx.acceptance_flg,
       cx.returned_flg
       FROM CIDXWALK.CLAIM_ID_XWALK@ptdrc cx
       WHERE CX.CORN IN SELECT(cs.claim_tcn_id 
                          FROM claimsstage cs 
                         WHERE cs.daily_stats_load_flag = 'Y');
Re: Procedure taking long time to execute [message #307091 is a reply to message #307083] Mon, 17 March 2008 16:31 Go to previous messageGo to next message
anandhi80
Messages: 19
Registered: December 2007
Location: Chennai
Junior Member
It si giving the following error message when i try to take the explain plan and while executing too

INSERT /*+ append */ INTO claimsstage_tmp(SELECT NVL(cx.corn,claimsstage_tmp_s.NEXTVAL),
cx.batch_type,
cx.payer_id,
cx.destination,
cx.provider_id,
cx.provider_sub_id,
cx.submitter_id,
cx.acceptance_flg,
cx.returned_flg
FROM CIDXWALK.CLAIM_ID_XWALK@ptdrc cx
WHERE CX.CORN IN SELECT(cs.claim_tcn_id
FROM claimsstage cs
WHERE cs.daily_stats_load_flag = 'Y')
Error at line 1
ORA-00936: missing expression
Re: Procedure taking long time to execute [message #307092 is a reply to message #306105] Mon, 17 March 2008 16:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>WHERE cs.daily_stats_load_flag = 'Y');
should be
WHERE cs.daily_stats_load_flag = 'Y'));
Re: Procedure taking long time to execute [message #307093 is a reply to message #307092] Mon, 17 March 2008 16:43 Go to previous messageGo to next message
anandhi80
Messages: 19
Registered: December 2007
Location: Chennai
Junior Member
i tried that too, but it still says the same error but it say at line 1 missing expression.

INSERT /*+ append */ INTO claimsstage_tmp(SELECT NVL(cx.corn,claimsstage_tmp_s.NEXTVAL),
cx.batch_type,
cx.payer_id,
cx.destination,
cx.provider_id,
cx.provider_sub_id,
cx.submitter_id,
cx.acceptance_flg,
cx.returned_flg
FROM CIDXWALK.CLAIM_ID_XWALK@ptdrc cx
WHERE cx.CORN IN SELECT(cs.claim_tcn_id
FROM claimsstage cs
WHERE cs.daily_stats_load_flag = 'Y'))
Error at line 1
ORA-00936: missing expression
Re: Procedure taking long time to execute [message #307094 is a reply to message #307093] Mon, 17 March 2008 16:52 Go to previous messageGo to next message
anandhi80
Messages: 19
Registered: December 2007
Location: Chennai
Junior Member
is it possible to use hint to forcefully use the index.
Re: Procedure taking long time to execute [message #307108 is a reply to message #307094] Mon, 17 March 2008 18:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Hint is just a hint. CBO may or may not use it.
Re: Procedure taking long time to execute [message #307120 is a reply to message #307108] Mon, 17 March 2008 20:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You have told us that there are 124250 rows in the local table.

- How many rows are there in the remote table?
- How many of those do you think will be inserted by the statement?

I think the plan you have is as good as you are going to get. The remote table would have to monumentally huge before an indexed access path made sense. Since the table is remote, and indexed nested loops plan would mean 124250 round trips across the database link.

Ross Leishman
Re: Procedure taking long time to execute [message #307132 is a reply to message #307120] Mon, 17 March 2008 22:53 Go to previous messageGo to next message
anandhi80
Messages: 19
Registered: December 2007
Location: Chennai
Junior Member
- How many rows are there in the remote table?

A: COUNT(*)
----------
181930422


- How many of those do you think will be inserted by the statement?

A: I am not sure how many will be inserted

Re: Procedure taking long time to execute [message #307193 is a reply to message #307132] Tue, 18 March 2008 02:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Let me rephrase that:

For each row in CLAIMSSTAGE, how many matching rows are there on average in CIDXWALK.CLAIM_ID_XWALK?

Say there is on average just ONE matching row. That means you will insert around 124250 rows. In this case, you would be better forcing Oracle to perform a nested loops join on the remote database and just return the matching rows.

On the other hand, if there are on average 1000 matching rows, you would be picking up close to 100% of the remote table. In this case you already have the optimal plan.

Try this:
SELECT num_distinct
FROM   all_tab_columns@ptdrc
WHERE  table_name = 'CLAIM_ID_XWALK'
AND    owner = 'CIDXWALK'
AND    column_name = 'CORN'


If that returns NULL, then try this
SELECT /*+DRIVING_SITE(cx)*/ COUNT(DISTINCT corn)
FROM   CIDXWALK.CLAIM_ID_XWALK@ptdrc cx


Ross Leishman
Re: Procedure taking long time to execute [message #307233 is a reply to message #307081] Tue, 18 March 2008 03:50 Go to previous messageGo to next message
smrutiranjan
Messages: 3
Registered: March 2008
Location: mumbai
Junior Member
It is not a solution.
Re: Procedure taking long time to execute [message #307358 is a reply to message #307193] Tue, 18 March 2008 10:29 Go to previous messageGo to next message
anandhi80
Messages: 19
Registered: December 2007
Location: Chennai
Junior Member
SELECT num_distinct
FROM all_tab_columns@ptdrc
WHERE table_name = 'CLAIM_ID_XWALK'
AND owner = 'CIDXWALK'
AND column_name = 'CORN'


NUM_DISTINCT
------------
167302230


1 row selected.
Re: Procedure taking long time to execute [message #307440 is a reply to message #307358] Tue, 18 March 2008 22:00 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK, so CORN is reasonably unique (1-2 rows per key).

Since CLAIMSSTAGE will not have more than 124,000 different values of CLAIM_TCN_ID, it is possible that the join will not match more than (say) 200,000 or so rows in the remote table.

1. You don't want to bring across 180 million rows just to get 200,000 of them.

2. You don't want to perform 124,000 nested loops across the database link

That leaves one option: push the 124,000 rows across to the remote database and perform the join there.

Post the explain plan for the following query:
INSERT /*+ append driving_site(cx)*/ INTO claimsstage_tmp(
  SELECT NVL(cx.corn,'0'),
    cx.batch_type,
    cx.payer_id,
    cx.destination,
    cx.provider_id,
    cx.provider_sub_id,
    cx.submitter_id,
    cx.acceptance_flg,
    cx.returned_flg
FROM claimsstage cs,
    CIDXWALK.CLAIM_ID_XWALK@ptdrc cx
WHERE cs.claim_tcn_id = cx.corn(+));


Ross Leishman
Previous Topic: Database parameters for tuning
Next Topic: Simple query going for a full table scan
Goto Forum:
  


Current Time: Fri Dec 09 19:38:46 CST 2016

Total time taken to generate the page: 0.12015 seconds