A Reminder : The AIOUG Sangam '11 Conference 09-10 December, Bengaluru (Bangalore)
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
24 October, 2011
20 October, 2011
The impact of ASSM on Clustering of data -- 2
Revisiting my earlier test on the same topic, I add a change to "slow" down the operations. The fetching of sequence values with the default cache was so fast in the earlier test, that the three sessions that I manually invoked to execute concurrently did not show a real distribution of data. The first session had retrieved very many sequence values and inserted rows before the second session had even started ... and the third session was "far behind" -- this was because my cycling through the 3 terminal windows and hitting the ENTER button to start the executions of the procedures was not fast enough.
Notice how the number of blocks for the table and for each inserting process is generally the same for the ASSM table and the MSSM table. But the CLUSTERING_FACTOR is now significantly higher in the ASSM table.
In MSSM, each new row (higher sequence value) almost always went in the same block as the previous row, except when the block was full. Thus a table with 15 blocks has a CLUSTERING_FACTOR of only 17. These rows are very well clustered.
In ASSM, on the other hand, the CLUSTERING_FACTOR is 302 ! Much higher spread across blocks. Given a row for INVOICE_ID (i.e. the sequence-based value) 'n', the likelihood of the row for INVOICE_ID 'n+1' being in the same block is much lower in ASSM than in MSSM. An Index Range Scan will be more "expensive" in the ASSM Table.
.
.
.
So, in this "twist" I deliberately set the sequences to NOCACHE, thus causing each INSERT to be much slower. Now, there is a much higher degree of concurrency of inserts.
Remember : This experiment was to demonstrate how ASSM "distributes" rows across different blocks. 3 different sessions concurrently inserting in MSSM will be hitting a single free list but will likely be inserting across different blocks in ASSM.
So, here is the test :
SQL> connect / as sysdba
Connected.
SQL>
SQL>
SQL> -- demo behaviour of ASSM with multiple sessions inserting concurrently
SQL> -- data gets "distributed" to reduce block-contention
SQL> -- but this affects the Clustering Factor !
SQL>
SQL>
SQL> drop tablespace ASSM_TBS including contents and datafiles;
Tablespace dropped.
SQL> create tablespace ASSM_TBS extent management local autoallocate segment space management auto;
Tablespace created.
SQL> drop tablespace MSSM_TBS including contents and datafiles;
Tablespace dropped.
SQL> create tablespace MSSM_TBS extent management local autoallocate segment space management manual;
Tablespace created.
SQL>
SQL>
SQL> -- grants required for stored procedures to access v$process, v$session, v$mystat
SQL> grant select on v_$process to hemant;
Grant succeeded.
SQL> grant select on v_$session to hemant;
Grant succeeded.
SQL> grant select on v_$mystat to hemant;
Grant succeeded.
SQL>
SQL>
SQL> -- Reconnect as HEMANT
SQL> connect hemant/hemant
Connected.
SQL>
SQL> -- setup the Invoices table and PK index and sequence
SQL> drop table ASSM_Table;
drop table ASSM_Table
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table ASSM_Table (
2 invoice_id number not null,
3 invoice_date date,
4 invoice_details varchar2(50),
5 inserting_pid number)
6 tablespace ASSM_TBS
7 /
Table created.
SQL> create unique index ASSM_Table_PK on ASSM_Table(invoice_id) tablespace ASSM_TBS;
Index created.
SQL> alter table ASSM_Table add constraint ASSM_Table_PK primary key (invoice_id);
Table altered.
SQL> drop sequence ASSM_invoice_Seq ;
Sequence dropped.
SQL> create sequence ASSM_invoice_Seq start with 1 increment by 1 nocache ;
Sequence created.
SQL>
SQL>
SQL> drop table MSSM_Table;
drop table MSSM_Table
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table MSSM_Table (
2 invoice_id number not null,
3 invoice_date date,
4 invoice_details varchar2(50),
5 inserting_pid number)
6 tablespace MSSM_TBS
7 /
Table created.
SQL> create unique index MSSM_Table_PK on MSSM_Table(invoice_id) tablespace MSSM_TBS;
Index created.
SQL> alter table MSSM_Table add constraint MSSM_Table_PK primary key (invoice_id);
Table altered.
SQL> drop sequence MSSM_invoice_Seq ;
Sequence dropped.
SQL> create sequence MSSM_invoice_Seq start with 1 increment by 1 nocache;
Sequence created.
SQL>
SQL>
SQL> -- create procedures that will simulate an application server
SQL> -- or ETL engine that inserts 500 rows
SQL> create or replace procedure populate_ASSM_Table
2 as
3 i number;
4 begin
5 for i in 1..501
6 loop
7 insert into ASSM_Table
8 select ASSM_invoice_seq.nextval, sysdate+rownum/10000, rpad('A',40,'X'), pid
9 from
10 v$process p, v$session s
11 where p.addr=s.paddr
12 and s.sid=(select sid from v$mystat where rownum=1)
13 ;
14 end loop;
15 commit;
16 end;
17 /
Procedure created.
SQL>
SQL> create or replace procedure populate_MSSM_Table
2 as
3 i number;
4 begin
5 for i in 1..501
6 loop
7 insert into MSSM_Table
8 select MSSM_invoice_seq.nextval, sysdate+rownum/10000, rpad('A',40,'X'), pid
9 from
10 v$process p, v$session s
11 where p.addr=s.paddr
12 and s.sid=(select sid from v$mystat where rownum=1)
13 ;
14 end loop;
15 commit;
16 end;
17 /
Procedure created.
SQL>
SQL>
SQL> REM now run the procedures from three separate sessions
SQL> pause .............. wait for the procedures to be executed
.............. wait for the procedures to be executed
SQL>
SQL>
SQL> -- gather statistics on the tables and indexes
SQL> exec dbms_Stats.gather_table_stats('','ASSM_TABLE',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_Stats.gather_table_stats('','MSSM_TABLE',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> -- view statistics on the tables and indexes
SQL> select table_name, num_rows, blocks
2 from user_tables
3 where table_name in ('ASSM_TABLE','MSSM_TABLE')
4 order by 1
5 /
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
ASSM_TABLE 1503 13
MSSM_TABLE 1503 15
SQL>
SQL> select index_name, num_rows, blevel, leaf_blocks, clustering_factor
2 from user_indexes
3 where index_name in ('ASSM_TABLE_PK','MSSM_TABLE_PK')
4 order by 1
5 /
INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- -----------------
ASSM_TABLE_PK 1503 1 4 302
MSSM_TABLE_PK 1503 1 3 17
SQL>
SQL>
SQL> -- view the number of distinct blocks
SQL> select inserting_pid,count(distinct(dbms_rowid.rowid_block_number(rowid)))
2 from ASSM_TABLE
3 group by inserting_pid
4 order by 1
5 /
INSERTING_PID COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
------------- -----------------------------------------------------
23 6
29 7
30 5
SQL>
SQL> select inserting_pid,count(distinct(dbms_rowid.rowid_block_number(rowid)))
2 from MSSM_TABLE
3 group by inserting_pid
4 order by 1
5 /
INSERTING_PID COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
------------- -----------------------------------------------------
23 7
29 6
30 8
SQL>
Notice how the number of blocks for the table and for each inserting process is generally the same for the ASSM table and the MSSM table. But the CLUSTERING_FACTOR is now significantly higher in the ASSM table.
In MSSM, each new row (higher sequence value) almost always went in the same block as the previous row, except when the block was full. Thus a table with 15 blocks has a CLUSTERING_FACTOR of only 17. These rows are very well clustered.
In ASSM, on the other hand, the CLUSTERING_FACTOR is 302 ! Much higher spread across blocks. Given a row for INVOICE_ID (i.e. the sequence-based value) 'n', the likelihood of the row for INVOICE_ID 'n+1' being in the same block is much lower in ASSM than in MSSM. An Index Range Scan will be more "expensive" in the ASSM Table.
.
.
.
17 October, 2011
DBMS_REDEFINITION to redefine a Partition -- and the impact of deferred_segment_creation
During a forums discussion on START_REDEF, I created test cases in 10.2 and 11.2 to test DBMS_REDEFINITION to redefine a Partition (instead of using ALTER TABLE .... EXCHANGE PARTITION).
ANSWER : See MyOracleSupport Documents :
"Bug 8891929 - ORA-42012 / ORA-4020 during DBMS_REDEFINITION (Doc ID 8891929.8)"
and
"Bug 11775474 - DBMS_REDEFINITION does not use deferred segment creation in 11.2.0.2 (Doc ID 11775474.8)"
If there is a Primary Key, normally, I should use options_flag => DBMS_REDEFINITION.CONS_USE_PK However, if I use options_flag => DBMS_REDEFINITION.CONS_USE_ROWID, Oracle attempts to create another Unique Index.
Here's the peculiarity. If I do not have any row present in the partition (so it is a segmentless partition), the FINISH_REDEF fails with an Object deadlock :
(see ANSWER at the end of this post)
Note the usage of CONS_USE_ROWID.
However, if the partition does have a row present, i.e it does have a segment created, then the FINISH_REDEF can succeed.
Well... the next thing is to test without deferred_segment_creation. This should succeed.
So, in 11.2, with deferred segments, CONS_USE_ROWID results in an object deadlock for an empty partition. What if I use CONS_USE_PK ?
Then, I retry disabling deferred_segment_creation, so as to force a segment to be created, and use CONS_USE_PK :
This succeeds as expected.
Conclusion : If a partition that is to be "exchanged" via DBMS_REDEFINITION is empty and deferred_segment_creation is enabled in 11.2, an ORA-4020 Object Deadlock error occurs when attempting to FINISH_REDEF.
(see ANSWER at the end of this post)
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter deferred;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL>
SQL> drop materialized view int_salestable;
drop materialized view int_salestable
*
ERROR at line 1:
ORA-12003: materialized view "HEMANT"."INT_SALESTABLE" does not exist
SQL>
SQL> drop TABLE salestable;
Table dropped.
SQL>
SQL> CREATE TABLE salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 TABLESPACE users
8 PARTITION BY RANGE(s_saledate)
9 (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10 PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11 PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12 PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
Table created.
SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;
Table altered.
SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;
Index created.
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL>
SQL> drop TABLE int_salestable ;
Table dropped.
SQL>
SQL> CREATE TABLE int_salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 tablespace example;
Table created.
SQL>
SQL> alter table int_salestable modify s_saleid number primary key;
Table altered.
SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2 (s_saledate, s_saleid, s_custid)
3 logging
4 TABLESPACE example;
Index created.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE(
3 uname => 'HEMANT',
4 tname => 'SALESTABLE',
5 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6 part_name => 'SAL03Q1');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 col_mapping => NULL,
7 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8 part_name => 'SAL03Q1');
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 part_name => 'SAL03Q1');
7 END;
8 /
BEGIN
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object HEMANT.INT_SALESTABLE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 78
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1680
ORA-06512: at line 2
SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
Note the usage of CONS_USE_ROWID.
However, if the partition does have a row present, i.e it does have a segment created, then the FINISH_REDEF can succeed.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter deferred;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL>
SQL> drop materialized view int_salestable;
Materialized view dropped.
SQL>
SQL> drop TABLE salestable;
Table dropped.
SQL>
SQL> CREATE TABLE salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 TABLESPACE users
8 PARTITION BY RANGE(s_saledate)
9 (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10 PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11 PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12 PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
Table created.
SQL>
SQL> insert into salestable partition (sal03q1) values (101,to_date('01-JAN-2003','DD-MON-YYYY'),1,2000);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> alter table salestable modify s_saleid number primary key;
Table altered.
SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;
Index created.
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL>
SQL> drop TABLE int_salestable ;
Table dropped.
SQL>
SQL> CREATE TABLE int_salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 tablespace example;
Table created.
SQL>
SQL> alter table int_salestable modify s_saleid number primary key;
Table altered.
SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2 (s_saledate, s_saleid, s_custid)
3 logging
4 TABLESPACE example;
Index created.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE(
3 uname => 'HEMANT',
4 tname => 'SALESTABLE',
5 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6 part_name => 'SAL03Q1');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 col_mapping => NULL,
7 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8 part_name => 'SAL03Q1');
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 part_name => 'SAL03Q1');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 EXAMPLE
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 EXAMPLE
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
Well... the next thing is to test without deferred_segment_creation. This should succeed.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter deferred;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> alter session set deferred_segment_creation=FALSE;
Session altered.
SQL>
SQL> drop materialized view int_salestable;
drop materialized view int_salestable
*
ERROR at line 1:
ORA-12003: materialized view "HEMANT"."INT_SALESTABLE" does not exist
SQL>
SQL> drop TABLE salestable;
Table dropped.
SQL>
SQL> CREATE TABLE salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 TABLESPACE users
8 PARTITION BY RANGE(s_saledate)
9 (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10 PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11 PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12 PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
Table created.
SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;
Table altered.
SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;
Index created.
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL>
SQL> drop TABLE int_salestable ;
Table dropped.
SQL>
SQL> CREATE TABLE int_salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 tablespace example;
Table created.
SQL>
SQL> alter table int_salestable modify s_saleid number primary key;
Table altered.
SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2 (s_saledate, s_saleid, s_custid)
3 logging
4 TABLESPACE example;
Index created.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE(
3 uname => 'HEMANT',
4 tname => 'SALESTABLE',
5 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6 part_name => 'SAL03Q1');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 col_mapping => NULL,
7 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8 part_name => 'SAL03Q1');
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 part_name => 'SAL03Q1');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 EXAMPLE
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 EXAMPLE
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
So, in 11.2, with deferred segments, CONS_USE_ROWID results in an object deadlock for an empty partition. What if I use CONS_USE_PK ?
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter deferred;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL>
SQL> drop materialized view int_salestable;
Materialized view dropped.
SQL>
SQL> drop TABLE salestable;
Table dropped.
SQL>
SQL> CREATE TABLE salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 TABLESPACE users
8 PARTITION BY RANGE(s_saledate)
9 (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10 PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11 PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12 PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
Table created.
SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;
Table altered.
SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;
Index created.
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL>
SQL> drop TABLE int_salestable ;
Table dropped.
SQL>
SQL> CREATE TABLE int_salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 tablespace example;
Table created.
SQL>
SQL> alter table int_salestable modify s_saleid number primary key;
Table altered.
SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2 (s_saledate, s_saleid, s_custid)
3 logging
4 TABLESPACE example;
Index created.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE(
3 uname => 'HEMANT',
4 tname => 'SALESTABLE',
5 options_flag => DBMS_REDEFINITION.CONS_USE_PK,
6 part_name => 'SAL03Q1');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 col_mapping => NULL,
7 options_flag => DBMS_REDEFINITION.CONS_USE_PK,
8 part_name => 'SAL03Q1');
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 part_name => 'SAL03Q1');
7 END;
8 /
BEGIN
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object HEMANT.INT_SALESTABLE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 78
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1680
ORA-06512: at line 2
SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
Then, I retry disabling deferred_segment_creation, so as to force a segment to be created, and use CONS_USE_PK :
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter deferred;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> alter session set deferred_segment_creation=FALSE;
Session altered.
SQL>
SQL> drop materialized view int_salestable;
Materialized view dropped.
SQL>
SQL> drop TABLE salestable;
Table dropped.
SQL>
SQL> CREATE TABLE salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 TABLESPACE users
8 PARTITION BY RANGE(s_saledate)
9 (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10 PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11 PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12 PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
Table created.
SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;
Table altered.
SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;
Index created.
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL>
SQL> drop TABLE int_salestable ;
Table dropped.
SQL>
SQL> CREATE TABLE int_salestable
2 (s_saleid NUMBER,
3 s_saledate DATE,
4 s_custid NUMBER,
5 s_totalprice NUMBER)
6 logging
7 tablespace example;
Table created.
SQL>
SQL> alter table int_salestable modify s_saleid number primary key;
Table altered.
SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2 (s_saledate, s_saleid, s_custid)
3 logging
4 TABLESPACE example;
Index created.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE(
3 uname => 'HEMANT',
4 tname => 'SALESTABLE',
5 options_flag => DBMS_REDEFINITION.CONS_USE_PK,
6 part_name => 'SAL03Q1');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 col_mapping => NULL,
7 options_flag => DBMS_REDEFINITION.CONS_USE_PK,
8 part_name => 'SAL03Q1');
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'HEMANT',
4 orig_table => 'SALESTABLE',
5 int_table => 'INT_SALESTABLE',
6 part_name => 'SAL03Q1');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'SALESTABLE' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 EXAMPLE
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2 where index_name = 'SALES_INDEX' order by partition_name
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 EXAMPLE
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
SQL>
This succeeds as expected.
Conclusion : If a partition that is to be "exchanged" via DBMS_REDEFINITION is empty and deferred_segment_creation is enabled in 11.2, an ORA-4020 Object Deadlock error occurs when attempting to FINISH_REDEF.
Question : Is this a valid conclusion ?
ANSWER : See MyOracleSupport Documents :
"Bug 8891929 - ORA-42012 / ORA-4020 during DBMS_REDEFINITION (Doc ID 8891929.8)"
and
"Bug 11775474 - DBMS_REDEFINITION does not use deferred segment creation in 11.2.0.2 (Doc ID 11775474.8)"
.
.
.
08 October, 2011
The impact of ASSM on Clustering of data
Automatic Segment Space Management ("ASSM") is designed to reduce block-level contention by multiple sessions concurrently attempting INSERTs by using a bitmap to identify blocks that are on the FreeList. The FreeList is accessed by sessions attempting to insert a row in a table when they need to identify "candidate" blocks. In Manual Segment Space Management ("MSSM"), it is the block-level storage parameter PCTUSED that is a determinant for when a block "enters" and "exits" the freelist. (Note : PCTFREE plays the same role in in MSSM and ASSM -- determining the "reserved" free space in a block that may be used for UPDATEs to rows in the block.
I start with two tablespaces : ASSM and MSSM and the target tables and indexes :
I then create two procedures that I will use to simulate an application server / ETL engine that inserts 50,000 rows but uses the Sequence to generate Primary Key.
I then wait for the procedures to be executed by 3 sessions to simulate 3 concurrent sessions inserting 50,000 rows each :
Note : The two procedures are executed from 3 sessions (i.e. the three sessions first execute Populate_ASSM_Table, followed by an execution of Populate_MSSM_Table). The invocations of the procedures are almost concurrent as I execute them from SQL sessions in 3 terminal windows.
Once the executions are completed, I gather and review statistics for the two tables : the one in the ASSM Tablespace and the other in the MSSM Tablespace.
Since I have run GATHER_TABLE_STATS, cascade=>TRUE, the CLUSTERING_FACTOR truly represents the distribution of the rows. Thus, after reading the row for INVOICE_ID 'n', the likelihood of INVOICE_ID 'n+1' being in the same block is much better in the MSSM_Table than in the ASSM_Table.
Now, here's a quiz question. Note the count of distinct blocks for each inserting Process. Why is the count of distinct blocks actually much higher for the MSSM Table ?
(Note : Each tablespace is only 1 datafile, so we need not consider FILE_ID when evaluating the BLOCK_NUMBERs).
.
.
Here is demonstration of the impact of ASSM on the clustering of data. I use a Unique Index based on a Sequence to ensure that every new row has an incremented value.
SQL>
SQL> -- demo behaviour of ASSM with multiple sessions inserting concurrently
SQL> -- data gets "distributed" to reduce block-contention
SQL> -- but this affects the Clustering Factor !
SQL>
SQL>
I start with two tablespaces : ASSM and MSSM and the target tables and indexes :
SQL> drop tablespace ASSM_TBS including contents and datafiles;
drop tablespace ASSM_TBS including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'ASSM_TBS' does not exist
SQL> create tablespace ASSM_TBS extent management local autoallocate segment space management auto;
Tablespace created.
SQL> drop tablespace MSSM_TBS including contents and datafiles;
drop tablespace MSSM_TBS including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'MSSM_TBS' does not exist
SQL> create tablespace MSSM_TBS extent management local autoallocate segment space management manual;
Tablespace created.
SQL>
SQL>
SQL> -- grants required for stored procedures to access v$process, v$session, v$mystat
SQL> grant select on v_$process to hemant;
Grant succeeded.
SQL> grant select on v_$session to hemant;
Grant succeeded.
SQL> grant select on v_$mystat to hemant;
Grant succeeded.
SQL>
SQL>
SQL> -- Reconnect as HEMANT
SQL> connect hemant/hemant
Connected.
SQL>
SQL> -- setup the Invoices table and PK index and sequence
SQL> drop table ASSM_Table;
drop table ASSM_Table
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table ASSM_Table (
2 invoice_id number not null,
3 invoice_date date,
4 invoice_details varchar2(50),
5 inserting_pid number)
6 tablespace ASSM_TBS
7 /
Table created.
SQL> create unique index ASSM_Table_PK on ASSM_Table(invoice_id) tablespace ASSM_TBS;
Index created.
SQL> alter table ASSM_Table add constraint ASSM_Table_PK primary key (invoice_id);
Table altered.
SQL> drop sequence ASSM_invoice_Seq ;
drop sequence ASSM_invoice_Seq
*
ERROR at line 1:
ORA-02289: sequence does not exist
SQL> create sequence ASSM_invoice_Seq start with 1 increment by 1;
Sequence created.
SQL>
SQL>
SQL> drop table MSSM_Table;
drop table MSSM_Table
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table MSSM_Table (
2 invoice_id number not null,
3 invoice_date date,
4 invoice_details varchar2(50),
5 inserting_pid number)
6 tablespace MSSM_TBS
7 /
Table created.
SQL> create unique index MSSM_Table_PK on MSSM_Table(invoice_id) tablespace MSSM_TBS;
Index created.
SQL> alter table MSSM_Table add constraint MSSM_Table_PK primary key (invoice_id);
Table altered.
SQL> drop sequence MSSM_invoice_Seq ;
drop sequence MSSM_invoice_Seq
*
ERROR at line 1:
ORA-02289: sequence does not exist
SQL> create sequence MSSM_invoice_Seq start with 1 increment by 1;
Sequence created.
SQL>
SQL>
I then create two procedures that I will use to simulate an application server / ETL engine that inserts 50,000 rows but uses the Sequence to generate Primary Key.
SQL> -- create procedures that will simulate an application server
SQL> -- or ETL engine that inserts 50000 rows
SQL> create or replace procedure populate_ASSM_Table
2 as
3 i number;
4 begin
5 for i in 1..50001
6 loop
7 insert into ASSM_Table
8 select ASSM_invoice_seq.nextval, sysdate+rownum/10000, rpad('A',40,'X'), pid
9 from
10 v$process p, v$session s
11 where p.addr=s.paddr
12 and s.sid=(select sid from v$mystat where rownum=1)
13 ;
14 end loop;
15 commit;
16 end;
17 /
Procedure created.
SQL>
SQL> create or replace procedure populate_MSSM_Table
2 as
3 i number;
4 begin
5 for i in 1..50001
6 loop
7 insert into MSSM_Table
8 select MSSM_invoice_seq.nextval, sysdate+rownum/10000, rpad('A',40,'X'), pid
9 from
10 v$process p, v$session s
11 where p.addr=s.paddr
12 and s.sid=(select sid from v$mystat where rownum=1)
13 ;
14 end loop;
15 commit;
16 end;
17 /
Procedure created.
SQL>
I then wait for the procedures to be executed by 3 sessions to simulate 3 concurrent sessions inserting 50,000 rows each :
SQL>
SQL> REM now run the procedures from three separate sessions
SQL> pause .............. wait for the procedures to be executed
.............. wait for the procedures to be executed
SQL>
Note : The two procedures are executed from 3 sessions (i.e. the three sessions first execute Populate_ASSM_Table, followed by an execution of Populate_MSSM_Table). The invocations of the procedures are almost concurrent as I execute them from SQL sessions in 3 terminal windows.
Once the executions are completed, I gather and review statistics for the two tables : the one in the ASSM Tablespace and the other in the MSSM Tablespace.
SQL> -- gather statistics on the tables and indexes
SQL> exec dbms_Stats.gather_table_stats('','ASSM_TABLE',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_Stats.gather_table_stats('','MSSM_TABLE',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> -- view statistics on the tables and indexes
SQL> select table_name, num_rows, blocks
2 from user_tables
3 where table_name in ('ASSM_TABLE','MSSM_TABLE')
4 order by 1
5 /
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
ASSM_TABLE 150003 1378
MSSM_TABLE 150003 1294
SQL>
SQL> select index_name, num_rows, blevel, leaf_blocks, clustering_factor
2 from user_indexes
3 where index_name in ('ASSM_TABLE_PK','MSSM_TABLE_PK')
4 order by 1
5 /
INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- -----------------
ASSM_TABLE_PK 150003 1 341 5729
MSSM_TABLE_PK 150003 1 334 1588
SQL>
Since I have run GATHER_TABLE_STATS, cascade=>TRUE, the CLUSTERING_FACTOR truly represents the distribution of the rows. Thus, after reading the row for INVOICE_ID 'n', the likelihood of INVOICE_ID 'n+1' being in the same block is much better in the MSSM_Table than in the ASSM_Table.
Now, here's a quiz question. Note the count of distinct blocks for each inserting Process. Why is the count of distinct blocks actually much higher for the MSSM Table ?
SQL> -- view the number of distinct blocks
SQL> select inserting_pid,count(distinct(dbms_rowid.rowid_block_number(rowid)))
2 from ASSM_TABLE
3 group by inserting_pid
4 order by 1
5 /
INSERTING_PID COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
------------- -----------------------------------------------------
28 622
29 711
30 666
SQL>
SQL> select inserting_pid,count(distinct(dbms_rowid.rowid_block_number(rowid)))
2 from MSSM_TABLE
3 group by inserting_pid
4 order by 1
5 /
INSERTING_PID COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
------------- -----------------------------------------------------
28 1269
29 1290
30 1273
SQL>
(Note : Each tablespace is only 1 datafile, so we need not consider FILE_ID when evaluating the BLOCK_NUMBERs).
.
.
UPDATE : See the test repeated with the distribution of INVOICE_IDs across multiple blocks (i.e. poorly clustered) in ASSM on 20-Oct.
.
.
.
04 October, 2011
Controlfiles : Number and Size
A forums thread "Maximum number of Control Files" allowed me to express my opinions about why, for very practical reasons, Oracle does not allow very many multiplexed copies of the controlfile and why the controlfile is "limited" in size.
Here are my two responses :
On the "maximum number of control files" :
You have to consider how Oracle uses the control file.
Logically it is one single controlfile multiplexed 'N' times. Therefore, each physical copy has to be an exact mirror of every other copy (just as every member of a log group is a mirror of other members in the same group).
When does Oracle update the controlfile ? Very frequently ! When a log switch occurs, when ARCH needs to add information about an archived file, when datafiles are added, when direct path operations take place, when RMAN creates backuppieces etc ....
Since the multiplexed controlfiles have to be mirrors, Oracle has to actually update every controlfile precisely. It has to lock the controlfile. If you seen 'enq : CF contention' waits and/or read threads and bugs relating to this you'd have seen the impact of this. The default CF enqueue is 900seconds. Database instances have been known to crash when the CF enqueue is held too long by one process while another background is attempting to update the controlfile.
If you have multiplexed the controlfile 12 times, the enqueue has to be held until Oracle is sure that all 12 copies have been written to the OS.
Essentially : the controlfile is a point of serialisation. Too many copies and the serialisation can be a severe constraint.
Also when there are multiple controlfile "copies" some of them might just happen to be on "slower" disks. In such a case, controlfile updates become as slow as the slowest disk !
So, rather than a "technical constraint" there is a real-world constraint.
Logically it is one single controlfile multiplexed 'N' times. Therefore, each physical copy has to be an exact mirror of every other copy (just as every member of a log group is a mirror of other members in the same group).
When does Oracle update the controlfile ? Very frequently ! When a log switch occurs, when ARCH needs to add information about an archived file, when datafiles are added, when direct path operations take place, when RMAN creates backuppieces etc ....
Since the multiplexed controlfiles have to be mirrors, Oracle has to actually update every controlfile precisely. It has to lock the controlfile. If you seen 'enq : CF contention' waits and/or read threads and bugs relating to this you'd have seen the impact of this. The default CF enqueue is 900seconds. Database instances have been known to crash when the CF enqueue is held too long by one process while another background is attempting to update the controlfile.
If you have multiplexed the controlfile 12 times, the enqueue has to be held until Oracle is sure that all 12 copies have been written to the OS.
Essentially : the controlfile is a point of serialisation. Too many copies and the serialisation can be a severe constraint.
Also when there are multiple controlfile "copies" some of them might just happen to be on "slower" disks. In such a case, controlfile updates become as slow as the slowest disk !
So, rather than a "technical constraint" there is a real-world constraint.
On the "limited size of the control file" :
Think in terms of how the controlfile can be backed up ...
Why did Oracle never allow User-Managed backups of controlfiles using OS commands like "cp" or "tar" or "cpio" ? Datafiles can be backed up (after an ALTER DATABASE / TABLESPACE BEGIN BACKUP) using such commands even though these may well use a block size of 512bytes or 1KB or 4KB -- a block size other than the database block size. The datafiles are "protected" from fractured block scenarios by capturing block updates in the redo stream. Unfortunately, controlfile updates cannot be so protected. A controlfile copy has to be an "all or nothing" operation. That is why Oracle provided the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' command -- it actually "locks" the controlfile while it is being backed up to the destination. An excessively large controlfile would take very long to backup and would mean that it would be locked for that duration.
Why did Oracle never allow User-Managed backups of controlfiles using OS commands like "cp" or "tar" or "cpio" ? Datafiles can be backed up (after an ALTER DATABASE / TABLESPACE BEGIN BACKUP) using such commands even though these may well use a block size of 512bytes or 1KB or 4KB -- a block size other than the database block size. The datafiles are "protected" from fractured block scenarios by capturing block updates in the redo stream. Unfortunately, controlfile updates cannot be so protected. A controlfile copy has to be an "all or nothing" operation. That is why Oracle provided the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' command -- it actually "locks" the controlfile while it is being backed up to the destination. An excessively large controlfile would take very long to backup and would mean that it would be locked for that duration.
.
.
.
03 October, 2011
RMAN Tips -- 3
ALWAYS mention the actual Start and End Times and Contents of all backups.
Whenever you
a. Post a query on forums / email lists
b. Talk to a colleague
c. Report to your manager
*always* mention the Start and End times and contents of your backups.
"Weekly Full Backup and Daily Incremental Backup" is empty of meaning when attempting to resolve a Backup-Restore issue.
There are very noticeable differences between
a) "Backup of 11pm on Sunday"
and
b) "Database Backup that began at 11pm on Sunday and completed at 1am on Monday"
and
c) "'BACKUP DATABASE PLUS ARCHIVELOG' that began at 11pm on Sunday and completed at 9am on Monday"
and
d) "'BACKUP DATABASE INCREMENTAL LEVEL 0 PLUS ARCHIVELOG DELETE INPUT' that began at 11pm on Sunday and completed at 9am on Monday"
Any DBA who thinks he's a DBA should know this and should understand why it is important to know the Start and End times and what was actually backed-up.
.
.
.
Subscribe to:
Posts (Atom)