Millennium Medic

Purges Help Minimize Database Growth

By David Hallberg

Millennium® uses the Oracle database as its main repository of information, which includes data that you may only want to keep for a period of time. This period may be one day or 365 days, but what is important is to delete the data as soon as you can to keep your database as trim as you can.

Millennium has many purges to delete rows from application tables that are not designed for long-term storage. Generally, a 2007.18 client has 147 active purge templates. Without all of them running, you are going to experience a couple of side effects. The first is general database growth. You want to control growth where you can so that backups run as fast as possible and, if you have to recover the database, recovery times are shortened. A second side effect also relates to database size. Depending on the way the applications are written and the indexing on the tables being purged, if you get too large you can have stability and performance issues within the applications. Generally, the Open Engine and old MDI applications are the most susceptible.

I realize that my advice to configure all of the purges for your Millennium version goes against what many of you believe or have been told by consultants — that you only want purges for the applications you are using. Here’s why I disagree. The system experiences no degradation when it runs a purge for an unused application. If you try to purge a table that has no data in it, the purge script looks for qualifying rows and says, “No rows match," and then the process moves on to the next purge. There is no harm to the system and no performance impact to users. If, however, you decide at some point to implement that application, the purge will already be set up, saving a step in the activation process.

You should set up all of the purges to run at least once each day. After any Millennium upgrade, look for new purges to configure for optimal performance of Millennium and to make database recovery as rapid as possible.

To learn how to configure and run purges, click here.

Prognosis: Purging unused data daily helps keep the system as trim and spry as possible.

Remedies for RHOs — Part 6

The OS Path for Rapid Transactions

By David Hallberg

The first five installments in our series on Millennium^® performance issues for RHOs walked you through the necessary steps for evaluating connectivity issues as well as queuing problems involving Websphere MQ, shared service proxies, interfaces and Oracle. This week I’ll finish up with yet another area where queuing or waits can be a problem: the operating system (OS).

The OS runs the hardware for Millennium and is often referred to as the backend or backend servers. OS capacity issues can cause Millennium to perform slowly or, worse, sporadically. A sporadic manner could mean that at 7 a.m. Millennium is working fine, but from 9-11:30 a.m. it’s slow.

Before detailing helpful OS diagnostics, I want to answer the larger question, “Why do I need to bother with all this diagnosis? I’ve got an outsourcer to troubleshoot for me.” Experience has taught me that any time you outsource or out-task work, you should audit it. That way, if your clinicians and business staff are complaining about performance, you will have the information you need to have a data-driven conversation with your outsourcer.

The generic complaint of “Millennium is too slow” will generally be met with deaf ears. A comment such as “I have a Run Queue on all application nodes exceeding 4x my physical CPUs” is something your outsourcer can track down and help you correct. So I urge you to maintain technical knowledge in-house or find a trusted consultant who can assist in gathering and interpreting key data concerning your Millennium operations.

So what can you do to determine if your application nodes have performance or capacity issues? Click here to read more.

Prognosis: Monitoring OS capacity and taking action to reduce or eliminate issues as soon as they start — instead of days, weeks or months later — will improve Millennium's consistency.

The Ghost of Standards Past

By David Hallberg

As Halloween approaches, I’ll look at a second tuning standard for AIX that has taken on the status of an urban legend. I stumbled on both last week during a client visit. Like last week’s phantom belief that you need to have AIX disk I/O pacing enabled to make Oracle run faster, this week’s myth involves Millennium® users running on IBM’s AIX operating system. Unlike last week, however, today’s myth can be traced directly back to me.

While working at Cerner in 1997, I stumbled on a tuning parameter Millennium was not using: the logical volume manager buffer count, or lvm_bufcnt. The parameter was designed to provide greater I/O throughput on raw devices, which for Millennium deployments applied to the nodes where the Oracle database was running. I began a campaign to get this parameter tuned.

During this time, Millennium ran with both the application and database on the same node and was only a 32-bit system. The 32-bit architecture severely limited the amount of memory on these nodes, yet lvm_bufcnt needed memory to buffer disk reads and writes. As a compromise, we determined that setting the lvm_bufcnt to 20 would provide better Oracle throughput without consuming too much memory.

Now that Millennium is a 64-bit system and its application nodes and database nodes are split, the tuning standard for this parameter is very different. But, at least for the client I visited last week, the setting has remained the same. What should the new parameters be?

· For the application nodes, the setting should be 9, which is the default. Because there are no raw logical volumes (LVs) mounted on the application nodes, the higher setting of 20 is simply a waste of memory.

· For the Oracle nodes, the setting should be 64, which is the maximum value. These databases benefit from having the highest amount of memory buffers for performing reads and writes to the raw LVs.

