Home » SQL & PL/SQL » SQL & PL/SQL » simple program taking 2 hrs to execute..plz help
icon8.gif  simple program taking 2 hrs to execute..plz help [message #218787] Sat, 10 February 2007 04:45 Go to next message
junaidsystems
Messages: 30
Registered: June 2006
Member
I have 65000 recs in table_1 and 65000 in table_2. it takes 2 hours to execute.

declare

curosr table1_cur is select column_a from Table_1;

begin

for tab_rec in table1_cur
loop

select col_b,col_c from table_2 where table_rec.column_a between to_number(col_b) and to_number(col_c);

... some operations....
... some operations....

end;

end loop;

index on table_2 which is :

create index large_numbers on table_2 (to_number(col_b),to_number(col_c))

restriction: I have to pick records from table_1 one by one in order to perform some operations on each rec.

your comments are appriciated...

[Updated on: Sat, 10 February 2007 04:47]

Report message to a moderator

Re: simple program taking 2 hrs to execute..plz help [message #218790 is a reply to message #218787] Sat, 10 February 2007 05:21 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
Check the QUERY_REWRITE_ENABLED parameter in your init.ora file...it should be set on TRUE, on Oracle 9i ( QUERY_REWRITE_ENABLED = TRUE )..otherwise your index won't work because as i see you use a function-based-index...this may be one solution...if you are using Oracle 10g check the proper documentation for function-based-indexes.
good luck !

P.S. also check QUERY_REWRITE_INTEGRITY=TRUSTED in your init.ora

[Updated on: Sat, 10 February 2007 05:24]

Report message to a moderator

Re: simple program taking 2 hrs to execute..plz help [message #218801 is a reply to message #218790] Sat, 10 February 2007 08:12 Go to previous messageGo to next message
junaidsystems
Messages: 30
Registered: June 2006
Member
No improvement

both paarameters are set to

QUERY_REWRITE_INTEGRITY=TRUSTED
QUERY_REWRITE_ENABLED = TRUE
Re: simple program taking 2 hrs to execute..plz help [message #218821 is a reply to message #218801] Sat, 10 February 2007 15:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you can blindly do a to_number on those columns without getting an error, change the datatype to number!
Re: simple program taking 2 hrs to execute..plz help [message #218822 is a reply to message #218790] Sat, 10 February 2007 18:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
user82 wrote on Sat, 10 February 2007 22:21
Check the QUERY_REWRITE_ENABLED parameter in your init.ora file...it should be set on TRUE, on Oracle 9i ( QUERY_REWRITE_ENABLED = TRUE )..otherwise your index won't work because as i see you use a function-based-index...this may be one solution...if you are using Oracle 10g check the proper documentation for function-based-indexes.
good luck !

P.S. also check QUERY_REWRITE_INTEGRITY=TRUSTED in your init.ora


Rubbish. QUERY_REWRITE_ENABLED allows Oracle to use MVs to resolve queries on base tables. Nothing to do with Function-based indexes.

There are two problems in this program.

1. Range based Predicates

The predicate where table_rec.column_a between to_number(col_b) and to_number(col_c) is trying to access on two colums using a range. The index you created is OK, but once you range-scan on an indexed value [to_number(col_b)], subsequent columns in the index will only be used as filters; they will not be used in the scan.

For example, if col_b and col_c included ranges 0-10, 11-20, ... 649,990-650,000, and you wanted to lookup where 325,000 between to_number(col_b) and to_number(col_c), then it would scan straight to 325,000-325,010 but then read ALL OF THE REMAINING ROWS up to 650,000 rejecting each one in turn.

What's happening with your query is that you are filtering an average of 325000 rows 650000 times. 2 hours is not bad!

This problem is described in more detail here.

Just add AND ROWNUM = 1 to the query and it will stop as soon as t gets the first matching row.

If you want to tun the job some more, you will need to move on to the suggestion below. If you do that, you wont be able to use the ROWNUM fix any more; the link above has two other suggestions for when the range lookup is part of a table join.

2. Cursors within cursors.

Writing a cursor (or a SQL, or a DML - they are all cursors) within a cursor loop is horribly inefficient. See here for more details and alternatives.

Ross Leishman
Re: simple program taking 2 hrs to execute..plz help [message #218830 is a reply to message #218787] Sat, 10 February 2007 23:33 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


What are the details of the some operations that you are doing ?

Perhaps you can avoid the PLSQL loop itself .

Srini.
Re: simple program taking 2 hrs to execute..plz help [message #218841 is a reply to message #218787] Sun, 11 February 2007 05:10 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post explain plan of your inner statement.
Re: simple program taking 2 hrs to execute..plz help [message #218924 is a reply to message #218830] Mon, 12 February 2007 01:59 Go to previous messageGo to next message
junaidsystems
Messages: 30
Registered: June 2006
Member
user: srinivnp wrote on Sat, 10 February 2007 23:33
What are the details of the some operations that you are doing ?

Perhaps you can avoid the PLSQL loop itself .
Srini.
----------------------
the operations inside the loop are simple validations on each filed and errors for each filed and recrods are written seperately line by line on a flat file using utl utility.

point to be noted... without the problem query (given in my first message), it takes only 15 seconds to complete the procedure.
Re: simple program taking 2 hrs to execute..plz help [message #218925 is a reply to message #218822] Mon, 12 February 2007 02:04 Go to previous messageGo to next message
junaidsystems
Messages: 30
Registered: June 2006
Member
user rleishman wrote on Sat, 10 February 2007 18:49

Just add AND ROWNUM = 1 to the query and it will stop as soon as t gets the first matching row.
---------------------------

no instant improvement after adding the above rownum criteria..

but your analysis seems to be very right in this situation, i m going to read your given url reference, which looks very useful...

lets hope for the best.. many thanks for the advice.

[Updated on: Mon, 12 February 2007 02:13]

Report message to a moderator

Re: simple program taking 2 hrs to execute..plz help [message #218927 is a reply to message #218841] Mon, 12 February 2007 02:11 Go to previous messageGo to next message
junaidsystems
Messages: 30
Registered: June 2006
Member
user michael_bialik wrote on Sun, 11 February 2007 05:10

Post explain plan of your inner statement.
----------------------------------

SELECT STATEMENT, GOAL = ALL_ROWS Cost=1 Cardinality=1 Bytes=1020 Optimizer=ALL_ROWS IO cost=1
TABLE ACCESS BY INDEX ROWID Object owner=MIGRATION Object name=AD_TMP_SCRIP_MIGRATION Cost=1 Cardinality=1 Bytes=1020 Optimizer=ANALYZED IO cost=1
INDEX RANGE SCAN Object owner=MIGRATION Object name=AD_TMP_SCRIP_MIGRATION_IND1 Cost=1 Cardinality=1 Optimizer=ANALYZED IO cost=1

[Updated on: Mon, 12 February 2007 02:12]

Report message to a moderator

Re: simple program taking 2 hrs to execute..plz help [message #219084 is a reply to message #218927] Mon, 12 February 2007 20:22 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Woops. Forgot to mention: for the ROWNUM = 1 suggestion to work, you will need to swap the columns in the index:

create index large_numbers
on table_2 (to_number(col_c),to_number(col_b))

Don't forget to gather stats after the rebuild.

Ross Leishman
Re: simple program taking 2 hrs to execute..plz help [message #219437 is a reply to message #218787] Wed, 14 February 2007 07:00 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
SELECT STATEMENT, GOAL = ALL_ROWS Cost=1 Cardinality=1 Bytes=1020 Optimizer=ALL_ROWS IO cost=1
TABLE ACCESS BY INDEX ROWID Object owner=MIGRATION Object name=AD_TMP_SCRIP_MIGRATION Cost=1 Cardinality=1 Bytes=1020 Optimizer=ANALYZED IO cost=1
INDEX RANGE SCAN Object owner=MIGRATION Object name=AD_TMP_SCRIP_MIGRATION_IND1 Cost=1 Cardinality=1 Optimizer=ANALYZED IO cost=1


1.How many rows you have in MIGRATION.AD_TMP_SCRIP_MIGRATION table?
2. How the index AD_TMP_SCRIP_MIGRATION_IND1 is defined?

Run the entire process with event 10046 or sql_trace = true and post TKPROF.
icon14.gif  Re: simple program taking 2 hrs to execute..plz help [message #219672 is a reply to message #219084] Thu, 15 February 2007 06:57 Go to previous messageGo to next message
junaidsystems
Messages: 30
Registered: June 2006
Member
user rleishman wrote on Mon, 12 February 2007 20:22

Woops. Forgot to mention: for the ROWNUM = 1 suggestion to work, you will need to swap the columns in the index:

create index large_numbers
on table_2 (to_number(col_c),to_number(col_b))

----------------------

Great!!! It's working and giving result in 2 minutes instead 2hrs. Great help to me....


more... but there is another problem with this query... if use this query in another situation where i need to check another column, it again goes for 2hrs to complete. example:

declare

curosr table1_cur is select column_a from Table_1;

begin

for tab_rec in table1_cur
loop

select col_b,col_c from table_2 where table_rec.column_a between to_number(col_b) and to_number(col_c)
and rec_num != vrec_num;

... some operations....
... some operations....

end;

end loop;

create index large_numbers on table_2 (rec_num,to_number(col_b),to_number(col_c))
Re: simple program taking 2 hrs to execute..plz help [message #219773 is a reply to message #219672] Thu, 15 February 2007 22:40 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
create index large_numbers 
on table_2 (to_number(col_c), rec_num);


You don't really need to_number(col_b) in the index.


So your SQL is
select col_b,col_c 
from table_2 
where table_rec.column_a between 
    to_number(col_b) and to_number(col_c)
and rec_num != vrec_num
and rownum = 1;


This will ONLY be efficient if two conditions hold true:
1. There are not many/any overlapping ranges.
2. The query will ALWAYS find a value.

If one or both of these is untrue, then it will continue to scan hundreds or thousands of rows looking for a match. The only way it can be efficient is if it finds the matching row in the first few rows scanned.

If you have no overlapping ranges (guaranteed), you could guarantee performance - even if there is sometimes no matching row - by using the technique described in the link I posted above. If the ranges overlap, you should use the Sort-Merge technique also descibed in the link.

Ross Leishman
Previous Topic: Execute Immediate and SQL not properly ended.
Next Topic: Help required in optimizing the query response
Goto Forum:
  


Current Time: Sat Dec 10 02:50:31 CST 2016

Total time taken to generate the page: 0.06808 seconds