Insert running long with Sequence

From: Bhavani Dhulipalla <bhavanidba6_at_gmail.com>
Date: Wed, 15 Apr 2020 11:06:57 -0400
Message-ID: <CAC=-2+zW4DwCCHUCFQ9NNwK_QqTvzfa4js2vRo=xKShcmKSkUQ_at_mail.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

Received on Wed Apr 15 2020 - 17:06:57 CEST