11 Replies Latest reply on Dec 12, 2012 10:01 PM by rsar001

    Insert Statement Slow Performance

    rsar001
      Hi Experts,
      Oracle 11.2.0.3 64x
      OS: Windows 2008 Server

      I have an insert statement based on a select from a DB link that is running extremely slow and I can't work out what's going on..

      Here is the select statement:
       INSERT INTO load_control
               SELECT 'VOTERS'
                     ,sysdate
                     ,COUNT(1) cnt
                     ,NULL
                     ,NULL
                     ,25570
                 FROM data_schema.voter@extract_link v
                WHERE v.status = 'Active'
                  AND v.isprotected = 0;
      When I try the select statement on it's own, it runs super fast:
       SELECT 'VOTERS'
        2                 ,sysdate
        3                 ,COUNT(1) cnt
        4                 ,NULL
        5                 ,NULL
        6                 ,25570
        7             FROM data_schema.voter@extract_link v
        8            WHERE v.status = 'Active'
        9              AND v.isprotected = 0;
      
      'VOTER SYSDATE          CNT N N      25570
      ------ --------- ---------- - - ----------
      VOTERS 11-DEC-12    3147935          25570
      
      Elapsed: 00:00:00.81
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1563332406
      
      ------------------------------------------------------------------------------------------------------
      | Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
      ------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT REMOTE|                    |     1 |    11 |  4917   (4)| 00:01:00 |        |
      |   1 |  SORT AGGREGATE        |                    |     1 |    11 |            |          |        |
      |*  2 |   INDEX FAST FULL SCAN | VOTER_SRCH_2       |  3151K|    33M|  4917   (4)| 00:01:00 |   EISP |
      ------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter("A1"."STATUS"='Active' AND "A1"."ISPROTECTED"=0)
      
      Note
      -----
         - fully remote statement
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                1  db block gets
                0  consistent gets
                0  physical reads
              208  redo size
              882  bytes sent via SQL*Net to client
              520  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
      We took the code, and ran it on it's own in the remote database, and the code performed quickly as expected.

      If we'd to create a temp table, and run a sql statement as create table xxx based on the select from the query above, the create table statement execute in no time.. The min we try to insert based on the select, then it just sits there doing nothing!!!

      There are no indexes, no constraints, and no triggers on this table. The table has 21159 rows in it and is rather small. There is nothing in the alert log. I've tried changing the insert to use the append and append_values hints but to no avail.

      There is nothing running on the database and it has been bounced already. Also, if I create a 2nd table (create table XX as select * from load_control) and try to do the insert on the new table, then the results are the same and the query just sits there without doing anything.

      Looking at the session wait event we get teh following:
       select event,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where sid=362;
      
      EVENT                                                             WAIT_TIME SECONDS_IN_WAIT STATE
      ---------------------------------------------------------------- ---------- --------------- -------------------
      SQL*Net more data from dblink                                             0             148 WAITING
      
      Elapsed: 00:00:00.01
      Any ideas please? anything we can further check?

      Edited by: rsar001 on Dec 11, 2012 5:25 PM
        • 1. Re: Insert Statement Slow Performance
          sb92075
          ALTER SESSION SET SQL_TRACE=TRUE;

          process resultant trace file using tkprok & explicitly request it produce EXPLAIN PLAN

          post the results back here
          • 2. Re: Insert Statement Slow Performance
            rsar001
            I've ran a trace file for a similar statement that should've ran in second or so, that took almost 25 mins to run. The statement does use the same link (querying a different table though):
                  INSERT INTO load_control
                     SELECT 'TEMP EMPLOYEES'
                           ,sysdate
                           ,COUNT(1) cnt
                           ,NULL
                           ,NULL
                           ,25570
                       FROM data_schema.tempemployee@extract_link
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse        1      0.01       0.00          0          0          0           0
            Execute      1      0.01    1459.93          0          1          4           1
            Fetch        0      0.00       0.00          0          0          0           0
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total        2      0.03    1459.94          0          1          4           1
            
            Misses in library cache during parse: 1
            Optimizer mode: ALL_ROWS
            Parsing user id: 61  
            Number of plan statistics captured: 1
            
            Rows (1st) Rows (avg) Rows (max)  Row Source Operation
            ---------- ---------- ----------  ---------------------------------------------------
                     0          0          0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=905497377 us)
                     1          1          1   SORT AGGREGATE (cr=0 pr=0 pw=0 time=905497266 us)
                 94962      94962      94962    REMOTE  TEMPEMPLOYEE (cr=0 pr=0 pw=0 time=212836315 us cost=29 size=0 card=95300)
            
            ********************************************************************************
            Thanks
            • 3. Re: Insert Statement Slow Performance
              Nikolay Savvinov
              Hi,

              if you look at the good plan carefully, you'll see the "fully remote statement" footnote, i.e. it's fast because everything is executed on the remote site, and only a small amount of resulting data needs to be transferred over the network.

              The insert statement behaves differently because of a limitation described here: http://jonathanlewis.wordpress.com/2008/12/05/distributed-dml/ and as a result Oracle spends a lot of time transferring the entire table over network.

              Try to create the view on the remote site as suggested in the link above.

              Best regards,
              Nikolay
              • 4. Re: Insert Statement Slow Performance
                rsar001
                I did go through the link you provided me and it does make sense. However, one would imagine that the behavior should be consistent, that is an insert or a create table statement should take almost the same time. In our case the create table statement takes next to nothing, while the insert just takes for ever.

                Also, we have this particular sql running everyday, why does it run fine everyday and it just decided that the insert is an issue where it wasn't in the pass?

                It just doesn't make sense, and I'm sure there is more to it..

                Thanks
                • 5. Re: Insert Statement Slow Performance
                  Nikolay Savvinov
                  Hi
                  rsar001 wrote:
                  I did go through the link you provided me and it does make sense. However, one would imagine that the behavior should be consistent, that is an insert or a create table statement should take almost the same time. In our case the create table statement takes next to nothing, while the insert just takes for ever.
                  You seem to have missed that bit from the link I gave you:

                  "This is not a bug. A distributed DML statement must execute on the database where the DML target resides. "

                  The keyword here is DML. CTAS is not DML and therefore can behave differently.
                  Also, we have this particular sql running everyday, why does it run fine everyday and it just decided that the insert is an issue where it wasn't in the pass?
                  In your original post you didn't provide any information that the same INSERT statement was running fine in the past. If you provide something -- like execution stats from DBA_HIST_SQLSTAT, the old plan, etc., then we have a basis for discussion, otherwise it's all moot. I cannot discuss something that I can't see. Maybe the old plan was always transferring lots of data over the network but you only noticed it now because the network got slower, or maybe it's one of million other things, there is no point in guessing.
                  It just doesn't make sense,
                  Maybe it does, maybe it doesn't. Why not create a remote view and check?

                  Best regards,
                  Nikolay
                  • 6. Re: Insert Statement Slow Performance
                    Mark Malakanov (user11181920)
                    First of all try to isolate remote part from local part.
                            SELECT COUNT(1) cnt
                               FROM data_schema.voter@extract_link v
                              WHERE v.status = 'Active'
                                AND v.isprotected = 0;
                    How long does it takes to execute?

                    If fast, try
                    WITH R as (
                            SELECT COUNT(1) cnt
                               FROM data_schema.voter@extract_link v
                              WHERE v.status = 'Active'
                                AND v.isprotected = 0)
                    SELECT 'VOTERS'
                                   ,sysdate
                                   ,cnt
                                   ,NULL
                                   ,NULL
                                   ,25570
                    FROM R;
                    Edited by: user11181920 on Dec 12, 2012 12:19 PM
                    • 7. Re: Insert Statement Slow Performance
                      rsar001
                      I did create a view on the remote DB and did an insert based on select * from the remote view and the response was good as expected:
                      insert into rtest select * from load_ctl_cnt_voters@extract_link;
                      
                      1 row created.
                      
                      Elapsed: 00:00:00.64
                      user11181920 , the select statement runs fine:
                      SELECT COUNT(1) cnt
                                 FROM data_schema.voter@extract_link v
                                WHERE v.status = 'Active'
                                  AND v.isprotected = 0;
                      
                             CNT
                      ----------
                         3147938
                      
                      Elapsed: 00:00:00.71
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 1563332406
                      
                      ------------------------------------------------------------------------------------------------------
                      | Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
                      ------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT REMOTE|                    |     1 |    11 |  4917   (4)| 00:01:00 |        |
                      |   1 |  SORT AGGREGATE        |                    |     1 |    11 |            |          |        |
                      |*  2 |   INDEX FAST FULL SCAN | VOTER_SRCH_2       |  3151K|    33M|  4917   (4)| 00:01:00 |   EISP |
                      ------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - filter("A1"."STATUS"='Active' AND "A1"."ISPROTECTED"=0)
                      
                      Note
                      -----
                         - fully remote statement
                      
                      
                      Statistics
                      ----------------------------------------------------------
                                1  recursive calls
                                1  db block gets
                                0  consistent gets
                                0  physical reads
                              252  redo size
                              524  bytes sent via SQL*Net to client
                              519  bytes received via SQL*Net from client
                                2  SQL*Net roundtrips to/from client
                                0  sorts (memory)
                                0  sorts (disk)
                                1  rows processed
                      I then went and tried the 2nd sql:
                      WITH R as (
                              SELECT COUNT(1) cnt
                                 FROM data_schema.voter@extract_link v
                                WHERE v.status = 'Active'
                                  AND v.isprotected = 0)
                      SELECT 'VOTERS'
                                     ,sysdate
                                     ,cnt
                                     ,NULL
                                     ,NULL
                                     ,25570
                      FROM R;
                      
                      'VOTER SYSDATE          CNT N N      25570
                      ------ --------- ---------- - - ----------
                      VOTERS 12-DEC-12    3147938          25570
                      
                      Elapsed: 00:00:00.62
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 399193147
                      
                      -----------------------------------------------------------------------------------------
                      | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
                      -----------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT |      |     1 |    13 |  2464   (0)| 00:00:35 |        |      |
                      |   1 |  VIEW            |      |     1 |    13 |  2464   (0)| 00:00:35 |        |      |
                      |   2 |   REMOTE         |      |       |       |            |          | EBC_E~ | R->S |
                      -----------------------------------------------------------------------------------------
                      
                      Remote SQL Information (identified by operation id):
                      ----------------------------------------------------
                      
                         2 - EXPLAIN PLAN SET STATEMENT_ID='PLUS289836745' INTO PLAN_TABLE@! FOR
                             SELECT COUNT(*) FROM "DATA_SCHEMA"."VOTERPERSON" "A1" WHERE
                             "A1"."STATUS"='Active' AND "A1"."ISPROTECTED"=0 (accessing
                             'EXTRACT_LINK.LOCAL' )
                      
                      
                      
                      Statistics
                      ----------------------------------------------------------
                                1  recursive calls
                                0  db block gets
                                0  consistent gets
                                0  physical reads
                                0  redo size
                              882  bytes sent via SQL*Net to client
                              519  bytes received via SQL*Net from client
                                2  SQL*Net roundtrips to/from client
                                0  sorts (memory)
                                0  sorts (disk)
                                1  rows processed
                      • 8. Re: Insert Statement Slow Performance
                        jgarry
                        >
                        The keyword here is DML. CTAS is not DML and therefore can behave differently.
                        It's always fun when DDL, the manipulation of metadata, also manipulates data. So manipulating data is not DML, which of course stands for...

                        One of the definitions of manipulation is "Shrewd or devious management, especially for one's own advantage." Bwahaha.
                        • 9. Re: Insert Statement Slow Performance
                          Nikolay Savvinov
                          Hi,
                          rsar001 wrote:
                          I did create a view on the remote DB and did an insert based on select * from the remote view and the response was good as expected:
                          so it sounds like your problem is solved? Or is there anything else?

                          Best regards,
                          Nikolay
                          • 10. Re: Insert Statement Slow Performance
                            Mark Malakanov (user11181920)
                            I then went and tried the 2nd sql:
                            Elapsed: 00:00:00.62
                            Then do insert from it.
                            • 11. Re: Insert Statement Slow Performance
                              rsar001
                              "Then do insert from it."

                              That would simply mean changing all of our scripts around for something that have worked for over 6 years now; somehow I don't think this is the fix we're after. What happens in another 5 or 6 years when this stops working?!!

                              Edited by: rsar001 on Dec 12, 2012 2:01 PM