Wednesday, October 21, 2015

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 2nd 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 1st SQL worked but the 2nd 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>

No comments: