FORALL Command in Oracle PL/SQL

 The FORALL command in Oracle PL/SQL is a helpful feature designed to optimize bulk data manipulation. It enhances performance by minimizing context switches between the PL/SQL engine and the SQL engine, making it ideal for large-scale data operations.

What is the FORALL Command?

FORALL is a loop construct that efficiently performs bulk INSERT, UPDATE, or DELETE operations. Unlike traditional FOR loops, FORALL processes entire collections in a single batch, reducing the overhead of multiple context switches.

Syntax of FORALL

FORALL index IN lower_bound .. upper_bound

    sql_statement;

Examples of Using FORALL

DECLARE

    TYPE emp_data IS TABLE OF employees%ROWTYPE;

    v_emps emp_data;

BEGIN

    SELECT * BULK COLLECT INTO v_emps FROM employees_data WHERE department_id = 100;

    FORALL i IN v_emps.FIRST .. v_emps.LAST

        INSERT INTO employees_data_table VALUES v_emps(i);

    COMMIT;

END;

/

The FORALL command is a vital tool for enhancing performance in Oracle PL/SQL bulk operations. By mastering its syntax, combining it with SAVE EXCEPTIONS, and following best practices, you can significantly improve the efficiency of data manipulation tasks in your Oracle database projects.

INSERT ALL Command in Oracle PL/SQL

The INSERT ALL command in Oracle PL/SQL is a powerful feature for inserting multiple data rows into one or more tables efficiently. 

This will simplify data insertion logic, reduces data redundancy, and improves performance when dealing with bulk data operations.

What is the INSERT ALL Command?

The INSERT ALL statement allows multiple INSERT operations to be performed in a single command, which  improves efficiency and minimizes network round-trips.

Syntax of INSERT ALL

INSERT ALL

    INTO table1 (column1, column2) VALUES (value1, value2)

    INTO table2 (column1, column2) VALUES (value3, value4)

SELECT * FROM dual;

Examples of Using INSERT ALL




1. Inserting Data into Multiple Tables

INSERT ALL

    INTO employees_dept (id, name, department) VALUES (1, 'ABC_123', 'Sales')

    INTO employees_old (id, name, department) VALUES (2, 'XYZ_567', 'Sales')

SELECT * FROM dual;

2. Conditional Insertion with WHEN




INSERT ALL also allows conditional logic for row insertion.

INSERT ALL

    WHEN salary > 10000 THEN

        INTO high_employee_earners (id, name, salary) VALUES (id, name, salary)

    WHEN salary <= 5000 THEN

        INTO normal_earners (id, name, salary) VALUES (id, name, salary)

SELECT id, name, salary FROM employees;

This syntax can be used and modified accordingly for your requirements.

Key Procedures in DBMS_CLOUD

Key Procedures in DBMS_CLOUD

1. DBMS_CLOUD.CREATE_CREDENTIAL

   • Registers cloud credentials for secure access.

2. DBMS_CLOUD.DROP_CREDENTIAL

   • Deletes a previously created credential.

3. DBMS_CLOUD.LIST_FILES

   • Lists files in cloud storage locations.

4. DBMS_CLOUD.GET_OBJECT

   • Downloads files from cloud storage to a database directory.

5. DBMS_CLOUD.PUT_OBJECT

   • Uploads files from the database to cloud storage.

6. DBMS_CLOUD.COPY_DATA

   • Imports data from cloud storage into a database table.

7. DBMS_CLOUD.COPY_FILES

   • Copies files from one cloud location to another.

8. DBMS_CLOUD.DELETE_FILE

   • Deletes files from a cloud storage location.

9. DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE

   • Validates the structure and data of an external table.

10. DBMS_CLOUD.CREATE_EXTERNAL_TABLE

    • Creates an external table that reads data directly from cloud storage.

Creating Global Temporary Table - Oracle Syntax

 

Creating Global Temporary Table

Here is the syntax for creating a GTT is similar to a regular table but includes the ON COMMIT clause to define its behavior.

Example:

CREATE GLOBAL TEMPORARY TABLE sales_gtt (
    sale_number NUMBER,
    product_name VARCHAR2(100),
    sale_date DATE
) ON COMMIT DELETE ROWS;  -- Data deleted at the end of each transaction

Alternatively, for session-specific data retention:

CREATE GLOBAL TEMPORARY TABLE temp_logs (
    log_id NUMBER,
    log_message VARCHAR2(255)
) ON COMMIT PRESERVE ROWS;  -- Data persists until the session ends

Global Temporary Tables (GTT) in Oracle Database

 

