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>

Subquery with Order By?

This was tested on Oracle 11.1.0.6.0 running on Windows XP. Looking through some course notes from 1990 (as you do), I read that you cannot include an order by in a subquery. I'm not sure why you would ever want to do such a thing but I decided to see what happened if you did:

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5   from dba_indexes
  6*  order by 1)
SQL> /
 order by 1)
 *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5   from dba_indexes
  6*  order by table_name)
SQL> /
 order by table_name)
 *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL>

It gave me an ORA-00907, as you can see above, which was not especially helpful. I removed the order by and the query ran successfully:

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5*  from dba_indexes)
SQL> /

  COUNT(*)
----------
       444

SQL>

However, I have just thought of a special kind of subquery, sometimes called an in-line view, which is allowed to have an order by. Here is an example, suggested by Laurent in the 1st comment below:

SQL> l

  1  select * from

  2  (select sid, a.value/100 CPU_Seconds

  3   from v$sesstat a, v$sysstat b

  4   where a.statistic# = b.statistic#

  5   and name = 'CPU used by this session'

  6   order by a.value desc)

  7* where rownum < 6

SQL> /

       SID CPU_SECONDS

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

        71      401.64

       140      361.04

        10      306.59

         9         306

        43      280.75

SQL>

Hit Ratio by Session

This was tested on Oracle 11.2. I recently did a post showing how you could calculate your database's hit ratio. If you need to see the hit ratio broken down by session, you can do it like this:

SQL> select a.sid, trunc

  2  ((1-(physical_reads/(db_block_gets+consistent_gets)))*100,1)||'%'

  3  hit_ratio from

  4  (select sid, ses.value physical_reads

  5   from v$sesstat ses, v$sysstat sys

  6   where ses.statistic# = sys.statistic#

  7   and name = 'physical reads') a,

  8  (select sid, ses.value db_block_gets

  9   from v$sesstat ses, v$sysstat sys

 10   where ses.statistic# = sys.statistic#

 11   and name = 'db block gets') b,

 12  (select sid, ses.value consistent_gets

 13   from v$sesstat ses, v$sysstat sys

 14   where ses.statistic# = sys.statistic#

 15   and name = 'consistent gets') c

 16  where a.sid = b.sid

 17    and b.sid = c.sid

 18    and db_block_gets + consistent_gets <> 0

 19  order by sid

 20  /

       SID HIT_RATIO

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

         2 99.6%

         4 99.9%

         5 99.6%

         6 99.7%

        25 99.5%

        47 100%

        50 99.9%

        51 99.6%

        67 99.5%

        70 100%

        71 100%

        91 98.3%

        92 99.9%

        93 99.4%

       112 95.6%

       113 98.8%

       115 99.3%

       116 99.5%

       133 94.4%

       134 97.7%

       137 98.6%

       139 100%

       140 99.7%

       156 99.2%

       159 99.8%

       160 99.1%

26 rows selected.

SQL>

DB time from V$SESSTAT

This was tested on Oracle 11.2. The DB time entry in V$MYSTAT (or V$SESSTAT) just seems to record the amount of time a session spends doing something in the database, irrespective of the amount of CPU time used. First reconnect to the database to zeroise the figures:

SQL> conn /

Connected.

SQL> select name, m.value/100

  2  from v$mystat m, v$sysstat s

  3  where m.statistic# = s.statistic#

  4  and name in

  5  ('DB time',

  6   'CPU used by this session')

  7  /

NAME                                M.VALUE/100

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

CPU used by this session                    .01

DB time                                       0

SQL>

If you sleep in the OS for a few seconds:

SQL> host sleep 5

... this does not increment DB time or CPU time by much:

SQL> select name, m.value/100

  2  from v$mystat m, v$sysstat s

  3  where m.statistic# = s.statistic#

  4  and name in

  5  ('DB time',

  6   'CPU used by this session')

  7  /

NAME                                M.VALUE/100

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

CPU used by this session                    .02

DB time                                     .02

SQL>

However, if you sleep in the database:

