Documentation  ·  Oracle

Session management

What Oracle is told, and by which half

Both halves of the product call DBMS_APPLICATION_INFO.SET_MODULE when they open a connection, and they report different things — which is the first thing to get straight, because only one of them says “MCPDBWizard”.

So a running MCP server does not identify itself as MCPDBWizard. It identifies itself as your factory class, which is DAO_FACTORY_NAME in the config. That is deliberate: with a service-account shape the Oracle username identifies nobody, and what a DBA wants to see is which application, not which vendor generated it.

Finding the sessions

SELECT sid, serial#, username, module, action, status, last_call_et
FROM   v$session
WHERE  module = 'MCPDBWizard'          -- the generator's dictionary reads
   OR  module = 'DaoFactory'           -- a generated server; use YOUR factory name
ORDER  BY last_call_et DESC;

That is also how you kill a runaway: identify it here, then ALTER SYSTEM KILL SESSION. Note that we have no reason to think you’ll need to do this.

Finding the SQL, in V$SQL

Every generated statement starts with the same marker, whatever the surface:

/* Created By MCPDBWizard */

So one pattern finds the lot — table CRUD, the unique-key/index/FK lookups, sequence nextval, PL/SQL calls and your own SQL statements:

SELECT   sql_id, module, executions, buffer_gets,
         ROUND(elapsed_time/1e6, 2) AS elapsed_sec,
         ROUND(elapsed_time/1e6/GREATEST(executions,1), 4) AS sec_per_exec,
         sql_text
FROM     v$sql
WHERE    sql_text LIKE '/* Created By MCPDBWizard */%'
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

That is the query for “what are the agents actually costing me” — and the one to run before blaming a SQL statement you exposed.

The marker is a plain comment: it costs a few bytes per cursor, survives into the shared pool exactly as written, and cannot change a plan.

What each statement looks like

A table statement also carries a comment per bind variable, naming the parameter and its Java type — which makes a statement in V$SQL readable back to the tool it came from:

/* Created By MCPDBWizard */
SELECT
       i.AIRPORT_CODE
     , i.AIRPORT_NAME
FROM AIRPORTS i
WHERE  i.AIRPORT_CODE = ? /* AirportCode String */

A PL/SQL call is an anonymous block that binds the arguments into locals and calls the routine in named notation, so the routine’s own parameter names show up too:

/* Created By MCPDBWizard */
DECLARE
p_fromcity VARCHAR2(16) := ?;
p_tocity VARCHAR2(16) := ?;
BEGIN
functionResult := CURSOR_EXAMPLE.DIRECT_FLIGHT_AVAILABLE(p_fromcity => p_fromcity, p_tocity => p_tocity);
...

A sequence is the marker and one line:

/* Created By MCPDBWizard */ SELECT "EMPLOYEE_ID_SEQ".nextval FROM DUAL

The product name is also in every generated Java file’s javadoc (Generated by MCPDBWizard build <n>), but that never reaches the database.

What carries across is the module. Oracle stamps MODULE and ACTION onto a statement in V$SQL when it is first parsed, so the same name that finds the session finds its SQL — and it keeps finding it after the session has gone, which V$SESSION cannot do.

SELECT   module, sql_id, executions, buffer_gets,
         ROUND(elapsed_time/1e6, 2) AS elapsed_sec,
         ROUND(elapsed_time/1e6/GREATEST(executions,1), 4) AS sec_per_exec,
         sql_text
FROM     v$sql
WHERE    module IN ('MCPDBWizard', 'DaoFactory')     -- again, your factory name
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

That is the query for “what are the agents actually costing me” — and the one to run before blaming a SQL statement you exposed.

One caveat worth knowing: MODULE is stamped at FIRST PARSE. A statement already in the shared pool, parsed by something else, keeps that other module — so a query your tools share with an existing application may be attributed to the application. Cursors are per statement text, not per session, and this view is a cache: an aged-out statement is simply gone. For a record that does not depend on the shared pool, use DBA_HIST_SQLSTAT (module is carried into AWR too), or the audit trail, which is the thing built for the purpose.

Give each config its own factory name if you run several servers against one database. They otherwise all report DaoFactory and neither view can tell them apart — and sum by (db_object) in Prometheus is doing that job with labels the generator wrote in.

One session, or one per caller

Unpooled, a generated server holds one shared connection and every tool call queues behind it. That is fine for a single desktop client and wrong for several agents at once.

Pooled (DAO_POOL=YES) each concurrent call borrows its own factory, which keeps its connection and its already-parsed statements — which is why this pools factories rather than connections. See Connection pooling.

Commit and release

Two config settings decide what happens around a call:

Pooling moves the transaction boundary. Unpooled, the transaction ends when the connection is released; pooled, it ends when a caller finishes and returns its factory. If your PL/SQL does its own transaction control, read Commit handling in called procedures before turning pooling on.

Runaway calls

Under normal usage this shouldn’t happen, as only approved SQL statements are run.

A rate limit bounds how often calls start, not how long one runs. The control that actually protects the database is DAO_QUERY_TIMEOUT_SECONDS: Oracle raises ORA-01013, the call fails, and the pooled factory goes back — so a runaway query stops holding a connection.