Global Temporary Tables (GTT) is a powerful feature in Oracle Database that provides a way to manage temporary data during a session or transaction. 

Unlike regular stored database tables, data stored in GTTs is temporary and automatically cleared when the session ends or the transaction completes.

Features of Global Temporary Tables

1. Session-Specific or Transaction-Specific Data: GTTs holds data that persists for either the duration of a session or a transaction.
2. Automatic Data Cleanup: Data is automatically deleted when the defined scope ends.
3. Performance Optimization: GTTs are often stored in temporary segments, reducing redo log generation and improving performance.
4. Structure Persistence: While data is temporary, the table structure itself persists in the database.

Creating a Global Temporary Table

Syntax for creating a GTT is similar to a regular table but includes the ON COMMIT clause to define its behavior.

Example:

CREATE GLOBAL TEMPORARY TABLE sales_gtt (
    sale_number NUMBER,
    product_name VARCHAR2(100),
    sale_date DATE
) ON COMMIT DELETE ROWS;  -- Data deleted at the end of each transaction

Alternatively, for session-specific data retention:

CREATE GLOBAL TEMPORARY TABLE temp_logs (
    log_id NUMBER,
    log_message VARCHAR2(255)
) ON COMMIT PRESERVE ROWS;  -- Data persists until the session ends

Use Cases

• Staging Data for Reporting: Useful for holding intermediate data during complex data transformation tasks.
• Temporary Storage for Batch Processing: will acts as a workspace for data manipulation within a session.
• Session-Based Customization: helps manage session-specific calculations or configurations.

Best Practices

• Avoid Indexing GTTs Unless Necessary: Since data is temporary, indexing may add unnecessary overhead unless performance issues arise.
• Use GTTs for Intermediate Results: Ideal for holding temporary result sets in complex queries.
• Monitor Storage Usage: GTT data is stored in the temporary tablespace; ensure enough space is allocated.

For projects involving data staging, batch processing, or complex transformations, GTTs are a reliable solution to improve database efficiency.

How to Creating a Simple Batch Job in Oracle PL/SQL

 Here is the PL/SQL Snippet to create and schedule a batch job.

Note : Please test and modify accordingly as per your requirements.

BEGIN

    DBMS_SCHEDULER.CREATE_JOB (

        job_name        => 'MY_BATCH_JOB',

        job_type        => 'PLSQL_BLOCK',

        job_action      => 'BEGIN MY_PACKAGE.MY_PROCEDURE; END;',

        start_date      => SYSTIMESTAMP,

        enabled         => TRUE

    );

END;

Understanding Oracle DBMS_CLOUD Common Packages

Understanding Oracle DBMS_CLOUD Common Packages

Oracle Cloud provides the DBMS_CLOUD package to simplify the integration of cloud-based storage, data loading, and external data access. 

This package is useful for managing data in Oracle Autonomous Database and other cloud environments, enabling users to efficiently interact with external cloud services.

Overview of DBMS_CLOUD

The DBMS_CLOUD package is a collection of procedures and functions that help users perform operations such as loading data, managing credentials, and accessing external storage. It eliminates the need for complex scripting and provides secure access to cloud-based resources.

DBMS_CLOUD Packages and their usages.

1. DBMS_CLOUD.CREATE_CREDENTIAL

This procedure securely stores cloud service credentials, which are used to authenticate database access to object storage.

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'my_credential',
        username => 'your_username',
        password => 'your_password'
    );
END;

2. DBMS_CLOUD.LIST_OBJECTS

Retrieves a list of objects from a specified cloud storage bucket.

SELECT * FROM TABLE(DBMS_CLOUD.LIST_OBJECTS(
    credential_name => 'my_credential',
    location => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/'
));

3. DBMS_CLOUD.GET_OBJECT

Downloads a file from cloud storage into the database directory.

BEGIN
    DBMS_CLOUD.GET_OBJECT(
        credential_name => 'my_credential',
        object_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/sample.csv',
        directory_name => 'DATA_PUMP_DIR',
        file_name => 'sample.csv'
    );
END;

4. DBMS_CLOUD.COPY_DATA

Loads data from an external source (such as an object storage CSV file) into an Oracle database table.

BEGIN
    DBMS_CLOUD.COPY_DATA(
        table_name => 'employees',
        credential_name => 'my_credential',
        file_uri_list => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/employees.csv',
        format => JSON_OBJECT('delimiter' VALUE ',', 'skipheaders' VALUE 1)
    );
END;

5. DBMS_CLOUD.DELETE_CREDENTIAL

Removes a stored credential when it is no longer needed.

