How to Enable PL/SQL Query Logging?

In database management, tracking and analyzing SQL queries are essential for optimizing performance, debugging issues, and ensuring efficient resource utilization. PL/SQL query logging provided a robust mechanism to capture and store the information about the SQL queries executed within the PL/SQL code. By logging the above queries developers and administrators of the database gain valuable insights into the behavior of the applications of the database.

This article explains the concept of PL/SQL query logging and provides step-by-step guidance on enabling it in an Oracle database environment. It outlines the main component involved in setting up the query logging, including the creation of the logging tables and implementation of the logger procedures or packages and instrumentation of the PL/SQL code to the logging mechanism.

Prerequisites

The following are the prerequisites to enable PL/SQL query logging:

1. Access Permissions
2. Database Schema
3. Understanding of PL/SQL
4. Database Connectivity
5. Logging Strategy

Example for Enabling PL/SQL Query Logging

Step 1: Create a Logging Table

CREATE TABLE query_log (
    log_id NUMBER PRIMARY KEY,
    timestamp TIMESTAMP,
    sql_text VARCHAR2(4000),
    bind_variables VARCHAR2(4000)
);

• The above table can store the logged information, which includes the timestamp, SQL text, and bind variables.

Step 2: Write Logger Procedure or Package

CREATE OR REPLACE PACKAGE query_logger AS
    PROCEDURE log_query(p_sql_text IN VARCHAR2, p_bind_variables IN VARCHAR2 DEFAULT NULL);
END query_logger;
/

CREATE OR REPLACE PACKAGE BODY query_logger AS
    PROCEDURE log_query(p_sql_text IN VARCHAR2, p_bind_variables IN VARCHAR2 DEFAULT NULL) AS
    BEGIN
        INSERT INTO query_log (log_id, timestamp, sql_text, bind_variables)
        VALUES (query_log_seq.NEXTVAL, SYSTIMESTAMP, p_sql_text, p_bind_variables);
        COMMIT;
    END log_query;
END query logger;
/

• This is created by the package which is named query_logger with the procedure of log_query that inserts the new record into the table of query_log.

Step 3: Instrument Your PL/SQL Code

DECLARE
    v_sql_text VARCHAR2(4000);
    v_bind_variables VARCHAR2(4000);
BEGIN
    -- Your SQL statement
    v_sql_text := 'SELECT * FROM your_table WHERE column_name = :1';
    -- Bind variables (if any)
    v_bind_variables := 'value_of_bind_variable';
    
    -- Execute SQL statement
    query_logger.log_query(v_sql_text, v_bind_variables);
    
    -- Execute your SQL statement here
    -- ...
END;
/

• In the above example, to log the SQL text and bind variables, you can call the log_query procedure before executing the SQL statement.

Step 4: Enabled Logging in Production

-- Ensure that logging is enabled only in non-production environments or controlled circumstances in production.

• Make sure that the logging is enabled in non-production environments or controlled circumstances in production, it may impact performance due to the additional database operations.

Step 5: Combine the Code

• Let's combine all together with the complete example

CREATE TABLE query_log (
    log_id NUMBER PRIMARY KEY,
    timestamp TIMESTAMP,
    sql_text VARCHAR2(4000),
    bind_variables VARCHAR2(4000)
);

CREATE SEQUENCE query_log_seq;

CREATE OR REPLACE PACKAGE query_logger AS
    PROCEDURE log_query(p_sql_text IN VARCHAR2, p_bind_variables IN VARCHAR2 DEFAULT NULL);
END query_logger;
/

CREATE OR REPLACE PACKAGE BODY query_logger AS
    PROCEDURE log_query(p_sql_text IN VARCHAR2, p_bind_variables IN VARCHAR2 DEFAULT NULL) AS
    BEGIN
        INSERT INTO query_log (log_id, timestamp, sql_text, bind_variables)
        VALUES (query_log_seq.NEXTVAL, SYSTIMESTAMP, p_sql_text, p_bind_variables);
        COMMIT;
    END log_query;
END query_logger;
/

DECLARE
    v_sql_text VARCHAR2(4000);
    v_bind_variables VARCHAR2(4000);
BEGIN
    -- Your SQL statement
    v_sql_text := 'SELECT * FROM employees WHERE department_id = :1';
    -- Bind variables (if any)
    v_bind_variables := '20';
    
    -- Execute SQL statement
    query_logger.log_query(v_sql_text, v_bind_variables);
    
    -- Execute your SQL statement here
    -- ...
END;
/

Explanation:

1. Creation of Table: The code creates the table and is named query_log to store the information about the logged SQL queries.
2. Sequence Creation: It is used to create the sequence called query_log_seq to generate the unique IDs for log entries.
3. Package Creation: The package is named query_logger for creating the group procedures. It contains the procedure log_query to the log SQL queries.
4. Procedure Implementation: Within the package body, the log_query procedure inserts the new log entry into the query_log table, and it includes the SQL text and bind variables.
5. PL/SQL Block: The DECLARE block will initialize the SQL text and bind variables. The log_query procedure is called the log SQL query into the query_log table.

Step 6: To view the data

SELECT * FROM query_log;

• This query is used to retrieve all records from the query_log table. This table shows the log IDs, execution timestamp, SQL text and bind variables of the logged queries.

Output:

Conclusion

In conclusion, the above code establishes a robust mechanism for logging SQL queries executed within the PL/SQL code in an Oracle database. By creating the logging table, sequence, and package, developers can effectively store valuable information about the query executions. The logging mechanism into the database applications, developers, and database administrators gain insights into the query performance, adding in debugging, optimizing, and overall monitoring of the system.