Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Unanswered: Collections Vs GTTs for performance tuning

    Hello All,

    I am currently wroking in Oracle 10g Release 10.2.0.4.0. I have a stored procedure that that has three steps:
    1) Build base data dynamically and insert into GTT1
    2) Join GTT1 and TAB1 and insert into GTT2
    3) Reinsert the same data in GTT2, with slightly different values, for the same records in Step 2

    Originally I was using a cursor, bulk collect and bulk insert and the procedure used to complete in 30 seconds. When my client reviewed the code they suggested that I use collections instead of GTTs and cursors. I was told that this approach had given them huge performance benefits. Hence I changed the code as follows:

    1) Base data is built and collected into a PL/SQL table ColTab1
    2) For each record in the PL/SQL table, I am querying against a large table, and bulk collecting the data into ColTab2
    3) I am using the TABLE(ColTab2) to select my data, with different column values, and iteratively extending my ColTab2 and apending the new data.

    The collection approach was disastorous performance wise.

    GTT1 and ColTab1 will have 25000 records, while GTT2 and ColTab2 will have 50000 records.

    After implementing the POC for our code, we wanted to let the client know why the GTT approach is better than the collections only approach recommended by them. Please list the pros and cons of using GTTs Vs Collections.

    Thank you.

    CC

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Memory, sweet memory.

    Quote Originally Posted by oyepal143 View Post
    ... Blah, blah, blah ...
    The collection approach was disastorous performance wise.

    GTT1 and ColTab1 will have 25000 records, while GTT2 and ColTab2 will have 50000 records.

    After implementing the POC for our code, we wanted to let the client know why the GTT approach is better than the collections only approach recommended by them. Please list the pros and cons of using GTTs Vs Collections.
    ... Etc ...
    Disastrous? by how much?

    Because there are very few rows (25k and 50k) the difference should be very small because the GTT data would mainly reside in memory as occurs with the collections.

    The issue may be on how you implemented step 2 and 3 of the modified procedure.

    Post the "collections" version of your procedure so we can check it out.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This jumps out at me:
    2) For each record in the PL/SQL table, I am querying against a large table, and bulk collecting the data into ColTab2
    So you are effectively doing:
    Code:
    FOR i IN 1..25000 LOOP
       SELECT ... FROM large_table WHERE ...;
    END LOOP;
    That will be s-l-o-w.

    I would have thought that the collection-based method would still use joins, like:

    Code:
    SELECT ...
    FROM large_table
    JOIN TABLE (CAST my_collection AS my_collection_type) ON ...;
    It's pretty rare to need to use GTTs in Oracle; I wonder if there is a third, faster approach that uses neither GTTs nor collections? Impossible to say without knowing your requirements in detail of course.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •