Re: Insert running long with Sequence

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