LOB prefetch does not work

[kogan69]

Selecting from table with one column of type CLOB turned to be really slow. This is critical problem for us, since our application uses many tables with a clob columns of rather small size.

I've tried to look in the source code for something similar to what's described in:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci07lob.htm#CHDDHFAB
, but could just find the setting of OCI_ATTR_LOBPREFETCH_LENGTH to TRUE in the defLob.go:define function.
I've tried to modify the code myself as it's described in the Oracle's article, to set the OCI_ATTR_LOBPREFETCH_SIZE on the session, but this had no effect on the performance.
I've also tried to set these attributes in the alloc() function of defLob, but also failed miserably :((

May be someone can point me to the right direction so I could implement the change and create a pull request?

With many thanks in advance!
LK

[tgulacsi]

The session default OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE is already set in srv.go#L258 to lobChunkSize = 1MiB.
The OCI_ATTR_LOBPREFETCH_LENGTH is set on the LOB to force OCI to prefetch the LOB's length (metadata).

What is your select? How big those LOBs are? What is the usage pattern?
What are you doing with the returned LOB?

[kogan69]

Hi! Many thanks for the response - much appreciate that. Here's the isolated test that I've created. It creates a table of two columns one of which is an id and the second one is CLOB. The table is filled with 2000 rows where all CLOBs are empty ones. Then I select all rows and iterate through the result set reading the values. In case when I select the data including the CLOB column, the test takes approximately 20 times more than the one without. Below is my test code (just substitute the connection string :) with your one ) package lobtest import ( "database/sql" "time" _ "gopkg.in/rana/ora.v4" "fmt" "testing" ) var ( dbConnectionString = "user/pass@host:1521/DB" ) func TestWithCLob(t *testing.T) { err := createTable() if err != nil { t.Error(err) return } db, err := sql.Open("ora", dbConnectionString) defer db.Close() if err != nil { panic(err) } q := "select aut.id, aut.auto_validation_response from aut" rows, err := db.Query(q) if err != nil { panic(err) } defer rows.Close() start := time.Now() count := 0 for rows.Next() { var rowId string var autoValidationResponse []byte err = rows.Scan(&rowId, &autoValidationResponse) if err != nil { t.Error(err) return } if clobSize := len(autoValidationResponse); clobSize > 0 { fmt.Printf("clob size = %d", clobSize) } count++ } fmt.Printf("processed %d rows in %f seconds\n", count, time.Now().Sub(start).Seconds()) } func TestWithoutCLob(t *testing.T) { err := createTable() if err != nil { t.Error(err) return } db, err := sql.Open("ora", dbConnectionString) defer db.Close() if err != nil { panic(err) } q := "select aut.id from aut" rows, err := db.Query(q) if err != nil { panic(err) } defer rows.Close() start := time.Now() count := 0 for rows.Next() { var id string err = rows.Scan(&id) if err != nil { t.Error(err) return } count++ } fmt.Printf("processed %d rows in %f seconds\n", count, time.Now().Sub(start).Seconds()) } func createTable() error { fmt.Println("table creation started") db, err := sql.Open("ora", dbConnectionString) defer db.Close() if err != nil { panic(err) } db.Exec("drop table aut") _, err = db.Exec("create table aut (id number,auto_validation_response clob)") if err != nil { return err } cmd := " insert all " for i := 0; i < 2000; i++ { cmd += fmt.Sprintf(" into aut (id,auto_validation_response) values (%d, null) ", i) } cmd += " select * from dual" stmt, err := db.Prepare(cmd) if err != nil { return err } _, err = stmt.Exec() if err != nil { return err } fmt.Println("table creation completed") return nil } Many thanks in advance! LK

…

On Wed, Oct 18, 2017 at 6:17 PM, Tamás Gulácsi ***@***.***> wrote: The session default OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE is already set in srv.go#L258 to lobChunkSize = 1MiB. The OCI_ATTR_LOBPREFETCH_LENGTH is set on the LOB to force OCI to prefetch the LOB's length (metadata). What is your select? How big those LOBs are? What is the usage pattern? What are you doing with the returned LOB? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#225 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ALjeJ7-quXFhBTLAS8smQUAL4qHbVpzlks5sthaJgaJpZM4P9vZN> .

[kogan69]

Hi! I think I've managed to find the reason for this. There's no problem with the blob prefetch size/flag. My mistake! It's in rset.go#529 Loop: for _, param := range params { switch param.typeCode { // These can consume a lot of memory. case C.SQLT_LNG, C.SQLT_BFILE, C.SQLT_BLOB, C.SQLT_CLOB, C.SQLT_LBI: fetchLen = MinFetchLen break Loop } } Since the result set contains the blob, the fetchLen drops to minimal value which is in turn causes excessive calling of OCIStmtFetch2 and the performance drops dramatically. I'm closing this issue, but going to open a new one - directly related to the problem. Thanks a lot! LK

…

On Wed, Oct 18, 2017 at 6:17 PM, Tamás Gulácsi ***@***.***> wrote: The session default OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE is already set in srv.go#L258 to lobChunkSize = 1MiB. The OCI_ATTR_LOBPREFETCH_LENGTH is set on the LOB to force OCI to prefetch the LOB's length (metadata). What is your select? How big those LOBs are? What is the usage pattern? What are you doing with the returned LOB? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#225 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ALjeJ7-quXFhBTLAS8smQUAL4qHbVpzlks5sthaJgaJpZM4P9vZN> .