developers

    Forum Stats

  • 3,873,884 Users
  • 2,266,627 Discussions
  • 7,911,651 Comments

Discussions

sequence.nexval hanging my query

913578
913578 Member Posts: 385
edited Jun 10, 2019 2:21PM 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
«1

Answers

  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited Jun 8, 2019 3:38AM

    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 Jun 8, 2019 4:34AM

    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: 10,116 Blue Diamond
    edited Jun 8, 2019 4:40AM

    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: 13,007 Gold Crown
    edited Jun 8, 2019 4:42AM

    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: 28,778 Gold Crown
    edited Jun 8, 2019 7:55AM
    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: 28,778 Gold Crown
    edited Jun 8, 2019 7:58AM
    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: 10,116 Blue Diamond
    edited Jun 8, 2019 11:08AM
    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 Jun 9, 2019 1:40PM

    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: 13,007 Gold Crown
    edited Jun 9, 2019 2:42PM
    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 Jun 9, 2019 2:58PM

    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' )

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

developers