This is my 13 post. So I’ve decided to make some digression. Usually number 13 is considered as unhappy. It seems not a rule for me. This year 13 is my happy number. On 13th January I’ve signed new job contract. It was just about 13:15 (or might be it was exactly at 13:13). What is more interesting, sum of digits in that date (13-01-2006) is equal to 13 (1+3+0+1+2+0+0+6=13). Number of building I’m now working in is also 13. As for now I’m quite happy in my new job.
Also according to Walter Schneider 13 is definitely happy number. I’ve observed that for me the unhappiest day in month is 26th. In fact it is 2*13. When I use Walters method to it gives me: 26 => 40 (2*2+6*6) => 16 => 37 => 58 => 89 => 145 => 42 => 20 => 4. So 26 is unhappy number and it confirms my observations :).
More on Happy Numbers in Wikipedia.
Cheers, Paweł
Saturday, September 30, 2006
Flashback Table and Materialized View – not working together
I must say that I like Flashback features very much. This is very useful option and should be used more often. But sometimes you might hit some obstacles. One of them is Materialized view. When You want to issue FLASHBACK TABLE statement on table that has materialized view it does not work.
Lets try:
Lets try:
SQL> create table a_fb 2 (id number primary key, text char(200)) enable row movement; Table created. SQL> create materialized view log on a_fb with rowid; Materialized view log created.Now insert some data:
SQL> insert into a_fb select object_id, object_name
2 from all_objects where rownum <= 100;
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
2 min(id), max(id), count(*)
3 from a_fb;
GET_SYSTEM_CHANGE_NUMBER MIN(ID) MAX(ID) COUNT(*)
------------------------ ---------- ---------- ----------
568668 258 1069 100
ok. Lets insert more data and then try to flashback to above SCN.
SQL> insert into a_fb select object_id, object_name
2 from all_objects where rownum <= 100
3 and object_id not in(select id from a_fb);
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
2 min(id), max(id), count(*)
3 from a_fb;
GET_SYSTEM_CHANGE_NUMBER MIN(ID) MAX(ID) COUNT(*)
------------------------ ---------- ---------- ----------
568704 258 1257 200
And now flashback:
SQL> flashback table a_fb to scn 568668; Flashback complete.It seems that Materialized view log is not a problem. Moreover when FLASHBACK TABLE is executed new rows are inserted to Materialized view log. You can check it issuing this select:
select count(*) from MLOG$_A_FB;Now lets try to create materialized view
SQL> create materialized view a_fb_mv
2 REFRESH FAST ON COMMIT with rowid
3 as
4 select id, text, rowid a_rowid from a_fb;
Materialized view created.
SQL> select sys.dbms_flashback.get_system_change_number,
2 min(id), max(id), count(*)
3 from a_fb;
GET_SYSTEM_CHANGE_NUMBER MIN(ID) MAX(ID) COUNT(*)
------------------------ ---------- ---------- ----------
568852 258 1069 100
Lets add some data and roll back to that SCN
SQL> insert into a_fb select object_id, object_name
2 from all_objects where rownum <= 100
3 and object_id not in(select id from a_fb);
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
2 min(id), max(id), count(*)
3 from a_fb;
GET_SYSTEM_CHANGE_NUMBER MIN(ID) MAX(ID) COUNT(*)
------------------------ ---------- ---------- ----------
568893 258 1257 200
Lets see what we have in Materialized view:
SQL> select sys.dbms_flashback.get_system_change_number,
2 min(id), max(id), count(*)
3 from a_fb_mv;
GET_SYSTEM_CHANGE_NUMBER MIN(ID) MAX(ID) COUNT(*)
------------------------ ---------- ---------- ----------
568895 258 1257 200
Problem
Try to flashback:SQL> flashback table a_fb to scn 568852;
flashback table a_fb to scn 568852
*
ERROR at line 1:
ORA-08194: Flashback Table operation is not allowed on materialized views
You cannot flashback table if there is materialized view defined on it. But materialized view log is not a problem. I must say that I do not see any reason for that. If materialized view log is generated during flashback operation why materialized view is not updated according to that log?
Workaround
This is quite simple. You just have to drop materialized view and issue flashback table statement:SQL> drop materialized view a_fb_mv;
Materialized view dropped.
SQL> flashback table a_fb to scn 568852;
Flashback complete.
SQL> select sys.dbms_flashback.get_system_change_number,
2 min(id), max(id), count(*)
3 from a_fb;
GET_SYSTEM_CHANGE_NUMBER MIN(ID) MAX(ID) COUNT(*)
------------------------ ---------- ---------- ----------
569009 258 1069 100
Now you need only to recreate dropped materialized view.
Related articles:
Thursday, September 21, 2006
ORA_ROWSCN continuation…
In my previous post I was wondering how SCN is assigned to ROW or Block. I couldn’t find explanation in Oracle Documentation. But I’ve found this document written by Jonathan Lewis. You should read this! I’m little afraid about 'best guess' technology.
By the way you can also read article about undocumented userenv('commitscn') written in May 1999. When I first saw it I thought it might be replaced by ORA_ROWSCN.
So I’ve try this:
If You have some spare time, I recommend to read this post: Is it crazy to feel guilty about success? and from Eddie Awad blog: SYS_OP_MAP_NONNULL, REVERSE, LNNVL, NVL, NVL2, COALESCE
Cheers, Paweł
By the way you can also read article about undocumented userenv('commitscn') written in May 1999. When I first saw it I thought it might be replaced by ORA_ROWSCN.
So I’ve try this:
SQL> create table t (n1 number, text varchar2(10)) ROWDEPENDENCIES;
Table created.
SQL> insert into t values (userenv('commitscn'), 'T1');
1 row created.
SQL> select ora_rowscn, n1 commitscn, text from t;
ORA_ROWSCN COMMITSCN TEXT
---------- ---------- ----------
4621851 T1
SQL> declare
2 pragma autonomous_transaction;
3 begin
4 insert into t values (userenv('commitscn'), 'TAuto');
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select ora_rowscn, n1 commitscn, text from t;
ORA_ROWSCN COMMITSCN TEXT
---------- ---------- ----------
4621851 T1
4621852 4621851 TAuto
SQL> commit;
Commit complete.
SQL> select ora_rowscn, n1 commitscn, text from t;
ORA_ROWSCN COMMITSCN TEXT
---------- ---------- ----------
4621854 4621853 T1
4621852 4621851 TAuto
So for sure ORA_ROWSCN and userenv('commitscn') is not the same. I must say that I’m even more confused.
If You have some spare time, I recommend to read this post: Is it crazy to feel guilty about success? and from Eddie Awad blog: SYS_OP_MAP_NONNULL, REVERSE, LNNVL, NVL, NVL2, COALESCE
Cheers, Paweł
Sunday, September 17, 2006
ORA_ROWSCN behavior: bug or feature?
ORA_ROWSCN is pseudo column that gives SCN (System Change Number) for transaction that was modifying record. Lets take a closer look. First create table:
Now lets take a look what will happen for table with ROWDEPENDENCIES on.
What about Index Organized Tables:
2. We can see that SCN is assigned at commit. Does commit tackles all blocks modified by transaction? For short transaction it is not a problem, as blocks probably are still in buffer. But large transactions that generate lot of redo, and there are few check points during transaction time should not change this blocks. That means that block should have only transaction id, and oracle translates this transaction id to SCN.
Cheers, Paweł
CREATE TABLE A_TABLE (ID NUMBER, TEXT CHAR(2000));Then feed table with some data:
SQL> declare 2 procedure auto_commit 3 is pragma autonomous_transaction; 4 begin commit; end; 5 begin 6 for i in 1..10 loop 7 insert into a_table select rownum, rownum from dual connect by level <=10; 8 auto_commit; 9 end loop; 10 end; 11 /Procedure auto_commit is used to force change of SCN. Take a look what we have in table:
SQL> select ora_rowscn, count(*) from a_table 2 group by ora_rowscn; ORA_ROWSCN COUNT(*) ---------- ---------- 4589308 24 4589313 16 4589293 15 4589297 24 4589302 21We ran INSERT statement 10 times and we get 5 different SCN’s in table. None of them has exactly 10 rows. This is because table was created with default setting: NOROWDEPENDENCIES, and SCN is kept on block level. Every and each row in block has the same SCN. Lets commit this transaction:
SQL> commit; Commit complete. SQL> select ora_rowscn, count(*) from a_table 2 group by ora_rowscn; ORA_ROWSCN COUNT(*) ---------- ---------- 4589363 100Now all rows haveexactly the same SCN.
Now lets take a look what will happen for table with ROWDEPENDENCIES on.
CREATE TABLE A_TABLE_ROW (ID NUMBER, TEXT CHAR(2000)) ROWDEPENDENCIES;Insert some data:
SQL> declare 2 procedure auto_commit 3 is pragma autonomous_transaction; 4 begin commit; end; 5 begin 6 for i in 1..10 loop 7 insert into A_TABLE_ROW select rownum, rownum from dual connect by level <=10; 8 auto_commit; 9 end loop; 10 end; 11 /Query table:
SQL> select ora_rowscn, count(*) from A_TABLE_ROW
2 group by ora_rowscn;
ORA_ROWSCN COUNT(*)
---------- ----------
100
Now ORA_ROWSCN is NULL for uncommitted transaction. This is very interesting.
SQL> commit; Commit complete. SQL> select ora_rowscn, count(*) from A_TABLE_ROW 2 group by ora_rowscn; ORA_ROWSCN COUNT(*) ---------- ---------- 4590773 100What will happen when updating:
SQL> update A_TABLE_ROW set id = id where rownum <20;
19 rows updated.
SQL> select ora_rowscn, count(*) from A_TABLE_ROW
2 group by ora_rowscn;
ORA_ROWSCN COUNT(*)
---------- ----------
19
4590773 81
We can conclude that data in active transaction have NULL ORA_ROWSCN for tables with ROWDEPENDENCY.
What about Index Organized Tables:
CREATE TABLE A_TABLE_IOT (ID NUMBER primary key, TEXT CHAR(2000)) organization index;Generate data:
SQL> declare
2 procedure auto_commit
3 is pragma autonomous_transaction;
4 begin commit; end;
5 begin
6 for i in 1..10 loop
7 insert into A_TABLE_IOT select i*10+rownum, rownum
from dual connect by level <=10;
8 auto_commit;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select ora_rowscn, count(*) from A_TABLE_IOT
2 group by ora_rowscn;
ORA_ROWSCN COUNT(*)
---------- ----------
100
For IOT with NOROWDEPENDENCIES we still get NULLs for ORA_ROWSCN.Questions:
1. Why we have such differences in implementation of ORA_ROWSCN?2. We can see that SCN is assigned at commit. Does commit tackles all blocks modified by transaction? For short transaction it is not a problem, as blocks probably are still in buffer. But large transactions that generate lot of redo, and there are few check points during transaction time should not change this blocks. That means that block should have only transaction id, and oracle translates this transaction id to SCN.
Cheers, Paweł
Thursday, September 14, 2006
Avoiding Deadlocks: SKIP LOCKED
After reading Tim's article on Deadlocks, I've recalled solution that can help avoid deadlocks in some situations.
Assume that in our application we have to choose few (say 5) documents from pool to assign to users for processing. Usually we can use just update statement like that:
Cheers, Paweł
Assume that in our application we have to choose few (say 5) documents from pool to assign to users for processing. Usually we can use just update statement like that:
UPDATE documents SET doc_state = 'ASSIGNED', doc_user = USER WHERE doc_state = 'TO PROCESS' AND rownum <= 5;But this can cause some waits in highly loaded environments where this statement is run often. To overcome that we can use SKIP LOCKED clause:
BEGIN
FOR r IN
(SELECT id FROM documents
WHERE doc_state = 'TO PROCESS'
AND rownum <= 5 FOR UPDATE SKIP LOCKED) LOOP
UPDATE documents
SET doc_state = 'ASSIGNED',
doc_user = USER
WHERE id = r.id;
END LOOP;
END;
In this statement we do not suffer from waits, and we get up to 5 rows assigned to current user.Cheers, Paweł
Wednesday, September 13, 2006
Binary Days
As addition to may previous post lets see other Binary days, where day number is power of 2.
BTW. Does anybody know if is it possible to turn off these automatic <br /> tags in beta blogger?
Paweł
SQL> select rownum-1 power_of2
2 , to_char(trunc(sysdate,'YYYY')-1+power(2,rownum-1),
3 'DD-MON-YYYY DAY') magic_day
4 from dual connect by level <=9;
POWER_OF2 MAGIC_DAY
---------- ---------------------------------------------------------
0 01-JAN-2006 SUNDAY
1 02-JAN-2006 MONDAY
2 04-JAN-2006 WEDNESDAY
3 08-JAN-2006 SUNDAY
4 16-JAN-2006 MONDAY
5 01-FEB-2006 WEDNESDAY
6 05-MAR-2006 SUNDAY
7 08-MAY-2006 MONDAY
8 13-SEP-2006 WEDNESDAY
9 rows selected.
Interesting coincidence - there are only 3 days of week: SUNDAY, MONDAY and WEDNESDAY.
BTW. Does anybody know if is it possible to turn off these automatic <br /> tags in beta blogger?
Paweł
Subscribe to:
Posts (Atom)
