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@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@xxxxxxxxx>
To: "denis.sun@xxxxxxxxx" <denis.sun@xxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx" 
<oracle-l@xxxxxxxxxxxxx> 
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@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 
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


Other related posts: