DBA_DML_LOCKS

DBA_DML_LOCKS lists all DML locks held in the database and all outstanding requests for a DML lock.

Column	Datatype	NULL	Description
SESSION_ID	NUMBER		Session holding or acquiring the lock
OWNER	VARCHAR2(30)	NOT NULL	Owner of the lock
NAME	VARCHAR2(30)	NOT NULL	Name of the lock
MODE_HELD	VARCHAR2(13)		The type of lock held. The values are: · ROWS_S (SS): row share lock · ROW-X (SX): row exclusive lock · SHARE (S): share lock · S/ROW-X (SSX): exclusive lock · NONE: lock requested but not yet obtained
MODE_REQUESTED	VARCHAR2(13)		Lock request type. The values are: · ROWS_S (SS): row share lock · ROW-X (SX): row exclusive lock · SHARE (S): share lock · S/ROW-X (SSX): exclusive lock · NONE: Lock identifier obtained; lock not held or requested
LAST_CONVERT	NUMBER		The last convert
BLOCKING_OTHERS	VARCHAR2(40)		Blocking others

Oracle data dictionary views

Oracle dynamic performance views

PRE_PAGE_SGA

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	No
Range of values	true | false

PRE_PAGE_SGA determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.

Note:

1. This setting does not prevent your operating system from paging or swapping the SGA after it is initially read into memory.

PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. The cost of this strategy is fixed; however, you might simply determine that 20,000 pages must be touched every time a process starts. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.

The advantage that PRE_PAGE_SGA can afford depends on page size. For example, if the SGA is 80 MB in size and the page size is 4 KB, then 20,000 pages must be touched to refresh the SGA (80,000/4 = 20,000).

If the system permits you to set a 4 MB page size, then only 20 pages must be touched to refresh the SGA (80,000/4,000 = 20). The page size is operating system-specific and generally cannot be changed. Some operating systems, however, have a special implementation for shared memory whereby you can change the page size.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘PRE_PAGE_SGA’;

Oracle initializatoin parameters

processes

Property	Description
Parameter type	Integer
Default value	100
Modifiable	No
Range of values	6 to operating system dependent
Basic	Yes
Real Application Clusters	Multiple instances can have different values.

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.

Note:

1. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘PROCESSES’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE		ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
processes	2400	FALSE	FALSE	FALSE	FALSE		FALSE	user processes

Oracle initializatoin parameters

QUERY_REWRITE_ENABLED

Property	Description
Parameter type	String
Syntax	QUERY_REWRITE_ENABLED = { false | true | force }
Default value	If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then true If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0 or lower, then false
Modifiable	ALTER SESSION, ALTER SYSTEM
Real Application Clusters	Multiple instances can have different values.

QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database.

Values:

· false

Oracle does not use rewrite.

· true

Oracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.

· force

Oracle always uses rewrite and does not evaluate the cost before doing so. Use force when you know that the query will always benefit from rewrite and when reduction in compile time is important.

To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.

Note:

1. This parameter can be altered at session level. It needs to be set to TRUE to enable the use of functional indexes and for stored outlines.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘QUERY_REWRITE_ENABLED’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE		ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
query_rewrite_enabled	TRUE	FALSE	TRUE	IMMEDIATE	TRUE		FALSE	allow rewrite of queries using materialized views if enabled

Oracle initializatoin parameters

QUERY_REWRITE_INTEGRITY

Property	Description
Parameter type	String
Syntax	QUERY_REWRITE_INTEGRITY = { enforced | trusted | stale_tolerated }
Default value	enforced
Modifiable	ALTER SESSION, ALTER SYSTEM
Real Application Clusters	Multiple instances can have different values.

QUERY_REWRITE_INTEGRITY determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.

Values:

· enforced

Oracle enforces and guarantees consistency and integrity.

· trusted

Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.

· stale_tolerated

Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.

Note:

1. Related parameter: QUERY_REWRITE_ENABLED

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘QUERY_REWRITE_INTEGRITY’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE		ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
query_rewrite_integrity	STALE_TOLERATED	FALSE	TRUE	IMMEDIATE	TRUE		FALSE	perform rewrite using materialized views with desired integrity

Oracle initializatoin parameters