Oracle Tuning - Kill Long running Sessions

While killing long-running sessions can sometimes be a temporary solution to performance issues, it should be used with caution as it can lead to data loss or corruption. Before killing a long-running session, it is important to understand the cause of the issue and take appropriate steps to address it.

Identifying Long-Running Sessions :
There are several methods to identify long-running sessions in Oracle:
  1. V$SESSION View : This view provides information about all active sessions, including their session IDs, elapsed time, and current activity. You can query this view to identify sessions that have been running for an extended period.
  2. V$SESSION_WAIT View : This view provides information about sessions that are currently waiting for resources, such as locks or I/O. You can query this view to identify sessions that may be holding up other sessions.
  3. V$SQL View : This view provides information about currently executing SQL statements, including their session IDs, execution time, and resource consumption. You can query this view to identify sessions that are executing long-running queries.
  4. Performance Monitoring Tools : Various performance monitoring tools, such as Oracle Enterprise Manager or STATSPACK, can provide information about long-running sessions and their associated queries.
HERE SAMPLE EXAMPLE :
WE TAKE THREE TERMINAL
  1. DATABSAE LEVEL
  2. OS LEVEL
  3. DATABASE LEVEL

STEP 1 : Create Two Tables & Add Values : 
    create table tesdb1(id int,name varchar2(20));
    create table tesdb2(id int,name varchar2(20));

    begin
        for i in  1 .. 10000 loop
    insert into tesdb1 values(i,i);
    end loop;
    commit;
    end;
    /
    PL/SQL procedure successfully completed.


    begin
    for i  in 1..10000 loop
    insert into tesdb2 values(i,i);
    end loop;
    commit;
    end;
    /

    SQL> commit;

    Commit complete.

    SQL> select count(*) from tesdb1;

    COUNT(*)
    ----------
        10000

    SQL> select count(*) from tesdb2;

    COUNT(*)
    ----------
        10000
STEP 2 : Select The Table Using Join : 
    SQL> select a.name,b.name from tesdb1 a inner join tesdb2 b on a.id<>b.id;
Open another terminal check the top command
Top kill-long-run-1

    SQL> select addr from v$process where spid=8813;
    
        ADDR
    ----------------
    0000000065D653B8


    SQL> select sql_id from v$session where paddr='0000000065D653B8';

    SQL_ID
    -------------
    131n5bxcwmkgp 


    SQL> select sql_text from v$sql where sql_id='131n5bxcwmkgp';

    SQL_TEXT
    --------------------------------------------------------------------------------
    select a.name,b.name from tesdb1 a inner join tesdb2 b on a.id<>b.id


    SQL> SELECT sid, serial#, sql_id, status from v$session where sql_id='131n5bxcwmkgp';

    SID    SERIAL# SQL_ID	    STATUS
    ---------- ---------- ------------- --------
    237	31657 131n5bxcwmkgp INACTIVE
Kill the Session :
Once you have identified the session to be terminated, use the ALTER SYSTEM KILL SESSION statement. Replace sid and serial# with the values from the identified session. 
    SQL> ALTER SYSTEM KILL SESSION '237,31657';

    System altered.

    SQL>
The following error will come in first terminal :
ERROR:
ORA-00028: your session has been killed
27848235 rows selected.

Verify Termination :
After executing the ALTER SYSTEM KILL SESSION statement, you can check whether the session has been successfully terminated by querying the v$session view.


(Oracle Tuning - Improve performance on DMLs)