Easy Tuning of Oracle Sequences Improves Throughput

In more than 13 years of helping clients tune their Cerner Millennium® systems, I have only recently turned my analysis to Oracle sequences. I have the IT team for a PowerChart client to thank for my newfound understanding of how sequence numbers can increase the variability in system response times…and drive clinicians crazy.

After engaging me to diagnose their PowerChart performance problems, the team corrected several MQ queuing issues and other straightforward problems, but clinicians were still experiencing slowdowns during their peak usage times. The culprit turned out to be the Oracle sequence numbers.

Oracle sequences give an identifying number to certain elements in the database. Millennium has about 964 separate sequences, Oracle’s System and Sys have about 20 and 66 sequences, respectively, DBSNMP has two, and CMS_DBA and Panther_Read have one each. Oracle generates these sequences when new data or rows are added to the database. Sequences become a performance problem when you have a lot of new data that needs sequencing but the tables aren’t configured to adequately handle the load. Making the change is surprisingly simple.

Here’s what I discovered for my client. I took a 1-minute and a 30-minute sampling of the Oracle sequence numbers being generated by PowerChart. The following chart shows the results of the 1-minute interval:

The key columns are Cache_Size compared to Sequences Generated. The Cache_Size shows how many sequence numbers can be generated before updating the high water number, which is the highest sequence number ever created for that sequence name. It takes time for Oracle to update this number, so you don’t want the system to have to do it too often. Increasing the Cache_Size decreases the number of times the high water value needs to be updated and, therefore, improves the throughput for the tables that use that sequence number.

Let’s look at an example in the chart. The Sequence_Name of Charge_Event_Seq generated 8,000 sequences in 1 minute. Because its Cache_Size was only 2,000, Oracle had to update the high water value four times during that minute. The Sequence_Name of Idgen1$ generated far fewer sequences, only 2,000, but its Cache_Size of 20 meant that Oracle had to update its high water value 100 times in this same minute. You can reduce this overhead associated with sequences — and improve the throughput for their corresponding tables — by increasing the Cache_Size to match or, preferably, exceed the number of sequences generated during your time frame. I recommended my client set Charge_Event_Seq to a Cache_Size of 9,000 and Idgen1$ to 2,000.

The change can be done in uptime without taking any action on the Millennium application nodes and without doing anything else on the Oracle nodes. The change is accomplished via SQL*Plus with this command:

Alter sequence charge_event_seq cache 8000;

In CCL, use the following command:

rdb alter sequence charge_event_seq cache 8000 go

The following chart shows the results from my 30-minute interval, which are a little more staggering in terms of the impact that high-water-value generation has on overall throughput. It also shows that only 35 sequences — about 3 percent of the more than 1,050 — had increases in their sequence numbers during this time (32 of 964 for V500 or Millennium and 2 of 66 for Sys).

So I recommend only changing sequence numbers that are being used a lot. In this case, I recommended:

  • Charge_Event_Seq and OCF_Seq have Cache_Size set to 10,000
  • All V500 sequences with more than 2,000 sequences generated have Cache_Size set to 8,000
  • All V500 sequences that match their Cache_Size be left at 2,000, at least initially
  • All V500 sequences with a Cache_Size less than 2,000 be increased to 2,000
  • Sys Idgen1$ have Cache_Size set to 10,000
  • Sys Ora_Tq_Base$ have Cache_Size set to 20
  • Panther_Read have Cache_Size set to 2,000

It is possible to make the Cache_Size too large, so my recommendations are fairly conservative. Still, the client will have much less variability in Millennium response times after implementing these straightforward fixes.

Prognosis: Keeping your Oracle sequence Cache_Size properly set is a simple, uptime tuning procedure that decreases performance-robbing events and variability in your Millennium environment.