Home » RDBMS Server » Performance Tuning » Tuning for slow Oracle query (Oracle database 11.2.0.2.0)
Tuning for slow Oracle query [message #549919] Wed, 04 April 2012 12:07 Go to next message
trowsdale
Messages: 17
Registered: April 2005
Junior Member
Hi, please refer to the sample code below. It's simplified from the real version, but still useful in helping my explanation.

I'm running a fairly typical PL/SQL program where I want to loop through a dataset, and insert the results to another table.

The number of records returned from cursor c1 is about 25X the total records in base_table.

So if base table is 1000 rows, the cursor will return about 25,000 rows.

When "base_table" is about 1000 rows or less, this program performs very well.

But the overall performance is very slow when base_table grows to, say, 3000 records. And I don't mean it's "3X as slow as 1000 record base." Way slower than that

In this latter scenario, I'm able to trace how c1 is running. It takes about 30 minutes to get the first rows, but for about the next 15 minutes, it fetches a pile of rows about 15-20 thousand. So that's good

Unfortunately, after that first burst, it dies out. It will take "breaks" of about 15 minutes, up to 2 hours even ...... And when it resumes, it sputters, maybe getting 100-200 rows per minute, if that.

8 hours later, it's still chugging, and that strikes me as being a bit too pokey, but maybe I'm wrong.

Any suggestions? I've tried FIRST_ROWS(100), ALL_ROWS, CHOOSE hints ... nothing seems to help too much.

We are using Oracle database 11.2.0.2.0, and someone has suggested to me that index hints are almost mandatory now. Is that true??? I hope not, what an awful pain that would be.

Maybe a higher/lower LIMIT clause??

Thanks for any tips!!

Declare 

Cursor c1
SELECT /* CHOOSE */  <columns>   ----  ????? What hint to use ??????
FROM base_table a,   --- variable row count; anywhere from 1 to 4000
large_child_table b  --- about 50,000,000 records
WHERE a.person_id = b.person_id;  -- indexed join

Type c1_table_type     is table of c1%rowtype;
C1_table                      c1_table_type;

Begin

Open c1;
loop
  Fetch c1 bulk collect into c1_table limit 100;
  Exit when c1_table.first is null;

  Forall l_rec  c1_table.first .. c1_table.last

      Insert into other_table
     (   ......  )
     Values
     ( ........ )

    Commit;

End loop;
Close c1;

End;
/



[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Wed, 04 April 2012 12:20] by Moderator

Report message to a moderator

Re: Tuning for slow Oracle query [message #549920 is a reply to message #549919] Wed, 04 April 2012 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 21951
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


PL/SQL will NEVER be faster than plain, raw SQL
Re: Tuning for slow Oracle query [message #549921 is a reply to message #549920] Wed, 04 April 2012 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And even less when you commit in the loop.
In addition your exit test is wrong and badly placed.

Quote:
What hint to use


NO hint, NEVER untill you'll be an Oracle SQL and optimizer expert.

Regards
Michel
Re: Tuning for slow Oracle query [message #549923 is a reply to message #549921] Wed, 04 April 2012 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 21951
Registered: January 2009
Senior Member
>Insert into other_table ( ...... ) Values ( ........ )

row by row is slow by slow
Re: Tuning for slow Oracle query [message #549924 is a reply to message #549919] Wed, 04 April 2012 13:03 Go to previous messageGo to next message
trowsdale
Messages: 17
Registered: April 2005
Junior Member
Unless I'm mistaken, I believe I'm inserting 100 rows at a time by using FORALL. Not sure how that's row-by-row.
Re: Tuning for slow Oracle query [message #549925 is a reply to message #549924] Wed, 04 April 2012 13:07 Go to previous messageGo to next message
BlackSwan
Messages: 21951
Registered: January 2009
Senior Member
>Unless I'm mistaken, I believe I'm inserting 100 rows at a time by using FORALL. Not sure how that's row-by-row.
how can INSERT containing VALUES clause do more than 1 row at a time?
Re: Tuning for slow Oracle query [message #549928 is a reply to message #549919] Wed, 04 April 2012 13:37 Go to previous messageGo to next message
trowsdale
Messages: 17
Registered: April 2005
Junior Member
While I do see your point - the syntax should be:

insert into new_table values bulk_bind(i);

instead of

insert into new_table (col1,col2)
values (bulk_bind(i).col1, bulk_bind(i).col2);


... but the package did compile with the existing syntax, including the FORALL.

So perhaps the FORALL functionality gets "over-written" when the INSERT statement references individual values?


Also, I do know that straight SQL is best, but we are plagued with ROLLBACK SEGMENT issues when performing large INSERT/UPDATE/DELETE statements, and I have no say or input into DBA-related affairs.
Re: Tuning for slow Oracle query [message #549929 is a reply to message #549928] Wed, 04 April 2012 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but we are plagued with ROLLBACK SEGMENT issues when performing large INSERT/UPDATE/DELETE statements


We have to choose between space and time but if you choose space be aware that you impact ALL session performances and not just your own.

Quote:
I have no say or input into DBA-related affairs.


Size of rollback segments is HIS problem NOT yours.
You MUST code as efficiently as you can and only goes down if your entreprise manager don't want to buy disk NOT because of any DBA or sysadmin or storage admin.

Regards
Michel
Re: Tuning for slow Oracle query [message #549930 is a reply to message #549928] Wed, 04 April 2012 13:50 Go to previous messageGo to next message
BlackSwan
Messages: 21951
Registered: January 2009
Senior Member
>insert into new_table values bulk_bind(i);
how many rows does the line above INSERT for any given value of "i"?

> but we are plagued with ROLLBACK SEGMENT issues
I am/was unaware that V11 still used ROLLBACK SEGMENTS.
Where is this documented?
Re: Tuning for slow Oracle query [message #549931 is a reply to message #549919] Wed, 04 April 2012 13:53 Go to previous messageGo to next message
trowsdale
Messages: 17
Registered: April 2005
Junior Member
OK, thanks for that tip .... no, I just assumed that "rollback segments" were still with us, had no idea that might be a non-issue .... if that's true, I'll probably try to get this into pure SQL
Re: Tuning for slow Oracle query [message #549959 is a reply to message #549931] Wed, 04 April 2012 23:33 Go to previous messageGo to next message
krishsidd
Messages: 2
Registered: March 2012
Location: India
Junior Member
Hi Frnd, is it possible for you to get the trace file during the timeframe when job is slowing down?
Re: Tuning for slow Oracle query [message #549967 is a reply to message #549959] Thu, 05 April 2012 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See Pete Finnigan, How to set trace for others sessions, for your own session and at instance level

Regards
Michel
Re: Tuning for slow Oracle query [message #550075 is a reply to message #549967] Thu, 05 April 2012 14:54 Go to previous messageGo to next message
piraco
Messages: 2
Registered: April 2012
Junior Member
You can try with direct path insert, saving exceptions, and also by exiting the cursor after each forall...insert
Something like this:

   open c1;
   loop
       fetch c1 bulk collect into c1_table LIMIT 100 ;
       forall i in 1 .. c1.count SAVE EXCEPTIONS
            insert /+ append*/ into other_table 
                   values ( c1(i).colmun1, ...etc);
       end loop;

       exit when c1%notfound;
   end loop;
   close c1 ;
Re: Tuning for slow Oracle query [message #550183 is a reply to message #550075] Fri, 06 April 2012 09:44 Go to previous message
Bill B
Messages: 1049
Registered: December 2004
Senior Member
Why not do a pure sql plus

insert into other_table
SELECT <columns>   
FROM base_table a, 
large_child_table b
WHERE a.person_id = b.person_id;


Make very sure that person_id is indexed on both tables and that person_id is at the front of the respective index. And if your running out of space in your redo or temp tablespaces, tell the DBA that they need to provide more space. I have been a developer and a dba for 20 years and that is not reasonable.

[Updated on: Fri, 06 April 2012 09:46]

Report message to a moderator

Previous Topic: functional index null values
Next Topic: Need help on SQL Query in decode function performance tunning
Goto Forum:
  


Current Time: Sun Apr 20 16:06:42 CDT 2014

Total time taken to generate the page: 0.08131 seconds