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>

NOSORT and ORA-01409

When you create an index, Oracle usually does a sort. I read about the NOSORT option recently. This allows Oracle to create an index without doing a sort. I decided to give it a try on an Oracle 9.2.0.7.0 database. First I created a table, counted the number of sorts my session had done, created an index on the table, counted the number of sorts again and saw that it had increased by 1:

 

SQL> create table andrews_table

  2  as select owner, table_name

  3  from dba_tables

  4  /

 

Table created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

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

sorts (memory)               17

sorts (disk)                  0

sorts (rows)              11527

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  /

 

Index created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

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

sorts (memory)               18

sorts (disk)                  0

sorts (rows)              13069

 

SQL>

 

Then I dropped the index and tried to recreate it with the NOSORT option. I expected this to fail as the table was not ordered on the indexed column:

 

SQL> drop index andrews_index

  2  /

 

Index dropped.

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  nosort

  4  /

on andrews_table(table_name)

   *

ERROR at line 2:

ORA-01409: NOSORT option may not be used; rows are not

in ascending order

 

SQL>

 

Finally, I dropped and recreated the table in table_name order, counted the number of sorts my session had done, created an index with the NOSORT option on the sorted table_name column, counted the number of sorts again and saw that it had not increased:

 

SQL> drop table andrews_table

  2  /

 

Table dropped.

 

SQL> create table andrews_table

  2  as select owner, table_name

  3  from dba_tables

  4  order by table_name

  5  /

 

Table created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

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

sorts (memory)               35

sorts (disk)                  0

sorts (rows)              26085

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  nosort

  4  /

 

Index created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

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

sorts (memory)               35

sorts (disk)                  0

sorts (rows)              26085

 

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>

V$SYSTEM_EVENT

This was tested on Oracle 11.2. V$SYSTEM_EVENT shows what an instance has been waiting for since it was last started. You can see the top 10 events (by total time waited) as follows. The times are in hundredths of a second:

SQL> l

  1  select * from

  2  (select event, time_waited

  3   from v$system_event

  4   where wait_class != 'Idle'

  5   order by 2 desc)

  6* where rownum <= 10

SQL> /

 

EVENT                               TIME_WAITED

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

control file sequential read             992637

db file sequential read                  569680

control file parallel write              543468

os thread startup                        515992

log file parallel write                  499896

db file parallel write                   426726

db file scattered read                   202302

log file sync                            159840

Disk file operations I/O                  45765

ADR block file read                       29553

 

10 rows selected.

 

SQL>

 

If you include Idle events, you see the wait events where the database wasn’t doing anything. The SQL*Net message from client event, for example, records how long the database spent waiting for its next instruction. This may not be what you want to see:

SQL> l

  1  select * from

  2  (select event, time_waited

  3   from v$system_event

  4   order by 2 desc)

  5* where rownum <= 10

SQL> /

 

EVENT                                    TIME_WAITED

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

rdbms ipc message                         1956293654

SQL*Net message from client                637129764

DIAG idle wait                             326433989

dispatcher timer                           163322456

shared server idle wait                    163320698

Streams AQ: qmn coordinator idle wait      163318553

Streams AQ: qmn slave idle wait            163315578

smon timer                                 163264804

pmon timer                                 163252749

Space Manager: slave idle wait             163171812

 

10 rows selected.

 

SQL>

 

The TIME_WAITED for SQL*Net message from client works out at almost 74 days. However, the database has been open for less than 20 days:

 

SQL> l

  1  select startup_time, sysdate

  2* from v$instance, dual

SQL> /

 

STARTUP_TIME SYSDATE

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

04-MAY-12    23-MAY-12

 

SQL>

 

That’s because the TIME_WAITED values are the sum of all the TIME_WAITED values for all the sessions which have logged in since the database was opened.

 

If you combine these figures with the CPU time used, you start to get an idea of what your database has been doing:

 

SQL> l

  1  select a.name, b.value

  2  from v$statname a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4* and a.name = 'CPU used by this session'

SQL> /

 

NAME                                     VALUE

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

CPU used by this session               1854863

 

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>