V$OSSTAT

V$OSSTAT displays system utilization statistics from the operating system. One row is returned for each system statistic.

Column	Datatype	Description
STAT_NAME	VARCHAR2(64)	Name of the statistic (see table below)
VALUE	NUMBER	Instantaneous statistic value
OSSTAT_ID	NUMBER	Statistic ID
COMMENTS	VARCHAR2(64)	Any additional OS-specific clarifications for the statistic
CUMULATIVE	VARCHAR2(3)	Indicates whether the statistic is cumulative (that is, accumulates over time) (YES) or not (NO)

V$OSSTAT Statistics

Statistic Name	Description
NUM_CPUS	Number of CPUs or processors available
IDLE_TIME	Number of hundredths of a second that a processor has been idle, totalled over all processors
BUSY_TIME	Number of hundredths of a second that a processor has been busy executing user or kernel code, totalled over all processors
USER_TIME	Number of hundredths of a second that a processor has been busy executing user code, totalled over all processors
SYS_TIME	Number of hundredths of a second that a processor has been busy executing kernel code, totalled over all processors
IOWAIT_TIME	Number of hundredths of a second that a processor has been waiting for I/O to complete, totalled over all processors
NICE_TIME	Number of hundredths of a second that a processor has been busy executing low-priority user code, totalled over all processors
AVG_IDLE_TIME	Number of hundredths of a second that a processor has been idle, averaged over all processors
AVG_BUSY_TIME	Number of hundredths of a second that a processor has been busy executing user or kernel code, averaged over all processors
AVG_USER_TIME	Number of hundredths of a second that a processor has been busy executing user code, averaged over all processors
AVG_SYS_TIME	Number of hundredths of a second that a processor has been busy executing kernel code, averaged over all processors
AVG_IOWAIT_TIME	Number of hundredths of a second that a processor has been waiting for I/O to complete, averaged over all processors
AVG_NICE_TIME	Number of hundredths of a second that a processor has been busy executing low-priority user code, averaged over all processors
OS_CPU_WAIT_TIME	Total number of hundredths of a second that processes have been in a ready state, waiting to be selected by the operating system scheduler to run
RSRC_MGR_CPU_WAIT_TIME	Total number of hundredths of a second that Oracle processes have been in a ready state, waiting for CPU to be available for their consumer group in the currently active resource plan
VM_PAGE_IN_BYTES	Total number of bytes of data that have been paged in due to virtual memory paging
VM_PAGE_OUT_BYTES	Total number of bytes of data that have been paged out due to virtual memory paging
PHYSICAL_MEMORY_BYTES	Total number of bytes of physical memory
LOAD	Current number of processes that are either running or in the ready state, waiting to be selected by the operating-system scheduler to run. On many platforms, this statistic reflects the average load over the past minute.
NUM_CPU_CORES	Number of CPU cores available (includes subcores of multicore CPUs as well as single-core CPUs)
NUM_CPU_SOCKETS	Number of CPU sockets available (represents an absolute count of CPU chips on the system, regardless of multithreading or multi-core architectures)
TCP_SEND_SIZE_MIN	Minimum size of the TCP send buffer
TCP_SEND_SIZE_DEFAULT	Default size of the TCP send buffer
TCP_SEND_SIZE_MAX	Maximum size of the TCP send buffer
TCP_RECEIVE_SIZE_MIN	Minimum size of the TCP receive buffer
TCP_RECEIVE_SIZE_DEFAULT	Default size of the TCP receive buffer
TCP_RECEIVE_SIZE_MAX	Maximum size of the TCP receive buffer
GLOBAL_SEND_SIZE_MAX	Maximum size of the global send buffer
GLOBAL_RECEIVE_SIZE_MAX	Maximum size of the global receive buffer

Note:

1. The availability of all statistics except for NUM_CPUS and RSRC_MGR_CPU_WAIT_TIME is subject to the operating system platform on which the Oracle Database is running.

2. select * from V$OSSTAT;

STAT_NAME	VALUE	OSSTAT_ID
NUM_CPUS	4	0
IDLE_TIME	14036520	1
BUSY_TIME	9937639	2
USER_TIME	7886436	3
SYS_TIME	2051203	4
IOWAIT_TIME	0	5
AVG_IDLE_TIME	3507618	7
AVG_BUSY_TIME	2482915	8
AVG_USER_TIME	1970078	9
AVG_SYS_TIME	511292	10
AVG_IOWAIT_TIME	0	11
OS_CPU_WAIT_TIME	999400	13
RSRC_MGR_CPU_WAIT_TIME	0	14
LOAD	0.96875	15
PHYSICAL_MEMORY_BYTES	34240520192	1008
VM_IN_BYTES	3093569536	1009
VM_OUT_BYTES	0	1010

Oracle data dictionary views

Oracle dynamic performance views

Why some process remain KILLED in V$SESSION

When an inactive session is killed via “alter system kill session 'SID,SERIAL#'” command, the process continues in v$session with status as KILLED.

SQL> Select username,sid,serial#,status from v$session where username = 'USERA'; USERNAME SID SERIAL# STATUS --------------- ---------- ---------- -------- USERA 147 720 INACTIVE

SQL> alter system kill session '147,720'; System altered.

SQL> Select USERNAME, SID, PROCESS, STATUS, PROGRAM from v$session where username = 'USERA'; USERNAME SID PROCESS STATUS PROGRAM --------------- ---------- ------------ -------- -------------------- USERA 147 4956:4848 KILLED sqlplus.exe

SQL> show user USER is "USERA" SQL> select * from user_tables; select * from user_tables * ERROR at line 1: ORA-00028: your session has been killed

