Re: Insert running long with Sequence

If using an Oracle Sequence, you *cannot* guarantee a gap-free sequence, regardless of the CACHE value. If you *really* need a guaranteed gap-free sequence, you cannot use sequences and youhave an unscalable system.

-Mark

On Wed, Apr 15, 2020 at 11:41 AM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi,
>
> *what is the drawback of setting to 0 ?*
> It is damn slow.
> *if I increase to lets say 20 , does oracle pre create these 20 numbers at
> once?*
> Yes it does. But 20 is still too low.
> *Any drawback if I set to 500?*
> Sometimes there must not be any holes in your numbering. E.g. transcation
> numbers in a Bank.
> Sequences are not transactional. If there is any rollback, up to 500
> numbers will be skipped.
> Normally that is not a issue. You get bigger numbers instead. Butr it can
> be an issue, that depends on business.
>
> Regards
>
> Lothar
>
>
> Am 15.04.2020 um 17:24 schrieb Bhavani Dhulipalla:
>
> hi Sayan abd Lothar
>
> Thank you - I don't know why it is set as 0?
>
> Can you please let me know what is the drawback of setting to 0 ? if I
> increase to lets say 20 , does oracle pre create these 20 numbers at once?
>
> Any drawback if I set to 500?
>
> Thank you
> Bhavani
>
> On Wed, Apr 15, 2020 at 11:18 AM Sayan Malakshinov <xt.and.r_at_gmail.com>
> wrote:
>
>> Hi Bhavani,
>>
>> > CACHE_SIZE : 0
>>
>> Do you know why that sequence was created with cache size 0? Default
>> cache size is 20. So you just need to increase cache size of the sequence:
>> Alter sequence comsys.statement_id_seq cache N;
>> Just choose appropriate N.
>>
>> ср, 15 апр. 2020 г., 18:08 Bhavani Dhulipalla <bhavanidba6_at_gmail.com>:
>>
>>> Hi -
>>>
>>> DB Version 11.2 OS. Aix -
>>>
>>> We have the insert statement and it is using the sequence in the insert
>>> and most of the time insert isn spending its time one accessing the
>>> Sequence
>>>
>>> Query:
>>>
>>> INSERT INTO lpl_statement (
>>> batch_no,
>>> line_no,
>>> payee_id,
>>> ctype_code,
>>> branch_id,
>>> osj_id,
>>> rep_id,
>>> product_id,
>>> client_id,
>>> split_code,
>>> trans_date,
>>> client_name,
>>> buy_sell,
>>> cusip,
>>> units,
>>> description,
>>> short_desc,
>>> security_type,
>>> security_calc,
>>> principal_agency,
>>> listed_otc,
>>> order_type,
>>> order_terminal,
>>> price,
>>> invested_amt,
>>> commission,
>>> payee_split_pct,
>>> rep_gross_pct,
>>> rep_gross,
>>> front_end_pct,
>>> rep_adj_gross,
>>> rep_payout_pct,
>>> rep_net,
>>> override_pct,
>>> rep_payout,
>>> rep_ticket,
>>> rep_floor,
>>> rep_amount,
>>> stmt_amount,
>>> account_class,
>>> class_short_desc,
>>> firm_id,
>>> emp_ind,
>>> order_source,
>>> payee_branch_id,
>>> payee_osj_id,
>>> trans_posted_date,
>>> atype_code,
>>> statement_id,
>>> desk_fee_pct,
>>> desk_fee,
>>> beta_commission
>>> )
>>> SELECT --+index(r receipt_pstat_index)
>>> r.batch_no,
>>> r.line_no,
>>> r.payee_id,
>>> r.ctype_code,
>>> r.branch_id,
>>> r.osj_id,
>>> r.rep_id,
>>> r.product_id,
>>> r.client_id,
>>> r.split_code,
>>> r.trans_date,
>>> r.client_name,
>>> r.buy_sell,
>>> r.cusip,
>>> r.units,
>>> r.description,
>>> r.short_desc,
>>> r.security_type,
>>> r.security_calc,
>>> r.principal_agency,
>>> r.listed_otc,
>>> r.order_type,
>>> r.order_terminal,
>>> r.price,
>>> r.invested_amt,
>>> r.commission,
>>> r.payee_split_pct,
>>> r.rep_gross_pct,
>>> r.rep_gross,
>>> r.front_end_pct,
>>> r.rep_adj_gross,
>>> r.rep_payout_pct,
>>> r.rep_net,
>>> r.override_pct,
>>> r.rep_payout,
>>> r.rep_ticket,
>>> r.rep_floor,
>>> r.rep_amount,
>>> r.rep_amount,
>>> r.account_class,
>>> ac.short_desc,
>>> r.firm_id,
>>> r.emp_ind,
>>> r.order_source,
>>> r.payee_branch_id,
>>> r.payee_osj_id,
>>> created_date,
>>> :b1,
>>> comsys.statement_id_seq.nextval,
>>> r.desk_fee_pct,
>>> r.desk_fee,
>>> nvl(r.desk_fee, 0) + nvl(r.commission, 0)
>>> FROM
>>> comsys.receipt_statement_view r,
>>> dcdsys.dcd_account_class ac
>>> WHERE
>>> r.pstat_code = '7'
>>> AND r.account_class = ac.account_class (+)
>>> AND pay_period IS NULL
>>> AND NOT EXISTS (
>>> SELECT
>>> 'x'
>>> FROM
>>> comsys.cm_scheduled_payout_reps_e xcl
>>> WHERE
>>> rep_id = r.payee_id
>>> );
>>> Plan hash value: 947388113 -----------------------------------------------------------------------------------------------------------------------------
>>> | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart|
>>> Pstop |
>>> -----------------------------------------------------------------------------------------------------------------------------
>>> | 0 | INSERT STATEMENT | | | | 127K(100)| | | | | 1 | LOAD TABLE
>>> CONVENTIONAL | | | | | | | | | 2 | SEQUENCE | STATEMENT_ID_SEQ | | | | | |
>>> | | 3 | HASH JOIN RIGHT OUTER | | 4501K| 1038M| 127K (6)| 00:02:19 | | | |
>>> 4 | TABLE ACCESS FULL | DCD_ACCOUNT_CLASS | 69 | 966 | 4 (0)| 00:00:01 | |
>>> | | 5 | HASH JOIN RIGHT ANTI | | 4501K| 978M| 127K (6)| 00:02:19 | | | | 6
>>> | INDEX FAST FULL SCAN | CM_SCHED_PAYOUT_REPS_EXCL_IDX1 | 1595 | 7975 | 11
>>> (0)| 00:00:01 | | | | 7 | PARTITION LIST SINGLE| | 4693K| 998M| 127K (6)|
>>> 00:02:18 | KEY | KEY | | 8 | TABLE ACCESS FULL | RECEIPT | 4693K| 998M|
>>> 127K (6)| 00:02:18 | 1 | 1 |
>>> -----------------------------------------------------------------------------------------------------------------------------
>>> Query Block Name / Object Alias (identified by operation id):
>>> ------------------------------------------------------------- 1 -
>>> SEL$1F949E82 4 - SEL$1F949E82 / AC_at_SEL$1 6 - SEL$1F949E82 /
>>> CM_SCHEDULED_PAYOUT_REPS_EXCL_at_SEL$3 8 - SEL$1F949E82 / RECEIPT_at_SEL$2
>>> Outline Data ------------- /*+ BEGIN_OUTLINE_DATA
>>> IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
>>> DB_VERSION('11.2.0.4') OPT_PARAM('optimizer_index_cost_adj' 50)
>>> OUTLINE_LEAF(_at_"SEL$1F949E82") UNNEST(_at_"SEL$3") OUTLINE_LEAF(@"INS$1")
>>> OUTLINE(_at_"SEL$F5BB74E1") MERGE(_at_"SEL$2") OUTLINE(@"SEL$3")
>>> OUTLINE(_at_"SEL$1") OUTLINE(_at_"SEL$2") FULL(@"INS$1" "LPL_STATEMENT"@"INS$1")
>>> FULL(_at_"SEL$1F949E82" "RECEIPT"_at_"SEL$2") INDEX_FFS(@"SEL$1F949E82"
>>> "CM_SCHEDULED_PAYOUT_REPS_EXCL"_at_"SEL$3"
>>> ("CM_SCHEDULED_PAYOUT_REPS_EXCL"."REP_ID")) FULL(_at_"SEL$1F949E82"
>>> "AC"_at_"SEL$1") LEADING(_at_"SEL$1F949E82" "RECEIPT"@"SEL$2"
>>> "CM_SCHEDULED_PAYOUT_REPS_EXCL"_at_"SEL$3" "AC"_at_"SEL$1")
>>> USE_HASH(_at_"SEL$1F949E82" "CM_SCHEDULED_PAYOUT_REPS_EXCL"_at_"SEL$3")
>>> USE_HASH(_at_"SEL$1F949E82" "AC"_at_"SEL$1") SWAP_JOIN_INPUTS(@"SEL$1F949E82"
>>> "CM_SCHEDULED_PAYOUT_REPS_EXCL"_at_"SEL$3") SWAP_JOIN_INPUTS(_at_"SEL$1F949E82"
>>> "AC"_at_"SEL$1") END_OUTLINE_DATA */
>>> bdhulipa_at_LPLPRD>_at_ash/dashtop event2 sql_id='5uwcpdkvxwdpg'
>>> "timestamp'2020-04-13 14:53:00'" "timestamp'2020-04-13 21:20:00'" Total
>>> Seconds AAS %This EVENT2 FIRST_SEEN LAST_SEEN --------- ------- -------
>>> ------------------------------------------ -------------------
>>> ------------------- 2090 .1 90% ON CPU 2020-04-13 15:16:17 2020-04-13
>>> 15:54:50 200 .0 9% row cache lock 2020-04-13 15:16:37 2020-04-13 15:49:19
>>> 10 .0 0% enq: FB - contention [mode=6] 2020-04-13 15:54:20 2020-04-13
>>> 15:54:20 10 .0 0% gcs drm freeze in enter server mode 2020-04-13 15:18:17
>>> 2020-04-13 15:18:17 10 .0 0% latch: row cache objects 2020-04-13 15:31:58
>>> 2020-04-13 15:31:58 bdhulipa_at_LPLPRD>_at_ash/dashtop
>>> sql_plan_line_id,sql_plan_options,sql_plan_operation sql_id='5uwcpdkvxwdpg'
>>> "timestamp'2020-04-13 14:53:00'" "timestamp'2020-04-13 21:20:00'" Total
>>> Seconds AAS %This SQL_PLAN_LINE_ID SQL_PLAN_OPTIONS SQL_PLAN_OPERATION
>>> FIRST_SEEN LAST_SEEN --------- ------- ------- ----------------
>>> ----------------------------------------------------------------
>>> ----------------------------------------------------------------
>>> ------------------- ------------------- * 1570 .1 68% 2 SEQUENCE
>>> 2020-04-13 15:16:27 2020-04-13 15:54:50 * 450 .0 19% 2020-04-13
>>> 15:16:17 2020-04-13 15:54:40 270 .0 12% 1 LOAD TABLE CONVENTIONAL
>>> 2020-04-13 15:18:17 2020-04-13 15:54:20 20 .0 1% INSERT STATEMENT
>>> 2020-04-13 15:42:09 2020-04-13 15:48:19 10 .0 0% 8 FULL TABLE ACCESS
>>> 2020-04-13 15:22:37 2020-04-13 15:22:37
>>> From above it seems like plan_line_id 2 is running long .
>>> Below is sequence definition :
>>> bdhulipa_at_LPLPRD>_at_PR
>>>
>>> Pivoting output using Tom Kyte's printtab....
>>> ==============================
>>> SEQUENCE_OWNER : COMSYS
>>> SEQUENCE_NAME : STATEMENT_ID_SEQ
>>> MIN_VALUE : 1
>>> MAX_VALUE : 999999999999999999999999999
>>> INCREMENT_BY : 1
>>> CYCLE_FLAG : N
>>> ORDER_FLAG : N
>>> CACHE_SIZE : 0
>>> LAST_NUMBER : 600414874
>>> PL/SQL procedure successfully completed.
>>>
>>> Thanks
>>> Bhavani.
>>>
>>
>
> --
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l