SQLPlus 12c Memory usage Grows (Leaks) While Running Long Query (Doc ID 1919000.1)

Last updated on OCTOBER 28, 2016

Applies to:

SQL*Plus - Version 12.1.0.1 and later
Information in this document applies to any platform.
*** Check for Relevance Oct-28-2016 ***

Symptoms

Observed On :SQL*Plus 12.1.0.1 on UNIX machines, but could happen with any tool that uses sqlnet connections (client tool using sqlnet.ora file).

ACTUAL BEHAVIOR

When executing  "select * from huge_table", the memory used by sqlplus grows until exhausted.

The memory leak is observed also for the background processes ora_asmb, oracle+ASM_asmb, ora_fsfp and ora_nss if the server side setting SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER is used.


EXPECTED BEHAVIOR

SQL*Plus or mentioned background processes should not use all the memory available.

STEPS

The issue can be reproduced at will with the following steps:

1. Have a huge table (millions of records)
2. In one session connected in sqlplus, do:

4.- Observe the memory growing. For example:

$ps -elf|grep sqlplus|grep -v truss
<date> 10:04:00 EDT 2014 43844 sqlplus
...              
$ps -elf|grep sqlplus|grep -v truss
<date>5 10:04:29 EDT 2014 68488 sqlplus
....

Notice that memory grows from 43844 to 68488 for a table less than 1 million rows in 29 secs.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms