LOB Space Scripts

I said in my previous post that I put LOB space scripts in my GitHub repository and I wanted to explain a little more here. I have two databases that were growing rapidly and the top segment in each was a LOB segment. For one database the top LOB was a CLOB and for the other it was a BLOB. In both cases there were many inserts and deletes against the tables with the largest LOB segment. I was trying to find out if space was wasted and unusable in the LOB segments. Best I can tell these applications are reusing space when LOBs are deleted. These were both BasicFiles LOBs in 11.2 Oracle. And for the CLOB the character set was such that each character used one byte. Also, both databases had an 8192-byte block size.

A lot of this work was inspired by this Oracle support document:

LOB space not released after delete (Doc ID 2285007.1)

The title made me think the LOB space was wasted after the deletes. But this article had an interesting disclaimer highlighted in a box:

“Note: be aware that the unused space after deletion can be reused in the
LOB segment for further insert after retention time is passed.”

As far as I can tell, Oracle’s disclaimer is correct, at least in our situation. Unused LOB space left after a deletion is reused.

Here is a description of the LOB scripts in my GitHub repository:

spacetest.sql – This was my first script. I used various versions of this script to test different metrics that I could find about LOBs. This was helpful because I started very simple and tried to understand what each source of information was telling me. I really struggled to understand what AVG_ROW_LEN from USER_TABLES was telling me until I realized that I was hitting this bug or one like it:

Bug 14651892 AVG_ROW_LEN computed incorrectly on LOB when AUTO_SAMPLE_SIZE is used

It also took me a long time to understand why DBMS_SPACE.SPACE_USAGE only reported full or unformatted blocks for LOB segments. LOBs are all or nothing for a block. Each LOB that is not stored inline for a row fully occupies one or more blocks. With all these observations I throw in the caveat that this is the best I can tell based on my observations on my system.

The database with the CLOB uses this character set: WE8MSWIN1252.

This Oracle document says that character sets like this have a single byte per character in a CLOB:

CLOBs and NCLOBs character set storage in Oracle Release 8i, 9i, 10g and higher (Doc ID 257772.1)

“CLOBs when using fixed width character set NLS_CHARACTERSET are stored in the NLS_CHARACTERSET character set on disk. Examples are WE8MSWIN1252 …”

This matters because dbms_lob.getlength returns the size of a CLOB in characters. In some character sets like AL32UTF8 you have to multiply dbms_lob.getlength’s output by 2 to get the bytes for the CLOB.

Given all the information I got from playing with spacetest.sql I created the next two scripts to feed information into the final space script.

blobinlinecutoff.sql – Takes an integer from 1 to 8191 and creates a row in a table with a LOB of that many bytes in size and it outputs whether that LOB is an inline LOB or not. I used this to find the cutoff point between LOBs that fit in a row and ones that did not. I found the cutoff to be 3964 bytes in all the scenarios I tried. I use this cutoff to ignore inline LOBs when counting the total LOB space in the column’s LOB segment.

inoneblock.sql – Takes an integer from 1 to 8191 and creates a row in a table with a LOB of that many bytes in size and it outputs whether that LOB fits fully in one block. Many LOBs are larger than one block which is only 8K for me so I wanted to calculate exactly how many blocks each LOB would take. I got 8132 in my tests which means 60 bytes per block are unusable for LOB storage. So, I would need to divide the LOB size in bytes by 8132 and round the resulting number up to the next integer to get the number of blocks occupied by the LOB.

I plugged the results from blobinlinecutoff.sql and inoneblock.sql into my final script:

lobspace.sql – Outputs information about LOB space used to show how much is allocated but not used by current LOBs. This space could either be available for reuse by future inserts or it could be wasted due to some bug.

Notice that the top of the script has the input from the previous two:

  inlinecutoff number := 3964;
  usableperblock number := 8132;

You also put the table owner, name, and LOB column name at the top as parameters.

The output looks like this on my BLOB column table:

Table owner = MYOWNER
Table name = MYTABLE
LOB column name = MYLOBCOLUMN
Number of rows in table = 380553608
Number of rows with lob in table row = 338491004
Number of rows with lob in lob segment = 42062604
Total lob segment size = 1496535 megabytes
Total size of full lob segment blocks = 1473933.135288238525390625 megabytes
Total lob space used in lob segment = 1462133.555019378662109375 megabytes
Percentage of full blocks used = 99%