SQL> Select USERNAME, SID, PROCESS, STATUS, PROGRAM from v$session where username = 'USERA'; no rows selected

This happens when the client is SQL*Plus and Forms.

When the user tries to use the terminated session, the message ORA-00028 is returned.

When an inactive session has been terminated, STATUS in the view V$SESSION is "KILLED." The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.

If an active session cannot be interrupted (for example, it is performing network I/O or rolling back a transaction), the session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits up to 60 seconds for the session to be terminated; if the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been "marked" to be terminated. A session marked to be terminated is indicated in V$SESSION with a status of "KILLED" and a server that is something other than "PSEUDO."

Please note that KILLing a background thread (DBWR, LGWR, SMON or PMON) will crash the instance.

ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose]

At the end of shutting down a database, got ORA-00600 error in alert log:

Tue Feb  3 12:30:18 2009 Starting background process EMN0 EMN0 started with pid=29, OS id=21581 Tue Feb  3 12:30:18 2009 Shutting down instance: further logons disabled Tue Feb  3 12:30:18 2009 Stopping background process QMNC Tue Feb  3 12:30:18 2009 Stopping background process CJQ0 Tue Feb  3 12:30:20 2009 Stopping background process MMNL Tue Feb  3 12:30:21 2009 Stopping background process MMON Tue Feb  3 12:30:23 2009 Shutting down instance (immediate) License high water mark = 46 Tue Feb  3 12:30:23 2009 Stopping Job queue slave processes Tue Feb  3 12:30:23 2009 Job queue slave processes stopped All dispatchers and shared servers shutdown Tue Feb  3 12:32:55 2009 ALTER DATABASE CLOSE NORMAL Tue Feb  3 12:32:55 2009 SMON: disabling tx recovery SMON: disabling cache recovery Tue Feb  3 12:32:56 2009 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Thread 1 closed at log sequence 33665 Successful close of redo thread 1 Tue Feb  3 12:32:56 2009 Completed: ALTER DATABASE CLOSE NORMAL Tue Feb  3 12:32:56 2009 ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active System State dumped to trace file /oracle/admin/mydb/udump/mydb_ora_1541.trc Tue Feb  3 12:32:59 2009 Errors in file /oracle/admin/mydb/udump/mydb_ora_1541.trc: ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []

Note:

1.       It is a bug in Oracle 10.2.0.1/2/3/4 identified as Bug 4483084 - OERI[LibraryCacheNotEmptyOnClose] on shutdown. The bug is fixed in 11.1.0.6 base release.

2.       The error can be ignored as it just indicates that there are some items in the library cache when closing down the instance. The error itself occurs AFTER the database close and dismount stages so only affects the instance shutdown itself. Datafiles have been closed cleanly.

3.       The error is annoying because the RMAN cold backup fails when the error happens.

4.       After googling the web, two triggers were suggested to solve the issue if you have not upgraded to 11.1.0.6:

CREATE or replace TRIGGER flush_shared_pool BEFORE SHUTDOWN ON DATABASE BEGIN   execute immediate 'ALTER SYSTEM FLUSH SHARED_POOL'; EXCEPTION   WHEN OTHERS THEN   RAISE_APPLICATION_ERROR (num => -20000, msg => 'Error flushing pool'); END;

CREATE or replace TRIGGER flush_shared_pool BEFORE SHUTDOWN ON DATABASE BEGIN   execute immediate 'alter TRIGGER SYS.OLAPISTARTUPTRIGGER DISABLE';   execute immediate 'ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER DISABLE';   execute immediate 'ALTER SYSTEM FLUSH SHARED_POOL';   execute immediate 'alter TRIGGER SYS.OLAPISTARTUPTRIGGER ENABLE';   execute immediate 'ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER ENABLE'; EXCEPTION   WHEN OTHERS THEN   RAISE_APPLICATION_ERROR (num => -20000, msg => 'Error flushing pool'); END;

More Oracle DBA tips, please visit Oracle DBA Tips  

Database administrator (dba) (Oracle DBA)

Title: Database administrator (dba) (Oracle DBA)

Terms of Employment: Temporary, Full Time

Salary: To be negotiated

Anticipated Start Date: As soon as possible

Location: Brampton, Ontario (1 vacancy)

Skill Requirements:

Education: Completion of university

Credentials (certificates, licences, memberships, courses, etc.): Oracle Certification

Experience: 3 years to less than 5 years

Languages: Speak English, Read English, Write English

Specific Skills: Design and develop database, Design, construct, modify, implement and test data models and database management systems, Conduct research and provide advice to other informatics professionals regarding the selection application and implementation of database management tools, Operate database management systems to analyze data, Develop and implement data administration policy, standards and models, Research and document data requirements, data collection and administration policy, and data access rules, Develop policies and procedures for network access and usage and for the backup and recovery of data, Conduct research and provide advice to other information systems professionals regarding the collection, availability and suitability of data, Lead and co-ordinate teams of data administrators in the development and implementation of data policies, standards and models

Management Systems (erp or db): Data Warehouse, Erwin, Informix, Oracle / PL-SQL / CASE / Developer 2000 / Ingres

Essential Skills: Writing, Oral communication, Working with others, Problem solving, Decision making, Critical thinking

Other Information:Candidate must have at least 5 years experience with UNIX, Informatica Administration, Oracle Administration , MS Office and MS Office Visio

Employer: Apsoft Inc

How to Apply:

Please apply for this job only in the manner specified by the employer. Failure to do so may result in your application not being properly considered for the position.

By E-mail: hr.apsoft@gmail.com

Advertised until: 2009/02/25