Home » RDBMS Server » Performance Tuning » Stored procedure taking long time to run (Oracle 11.2, Linux v6)
Stored procedure taking long time to run [message #669629] Thu, 03 May 2018 22:03 Go to next message
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 #669630 is a reply to message #669629] Thu, 03 May 2018 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof
3) EXPLAIN PLAN
Re: Stored procedure taking long time to run [message #669647 is a reply to message #669630] Fri, 04 May 2018 13:58 Go to previous messageGo to next message
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 2022 times)
Re: Stored procedure taking long time to run [message #669702 is a reply to message #669647] Tue, 08 May 2018 09:36 Go to previous messageGo to next message
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 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
senmng wrote on Fri, 04 May 2018 11:58
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);
stop posting invalid SQL!
trailing (rightmost) comma on " col7 DATE," prevents statement successful execution!
Previous Topic: Update Query Performance
Next Topic: ORA-07445: exception encountered: core dump [audplsfailure()+54]
Goto Forum:
  


Current Time: Thu Mar 28 12:51:37 CDT 2024