PL/SQL procedure successfully completed.

Elapsed: 07:46:50.26

I found that this database is not deleting all the rows that it is supposed to, so it is fully using all the space freed by the deletes and is continually adding space.

But the output from the other database (the one with the CLOB) looked different:

Table owner = MYOWNER2
Table name = MYTABLE2
LOB column name = MYLOBCOLUMN2
Number of rows in table = 66360290
Number of rows with lob in table row = 54200592
Number of rows with lob in lob segment = 12159698
Total lob segment size = 913999 megabytes
Total size of full lob segment blocks = 906413.57425689697265625 megabytes
Total lob space used in lob segment = 373804.97021484375 megabytes
Percentage of full blocks used = 41%

PL/SQL procedure successfully completed.

Elapsed: 00:45:22.57

Notice that the percentage of full blocks used in the first database was 99% but in this one it is 41%. This made me really wonder if deleted space was really being freed for use by inserts. But digging further I found that we only keep 7 days of history on this table and back in November we hit a peak of activity which expanded the LOB segment. Since then, we have stopped adding new space to the LOB segment’s tablespace. It appears that we have plenty of space free for this LOB segment to absorb a new batch of inserts because more than half of the space in the LOB segment is free for reuse.

Here are the settings for the BLOB:

  CHUNK       8192

Here are the settings for the CLOB:

  CHUNK       8192

Some blog posts that I studied for this:




I wanted to put this out there to help others but also myself. I do not want to forget some of the things I learned in the process. Also, if anyone out there has any feedback on this including any errors that I made it would be great to hear it.


Posted in Uncategorized | 2 Comments

LOB Scripts in my GitHub Repository

I do not have time to write a post explaining these now, but I wanted to note that I am posting LOB space related scripts here:



Posted in Uncategorized | Leave a comment

December 26 Plan Change

On December 26th, which is a holiday for my company this year, our team got a page that a plan had changed on an important HP Unix platform Oracle database. The new plan was inefficient but did not cause any harm to the application. I added the SQL_ID value for the query to our plan change monitor exception list so that it would not page us again when it changes to slower plans because the slower plans were not a threat to the system. Normally I would just move on but this time I thought I would dig into why the query changed plan and see if there is anything we could do to prevent similar changes. If the new plan was a problem, I typically would force the faster plan with a SQL Profile. I was going to do that this morning, but the system was busy, and the profile script was hung up for a few minutes on a library cache lock, so I just backed it out. I do not really need to intervene since it is not hurting the system. We have enough CPU capacity to handle the slow plan. But I was still curious if I could find the underlying cause of the plan change and learn any lessons from it. That is what this post is about.

Like many queries that change plan and set off alerts, this problem query uses bind variables. The PL/SQL package that includes the queries passes in values in the where clause instead of using constants. I.e., it looks like this:

    div = p_div
    AND cust_nbr = p_cust_nbr

and not this:

    div = 123
    AND cust_nbr = 456

You can see here where the query changed to the bad plan:

--------------- --------------------- ---------------- ------------------
      504369030 25-DEC-22 04.00.45 AM              838         .478386635
      504369030 25-DEC-22 07.00.40 PM             1599         .669989368
      504369030 25-DEC-22 11.00.15 PM             1044         .595122605
      504369030 26-DEC-22 01.00.16 AM              891         .558159371
      504369030 26-DEC-22 02.00.43 AM              473         .453122622
     2693825138 26-DEC-22 02.00.43 AM              311         3.61250804
     2693825138 26-DEC-22 03.00.07 AM              779         2.91877792
     2693825138 26-DEC-22 04.00.10 AM             1076           7.274671
     2693825138 26-DEC-22 05.00.41 AM             1218         11.1912258

The bad plan is averaging 11 milliseconds instead of less than 1 millisecond so “bad” is relative. It still is not slow enough to affect the users.

I used my plan script to run the problem query with different constants in place of the two variables. I used my optimizer statistics scripts to find what the optimizer thought the range of possible values was for the two columns. Here are the ranges:

----------- ---- --------
DIV         228  5220    
CUST_NBR    1675 74394502

I tried a variety of variable value combinations in and out of this range and most of the values outside these ranges resulted in the bad plan having a lower cost than the good one. It was especially sensitive to the values of DIV. It turns out that there are many possible DIV values outside this range. They are just not on this table. So likely this query is often run with variable values that are out of the range of what the optimizer knows is in the table.

I tried to find a way to make the out-of-range values pick the better plan. I loaded this data on a 19c database but got the same results. I tried different kinds of histograms, including one on (DIV, CUST_NBR), but they didn’t help.

So what?

Maybe when you write a query that takes variables you should try out its performance with atypical values for the variable values. In simple equals conditions like those in my problem query maybe check how the query runs on values that are outside of those found in the table. Since the query’s plan is not fixed you must be prepared for the query to run with all the plans that the data passed into its variables could generate. Also, the database will stick with any of those plans for a long time unless something forces it to be recalculated. So, you would have to test the query with atypical values and once the plan is in memory run all your typical data through that plan to see if it is still fast enough. If the plan from the atypical data is too slow then you would have to change the query or the data to handle those cases or resort to something like a hint to lock in the plan that runs well with the more typical variable values.



I just want to say how hard it is to write a blog post. What a pain. But the interaction with others on the internet is very valuable.

Someone commented that I could look at v$sql_shared_cursor to find why 1anm65yacs6ky changed plan. As expected, it changed plan because statistics were gathered on the table. Often statistics gathering causes plans to be recreated (hard parsing). But it was interesting that the reason from v$sql_shared_cursor was “Rolling Invalidate Window Exceeded”. A quick Google search led me to this article:


It claims that there is a 5 hour window after statistics are gathered for plans to be reparsed. This makes sense because I know statistics are gathered on the table around 22:00 but the plan is flipping between 01:00 and 02:00 the next day. Very cool.

But it does not really explain why the plan changes. It only explains why 1anm65yacs6ky was hard parsed. The plan changed because out of range data was passed into the bind variables during the hard parse and this resulted in a plan that was inefficient for in range data. On December 26th we got unlucky during the hard parse. This morning, January 9th, we got lucky, and we flipped back to the good plan.

What makes blogging about this stuff so hard is that there is so much I want to say about this, but I just don’t know if I have the energy or ability to put it all down. We use bind variables to minimize parsing. With the good plan this query runs in a couple of milliseconds. If it had to be hard parsed every time it runs, 14,000 times per hour, it would be very wasteful and slow. But the cost of using bind variables is that a plan can get locked in that isn’t efficient for many of the values passed into the variables. It is a no-win scenario. The bigger picture is that I think Oracle’s optimizer, and probably any conceivable SQL optimizer, has limits. Somehow all these years Oracle and others have produced SQL RDBMSs that people find useful. But based on my Oracle experience it seems like they are imperfect but useful.

In the case of my December 26th query that changed plan, our business is functioning just fine with the query’s plan flipping back and forth. If I did not have my plan change monitor setup, I would not even know that the plan was changing because it is not causing an issue that is visible to the users. So, it offends my perfectionist tendencies to have a query run an inefficient plan, but from a business perspective the inefficiency and imperfection that is fundamental to the design of Oracle’s optimizer with the tradeoffs of minimizing hard parses with bind variables but suffering with inefficient plans until the next hard parse is acceptable because the business purpose of the application is still being accomplished. Maybe in the bigger picture Oracle’s SQL and SQL in general have been useful to people all these years because they do well enough to meet people’s needs despite their fundamentally imperfect execution of SQL queries.


I found two ways to prevent the SQL statement from changing plans when it is hard parsed with variable values that are out of the range of the column statistics. One is to add a new index, and the other is to add a cardinality hint.

I had no idea if the index would help but I noticed that the query touched the same table 3 times after looking up rows using the only index. I looked at the subselects on the query and picked a new index that would cover all the columns used by the subselects and be efficient. This eliminated all but the one table lookup that we had to have. For some reason this plan is chosen even if the data in the variables is out of range. Maybe the new index made the plan so efficient that out-of-range data would not cause a plan flip.

Just now I got the idea of trying a cardinality hint like this:

/*+ cardinality(PSD 10) */

