Previous | Table of Contents | Next

Page 544

NOTE
The monitoring technique here assumes that users are not constantly logging on and off. If users are constantly logging on and off, the session ID assigned by Oracle can change, making it difficult to keep track or report at a session level.n

NOTE
It may not be relevant to execute this script in Listing 22.7 because the busy interval has already passed. But you could use this script to store all the SQL commands the session is executing in a temporary table by periodically polling during the interval.n

Create the temporary table to store the SQL statements executed by the session using script in Listing 22.8.

Listing 22.8 Script to Create SQL Execution History Table stat$session_hash
Create table stat$session_hash
(sid number,
spid varchar2(9),
schemaname varchar2(30),
hash_value number,
time_stamp date,
num_occ    number);

After the table is created, you can run the script in Listing 22.9 to create the procedure sp_stat$sample_hash, which stores all SQLs executed by different sessions during the monitoring interval in the SQL execution history table stat$session_hash.

Listing 22.9 Script to Create Procedure sp_stat$sample_hash
Cursor c_stat$session is
       Select sid,
              spid,
              schemaname,
              sql_hash_value
       From v$session a,
            v$process b
       Where a.paddr = b.addr;

v_stat$session    c_stat$session%RowType;
v_stat$session_hash stat$session_hash%RowType;
e_next                            exception;

Begin

       Open c_stat$session ;

       Loop


Page 545


       Begin

               Fetch c_stat$session
               Into  v_stat$session;

               Begin

                       Select *
                       Into   v_stat$session_hash
                       From   stat$session_hash
                       Where  sid = v_stat$session.sid
                       And    spid = v_stat$session.spid
                       And    hash_value = v_stat$session.sql_hash_value;
                       Insert Into stat$session_hash
                       values
                       (v_stat$session.sid,
                        v_stat$session.spid,
                        v_stat$session.schemaname,
                        v_stat$session.sql_hash_value,
                        sysdate,
                        1
                       );

               Exception
               When No_Data_Found Then
               Update stat$session_hash
               Set num_occ = num_occ + 1
               Where  sid = v_stat$session.sid
               And    spid = v_stat$session.spid
               And    hash_value = v_stat$session.sql_hash_value;

               When Others Then
               Raise e_next;

               End;

       Exception

       When e_next Then
       Null;

       When Others Then
       Null;

       End;

       Commit;

       End Loop;

       Close c_stat$session;

End;

Page 546

This procedure sp_stat$sample_hash can be run intermittently during the monitoring interval. After the begin and end CPU usage information and the SQL execution history is captured, you can generate a report of the heavy CPU users along with the SQL statements that each session was executing during the period of the snapshot. This report can then be used to tune the SQL statements that cause the heavy CPU usage. Listing 22.10 is the script for generating the heavy CPU usage report along with the hash value of the SQL statements executed by the sessions.

Listing 22.10 Script to Produce CPU Usage Report with Hash Value of the SQL Statements Executed During the Monitoring Interval
Break on sid on spid on tot_usg_mins on mins_per_hr

Select e.sid,
  e.spid,
  to_char((e.cpu_usage - b.cpu_usage)/(6000),'9999.99')   tot_usg_mins,
  to_char( ((e.cpu_usage - b.cpu_usage)/(60100))/( (e.time_stamp - 
Âb.time_stamp)*24),'99.99') mins_per_hr, hash_value, num_occ From stat$cpu_end e, stat$cpu_begin b , stat$session_hash c Where b.sid = e.sid And b.spid = e.spid And e.sid = c.sid And e.spid = c.spid order by mins_per_hr; SID SPID Tot Usg Mins HASH_VALUE NUM_OCC In Mins Per Hr ----- ----- ------ ------ ---------- -------- 23 9461 29.04 9.68 -1.447E+09 7 24 9776 25.38 8.46 1739700154 1 -1.306E+09 5 1014890971 1 34 11525 22.41 7.47 573420945 7 35 9459 17.55 5.85 1739700154 2 1561770773 1 35 9459 10.32 3.44 2113573249 1 59 9531 .00 .00 1739700154 6 56 10010 .00 .00 573420945 7 53 11114 .00 .00 -2.056E+09 7 52 10014 .00 .00 1739700154 3 105 9533 .00 .00 573420945 7 86 9529 .00 .00 573420945 7 1907631373 1 73 9527 .00 .00 573420945 7 62 9541 .01 .00 -1.306E+09 5 109 16988 .00 .00 1848020776 7 52 10014 .00 .00 -1.306E+09 1

