How to limit execution rate for particular query

While investigation high system load after new application release I found that group of daemons were checking one table for new rows… thousands times per second. 4 cores were busy executing the same query again and again. Following temporary solution was implemented to solve the problem without application reconfiguration and restart.

--setup test user with DBA role. just don't want to bother with permissions

create user testrw identified by rw default tablespace users;

grant dba to testrw;

grant execute on dbms_lock to testrw;

grant execute on DBMS_ADVANCED_REWRITE  to testrw;

conn testrw/rw

DROP TABLE t1;

DROP VIEW v1;

-- test table that represents our application table

CREATE TABLE t1 (c1 VARCHAR2 (10));

--sleep function will be used for rewrite

CREATE OR REPLACE FUNCTION do_sleep (n IN NUMBER)

RETURN NUMBER AS

BEGIN

DBMS_LOCK.sleep (n);

RETURN 1;

END;

/

show errors

--rewritten queries will use this view

CREATE OR REPLACE VIEW v1 AS

  SELECT *

    FROM t1

   WHERE do_sleep (2) = 1;

--setup rewrite

BEGIN

  BEGIN

    SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence ('t1_to_v1_rewrite');

  EXCEPTION

    WHEN OTHERS THEN

      NULL;

  END;

  SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (NAME               => 't1_to_v1_rewrite',

                                                         source_stmt        => 'SELECT c1 FROM t1',

                                                         destination_stmt   => 'SELECT c1 FROM v1',

                                                         VALIDATE           => FALSE,

                                                         rewrite_mode       => 'GENERAL');

END;

/

All looks fine and supposed to work but it doesn’t!

SQL> SELECT c1 from t1;

no rows selected

Elapsed: 00:00:00.00

It is expected and we have to switch from default query_rewrite_integrity to trusted.

SQL> alter session set query_rewrite_integrity = trusted;

Session altered.

Elapsed: 00:00:00.00

SQL> SELECT c1 from t1;

no rows selected

Elapsed: 00:00:02.01

The problem is that it works the same way for all where clauses:

SQL> variable b1 varchar2;

SQL> exec :b1:='C';

SQL> select * from t1 where c1=:b1;

no rows selected

Elapsed: 00:00:02.00

SQL>  select * from t1 where c1!=:b1;

no rows selected

Elapsed: 00:00:02.00

It is slightly not what we wanted before, our task was limit the rate for *particular* query.

alter session set query_rewrite_integrity = enforced;

DECLARE

  sqlt  VARCHAR2 (32000);

BEGIN

  BEGIN

    DBMS_SQLTUNE.drop_sql_profile ('sp_rewrite_t1_to_v1');

  EXCEPTION

    WHEN OTHERS THEN

      NULL;

  END;

  sqlt := 'SELECT c1 FROM t1 WHERE c1 != :b1';

  DBMS_SQLTUNE.import_sql_profile (NAME => 'sp_rewrite_t1_to_v1',

   sql_text      => sqlt,

   force_match   => TRUE,

   PROFILE       => sqlprof_attr ('OPT_PARAM(''query_rewrite_integrity'' ''TRUSTED'')'));

END;

/

And final tests:

--two seconds as expected

SQL>  SELECT c1 FROM t1 WHERE c1 != :b1;

no rows selected

Elapsed: 00:00:02.00

-- query was not rewritten

SQL>  SELECT c1 FROM t1 WHERE c1 = :b1;

no rows selected

Elapsed: 00:00:00.00

Problem has been resolved. The only limitation is to stay on default value of query_rewrite_integrity and additional license on “Tuning Pack”.

Note 392214.1 was used as the reference.