| Performance Slow [message #194315] |
Thu, 21 September 2006 08:14  |
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 #194318 is a reply to message #194315] |
Thu, 21 September 2006 08:21  |
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 |
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 |
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 |
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 |
michael_bialik
Messages: 621
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