Many urban myths have a kernel of truth in them. In this case, the truth that existed years ago under very different operational conditions still haunts us. It seems like a perfect time for an exorcism.

Prognosis: Millennium users running on AIX can set the LVM buffer count on the application and database nodes for greater I/O throughput.

 

Past Standards Still Haunt Us

By David Hallberg

A client visit earlier this week brought my attention to tuning standards established years ago for AIX nodes that are still being circulated. Like urban legends, the standards no longer have evidence to support them, but they live on. October seems like a good time to debunk these phantom beliefs, so I’ll be putting my next installment on issues facing RHO clients on hold for a bit longer.

The legend I’ll address this week claims that you need to have AIX disk I/O pacing enabled to make Oracle run faster.

When Millennium® was first created, AIX (IBM’s Unix) was only 32-bit, and Millennium was deployed with the application node and the database node existing on the same node or lpar. Back then, like today, the various AIX nodes had to be able to share resources for failover. For instance, if you had two Millennium application nodes running WebsphereMQ (MQ) and one node crashed, you needed them to share the log and data file systems for MQ. Why? If node 1 crashed, the other node would be able to complete its transactions.

Way back in the 1990s, Serial Storage Architecture (SSA) drives and HACMP (High Availability Cluster Multiprocessing) were used to share data between nodes. One artifact of this technology was that AIX could send more disk I/O messages to the shared SSA drives than the drives could handle. To minimize this risk, the minpout and maxpout throttles on disk I/O were used to control the disk I/O rate. Maxpout set a limit on the number of pending I/Os (typically 33) for any logical volume (LV) or file. The minpout was set lower (typically from 20-24) and would allow the drive to read and write to the file again once the number of messages fell back to that level.

This disk I/O pacing was a workable solution for SSA drives running on a 32-bit system, but it does not translate to today’s AIX client using storage area networks (SANs) on a 64-bit system. The pacing creates an artificial bottleneck and prevents you from getting the most out of your disk subsystem. It should be disabled.

Unfortunately, the disk I/O pacing caused so many issues when it was not set, the myth persists that you need to keep it set. If you are using SAN storage — as most of you are — this simply is not true and never has been true. SAN fabrics have other pacing mechanisms you should use instead of the AIX OS-level disk pacing to achieve optimal throughput. To disable the disk I/O pacing, you can run the following two commands as root on all of your AIX nodes (please note that these changes take effect immediately):

chdev -l sys0 -a minpout=0

chdev -l sys0 -a maxpout=0

Prognosis: To advance toward optimal throughput, discard the outdated tuning standards of AIX disk I/O pacing in favor of SAN pacing mechanisms.

Next week: Illogical tuning of the logical volumes.

Meeting face to face

We want to thank those of you who were in town this week and stopped by to talk to us. We learn much when we meet you face to face and hear about the issues at your site. Future posts will address how to improve your system’s performance in these areas. If you’re lingering in Kansas City a little longer this week and would still like to meet with one of our medics, give us a call at 913.238.3205. We’ll schedule a time to talk with you.

We're sorry that technical problems last week prevented our post from reaching subscribers. We've corrected the issue and will give you the rest of this week to digest all the meat it contains on determining and alleviating queuing issues in Oracle. We’ll continue with our series on throughput issues next week, specifically in the area of application and database capacity.

Remedies for RHOs — Part 5

Oracle's Path for Rapid Transactions

By David Hallberg

Our last entry on Millennium^® performance issues for RHOs (remote-hosted organizations) walked you through the necessary steps for evaluating queuing in Millennium's Open Engine FSI/ESO (OEN). This week we will cover another area where queuing or waits can be a problem: Oracle tuning. Oracle is the database where all of the clinical and financial information for Millennium is kept. When Oracle is slow, everything in Millennium is slow.

Tuning Oracle has two major areas of analysis: scripts and the kernel. Since Rob Dearden and David Fletcher spent several weeks on how to determine which scripts were causing issues, I am going to focus on the kernel. The kernel is the part of Oracle that can be tuned without getting new code or SQL scripts for Millennium. Oracle has an incredible amount of data about itself. If you run the CCL command "rdb select * from v$fixed_view_definition go" against an Oracle 10g database, you will see at least 774 views of data about the database and its performance.

So how do I find out if I’m having a queuing issue with Oracle?

There are a couple of queries you can run from CCL or from SQL*Net to show you some information about how Oracle is tuned. Due to the fact that Cerner is only supporting Oracle 9.2.0.5 on VMS, 10.0.1.4 on HP-UX, and 10.2.0.3 on AIX and Linux, I will focus on what we can see with these versions of Oracle. To find out which version of Oracle you are running, go into CCL and type the following command: "rdb select * from v$version go". The results will look something like this:

