Re: Design question regarding event log table processing

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 24 Jan 2013 14:08:28 -0800 (PST)
Message-ID: <1359065308.66029.YahooMailNeo_at_web121604.mail.ne1.yahoo.com>



As an example:
 

SQL> create table pending_txns(
  2          txn_id  number not null,
  3          txn_dt  date not null,
  4          url     varchar2(200) not null,
  5          url_err varchar2(200),
  6          proc_dt date,
  7          processed       number
  8  );
 

Table created.
 

SQL>
SQL> create index pend_txn_idx
  2  on pending_txns(processed, txn_id, url_err);
 

Index created.
 

SQL>
SQL> create table processed_txns(
  2          txn_id  number not null,
  3          url     varchar2(200) not null,
  4          proc_dt date,
  5          success number
  6  );
 

Table created.
 

SQL>
SQL> create index proc_txn_idx
  2  on processed_txns(txn_id, success);
 

Index created.
 

SQL>
SQL> begin
  2          for i in 1..1000 loop
  3                  insert into pending_txns
  4                  values(i, sysdate, 'http://myurl.com', null, null, null);
  5          end loop;
  6
  7          commit;
  8
  9  end;
 10  /
 

PL/SQL procedure successfully completed.
 
SQL>
SQL> set autotrace on
SQL>
SQL> insert into processed_txns

  2  select txn_id, url, sysdate, nvl2(url_err, -1, 1)
  3  from pending_txns
  4  where processed is null;
 

1000 rows created.
Execution Plan

Plan hash value: 48555499
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |                |  1000 |   224K|     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL     | PROCESSED_TXNS |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| PENDING_TXNS   |  1000 |   224K|     1   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | PEND_TXN_IDX   |    50 |       |     1   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   3 - access("PROCESSED" IS NULL)

Note

   - dynamic sampling used for this statement (level=2)
Statistics

         64  recursive calls
        265  db block gets
        139  consistent gets
          0  physical reads
     131572  redo size
        673  bytes sent via SQL*Net to client
        694  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1000  rows processed
 

SQL>
SQL> update pending_txns
  2  set proc_dt=sysdate, processed=1
  3  where processed is null
  4  and url_err is null;
 

1000 rows updated.
Execution Plan

Plan hash value: 3491915266

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | UPDATE STATEMENT  |              |     1 |   137 |     1   (0)| 00:00:01 |
|   1 |  UPDATE           | PENDING_TXNS |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| PEND_TXN_IDX |     1 |   137 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):

   2 - access("PROCESSED" IS NULL AND "URL_ERR" IS NULL)
       filter("URL_ERR" IS NULL)

Note

   - dynamic sampling used for this statement (level=2)

Statistics



         26  recursive calls
       2403  db block gets
        175  consistent gets
          0  physical reads
     554000  redo size
        673  bytes sent via SQL*Net to client
        672  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1000  rows processed
 

SQL>
SQL> commit;
 

Commit complete.
 

SQL>
SQL> select processed, count(*)
  2  from pending_txns
  3  group by processed;
 
 PROCESSED   COUNT(*)

---------- ----------
         1       1000

Execution Plan



Plan hash value: 3847073747

| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT     |              |  1000 | 13000 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|              |  1000 | 13000 |     3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | PEND_TXN_IDX |  1000 | 13000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Note

   - dynamic sampling used for this statement (level=2)

Statistics



          5  recursive calls
          0  db block gets
         32  consistent gets
          0  physical reads
        436  redo size
        488  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 

SQL>
SQL> select success, count(*)
  2  from processed_txns
  3  group by success;
 
   SUCCESS   COUNT(*)

---------- ----------
         1       1000

Execution Plan



Plan hash value: 929678157

| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT |              |  1000 | 13000 |     2  (50)| 00:00:01 |
|   1 |  HASH GROUP BY   |              |  1000 | 13000 |     2  (50)| 00:00:01 |
|   2 |   INDEX FULL SCAN| PROC_TXN_IDX |  1000 | 13000 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note

   - dynamic sampling used for this statement (level=2)

Statistics



          5  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
         80  redo size
        486  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>