I put this on every subselect that had the main table. This caused the plan to act as if the table would return 10 rows and it choose the good plan even if the data in the variables was out of range.

These two approaches work but they require some query tuning knowledge when you are rolling out new SQL statements. I think part of the challenge of using SQL databases like Oracle is that you need developers and DBAs who understand the optimizer well enough to understand the challenges of bind variables and plan changes and how to prevent them or fix them.


See this Oracle support document:

Limitations of the Oracle Cost Based Optimizer (Doc ID 212809.1)

There is also this bug which may prevent us from using Adaptive Cursor Sharing on this application which has all its queries wrapped in PL/SQL:


It looks like none of our SQL is using ACS:

  4  count(*) cnt
  5  from
  6  v$sql
  7  group by
 10  order by cnt desc;

I I        CNT
- - ----------
N N      22970

Maybe our application design suppresses the use of ACS which results in more bad plans being locked in on queries with bind variables. If we bypassed PL/SQL and just ran queries against the database from the Java front end, we might use ACS to prevent a bad plan getting locked in based on certain oddball variable values.


Tired of writing PPS and all that. I am probably running out of steam. I cannot find anything that really applies generally to our system based on this example. I found this weird part of the plan when the variable values are out of range:

| Id  | Operation                            | Name            | Rows  |
|   6 |       MERGE JOIN CARTESIAN           |                 |     1 |
|   7 |        TABLE ACCESS BY INDEX ROWID   | PROD_ATLN_DTL   |     1 |
|   8 |         INDEX RANGE SCAN             | PROD_ATLN_DTL_0 |     1 |
|   9 |        BUFFER SORT                   |                 |    36 |
|  10 |         TABLE ACCESS FULL            | XYZ_ENT         |    36 |

It seems like for Id 6 the Rows value should be 36 instead of 1. I think the optimizer treats 0 rows as 1 row when it displays the plan so maybe it is multiplying 0 * 36 and then displaying 1 instead of 0 for id 6. But other parts of the plan treat 0 as if it was 1 and add up the cost of the steps of the plan. Both the good and bad plan have costs that are way off for the out of range variable values so maybe when you get down to this level the errors inherent in the optimizer make plan comparisons meaningless. But then what strategy can you use with queries that use bind variables and that have out-of-range data values passed into them? You can use hints, etc. but what can you do without intervening for every query like this?


Looks like the cartesian joins were caused by the constants being in the query in two places.

In the innermost subselect it was this:

   WHERE     DIV = 111
         AND CUST_NBR = 222

Then in the final where clause:

 WHERE     PCD.DIV = 111
       AND PCD.CUST_NBR = 222

DIV and CUST_NBR where part of the joins all through the query so these constants were only needed once. Ultimately this query was poorly constructed, so the optimizer had trouble locking down a good plan. Maybe the bigger picture is that the better job we do designing our queries, indexes, etc. the less likely plans are to change for the worse.

Posted in Uncategorized | 2 Comments

US Foods IT Hiring

US Foods IT Jobs: url

My company is hiring a number of IT positions with more to come soon.


Posted in Uncategorized | Leave a comment

MiB – not Men in Black!

Yesterday I was reading over some Kubernetes documentation and ran across the abbreviation MiB. I almost ignored it and kept reading. It seemed to just mean megabytes as in 256 MiB meaning 256 megabytes or 256*1024*1024 bytes. It was here:


“For example, if you set a memory request of 256 MiB for a container…”

Why in the world are they saying 256 MiB instead of 256 MB? I figured it didn’t really matter and was about to skip it when I decided to do a quick Google search and that took me down this rabbit hole.

Evidently MiB is short for mebibytes – https://simple.wikipedia.org/wiki/Mebibyte.

As expected, 256 MiB is the same as what I call 256 megabytes. But it gets a lot hairier. It seems that there is a standard that has been around for years which redefines a megabyte as 1 million bytes. As far as I understand it, IEC 60027-2:2019 is the current standard for names for the number of bytes. Look at the history of this publication here:


It seems like the IEC 60027-2 standard has been evolving since 1972, but Wikipedia says it was published in January 1999:


This page has a nice summary I think of the standard:


It says that a megabyte is 1000000 bytes and a mebibyte is 1048576 bytes.

This is all very bizarre to me, and I shared it in my team’s chat and my manager said he sees MiB a lot dealing with storage. But we joked that it also looks a lot like an abbreviation for Men in Black:


But I guess that would be MIB not MiB. 🙂

Digging further into Google searches I found this very amusing history of how quantities of bytes have been described down through history:


How cool is that? So, I tried to get a grip on myself and make sense of why Kubernetes is using MiB.

Evidently with meters mega means 1000000 meters. So, maybe this is like the controversy when I was a child where they tried to get the United States to convert to the metric system. Trying to get everyone to call 2^20 bytes a mebibyte and 10^6 bytes a megabyte will probably be an uphill battle for it to become standard just as the metric system never took hold in the US.

But in Kubernetes you are forced to think this way. Later in the same manual page it describes two suffixes: M and Mi. It says that 129M is about the same as 123Mi. 129M would just be 129,000,000 bytes. 123 Mi is 123*1024*1024 = 128974848 bytes.

But in all my work with Oracle 1M has always been 1024*1024 = 1048576 bytes. For example:

DATAFILE '/tmp/test.dbf' SIZE 1M; 

Tablespace created.

SQL> host ls -l /tmp/test.dbf
-rw-r-----. 1 oracle dba 1056768 Nov 22 10:18 /tmp/test.dbf

In this example the database tacks on an 8192 bytes header block to the 1048576 bytes of allocated space to create a 1056768 byte file. (1048576+8192=1056768)

So, working with Oracle M means 2^20 bytes but in Kubernetes 10^6. In Kubernetes Mi means 2^20 bytes.

For fun search the Oracle manuals for the word “mebibyte” and you will find a couple of entries for 19c and 21c which is amusing.

In my Oracle work I use the following definitions:

kilobyte = 1024 bytes = K

megabyte = 1024 kilobytes = M

gigabyte = 1024 megabytes = G

terabyte = 1024 gigabytes = T

As Oracle’s database software works today these are good definitions.

Outside of the Oracle database these values might be named kibibyte, mebibyte, gibibyte, and tebibyte abbreviated Ki, Mi, Gi, and Ti.

No space aliens involved. At least, none that I remember… 🙂


Posted in Uncategorized | 2 Comments

Gold image for 19.16 install on RHEL 8

On Red Hat 7 Linux VMs we use a zip of a 19c Oracle home with the latest quarterly database release update applied which at the moment in 19.16, the July 19, 2022 version. Our standard deployment script just unzips the gold image zip and then runs the installer silently with a response file. But when I ran the same process on a Red Hat 8 VM I got errors. I found something that said to set this variable to resolve the first error:


And then I got package missing errors which I could ignore about this package:


But I finally hit an error that I could not get around no matter what I did:

[FATAL] Error in invoking target ‘all_no_orcl’ of makefile ‘/oracle/product/db/’.

So, I opened a service request (SR) with Oracle support, and they gave me a series of steps to rebuild my 19.16 gold image zip in a way that would get past this error. There was only once step that I had to add to what they recommended so I want to document that here.

First, they recommended unzipping the base 19.3 install file in my oracle home and then putting in the current opatch. These steps looked like this on my system:

unzip LINUX.X64_193000_db_home.zip -d $ORACLE_HOME

cp p6880880_190000_Linux-x86-64.zip $ORACLE_HOME


mv OPatch OPatch.orig

unzip p6880880_190000_Linux-x86-64.zip

This just left me with the base 19.3 install and the current opatch in the Oracle home but nothing installed.

Through a bunch of trial and error I found that I needed this step before I went further:

cp /etc/oraInst.loc /oracle/product/oraInventory

Our VMs come with an OEM client pre-installed so there is already an inventory. Maybe that is why I needed this step. I have not had a chance to test this on a clean RHEL 8 VM without an OEM client installed.

Next, I had to run the actual install which required an X server with the DISPLAY variable setup. I had fun getting this to work with MobaXterm and its ssh tunnel feature but once I figured it out it worked great. I ended up setting my DISPLAY variable like this:

export DISPLAY=localhost:0.0

I set the tunnel to listen on port 6000 on my RHEL8 vm and connected it to that same port on the ip for my MobaXterm X server. Maybe that needs a separate post, but other people probably do this all the time.

