PGA memory leak with LOBs (Regression)

[nenadnoveljic]

The memory leak bug that was fixed once in the release 2.7.6 started appearing again in 2.7.24. Actually, the bug was reintroduced in 2.7.7

[sijms]

I test the code with go_ora without sqlx shows no leak

[nenadnoveljic]

Here's the test case that leaks memory without sqlx as of 2.7.7. In 2.7.6 it used to work correctly. I also tested agains the latest release 2.7.25.

package main

import (
    "fmt"
    "log"

    "github.com/jmoiron/sqlx"
    go_ora "github.com/sijms/go-ora/v2"
)

const HOST = "localhost"
const PORT = 1521
const SERVICE_NAME = "XE"
const USER = 
const PASSWORD = 

func getMyUsedPGA(db *sqlx.DB) (float64, error) {
    var pga float64
    err := db.Get(&pga, `SELECT 
    sum(p.pga_used_mem)
FROM   v$session s,
    v$process p
WHERE  s.paddr = p.addr AND 
s.sid = userenv('SID')`)
    return pga, err
}

func getTemporaryLobs(db *sqlx.DB) (int, error) {
    var r int
    err := db.Get(&r, `SELECT SUM(cache_lobs) + SUM(nocache_lobs) + SUM(abstract_lobs) 
    FROM v$temporary_lobs l, v$session s WHERE s.SID = l.SID AND s.sid = userenv('SID')`)
    return r, err
}

func main() {
    databaseUrl := go_ora.BuildUrl(HOST, PORT, SERVICE_NAME, USER, PASSWORD, map[string]string{})
    db, err := sqlx.Open("oracle", databaseUrl)
    if err != nil {
        log.Fatalf("failed to connect %s", err)
    }

    for i := 0; i < 10; i++ {
        sql := "BEGIN SELECT to_clob('A') into :c FROM dual; END;"
        var cRet go_ora.Clob
        _, err = db.Exec(sql, go_ora.Out{Dest: &cRet, Size: 8000})

        p, err := getMyUsedPGA(db)
        if err != nil {
            log.Fatalf("failed to get memory %s", err)
        }
        fmt.Printf("PGA [bytes]: %f \n", p)
        l, err := getTemporaryLobs(db)
        if err != nil {
            log.Fatalf("failed to get temporary LOBs %s", err)
        }
        fmt.Printf("Temp LOB count: %d \n", l)

    }
}

The output:

PGA [bytes]: 2763871.000000 
Temp LOB count: 1 
PGA [bytes]: 2886527.000000 
Temp LOB count: 2 
PGA [bytes]: 3017551.000000 
Temp LOB count: 3 
PGA [bytes]: 3148575.000000 
Temp LOB count: 4 
PGA [bytes]: 3279599.000000 
Temp LOB count: 5 
PGA [bytes]: 3410623.000000 
Temp LOB count: 6 
PGA [bytes]: 3541647.000000 
Temp LOB count: 7 
PGA [bytes]: 3672671.000000 
Temp LOB count: 8 
PGA [bytes]: 3803695.000000 
Temp LOB count: 9 
PGA [bytes]: 3934719.000000 
Temp LOB count: 10 

In summary, we have two memory leak issues:

1. without sqlx as of 2.7.7
2. with sqlx, that never worked correctly.

With regards to the latter, sqlx never receives the temp LOB locator, so it can't close it. Do you think go_ora can close it before returning the LOB to the caller?

[sijms]

I make some modification removing sqlx and replace with sql then this is result I get

PGA [bytes]: 8907158.000000 
Temp LOB count: 1 
PGA [bytes]: 9661726.000000 
Temp LOB count: 2 
PGA [bytes]: 9674014.000000 
Temp LOB count: 3 
PGA [bytes]: 9678110.000000 
Temp LOB count: 4 
PGA [bytes]: 9678110.000000 
Temp LOB count: 5 
PGA [bytes]: 9743622.000000 
Temp LOB count: 6 
PGA [bytes]: 9743622.000000 
Temp LOB count: 7 
PGA [bytes]: 9743622.000000 
Temp LOB count: 8 
PGA [bytes]: 9743622.000000 
Temp LOB count: 9 
PGA [bytes]: 9743622.000000 
Temp LOB count: 10 

I will test again with sqlx and share result

[sijms]

result after use sqlx

PGA [bytes]: 8906750.000000 
Temp LOB count: 1 
PGA [bytes]: 8879358.000000 
Temp LOB count: 2 
PGA [bytes]: 8879358.000000 
Temp LOB count: 3 
PGA [bytes]: 8879358.000000 
Temp LOB count: 4 
PGA [bytes]: 8879358.000000 
Temp LOB count: 5 
PGA [bytes]: 8879358.000000 
Temp LOB count: 6 
PGA [bytes]: 8879358.000000 
Temp LOB count: 7 
PGA [bytes]: 8879358.000000 
Temp LOB count: 8 
PGA [bytes]: 8965206.000000 
Temp LOB count: 9 
PGA [bytes]: 8965206.000000 
Temp LOB count: 10 

[nenadnoveljic]

Both test cases are showing the leak, as the number of temp LOB locators is always increasing.

Do you agree?

Just, with sqlx I would expect that the PGA is also increasing.

Have you also replaced sql in the test case?

Without sqlx it's the PL/SQL anonymous block: sql := "BEGIN SELECT to_clob('A') into :c FROM dual; END;

With sqx it has to be a query sql := "SELECT to_clob('A') FROM dual"

[sijms]

ok I will review the original drivers for some solution

[sijms]

result after correction by calling lob.close(0x10000)

PGA [bytes]: 8906750.000000 
Temp LOB count: 1 
PGA [bytes]: 8899838.000000 
Temp LOB count: 2 
PGA [bytes]: 8886550.000000 
Temp LOB count: 0 
PGA [bytes]: 8879086.000000 
Temp LOB count: 1 
PGA [bytes]: 8879086.000000 
Temp LOB count: 2 
PGA [bytes]: 8886550.000000 
Temp LOB count: 0 
PGA [bytes]: 8879086.000000 
Temp LOB count: 1 
PGA [bytes]: 8899566.000000 
Temp LOB count: 2 
PGA [bytes]: 8886550.000000 
Temp LOB count: 0 
PGA [bytes]: 8879086.000000 
Temp LOB count: 1 

the problem now is there is some sort of lag introduce by calling this function for each lob data

[sijms]

I have function in command.go which free all temp lobs at the end of query/exec it will help if you have multiple lobs in same query/exec call.

so I will update the code by adding all these lob locator to temp lob array of the command and let freeTemporayLobs do its job

[sijms]

result after add returned lobs to stmt.temporayLobs

PGA [bytes]: 8906750.000000 
Temp LOB count: 0 
PGA [bytes]: 8879358.000000 
Temp LOB count: 0 
PGA [bytes]: 8879358.000000 
Temp LOB count: 0 
PGA [bytes]: 8879358.000000 
Temp LOB count: 0 
PGA [bytes]: 8879358.000000 
Temp LOB count: 0 
PGA [bytes]: 8899838.000000 
Temp LOB count: 0 
PGA [bytes]: 8899838.000000 
Temp LOB count: 0 
PGA [bytes]: 8899838.000000 
Temp LOB count: 0 
PGA [bytes]: 8899838.000000 
Temp LOB count: 0 
PGA [bytes]: 8899838.000000 
Temp LOB count: 0 

[sijms]

I make a new commit would you please test it before make new release

[sijms]

fixed in v2.7.26

[nenadnoveljic]

Thanks, it works. Do you think that the leak with sqlx can be fixed as well?