Oracle released Berkely DB 4.5

Yesterday Oracle released Berkely DB 4.5. It is open source(originially from SleepyCat), embeddede, non-relational, unattended DBMS.
Key Features in R4.5:

• Multi-Version Concurrency Control
• Non-stop Upgrades - coooool
  
• Replication Framework

Links:
http://www.sleepycat.com/
http://www.oracle.com/database/berkeley-db.html

SQL Trace (incl. 10g new tracing features)

--
-- sql trace
--
--related parameters
timed_statistics
max_dum_file_size
user_dump_dest

--to get session to be traced
select sid,serial# from v$session where ...
--or
select s.sid,s.serial#,s.username,s.osuser from v$session s,v$process p where s.paddr=p.addr;

use dbms_support to enable and disable the trace
($ORACLE_HOME/rdbms/admin/dbmssupp.sql)

--session level
alter session set sql_trace =true;
--instance level
alter system set sql_trace =true;

--trace other session
--to start tracing
--
--dbms_support(available in Oracle 7.2 and above)
exec dbms_support.start_trace_in_session(,);
exec dbms_support.start_trace_in_session(,,binds=true);
exec dbms_support.start_trace_in_session(,,waits=true,binds=true);
--
----dbms_system(undocumented since Oracle 7.3 to 10g R2)
see a guide at http://orafaq.com/papers/dbms_sys.doc
exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

--to check trace file under user_dum_dest
tkprof source output explain=user/pwd sys=no

--to stop tracing
--dbms_support
exec dbms_support.stop_trace_in_session(,);
--dbms_system
exec dbms_system.set_sql_trace_in_session(sid,serial#,false);

--Introduced in Oracle 10.1
DBMS_MONITOR
trcsess
DBA_ENABLED_TRACES
see http://julian.dyke.users.btopenworld.com/com/Presentations/OracleDiagnostics.ppt

Further Notes:
http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
http://www.nyoug.org/Presentations/SIG/DBA/hotsos_perf.PDF

Oracle Background Processes, incl. 10gR2

-------------------

--New in 10gR2

-------------------

PSP0 (new in 10gR2) - Process SPawner - to create and manage other Oracle processes.
NOTE: There is no documentation currently in the Oracle Documentation set on this process.

LNS1(new in 10gR2) - a network server process used in a Data Guard (primary) database.

Further explaination From "What's New in Oracle Data Guard?" in the Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)

"During asynchronous redo transmission, the network server (LNSn) process transmits redo data out of the online redo log
files on the primary database and no longer interacts directly with the log writer process. This change in behavior allows the log writer (LGWR) process to write redo data to the current online redo log file and continue processing the next request without waiting for inter-process communication or network I/O to complete."

-------------------

--New in 10gR1

-------------------

MMAN - Memory MANager - it serves as SGA Memory Broker and coordinates the sizing of the memory components, which keeps track of the sizes of the components and pending resize operations. Used by Automatic Shared Memory Management feature.

RVWR -Recovery Writer - which is responsible for writing flashback logs which stores pre-image(s) of data blocks. It is used by Flashback database feature in 10g, which 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.
- One can use Flashback Database to back out changes that:
- Have resulted in logical data corruptions.
- Are a result of user error.
- 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 - Job queue processes which are spawned as needed by CJQ0 to complete scheduled jobs. This is not a new process.


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

MMNL - Memory Monitor Light process - which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

MMON - Memory MONitor (MMON) process - 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 - Job Queue monitoring process - which is initiated with the job_queue_processes parameter. This is not new.

RBAL - It 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 - 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.

Changes about Queue Monitor Processes

The QMON processes are optional background processes for Oracle Streams Advanced Queueing (AQ) which monitor and maintain all the system and user owned AQ objects. These optional processes, like the job_queue processes, does not cause the instance to fail on process failure. They provide the mechanism for message expiration, retry, and delay, maintain queue statistics, remove processed messages from the queue table and maintain the dequeue IOT.

QMNx - Pre-10g QMON Architecture

The number of queue monitor processes is controlled via the dynamic initialisation parameter AQ_TM_PROCESSES. If this parameter is set to a non-zero value X, Oracle creates that number of QMNX processes starting from ora_qmn0_ (where is the identifier of the database) up to ora_qmnX_ ; if the parameter is not specified or is set to 0, then QMON processes are not created. There can be a maximum of 10 QMON processes running on a single instance. For example the parameter can be set in the init.ora as follows

aq_tm_processes=1 or set dynamically via alter system set aq_tm_processes=1;

QMNC & Qnnn - 10g QMON Architecture

Beginning with release 10.1, the architecture of the QMON processes has been changed to an automatically controlled coordinator slave architecture. The Queue Monitor Coordinator, ora_qmnc_, dynamically spawns slaves named, ora_qXXX_, depending on the system load up to a maximum of 10 in total.

For version 10.01.XX.XX onwards it is no longer necessary to set AQ_TM_PROCESSES when Oracle Streams AQ or Streams is used. However, if you do specify a value, then that value is taken into account. However, the number of qXXX processes can be different from what was specified by AQ_TM_PROCESSES. If AQ_TM_PROCESSES is not specified in versions 10.1 and above, QMNC only runs when you have AQ objects in your database.

Oracle Background processes By Don Burleson

Comparison of Background Processes between 8i OPS and 9i RAC

20 Differences Between Oracle on NT and Oracle on Unix

ORA-1000 MAXIMUM NUMBER OF CURSORS EXCEEDED after Java application migrated

ORA-1000 MAXIMUM NUMBER OF CURSORS EXCEEDED
in Java application orcurred after application migrated for iPlanet to JBoss infrastructure.

It is a very very common error message. I remember I was asked this question in a job interview a couple of years ago. But I cannot remember what I explained. Anyway I got the offer. Ok, I'd like to give it a summary, even I still have a question left.

--
--Troubleshooting Steps:
--
1. Login database as DBA to check current setting of open_cursors
> show parameter open_cursors

2. Check who open too many cursors

set line 140
col username format a16
col machine format a26
SELECT vs.username, vs.sid,vs.serial#, vs.machine,Count(*) "Open_Cursors"
FROM v$open_cursor voc, v$session vs
WHERE voc.sid= vs.sid
GROUP BY vs.username, vs.sid,vs.serial# ,vs.machine
ORDER BY 5;

3. Get the entire queries

SELECT vst.HASH_VALUE, vst.SQL_TEXT, vst.PIECE, Count(vst.SQL_TEXT)
FROM v$open_cursor voc, v$sqltext vst, v$session vs
WHERE voc.sid= vs.sid
AND vs.username='CMGWAOE'
and voc.HASH_VALUE=vst.HASH_VALUE
and voc.ADDRESS=vst.ADDRESS
GROUP BY vst.HASH_VALUE,vst.SQL_TEXT ,vst.PIECE
/

4. Narrow down where these queries are called or from.

5.Review Java code with developer.

Make sure to close "ResultSet" objects before closing "PreparedStatement"/"CallableStement" objects.

if forgot to close the "ResultSet", some java application server(JBoss) may not have ability to close the (Oracle) cursors when close statement.(iPanet does)
If connection pooling is using, this issue may get even worse.

6. After enhance java code, problem went away.

But further question is, why child cursors still are open in shared-pool after parent cursor closed???