SSD (EFD) devices for Oracle Redo

 

I performed one more testing of this subject and found that results are quite interesting. RAID5 7+1 on High-End disk system was used as dedicated device for redo log files (RH Linux 5.5, redo filesystem formatted to ext2, Oracle 11.2.0.3)

Load generator script is simple and does commit after each (!) insert.

create table &1 (id number, v varchar2(2000)) tablespace users;

BEGIN

  FOR i IN 1 .. 100000000 LOOP

    IF MOD (i, 10000) = 0 THEN

      EXECUTE IMMEDIATE 'truncate table &1 drop storage';

    END IF;

    INSERT INTO &1

    VALUES (

             i,

             '111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111');

    COMMIT;

  END LOOP;

END;

/

I got roughly 43MBS of redo with 2 concurrent processes running.  await is amazing ~0.55msec! All writes are served by the cache of the storage array. All redo related wait events are negligible.

Device:         rrqm/s   wrqm/s   r/s   w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util

dm-12             0.00     0.00  0.00 1675.80     0.00    52.79    64.52     0.90    0.54   0.53  89.19

dm-12             0.00     0.00  0.00 1835.00     0.00    54.10    60.38     0.94    0.51   0.51  92.69

dm-12             0.00     0.00  0.00 1619.60     0.00    52.02    65.78     0.93    0.57   0.57  92.02

dm-12             0.00     0.00  0.00 1645.40     0.00    50.96    63.43     0.94    0.57   0.56  92.72

dm-12             0.00     0.00  0.00 1408.79     0.00    49.82    72.42     0.93    0.66   0.64  90.36

...

Load Profile              Per Second    Per Transaction   Per Exec   Per Call

~~~~~~~~~~~~         ---------------    --------------- ---------- ----------

       Redo size:       43,526,576.9            2,291.7

   Logical reads:          117,138.2                6.2

   Block changes:          124,364.1                6.6

  Physical reads:                2.1                0.0

 Physical writes:            9,053.5                0.5

        Executes:           19,003.3                1.0

    Transactions:           18,993.4

...

Top 5 Timed Foreground Events

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                                           Avg                 

                                                          wait   % DB          

Event                                 Waits     Time(s)   (ms)   time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

DB CPU                                              188          99.4          

log buffer space                         22           1     34     .4 Configurat

Data file init write                    516           0      0     .1 User I/O 

log file switch completion               20           0      4     .0 Configurat

log file sync                             2           0     39     .0 Commit   

7 parallel processes produce 86MB of redo per second. avgrq-sz rose by 10 times, hense await  rose to 5msec as well. I have no idea why :(
Redo waits now ~35% of total waits.

Device:         rrqm/s   wrqm/s   r/s   w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util

dm-12             0.00     0.00  0.00 325.90     0.00   103.96   653.29     1.77    5.44   2.81  91.57

dm-12             0.00     0.00  0.00 365.70     0.00   108.86   609.61     1.82    4.97   2.48  90.77

dm-12             0.00     0.00  0.00 343.80     0.00   107.91   642.84     1.77    5.14   2.67  91.80

dm-12             0.00     0.00  0.00 370.03     0.00   119.00   658.65     1.76    4.76   2.44  90.39

dm-12             0.00     0.00  0.00 362.60     0.00   113.42   640.62     1.76    4.86   2.50  90.48

Load Profile              Per Second    Per Transaction   Per Exec   Per Call

~~~~~~~~~~~~         ---------------    --------------- ---------- ----------

       Redo size:       86,854,088.7            2,312.4

   Logical reads:          243,571.1                6.5

   Block changes:          251,819.1                6.7

  Physical reads:               40.0                0.0

 Physical writes:           20,626.0                0.6

        Executes:           37,959.4                1.0

    Transactions:           37,560.4

    

....

Top 5 Timed Foreground Events

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                                           Avg                 

                                                          wait   % DB          

Event                                 Waits     Time(s)   (ms)   time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

DB CPU                                            2,053          58.6          

log buffer space                     36,487         858     24   24.5 Configurat

local write wait                      4,956         343     69    9.8 User I/O 

enq: RO - fast object reuse           3,140         138     44    3.9 Applicatio

enq: HW - contention                 16,982          85      5    2.4 Configurat

Conclusion: we don’t need SSD devices for redo on our storage array but entry level arrays and standalone servers should be tested.

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.