Home » SQL & PL/SQL » SQL & PL/SQL » Performance Slow
Performance Slow [message #194315] Thu, 21 September 2006 08:14 Go to next message
jkkandola
Messages: 5
Registered: September 2006
Junior Member
I am running oracle 9.2 on windows xp and having one table with 2 million rows. I am running a pl/sql procedure to process rows and its running really slow. Can you help me this. I already analysed the table. What else i can do?


I am attaching the pl/sql information:


I run same statement on sql sheet, it runs in 29 seconds. but i want to reduce even this time even its possible.

Jaz

[Updated on: Thu, 21 September 2006 10:49]

Report message to a moderator

Re: Performance Slow [message #194317 is a reply to message #194315] Thu, 21 September 2006 08:20 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
With information as lacking as that, I can offer no help.
Re: Performance Slow [message #194318 is a reply to message #194315] Thu, 21 September 2006 08:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Paraphrase as:
Quote:

I'm was driving my car, and I went 200 miles. It took me longer than I thought it should. I filled the tank with fuel before I started. What else can I do?


Come on.
There is almost no useful information in your post at all.

For Starters:
If the procedure fits on a page, post it, otherwise post a synopsis.
Let us know about the tables involved
Which bits of the procedure are taqking the time
Did it always run slowly, or has it just started?
Re: Performance Slow [message #194320 is a reply to message #194315] Thu, 21 September 2006 08:47 Go to previous messageGo to next message
jkkandola
Messages: 5
Registered: September 2006
Junior Member
I embedded the code. The only message i got is database connected. there is no error but code seems running. What information you need?

Re: Performance Slow [message #194321 is a reply to message #194320] Thu, 21 September 2006 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does the function get_scheduled_dt do?

Also, in the OP you now say that the code takes 29 seconds to run, but in the last post you imply that the code is still running.

How long does this code take to execute?
Re: Performance Slow [message #194347 is a reply to message #194315] Thu, 21 September 2006 10:35 Go to previous messageGo to next message
jkkandola
Messages: 5
Registered: September 2006
Junior Member
create or replace FUNCTION get_scheduled_dt(mfg_duns IN gmcc_capacity_header.mfg_duns_nbr%TYPE, part_gp IN VARCHAR2) RETURN DATE AS

/* Gets Schedule date from table so_scheduled capacity for a given part or group*/

type dynamic_rc IS ref CURSOR;
v_rc dynamic_rc;

dyn_query_string VARCHAR2(1000);

v_sched_genrtn_dt so_scheduled_capacity.sched_genrtn_dt%TYPE;

BEGIN


dyn_query_string := 'select SCHED_GENRTN_DT from so_scheduled_capacity where ticket_id is null and' || ' so_scheduled_capacity.CAPACITY_GRP_CD= :part_gp' || ' and so_scheduled_capacity.MFG_DUNS_NBR= :mfg_duns ';

OPEN v_rc FOR dyn_query_string USING part_gp,
mfg_duns;

FETCH v_rc
INTO v_sched_genrtn_dt;

close v_rc;
RETURN v_sched_genrtn_dt;



END;



Query 'select CAPACITY_GRP_CD, MFG_DUNS_NBR,min(WKLY_ADJ_OVERRIDE_CAPCTY_QTY),min(SCHED_GENRTN_DT) from so_scheduled_capacity where ticket_id is null group by CAPACITY_GRP_CD, MFG_DUNS_NBR;' takes 29 seconds to execute.



Re: Performance Slow [message #194750 is a reply to message #194315] Mon, 25 September 2006 02:15 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Your query is:

'select SCHED_GENRTN_DT from so_scheduled_capacity where ticket_id is null and' || ' so_scheduled_capacity.CAPACITY_GRP_CD= :part_gp' || ' and so_scheduled_capacity.MFG_DUNS_NBR= :mfg_duns '

1. Why do you need dynamic SQL at all? there is nothing dynamic here?

2. Try

CREATE INDEX ... ON so_scheduled_capacity (CAPACITY_GRP_CD, MFG_DUNS_NBR ).

3. How many rows ( out of 2M rows in your table) have "ticket_id is null " ?

If the number is very low consider using function based index:
CREATE INDEX ... ON so_scheduled_capacity (CAPACITY_GRP_CD, MFG_DUNS_NBR, NVL(ticket_id,-1) )

and rewriting your query:

'select SCHED_GENRTN_DT from so_scheduled_capacity where NVL(ticket_id, -1) = -1 and' || ' so_scheduled_capacity.CAPACITY_GRP_CD= :part_gp' || ' and so_scheduled_capacity.MFG_DUNS_NBR= :mfg_duns '

HTH.


Re: Performance Slow [message #194945 is a reply to message #194750] Tue, 26 September 2006 05:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The basic problem is that for every row in your table you are running the get_scheduled_dt function which goes away and does some dynamic sql.
If you do this 2,000,000 times, it's going to be slow.

As the previous poster said, lose the dynamic sql, or ideally stop using that funtion entirely and embed the Sql into the outer query.
Previous Topic: Required urgent solution
Next Topic: update a row with object
Goto Forum:
  


Current Time: Fri Dec 09 11:53:30 CST 2016

Total time taken to generate the page: 0.09029 seconds