Home » SQL & PL/SQL » SQL & PL/SQL » Insert much slower in PL/SQL than native SQL
Insert much slower in PL/SQL than native SQL [message #279266] Wed, 07 November 2007 09:34 Go to next message
Ignatius
Messages: 6
Registered: November 2007
Junior Member
Hi all, I'm new to PL/SQL and am running into a problem...

I need to insert about 1.2 million rows over a database link into a different schema each day. If I run the query in SQL+ through a regular INSERT statement, this takes about 3-4 minutes. If I run the same query as part of a PL/SQL package, it runs for 3 hours. The target table, volume of data, and database traffic are all the same under both scenarios, I'm sure there's something basic I'm missing but I'm kind of stumped.

The native SQL query (3-4 minutes):

 INSERT INTO	target_table
      SELECT	column1,
		column2,
		column3
		...
		sysdate
	FROM	source_table@dblink
       WHERE	load_period BETWEEN TO_DATE('07-NOV-2007','DD-MON-YYYY') AND TO_DATE('07-NOV-2007','DD-MON-YYYY');



The procedure (3 hours):

   PROCEDURE	procedure_name (in_lower_load_period IN NUMBER,
					 in_upper_load_period IN NUMBER) IS

		v_lower_load_period_date	target_table.load_period%TYPE;
		v_upper_load_period_date	target_table.load_period%TYPE;
		v_record_update_date		target_table.record_update_date%TYPE;

       BEGIN

		v_record_update_date:= sysdate;

		v_lower_load_period_date:= TO_DATE(in_lower_load_period,'YYYYMMDD');
		v_upper_load_period_date:= TO_DATE(in_upper_load_period,'YYYYMMDD');

-- Truncate target table:

EXECUTE IMMEDIATE 'TRUNCATE TABLE target_table';

--Insert records into target table:

 INSERT INTO	target_table
      SELECT	column1,
		column2,
		column3
		...
		v_record_update_date
	FROM	source_table@dblink
       WHERE	load_period BETWEEN v_lower_load_period_date AND v_upper_load_period_date;



Thanks in advance, any help would be appreciated...

Iggy

[Updated on: Wed, 07 November 2007 10:35]

Report message to a moderator

Re: Insert much slower in PL/SQL than native SQL [message #279267 is a reply to message #279266] Wed, 07 November 2007 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines above.

Post (using <code tags>) EXPLAIN PLAN from each case.
Re: Insert much slower in PL/SQL than native SQL [message #279281 is a reply to message #279266] Wed, 07 November 2007 10:37 Go to previous messageGo to next message
Ignatius
Messages: 6
Registered: November 2007
Junior Member
Sorry, fixed the formatting of the code...

I'm very new to PL/SQL and have no idea how to generate an explain plan for a procedure (vs. a native SQL query, which I know how to do). Sorry to be so needy, if someone can point me to a link as to how to do this I'll generate both explain plans...
Re: Insert much slower in PL/SQL than native SQL [message #279288 is a reply to message #279281] Wed, 07 November 2007 10:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am more curious to know why do you choose to use PL/SQL when you can do the same using plain SQL.

Regards
Raj

P.S : To identify performance bottleneck check this link. It explains step by step what needs to be done.
http://www.orafaq.com/forum/t/84315/94420/

[Updated on: Wed, 07 November 2007 10:51]

Report message to a moderator

Re: Insert much slower in PL/SQL than native SQL [message #279291 is a reply to message #279288] Wed, 07 November 2007 10:54 Go to previous messageGo to next message
Ignatius
Messages: 6
Registered: November 2007
Junior Member
Our admins don't want to call individual SQL commands for daily processes, they want to have shell scripts automatically make calls to packages/procedures and maintain everything there, rather than have someone manipulate the dates each day and run them manually...

To be honest, they're new to Oracle, is this a bad idea? On the one hand, it is more convenient to have everything in one 'wrapper' (there are a lot of processes like this running every day), but it sounds like there might be some performance disadvantages?

Thanks,
Iggy

[Updated on: Wed, 07 November 2007 10:55]

Report message to a moderator

Re: Insert much slower in PL/SQL than native SQL [message #279295 is a reply to message #279291] Wed, 07 November 2007 11:02 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hello,

In that case what you can do is either embed your sql in your .ksh and if that is not allowed then create a sql file and store your insert statements there and execute it... Will that solve your problem ?

Yeah it is sort of bad idea. I follow the mantra given by the guru (Thomas Kyte asktom.oracle.com). Mantra is

a) Do it in sql
b) If not possible then pl/sql
c) If not possible then java
d) If not possible then proc or Extern C
e) Still not possible then something is wrong or you are missing something. Rethink your design

Having said that if you are planning to group everything in a package it's a good practice at the same time it should be a manageable code. Single package should not have 100's of procedures then it is a monster and difficult to maintain. By all means refer the link which i mentioned earlier and try to identify why it is taking 3 hours to do the same job which you can do it in 3 minutes. I feel you are getting blocked by something. But it's a pure guess. So rather than guessing follow the link. It will explain how to produce trace files. Trace files will have enough information to tell you what's happening behind the scenes.

Also, Check the website i mentioned here... It's a huge knowledge base. It's like wikipedia for oracle.

Hope that helps.

Regards

Raj
Re: Insert much slower in PL/SQL than native SQL [message #279300 is a reply to message #279295] Wed, 07 November 2007 12:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think doing a straight insert from a pl/sql stored procedure counts as a 1) Do it in SQL in Tom's book, since he advocates using stored procedures to handle dml.

It should be, give or take a hsec, as fast as doing it from sqlplus.

Did you also do a truncate in sqlplus? And was the HWM as high as it was before the execution of the PL/SQL truncate?
Re: Insert much slower in PL/SQL than native SQL [message #279303 is a reply to message #279295] Wed, 07 November 2007 12:40 Go to previous messageGo to next message
Ignatius
Messages: 6
Registered: November 2007
Junior Member
Thanks Raj, I'll take a look at that link...

Frank, thanks for the response, I've done a truncate each time before attempting an insert while troubleshooting, so my high-water mark should be OK, or at least comparable each time (my target table is the only one in this tablespace)...

Here's another twist - in messing with the code I've found that the PL/SQL version is OK when I don't use a range of load_periods, ie if I just change the where clause in the PL/SQL from this:

       WHERE	load_period BETWEEN v_lower_load_period_date
	 AND 	v_upper_load_period_date;


...to this:

       WHERE	load_period = v_lower_load_period_date;


...everything runs in 3-4 minutes. However, as in the original code, doing a range of load_period with the date hardcoded inside the package runs fine also, ie:

       WHERE	load_period BETWEEN TO_DATE('07-NOV-2007','DD-MON-YYYY') 
	 AND 	TO_DATE('07-NOV-2007','DD-MON-YYYY');


Weird. In normal circumstances (100% of the runs to date), the lower/upper dates will be the same ('yesterday'), the range is just there to allow running of multiple days if needed for a one-off basis. I've at least got a fix for the short-term, but I'll keep digging to see if I can uncover anything, thanks everyone...

Iggy
Re: Insert much slower in PL/SQL than native SQL [message #279310 is a reply to message #279303] Wed, 07 November 2007 13:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Is target_table.load_period a DATE or a VARCHAR2 or NUMBER?
Re: Insert much slower in PL/SQL than native SQL [message #279311 is a reply to message #279303] Wed, 07 November 2007 14:19 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Now comes another interesting question.
Is the source_table partitioned on the load_period ?

Also try running your sql by using bind variables rather than hard coding the value and let us know how long the query is taking ?

Regards

Raj

P.S : Forgot to mention you can also use append hint to speed up your insert processing.

[Updated on: Wed, 07 November 2007 14:34]

Report message to a moderator

Re: Insert much slower in PL/SQL than native SQL [message #279314 is a reply to message #279310] Wed, 07 November 2007 14:47 Go to previous messageGo to next message
Ignatius
Messages: 6
Registered: November 2007
Junior Member
joy_division wrote on Wed, 07 November 2007 13:53

Is target_table.load_period a DATE or a VARCHAR2 or NUMBER?


It's a DATE in both the source and target tables; the purpose of this query is just to persist the day's data into a staging table; on subsequent procedures, the data is manipulated/aggregated up to a reporting table...Since it's just here to stage the data, the data types of 'target_table' and 'source_table' are exactly the same...

Thanks,
Iggy
Re: Insert much slower in PL/SQL than native SQL [message #279315 is a reply to message #279311] Wed, 07 November 2007 14:52 Go to previous messageGo to next message
Ignatius
Messages: 6
Registered: November 2007
Junior Member
S.Rajaram wrote on Wed, 07 November 2007 14:19

Now comes another interesting question.
Is the source_table partitioned on the load_period ?

Also try running your sql by using bind variables rather than hard coding the value and let us know how long the query is taking ?

Regards

Raj

P.S : Forgot to mention you can also use append hint to speed up your insert processing.


source_table is partitioned along load_period, and using a variable range within PL/SQL to get a count seems to run fine, ie

      SELECT	COUNT(*)
	INTO	v_counter
	FROM	source_table@dblink
       WHERE	load_period BETWEEN v_lower_load_period_date AND v_upper_load_period_date;


...returns a total in a couple of seconds, but attempting the insert with the same variable range is what takes the long time (although if I substitute a hard-coded date or a bind variable in its place, everything runs quickly).

I've also added the append hint to the variable range example, but it didn't make an appreciable difference (I killed it after 30 or so minutes). I guess it's OK because I've got a workaround, but it's definitely strange...

Thanks,
Iggy
Re: Insert much slower in PL/SQL than native SQL [message #279317 is a reply to message #279315] Wed, 07 November 2007 15:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search in asktom.oracle.com for BVP (Bind variable peeking). Also try running your sql statement after executing the following command on your sql session and do a tkprof on the trace file. Hopefully you should have an answer what is happening

alter session set events '10046 trace name context forever, level 12'


Regards

Raj
Re: Insert much slower in PL/SQL than native SQL [message #279347 is a reply to message #279317] Wed, 07 November 2007 20:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
As explained in the replies above, Oracle does not know what values you will supply for the dates when you use PL/SQL. This is equivalent in SQL*Plus to:

variable d1 varchar2
variable d2 varchar2

exec :d1 = '07-NOV-2007';
exec :d2 = '07-NOV-2007';

SELECT ...
...
WHERE	load_period BETWEEN TO_DATE(:d1,'DD-MON-YYYY') AND TO_DATE(:d2,'DD-MON-YYYY');

If you were to run the above, you would find that it too is slow.

The faster version is almost certainly using an index, and the slower version almost certainly not. A full table scan will be more efficient for large date ranges, and index scan more efficient for small ranges. Oracle chooses the FTS because it figures that there are more large date ranges than small dates ranges.

Bind-variable peeking pre-11g is an imperfect solution. It locks in a plan from the first time the SQL is parsed, then subsequent executions use the same plan regardless of whether the bind variables have changed.

If you have a single execution, this should not be a problem.

I think bind-variable peeking was introduced in 10g. If you are using an earlier version then it is unlikely to help. I'm also not sure whether it would work over a DB link.

You might consider using a hint. If you have an idea of how many rows will be affected, you can use the CARDINALITY hint:
SELECT /*+CARDINALITY(tab, 10000000)*/
FROM ....

If that doesn't work, you can "force" the index usage with an INDEX hint.

Ross Leishman
Re: Insert much slower in PL/SQL than native SQL [message #279412 is a reply to message #279347] Thu, 08 November 2007 03:21 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Ross,

BVP is there from 9i onwards.

Regards

Raj
Previous Topic: Dynamic PIVOT
Next Topic: search Non-ascii
Goto Forum:
  


Current Time: Sun Dec 04 04:32:22 CST 2016

Total time taken to generate the page: 0.15033 seconds