forums.oracle.com has come back online once again. An upgrade attempt in June was rolled back after the upgraded version had been "in play" for a few days. After the rollback, some posts were lost and some identities were lost.
Two weekends ago, forums.oracle.com was offline for maintenance. This Saturday (for most of the world that lives outside USA) it was offline again.
Now that it is back,
a. font size cannot be adjusted in MSIE. I might as well stop using forums.oracle.com
b. The new Legends of "Guru", "Expert", "Pro", "Journeyman" and "Newbie" are so so .... so ... what shall I say ? We have already suffered "Oracle ACE"s and "Oracle ACE Director"s. And now I suffer being labelled a "Newbie" until some unknowns start awarding me 5 points for "Helpful" answers and 10 points for "Correct" answers so that I must accumulate points to becoming a "Journeyman" and then a "Pro".
It might well be very very very soon time to say goodbye to forums.oracle.com and return to, say, google groups ?
Update : This thread is active on forums.oracle.com (so I am still logging in there yet !).
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
23 August, 2008
21 August, 2008
Testing Bug 4260477 Fix for Bug 4224840
Having posted about MetaLink Note#465226.1 on forums, earlier today, I have created a test case to simulate the same situation.
The base bug 4224840 has been logged by a customer that possibly found block corruption after running a transaction that locked more than 4,095 records in a block.
Considering that a table must have at least 1 column and that every data block loses some space to block header and PCTFREE, it is unlikely to have 4,095 rows in a 16KB data block. However, a 32KB data block can hold more than 4,095 rows.
Here is my Test Case Simulation on 10.2.0.1 64bit on Linux (you'd need 64bit for 32KB datablocks) :
[Note : I don't really create 4,095 records in the normal sense. I INSERT and DELETE that many times, but since I do not issue ROLLBACK or COMMIT, Oracle "preserves" each INSERT and DELETE ! ]
The 4260477 "fix" is to return an error message (8007 : "Further changes ....") before block corruption occurs. Neat isn't it ?
Of course, that means that the transaction fails !
The final fix is in 11g.
The base bug 4224840 has been logged by a customer that possibly found block corruption after running a transaction that locked more than 4,095 records in a block.
Considering that a table must have at least 1 column and that every data block loses some space to block header and PCTFREE, it is unlikely to have 4,095 rows in a 16KB data block. However, a 32KB data block can hold more than 4,095 rows.
Here is my Test Case Simulation on 10.2.0.1 64bit on Linux (you'd need 64bit for 32KB datablocks) :
[Note : I don't really create 4,095 records in the normal sense. I INSERT and DELETE that many times, but since I do not issue ROLLBACK or COMMIT, Oracle "preserves" each INSERT and DELETE ! ]
SQL>
SQL> REM Bug 4260477 is a fix for
SQL> REM 4224840 whereby Block Corruption (or error with db_block_checking) would occur with >4095 locks in a block
SQL> REM The fix in 4260477 is to return an error message (ORA-8007) to prevent the corruption
SQL>
SQL> REM This test simulates the issue
SQL>
SQL> drop tablespace TBS_32K including contents and datafiles;
Tablespace dropped.
SQL>
SQL> create tablespace TBS_32K datafile '/oracle_fs/Databases/ORT21FS/tbs_32K_01.dbf' size 10M autoextend on next 10M maxsize 2000M blocksize 32K
2 extent management local autoallocate segment space management auto ;
Tablespace created.
SQL>
SQL> show parameter db_block_checking
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string FALSE
SQL>
SQL> drop table Test_Bug_4260477 ;
drop table Test_Bug_4260477
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table Test_Bug_4260477 (col_1 varchar2(5)) pctfree 1 tablespace tbs_32K;
Table created.
SQL>
SQL> variable i number;
SQL>
SQL> set serveroutput on size 100000
SQL> begin
2 for i in 1 .. 5000
3 loop
4 insert into Test_Bug_4260477 values (to_char(i));
5 delete from Test_Bug_4260477 ;
6 if ((i-4080) > 0) then
7 dbms_output.put_line('Row ' i );
8 end if;
9 end loop;
10 end ;
11 /
Row 4081
Row 4082
Row 4083
Row 4084
Row 4085
Row 4086
Row 4087
Row 4088
Row 4089
Row 4090
Row 4091
Row 4092
Row 4093
Row 4094
begin
*
ERROR at line 1:
ORA-08007: Further changes to this block by this transaction not allowed
ORA-06512: at line 5
SQL>
The 4260477 "fix" is to return an error message (8007 : "Further changes ....") before block corruption occurs. Neat isn't it ?
Of course, that means that the transaction fails !
The final fix is in 11g.
20 August, 2008
ASSM or MSSM ? -- The impact on INSERTS
Segment Space Management 'AUTO' was introduced in 9i. For a few years, I thought that it was a good / useful feature. However, I've encountered issues with space leakage in LOBs, issues in data not being clustered (as it would have been in MSSM) and seen various bugs logged against ASSM.
I guess that ASSM makes sense only in a really very high concurrency environment -- where multiple sessions need to manipulate the FreeList for the same segment (table) concurrently. Some new features (eg ALTER TABLE SHRINK) require ASSM. Going forward with ASSM being the "default" default in 10g and 11g, I guess that it is Oracle's direction.
However, for those of us who do need to get performance out of our systems, ASSM can be a drag.
i. It seems to increase current gets when doing INSERTs
ii. Data isn't clustered into contigous blocks as it is inserted. This is important for index range scans on an indexed column that is incremented for each insert (eg a DATE or SEQUENCE). The Clustering Factor goes "bad"
Here are some simple tests that I ran, running the same INSERT (and CREATE INDEX) statements on two copies of a "target" table, one in MSSM and the other in ASSM.
Running this in one session :
and running this in another session :
Here's a comparison of the results :
For the first INSERT statement :
The number of blocks (and rows) read from the SOURCE_TABLE is exactly the same (4,193 ,with no physical reads as the table had been cached by previous queries).
Yet, the INSERT measured up as :
That means that the Insert into an ASSM Tablespace causes more Current Gets and CPU usage.
These are the results of the second INSERT statement :
Once again, we see a siginficant difference in Current Gets.
Next is the CREATE INDEX statement :
Again, the ASSM performed poorer.
The final test is the Insert with the Index present.
Again, the ASSM Insert performs poorer.
These are the final sizes :
I guess that ASSM makes sense only in a really very high concurrency environment -- where multiple sessions need to manipulate the FreeList for the same segment (table) concurrently. Some new features (eg ALTER TABLE SHRINK) require ASSM. Going forward with ASSM being the "default" default in 10g and 11g, I guess that it is Oracle's direction.
However, for those of us who do need to get performance out of our systems, ASSM can be a drag.
i. It seems to increase current gets when doing INSERTs
ii. Data isn't clustered into contigous blocks as it is inserted. This is important for index range scans on an indexed column that is incremented for each insert (eg a DATE or SEQUENCE). The Clustering Factor goes "bad"
Here are some simple tests that I ran, running the same INSERT (and CREATE INDEX) statements on two copies of a "target" table, one in MSSM and the other in ASSM.
Running this in one session :
set echo on
set timing on
spool run_insert_tests_mssm
create tablespace mssm_tbs
datafile '/oracle_fs/Databases/ORT24FS/mssm01.dbf' size 1000M
extent management local autoallocate segment space management manual;
undefine sid
col mysid new_value sid
select distinct sid mysid from V$mystat ;
alter session set tracefile_identifier='run_insert_tests_mssm';
alter session set events '10046 trace name context forever, level 8';
alter table source_table cache;
select /*+ FULL (s) */ count(*) from source_table s;
create table target_table_mssm tablespace mssm_tbs as select * from source_table where 1=2;
insert /* first_mssm_insert */ into target_table_mssm select * from source_table;
commit;
insert /* second_mssm_insert */ into target_table_mssm select * from source_table;
commit;
create index target_table_mssm_ndx on target_table_mssm(object_id) tablespace mssm_tbs;
insert /* indexed_mssm_insert */ into target_table_mssm select * from source_table;
commit;
select segment_name, segment_type, blocks from user_segments where segment_name like '%_MSSM%' order by segment_name ;
and running this in another session :
set echo on
set timing on
spool run_insert_tests_assm
create tablespace assm_tbs
datafile '/oracle_fs/Databases/ORT24FS/assm01.dbf' size 1000M
extent management local autoallocate segment space management auto;
undefine sid
col mysid new_value sid
select distinct sid mysid from V$mystat ;
alter session set tracefile_identifier='run_insert_tests_assm';
alter session set events '10046 trace name context forever, level 8';
alter table source_table cache;
select /*+ FULL (s) */ count(*) from source_table s;
create table target_table_assm tablespace assm_tbs as select * from source_table where 1=2;
insert /* first_assm_insert */ into target_table_assm select * from source_table;
commit;
insert /* second_assm_insert */ into target_table_assm select * from source_table;
commit;
create index target_table_assm_ndx on target_table_assm(object_id) tablespace assm_tbs;
insert /* indexed_assm_insert */ into target_table_assm select * from source_table;
commit;
select segment_name, segment_type, blocks from user_segments where segment_name like '%_ASSM%' order by segment_name;
Here's a comparison of the results :
For the first INSERT statement :
insert /* first_mssm_insert */ into target_table_mssm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 1 1.32 2.98 0 8771 23618 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.33 3.01 0 8771 23618 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=607550 us)
insert /* first_assm_insert */ into target_table_assm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.04 0 0 0 0
Execute 1 2.39 2.85 0 11917 38462 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.42 2.90 0 11917 38462 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911236 us)
The number of blocks (and rows) read from the SOURCE_TABLE is exactly the same (4,193 ,with no physical reads as the table had been cached by previous queries).
Yet, the INSERT measured up as :
MSSM ASSM
Consistent Gets 8,771 11,917
Current Gets 23,618 38,462
CPU Used (seconds) 1.32 2.39
That means that the Insert into an ASSM Tablespace causes more Current Gets and CPU usage.
These are the results of the second INSERT statement :
insert /* second_mssm_insert */ into target_table_mssm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.58 2.70 0 8758 23393 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.58 2.70 0 8758 23393 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911108 us)
insert /* second_assm_insert */ into target_table_assm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.33 3.19 0 12254 37667 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.34 3.19 0 12254 37667 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=607515 us)
Once again, we see a siginficant difference in Current Gets.
MSSM ASSM
Consistent Gets 8,758 12,254
Current Gets 23,393 37,667
CPU Used (seconds) 1.58 1.33
Next is the CREATE INDEX statement :
create index target_table_mssm_ndx on target_table_mssm(object_id) tablespace
mssm_tbs
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 1 0 0
Execute 1 1.62 2.11 0 9357 1688 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.63 2.18 0 9358 1688 0
create index target_table_assm_ndx on target_table_assm(object_id) tablespace
assm_tbs
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 9 0 0
Execute 1 1.83 2.20 0 9535 2188 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.84 2.21 0 9544 2188 0
Again, the ASSM performed poorer.
The final test is the Insert with the Index present.
insert /* indexed_mssm_insert */ into target_table_mssm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.12 4.26 1342 16682 119228 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.12 4.27 1342 16682 119228 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=607454 us)
insert /* indexed_assm_insert */ into target_table_assm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.70 5.60 1342 17923 135324 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.70 5.60 1342 17923 135324 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911107 us)
Again, the ASSM Insert performs poorer.
MSSM ASSM
Consistent Gets 16,682 17,923
Current Gets 119,228 135,324
CPU Used (seconds) 3.12 3.70
These are the final sizes :
SQL> select segment_name, segment_type, blocks from user_segments where segment_name like '%_MSSM%' order by segment_name ;
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------------ ------------------ ----------
TARGET_TABLE_MSSM TABLE 13312
TARGET_TABLE_MSSM_NDX INDEX 2688
SQL> select segment_name, segment_type, blocks from user_segments where segment_name like '%_ASSM%' order by segment_name;
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------------ ------------------ ----------
TARGET_TABLE_ASSM TABLE 13312
TARGET_TABLE_ASSM_NDX INDEX 2816
Subscribe to:
Posts (Atom)