high gets per execution for an update based on rowid
- From: Denis <denis.sun@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Apr 2012 13:10:24 -0700 (PDT)
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
~~~~~
09-APR 07:30 182,900 2.1 0.0
0.000 0.000
09-APR 08:00 239,119 2.1 0.0
0.000 0.000
09-APR 08:30 276,851 2.1 0.0
0.000 0.000
09-APR 09:00 267,466 2.2 0.0
0.000 0.000
09-APR 09:30 233,059 2.1 0.0
0.000 0.000
09-APR 10:00 231,006 2.2 0.0
0.000 0.000
09-APR 10:30 241,050 2.2 0.0
0.000 0.000
09-APR 11:00 248,550 2.2 0.0
0.000 0.000
09-APR 11:31 259,654 5.7 0.0
0.000 0.000
09-APR 12:00 97,615 596.7 0.0
0.079 0.080
09-APR 12:30 22,176 2,455.8 0.0
0.532 0.541
09-APR 13:00 17,262 3,118.2 0.0
0.681 0.694
09-APR 13:30 18,558 2,959.9 0.0
0.634 0.644
09-APR 14:00 20,746 2,374.5 0.0
0.535 0.543
09-APR 14:30 28,066 2,020.6 0.0
0.399 0.404
09-APR 15:00 25,121 2,256.3 0.0
0.421 0.425
09-APR 15:30 22,349 2,488.1 0.0
0.486 0.492
09-APR 16:00 20,195 2,715.1 0.0
0.546 0.553
09-APR 16:30 20,117 2,711.3 0.0
0.576 0.585
09-APR 17:00 22,098 2,524.2 0.0
0.511 0.517
09-APR 17:30 22,949 2,448.9 0.0
0.454 0.459
09-APR 18:00 26,879 2,068.6 0.0
0.391 0.395
09-APR 18:30 28,609 1,977.3 0.0
0.365 0.369
09-APR 19:00 26,516 2,047.9 0.0
0.417 0.423
09-APR 19:30 25,905 2,199.3 0.0
0.411 0.416
09-APR 20:00 25,723 2,225.0 0.0
0.411 0.415
09-APR 20:30 28,711 1,996.6 0.0
0.362 0.365
09-APR 21:00 25,093 2,160.2 0.0
0.426 0.431
09-APR 21:30 26,004 2,203.1 0.0
0.405 0.411
09-APR 22:00 22,299 2,306.3 0.0
0.504 0.515
09-APR 22:30 27,452 2,042.2 0.0
0.402 0.408
09-APR 23:00 23,811 2,245.4 0.0
0.494 0.505
09-APR 23:30 24,851 2,288.0 0.0
0.445 0.450
10-APR 00:00 281,031 93.4 0.0
0.018 0.018
10-APR 00:30 482,017 2.0 0.0
0.000 0.000
10-APR 01:00 377,503 2.2 0.0
0.000 0.000
10-APR 01:30 409,588 2.3 0.0
0.000 0.000
10-APR 02:00 274,517 2.2 0.0
0.000 0.000
10-APR 02:30 210,895 227.5 0.0
0.024 0.024
10-APR 03:00 55,286 1,063.0 0.0
0.152 0.153
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
Other related posts:
