Re: Insert running long with Sequence
Date: Wed, 15 Apr 2020 17:39:41 +0200
Message-ID: <3591ad9e-797e-25a9-13d1-0a9701c5187b_at_bluewin.ch>
Hi,
/what is the drawback of setting to 0 ?/
It is damn slow.
Regards
Lothar
Am 15.04.2020 um 17:24 schrieb Bhavani Dhulipalla:
/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.
> 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
> <mailto: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 <mailto: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(_at_"INS$1") OUTLINE(_at_"SEL$F5BB74E1")
> MERGE(_at_"SEL$2") OUTLINE(_at_"SEL$3") OUTLINE(@"SEL$1")
> OUTLINE(_at_"SEL$2") FULL(_at_"INS$1" "LPL_STATEMENT"@"INS$1")
> FULL(_at_"SEL$1F949E82" "RECEIPT"_at_"SEL$2")
> INDEX_FFS(_at_"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(@"SEL$1F949E82"
> "RECEIPT"_at_"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(_at_"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-lReceived on Wed Apr 15 2020 - 17:39:41 CEST