Long running queries and table locks : Killing such sessions or taking the plans and tuning them

Long running queries are a very common and unavoidable part of database activities. These queries may be for some high volume business data processing, report extractions, upload processes etc. These queries sometimes take up a lot of the db resources in terms of cpu, elapses time etc and in the bargain slow down other application processes running at the same time. Sometimes it becomes imperative to kill these long running queries to reduce the load on the database to facilitate smooth running of the applications accessing this database. These long running queries which are not critical can then be rescheduled to run during non peak hours.
Or you may want to take the explain plans and tune these queries to run at optimum performance. Sometimes a simple index creation on a table results in a drastic improvement in the performance of a query.

Below are some queries that can be used to find out such long running queries on the database.

SELECT osuser,
       sl.sql_id,
       sl.sql_hash_value,
       opname,
       target,
       elapsed_seconds,
       time_remaining
  FROM v$session_longops sl
inner join v$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL#
WHERE time_remaining > 0;

SELECT s.username,
       sl.sid,
    s.serial#,
       sq.executions,
       sl.last_update_time,
       sl.sql_id,
       sl.sql_hash_value,
       opname,
       target,
       elapsed_seconds,
       time_remaining,
       sq.sql_fulltext
  FROM v$session_longops sl
 INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
 INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
 WHERE time_remaining > 0;

Once the problem query is identified and you want to kill the particular session from which the query is running you can kill the session as follows

ALTER SYSTEM KILL SESSION 'sid,serial#';

If you want to tune the query, you must first get the explain plan for the query with the sql_id obtained as below.

select * from table(dbms_xplan.display_awr('sql_id'));
select * from table(dbms_xplan.display_cursor('sql_id'));

With the plan thus obtained you can then tune the query by creating the necessary indexes, restructuring the query to run more efficiently or using the appropriate hints.

Below is a query to give high cost queries in the database higher than a give value. This value you can determine based on the load on the database.

select
  p.sql_id            c1,
  p.cost              c2,
  DBMS_LOB.SUBSTR(s.sql_text,4000,1) c3
from
  dba_hist_sql_plan    p,
  dba_hist_sqltext     s
where
      p.id = 0
  and
      p.sql_id = s.sql_id
  and
      p.cost is not null
  and
      p.cost > '' --7 *1000 *1000*1000
order by
  p.cost desc;

With the sql_id obtained you can get the plan and tune the query as explained above.

Sometimes if a query is taking a long time to execute it may simply be that there are some locking sessions and the table is locked due to which the dml operation of a particular table are slowed down drastically.
To find out if there are locks on any tables on the database you can run the following query

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

If the output of this query shows locks on any table you can kill those sessions as explained above.

DBMS_METADATA.GET_DDL to get DDL script for Oracle objects

To get the DDL script for an oracle object in a schema we use the below oracle command.

SELECT DBMS_METADATA.GET_DDL('TABLE' ,'EMPLOYEES','HR') FROM DUAL;

We can extend this to get the DDL script for all oracle object types in a schema as below

DECLARE
  v_ddl_query VARCHAR2(1000) := 'SELECT DBMS_METADATA.GET_DDL(:object_type ,:table_name,:owner) FROM DUAL';
  v_ddl VARCHAR2(32000);

CURSOR C1
IS
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
  where OBJECT_TYPE IN ('TABLE','FUNCTION','PROCEDURE','PCAKAGE','PACKAGE BODY')
  AND OWNER = 'HR'
  ORDER BY OBJECT_TYPE;

BEGIN
 FOR rec IN C1
 LOOP --{
  EXECUTE IMMEDIATE v_ddl_query INTO v_ddl USING rec.OBJECT_TYPE, rec.OBJECT_NAME, rec.OWNER;
  dbms_output.put_line('DDL script for ' || rec.OBJECT_TYPE || ' ' || rec.OBJECT_NAME || ':'  || v_ddl);
  
 END LOOP; --} 
END;

Thought for the day

Do not meddle in matters that are beyond you 

For more than you can understand has been shown you

Sirach 3:23