Architecture:

Hash Group By

10g: Hash Group By aggregation enabled instead of SORT GROUP BY

_gby_hash_aggregation_enabled

Parallel query

10g: Parallel Query (PQ) has been enhanced to allow more queries to be parallelized

New background:

11g:

DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks.

DIA0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution.

EMNC (event monitor coordinator) is the background server process used for database event management and notifications.

FBDA (flashback data archiver process) archives the historical rows of tracked tables into flashback data archives. Tracked tables are tables which are enabled for flashback archive. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the flashback archive. It also keeps metadata on the current rows.

FBDA is also responsible for automatically managing the flashback data archive for space, organization, and retention and keeps track of how far the archiving of tracked transactions has occurred.

GTX0-j (global transaction) processes provide transparent support for XA global transactions in an Oracle RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions. Global transaction processes are only seen in an Oracle RAC environment

GMON maintains disk membership in ASM disk groups.

KATE performs proxy I/O to an ASM metafile when a disk goes offline.

MARK marks ASM allocation units as stale following a missed write to an offline disk.

SMCO (space management coordinator) process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task.

VKTM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms and available only when running at elevated priority

Optimizer:

push predicate:

10g: it will automatically rewrite the work to enables the join of the view and table to

become the join between the base table and table.

join can be performed by nested loop rahter than just hash join and sort merge

11g: it extends the feature for group by, distinct, anti-join, semi-join.

bloom filtering:

11g: partition pruning will user bloom filtering rather than subquery pruning

distinct:

11g: delimit the extra hash/sort steps in block level when run select distinct query

extended statistics:

11g: statistiscs can be collected based on a group of columns (distinct value, null value, density)

instead of each single column.

Adaptive cursor sharing:

11g: each time sql is running, the optimizer will peak the bind variable and

generate the alternative execution plan if necessary. oracle will use different plan depending

on various cursor result.

FULL OUTER JOIN:

11g: use hash full outer joins instead of union all join

stats collection:

11g: hash algorithm is used for estimate stats collection for more precision and less time.

multi columns, view can have statistics

density value:

11g, it will be collected at run time instead of storing in data dictionary

MVIEW refresh:

10g: use dbms_mview.refresh instead of truncate on base table, more efficient and avoid inconsistency

db hanging management:

11g: rdmbs will automatically dump the information by DIA0 process

Key Parameter:

PARALLEL_MAX_SERVERS

10g: the default is derived using the following formula:

CPU_COUNT x PARALLEL_THREADS_PER_CPU x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5

SEC_MAX_FAILED_LOGIN_ATTEMPTS

11g: control max number of authentication attempts made from client on a connection to server process