Tuesday, August 12, 2008

V$BLOCKING_QUIESCE

Oracle 11gR1

V$BLOCKING_QUIESCE indicates if a session is blocking, or would block, a quiesce operation.

Column

Datatype

Description

SID

NUMBER

Session identifier

Note:

A database can be put into a quiesced mode by issuing the statement:

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (e.g., enqueues). No inactive sessions are allowed to become active. The statement may take a long time for active sessions to become inactive. Querying V$BLOCKING_QUIESCE view returns the sessions that are blocking the quiesce operations.

More information can be revealed by joining with V$SESSION:

select bl.sid, user, osuser, type, program

from v$blocking_quiesce bl, v$session se

where bl.sid = se.sid;

Querying ACTIVE_STATE column of V$INSTANCE view reveals the current state of an instance. Its values are:

  • NORMAL: normal unquiesced state.
  • QUIESCING: being quiesced, but some non-DBA sessions (username not as SYSTEM or SYS) are still active.
  • QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

A quiesced database can be restored to normal operation by issuing the following statement:

ALTER SYSTEM UNQUIESCE;

Oracle dynamic performance views

Posted by Admin at 8/12/2008 02:01:00 PM 0 comments

Labels: ,

DBA_ALERT_HISTORY

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.


===============================================================
Oracle 11gR1
DBA_ALERT_HISTORY describes a time-limited history of alerts which are no longer outstanding.

Column
Datatype
NULL
Description
SEQUENCE_ID
NUMBER
NOT NULL
Alert sequence number
REASON_ID
NUMBER
ID of the alert reason
OWNER
VARCHAR2(30)
Owner of the object on which the alert was issued
OBJECT_NAME
VARCHAR2(513)
Name of the object
SUBOBJECT_NAME
VARCHAR2(30)
Name of the subobject
OBJECT_TYPE
VARCHAR2(64)
Object type
REASON
VARCHAR2(4000)
Reason for the alert
TIME_SUGGESTED
TIMESTAMP(6) WITH TIME ZONE
Time when the alert was last updated
CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
Time when the alert was first produced
SUGGESTED_ACTION
VARCHAR2(4000)
Advice of the recommended action
ADVISOR_NAME
VARCHAR2(30)
Name of the advisor to be invoked for more information
METRIC_VALUE
NUMBER
Value of the related metrics
MESSAGE_TYPE
VARCHAR2(12)
Message type:
· Notification
· Warning
MESSAGE_GROUP
VARCHAR2(64)
Name of the message group to which the alert belongs
MESSAGE_LEVEL
NUMBER
Severity message level (1 to 32)
HOSTING_CLIENT_ID
VARCHAR2(64)
ID of the client or security group to which the alert relates
MODULE_ID
VARCHAR2(64)
ID of the module that originated the alert
PROCESS_ID
VARCHAR2(128)
Process ID
HOST_ID
VARCHAR2(256)
DNS host name of the originating host
HOST_NW_ADDR
VARCHAR2(256)
IP or other network address of the originating host
INSTANCE_NAME
VARCHAR2(16)
Originating instance name
INSTANCE_NUMBER
NUMBER
Originating instance number
USER_ID
VARCHAR2(30)
User ID
EXECUTION_CONTEXT_ID
VARCHAR2(60)
ID of the thread of execution
ERROR_INSTANCE_ID
VARCHAR2(142)
ID of an error instance plus a sequence number
RESOLUTION
VARCHAR2(7)
Resolution:
· cleared
· N/A
Note:
1.
SELECT REASON_ID, OBJECT_NAME, OBJECT_TYPE, REASON, TIME_SUGGESTED, SUGGESTED_ACTION
FROM DBA_ALERT_HISTORY
ORDER BY TIME_SUGGESTED DESC;
REASON_ID
OBJECT_NAME
OBJECT_TYPE
REASON
TIME_SUGGESTED
SUGGESTED_ACTION
9
UNDO
TABLESPACE
Tablespace [UNDO] is [70 percent] full
2008-08-12 3:59:38.061726 AM -07:00
Add space to the tablespace
9
CADASTRE_TABLES
TABLESPACE
Tablespace [CADASTRE_TABLES] is [74 percent] full
2008-08-09 4:07:27.364360 PM -07:00
Add space to the tablespace
10
UNDO
TABLESPACE
Snapshot Too Old Error detected: SQL ID 9ama6jfu7cpkw, Snapshot SCN 0x08ca.4862a1e4, Recent SCN 0x08ca.4b064c83, Undo Tablespace UNDO, Current Undo Retention 70238.
2008-08-06 3:56:20.442627 PM -07:00
Please run undo advisor.
9
UNDO
TABLESPACE
Tablespace [UNDO] is [83 percent] full
2008-08-04 4:12:38.097134 PM -07:00
Add space to the tablespace
2. The following dictionary views provide information about server alerts:
  • DBA_THRESHOLDS lists the threshold settings defined for the instance.
  • DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
  • DBA_ALERT_HISTORY lists a history of alerts that have been cleared.
  • V$ALERT_TYPES provides information such as group and type for each alert.
  • V$METRICNAME contains the names, identifiers, and other information about the system metrics.
  • V$METRIC and V$METRIC_HISTORY views contain system-level metric values in memory.