SQL> set autotrace off
SQL>
SQL> declare

  2          max_id number;
  3  begin
  4          select max(txn_id) into max_id from pending_txns;
  5
  6          for i in 1..1000 loop
  7                  if mod(i,2) = 0 then
  8                  insert into pending_txns
  9                  values(i + max_id, sysdate, 'http://myurl.com', null, null, null);
 10                  else
 11                  insert into pending_txns
 12                  values(i + max_id, sysdate, 'http://myurl.com', '404', null, null);
 13                  end if;
 14          end loop;
 15
 16          commit;
 17
 18  end;
 19  /
 

PL/SQL procedure successfully completed.
 
SQL>
SQL> set autotrace on
SQL>
SQL> insert into processed_txns

  2  select txn_id, url, sysdate, nvl2(url_err, -1, 1)
  3  from pending_txns
  4  where processed is null;
 

1000 rows created.

Execution Plan



Plan hash value: 48555499
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |                |  1000 |   224K|     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL     | PROCESSED_TXNS |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| PENDING_TXNS   |  1000 |   224K|     1   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | PEND_TXN_IDX   |   100 |       |     1   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   3 - access("PROCESSED" IS NULL)

Note

   - dynamic sampling used for this statement (level=2)

Statistics



         10  recursive calls
        140  db block gets
         61  consistent gets
          0  physical reads
     121104  redo size
        675  bytes sent via SQL*Net to client
        694  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed
 

SQL>
SQL> update pending_txns
  2  set proc_dt=sysdate, processed=1
  3  where processed is null
  4  and url_err is null;
 

500 rows updated.

Execution Plan



Plan hash value: 3491915266

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | UPDATE STATEMENT  |              |     1 |   137 |     1   (0)| 00:00:01 |
|   1 |  UPDATE           | PENDING_TXNS |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| PEND_TXN_IDX |     1 |   137 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):

   2 - access("PROCESSED" IS NULL AND "URL_ERR" IS NULL)
       filter("URL_ERR" IS NULL)

Note

   - dynamic sampling used for this statement (level=2)

Statistics



          0  recursive calls
        864  db block gets
         13  consistent gets
          0  physical reads
     234516  redo size
        676  bytes sent via SQL*Net to client
        672  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        500  rows processed
 

SQL>
SQL> commit;
 

Commit complete.
 

SQL>
SQL> select processed, count(*)
  2  from pending_txns
  3  group by processed;
 
 PROCESSED   COUNT(*)

---------- ----------
         1       1500
                  500

Execution Plan



Plan hash value: 3847073747

| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT     |              |  2000 | 26000 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|              |  2000 | 26000 |     3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | PEND_TXN_IDX |  2000 | 26000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Note

   - dynamic sampling used for this statement (level=2)

Statistics



          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
        196  redo size
        527  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
 

SQL>
SQL> select success, count(*)
  2  from processed_txns
  3  group by success;
 
   SUCCESS   COUNT(*)

---------- ----------
         1       1500
        -1        500

Execution Plan



Plan hash value: 929678157

| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT |              |  2000 | 26000 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY   |              |  2000 | 26000 |     4  (25)| 00:00:01 |
|   2 |   INDEX FULL SCAN| PROC_TXN_IDX |  2000 | 26000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note

   - dynamic sampling used for this statement (level=2)

Statistics



          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
 

SQL> As stated in a previous response adding an 'indicator' column should be fairly easy and can be indexed to provide fast access to unprocessed records.
 

David Fitzjarrell

From: Bruno Lavoie <bl_at_brunol.com>
To: Sayan Malakshinov <xt.and.r_at_gmail.com> Cc: oracle-l_at_freelists.org
Sent: Thursday, January 24, 2013 1:49 PM Subject: Re: Design question regarding event log table processing

Hello Sayan,
yes you're right, direct with simplicity... I though of a consumption timestamp to be useful for some precessing time stats and reuse this column. Your suggested column can simply be added in conjunction with the timestamp.

Thanks
Bruno

On Thu, Jan 24, 2013 at 1:36 PM, Sayan Malakshinov <xt.and.r_at_gmail.com>wrote:

> Why not a simple indexed field "NOT_PROCESSED" number(1), where "null"
> means that it is already processed and 1 where not?
>
> --
> Best regards,
> Sayan Malakshinov
> Senior performance tuning engineer
> PSBank
> Tel: +7 903 207-1576
>


--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 24 2013 - 23:08:28 CET

Original text of this message