Page 547

From this output, you can easily determine that sessions 23 and 24 are the top CPU users with a minutes-per-hour CPU usage of 9.68 and 8.46 minutes, respectively. Using the sql_hash_value and the V$SQLTEXT view, the SQL statement that causes such a high amount of CPU to be used can be determined and suitable action can be taken. The column num_occ in Listing 22.10 shows the number of times the SQL statement was executed during the monitoring interval.

NOTE
Most likely, the SQL statements that cause more CPU to be used are full table scans or SQL statements causing more physical reads on the system. When data is required that is not already present in the data block buffer, a miss occurs and the data must be read from the physical device. The miss of data causes the buffer manager to allocate space for data to be read in the buffer cache if free space is not available. The buffer manager will then move blocks of data to the dirty list so that they are subsequently written out to the disk. The processing requirements of the buffer manager make the physical read a CPU-intensive operation as compared with data that is read directly from the cache.n

Using the Operating System Route To find heavy CPU users on UNIX systems, you could use the top command. The top command will list the heaviest CPU users in descending order of wait times. When the top CPU user is found, you can find what the user is actually doing. Listing 22.11 is a sample output of the top command.

Listing 22.11 Sample Output of the top Command
%top
System: arms1                                         Sat Aug 30 16:35:57 1997
Load averages: 0.59, 0.61, 0.69
20 processes: 18 sleeping, 2 running
Cpu states:
CPU  LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS
0    0.56  34.6%   0.0%   1.4%  64.0%   0.0%   0.0%   0.0%   0.0%
1    0.61  57.6%   0.0%   1.2%  41.3%   0.0%   0.0%   0.0%   0.0%
--   ----- -----  -----  -----  -----  -----  -----  -----  -----
avg  0.59  46.2%   0.0%   1.2%  52.7%   0.0%   0.0%   0.0%   0.0%

Memory: 25956K (10740K) real, 37996K (19316K) virtual, 15364K free  Page# 1/14

CPU  TTY  PID USERNAME  PRI NI   SIZE   RES  STATE   TIME %WCPU  %CPU  COMMAND
1     ? 28586 arms      238 20  9164K  1072K run   157:22 90.13 89.97 oracleor
1     ?  1020 oracle    154 20  8712K   616K sleep   0:10  1.47  1.46 oracleor
1     ?  1900 oracle    154 20  8692K   588K sleep   0:05  1.10  1.09 oracleor
1     ?  1988 oracle    154 20  8620K   528K sleep   0:04  0.55  0.55 oracleor
0     ?   822 oracle    154 20  8764K   656K sleep   0:24  0.51  0.51 oracleor
1     ?  1793 oracle    154 20  8636K   524K sleep   0:01  0.43  0.43 oracleor
1     ?    53 root      100 20     0K     0K sleep 226:02  0.37  0.37 netisr
0     ?   304 root      154 20    24K    24K sleep 258:04  0.33  0.33 syncer
0     ?     0 root      127 20     0K     0K sleep  86:40  0.30  0.30 swapper
0    p2  2204 sanjay    178 20   720K   312K run     0:00  0.92  0.27 top
1     ? 29494 oracle    154 20  8772K   660K sleep   0:28  0.23  0.23 oracleor

Previous | Table of Contents | Next