DBWR_IO_SLAVES vs DB_WRITER_PROCESSES

DBWR_IO_SLAVES vs DB_WRITER_PROCESSES
Questions about multiple DBWR processes have plagued DBAs since Oracle7. You configured multiple DBWR process in Oracle7 by setting the parameter DB_WRITERS. In Oracle7, multiple DBWR processes were actually slave processes that were unable to perform asynchronous I/O calls on their own. The algorithm used by the Oracle7 DBWR caused it to incur waits when the delay of a single write caused additional writes to queue up until the initial write was complete.
Oracle8 and later release’s DBWR architecture corrects this problem. Oracle’s DBWR now writes continuously without waiting for previous writes to complete. The new design allows DBWR to act as if it were inherently synchronous, regardless of whether the operating system supports asynchronous I/O or not. Administrators are able to configure multiple DBWR process by setting the init.ora parameter db_writer_processes. Multiple database writers became available in Oracle 8.0.4 and allow true multiple database writes. There is no master-slave relationship as in Version 7.

If you implement database writer I/O slaves by setting the dbwr_io_slaves parameter, you configure a single (master) DBWR process that has slave processes that are subservient to perform asynchronous I/O calls. I/O slaves can also be used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or implement it inefficiently.
You can't activate both multiple DBWRs and I/O slaves. If both parameters are set in the parameter file, dbwr_io_slaves will take precedence.
To determine whether to use multiple DBWn processes or database slaves, follow these guidelines:
Use db_writer_processes for most write intensive applications. One per CPU is the recommended setting.
Use db_writer_processes for databases that have a large data buffer cache.
Use dbwr_io_slaves for applications that are not write intensive and run on operating systems that support asynchronous I/O.
Use dbwr_io_slaves on platforms that do no support asynchronous I/O.
Use dbwr_io_slaves on single CPU systems. Multiple DBWR processes are CPU intensive.

Dictionary Objects you should know

Oracle Dictionary View Objects.

USER_TAB_COMMENTS Stores comment information on tables.

USER_COL_COMMENTS Stores comment information for columns.

USER_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint.

ALL_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint.

USER_OBJECTS Can be used to verify the validity status of all user database objects.

ALL_OBJECTS Can be used to verify the validity status of all database objects.

USER_UPDATABLE_COLUMNS COLUMN_NAME and UPDATABLE will tell you whether the columns in a complex view can be modified.

USER_INDEXES Contains the name of the index and it uniqueness.

USER_IND_COLUMNS Contains the index name, the table name, and the column name.

USER_SYS_PRIVS Shows all privileges associated with this user.

SESSION_PRIVS Shows all privileges available in this session.

USER_ROLE_PRIVS Identifies the roles granted to you.

ROLE_ROLE_PRIVS Identifies the roles granted to other roles in the database.

ROLE_TAB_PRIVS Identifies object privileges granted to roles.

ROLE_SYS_PRIVS Identifies system privileges granted to roles.

SESSION_ROLES Identifies roles available to the current session.

Bitmap Join Indexes

Bitmap Join Indexes

Oracle8i supported bitmap indexes on a single table. 9i adds bitmap join indexes, built on two tables, to optimize access for a specific join condition:

CREATE BITMAP INDEX my_bit_join_ix
ON emp_table (e.job)
FROM emp_table e, dept_table d
WHERE e.deptno = d.deptno ;

Restrictions:

• Can not reference an IOT, temporary table, or a self-join
• Only one table can be updated concurrently by different transactions
• Parallel DML is only supported on the fact table, and the columns in the index must all be columns of the dimension tables
• Join must be on primary key or unique-constraint key, and, if the key is composite, the entire key must be used