1 2 Previous Next 15 Replies Latest reply on Dec 16, 2019 5:11 PM by Mark D Powell

    Query via database link performance issue

    marindo

      Hi!

       

      SUMMARY

       

      I have a "small" local database and a huge remote database which I can access via a database link only. Trying to pull remote data, restricted by data from a local table...

       

      SELECT * FROM remote_data@remote_system WHERE id IN (SELECT id FROM local_table);

       

      ...runs extremely long and finally returns an error (ORA-01652: unable to extend temp segment by 128 in tablespace TEMP), only

       

      FYI:

      select * from v$version;

      yields:

       

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      PL/SQL Release 11.2.0.3.0 - Production

      "CORE    11.2.0.3.0    Production"

      TNS for Linux: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

       

      IN DETAIL:

       

      I have SQL access to a reporting database and can access data from the production database via a database link, only.

       

      On the reporting database, I defined a view like this:

       

      CREATE VIEW remote_data AS (

      SELECT

        ...

      FROM

         table1@prod a

         JOIN table2@prod b ON b.col_x=a.col_x

         JOIN table3@prod c ON c.col_y=b.col_y

      );

       

      A typical use on the reporting database would be:

       

      SELECT * FROM remote_data WHERE id=123456789;

       

      Now, I created table candidates with ~15,000 rows where one column is the id of which I want to pull the remote data. I tried:

       

      SELECT * FROM remote_data WHERE id IN (SELECT id FROM id_list);

       

      The query run ~3h and the stopped with the error message: "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP"

       

      I tried to check not all id's at once, but looking at just 10 via:

       

      SELECT * FROM remote_data WHERE id IN (SELECT id FROM id_list WHERE ROWNUM<=10);

       

      But this had no effect. The query again runs for hours and finally just returns the same error message. Doing the same manually, works pretty fine. I.e. I did

       

      SELECT id FROM id_list WHERE ROWNUM<=10;

       

      first, and the created a query listing the id's as constant values in a list:

       

      SELECT * FROM remote_data WHERE id IN (123456789, 223456789, 323456789, 423456789, ...);

       

      This executed fine and returned the expected data in ~30 secs. So, I created the same with a list of all 15,000 id's. But I got an error. Lists may have only up to 1000 values. So, ok, I could split my 15000 id's into 15 blocks with 1000 id's each, but that's a cumbersome approach which doesn't scale. So I started searching.

       

      It looks like the root issue is a join between a local and a remote table. Well, there is no JOIN in may query, but most-likely

       

      SELECT * FROM remote_data WHERE id IN (SELECT id FROM id_list)

       

      is optimized / executed as

       

      SELECT a.* FROM remote_data a JOIN (SELECT id FROM id_list) b on b.id=a.id;

       

      And it looks like the system would then (a) first try to execute (SELECT * FROM remote_data) on the prod system, then (b) copy the result to the reporting system and finally do the join with (SELECT id FROM id_list) locally. Due to the huge amount of data, this fails.

       

      But it should be done the other way round! Assuming the query would be issued local on the prod database, it would first run (SELECT id FROM id_list) on the remote reporting system, send the resulting list of only 15000 id's back to the production system and finally do the select locally, restricted on that id's.

       

      I found the DRIVING_SITE() hint and tried:

       

      SELECT /*+ DRIVING_SITE(r) */

        *

      FROM

        remote_data r

      WHERE id IN (SELECT id FROM id_list where rownum<=10)

      ;

       

      But it did not really work. Well, this did not run 3 hours anymore, but just a few mins. However, the only result I got again was: "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP"

       

      Any hints / ideas?

       

      Thx for your help!

        • 1. Re: Query via database link performance issue
          jaramill

          From the link on the FAQ (Frequently Asked Questions) forum --> Re: 2. How do I ask a question on the forums?

          Please answer #5

           

          5) Database Version and IDE Version
          Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.

           

          If you're not sure what it is you can do the following:

          select * from v$version;

          in an SQL*Plus session and paste the results.

          • 2. Re: Query via database link performance issue
            marindo

            Sorry & thx. Edited in my original posting.

            • 3. Re: Query via database link performance issue
              Mark D Powell

              marindo, you should have posted the actual error stack returned by Oracle.  Is the ORA-01652 happening our your system or is this error being returned from the remote system?  The full error stack would show this.

              - -

              All SQL tuning starts with the plan.  Post the plan for your size and ask the remote system DBA to pull the plan for your task.

              - -

              Try recoding the query with the remote tables in the query rather than use a view.  See if the optimizer performs the query with the same plan or generates a different one.

              - -

              HTH -- Mark D Powell --

              • 4. Re: Query via database link performance issue
                marindo

                Well, the full query result is:

                 

                ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

                ORA-02063: preceding line from REMOTE_SYSTEM

                01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"

                *Cause:    Failed to allocate an extent of the required number of blocks for

                           a temporary segment in the tablespace indicated.

                *Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more

                           files to the tablespace indicated.

                 

                Honestly, I don't know how to interpret this in more detail. It's this the "error stack"? How does it tell me if the error was on my local or on the remote system?

                • 5. Re: Query via database link performance issue
                  L. Fernigrini

                  The error seems to be on the remote side, and you are getting out of space on the TEMP tablespace.

                   

                  You should reach the DBA that manages that instance and ask him/her to increase the TEMP tablespace by adding additional tempfiles.

                   

                  You could also try to enhance the query to avoid using temp space. Does it have an index on ID column?

                   

                  You can also ensure that the query is executed on the remote server using the DRIVING_SITE hint:

                   

                  SELECT /*+driving_site(r) */ r.* FROM remote_data@remote_system r WHERE r.id IN (SELECT l.id FROM local_table l);

                  • 6. Re: Query via database link performance issue
                    marindo

                    Ok, I just tried...

                     

                    SELECT /*+ DRIVING_SITE(t1) */

                      ...

                    FROM

                       id_list t0

                       JOIN table1@prod a ON t1.id=t0.id

                       JOIN table2@prod b ON b.col_x=a.col_x

                       JOIN table3@prod c ON c.col_y=b.col_y

                    ;

                     

                    which rather quickly returned resulting row. To get all stored, I am now executing

                     

                    CREATE TABLE result AS (

                    SELECT /*+ DRIVING_SITE(t1) */

                      ...

                    FROM

                       id_list t0

                       JOIN table1@prod a ON t1.id=t0.id

                       JOIN table2@prod b ON b.col_x=a.col_x

                       JOIN table3@prod c ON c.col_y=b.col_y

                    );

                     

                    ...it's still running...

                    • 7. Re: Query via database link performance issue
                      L. Fernigrini

                      I believe that you driving site is ignored when using a local CREATE TABLE, a local MERGE, and other scenarios where DML is done locally

                      https://asktom.oracle.com/pls/apex/asktom.search?tag=driving-site-hint .

                       

                      You may try this approach:

                      https://hourim.wordpress.com/2012/02/25/tuning-an-insertselect-via-dblink/

                      • 8. Re: Query via database link performance issue
                        EdStevens

                        marindo wrote:

                         

                        Well, the full query result is:

                         

                        ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

                        ORA-02063: preceding line from REMOTE_SYSTEM

                        01652. 00000 - "unable to extend temp segment by %s in tablespace %s"

                        *Cause: Failed to allocate an extent of the required number of blocks for

                        a temporary segment in the tablespace indicated.

                        *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more

                        files to the tablespace indicated.

                         

                        Honestly, I don't know how to interpret this in more detail. It's this the "error stack"? How does it tell me if the error was on my local or on the remote system?

                        "How does it tell me if the error was on my local or on the remote system?"

                         

                        perhaps this line from the error stack:

                        "ORA-02063: preceding line from REMOTE_SYSTEM"

                        • 9. Re: Query via database link performance issue
                          655734

                          is issue fixed after adding tempfile?

                          • 10. Re: Query via database link performance issue
                            marindo

                            Thanks for the pointer, Fernigrini.

                             

                            While the pure query returned all rows in a few minutes, only, encapsulating that query in a CREATE TABLE caused it to run for 6.5 hours until the network connection was lost...

                            • 11. Re: Query via database link performance issue
                              John Thorton

                              marindo wrote:

                               

                              Hi!

                               

                              SUMMARY

                               

                              I have a "small" local database and a huge remote database which I can access via a database link only. Trying to pull remote data, restricted by data from a local table...

                               

                              SELECT * FROM remote_data@remote_system WHERE id IN (SELECT id FROM local_table);

                              How many total rows returned by SQL above?

                              • 12. Re: Query via database link performance issue
                                marindo

                                The local table has about 15,000 ID's in that number of rows. The remote database usually has 2 rows per ID, thus the total number of rows returned is about 30,000 rows.

                                • 13. Re: Query via database link performance issue
                                  BrunoVroman

                                  Hello Marindo,

                                   

                                  could you maybe replace

                                   

                                  CREATE VIEW remote_data AS (
                                    SELECT
                                      ...
                                    FROM
                                      table1@prod a
                                      JOIN table2@prod b ON b.col_x=a.col_x
                                      JOIN table3@prod c ON c.col_y=b.col_y
                                    );

                                   

                                  by:

                                  IN REMOTE DB (if you can't do it yourself, you might ask to the DBA taking care of the database; this is just creating a local view)
                                  CREATE VIEW local_data AS
                                    SELECT
                                      ...
                                    FROM
                                      table1 a
                                      JOIN table2 b ON b.col_x = a.col_x
                                      JOIN table3 c ON c.col_y = b.col_y
                                  ;

                                  and, in reporting DB:
                                  CREATE VIEW remote_data AS SELECT ... FROM local_data@prod;

                                   

                                  Best regards,

                                   

                                  Bruno Vroman.

                                  • 14. Re: Query via database link performance issue
                                    BEDE

                                    Could you create a materialized view with refresh on demand:

                                    create materialized view mv_rem_data as SELECT * FROM remote_data@remote_system refresh on demand;

                                     

                                    And then, after refresh, use the materilaized view in the query? How much would last the refresh of such a view? How often do you have to get that data?

                                     

                                    And how many rows do you have in the local table?

                                     

                                    Or, another thing to try.

                                    Run:SELECT * FROM remote_data@remote_system WHERE id =:x;

                                    If this runs pretty fast and you do not have many rows in the local table, then, you may write your query the following way:

                                     

                                    SELECT  /*+ leading(loc) use_nl(rem) */ rem.*

                                    FROM

                                    local_table loc

                                    join remote_data@remote_system rem on rem.id =loc.id

                                    ;

                                    1 2 Previous Next