Analytics Software

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"

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

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

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.

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.

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.

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

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 SDCABLEINV.SERVICE_TOPOLOGY@SUPERDAM_DBLINK where rownum<2;

But going to hung state when running below one

SELECT  TELSTRA_ADDRESS_ID,SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval  FROM SDCABLEINV.SERVICE_TOPOLOGY@SUPERDAM_DBLINK where rownum<2;

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 SDCABLEINV.SERVICE_TOPOLOGY@SUPERDAM_DBLINK where rownum<2;
But going to hung state when running below one
SELECT TELSTRA_ADDRESS_ID,SDCACHE_IMPORT_KEY_SEQ_NEW2.nextval FROM SDCABLEINV.SERVICE_TOPOLOGY@SUPERDAM_DBLINK where rownum<2;

Show us the execution plans of the two queries

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 SDCABLEINV.SERVICE_TOPOLOGY@SUPERDAM_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 / SERVICE_TOPOLOGY@SEL$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 SDCABLEINV.SERVICE_TOPOLOGY@SUPERDAM_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 / SERVICE_TOPOLOGY@SEL$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' )

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

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 SDCABLEINV.SERVICE_TOPOLOGY@SUPERDAM_DBLINK A,

  SDCABLEINV.SERVICE_DESC@SUPERDAM_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 SDCABLEINV.SERVICE_TOPOLOGY@SUPERDAM_DBLINK A,

  SDCABLEINV.SERVICE_DESC@SUPERDAM_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

;

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.

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 test_user.t1@orcl@loopback where rownum < 2;

   NEXTVAL

----------

        81

1 row selected.

SQL> select  id, s1.nextval from test_user.t1@orcl@loopback where rownum < 2;

        ID    NEXTVAL

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

        16         82

1 row selected.

SQL> select  id from test_user.t1@orcl@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