The install uses this patch:


I unzipped this to /oracle/db01/install/34160854

Then I ran the install like this:

./runInstaller -applyRU /oracle/db01/install/34160854/34130714

This spit out some text messages about applying the patch but then went into the normal graphical interactive installation steps through X windows. I did a standalone binary install without RAC.

Next, I had to apply the other part of the combo patch:

$ORACLE_HOME/OPatch/opatch apply /oracle/db01/install/34160854/34086870

This ran like a typical opatch apply.

Now that I had followed Oracle’s instructions to install 19.16 in a way that could be made into a gold image that works on RHEL 8 I did the following to make the gold image:

./runInstaller -silent -createGoldImage -destinationLocation /oracle/db01/install

Then I blew away everything in the oracle home and the inventory directory and redid the install from the new gold image like this:

unzip /oracle/db01/install/db_home_2022-09-20_05-53-23PM.zip -d $ORACLE_HOME

$ORACLE_HOME/runInstaller -silent -responseFile $ORACLE_HOME/19cresponsefile.rsp

The response file was the same that we always use for 19c on RHEL 7. Also, I did not need to set CV_ASSUME_DISTID=’OL7′ because the gold image has a recent version of the installer that does not require it. I think the main point of installing from patch 34160854 was to get a patched version of the installer that works with RHEL 8. My old gold image zip was made from the base 19.3 zip with the 19.16 database release update applied. Evidently that did not update the installer to make it support Red Hat 8, so I had to build a new gold image using patch 34160854 as described above.

Anyway, I don’t have a ton of time to go back and clean all this up right now but hopefully this basic dump of information will be helpful to someone. If nothing else, it will remind me!


Posted in Uncategorized | Leave a comment

netstat -o shows that (ENABLE=BROKEN) turns on TCP keepalive

In an earlier post I showed a Java program that will login to an Oracle database and wait for 350 seconds. I also talked about how we set the Linux parameter net.ipv4.tcp_keepalive_time to 60 seconds but that I needed to add (ENABLE=BROKEN) to the TNS connect string to enable the keepalive. I found a helpful post that said to use netstat -a -n -o to see connections that are using TCP keepalive. So, I tried my Java program with and without (ENABLE=BROKEN) and ran netstat -a -n -o both ways and it showed that keepalive was only working with (ENABLE=BROKEN).


$ netstat -a -n -o | grep
tcp6 0 0 ESTABLISHED keepalive (27.30/0/0)
$ netstat -a -n -o | grep
tcp6 0 0 ESTABLISHED keepalive (41.47/0/0)


$ netstat -a -n -o | grep
tcp6 0 0 ESTABLISHED off (0.00/0/0)

I edited the IP addresses to obscure them and removed spaces to make it fit better, but the important thing is that with (ENABLE=BROKEN) the 60 second keepalive timer is working, but without it the timer is off.

This information might not be that helpful to others if they do not have this kind of timeout, although I have been told that many firewalls have similar timeouts. Certainly, any AWS customer that connects through their Gateway Load Balancer to an on premises Oracle database would need to know this sort of thing. Hopefully, we are not the only ones in the world doing it this way! But at least I documented it for myself which will be helpful no matter what.


P.S. Related Oracle Community discussion

Posted in Uncategorized | Leave a comment

350 Second Timeout Causes ORA-03135 Errors in AWS DMS

This is a follow up to an earlier post about the 350 second timeout that is built into Amazon Web Services’ (AWS) Gateway Load Balancer (GWLB).

The earlier post was about Debezium (DBZ) using its Oracle Connector to pull data from an on-premises Oracle database into Kafka in AWS. DBZ used JDBC to connect to the Oracle database so I built a simple Java program that uses JDBC to mimic the behavior we saw in DBZ. With DBZ we were hanging if any SQL statement that DBZ ran took >= 350 seconds to run. If it did, then the Oracle session hung and Debezium never got past that SQL statement.

But for AWS Database Migration Service (DMS) the symptoms were different. For DMS I could not find any SQL statement that ran for >= 350 seconds. All the SQL statements ran much faster. But we did see ORA-03135 errors in DMS’s log like this:

ORA-03135 Error

