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@xxxxxxxxx <mailto:xt.and.r@xxxxxxxxx>> 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@xxxxxxxxx <mailto:bhavanidba6@xxxxxxxxx>>:
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@SEL$1 6 - SEL$1F949E82
/ CM_SCHEDULED_PAYOUT_REPS_EXCL@SEL$3 8 - SEL$1F949E82 /
RECEIPT@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(@"SEL$1F949E82") UNNEST(@"SEL$3")
OUTLINE_LEAF(@"INS$1") OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2") OUTLINE(@"SEL$3") OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2") FULL(@"INS$1" "LPL_STATEMENT"@"INS$1")
FULL(@"SEL$1F949E82" "RECEIPT"@"SEL$2")
INDEX_FFS(@"SEL$1F949E82"
"CM_SCHEDULED_PAYOUT_REPS_EXCL"@"SEL$3"
("CM_SCHEDULED_PAYOUT_REPS_EXCL"."REP_ID"))
FULL(@"SEL$1F949E82" "AC"@"SEL$1") LEADING(@"SEL$1F949E82"
"RECEIPT"@"SEL$2" "CM_SCHEDULED_PAYOUT_REPS_EXCL"@"SEL$3"
"AC"@"SEL$1") USE_HASH(@"SEL$1F949E82"
"CM_SCHEDULED_PAYOUT_REPS_EXCL"@"SEL$3")
USE_HASH(@"SEL$1F949E82" "AC"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1F949E82"
"CM_SCHEDULED_PAYOUT_REPS_EXCL"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$1F949E82" "AC"@"SEL$1")
END_OUTLINE_DATA */
bdhulipa@LPLPRD>@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@LPLPRD>@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@LPLPRD>@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.