BEGIN
    DBMS_CLOUD.DELETE_CREDENTIAL('my_credential');
END;

Benefits of Using DBMS_CLOUD

• Secure Cloud Storage Integration – Simplifies connecting Oracle databases with cloud storage services.
• Efficient Data Loading – Enables quick and efficient bulk data imports from external sources.
• Flexible Querying – Allows querying external data sources without physically moving data.
• Improved Performance – Optimized for cloud environments, reducing manual data transfer overhead.

Understanding Oracle SQL ID and Its Role in Performance Issues

 Understanding Oracle SQL ID and Its Role in Performance Issues

In Oracle databases, the SQL ID is a fundamental identifier that helps database administrators (DBAs) track and analyze SQL statements. When performance issues arise, SQL IDs can provide critical insights into problematic queries, enabling optimization and troubleshooting.

What is an Oracle SQL ID?

An SQL ID is a unique identifier assigned to an SQL statement when it is first parsed and stored in the library cache. It remains constant for that particular SQL text, helping DBAs identify and analyze the query’s execution behavior over time.

Why is SQL ID Important in Performance Tuning?

SQL IDs are vital for performance troubleshooting because they allow DBAs to:

• Locate specific queries in dynamic performance views.
• Analyze execution plans to identify inefficiencies.
• Monitor query performance using AWR (Automatic Workload Repository) and SQL Monitoring reports.
• Compare query performance over different executions.

Identifying Performance Issues Using SQL ID

Once you have an SQL ID, you can investigate performance issues using several approaches:

1. Retrieve Execution Plan

Using the DBMS_XPLAN.DISPLAY_CURSOR function, you can analyze the execution plan to identify inefficiencies such as full table scans, missing indexes, or suboptimal join methods.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<Your SQL ID>', 0, 'ALL'));

2. Query SQL History from AWR

To see historical performance data related to an SQL ID, use:

SELECT * FROM dba_hist_sqlstat WHERE sql_id = <Your SQL ID>
;

This helps determine if performance degradation is recent or has been ongoing.

3. Check Active Sessions

If an SQL statement is currently running and causing performance issues, use:

SELECT sql_id, status, username, sql_text FROM v$session WHERE sql_id = <Your SQL ID>
;

This provides information on users executing the query and its current status.

Common Causes of SQL Performance Issues

1. Full Table Scans – Ensure proper indexing to avoid unnecessary full table scans.
2. Inefficient Joins – Optimize joins by using appropriate indexes and execution strategies.
3. Outdated Statistics – Regularly update optimizer statistics using:

   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('your_schema');
   

4. High Parsing Overhead – Use bind variables to reduce excessive hard parsing.
5. Blocking Sessions – Check for locking issues using V$LOCK and V$SESSION.

Conclusion

SQL ID is a powerful tool for diagnosing and resolving performance issues in Oracle databases. By leveraging execution plans, historical data, and session monitoring, DBAs can pinpoint problematic queries and implement optimizations. Regular performance analysis ensures smooth database operations and improved efficiency.

 

How to Find the SQL ID in Oracle Developer

SQL ID is a unique identifier assigned by Oracle to each SQL statement executed in the database. It is essential for performance tuning, troubleshooting, and analyzing query execution plans. In this blog post, we will explore various methods to find the SQL ID in Oracle Developer and SQL*Plus.

1. Using V$SQL to Find SQL ID

Oracle provides the V$SQL view, which contains details about executed SQL statements, including the SQL ID. The following query retrieves the SQL ID for a specific SQL text:

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%your_query_pattern%';

Replace %your_query_pattern% with a part of your SQL statement to filter the results.

2. Finding SQL ID for a Running Query in V$SESSION

If a query is currently executing, you can find its SQL ID from the V$SESSION view:

SELECT sid, serial#, sql_id, sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE';

This query lists active sessions and their corresponding SQL IDs.

3. Using SQL Developer’s Autotrace or Explain Plan

Steps to Find SQL ID in Oracle SQL Developer:

1. Open Oracle SQL Developer and execute your query.
2. Enable Autotrace:
   • Click on Query Builder > Enable Autotrace.
   • Run your SQL statement.
   • The SQL ID will be displayed in the execution plan output.
3. Alternatively, run:

   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALL'));
   

   This retrieves the SQL ID of the last executed statement in your session.

4. Using AWR to Retrieve SQL ID for Historical Queries

Oracle's Automatic Workload Repository (AWR) stores SQL execution history, which is useful when identifying SQL IDs for past queries:

SELECT sql_id, sql_text, elapsed_time_total
FROM dba_hist_sqlstat
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;

