| Stored procedure taking long time to run [message #669629] |
Thu, 03 May 2018 22:03  |
 |
senmng
Messages: 22
Registered: April 2018
|
Junior Member |
|
|
Hi - When i tried to run a stored procedure to insert records from the remote database tables to the local database tables it's taking too long time.
CREATE OR REPLACE PROCEDURE proc1 as
begin
insert into table1
select distinct
table2.column1,
table3.column2,
table4.columnn2,
table5.column3
from table6.dblink
join table2@dblink on <condition1>
join table3@dblink on <condition2>
join table4@dblink on <condition3>
join table5 on <condition4>
end;
/
here the table5 is residing in local database and all others are residing in remote database.
row count of table6 is 9000
rowcount of table2 is 3 million
rowcount of table3 is 4 million
rowcount of table4 is 1 million
How do we optimize and fine tune in such a way to reduce the running time of stored proc?
|
|
|
|
|
|
| Re: Stored procedure taking long time to run [message #669647 is a reply to message #669630] |
Fri, 04 May 2018 13:58  |
|
senmng
Messages: 22
Registered: April 2018
|
Junior Member |
|
|
Here are the DDL details and explain plan report-
Table 1-
CREATE TABLE Table1
(
col1 VARCHAR2(128),
col2 VARCHAR2(128),
col3 VARCHAR2(256),
col4 VARCHAR2(256),
col5 NUMBER(10),
col6 VARCHAR2(256 CHAR),
col7 DATE,
);
CREATE INDEX index1 ON table1 (col5);
CREATE INDEX index2 ON table1 (col3, col4);
-
Attachment: EP1.jpg
(Size: 127.19KB, Downloaded 2516 times)
|
|
|
|
| Re: Stored procedure taking long time to run [message #669702 is a reply to message #669647] |
Tue, 08 May 2018 09:36 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Try using a driving site hint
select /*+ DRIVING_SITE (TABLE6) */ distinct
table2.column1,
table3.column2,
table4.columnn2,
table5.column3
from table6@dblink
join table2@dblink on <condition1>
join table3@dblink on <condition2>
join table4@dblink on <condition3>
join table5 on <condition4>
|
|
|
|
| Re: Stored procedure taking long time to run [message #669703 is a reply to message #669647] |
Tue, 08 May 2018 10:17 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
senmng wrote on Fri, 04 May 2018 11:58Here are the DDL details and explain plan report-
Table 1-
CREATE TABLE Table1
(
col1 VARCHAR2(128),
col2 VARCHAR2(128),
col3 VARCHAR2(256),
col4 VARCHAR2(256),
col5 NUMBER(10),
col6 VARCHAR2(256 CHAR),
col7 DATE,
);
CREATE INDEX index1 ON table1 (col5);
CREATE INDEX index2 ON table1 (col3, col4);
stop posting invalid SQL!
trailing (rightmost) comma on " col7 DATE," prevents statement successful execution!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
