SQL*Plus headsep Command

This was tested on Oracle 11.2. You can use headsep to tell Oracle when to continue a ttitle on a new line. With the following command, Oracle will continue a ttitle on a new line when it encounters an exclamation mark:

SQL> set headsep !

Set the pagesize small so that you don't need too much output to see headsep working:

SQL> set pages 10

Set the ttitle. This time do not include an exclamation mark, so the title will appear on 1 line:

SQL> ttitle 'Owners, Segments and Sizes'

Now see it working:

SQL> select owner, segment_name, bytes

  2  from dba_segments

  3  where rownum < 10

  4  /

Tue Sep 04                                    page    1

              Owners, Segments and Sizes

OWNER                SEGMENT_NAME              BYTES

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

SYS                  CON$                     393216

SYS                  BOOTSTRAP$                65536

SYS                  PROXY_DATA$               65536

SYS                  PROXY_ROLE_DATA$          65536

Tue Sep 04                                    page    2

              Owners, Segments and Sizes

OWNER                SEGMENT_NAME              BYTES

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

SYS                  OBJ$                    8388608

SYS                  FILE$                     65536

SYS                  UNDO$                     65536

SYS                  OBJERROR$                 65536

Tue Sep 04                                    page    3

              Owners, Segments and Sizes

OWNER                SEGMENT_NAME              BYTES

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

SYS                  OBJAUTH$                 917504

9 rows selected.

SQL>

Next, reissue the ttitle command but include an exclamation mark where you want the line break to appear:

SQL> ttitle 'Owners, Segments!and Sizes'

SQL>

Finally, rerun the SQL and see how the title is split over 2 lines:

SQL> select owner, segment_name, bytes

  2  from dba_segments

  3  where rownum < 10

  4  /

Tue Sep 04                                    page    1

                   Owners, Segments

                       and Sizes

OWNER                SEGMENT_NAME              BYTES

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

SYS                  CON$                     393216

SYS                  BOOTSTRAP$                65536

SYS                  PROXY_DATA$               65536

Tue Sep 04                                    page    2

                   Owners, Segments

                       and Sizes

OWNER                SEGMENT_NAME              BYTES

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

SYS                  PROXY_ROLE_DATA$          65536

SYS                  OBJ$                    8388608

SYS                  FILE$                     65536

Tue Sep 04                                    page    3

                   Owners, Segments

                       and Sizes

OWNER                SEGMENT_NAME              BYTES

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

SYS                  UNDO$                     65536

SYS                  OBJERROR$                 65536

SYS                  OBJAUTH$                 917504

9 rows selected.

SQL>

ROWNUM

Tested on an Oracle 9 database. ROWNUM is a pseudo column which you can include in the output  from a SQL query:

SQL> select rownum, object_id from dba_objects

  2  where rownum < 6

  3  /

    ROWNUM  OBJECT_ID

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

         1         47

         2         19

         3         17

         4         15

         5         45

SQL>

The first rownum is always 1, the second is always 2 and so on. So if you do not have a rownum of 1 at the start, you will not get any output at all. The following query selects the same rows as the one above:

SQL> select rownum, object_id from dba_objects

  2  where rownum between 1 and 5

  3  /

    ROWNUM  OBJECT_ID

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

         1         47

         2         19

         3         17

         4         15

         5         45

SQL>

But the next one does not select rows 2 through 5. It produces no output at all as the first row of output cannot have a rownum of 1:

SQL> select rownum, object_id from dba_objects

  2  where rownum between 2 and 5

  3  /

no rows selected

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>

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>