SQL> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

SQL>

... CPU time hardly changes but DB time goes up by the full 5 seconds:

SQL> select name, m.value/100

  2  from v$mystat m, v$sysstat s

  3  where m.statistic# = s.statistic#

  4  and name in

  5  ('DB time',

  6   'CPU used by this session')

  7  /

NAME                                M.VALUE/100

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

CPU used by this session                    .04

DB time                                    5.05

SQL>

CPU Usage

I tested this on Oracle 11.2. You need to be careful when looking at Oracle statistics on CPU usage. Here is an idle session (in red):

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

         6

 

SQL>

 

Here is its CPU usage as seen from another session (in blue). The CPU used when call started and CPU used by this session figures are in hundredths of a second. The DB CPU figure is in microseconds:

 

SQL> select

  2  (select value from v$sesstat a, v$statname b

  3   where sid = 6

  4   and a.statistic# = b.statistic#

  5   and name = 'CPU used when call started') as CUWCS,

  6  (select value from v$sesstat a, v$statname b

  7   where sid = 6

  8   and a.statistic# = b.statistic#

  9   and name = 'CPU used by this session') as CUBTS,

10  (select value

11   from v$sess_time_model

12   where sid = 6

13   and stat_name = 'DB CPU') as "DB CPU"

14  from dual

15  /

 

     CUWCS      CUBTS     DB CPU

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

     41669      41669  416810000

 

SQL>

 

The first session runs a query:

 

SQL> set timing on

SQL> select count(*) from dba_tables a, dba_tables b

  2  /

 

While this is going on, the second session monitors it every 30 seconds The  CPU used when call started figure stays the same while the SQL is running. This is reasonable. The CPU used by this session figure goes up a little then stays the same. It is not clear what this is measuring at all. The DB CPU figure appears to be updated regularly:

 

SQL> /

 

     CUWCS      CUBTS     DB CPU

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

     41669      41752  441270000

 

SQL> /

 

     CUWCS      CUBTS     DB CPU

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

     41669      41752  470980000

 

SQL> /

 

     CUWCS      CUBTS     DB CPU

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

     41669      41752  494630000

 

SQL> /

 

     CUWCS      CUBTS     DB CPU

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

     41669      41752  522780000

 

SQL>

 

The first session’s query finishes:

 

SQL> select count(*) from dba_tables a, dba_tables b

  2  /

 

  COUNT(*)

----------

   9369721

 

Elapsed: 00:02:25.93

SQL>

 

Then the figures in the second session match again:

 

SQL> /

 

     CUWCS      CUBTS     DB CPU

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

     54891      54891  549030000

 

SQL>

Answer to Q3 from Vijay

To find the user who is taking up most CPU time, you need to look in V$SYSSTAT to get the number of the statistic in question e.g.

 

SQL> select statistic# from v$sysstat

  2  where name = 'CPU used by this session';

 

STATISTIC#

----------

        12

 

SQL>

 

The above step is important as the numbers have a habit of changing from one Oracle version to the next. Then you can get the SIDs of the sessions that have used most CPU since they logged in e.g.

 

  1  select * from

  2  (select sid, value/100 from v$sesstat

  3   where statistic# = 12

  4   order by 2 desc)

  5* where rownum <=10

SQL> /

 

       SID  VALUE/100

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

        22     379.49

        15     310.84

        13     301.48

        33     287.93

        53     247.32

        79     176.91

        56     166.92

       153     161.46

        96     141.22

        36     136.64

 

10 rows selected.

 

SQL>

 

The values are in hundredths of a second so dividing them by 100 gives the results in seconds. If you want to get figures for users who are currently using most CPU, you will need to repeat the query after a while and just look at the users whose CPU usage has increased in the meantime. Once you have decided which SID you want to investigate you can find out who it is as follows:

 

  1  select sid, username from v$session

  2* where sid in (13, 15, 22)

SQL> /

 

       SID USERNAME

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

        13 ANDREW

        15 VIJAY

        22 FRED

 

SQL>