Re: high gets per execution for an update based on rowid
From: Denis <denis.sun_at_yahoo.com>
Date: Tue, 10 Apr 2012 13:37:33 -0700 (PDT)
Message-ID: <1334090253.78652.YahooMailNeo_at_web161805.mail.bf1.yahoo.com>
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
Not trigger, no any indexes on the table, usually the number of rows is around 4000.
On another targets with everything same ( OS , version etc as far as we knonw), the gets is normal:
xxxx_at_xxxxDR> 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
365983325 5.9786867
1 row selected.
Denis
From: David Fitzjarrell <oratune_at_yahoo.com> To: "denis.sun_at_yahoo.com" <denis.sun_at_yahoo.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, April 10, 2012 4:31 PM
Subject: Re: high gets per execution for an update based on rowid
From: Denis <denis.sun_at_yahoo.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, April 10, 2012 2:10 PM
Subject: high gets per execution for an update based on rowid
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
The problem started yesterday noon:
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
Date: Tue, 10 Apr 2012 13:37:33 -0700 (PDT)
Message-ID: <1334090253.78652.YahooMailNeo_at_web161805.mail.bf1.yahoo.com>
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
Not trigger, no any indexes on the table, usually the number of rows is around 4000.
On another targets with everything same ( OS , version etc as far as we knonw), the gets is normal:
xxxx_at_xxxxDR> 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
365983325 5.9786867
1 row selected.
Denis
From: David Fitzjarrell <oratune_at_yahoo.com> To: "denis.sun_at_yahoo.com" <denis.sun_at_yahoo.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, April 10, 2012 4:31 PM
Subject: Re: high gets per execution for an update based on rowid
Posting the Oracle version would help tremendously.
David Fitzjarrell
From: Denis <denis.sun_at_yahoo.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, April 10, 2012 2:10 PM
Subject: high gets per execution for an update based on rowid
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.00008-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.225ps :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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 10 2012 - 15:37:33 CDT