My Oracle Support Banner

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

Last updated on AUGUST 13, 2020

Applies to:

SQL*Plus - Version 12.1.0.1 and later
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
*** Checked for relevance on 16-Feb-2019 ****

Symptoms

Observed on :SQL*Plus 12.1.0.1 on UNIX machines, but could happen with any tool that uses sqlnet connections (client tools 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 or asmb alone 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.

Changes

 setting SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER on server side

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.