DMS seemed to be waiting >= 350 seconds between SQL statements in certain cases, maybe doing a large load, and that seemed to be causing the ORA-03135 errors. I also saw DMS Oracle sessions waiting for more than 350 seconds on “SQL*Net message from client” idle waits. These seemed to eventually go away after 6000 or more seconds. I think that the GWLB was silently dropping the network connection, but the Oracle sessions still existed until at some point they realized that the network connection was gone. But I wanted to recreate the problem in a simple test case to prove that the 350 second GWLB timeout would throw the ORA-03135 error and leave the DMS Oracle sessions hanging for several thousand seconds in the SQL*Net wait that I was seeing in our production DMS sessions.

To recreate this error and the orphaned session behavior and to show that it was due to the GWLB 350 second timeout and not some other weird network problem I did some simple tests with SQL*Plus and Instant Client. I installed these on an AWS EC2 Linux machine that already had the firewall and security group configuration setup to allow a connection from the EC2 to an on-premises Oracle database. Then I just logged into that database and sat idle for different lengths of time before running a select statement. I narrowed it down to about 350 seconds as the cutoff point where the session is lost due to too much idle time.

Here is my test with < 350 second wait:

SQL> connect myuser/mypassword@mydatabase
SQL> host sleep 348

SQL> select * from dual;


Elapsed: 00:00:00.11

Here is my test with > 350 seconds wait:

SQL> connect myuser/mypassword@mydatabase
SQL> host sleep 351

SQL> select * from dual;
select * from dual
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 1208
Session ID: 57 Serial number: 21111

Narrowing it down to 350 seconds at the cutoff showed that just logging in and waiting for > 350 seconds causes an ORA-03135 error. I also verified that the associated Oracle sessions hung around for > 350 seconds stuck on the “SQL*Net message from client” wait. Sure, DMS could be throwing a ORA-03135 error due to some unrelated network problem, but my SQL*Plus test proved that any Oracle connection from our AWS environment back to our on-premises Oracle databases will throw a ORA-03135 error and leave orphaned Oracle sessions if it sits idle for >= 350 seconds unless we put the fix in place that I mentioned in my earlier post.

The fix is to set the Linux parameter net.ipv4.tcp_keepalive_time to < 350 seconds and to use (ENABLE=BROKEN) in your connection strings. Once I put these in place for my SQL*Plus test I could wait longer than 350 seconds and then run a select statement with no errors.

Since March when we noticed this timeout with Debezium I have suspected the timeout would also affect DMS, but I did not know that the symptoms would be throwing ORA-03135 errors and leaving orphaned sessions when the time idle between SQL statements exceeded the timeout. It took a few tickets working with AWS support but last week they put net.ipv4.tcp_keepalive_time < 350 seconds and (ENABLE=BROKEN) in their global DMS configuration for all their customers.

So, from now on anyone setting up a new DMS replication instance version 3.4.5 or later should be able to replicate data from AWS to an on-premises Oracle database through Amazon’s Gateway Load Balancer without facing these ORA-03135 errors. If you created your replication instance before last week you should create a new one >= version 3.4.5 to take advantage of this fix, especially if you are seeing ORA-03135 errors in your logs.


Posted in Uncategorized | 2 Comments

Docker Sample Application behind Zscaler

I am trying to learn about Docker by installing it on an Oracle Linux 7 VM on top of VirtualBox on my work laptop. My work laptop uses Zscaler. I had a bunch of certificate issues and ended up learning a lot about Docker by working around them. I tried to do the Sample Application – really the simplest first step in the Docker documentation – and had all kinds of trouble getting it to work. Ultimately, I ended up with a Dockerfile that looked like this:

[root@docker ~]# cat Dockerfile
# syntax=docker/dockerfile:1
FROM oraclelinux:7
COPY z.pem /etc/pki/ca-trust/source/anchors/z.pem
RUN update-ca-trust
RUN echo sslverify=false >> /etc/yum.conf
RUN yum install -y oracle-nodejs-release-el7 oracle-release-el7
RUN yum install -y nodejs
RUN npm install -g npm
RUN npm install -g yarn
COPY . .
RUN yarn config set "strict-ssl" false -g
RUN yarn install --production
CMD ["node", "src/index.js"]

