The data you need to evaluate the performance of your chart server applications are available from two sources. You can either 1) go to each chart server individually, stop the chart application, and use the Alt+S keystroke to bring up the configuration, or 2) use a single command to run an Oracle query either from CCL or SQLPlus. I actually have included two options for the Oracle command. Although they give a slightly different perspective, either will provide the information you need.
The first Oracle query lists all the chart servers that have processed a chart job from this domain in the past seven days and ties this information to the table where the chart server settings are kept. Pulling the data this way excludes unnecessary listings of old or no-longer-used chart servers defined in the Chart Server Settings Table. (These are chart servers made obsolete by domain refreshes or reference data domain sync [RDDS] reference builds.)
From CCL, type the following query:
rdb SELECT * FROM chart_server_settings css INNER JOIN (SELECT distinct server_name FROM chart_request WHERE request_dt_tm > (sysdate - 7)) cr ON css.server_name = cr.server_name go
From SQLPlus, type the following query:
SELECT * FROM chart_server_settings css INNER JOIN (SELECT distinct server_name FROM chart_request WHERE request_dt_tm > (sysdate - 7)) cr ON css.server_name = cr.server_name;
Either query will generate a wide table with columns from A to Z and continuing to AE. I purposely put the Server Name heading in both columns B and AE to ease finding the Server Name as you scroll through the spreadsheet. We are primarily interested in the following columns: Log_Level, Cycle_Rate and Save_Files_Ind. Generally, you want:
- The Log_Level to be 0 or 1 unless you are actively troubleshooting a problem with Cerner. Thankfully, the more recent versions of Millennium do not log as much to the Oracle database even when the Log_Level is set to a value greater than 1. However, versions prior to 2007.02 can cause tremendous Oracle updates with this column being set greater than 1.
- The Cycle_Rate to be 30 or less, which delivers much better throughput. Some clients go down to 5, but I believe this is a little extreme and aim for a value between 20 and 30.
- The Save_Files_Ind to be 0. This column is just a flag to save the chart as a rich text format (RTF) file on the chart server or to the location specified in the RTF_Output_Dest_CD column. Unless there is a specific business reason, you would generally not be saving the chart output to an RTF file.
The second Oracle query is simply a dump of all of the chart server configurations, so it will include all of the old chart servers (those not used in the last seven days and those moved into Production from domain copies and RDDS).
From CCL, type the following:
rdb SELECT * FROM chart_server_settings go
From SQLPlus, type the following:
SELECT * FROM chart_server_settings;
Many clients have three times more chart servers defined in this table than actually are in use in Production, which can be confusing for the Support team as it responds to complaints that charts are running slow. You may want to consider using CCL or SQLPlus to delete any unused chart servers from the table.
In addition to chart server tuning, I would recommend tuning specific to chart distribution. The Cerner Support website will give you the latest recommendations, but I’ll give you some configurations that have been recommended for many years across Millennium versions.
To access the chart distribution settings, run the following command from CCL or SQLPlus:
select * from chart_distribution
Here’s what to look for:
- The numbers listed under the First_Qualification_Days heading should not be more than 30.
- The numbers listed under the Absolute_Qualification_Days heading should be any value over 300.
- Review the years listed under the Max_Lookback_DT_TM, Absolute_Qualification_DT_TM and First_Qualification_DT_TM headings to see if any are 1800 or a year earlier than the date Millennium went live. For example, if you went live with Millennium in 2001, then the dates in these columns should not have a year of 2000 or earlier. If they do, the CP_Process_Dist script will consume a lot of Oracle resources to get these charts generated. Please refer to the problem SQL statement blogs from 2009 (“Oracle's Path for Rapid Transactions”).
- When distributions do not qualify data for more than 30 days, set the Absolute_Qualification_Days to 45.
After making any of the aforementioned changes, you need to stop and start the chart servers for the new settings to take effect.
