How to Add Conditions After a Where Clause

I went on an Oracle 9i DBA Performance and Tuning course in 2005 and was looking through the course notes recently to find something to blog about. They suggested that if you had conditions after a WHERE clause joined by AND, you should put the test which was most likely to fail first. This would then save Oracle the bother of evaluating the subsequent condition(s). This seemed reasonable so I decided to try it out.

I copied the contents of DBA_TABLES into a table of my own called T1 and duplicated its contents repeatedly until it had over three million rows. Then I checked that every row had TABLE_LOCK set to ENABLED and that no rows had an owner called BLAH.

SQL> create table t1 as select * from dba_tables

  2  /

 

Table created.

 

SQL> begin

  2  for a in 1..11 loop

  3  insert into t1 select * from t1;

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> select count(*) from t1

  2  /

 

  COUNT(*)

----------

   3217408

 

SQL> select count(*) from t1 where table_lock = 'ENABLED'

  2  /

 

  COUNT(*)

----------

   3217408

 

SQL> select count(*) from t1 where owner = 'BLAH'

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

Then I ran the first SELECT statement as follows. If the course notes were correct, the first condition should reject every row and the second condition should never be evaluated:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where owner = 'BLAH'

  3  and table_lock = 'ENABLED'

  4  /

 

  COUNT(*)

----------

         0

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     13.83

 

SQL>

Then I ran the second SELECT statement shown below. It was identical to the first but the conditions were swapped round. If the course notes were correct, the first condition should accept each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times and got similar results each time. So far so good:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where table_lock = 'ENABLED'

  3  and owner = 'BLAH'

  4  /

 

  COUNT(*)

----------

         0

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     14.27

 

SQL>

The course notes also suggested that if you had conditions after a WHERE clause joined by OR, you should put the test which was most likely to succeed first. This would then save Oracle the bother of evaluating the subsequent condition(s). I decided to try this out too, using the table T1, which I created above.

I ran the third SELECT statement like this. If the course notes were correct, the first condition should accept every row and the second condition should never be evaluated: 

SQL> alter system flush shared_pool

  2  / 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where table_lock = 'ENABLED'

  3  or owner = 'BLAH'

  4  /

  COUNT(*)

----------

   3217408

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  / 

  CPU Used

----------

     14.17 

SQL>

Finally, I ran the fourth SELECT statement. It was identical to the third but the conditions were swapped round. If the course notes were correct, the first condition should reject each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times as well and got similar results each time. I think this demonstrated that the course notes were correct:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where owner = 'BLAH'

  3  or table_lock = 'ENABLED'

  4  /

 

  COUNT(*)

----------

   3217408

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     14.79

 

SQL>

ORA-02393

This example, which was tested on Oracle 11.2.0.2.7, shows how you can limit the amount of CPU time used by an SQL statement. Before you start, you need to ensure that RESOURCE_LIMIT  is set to TRUE otherwise limits will not be enforced:

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

Then you need to create a profile which will limit CPU per statement to 1 second:

SQL> create profile for_andrew

  2  limit cpu_per_call 100

  3  /

 

Profile created.

 

SQL>

Next you need to create a user who will be assigned this profile and connect to the database with it:

SQL> create user andrew identified by reid

  2  profile for_andrew

  3  /

 

User created.

 

SQL> grant create session,

  2        alter session,

  3        select any dictionary to andrew

  4  /

 

Grant succeeded.

SQL> conn andrew/reid

Connected.

SQL>

After this, you need to run a SQL statement which will use more than 1 second of CPU time. Before doing this, check the session's CPU usage so far:

SQL> select a.value/100

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and b.name = 'CPU used by this session'

  5  /

 

A.VALUE/100

-----------

        .02

 

SQL>

... then execute the statement and wait for it to fail:

SQL> select count(*)

  2  from dba_tables a, dba_tables b

  3  /

from dba_tables a, dba_tables b

     *

ERROR at line 2:

ORA-02393: exceeded call limit on CPU usage

 

SQL>

Once the statement has failed, check that it has only increased the session's CPU usage by 1 second:

SQL> select a.value/100

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and b.name = 'CPU used by this session'

  5  /

 

A.VALUE/100

-----------

       1.34

 

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>

V$MYSTAT

Tested on Oracle 11.2. This view stores information for the current session:

 

SQL> desc v$mystat

Name                       Null?    Type

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

SID                                 NUMBER

STATISTIC#                          NUMBER

VALUE                               NUMBER

 

SQL>

 

Although there is a column called SID in the view, it only has 1 value, the SID for the current session:

 

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

       394

 

SQL>

 

If you join it with v$statname, you can pick out individual statistics like this:

 

SQL> select value/100 CPU_used

  2  from v$mystat a, v$statname b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU_USED

----------

       .03

 

SQL>

 

An ideal way to use lots of CPU time is to have a join without a join condition:

 

SQL> set timing on

SQL> select count(*)

  2  from dba_tables a, dba_tables b

  3  /

 

  COUNT(*)

----------

   9375844

 

Elapsed: 00:03:29.23

SQL> set timing off

 

Now that the SQL has finished, you can see that the elapsed time was 209 seconds and the CPU time used was 144 seconds:

 

SQL> select value/100 CPU_used

  2  from v$mystat a, v$statname b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU_USED

----------

    144.23

 

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>