Re: Executions != Rows_Processed for single-row INSERT

From: Anurag Varma <avoracle_at_gmail.com>
Date: 16 May 2007 14:49:57 -0700
Message-ID: <1179352197.020753.206950@k79g2000hse.googlegroups.com>

On May 15, 2:54 am, Vsevolod Afanassiev <vafanass..._at_yahoo.com> wrote:
> Hi,
>
> I've noticed strange discrepancy in Oracle statistics: for particular
> singe-row INSERT statement
> value of V$SQL statistics Executions != Rows_Processed. For example:
>
> INST_ID EXECUTIONS FIRST_LOAD_TIME ROWS_PROCESSED
> ---------- ---------- ------------------- --------------
> SQL_TEXT
> --------------------------------------------------------------------------------
> 1 480394 2007-05-11/03:33:04 3898236
> INSERT INTO ec_journal (saveid, ip, jo_type, jo_process,
> jo_subprocess, jo_servi
> ce, jo_function, jo_message_id, jo_request_time, jo_response_time,
> jo_timediff,
> jo_retries, jo_maxretries, jo_returncode, jo_errorcode,
> jo_errormessage) VALUES
> (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :
> 16)
>
> 2 109531 2007-05-11/03:40:24 538666
> INSERT INTO ec_journal (saveid, ip, jo_type, jo_process,
> jo_subprocess, jo_servi
> ce, jo_function, jo_message_id, jo_request_time, jo_response_time,
> jo_timediff,
> jo_retries, jo_maxretries, jo_returncode, jo_errorcode,
> jo_errormessage) VALUES
> (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :
> 16)
>
> The value in ROWS_PROCESSED is correct as we know how many rows gets
> inserted in this table per day.
>
> All other frequently executed single-row insert statements have
> Executions = Rows_Processed.
>
> This is 9.2.0.6.0 two-node RAC.
>
> Thanks

One reason can be that some inserts failed.

See test example below:

ORA92> create table abcd (a varchar2(5));

Table created.

ORA92> insert into abcd values ('A');

1 row created.

ORA92> insert into abcd values ('B');

1 row created.

ORA92> insert into abcd values ('ABCDEF'); insert into abcd values ('ABCDEF')

                          *

ERROR at line 1:
ORA-01401: inserted value too large for column

ORA92> commit;

Commit complete.

ORA92> select sql_text, executions, rows_processed from v$sqlarea where sql_text like 'insert into abcd %';

SQL_TEXT

---

EXECUTIONS ROWS_PROCESSED
---------- --------------
insert into abcd values (:"SYS_B_0")

         3 2

• This test was done while cursor_sharing was set to SIMILAR .. so that literals don't make it a different insert statement...

Anurag Received on Wed May 16 2007 - 16:49:57 CDT