Millennium Medic

Use Oracle Tables to Kill Blocking Sessions

By David Hallberg

Cerner Millennium^® clients I talk with have lots of questions about Oracle. Today I’ll talk about how you can improve system throughput with the information in Oracle’s v$session table or gv$session table for the production database or any database in an Oracle Real Application Cluster (RAC). This Oracle table keeps track of all of the connections (or sessions) on the database. The table shows where the connection came from, the Millennium application node where executables like CPM Script run, the status of the session, the logon time, the blocking status (when another session is preventing or blocking the work from being completed), and the event or description of what the session is doing.

Why is this information important? This one table can tell you about any long-running script — either the cause of the problem or whether the script is just taking the resources it needs to accomplish its work. Problem scripts mean clinicians and business staff are not able to place orders or see results, so these issues need to be addressed.

Let me begin with a couple of cautions. First, with more than 70 columns, the table is quite wide, which can make viewing the data in a telnet, ssh, SQL*Plus session or CCL session quite difficult. I strongly recommend using Excel or another spreadsheet to save the query output and evaluate the data. Additionally, only use this set of queries if you are having a problem getting data into or out of the database. It is completely normal to have transient or sporadic blocked sessions. They provide a safety mechanism for Oracle to maintain data integrity.

The script analysis begins by finding the blocked sessions. Please keep in mind as you execute the following commands that SQL*Plus requires a semicolon (;) at the end of each command and CCL requires the characters rdb and a space at the front of the command as well as a space and go at the end. For the first command, type:

select * from gv$session where blocking_session is not null

If nothing is returned, then there are no blocked sessions. If you do get rows of data back, then you have found the sessions that are compromising Millennium’s performance. To manage the data, you can reduce the number of columns to only retrieve the entries you need. Modify the query as follows:

select inst_id, sid, serial#, osuser, process, machine, terminal, program, type, logon_time, current_queue_duration, blocking_session_status, blocking_session, event, wait_time, seconds_in_wait from gv$session where blocking_session is not null

Once the data is returned, you can use the sid column entry to find the problem processes. Type the following query:

select * from gv$session where sid = <sid number>

From the data returned, you can use the next command to kill the offending session. You need the ALTER SYSTEM privilege for Oracle to perform this function as well as the sid, serial # and inst_id values from the query. Execute the following:

alter system kill session ‘<sid>, <serial#,@<inst_id>’ IMMEDIATE

After you run the command, the session will either be completely removed or will be marked as SNIPED until the Millennium executable or server tries to use the session again. A SNIPED status will not inhibit Millennium process, so either result puts you in much better shape.

Prognosis: Using Oracle’s v$session table can allow you to quickly find problem processes that are blocking other processes and keeping them from completing their work. Unfinished processes mean users are not able to place orders or see results.

A Time to Use Messages.Suppress

By David Hallberg

If you are a regular reader, you know that over the last couple of years I have strongly advocated minimizing the use of Cerner Millennium’s messages.suppress file. Functionality that was intended to prevent extraneous events from cluttering up your message logs has been horribly misused by Millennium system administrators and the application support and application development staff. As a result, vital events are not being sent to the message logs, events that need appropriate troubleshooting and longer term problem resolution. (See “When No News Is Bad News” and “File Can Hide Serious Issues.”)

In recent months, however, I have seen some improvements in Millennium’s base tuning and configuration when a particular event is suppressed, so today I have an unexpected recommendation.

The improvement involves an event called SEC_seclibAudit, which is an Audit-level event telling you that debug is not enabled for the security components. A couple of years ago, only CPM Code Cache Manager seemed to log this event. With the 2010.x code, however, SEC_seclibAudit appears to be written by all of the Millennium servers. It generates two rows in the Data column of the message log each time it occurs. The first row says:

[SecDebugLog::Instance] Creating debug logging singleton.

The second row says:

[SecDebugLog::CheckConfiguration] Debug logging not enabled for all security components, componentLogicalName (SecDebugLogsecrtl)

This is a good setting, but you don’t need to be told about it with every executable. It would be better if you could assume the setting is off unless you took action to turn it on. (To enable debug logging, set the LogLevel of a server to 4 or set the environment variable or logical of CPM _GLOBAL_LOGGING or any one of a number of other logging increases that are available to individual Millennium servers.) This message has become so prevalent, logging on all servers, it has become noise. One client, who cycles 19,000 servers each week, gets 38,000 messages every week for this one event.

To stop the noise, I have to make a recommendation I rarely make: Put this event in the $cer_mgr directory’s messages.suppress file. Then cycle all of your multi-instance servers to make the change take effect.

Prognosis: Using the messages.suppress file appropriately stops extraneous logging and keeps your message logs clearer for real problems.