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_hashCreate 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_hashCursor 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 IntervalBreak 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