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  |
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  |
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 |
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 #307082 is a reply to message #307081] |
Mon, 17 March 2008 16:02 |
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 |
 |
BlackSwan
Messages: 26766
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 |
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 #307093 is a reply to message #307092] |
Mon, 17 March 2008 16:43 |
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 #307120 is a reply to message #307108] |
Mon, 17 March 2008 20:50 |
rleishman
Messages: 3728
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 #307193 is a reply to message #307132] |
Tue, 18 March 2008 02:09 |
rleishman
Messages: 3728
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 #307440 is a reply to message #307358] |
Tue, 18 March 2008 22:00 |
rleishman
Messages: 3728
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
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:32:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
