New Background Process Information in 10g

BACKGROUND PROCESS INFORMATION
=================================

MMAN

SGA Background Process
The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize operations

RVWR
======
Flashback database
—————————–
– This a new feature introduced in 10g.
– Flashbacking a database means going back to a previous database state.
– The Flashback Database feature provides a way to quickly revert an entire Oracle database to the state it was in at a past point in time.
– This is different from traditional point in time recovery.
– A new background process Recovery Writer ( RVWR) introduced which is responsible for writing flashback logs which stores pre-image(s) of data blocks
– This feature is not applicable for recovering the database in case of media
failure.
– The time required for flashbacking a database to a specific time in past is DIRECTLY PROPORTIONAL to the number of changes made and not on the size of the database.

Jnnn
=====
These are job queue processes which are spawned as needed by CJQ0 to complete scheduled jobs. This is not a new process.

CTWR
=====

This is a new process Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.

MMNL
=====

The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

MMON
======

The Manageability Monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.

M000
======
MMON background slave (m000) processes.

CJQn
=====
This is the Job Queue monitoring process which is initiated with the job_queue_processes parameter. This is not new.

RBAL
====
This is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx
====
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM
controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB
=====
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

Advertisements

Default parameter settings when gathering statistics on 9i and 10g

This document outlines how to determine the default parameter settings when gathering statistics on Table on 9i and 10g.

On 9i, Gather procedures have a number of hard coded default values
On 10g, All procedures that gather optimizer statistics no longer have hardcoded default values.

The defaults can be viewed using :

select dbms_stats.get_param(‘cascade’) from dual;
select dbms_stats.get_param(‘degree’) from dual;
select dbms_stats.get_param(‘estimate_percent’) from dual;
select dbms_stats.get_param(‘method_opt’) from dual;
select dbms_stats.get_param(‘no_invalidate’) from dual;
select dbms_stats.get_param(‘granularity’) from dual;

Parameters can be set using DBMS_STATS.SET_PARAM.

EXAMPLES

Get Parameter Example

SQL> select dbms_stats.get_param(‘method_opt’) from dual;

DBMS_STATS.GET_PARAM(‘METHOD_OPT’)
———————————-
FOR ALL COLUMNS SIZE AUTO

Set Parameter Example

SQL> exec dbms_stats.set_param(‘METHOD_OPT’, ‘FOR ALL COLUMNS SIZE 1’)
PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param(‘method_opt’) from dual;

DBMS_STATS.GET_PARAM(‘METHOD_OPT’)
———————————-
FOR ALL COLUMNS SIZE 1

The default values on 9i are hard coded and are as follows:

DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL, –> ALL partitions
estimate_percent NUMBER DEFAULT NULL, –> 100% sample
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT ‘FOR ALL COLUMNS SIZE 1’,
degree NUMBER DEFAULT NULL, –> parallel degree 1
granularity VARCHAR2 DEFAULT ‘DEFAULT’, –> level (PARTITION + GLOBAL)
cascade BOOLEAN DEFAULT FALSE, –> does not cascade to indexes by default
no_invalidate BOOLEAN DEFAULT FALSE);

Recommendation Statistics generation for Siebel Database in 10g

Recommendation Statistics generation for Siebel Database in 10g: the optimizer mode is all_rows

1. For all tables that have more than 1 million rows use the following:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1’,
estimate_percent => 20,
granularity => ‘ALL’,
cascade => TRUE,
degree => 8);

For large tables only take a 20 percent percent sample size, set 8 degrees of parallelism. The method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1’ tells the db not to generate histograms for the indexed columns of these large tables.

2. For all tables that have more than 15 rows and less than 1 million rows use the following:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’,
granularity => ‘ALL’,
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE);

For smaller tables let the db determine the estimate_percent and degrees of parallelism. The method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’ tells the db to generate histograms for the indexed columns of these tables (in addition to other index stats generating mechanisms).

3. For all tables that have 15 rows or less delete the stats from these tables:

EXEC DBMS_STATS.DELETE_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
force => TRUE);

The Oracle db optimizer is known to perform inefficiently if stats are generated on empty and very small tables. Therefore the solution is to delete any stats on these tables.

We generated the stats using the above mechanisms using the SQL below, this resulted in much better performance application wide.