Forum Stats

  • 3,722,789 Users
  • 2,244,414 Discussions
  • 7,850,092 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

sequence.nexval hanging my query

913578
913578 Member Posts: 385
edited June 2019 in SQL & PL/SQL

Hi,

I have a query fetching data from remote server and inserting in my local table.

when i run the below query it is hanging. when i run without sequence it is working completing execution in 2 seconds.

INSERT

INTO CIS_SERVICE_TOPO_DESC_STAGING

  (

    TELSTRA_ADDRESS_ID,

    UNIT_NUMBER,

    FLOOR,

    STREET_NUMBER,

    SEQ_NUM,

    WIZ_HEADEND_CODE,

    WIZ_NODE_CODE,

    SERVICE_ID

  )

SELECT A.TELSTRA_ADDRESS_ID,

  A.UNIT_NUMBER,

  A.FLOOR,

  A.STREET_NUMBER, 

  SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval,

  A.WIZ_HEADEND_CODE,

  A.WIZ_NODE_CODE,

  B.SERVICE_ID

FROM [email protected]_DBLINK A,

  [email protected]_DBLINK B

WHERE A.TELSTRA_ADDRESS_ID=B.TELSTRA_ADDRESS_ID;

I have checked the sequence object is in valid state. and able to run the as below. sequence number is populating .

select

--A.UNIT_NUMBER,

SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval

FROM [email protected]_DBLINK A,

  [email protected]_DBLINK B

WHERE A.TELSTRA_ADDRESS_ID=B.TELSTRA_ADDRESS_ID;

when i put at least one column in the above select statement it is hanging.

Thanks.

AndrewSayerBrunoVromanMustafa_KALAYCIDom BrooksL. Fernigrini

