Skip to Main Content
  • Questions
  • Limiting query execution based on sql id

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Duc.

Asked: July 03, 2019 - 4:31 pm UTC

Last updated: April 12, 2021 - 5:16 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

Thank you for reviewing my question. Two nodes RAC database version 11.2.0.4

1. Query is generated at the application server
2. Persistent connection to the database using the same account
3. The query is using a lot of IO. When there is more than 40 execution of the same query, the database slowdown dramatically
4. Is there a method to limit/queue/kill query execution based on sqlid?
5. Resource Manager limiting based on IO does not work for us since the application using the same database account and there are other query running with high IO usage

and we said...

19c has all sorts of goodies in this area but there is not an automated solution to handle this in 11g.

You could do something with a script, eg

SQL> select sid, serial#, last_call_et
  2  from   v$session
  3  where  sql_id = '0qht8ubcqk4wr'   -- the "problem" SQL_ID
  4  and    status = 'ACTIVE'
  5  order by 3;

       SID    SERIAL# LAST_CALL_ET
---------- ---------- ------------
       497        744           38
       136      13162           45
       373      28703           52

3 rows selected.


That shows the sessions running the SQL and how long they've been running it for. So then you could do:

SQL> set serverout on
SQL> declare
  2    l_threshold int := 1;
  3    l_cnt       int := 0;
  4  begin
  5   for i in (
  6       select sid, serial#, last_call_et
  7       from   v$session
  8       where  sql_id = '0qht8ubcqk4wr'
  9       and    status = 'ACTIVE'
 10       order by 3
 11   )
 12   loop
 13     l_cnt := l_cnt + 1;
 14     if l_cnt <= l_threshold then
 15       dbms_output.put_line('Leaving SID='||i.sid||' untouched');
 16     else
 17       dbms_output.put_line('Killing SID='||i.sid);
 18       -- execute immediate 'alter system kill session ....';
 19     end if;
 20   end loop;
 21  end;
 22  /
Leaving SID=497 untouched
Killing SID=136
Killing SID=373

PL/SQL procedure successfully completed.


and run that at a frequency that suits your needs

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What 19c goodies?

Jonas, April 08, 2021 - 12:45 pm UTC

What goodies can be utilized to achieve this in 19c?
Connor McDonald
April 12, 2021 - 5:16 am UTC

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.