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=””></sid>
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</serial#,@<inst_id></sid>
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.
