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.
4 where processed is null;
1000 rows created.
Execution Plan
Plan hash value: 48555499
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 |
2 - access("PROCESSED" IS NULL AND "URL_ERR" IS NULL)
filter("URL_ERR" IS NULL)
Note
- dynamic sampling used for this statement (level=2)
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
Plan hash value: 3847073747
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- dynamic sampling used for this statement (level=2)
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
Plan hash value: 929678157
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- dynamic sampling used for this statement (level=2)
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
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.
4 where processed is null;
1000 rows created.
Plan hash value: 48555499
Predicate Information (identified by operation id):
3 - access("PROCESSED" IS NULL)
Note
- dynamic sampling used for this statement (level=2)
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.
Plan hash value: 3491915266
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
2 - access("PROCESSED" IS NULL AND "URL_ERR" IS NULL)
filter("URL_ERR" IS NULL)
Note
- dynamic sampling used for this statement (level=2)
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
Plan hash value: 3847073747
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- dynamic sampling used for this statement (level=2)
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
Plan hash value: 929678157
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- dynamic sampling used for this statement (level=2)
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
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_txns3 from pending_txns
2 select txn_id, url, sysdate, nvl2(url_err, -1, 1)
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> declare3 begin
2 max_id number;
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_txns3 from pending_txns
2 select txn_id, url, sysdate, nvl2(url_err, -1, 1)
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-lReceived on Thu Jan 24 2013 - 23:08:28 CET