LILAM

LILAM - PL/SQL Process Monitoring & Observability Framework

LILAM = “LILAM Is Logging And Monitoring”

Release Status License: GPL v3 License: Enterprise Größe Sponsor

Lila Logger Logo

LILAM is a high-performance process monitoring, observability and logging framework for Oracle PL/SQL. It provides deep real-time insights into process metrics and utilizes a dynamic JSON-based rule engine to trigger autonomous responses and coordinate complex workflows. Its simple API allows for seamless integration into existing applications with minimal overhead.

LILAM utilizes autonomous transactions to ensure that process states, log entries, and performance metrics are persisted independently of the main execution flow. This decoupled approach guarantees a complete audit trail and reliable monitoring data, even if the primary business process undergoes a rollback.

LILAM is developed by a developer who hates over-engineered tools. Focus: 5 minutes to integrate, 100% visibility.

  DECLARE
    l_pid NUMBER;
  BEGIN
    l_pid := lilam.new_session('IMPORT_CUSTOMERS', lilam.logLevelInfo);
    lilam.info(l_pid, 'Import started');
    -- your business logic
    lilam.info(l_pid, 'Import finished');
    lilam.close_session(l_pid);
  END;
  /

  select   no, info, session_time, caller
  from     lilam_log
  order by process_id desc, no;

Content

Quick start

  1. Execute grants
  2. Copy & compile spec and body
  3. Execute 
     DECLARE
   l_pid NUMBER;
 BEGIN
   l_pid := lilam.new_session('MY_PROCESS');
   lilam.warn(l_pid, 'Hello LILAM');
   lilam.close_session(l_pid);
 END;
 /
  4. Query (the tables are created automatically on the first call) 
      select * from lilam_proc; -- process status
  select * from lilam_log;  -- log details
  select * from lilam_mon;  -- events and traces

Key features

  1. Lightweight: One Package, a handful of Tables, one Sequence. That’s it!
  2. Concurrent Logging: Supports multiple, simultaneous log entries from the same or different sessions without blocking
  3. Runtime Observability: Captures and correlates logs, events and business transactions in near real-time, providing deep, fluent performance tracing across application sessions
  4. Rule-Based Orchestration: Evaluates versioned JSON rule-sets against observability data to automatically trigger alerts or execute decoupled consumers for reactive error-handling and workflow control
  5. Hybrid Execution: Run LILAM in-session or offload processing to a dedicated LILAM-Server (decoupled).
  6. Data Integrity: Uses autonomous transactions to guarantee log persistence regardless of the main transaction’s outcome
  7. Smart Context Capture: Automatically records ERR_STACK, ERR_BACKTRACE, and ERR_CALLSTACK based on log level—deep insights with zero manual effort
  8. Optional self-cleaning: Automatically purges expired logs per application during session start—no background jobs or schedulers required
  9. Future Ready: Built and tested in latest Oracle 26ai (2026) and 19c environment
  10. Small Footprint: <5k lines of logical PL/SQL code ensures simple quality and security control, fast compilation, zero bloat and minimal Shared Pool utilization (reducing memory pressure and fragmentation)

Comparison

While traditional PL/SQL tools focus on logging or low-level tracing, LILAM introduces process-level observability directly inside the database. It combines process lifecycle tracking, metrics, and rule-based reactions into a unified in-database observability model.

This table compares different approaches to logging, instrumentation and observability in PL/SQL. It highlights conceptual focus areas rather than full feature parity.

Logging Frameworks

Capability LILAM Logger PIT log4plsql
Logging ✅ ✅ ✅ ✅
Log Levels ✅ ✅ ✅ ✅
Error Context (Stack, Backtrace) ✅ ✅ ✅ ⚠️
Autonomous Transaction Logging ✅ ✅ ⚠️ ✅
Minimal Setup (Package-based) ✅ ✅ ⚠️ ❌

Instrumentation & Debugging

Capability LILAM Console Custom Instrumentation
Logging ✅ ✅ ⚠️
Runtime Instrumentation ✅ ✅ ✅
Session / Context Tracking ✅ ⚠️ ❌
Performance Insights ✅ ⚠️ ⚠️
Centralized Data Model ✅ ❌ ❌

