Oracle error : ORA-01000: maximum open cursors exceeded - while reading query from DB during high load

[pawanGattani]

Hi Team,

We are using this library to connect with oracle and do the simple query on index column. We need to support huge load on this query ie. approx 60k request/min.We are properly closing all the cursors but still getting error : "ORA-01000: maximum open cursors exceeded". Is there any connection leak in the below code?

1.) Connection details: host := config.GetApplicationConfig().DatabaseConf.Host username := config.GetApplicationConfig().DatabaseConf.Username password := url.QueryEscape(config.GetApplicationConfig().DatabaseConf.Password) serviceName := config.GetApplicationConfig().DatabaseConf.serviceName connectionString := fmt.Sprintf("oracle://%s:%s@%s/%s", username, password, host, serviceName) Conn, err = sql.Open("oracle", connectionString) Conn.SetMaxIdleConns(config.GetApplicationConfig().DatabaseConf.MaxIdleConnections) Conn.SetMaxOpenConns(config.GetApplicationConfig().DatabaseConf.MaxOpenConnections)

2.) Query getting executed:

select distinct fieldName from table1 spm ,table2 rd ,table3 pr where pr.field1 = spm.field1 and pr.field2 = rd.field2 and spm.field3=:1 and spm.field4=:2.

3.) Preparing query context:

var selDB sql.Rows var err error ctx, cancel := context.WithTimeout(context.Background(), time.Secondtime.Duration(config.GetApplicationConfig().DatabaseConf.ConnectionTimeout)) conn := GetDBConnection()

maxRetries := config.GetApplicationConfig().DatabaseConf.MaxRetries
maxRetryWaitTimeSeconds := config.GetApplicationConfig().DatabaseConf.MaxRetryWaitTimeSeconds
var stmt *sql.Stmt
for attempts := 0; attempts < maxRetries; attempts++ {
	stmt, err = conn.PrepareContext(ctx, query)
	if err != nil {
		if stmt != nil {
			stmt.Close()
		}
		break
	}
	selDB, err = stmt.Query(a, b)
	if err == nil {
		break
	}
	time.Sleep(time.Duration(maxRetryWaitTimeSeconds) * time.Second)
}
if stmt != nil {
	stmt.Close()
}

We are getting error

[donnol]

Is *sql.Rows.Close not called after used?

[sijms]

you need to ensure that you call conn.Close(), stmt.Close() and Rows.Close() after you finish your work with database also note that: 1- use defer is better to assure that you close all open resource before end of the function 2- if you use stmt or rows inside loop don't use defer but call Close() inside the loop 3- if you are inserting rows use stmt + prepare and pass records in loop or better use bulk insert