By contrast the Dockerfile that was supposed to work looks like this:

# syntax=docker/dockerfile:1
FROM node:12-alpine
RUN apk add --no-cache python2 g++ make
COPY . .
RUN yarn install --production
CMD ["node", "src/index.js"]

I ended up using the oraclelinux:7 image because it had more stuff installed such as update-ca-trust. Because I could not get anything to work with Zscaler I had to start with an image that did not require me to pull more stuff down with yum. Then, after playing with it I still ended up disabling SSL verification on yum and yarn. I had to install node since I was starting with a plain Linux image and not a node image.

I had these instructions for getting Zscaler to work on my Oracle Linux 7 VirtualBox VMs on my company computer:

Had to extract Zscaler .cer root ca from Chrome browser as z.cer. 
Moved to linux and ran:

openssl x509 -inform der -in z.cer -outform der -out z.pem

copied z.pem to /etc/pki/ca-trust/source/anchors/




I do not know if this is really doing anything. It affects curl so that I can use curl without the -k option to disable SSL verification. Maybe things that use curl under the covers are affected by adding z.pem to the trusted certificates.

Anyway, I just wanted to document this for myself. Maybe someone out there will benefit also.


Posted in Uncategorized | 2 Comments

350 second AWS timeout causes JDBC call to hang

When I run the following Java program on an AWS EC2 Linux virtual machine connecting to an Oracle database in my company’s internal network it hangs forever.

When I run it on a Linux machine on our internal network it runs fine.

Evidently my company uses an AWS feature called “Gateway Load Balancer” to connect our AWS network to our internal on premises network. Evidently the GLB has a 350 second timeout. See this document:


Here is a quote of the relevant paragraph:

Some applications or API requests, such as synchronous API calls to databases, have long periods of inactivity. GWLB has a fixed idle timeout of 350 seconds for TCP flows and 120 seconds for non-TCP flows. Once the idle timeout is reached for a flow, it is removed from GWLB’s connection state table. As a result, the subsequent packets for that flow are treated as a new flow and may be sent to a different healthy firewall instance. This can result in the flow timing out on the client side. Some firewalls have a default timeout of 3600 seconds (1 hour). In this case, GWLB’s idle timeout is lower than the timeout value on the firewall, which causes GWLB to remove the flow without the firewall or client being aware it was dropped.

Best practices for deploying Gateway Load Balancer

This means that my JDBC call using the thin driver will work fine if I sleep for 349 seconds but will hang forever if I try to sleep for 350 seconds. The solution is to update a Linux operating system parameter and to update the JDBC connect string.


sysctl -w net.ipv4.tcp_keepalive_time=60

add this line to /etc/sysctl.conf:


Evidently our default tcp_keepalive_time value was 7200 seconds which is longer than the 350 second timeout so we had to lower it to 60 seconds to that the Gateway Load Balancer would know that our JDBC call was actually doing something.

You have to add (ENABLE=broken) to the jdbc connect string like this:


Once I did this my Java test program worked fine. It ran for about 350 seconds and finished cleanly.

If you are working in AWS and connecting to a on premises database using JDBC and you have a SQL statement that should run for 350 seconds or more and hangs forever you might check whether you are being affected by this timeout.


p.s. I forgot to mention that the Oracle database session goes away after 350 seconds. It is just the client side JDBC call that hangs apparently forever.

p.p.s. We have a related issue with Putty sessions connecting to Amazon EC2 Linux VMs timing out after 350 seconds. A coworker offered this article as a solution:


The Putty keepalives setting works great!

Another coworker of mine was saying that certain types of firewalls work this way with timeouts. The problem is that the GWLB times out our on-premises side but not our AWS side. So, in the case of using Putty to ssh into an EC2 that does not have keepalives configured my Putty session, which also does not have keepalives configured, times out after 350 seconds of idle time. When I hit enter, I get “Network error: Software caused connection abort” but if I check my BASH shell process id, I see that my shell process was never terminated. So, old processes hang around forever on my EC2 if the ssh connection times out due to the GWLB 350 second timeout.

Maybe it is normal for connections on one side of a firewall to time out and the other side to hang forever? I am not sure.

Posted in Uncategorized | 2 Comments