LOGICAL_READS_PER_SESSION

One of the resources you can limit in a profile is called logical_reads_per_session. According to the Oracle documentation, it is used to:

Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. I decided to try it out in an Oracle 11.1 database.

 

I created a profile called for_andrew, which would limit logical_reads_per_session to 10000:

SQL> conn / as sysdba

Connected.

SQL> create profile for_andrew

  2  limit logical_reads_per_session 10000

  3  /

 

Profile created.

 

SQL>

I set resource_limit to true so that Oracle would enforce the logical_reads_per_session limit:

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

I created a user with the new profile:

SQL> create user andrew

  2  identified by reid

  3  profile for_andrew

  4  /

 

User created.

 

SQL> grant create session,

  2  select any dictionary to andrew

  3  /

 

Grant succeeded.

 

SQL>

I logged in with this user, ran some SQL against dba_tables and counted the session logical reads this had consumed. For the purposes of this simple demonstration, I ignored any overhead which the 2^nd piece of SQL may have incurred:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_tables

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select value from v$sesstat a, v$statname b

  2  where sid =

  3  (select distinct sid from v$mystat)

  4  and a.statistic# = b.statistic#

  5  and name = 'session logical reads'

  6  /

 

       VALUE

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

        6936

 

SQL>

I logged in again ran some SQL against dba_indexes and counted the session logical reads this had consumed. Again, I ignored any overhead which may have been incurred by checking the session logical reads figure in v$sesstat:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_indexes

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select value from v$sesstat a, v$statname b

  2  where sid =

  3  (select distinct sid from v$mystat)

  4  and a.statistic# = b.statistic#

  5  and name = 'session logical reads'

  6  /

 

       VALUE

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

        7341

 

SQL>

The 2 statements together used over 14000 session logical reads. I guessed that if I tried to run them in the same session, this would exceed the limit set by the logical_reads_per_session parameter in the user’s profile. I logged in again and tried to do this. As expected, the 1^st SQL worked but the 2^nd failed with an ORA-02394:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_tables

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select max(last_analyzed) from dba_indexes

  2  /

select max(last_analyzed) from dba_indexes

                               *

ERROR at line 1:

ORA-02394: exceeded session limit on IO usage, you are

being logged off

 

SQL>