INST_ID	BANNER
1	Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
1	PL/SQL Release 9.2.0.5.0 - Production
1	CORE 9.2.0.6.0 Production
1	TNS for VMS: Version 9.2.0.5.0 - Production
1	NLSRTL Version 9.2.0.5.0 - Production

The first row under the heading tells you what Oracle release you are supporting. In this case, it’s the 9.2.0.5 client.

Next, you want to get a high-level view of where you are waiting. Since there can be up to 16 database instances running in support of Millennium 2007.18, you will use the gv$ commands. These commands run against all Oracle instances that are working as a cluster, unlike the v$ commands, which only show the information from the Oracle instance you are currently connected to. From CCL, run the following command: "rdb select * from gv$system_event order by time_waited_micro desc go". The first 10 values will look something like the table below.

INST _ID	EVENT	TOTAL_WAITS	AVERAGE _WAIT	TIME_WAITED_MICRO
1	SQL*Net message from client	962,668,574	39	373,907,951,010,156
1	rdbms ipc message	28,277,840	16	4,478,587,842,076
1	db file sequential read	271,931,027	1	1,488,969,858,599
1	ges remote message	707,889	100	708,576,585,945
1	pmon timer	238,459	297	708,379,689,995
1	smon timer	3,175	21,572	684,901,281,040
1	log file sync	20,394,137	1	252,162,674,967
1	log file parallel write	19,282,723	1	109,519,708,690
1	PX Deq: Execute Reply	127,533	55	69,627,574,406
1	SQL*Net more data from client	71,440,833	0	60,775,565,936

For readability, I took the liberty to add commas to the values over 1,000, and for space issues, I squished headings and removed two columns. Still, you can see that by far the No. 1 wait for Millennium RAC (Oracle clusters) is SQL*Net message from client. This measures the time that elapsed from the moment Oracle gave the data to the host’s TCP/IP stack until the client — in this case, Millennium Application servers — returned a response.

Here is a little quandary. This delay cannot be improved by tuning Oracle. Instead, you have to tune the TCP/IP stacks and operating systems where the database and Millennium Application servers reside. For a complete list of the events and their classifications that are considered null and not tunable by Oracle, please see the attached Oracle Waits table.

The two categories that are tunable include 1) the operating system (OS) and network tuning and 2) Oracle init.ora or spfile tuning. Below is a list of the waits frequently seen with Millennium and which category they fall into.

Tune OS and/or Network Settings	Tune to Oracle Recommendations
SQL*Net message to client	buffer busy waits
SQL*Net message to dblink	db file parallel read
SQL*Net more data from client	db file parallel write
SQL*Net more data from dblink	db file scattered read
SQL*Net more data to client	db file scattered write
SQL*Net more data to dblink	log archive I/O
ksfd: async disk IO	log buffer space
local write wait	log file parallel write
	log file sequential read
	log file single write
	log file switch (checkpoint incomplete)
	log flie switch completion
	log file sync
	undo segment extension

Another Oracle table that is incredibly useful when you are in RAC (Cache Fusion) configuration — meaning you have two or more Oracle instances running — is Resource Limit. This table shows you the Oracle resources each Oracle instance is consuming. To access the data, execute the following command from CCL: "rdb select * from gv$resource_limit go". The key columns are Max Utilization and Initial Allocation. The rows where Max Utilization is larger than the Initial Allocation indicate performance issues. The slowdown occurs because the additional memory has to be allocated from the shared pool. The solution is to increase the Initial Allocation to equal the Max Utilization. That way, the memory is allocated at Oracle startup instead of when clinicians or back-office people are using Millennium.

If you have Oracle 10g or higher, another useful tool is the DBA Alert History. This table shows a database administrator (DBA) the performance, space or other capacity issues occurring within the database. To access this table, execute the following command from CCL: "rdb select * from dba_alert_history go". The Reason and Suggested Action columns are the two you want to pay attention to. Reason shows you what went wrong; for instance, "Tablespace [UNDO] is [44 percent] full." This means the UNDO segment needs to be increased. The Suggested Action column in this case would say, "Add space to the tablespace". A third column that can be useful for categorizing these events is Message Group. In the example, this was a Space Message Group. The information in the DBA Alert History table provides you with an almost natural English view of issues in your database. It is an excellent starting point for easily tracking consistent Oracle issues.

What do I do if I’m having queuing issues?