Native Oracle Tools

Capability LILAM DBMS_TRACE / PROFILER
Low-level Tracing ⚠️ ✅
Profiling ❌ ✅
Process Lifecycle ✅ ❌
Aggregated Metrics ✅ ❌
Real-time Monitoring ✅ ❌
Developer-friendly API ✅ ❌

Architecture at a Glance

LILAM Architektur

Fast integration

LILAM comes ready to test right out of the box, so no custom implementation or coding is required to see the framework in action immediately after setup. First code impressions you can find here: learn_lilam.

Advantages

The following points complement the Key Features and provide a deeper insight into the architectural decisions and technical innovations of LILAM.

Smart Load Balancing & Execution

LILAM introduces a high-performance Client-Server architecture using Oracle Pipes. This allows for asynchronous log processing and cross-session monitoring

How it works

LILAM offers two execution models that can be used interchangeably:

  1. In-Session Mode (Direct): Initiated by lilam.new_session. LILAM acts as embedded library, Log and Metric calls are executed immediately within your current database session. This is ideal for straightforward debugging and ensuring data is persisted synchronously.
  2. Decoupled Mode (Server-based): In this mode, LILAM decouples the request from the execution. It acts as a proxy within the application session, offloading the heavy lifting to dedicated background worker processes.
    • Server Side: Launch one or more LILAM servers using lilam.start_server('PIPE_NAME', 'GROUP_NAME', 'PASSWORD'); (or lilam.create_server(...) to run them as scheduler jobs). Each server listens on its own pipe and registers under a group name. You can scale by running multiple servers in the same group or use different groups for logical separation.
    • Client Side: Register via lilam.server_new_session('PROCESS_NAME', 'GROUP_NAME');. LILAM automatically selects an available server of that group (or any available server if no group is given).
    • Execution: Log calls are serialized into a pipe and processed by the background server, minimizing the impact on your transaction time.

[!IMPORTANT] Unified API: Regardless of the chosen mode, the logging API remains identical. You use the same lilam.log(...) calls throughout your application. The only difference is the initial setup (lilam.new_session for In-Session mode vs. lilam.server_new_session for Decoupled mode).

Performance & Safety

LILAM prioritizes the stability of your application. It uses a Hybrid Model to balance speed and system integrity:

[!IMPORTANT] Buffering means write latency. Only entries up to the sync level of a process (p_syncLevel, default ERROR) are written synchronously: they are committed before the call returns, in In-Session and in Decoupled mode (there the client additionally writes them into LILAM_LOG of its schema as a safety net). All other entries, metrics and status updates stay in memory for up to about 1.5 seconds (longer if the session makes no further LILAM call). If a session dies without CLOSE_SESSION or FLUSH, these entries are lost. Details, measurements and failure scenarios: When Is a Log Entry Stored?

Technology

Autonomous Persistence

LILAM strictly utilizes PRAGMA AUTONOMOUS_TRANSACTION. This guarantees that log entries and monitoring data are permanently stored in the database, even if the calling main transaction performs a ROLLBACK due to an error. This ensures the root cause remains available for post-mortem analysis.

Deep Context Insights

By leveraging the UTL_CALL_STACK, LILAM automatically captures the exact program execution path. Instead of just logging a generic error, it documents the entire call chain, significantly accelerating the debugging process in complex, nested PL/SQL environments.

High-Performance Buffering

To minimize the impact on the main application’s overhead, LILAM features an internal buffering system. Log writing is processed efficiently, offering a decisive performance advantage over simple, row-by-row logging methods, especially in high-load production environments. The exception are entries up to the sync level (default ERROR): they are written immediately, so that the error and everything logged before it are stored (see the note under Performance & Safety).

Robust & Non-Invasive (Silent Mode)

LILAM is designed to be “invisible.” The framework ensures that an internal error during the logging process (e.g., table space issues or configuration errors) doesn’t crash the calling application logic. Exceptions within LILAM are caught and handled internally, prioritizing the stability of your business transaction over the logging activity itself.