Oracle data dictionary views

Posted by Admin at 8/12/2008 02:00:00 PM

Labels: ,

DBA_OUTSTANDING_ALERTS

Oracle 11gR1
DBA_OUTSTANDING_ALERTS describes alerts which the server considers to be outstanding.
Column
Datatype
NULL
Description
SEQUENCE_ID
NUMBER

Alert sequence number
REASON_ID
NUMBER
NOT NULL
ID of the alert reason
OWNER
VARCHAR2(30)

Owner of the object on which the alert was issued
OBJECT_NAME
VARCHAR2(513)

Name of the object
SUBOBJECT_NAME
VARCHAR2(30)

Name of the subobject
OBJECT_TYPE
VARCHAR2(64)

Object type
REASON
VARCHAR2(4000)

Reason for the alert
TIME_SUGGESTED
TIMESTAMP(6) WITH TIME ZONE

Time when the alert was last updated
CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE

Time when the alert was first created
SUGGESTED_ACTION
VARCHAR2(4000)

Advice of the recommended action
ADVISOR_NAME
VARCHAR2(30)

Name of the advisor to be invoked for more information
METRIC_VALUE
NUMBER

Value of the related metrics
MESSAGE_TYPE
VARCHAR2(12)

Message type:
· Notification
· Warning
MESSAGE_GROUP
VARCHAR2(64)

Name of the message group to which the alert belongs
MESSAGE_LEVEL
NUMBER

Message severity level (1 to 32)
HOSTING_CLIENT_ID
VARCHAR2(64)

ID of the client or security group to which the alert relates
MODULE_ID
VARCHAR2(64)

ID of the module that originated the alert
PROCESS_ID
VARCHAR2(128)

Process ID
HOST_ID
VARCHAR2(256)

DNS host name of the originating host
HOST_NW_ADDR
VARCHAR2(256)

IP or other network address of the originating host
INSTANCE_NAME
VARCHAR2(16)

Originating instance name
INSTANCE_NUMBER
NUMBER

Originating instance number
USER_ID
VARCHAR2(30)

User ID
EXECUTION_CONTEXT_ID
VARCHAR2(60)

ID of the threshold of execution
ERROR_INSTANCE_ID
VARCHAR2(142)

ID of an error instance plus a sequence number
Note:
1.
select reason_id, object_name, object_type, reason, creation_time,
suggested_action, message_group
from DBA_OUTSTANDING_ALERTS
order by creation_time desc;
REASON
_ID
OBJECT
_NAME
OBJECT
_TYPE
REASON
CREATION
_TIME
SUGGESTED
_ACTION
MESSAGE
_GROUP
9
UNDO
TABLESPACE
Tablespace [UNDO] is [86 percent] full
2008-08-12 5:19:56.053828
AM -07:00
Add space to
the tablespace
Space
9
BUS_TABLES
TABLESPACE
Tablespace [BUS_TABLES] is
[85 percent] full
2008-08-09 9:18:30.787179
PM -07:00
Add space to
the tablespace
Space
9
BUS_INDEXES
TABLESPACE
Tablespace [BUS_INDEXES] is
[88 percent] full
2006-06-11 7:46:39.945133
AM -07:00
Add space to
the tablespace
Space
2. The following dictionary views provide information about server alerts:
  • DBA_THRESHOLDS lists the threshold settings defined for the instance.
  • DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
  • DBA_ALERT_HISTORY lists a history of alerts that have been cleared.
  • V$ALERT_TYPES provides information such as group and type for each alert.
  • V$METRICNAME contains the names, identifiers, and other information about the system metrics.
  • V$METRIC and V$METRIC_HISTORY views contain system-level metric values in memory.

Oracle data dictionary views


More Oracle DBA tips, please visit Oracle DBA Tips 

Posted by Admin at 8/12/2008 01:59:00 PM 0 comments

Labels: ,

DBA_THRESHOLDS

Oracle 11gR1

DBA_THRESHOLDS describes all thresholds.

Column

Datatype

NULL

Description

METRICS_NAME

VARCHAR2(64)


Metrics name

WARNING_OPERATOR

VARCHAR2(12)


Relational operator for warning thresholds:

· GT

· EQ

· LT

· LE

· GE

· CONTAINS

· NE

· DO NOT CHECK

· DO_NOT_CHECK

WARNING_VALUE

VARCHAR2(256)


Warning threshold value

CRITICAL_OPERATOR

VARCHAR2(12)


Relational operator for critical thresholds:

· GT

· EQ

· LT

· LE

· GE

· CONTAINS

· NE

· DO NOT CHECK

· DO_NOT_CHECK

CRITICAL_VALUE

VARCHAR2(256)


Critical threshold value

OBSERVATION_PERIOD

NUMBER


Observation period length (in minutes)

