RE: high gets per execution for an update based on rowid
From: Iotzov, Iordan <IIotzov_at_newsamerica.com>
Date: Tue, 10 Apr 2012 17:07:31 -0400
Message-ID: <9287D00721407A4CBDC9925C789DBB67020581453C_at_nam-wil-exc-l03.newsamerica.com>
Have you looked at the system/session statistics /v$sysstat , v$sesstat / during that period? Did you see any change in the pattern of what your DB was doing around the time the problem started according to those statistics? That information could help you understanding better how the logical IO (LIO) was spent (to apply undo records to get read consistency or whatever else might be).
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
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.
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
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
CREATE TABLE "SPLEX"."SHAREPLEX_TRANS"
) 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 ..
Date: Tue, 10 Apr 2012 17:07:31 -0400
Message-ID: <9287D00721407A4CBDC9925C789DBB67020581453C_at_nam-wil-exc-l03.newsamerica.com>
Have you looked at the system/session statistics /v$sysstat , v$sesstat / during that period? Did you see any change in the pattern of what your DB was doing around the time the problem started according to those statistics? That information could help you understanding better how the logical IO (LIO) was spent (to apply undo records to get read consistency or whatever else might be).
Iordan Iotzov
http://iiotzov.wordpress.com/
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Denis
Sent: Tuesday, April 10, 2012 4:38 PM
To: David Fitzjarrell; oracle-l_at_freelists.org
Subject: Re: high gets per execution for an update based on rowid
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.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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 10 2012 - 16:07:31 CDT