If you discover Oracle queuing issues, you will have to work with your production DBA to discuss how to correct them. Use tact when talking with the DBA. Even though the truth is in the data, I have found that many DBAs grow defensive when they’re shown the data and can be reticent to make any changes. If they don’t seem to like my approach to data collecting, they can use one of the methodologies published by Oracle for maximizing throughput. Links to two are listed below. My experience has been that when Oracle is properly tuned, many hours of waiting can be eliminated from the Millennium environment.

http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm

http://www.oracle.com/technology/documentation/database10gr2.html

Prognosis: Monitoring Oracle waits and taking action to reduce or eliminate them will reduce the time it takes for Millennium applications to update and retrieve information. This saves time and frustration for the clinicians and improves care for the patients.

Next week: Application and database capacity issues and where to look to see if you are having throughput issues.

Remedies for RHOs — Part 4

Open Engine’s Path for Rapid Transactions

By David Hallberg

Last week our series on Millennium^® performance issues for RHOs (remote-hosted organizations) walked you through the necessary steps for evaluating if you are queuing in Millennium's Shared Service Proxy (SSP). This week we will cover another area where queuing can be a problem — in Millennium’s OEN/FSI/ESO servers. This has several aliases: OEN is short for Open Engine, FSI is an abbreviation for Foreign System Interface, and ESO is External System, Outbound. They provide the throughput for transactions to and from the other clinical systems that clinicians and office staff rely on to deliver quality patient care.

For example, a hospital’s registration system is usually not Millennium, but might be Epic, McKesson, or Centricity (IDX). After a patient is registered into that system, a set of processes sends that information to Millennium, which then saves and processes the data. On the outbound side, Millennium might send charges to the registration system either in batch or as they occur. If done through the ESO servers, the charges are created in Millennium, converted by a set of server processes into an HL7 message, and sent to the registration system.

Let me back up and explain the EON/FSI/ESO process and why it’s important.

Open Engine is a generic term for all of the servers (executables), middleware, and Oracle tables required to receive data from a non-Millennium system and send Millennium data to a non-Millennium system. I will be using OEN as a generic term for both inbound and outbound Millennium components, unless I specifically call out the differences.

OEN is important since so much information needs to be exchanged with other systems or computers. Without it, all of the people needing to see information held within Millennium would have to have Millennium access and use Microsoft’s copy-and-paste functionality to get information into their system. This process would be very slow and error-prone.

Generically, the figures below show the basic flow of information into and out of Millennium with OEN.

Figure 1 — Inbound Transactions

Figure 2 — Outbound Transactions

Starting at the left of the inbound transaction in Figure 1, we have a Foreign System, which means any computer system that is not part of the current Millennium domain. This system sends data into OEN, where the OEN processes put a row in the CQM_OENINTERFACE_TR_1 table to show the process status of the transaction. Another process takes that transaction and updates Millennium with the information.

The center of the outbound transaction in Figure 2 shows two tables that have the process status information of a transaction. The data first has to qualify as an outbound transaction. Then the CQM_FSIESO_TR_1 table is updated with a status for the transaction, and the transaction is put in the CQM_OENINTERFACE_TR_1 table with all of the formatting that the Foreign System needs to process it into its system.

So how do I find out if I’m having a queuing issue with OEN?

If you have Softek Panther, you can use the Interfaces control and view queuing in the Depth column. Without Panther, any Citrix or fat client should have the Millennium utility called Openview, or if you prefer seeing data flow across your screen, you can use a tool called oen_query_client(qc) and use the -mb -t15 switches to watch transactions. Both of these monitors look at the CQM_OENINTERFACE_TR_1 table. To monitor the CQM_FSIESO_TR_1 table for queuing, use the Interfaces control (if you have Panther)  or run CCL commands or SQL scripts. For an explanation of how to use these utilities, please refer to the appropriate Millennium documentation.

What do I do if I’m having queuing issues?

If you discover OEN backlog issues, your first step is to verify the settings of the backlogged OEN process in the Oracle Oen_personality table. If you right-click on the Process Name in the Panther Interfaces control and select Properties and Interface Properties, you can view the Oen_personality data for that OEN server. There are a couple of key values you want to check:

• trace_level should be 0
• sleep should be 10 or higher
• M1 should be DM
• lowleveltrace and logtcpip_ack both should be 0

You can change any of these settings through CCL's OEN_PROC_VIEW menu. You want to verify in the Panther Configurations control the appropriate settings in SCP for the servers. Right-click on the SCP Configuration name (Description) or the Entry ID, select Properties, and then verify that there is no Startup Script and that loglevel is set to 0.

Prognosis: Monitoring OEN queuing and verifying the appropriate configuration of servers (executables) will reduce the time it takes for Millennium to accept and send out transactions to other systems supporting your organization.

Next week: Oracle configuration and where to look to see if you are having throughput issues.