Built-in Extensibility (Adapters)

LILAMs decoupled architecture is designed for seamless integration with modern monitoring stacks. Its structured data format allows for the easy creation of adapters.

High-Efficiency Monitoring

Real-Time and Granular Action Tracking

LILAM is a specialized framework for deep process insights. Using MARK_EVENT and TRACE functionality, named actions are monitored independently. The framework automatically tracks metrics per action and context:

Intelligent Metric Calculation

Instead of performing expensive aggregations across millions of monitor records, LILAM uses an incremental calculation mechanism. Metrics like averages and counters are updated on-the-fly. This ensures that monitoring dashboards (e.g., in Grafana, APEX, or Oracle Jet) remain highly responsive even with massive datasets.

Core Strengths

Scalability & Cloud Readiness

By avoiding file system dependencies (UTL_FILE) and focusing on native database features, LILAM is suited for scalable cloud infrastructures.

Developer Experience (DX)

LILAM promotes a standardized error-handling and monitoring culture within development teams. Its easy-to-use API allows for a “zero-config” start, enabling developers to implement professional observability in just a few minutes. No excessive DBA grants or infrastructure overhead required — just provide standard PL/SQL permissions, deploy the package, and start logging immediately.

Process Tracking & Monitoring

LILAM categorizes data by its intended use to ensure maximum performance for status queries and analysis:

Rule-based Observability & Orchestration

LILAM doesn’t just log data; it evaluates it. Using versioned JSON Rule-Sets, LILAM monitors process changes and business transactions in real-time.

Key Benefits:

How To - The Subway Sample

To illustrate how LILAM works, imagine monitoring a subway system:

Process (TRACK_LINE_4): The overall mission or service run of a specific line.

Event (CLOSE_DOOR): A discrete point in time. We mark this event at a specific station (STATION_ID_400). If a mandatory event is missing, LILAM can trigger an alert.

Trace/Transaction (TRACK_SECTION): A time-based segment representing the travel between two points (e.g. SECTION_ID_402). By using trace_start and trace_stop, we automatically measure the travel time (latency).

Identifier for the ongoing Process

  l_processId NUMBER;