Answers

  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited June 2019

    Dear @913578,

    Seems like you have created your sequence with NO CACHE specified, which can decrease the speed for data access.

    When a sequence is read into the sequence cache, sequence values are generated and stored in a cache entry. These values can then be accessed quickly and hence the speed of data access is incresed.

    The only concern to be noted here is that in the case wherein the database is abnormally terminated, after startup, the next value of the sequence will be the value from the newly cached inset, rather than the follow-up value from the already worked on the sequence.

    Thanks and Regards,

    @Vysakh Suresh - 3035408

  • 913578
    913578 Member Posts: 385
    edited June 2019

    Thanks Suresh for your response.

    if NO CACHE is the issue then it should not work without the columns in the selection (please see the working scenario mentioned in my post).

    Below is the sequence details, have cache value=3

    CREATED    04-JUN-19 21:15:10

    LAST_DDL_TIME    04-JUN-19 21:15:10

    SEQUENCE_OWNER    ITAM_PROD_ARADMIN

    SEQUENCE_NAME    SDCACHE_IMPORT_KEY_SEQ_NEW2

    MIN_VALUE    1

    MAX_VALUE    9999999999999999999999999999

    INCREMENT_BY    1

    CYCLE_FLAG    N

    ORDER_FLAG    N

    CACHE_SIZE    3

    LAST_NUMBER    226

    SCALE_FLAG    N

    EXTEND_FLAG    N

    SESSION_FLAG    N

    KEEP_VALUE    N

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,557 Gold Crown
    edited June 2019

    You need to be more precise with your choice of words - I suspect that when you say "hanging" you mean "running very slowly". If you thinks it's hanging then you need to show some evidence that this is the case so that we have a better chance of helping.

    I'm going to guess that the insert really is just running slowing - this is (almost) expected behaviour for an insert such as yours.

    If you could run the insert without the sequence number you should see Oracle running the query part remotely - i.e. the SQL sent to the remote database would be a join query.

    When you run the insert with the sequence in place you are probably seeing Oracle running the query locally - which means it sends two queries to the remote database to get all the necessary data from the two tables before joining them locally.

    I am a little puzzled by the fact that you can select just the sequence number quickly (because that, too, should be doing the two-step fetch and join) while adding a column to the select makes it slow - but I suspect this means the plan with no columns is doing a fast full scan on the two remote indexes so operating much more quickly than the query with no columns which may be doing a full tablescan (or worse) in order to return the selected column before doing the hash join.

    Bottom line:  Find out what the execution plans are.

    Solution - a typical workaround toi this kind of problem is to create a view of the join on the remote database then select from the view. If you can't do that then create a pipelined function that runs the query (without the sequence number, but does a remote select) then "insert select sequence, etc. from the pipeline function". See: https://jonathanlewis.wordpress.com/2010/10/07/distributed-pipelines/

    Regards

    Jonathan Lewis

    AndrewSayerBrunoVromanMustafa_KALAYCIDom Brooks
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited June 2019

    You should look at what your session is waiting on when it does the insert.

    If you don’t know the sid for the session then look it up before you run the statement with

    select sys_context('userenv','sid') from dual;

    Then while the session is hanging, query from another session

    select * from v$session_wait where sid = <sid for your session>

    Select * from v$session where sid = <sid>

    I wonder if you’re waiting on an tx enqueue because your sequence Value is going into a column with a unique constraint on but other sessions are not using the sequence to ensure they get unique values. The insert statement will hang if another session has inserted the same value for the unique constraint without committing - it hangs because it might still be successful if the other session rollsback the transaction.

  • EdStevens
    EdStevens Member Posts: 27,980 Gold Crown
    edited June 2019
    Vysakh Suresh - 3035408 wrote:<snip>The only concern to be noted here is that in the case wherein the database is abnormally terminated, after startup, the next value of the sequence will be the value from the newly cached inset, rather than the follow-up value from the already worked on the 

    What you describe is true, but misleading.  This happens simply because the database is restarted.  It has nothing to do with the database being abnormally terminated.  It happens every time the database is restarted, not just from abnormal terminations.  And this is just one of the reasons why there is no guarantee of 'gapless' sequence usage.

  • EdStevens
    EdStevens Member Posts: 27,980 Gold Crown
    edited June 2019
    913578 wrote:Thanks Suresh for your response.if NO CACHE is the issue then it should not work without the columns in the selection (please see the working scenario mentioned in my post).

    What do you mean by that?  On the face of it, it makes no sense at all.  The insert, and the provision of values (or not) for specific columns has no relationship at all to the cache value of any sequences that may or may not be used.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,557 Gold Crown
    edited June 2019
    EdStevens wrote:It happens every time the database is restarted, not just from abnormal terminations.

    This does depend on version - most recent versions of Oracle tend to write back the current value on shutdown, or flush shared_pool, or when the shared pool is under pressure and memory has to be freed.  Currently I think it's only "shutdown abort" (and instance crash) that will fail to update seq$.

    Regards

    Jonathan Lewis

    L. Fernigrini
  • 913578
    913578 Member Posts: 385
    edited June 2019

    Forget about the insert query, the problem is with the select statment.

    I am getting output for below one.

    SELECT  SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval  FROM [email protected]_DBLINK where rownum<2;

    But going to hung state when running below one

    SELECT  TELSTRA_ADDRESS_ID,SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval  FROM [email protected]_DBLINK where rownum<2;

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited June 2019
    913578 wrote:Forget about the insert query, the problem is with the select statment.I am getting output for below one.SELECT SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval FROM [email protected]_DBLINK where rownum<2;But going to hung state when running below oneSELECT TELSTRA_ADDRESS_ID,SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval FROM [email protected]_DBLINK where rownum<2;

    Show us the execution plans of the two queries

  • 913578
    913578 Member Posts: 385
    edited June 2019

    GENERAL INFORMATION SECTION

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

    Tuning Task Name   : staName75420

    Tuning Task Owner  : ITAM_PROD_ARADMIN

    Tuning Task ID     : 67611

    Workload Type      : Single SQL Statement

    Execution Count    : 1

    Current Execution  : EXEC_65026

    Execution Type     : TUNE SQL

    Scope              : COMPREHENSIVE

    Time Limit(seconds): 1800

    Completion Status  : COMPLETED

    Started at         : 06/10/2019 04:55:40

    Completed at       : 06/10/2019 04:55:43

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

    Schema Name: ITAM_PROD_ARADMIN

    SQL ID     : 9ug0g0ms2jgq3

    SQL Text   : SELECT  SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval  FROM [email protected]_DBLINK where rownum<2

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

    There are no recommendations to improve the statement.

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

    EXPLAIN PLANS SECTION

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

    1- Original

    -----------

    Plan hash value: 2683674531

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

    | Id  | Operation        | Name                        | Rows  | Cost (%CPU)| Time     | Inst   |IN-OUT|

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

    |   0 | SELECT STATEMENT |                             |     1 |     2   (0)| 00:00:01 |        |      |

    |   1 |  SEQUENCE        | SDCACHE_IMPORT_KEY_SEQ_NEW2 |       |            |          |        |      |

    |*  2 |   COUNT STOPKEY  |                             |       |            |          |        |      |

    |   3 |    REMOTE        | SERVICE_TOPOLOGY            |     1 |     2   (0)| 00:00:01 | SUPER~ | R->S |

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

    Query Block Name / Object Alias (identified by operation id):

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

       1 - SEL$1

       3 - SEL$1 / [email protected]$1

    Predicate Information (identified by operation id):

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

       2 - filter(ROWNUM<2)

    Column Projection Information (identified by operation id):

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

       1 - ROWNUM[8]

       2 - ROWNUM[8]

    Remote SQL Information (identified by operation id):

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

       3 - SELECT 0 FROM "SDCABLEINV"."SERVICE_TOPOLOGY" "SERVICE_TOPOLOGY" (accessing

           'SUPERDAM_DBLINK' )

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

    GENERAL INFORMATION SECTION

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

    Tuning Task Name   : staName8929

    Tuning Task Owner  : ITAM_PROD_ARADMIN

    Tuning Task ID     : 67612

    Workload Type      : Single SQL Statement

    Execution Count    : 1

    Current Execution  : EXEC_65027

    Execution Type     : TUNE SQL

    Scope              : COMPREHENSIVE

    Time Limit(seconds): 1800

    Completion Status  : COMPLETED

    Started at         : 06/10/2019 04:57:45

    Completed at       : 06/10/2019 04:57:47

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

    Schema Name: ITAM_PROD_ARADMIN

    SQL ID     : 29c3pf7s4a6a1

    SQL Text   : SELECT  TELSTRA_ADDRESS_ID,SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval

                 FROM [email protected]_DBLINK where rownum<2

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

    There are no recommendations to improve the statement.

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

    EXPLAIN PLANS SECTION

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

    1- Original

    -----------

    Plan hash value: 2683674531

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

    | Id  | Operation        | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|

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

    |   0 | SELECT STATEMENT |                             |     1 |    13 |     2   (0)| 00:00:01 |        |      |

    |   1 |  SEQUENCE        | SDCACHE_IMPORT_KEY_SEQ_NEW2 |       |       |            |          |        |      |

    |*  2 |   COUNT STOPKEY  |                             |       |       |            |          |        |      |

    |   3 |    REMOTE        | SERVICE_TOPOLOGY            |     1 |    13 |     2   (0)| 00:00:01 | SUPER~ | R->S |

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

    Query Block Name / Object Alias (identified by operation id):

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

       1 - SEL$1

       3 - SEL$1 / [email protected]$1

    Predicate Information (identified by operation id):

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

       2 - filter(ROWNUM<2)

    Column Projection Information (identified by operation id):

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

       1 - "TELSTRA_ADDRESS_ID"[NUMBER,22], ROWNUM[8]

       2 - "TELSTRA_ADDRESS_ID"[NUMBER,22], ROWNUM[8]

       3 - "TELSTRA_ADDRESS_ID"[NUMBER,22]

    Remote SQL Information (identified by operation id):

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

       3 - SELECT "TELSTRA_ADDRESS_ID" FROM "SDCABLEINV"."SERVICE_TOPOLOGY" "SERVICE_TOPOLOGY" (accessing

           'SUPERDAM_DBLINK' )

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

  • BEDE
    BEDE Oracle Developer Member Posts: 2,200 Silver Trophy
    edited June 2019

    What I'd do.

    I'd run:

    SELECT A.TELSTRA_ADDRESS_ID,

      A.UNIT_NUMBER,

      A.FLOOR,

      A.STREET_NUMBER, 

    -------------------------  SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval,

      A.WIZ_HEADEND_CODE,

      A.WIZ_NODE_CODE,

      B.SERVICE_ID

    FROM [email protected]_DBLINK A,

      [email protected]_DBLINK B

    WHERE A.TELSTRA_ADDRESS_ID=B.TELSTRA_ADDRESS_ID;

    How does that perform? If that performs well enough then:

    INSERT

    INTO CIS_SERVICE_TOPO_DESC_STAGING

      (

        TELSTRA_ADDRESS_ID,

        UNIT_NUMBER,

        FLOOR,

        STREET_NUMBER,

        SEQ_NUM,

        WIZ_HEADEND_CODE,

        WIZ_NODE_CODE,

        SERVICE_ID

      )

    with tb as (

    SELECT A.TELSTRA_ADDRESS_ID,

      A.UNIT_NUMBER,

      A.FLOOR,

      A.STREET_NUMBER, 

    -------------------------  SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval,

      A.WIZ_HEADEND_CODE,

      A.WIZ_NODE_CODE,

      B.SERVICE_ID

    FROM [email protected]_DBLINK A,

      [email protected]_DBLINK B

    WHERE A.TELSTRA_ADDRESS_ID=B.TELSTRA_ADDRESS_ID;

    )

    select

      TELSTRA_ADDRESS_ID,

       UNIT_NUMBER,

        FLOOR,

        STREET_NUMBER, 

        SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval,

       WIZ_HEADEND_CODE,

       WIZ_NODE_CODE,

      SERVICE_ID

    from tb

    ;

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited June 2019

    I’m not sure if those are really explain plans or execution plans, you only ever really want to look at execution plans. They can be obtained thru dbms_xplan.display_cursor.

    If we assume theyre not lying to us, then we can see that the query which only references the sequence is able to do a remote query which only selects 0 from the remote table. This remote query doesn’t need to do much work at all, it could just read an index on a not null column and it’s work is done.

    The query which references sequence and columns is running a remote query which contains a column from the table. This query will have to either do a full tablescan (although it can stop at the first row it receives) or read from an index on that column if it is not nullable (Again only the first row).

    Do these SQLs actually demonstrate the slowness (considering they only fetch one row max)?

    The time is likely taken executing the remote query, so head over to the remote DB and find the EXECUTION plan there - you’ll need to determine the sql_id (use v$sql) and pass that to dbms_xplan.display_cursor. You‘ll also want to look at the number of executions, fetches and number of rows fetched from either query.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,557 Gold Crown
    edited June 2019

    When you select local nextval and remote column you have a distributed query, and Oracle behaves differently - the "rownum < 2" does not cross to the remote database.  Here's a cut and paste from an SQL*Plus session demonstrating the issue. I've set up a loopback database link so that the "remote" site is actually local (though my session doesn't know this):

    SQL> alter system flush shared_pool;

    System altered.

    SQL> select  s1.nextval from [email protected]@loopback where rownum < 2;

       NEXTVAL

    ----------

            81

    1 row selected.

    SQL> select  id, s1.nextval from [email protected]@loopback where rownum < 2;

            ID    NEXTVAL

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

            16         82

    1 row selected.

    SQL> select  id from [email protected]@loopback where rownum < 2;

            ID

    ----------

            16

    1 row selected.

    SQL> select sql_id, child_number, parse_calls, executions, rows_processed, sql_text from v$sql where sql_text like 'SELECT%T1%';

    SQL_ID        CHILD_NUMBER PARSE_CALLS EXECUTIONS ROWS_PROCESSED

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

    SQL_TEXT

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

    6kfc99r9mwq0s            0           1          1              1

    SELECT "A1"."ID" FROM "TEST_USER"."T1" "A1" WHERE ROWNUM<2

    71bq0j03wtsmz            0           2          0              0

    SELECT /*+ FULL(P) +*/ * FROM "TEST_USER"."T1" P

    1kzgwm8cj1t1x            0           1          1              1

    SELECT 0 FROM "TEST_USER"."T1" "T1"

    c6cavnnps7kn8            0           1          1           1000

    SELECT "ID" FROM "TEST_USER"."T1" "T1"

    4 rows selected.

    Notice that for each of the three statement (get remote id, get local nextval and remote id, get local nextval only) there is a different statement executed remotely. Two of these statements stop after ONE row is fetched, but the statement corresponding to the distributed query (i.e. local nextval, remote column) fetches 1,000 rows - which happens to be all the rows in the table.

    (The second query 71bq0j03wtsmz that is parsed twice is just Oracle doing a describe - an ODESCR call) - from the local system to find out the column details of the columns it will need to return from the remote database - that's why it is parsed twice, two of the three queries need to know what t1.id looks like)

    Your problem is about how Oracle finds and returns the remote data.

    Regards

    Jonathan Lewis

Sign In or Register to comment.