mattn/go-oci8

Statement caching for a session

stampy88 opened this issue · 16 comments

stampy88 commented

Hi,

I was curious if statement caching could be enabled for a session so that prepared statement could be reused within a session, or if there was some technical issue that prevented. Statement caching can be done without Session Pooling in OCI, so I was curious.

Thanks

MichaelS11 commented

I think you mean something like this:
https://godoc.org/github.com/mattn/go-oci8#example-package--SqlManyInserts

If not, please let us know

cjbj commented

[Edit; Whoops, wrong driver!]

mattn commented

What's happening

sudarshan12s commented
cjbj commented

What's happening

I clicked through email and replied thinking this was another project.

  • 😄1
krismohan commented

Hi, is the original question on OCI, or on go-oci8 driver?

stampy88 commented

It was on the go-oci-8 driver. @sudarshan12s I have modified the code to use operationMode=C.OCI_STMT_CACHE on the OCIBeginSession call, and modified the prepare call and release call as well, e.g.


if rv := C.OCIStmtPrepare2(
	conn.svc,                // service context handle
	stmt,                    // pointer to the statement handle returned
	conn.errHandle,          // error handle
	queryP,                  // statement text
	C.ub4(len(query)),       // statement text length
	queryP,                  // key to be used for searching the statement in the statement cache
	C.ub4(len(query)),       // length of the key
	C.ub4(C.OCI_NTV_SYNTAX), // syntax - OCI_NTV_SYNTAX: syntax depends upon the version of the server
	C.ub4(C.OCI_DEFAULT),    // mode
); rv != C.OCI_SUCCESS {
	if rv == C.OCI_SUCCESS_WITH_INFO{
		v, err:=conn.ociGetError()
		fmt.Println("value was", v)
		return nil, err
	}

	return nil, conn.getError(rv)
}

I am getting a C.OCI_SUCCESS_WITH_INFO and seeing this error ORA-24432: The statement that was returned is not tagged. Have you seen this before?

stampy88 commented

Actually that C.OCI_SUCCESS_WITH_INFO only occurs the 1st time I prepare the statement, subsequent calls is just a normal C.OCI_SUCCESS. Can someone with more OCI experience verify this is expected behavior?

stampy88 commented

It certainly seems like it is working. I can get a PR going shortly @MichaelS11

sudarshan12s commented

It was on the go-oci-8 driver. @sudarshan12s I have modified the code to use operationMode=C.OCI_STMT_CACHE on the OCIBeginSession call, and modified the prepare call and release call as well, e.g.


if rv := C.OCIStmtPrepare2(
	conn.svc,                // service context handle
	stmt,                    // pointer to the statement handle returned
	conn.errHandle,          // error handle
	queryP,                  // statement text
	C.ub4(len(query)),       // statement text length
	queryP,                  // key to be used for searching the statement in the statement cache
	C.ub4(len(query)),       // length of the key
	C.ub4(C.OCI_NTV_SYNTAX), // syntax - OCI_NTV_SYNTAX: syntax depends upon the version of the server
	C.ub4(C.OCI_DEFAULT),    // mode
); rv != C.OCI_SUCCESS {
	if rv == C.OCI_SUCCESS_WITH_INFO{
		v, err:=conn.ociGetError()
		fmt.Println("value was", v)
		return nil, err
	}

	return nil, conn.getError(rv)
}

I am getting a C.OCI_SUCCESS_WITH_INFO and seeing this error ORA-24432: The statement that was returned is not tagged. Have you seen this before?

It will come if non null Key(tag) is passed in OCIStmtPrepare2. Is this key intentional?. It would mean that different stmt texts issued with non-null keys would cause the search based on this key instead of the stmt texts.

sudarshan12s commented

It was on the go-oci-8 driver. @sudarshan12s I have modified the code to use operationMode=C.OCI_STMT_CACHE on the OCIBeginSession call, and modified the prepare call and release call as well, e.g.


if rv := C.OCIStmtPrepare2(
	conn.svc,                // service context handle
	stmt,                    // pointer to the statement handle returned
	conn.errHandle,          // error handle
	queryP,                  // statement text
	C.ub4(len(query)),       // statement text length
	queryP,                  // key to be used for searching the statement in the statement cache
	C.ub4(len(query)),       // length of the key
	C.ub4(C.OCI_NTV_SYNTAX), // syntax - OCI_NTV_SYNTAX: syntax depends upon the version of the server
	C.ub4(C.OCI_DEFAULT),    // mode
); rv != C.OCI_SUCCESS {
	if rv == C.OCI_SUCCESS_WITH_INFO{
		v, err:=conn.ociGetError()
		fmt.Println("value was", v)
		return nil, err
	}

	return nil, conn.getError(rv)
}

I am getting a C.OCI_SUCCESS_WITH_INFO and seeing this error ORA-24432: The statement that was returned is not tagged. Have you seen this before?

It will come if non null Key(tag) is passed in OCIStmtPrepare2. Is this key intentional?. It would mean that different stmt texts issued with non-null keys would cause the search based on this key instead of the stmt texts.

It was on the go-oci-8 driver. @sudarshan12s I have modified the code to use operationMode=C.OCI_STMT_CACHE on the OCIBeginSession call, and modified the prepare call and release call as well, e.g.


if rv := C.OCIStmtPrepare2(
	conn.svc,                // service context handle
	stmt,                    // pointer to the statement handle returned
	conn.errHandle,          // error handle
	queryP,                  // statement text
	C.ub4(len(query)),       // statement text length
	queryP,                  // key to be used for searching the statement in the statement cache
	C.ub4(len(query)),       // length of the key
	C.ub4(C.OCI_NTV_SYNTAX), // syntax - OCI_NTV_SYNTAX: syntax depends upon the version of the server
	C.ub4(C.OCI_DEFAULT),    // mode
); rv != C.OCI_SUCCESS {
	if rv == C.OCI_SUCCESS_WITH_INFO{
		v, err:=conn.ociGetError()
		fmt.Println("value was", v)
		return nil, err
	}

	return nil, conn.getError(rv)
}

I am getting a C.OCI_SUCCESS_WITH_INFO and seeing this error ORA-24432: The statement that was returned is not tagged. Have you seen this before?

It will come if non null Key(tag) is passed in OCIStmtPrepare2. Is this key intentional?. It would mean that different stmt texts issued with non-null keys would cause the search based on this key instead of the stmt texts.

Adding to this, subsequent statement cache hits will not return this OCI_SUCCESS_WITH_INFO , ORA-24432

krismohan commented

Basically it is meant to speed up the lookup using a shorter key instead of a long sql text. So the first time you call Prepare2() with a key, it gives you a brand new stmt as it does not exist in the stmt cache. Later on you'll get the same stmt from the cache for a given tag if you released the previous one with the same tag. Obviously you should not be using the same tag for different sql texts.

stampy88 commented

@MichaelS11 opened a PR for it

cjbj commented

Statement caching is a performance feature for production applications and, as such, doesn't do all cache invalidations that might be desired (by us all) in a dev or test environment. This can be noticeable if a statement is cached, and someone else drop & recreates the relevant table using columns of different datatypes (e.g. to a LOB). The next execution of the statement can give an error. One solution is to drop statements from the cache using OCI_STRLS_CACHE_DELETE if there was an error. See https://github.com/oracle/odpi/blob/00455fadad3f4e8d8c0346630e33145fab17815e/src/dpiStmt.c#L599-L621

stampy88 commented

Thanks for the pointer @cjbj. I'll modify my PR to handle that case.

MichaelS11 commented

Thank you very much for the PR @stampy88 !

  • 👍1