In 2009 I have presented "Alles Over Groeperen" twice in the Netherlands and the English version "All About Grouping" also twice, once at the Oracle OpenWorld Unconference and once at UKOUG. If you haven't been able to visit one of these presentations, and you are interested in the subject, then you can catch up by reading the paper that I just finished.
The usual sequence of events is to first produce a paper and then present the paper at conferences. When I was writing the paper, I discovered one or two new things. For example, I gained a preciser understanding of how a cube is implemented. Unfortunately, I did not include that in any of the presentations. So next time, I'll make sure to first write the paper and then present it. A nice lesson at the end of 2009.
Enjoy reading. If you have some remarks about the contents of the paper, then please leave a comment here or send an e-mail.
And I wish all readers a healthy 2010 and the wisdom to pursue your dreams.
Pages
▼
Thursday, December 31, 2009
Wednesday, December 16, 2009
Journaling using flashback data archives in 11.2.0.1?
In version 11.1.0.6, Oracle introduced flashback data archives under the marketing term Total Recall. Previously you could only flashback your table to an earlier point in time, as long as the needed undo information was available. With flashback data archives you can extend this period with as long as you specify. You create a flashback data archive, specify a retention period and associate the archive with a table. In version 11.1.0.6 there was a nasty bug, as I described in this post. In version 11.1.0.7, Oracle fixed this bug, and you could safely use flashback data archives for journaling, as described here. However, you could not modify the schema, for example add a constraint to a table with a flashback data archive associated, in 11.1.0.7. In the mentioned post I described how to overcome that, but it was cumbersome.
And then came 11.2.0.1. The new features guide mentions:
So now it seems the last restriction is removed and we can use this technology for journaling. Let's test how it works in 11.2.0.1 by using a similar setup as in my first two blog posts about this subject.
The tablespace already exists:
Create the flashback archive, a foreign key table and a table T with the flashback data archive associated:
The flashback data archive table is created:
Add a row to the foreign key table and switch on auditing to get access to the user. This is done because all journaling information is available when using Flashback Version Query, except for the user who changed the data. Switching on auditing records much more than just the username (just do a describe of sys.aud$), but at least it gives the username without additional coding.
And create the view that represents the journaling table:
Now add some data to see if it works:
Everything is there: 10 inserted rows, 1 updated row and 1 deleted row.
Now, add an extra column with a check constraint on it. Adding a column was possible in 11.1.0.x as well, but adding the constraint was not possible. Now it is.
Note though that it takes considerably more time when adding a constraint on a history-tracked table than on a regular table. On my laptop it consistently takes approximately 10 seconds.
Now adjust the journaling view to have it contain the extra column:
And select from the new view:
11 rows? Before adding the column and constraint, there were 12! The last one - the delete - has disappeared. To find out if it is the last one or the delete, I add another column with constraint, and do a delete followed by an insert. Will it remove the last (the insert) or the delete?
So it is the delete that is automatically removed.
Finally I want to know whether it was the addition of the column or the addition of the constraint that made the delete statement disappear. Since this behaviour was not there in 11.1.0.7 when you could add a column, I was tempted to believe it is the addition of the constraint. To know for sure, I repeated the above piece of code without adding the constraint:
So it is the addition of a column that made the delete disappear, and not the addition of the constraint.
The conclusion is that flashback data archives still cannot be used for journaling, but since the above seems like a quite obvious bug, a future patch set will probably solve it in the near future. And when that happens, it finally looks promising to use for journaling.
And then came 11.2.0.1. The new features guide mentions:
1.5.1.1 Flashback Data Archive Support for DDLs
Oracle Database 11g Release 2 (11.2) users can now use most DDL commands on tables that are being tracked with Flashback Data Archive. This includes:
* Add, Drop, Rename, Modify Column
* Drop, Truncate Partition
* Rename, Truncate Table
* Add, Drop, Rename, Modify Constraint
For more complex DDL (for example, upgrades and split table), the Disassociate and Associate PL/SQL procedures can be used to temporarily disable Total Recall on specified tables. The Associate procedure enforces schema integrity after association; the base table and history table schemas must be the same.
This feature makes it much easier to use the Total Recall option with complex applications that require the ability to modify the schema.
So now it seems the last restriction is removed and we can use this technology for journaling. Let's test how it works in 11.2.0.1 by using a similar setup as in my first two blog posts about this subject.
The tablespace already exists:
rwijk@ORA11GR2> create tablespace my_tablespace datafile 'extra_file.dat' size 10M
2 /
create tablespace my_tablespace datafile 'extra_file.dat' size 10M
*
ERROR at line 1:
ORA-01543: tablespace 'MY_TABLESPACE' already exists
Create the flashback archive, a foreign key table and a table T with the flashback data archive associated:
rwijk@ORA11GR2> create flashback archive flashback_archive_10_years
2 tablespace my_tablespace
3 retention 10 year
4 /
Flashback archive created.
rwijk@ORA11GR2> create table fktable (col number(10) primary key)
2 /
Table created.
rwijk@ORA11GR2> create table t
2 ( pkcol number(10) primary key
3 , fkcol number(10) not null references fktable(col)
4 , description varchar2(11)
5 )
6 flashback archive flashback_archive_10_years
7 /
Table created.
The flashback data archive table is created:
rwijk@ORA11GR2> select archive_table_name
2 from dba_flashback_archive_tables
3 where table_name = 'T'
4 /
ARCHIVE_TABLE_NAME
-----------------------------------------------------
SYS_FBA_HIST_75925
1 row selected.
rwijk@ORA11GR2> exec dbms_lock.sleep(15)
PL/SQL procedure successfully completed.
Add a row to the foreign key table and switch on auditing to get access to the user. This is done because all journaling information is available when using Flashback Version Query, except for the user who changed the data. Switching on auditing records much more than just the username (just do a describe of sys.aud$), but at least it gives the username without additional coding.
rwijk@ORA11GR2> insert into fktable (col) values (1)
2 /
1 row created.
rwijk@ORA11GR2> audit insert,update,delete on t by access
2 /
Audit succeeded.
And create the view that represents the journaling table:
rwijk@ORA11GR2> create view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 from t versions between scn minvalue and maxvalue tv
11 , user_audit_object ao
12 where tv.versions_xid = ao.transactionid (+)
13 /
View created.
Now add some data to see if it works:
rwijk@ORA11GR2> insert into t
2 select level
3 , 1
4 , lpad('*',11,'*')
5 from dual
6 connect by level <= 10
7 /
10 rows created.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> update t
2 set description = 'a'
3 where pkcol = 1
4 /
1 row updated.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> delete t
2 where pkcol = 7
3 /
1 row deleted.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION
- ---------- -------------------------------- ---------- ----- ----- -----------
I RWIJK 16-DEC-09 03.02.16 PM 5032930 7 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 2 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 10 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 9 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 8 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 1 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 6 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 5 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 4 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 3 1 ***********
U RWIJK 16-DEC-09 03.02.34 PM 5032938 1 1 a
D RWIJK 16-DEC-09 03.02.34 PM 5032941 7 1 ***********
12 rows selected.
Everything is there: 10 inserted rows, 1 updated row and 1 deleted row.
Now, add an extra column with a check constraint on it. Adding a column was possible in 11.1.0.x as well, but adding the constraint was not possible. Now it is.
rwijk@ORA11GR2> alter table t add (status varchar2(3))
2 /
Table altered.
rwijk@ORA11GR2> alter table t add constraint ck_status check (status in ('NEW','OLD'))
2 /
Table altered.
Note though that it takes considerably more time when adding a constraint on a history-tracked table than on a regular table. On my laptop it consistently takes approximately 10 seconds.
Now adjust the journaling view to have it contain the extra column:
rwijk@ORA11GR2> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 from t versions between scn minvalue and maxvalue tv
12 , user_audit_object ao
13 where tv.versions_xid = ao.transactionid (+)
14 /
View created.
And select from the new view:
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- -------------------------------- ---------- ----- ----- ----------- ------
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 1 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 7 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 4 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 2 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 3 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 10 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 5 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 6 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 8 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 9 1 ***********
U RWIJK 16-DEC-09 03.19.18.000000000 PM 5034217 1 1 a
11 rows selected.
11 rows? Before adding the column and constraint, there were 12! The last one - the delete - has disappeared. To find out if it is the last one or the delete, I add another column with constraint, and do a delete followed by an insert. Will it remove the last (the insert) or the delete?
rwijk@ORA11GR2> delete t where pkcol in (3,5)
2 /
2 rows deleted.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> insert into t values ( 11, 1, 'bla', 'OLD' )
2 /
1 row created.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> alter table t add (status2 varchar2(3))
2 /
Table altered.
rwijk@ORA11GR2> alter table t add constraint ck_status2 check (status2 in ('NEW','OLD'))
2 /
Table altered.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 , tv.status2
12 from t versions between scn minvalue and maxvalue tv
13 , user_audit_object ao
14 where tv.versions_xid = ao.transactionid (+)
15 /
View created.
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS STA
- ---------- -------------------------------- ---------- ----- ----- ----------- ------ ---
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 1 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 7 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 3 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 5 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 2 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 9 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 10 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 4 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 6 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 8 1 ***********
U RWIJK 16-DEC-09 03.19.18.000000000 PM 5034217 1 1 a
I RWIJK 16-DEC-09 03.19.53.000000000 PM 5034390 11 1 bla OLD
12 rows selected.
So it is the delete that is automatically removed.
Finally I want to know whether it was the addition of the column or the addition of the constraint that made the delete statement disappear. Since this behaviour was not there in 11.1.0.7 when you could add a column, I was tempted to believe it is the addition of the constraint. To know for sure, I repeated the above piece of code without adding the constraint:
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- -------------------------------- ---------- ----- ----- ----------- ------
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 6 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 1 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 7 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 2 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 10 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 9 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 8 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 5 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 4 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 3 1 ***********
U RWIJK 16-DEC-09 03.44.04.000000000 PM 5035535 1 1 a
D RWIJK 16-DEC-09 03.44.41.000000000 PM 5035710 3 1 ***********
D RWIJK 16-DEC-09 03.44.41.000000000 PM 5035710 5 1 ***********
I RWIJK 16-DEC-09 03.44.41.000000000 PM 5035713 11 1 bla OLD
14 rows selected.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> alter table t add (status2 varchar2(3))
2 /
Table altered.
rwijk@ORA11GR2> --alter table t add constraint ck_status2 check (status2 in ('NEW','OLD'))
rwijk@ORA11GR2> --/
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 , tv.status2
12 from t versions between scn minvalue and maxvalue tv
13 , user_audit_object ao
14 where tv.versions_xid = ao.transactionid (+)
15 /
View created.
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS STA
- ---------- -------------------------------- ---------- ----- ----- ----------- ------ ---
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 1 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 7 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 3 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 5 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 2 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 9 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 10 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 4 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 6 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 8 1 ***********
U RWIJK 16-DEC-09 03.44.04.000000000 PM 5035535 1 1 a
I RWIJK 16-DEC-09 03.44.41.000000000 PM 5035713 11 1 bla OLD
12 rows selected.
So it is the addition of a column that made the delete disappear, and not the addition of the constraint.
The conclusion is that flashback data archives still cannot be used for journaling, but since the above seems like a quite obvious bug, a future patch set will probably solve it in the near future. And when that happens, it finally looks promising to use for journaling.
Wednesday, December 9, 2009
SYS_GUID()
A colleague of mine sent me this link, and wanted me to have a look at point 5:
I knew about the SYS_GUID() function, but didn't realize you can use it as a default value for a primary key column. That sure is convenient. Where other RDBMS all have some sort of autonumber/identity column to automatically fill an ID column, Oracle uses sequences, which is a little more cumbersome to implement. However, I found the story not really balanced. Before addressing each point and mentioning some of the disadvantages of SYS_GUID, let's see how it works. Using sequences, a simple setup can be like this:
Here I create the sequence with "cache 1000", because I want a session to fetch 1000 numbers at once, instead of one each time. This reduces the overhead for Oracle to maintain the SYS.SEQ$ table. Gaps in the numbers might be large this way, but this shouldn't bother you. If it does bother you, you likely shouldn't be using a sequence, as they are never gap-free. A table is created without any reference to the sequence:
To fill the id column with the sequence value, I prefer to just use the nextval function directly in my api. Another option is to use database triggers, but I'm on a mission to avoid those whenever possible. The next procedure represents such an api:
Using the SYS_GUID() function, a similar setup would look like this:
One object less (the sequence), and you don't need to bother about the ID column in the insert statement. The mentioned disadvantages of sequences per point:
Sequences have to be created, maintained and deployed
While obviously true, I don't think it is a major point. A sequence never comes alone, so we are already creating, maintaining and deploying database objects. But it adds up a little indeed.
Sequences need PL/SQL-code to be fetched (the ‘select into’ construction is obsolete in 11g, but defaulting a column with sequence.nextval still does not work)
You don't necessarily need PL/SQL to use a sequence, as shown above. So this is untrue. Although it is true that you cannot default a column with sequence.nextval.
When you introduce triggers to fetch the .nextval value, you introduce significant undesired performance penalties
So don't introduce database triggers to fetch the .nextval value :-)
When moving your data from data from one environment to another (e.g. copy PRODUCTION data to ACCEPTANCE), you have to synchronize the .LASTVAL of all sequences
This is true. Very minor disadvantage though, in my opinion. If we refresh the acceptance database, we don't only refresh the data, but also the objects itself.
Fetching sequences can be a performance-critical in big environment (bulk fetch in Data warehouse for instance) which has to be fixed by increasing the CACHE property.
The solution to the possible problem is already presented here.
No advantages of sequences over SYS_GUID are mentioned, so here are some of mine:
A RAW(16) column takes up more space than a NUMBER column.
The SYS_GUID is a RAW(16) taking up 16 bytes, whereas as an ID populated by a sequences typically is a NUMBER(6), NUMBER(9) or NUMBER(12). A reasonable average is NUMBER(9) which takes up only 4 bytes. When you have foreign key columns in your table, this effect is multiplied.
Ad-hoc querying has become more cumbersome.
Which one would you rather type. This:
You can no longer see the order of inserts by the ID column.
When using the cache property and multiple sessions, this is neither necessarily true for sequence populated ID columns, but there is a strong correlation between the number and the time it was populated. This "information" is not there with SYS_GUID() populated columns.
Performance seems to be slightly less when using SYS_GUID()
The results of my test varied a lot. But generally it favoured the sequence approach. Here is the output of one of the more representative tests:
So while I am certainly not saying no to SYS_GUID(), I thought there was a little more to the story than the all-positive points in the link.
If you know some more advantages or disadvantages from one over the other, please say so in a comment.
Choose sys_guid() instead of sequences for populating meaningless PK-columns.
Traditional sequences are used for populating an ID-column.
Sequences have several disadvantages:
• Sequences have to be created, maintained and deployed.
• Sequences need PL/SQL-code to be fetched (the ‘select into’ construction is obsolete in 11g, but defaulting a column with sequence.nextval still does not work).
• When you introduce triggers to fetch the .nextval value, you introduce significant undesired performance penalties
• When moving your data from data from one environment to another (e.g. copy PRODUCTION data to ACCEPTANCE), you have to synchronize the .LASTVAL of all sequences.
• Fetching sequences can be a performance-critical in big environment (bulk fetch in Data warehouse for instance) which has to be fixed by increasing the CACHE property
Use guid's to overcome the disadvantages of sequences:
create table employees
( guid raw(32) default sys_guid() not null primary key);
I knew about the SYS_GUID() function, but didn't realize you can use it as a default value for a primary key column. That sure is convenient. Where other RDBMS all have some sort of autonumber/identity column to automatically fill an ID column, Oracle uses sequences, which is a little more cumbersome to implement. However, I found the story not really balanced. Before addressing each point and mentioning some of the disadvantages of SYS_GUID, let's see how it works. Using sequences, a simple setup can be like this:
rwijk@ORA11GR1> create sequence seq cache 1000
2 /
Sequence created.
Here I create the sequence with "cache 1000", because I want a session to fetch 1000 numbers at once, instead of one each time. This reduces the overhead for Oracle to maintain the SYS.SEQ$ table. Gaps in the numbers might be large this way, but this shouldn't bother you. If it does bother you, you likely shouldn't be using a sequence, as they are never gap-free. A table is created without any reference to the sequence:
rwijk@ORA11GR1> create table t_seq
2 ( id number(9) primary key
3 , filler varchar2(1000)
4 )
5 /
Table created.
To fill the id column with the sequence value, I prefer to just use the nextval function directly in my api. Another option is to use database triggers, but I'm on a mission to avoid those whenever possible. The next procedure represents such an api:
rwijk@ORA11GR1> create procedure p_seq
2 is
3 begin
4 insert into t_seq (id,filler)
5 select seq.nextval
6 , lpad('*',1000,'*')
7 from dual
8 connect by level <= 20000
9 ;
10 end;
11 /
Procedure created.
Using the SYS_GUID() function, a similar setup would look like this:
rwijk@ORA11GR1> create table t_sysguid
2 ( id raw(16) default sys_guid() primary key
3 , filler varchar2(1000)
4 )
5 /
Table created.
rwijk@ORA11GR1> create procedure p_sysguid
2 is
3 begin
4 insert into t_sysguid (filler)
5 select lpad('*',1000,'*')
6 from dual
7 connect by level <= 20000
8 ;
9 end;
10 /
Procedure created.
One object less (the sequence), and you don't need to bother about the ID column in the insert statement. The mentioned disadvantages of sequences per point:
Sequences have to be created, maintained and deployed
While obviously true, I don't think it is a major point. A sequence never comes alone, so we are already creating, maintaining and deploying database objects. But it adds up a little indeed.
Sequences need PL/SQL-code to be fetched (the ‘select into’ construction is obsolete in 11g, but defaulting a column with sequence.nextval still does not work)
You don't necessarily need PL/SQL to use a sequence, as shown above. So this is untrue. Although it is true that you cannot default a column with sequence.nextval.
When you introduce triggers to fetch the .nextval value, you introduce significant undesired performance penalties
So don't introduce database triggers to fetch the .nextval value :-)
When moving your data from data from one environment to another (e.g. copy PRODUCTION data to ACCEPTANCE), you have to synchronize the .LASTVAL of all sequences
This is true. Very minor disadvantage though, in my opinion. If we refresh the acceptance database, we don't only refresh the data, but also the objects itself.
Fetching sequences can be a performance-critical in big environment (bulk fetch in Data warehouse for instance) which has to be fixed by increasing the CACHE property.
The solution to the possible problem is already presented here.
No advantages of sequences over SYS_GUID are mentioned, so here are some of mine:
A RAW(16) column takes up more space than a NUMBER column.
The SYS_GUID is a RAW(16) taking up 16 bytes, whereas as an ID populated by a sequences typically is a NUMBER(6), NUMBER(9) or NUMBER(12). A reasonable average is NUMBER(9) which takes up only 4 bytes. When you have foreign key columns in your table, this effect is multiplied.
Ad-hoc querying has become more cumbersome.
Which one would you rather type. This:
select id from t_sysguid where id = '48000DA3C76E41A789C7E8925A91E28A'Or this:
select id from t_seq where id = 1234
You can no longer see the order of inserts by the ID column.
When using the cache property and multiple sessions, this is neither necessarily true for sequence populated ID columns, but there is a strong correlation between the number and the time it was populated. This "information" is not there with SYS_GUID() populated columns.
Performance seems to be slightly less when using SYS_GUID()
The results of my test varied a lot. But generally it favoured the sequence approach. Here is the output of one of the more representative tests:
rwijk@ORA11GR1> begin
2 runstats_pkg.rs_start;
3 p_seq;
4 runstats_pkg.rs_middle;
5 p_sysguid;
6 runstats_pkg.rs_stop(1000);
7 end;
8 /
Run1 draaide in 1670 hsecs
Run2 draaide in 2220 hsecs
Run1 draaide in 75.23% van de tijd
Naam Run1 Run2 Verschil
STAT.recursive calls 1,162 49 -1,113
LATCH.enqueue hash chains 1,626 508 -1,118
STAT.hot buffers moved to head of LRU 1,213 75 -1,138
STAT.dirty buffers inspected 5,613 3,266 -2,347
STAT.free buffer requested 6,215 3,482 -2,733
STAT.free buffer inspected 6,678 3,577 -3,101
STAT.redo subscn max counts 4,147 753 -3,394
STAT.heap block compress 3,678 7,119 3,441
LATCH.cache buffer handles 4,922 0 -4,922
STAT.Heap Segment Array Inserts 12,512 7,156 -5,356
STAT.HSC Heap Segment Block Changes 12,582 7,156 -5,426
STAT.calls to get snapshot scn: kcmgss 6,752 152 -6,600
LATCH.checkpoint queue latch 17,879 9,818 -8,061
LATCH.cache buffers lru chain 27,793 13,845 -13,948
LATCH.object queue header operation 40,308 25,913 -14,395
STAT.consistent gets 26,810 7,302 -19,508
STAT.consistent gets from cache 26,810 7,302 -19,508
STAT.consistent gets from cache (fastpath) 19,687 145 -19,542
STAT.redo entries 42,552 71,492 28,940
STAT.db block gets from cache (fastpath) 21,080 57,202 36,122
STAT.session logical reads 98,920 144,900 45,980
LATCH.simulator hash latch 5,752 56,028 50,276
STAT.db block gets from cache 72,110 137,598 65,488
STAT.db block gets 72,110 137,598 65,488
STAT.db block changes 63,379 129,357 65,978
LATCH.sequence cache 150,100 0 -150,100
LATCH.cache buffers chains 396,737 572,383 175,646
STAT.undo change vector size 2,564,000 4,921,872 2,357,872
STAT.physical read total bytes 17,776,640 23,355,392 5,578,752
STAT.physical read bytes 17,776,640 23,355,392 5,578,752
STAT.cell physical IO interconnect bytes 17,776,640 23,355,392 5,578,752
STAT.physical IO disk bytes 17,776,640 23,355,392 5,578,752
STAT.redo size 59,361,376 66,097,504 6,736,128
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
679,069 707,672 28,603 95.96%
PL/SQL procedure successfully completed.
So while I am certainly not saying no to SYS_GUID(), I thought there was a little more to the story than the all-positive points in the link.
If you know some more advantages or disadvantages from one over the other, please say so in a comment.
Thursday, December 3, 2009
UKOUG 2009
UKOUG Conference Series, Technology & E-Business Suite 2009 is over. Three days were filled with highly technical presentations in several streams. It was my first time, and I hope it wasn't my last time.
The presentations I saw were:
On Wednesday I had my own presentation "All About Grouping". Unfortunately I was starting to get sick on Tuesday afternoon. My walk from the hotel to the International Convention Centre included passing a nice Christmas market. On Wednesday morning everyone on this market looked like they thought it was cold (a few degrees Celsius), but I was walking there sweating like crazy and with a running nose. I skipped a few sessions, ate only the fruit and salad from my lunch bag, hoping I would feel a little better when the presentation started. When I had started my presentation I noticed I forgot to plug in my presentation device. I decided to move on and just stay with the speakers stand. I started to sweat again heavily, and not (only) because of nerves. Half-way through my stomach became empty and started making some noise. Nice, with a microphone just above your stomach ... Previously, this presentation took 50 minutes. Because I had an hour, I decided to get one more example in. Still, I finished in 45 minutes this time, so I guess I should slow down some more. If anything, this was a great learning experience for me about presenting. Supposedly, the audience doesn't experience a presentation in the same way that the presenter does. I very much hope that's true.
The slightly adjusted presentation and demonstration scripts can be found here and here. A white paper about grouping will appear shortly on this blog.
The presentations I saw were:
- John Scott - Building Scalable Applications with Oracle Application Express
- Joel Goodman - Oracle Database Links part 2 - Distributed Transactions
- Jonathan Lewis - Writing Optimal SQL
- Randolf Geist - Understanding the different modes of System Statistics
- Wolfgang Breitling - Seeding Statistics
- Anthony Rayner - Building Rich Web Applications! Inside look into the Oracle APEX 4.0 Development Lab
- Graham Wood - What's new in the Oracle Database?
- Connor McDonald - 11g features for Developers
- Jonathan Lewis - Introducing Partitions
- Julian Dyke - Vital Statistics
- Andrew Clarke - PL/SQL API Design Workshop
- Randolf Geist - Everything you always wanted to know about FIRST_ROWS_N but were afraid to ask
On Wednesday I had my own presentation "All About Grouping". Unfortunately I was starting to get sick on Tuesday afternoon. My walk from the hotel to the International Convention Centre included passing a nice Christmas market. On Wednesday morning everyone on this market looked like they thought it was cold (a few degrees Celsius), but I was walking there sweating like crazy and with a running nose. I skipped a few sessions, ate only the fruit and salad from my lunch bag, hoping I would feel a little better when the presentation started. When I had started my presentation I noticed I forgot to plug in my presentation device. I decided to move on and just stay with the speakers stand. I started to sweat again heavily, and not (only) because of nerves. Half-way through my stomach became empty and started making some noise. Nice, with a microphone just above your stomach ... Previously, this presentation took 50 minutes. Because I had an hour, I decided to get one more example in. Still, I finished in 45 minutes this time, so I guess I should slow down some more. If anything, this was a great learning experience for me about presenting. Supposedly, the audience doesn't experience a presentation in the same way that the presenter does. I very much hope that's true.
The slightly adjusted presentation and demonstration scripts can be found here and here. A white paper about grouping will appear shortly on this blog.