Open Session (begin Process) and set Session values

  -- Start the mission (as a new Process/Session.
  -- This and all other calls return in microseconds, as the LILAM proxy instantly offloads the workload to the asynchronous worker.
  -- Optional group-based isolation: LILAM servers can be assigned to specific groups to ensure strict workload isolation
  l_processId := lilam.server_new_session(p_processName => 'TRACK_LINE_4', p_groupName => 'UNDERGROUND_MONITORING', p_logLevel => lilam.logLevelMonitor);

  -- set number of steps this mission needs to be finished correctly
  -- in our sample there are only two steps: leaving station and arriving station
  lilam.set_steps_todo(p_processId => l_processId, p_stepsToDo => 2);
  
  -- leave station
  lilam.step_done(p_processId => l_processId); -- increments step-counter into `1`

Monitor Action (Metric) and Log

  -- doors must be closed (Event)
  lilam.mark_event(p_processId => l_processId, p_actionName => 'CLOSE_DOOR', p_contextName => 'STATION_ID_400);

  -- log travel start
  lilam.info(p_processId => l_processId, p_logText => 'Line 4 leaving base');

Track Business Transactions

  -- travel the segment (trace Transaction by starting and stopping)
  lilam.trace_start(p_processId => l_processId, p_actionName => 'TRACK_SECTION', p_contextName => 'SECTION_ID_402');
  dbms_session.sleep(30); -- the train needed 30 seconds
  lilam.trace_stop(p_processId => l_processId, p_actionName => 'TRACK_SECTION', p_contextName => 'SECTION_ID_402');

Close Session (end Process)

  -- the mission of line is very! short - only one section; so the mission ends here
  --   !  missed code: lilam.step_done(p_processId => l_processId); -- increments step-counter into `2`
  lilam.info(p_processId => l_processId, p_logText => 'Line 4 is back');
  lilam.close_session(p_processId => l_processId);

  -- the step-counter still is `1`. If there was an implemented rule-set which awaits 2 steps
  -- at the end of mission, LILAM would raise an `ALERT`

Data

LILAM stores its data in three tables per application. Their names are derived from p_tabNameMaster (default LILAM): LILAM_PROC, LILAM_LOG and LILAM_MON. The tables are created automatically on the first API call. The tables displayed below illustrate core content and, depending on the specific LILAM version, may include additional columns. The complete structure is described in architecture and concepts.

Process data

One row per process run; provides the current status of the process.

SELECT id, process_name, process_start, process_end, last_update, steps_todo, steps_done, status, info
FROM   lilam_proc
WHERE  process_name = 'MY_PROCESS';
ID PROCESS_NAME PROCESS_START PROCESS_END LAST_UPDATE STEPS_TODO STEPS_DONE STATUS INFO
1 MY_PROCESS 12.01.26 18:17:51,… 12.01.26 18:18:53,… 12.01.26 18:18:53,… 100 99 2 ERROR

Logging data

SELECT process_id, no, info, log_level_c, session_time, session_user, host_name, err_stack, err_backtrace, err_callstack
FROM   lilam_log
WHERE  process_id = <id>
ORDER  BY no;
PROCESS_ID NO INFO LOG_LEVEL_C SESSION_TIME SESSION_USER HOST_NAME ERR_STACK ERR_BACKTRACE ERR_CALLSTACK
1 1 Start INFO 13.01.26 10:… SCOTT SERVER1 NULL NULL NULL
1 2 Function A DEBUG 13.01.26 11:… SCOTT SERVER1 NULL NULL ”— PL/SQL …”
1 3 Something happened ERROR 13.01.26 12:… SCOTT SERVER1 ”— PL/SQL …” ”— PL/SQL …” ”— PL/SQL …”

Monitoring data

Events (MON_TYPE = 0) and traces (MON_TYPE = 1) share one table. STOP_TIME remains NULL for events.

SELECT process_id, mon_type, action, context, start_time, stop_time, used_millis, avg_millis, action_count
FROM   lilam_mon
WHERE  process_id = <id>;
PROCESS_ID MON_TYPE ACTION CONTEXT START_TIME STOP_TIME USED_MILLIS AVG_MILLIS ACTION_COUNT
1 0 MY_ACTION MY_CONTEXT 13.01.26 10:… NULL 402 402 1
1 0 MY_ACTION MY_CONTEXT 13.01.26 10:… NULL 510 456 2
1 1 TRANS_ACT ROUTE_1 13.01.26 10:… 13.01.26 10:… 490 490 1

Repository Structure

Locations of the core components:

Performance Benchmark

LILAM is designed for high-concurrency environments. The following results were achieved on standard Consumer Hardware (Fujitsu LIFEBOOK A-Series) running an Oracle Database inside VirtualBox. This demonstrates the massive efficiency of the Pipe-to-Bulk architecture, even when facing significant virtualization overhead (I/O emulation and CPU scheduling):

Configuration Throughput Status
Exclusive Server (1 Client) ~1.6k msg/s Finished in 30m
Shared Server (2 Clients) ~2.2k msg/s Finished in 45m

Key Takeaway: Even on mobile hardware, LILAM handles millions of records without blocking the application sessions. On enterprise-grade server hardware with NVMe storage, throughput is expected to scale significantly higher.

LILAM was developed and stress-tested on a consumer-grade laptop using Oracle Database 23ai Free. To provide a realistic assessment of its capabilities, a rigorous test scenario was designed to push the entire system to its physical limits under these conditions.

For a detailed analysis of throughput, latency, and resource efficiency, please refer to the full reports:

License

This project is dual-licensed:

If you wish to use LILAM in a proprietary environment without the GPL “copyleft” obligations, please contact me for a commercial license.

Roadmap

Support the Project 💜

Do you find LILAM useful? Consider sponsoring the project to support its ongoing development and long-term maintenance.

Beer

This site is open source. Improve this page.