CONSECUTIVE_OCCURRENCES

NUMBER


Number of occurrences before an alert is issued

INSTANCE_NAME

VARCHAR2(16)


Instance name; null for database-wide alerts

OBJECT_TYPE

VARCHAR2(64)


Object type:

· SYSTEM

· SERVICE

· EVENT_CLASS

· TABLESPACE

· FILE

OBJECT_NAME

VARCHAR2(513)


Name of the object for which the threshold is set

STATUS

VARCHAR2(7)


Indicates whether the threshold is applicable on a valid object (VALID) or not (INVALID)

Note:

1.

select * from dba_thresholds;

METRICS_

NAME

WARNING_

OPERATOR

WARNING_

VALUE

CRITICAL_

OPERATOR

CRITICAL_

VALUE

OBSERVATION_

PERIOD

CONSECUTIVE_

OCCURRENCES

INSTANCE_

NAME

OBJECT_

TYPE

OBJECT_

NAME

STATUS

Tablespace Space Usage

GE

85

GE

97

1

1

database_wide

TABLESPACE

NULL

VALID

2. The following dictionary views provide information about server alerts:

  • DBA_THRESHOLDS lists the threshold settings defined for the instance.
  • DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
  • DBA_ALERT_HISTORY lists a history of alerts that have been cleared.
  • V$ALERT_TYPES provides information such as group and type for each alert.
  • V$METRICNAME contains the names, identifiers, and other information about the system metrics.
  • V$METRIC and V$METRIC_HISTORY views contain system-level metric values in memory.

Oracle data dictionary views

Posted by Admin at 8/12/2008 01:58:00 PM 0 comments

Labels: ,

V$SHARED_POOL_RESERVED

Oracle 11gR1

V$SHARED_POOL_RESERVED displays statistics that help you tune the reserved pool and space within the shared pool.

Column

Datatype

Description

FREE_SPACE

NUMBER

Total amount of free space on the reserved list

AVG_FREE_SIZE

NUMBER

Average size of the free memory on the reserved list

FREE_COUNT

NUMBER

Number of free pieces of memory on the reserved list

MAX_FREE_SIZE

NUMBER

Size of the largest free piece of memory on the reserved list

USED_SPACE

NUMBER

Total amount of used memory on the reserved list

AVG_USED_SIZE

NUMBER

Average size of the used memory on the reserved list

USED_COUNT

NUMBER

Number of used pieces of memory on the reserved list

MAX_USED_SIZE

NUMBER

Size of the largest used piece of memory on the reserved list

REQUESTS

NUMBER

Number of times that the reserved list was searched for a free piece of memory

REQUEST_MISSES

NUMBER

Number of times the reserved list did not have a free piece of memory to satisfy the request, and started flushing objects from the LRU list

LAST_MISS_SIZE

NUMBER

Request size of the last request miss, when the reserved list did not have a free piece of memory to satisfy the request and started flushing objects from the LRU list

MAX_MISS_SIZE

NUMBER

Request size of the largest request miss, when the reserved list did not have a free piece of memory to satisfy the request and started flushing objects from the LRU list

REQUEST_FAILURES

NUMBER

Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-04031 occurred)

LAST_FAILURE_SIZE

NUMBER

Request size of the last failed request (that is, the request size for the last ORA-04031 error)

ABORTED_REQUEST_THRESHOLD

NUMBER

Minimum size of a request which signals an ORA-04031 error without flushing objects

ABORTED_REQUESTS

NUMBER

Number of requests that signalled an ORA-04031 error without flushing objects

LAST_ABORTED_SIZE

NUMBER

Last size of the request that returned an ORA-04031 error without flushing objects from the LRU list

Note:

1.

select free_space, free_count, used_space, used_count, request_failures,

last_failure_size from V$SHARED_POOL_RESERVED;

FREE_SPACE

FREE_COUNT

USED_SPACE

USED_COUNT

REQUEST_FAILURES

LAST_FAILURE_SIZE

212373616

302

3585936

99

21293

4064

2. The following views are useful for obtaining information about shared server configuration and for monitoring performance.

View

Description

V$DISPATCHER

Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number.

V$DISPATCHER_CONFIG

Provides configuration information about the dispatchers.

V$DISPATCHER_RATE

Provides rate statistics for the dispatcher processes.

V$QUEUE

Contains information on the shared server message queues.

V$SHARED_SERVER

Contains information on the shared servers.

V$CIRCUIT

Contains information about virtual circuits, which are user connections to the database through dispatchers and servers.

V$SHARED_SERVER_MONITOR

Contains information for tuning shared server.

V$SGA

Contains size information about various system global area (SGA) groups. May be useful when tuning shared server.

V$SGASTAT

Contains detailed statistical information about the SGA, useful for tuning.

V$SHARED_POOL_RESERVED

Lists statistics to help tune the reserved pool and space within the shared pool.

Oracle dynamic performance views

Posted by Admin at 8/12/2008 01:58:00 PM 0 comments

Labels: ,

Subscribe to: Posts (Atom)