This retrieves the top 10 longest-running SQL queries along with their SQL IDs.

5. Finding SQL ID from an Active Session in ASH

Oracle’s Active Session History (ASH) provides detailed SQL execution information:

SELECT sql_id, session_id, sql_text
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '10' MINUTE;

This helps track recent SQL statements executed in the database.

Conclusion

Knowing how to find the SQL ID in Oracle is crucial for diagnosing performance issues and analyzing execution plans. Whether using V$SQL, V$SESSION, ASH, or AWR reports, you can quickly locate the SQL ID and use it for further tuning or investigation.

Optimizing Long-Running SQL Queries

In this blog post, we will explore various methods to find, analyze, and optimize long-running SQL queries in Oracle.

Indexing for Performance Improvement

Indexes can significantly improve query performance. To check for missing indexes, you can analyze execution plans using the following:

SELECT * FROM v$sql_plan WHERE sql_id = '<SQL_ID>';

If the execution plan shows full table scans where indexes could be useful, consider creating indexes:

CREATE INDEX idx_column_name ON table_name (column_name);

Optimizing Execution Plans

Use EXPLAIN PLAN to understand how Oracle executes queries:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Review the execution plan and ensure the query is utilizing indexes efficiently.

Using Query Hints

SELECT /*+ INDEX(table_name index_name) */ column1, column2 FROM table_name WHERE column1 = 'value';

Partitioning Large Tables

Partitioning large tables can improve query performance by reducing the amount of data scanned:

CREATE TABLE sales_partitioned (

    sale_id NUMBER,

    sale_date DATE,

    amount NUMBER

)

PARTITION BY RANGE (sale_date) (

    PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),

    PARTITION sales_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))

);

 

Identifying Long-Running SQL Queries in Oracle Database

In any Oracle database environment, long-running SQL queries can impact performance, causing bottlenecks, and degrade the overall user experience. 

Identifying and optimizing these SQL queries is crucial for maintaining database efficiency. In this blog post, we will see the various methods to find and analyze long-running SQL queries in Oracle.

1. Using V$SESSION and V$SQL

Oracle provides dynamic performance views such as V$SESSION and V$SQL to monitor active sessions and queries. To find long-running queries, execute the following SQL statement:

SELECT s.sid, s.serial#, s.username, s.status, s.schemaname, s.osuser,
       s.machine, s.program, q.sql_id, q.sql_text, s.logon_time,
       (sysdate - s.logon_time) * 24 * 60 AS minutes_running
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
ORDER BY minutes_running DESC;

This query will help you identify active sessions, their corresponding SQL queries, and how long they have been running.

2. Using V$SESSION_LONGOPS

For queries that take a significant amount of time, Oracle tracks their progress in V$SESSION_LONGOPS. The following SQL query helps you to monitor long-running operations:

SELECT sid, serial#, opname, target, sofar, totalwork,
       ROUND(sofar/totalwork*100,2) AS percent_done, elapsed_seconds
FROM v$session_longops
WHERE totalwork > 0 AND sofar <> totalwork
ORDER BY elapsed_seconds DESC;

This view provides insights into the execution progress of long-running queries.

3. Using Active Session History (ASH)

Oracle Active Session History (ASH) collects sampled session activity, which can be used to analyze performance bottlenecks. The following query retrieves details on long-running queries from ASH:

SELECT sql_id, session_id, session_serial#, event, wait_class,
       sample_time, time_waited
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '10' MINUTE
ORDER BY sample_time DESC;

ASH provides granular insights into SQL execution patterns over a specific time frame.

4. Identifying Blocking Sessions

Long-running queries might be blocked by other sessions, leading to performance degradation. To identify blocking sessions, use the following query:

SELECT blocking_session, sid, serial#, wait_class, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

This will help you in diagnosing contention issues within the database.

5. Checking AWR Reports for SQL Performance

Oracle’s Automatic Workload Repository (AWR) captures detailed performance statistics. The following query lists the top 10 longest-running SQL queries from AWR:

SELECT sql_id, elapsed_time_total, executions,
       elapsed_time_total/executions AS avg_elapsed_time
FROM dba_hist_sqlstat
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;

AWR provides historical performance data, enabling better tuning and optimization.

Conclusion

Finding long-running SQL queries in Oracle is essential for performance tuning and system health. By leveraging views like V$SESSION, V$SESSION_LONGOPS, V$ACTIVE_SESSION_HISTORY, and DBA_HIST_SQLSTAT, you can proactively monitor, analyze, and optimize queries to improve overall database performance. 

Regularly analysis and tuning can significantly reduce query execution times and enhance user experience.