Re: high gets per execution for an update based on rowid
- From: Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>
- To: denis.sun@xxxxxxxxx
- Date: Tue, 10 Apr 2012 17:36:44 -0400
Denis,
<<< resending since my message was placed on hold >>>
I offer to help. I would need a SQLT XTRXEC for this SQL_ID. Please find
SQLT undex MOS 215187.1
Cheers -- Carlos
On Tue, Apr 10, 2012 at 4:10 PM, Denis <denis.sun@xxxxxxxxx> wrote:
> Hi, listers,
>
> The update based on rowid as shown below, which is a statement issued by
> SharePlex process at the target database, has about 2.5 K per execution,
> any thoughts about what could the cause?
> we truncate the table per support, we rebuild the table,we move it to
> another tablespace, we change inittrans, pctfree, pctused, not help.
>
> SQLT> select sql_id, sql_text,executions, buffer_gets/executions from
> v$sql where sql_id='9x1h5r7vgd662';
> SQL_ID
> -------------
> SQL_TEXT
>
> ---------------------------------------------------------------------------------------------------------------------------------------------
> -----------------------------
> EXECUTIONS BUFFER_GETS/EXECUTIONS
> ---------- ----------------------
> 9x1h5r7vgd662
> update "SPLEX".SHAREPLEX_TRANS set que_seq_no_1 = :1, que_seq_no_2 = :2,
> combo = :3, op_type = :4 where rowid = :5
> 7530 2573.28725
>
> PLAN_TABLE_OUTPUT
>
> ---------------------------------------------------------------------------------------------------------------
> SQL_ID 9x1h5r7vgd662, child number 0
> -------------------------------------
> update "SPLEX".SHAREPLEX_TRANS set que_seq_no_1 = :1, que_seq_no_2 = :2,
> combo = :3,
> op_type = :4 where rowid = :5
> Plan hash value: 2573686592
>
> -----------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost (%CPU)| Time |
>
> -----------------------------------------------------------------------------------------------
> | 0 | UPDATE STATEMENT | | |
> | 1 (100)| |
> | 1 | UPDATE | SHAREPLEX_TRANS | |
> | | |
> | 2 | TABLE ACCESS BY USER ROWID| SHAREPLEX_TRANS | 1 | 128
> | 1 (0)| 00:00:01 |
>
> -----------------------------------------------------------------------------------------------
>
> The problem started yesterday noon:
>
> Summary Execution Statistics
> Over Time
>
> Avg Avg
> Snapshot Avg LIO Avg PIO CPU
> (secs) Elapsed (secs)
> Time Execs Per Exec Per Exec
> Per Exec Per Exec
> ------------ -------- ------------------ ------------------
> -------------------- --------------------
> 08-APR 14:30 45,036 2.0 0.0
> 0.000 0.000
> 08-APR 15:00 49,890 2.0 0.0
> 0.000 0.000
> 08-APR 15:30 42,645 2.0 0.0
> 0.000 0.000
> ~~~~~
>
> 10-APR 03:30 44,380 1,357.6 0.0
> 0.200 0.201
> 10-APR 04:00 42,205 1,361.4 0.0
> 0.223 0.225
> ps :the original table def:
> CREATE TABLE "SPLEX"."SHAREPLEX_TRANS"
> ( "TRANS_NUM" NUMBER,
> "QUE_SEQ_NO_1" NUMBER,
> "QUE_SEQ_NO_2" NUMBER,
> "COMBO" VARCHAR2(150),
> "OP_TYPE" NUMBER
> ) PCTFREE 99 PCTUSED 0 INITRANS 20 MAXTRANS 255 NOCOMPRESS LOGGING
> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> TABLESPACE "SPLEX_DATA_1" ;
> it seems shareplex wanst one row per block ..
>
> we have no idea what happens at this moment and hugh backlog for the
> replication due to this.
>
> Denis
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
