(statement cache size = 0) == clear statement cache ?

Many of the configuration options are static, and only take effect when the
server is rebooted. The console should show that, with a blinking yellow icon
when you change the parameter.
Joe

Post by Erik Eide
java.sql.SQLException: ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "CCDB_APPS.MSSG_PROCS" has been invalidated
ORA-04065: not executed, altered or dropped package "CCDB_APPS.MSSG_PROCS"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
which seems to suggest even though the cache size has set to 0, previously cached
statements are not cleared.
Rgs
Erik

Erik Eide

2003-11-17 07:03:20 UTC

In our build of WLS 8.1 (8.1.0.0), only the statement cache type has a yellow icon
indicating a restart is required - not the statment cache size field.

Are there any plan to allow this field to be adjusted at runtime ? Having it would
allow us to set the cache size to 0 whilst performing database maintenance (predominently
re-compiling stored procedures) from under the application, and then re-enabling
it once these are finished.

Just flushing the cache isn't enough as statements may start being cached again
during the maintenance process.

Rgs
Erik

Post by Erik Eide

Post by Erik Eide
Hi
I ran this test with WLS 8.1. I set to the cache size to 5, and I call

a servlet

Post by Erik Eide
which invokes a stored procedure to get the statement cached. I then

recompile

Post by Erik Eide
the proc, set the statement cache size to 0 and re-execute the servlet.

Many of the configuration options are static, and only take effect when the
server is rebooted. The console should show that, with a blinking yellow icon
when you change the parameter.
Joe

Post by Erik Eide
java.sql.SQLException: ORA-04068: existing state of packages has been

discarded

Post by Erik Eide
ORA-04061: existing state of package "CCDB_APPS.MSSG_PROCS" has been

invalidated

Post by Erik Eide
ORA-04065: not executed, altered or dropped package "CCDB_APPS.MSSG_PROCS"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
which seems to suggest even though the cache size has set to 0, previously

cached

Post by Erik Eide
statements are not cleared.
Rgs
Erik

Joe Weinstein

2003-11-17 16:39:21 UTC

Post by Erik Eide
In our build of WLS 8.1 (8.1.0.0), only the statement cache type has a yellow icon
indicating a restart is required - not the statment cache size field.
Are there any plan to allow this field to be adjusted at runtime ? Having it would
allow us to set the cache size to 0 whilst performing database maintenance (predominently
re-compiling stored procedures) from under the application, and then re-enabling
it once these are finished.
Just flushing the cache isn't enough as statements may start being cached again
during the maintenance process.

I will submit this as a feature request. There is no reason why it can't be
dynamic.
Joe

Post by Erik Eide
Rgs
Erik

Post by Erik Eide

Post by Erik Eide
Hi
I ran this test with WLS 8.1. I set to the cache size to 5, and I call

a servlet

Post by Erik Eide
which invokes a stored procedure to get the statement cached. I then

recompile

Post by Erik Eide
the proc, set the statement cache size to 0 and re-execute the servlet.

Many of the configuration options are static, and only take effect when the
server is rebooted. The console should show that, with a blinking yellow icon
when you change the parameter.
Joe

Post by Erik Eide
java.sql.SQLException: ORA-04068: existing state of packages has been

discarded

Post by Erik Eide
ORA-04061: existing state of package "CCDB_APPS.MSSG_PROCS" has been

invalidated

cached

Post by Erik Eide
statements are not cleared.
Rgs
Erik

Joe Weinstein

2003-11-17 17:46:34 UTC

Hi. This seems to be a console bug only. The underlying pool code and MBean interface
already provide for this to work dynamically. This means that you could implement this
programatically now, via the MBean calls, say in a JSP. A Bug Fix request has been
filed for the console.
Joe

Post by Erik Eide
Rgs
Erik

Post by Erik Eide

Post by Erik Eide
Hi
I ran this test with WLS 8.1. I set to the cache size to 5, and I call

a servlet

Post by Erik Eide
which invokes a stored procedure to get the statement cached. I then

recompile

Post by Erik Eide
the proc, set the statement cache size to 0 and re-execute the servlet.

Many of the configuration options are static, and only take effect when the
server is rebooted. The console should show that, with a blinking yellow icon
when you change the parameter.
Joe

Post by Erik Eide
java.sql.SQLException: ORA-04068: existing state of packages has been

discarded

Post by Erik Eide
ORA-04061: existing state of package "CCDB_APPS.MSSG_PROCS" has been

invalidated

cached

Post by Erik Eide
statements are not cleared.
Rgs
Erik

Joe Weinstein

2003-11-17 19:31:24 UTC

Hi Erik!

I just ran some tests which seem to indicate that the console *does*
work, and that the cache *is* cleared when the cache size is dynamically
set to zero, *but*, it doesn't close or affect statements while they are
in-use by applications. These statements will be closed only when the
application closes them, and then they will be uncached.
Can you tell me if your servlet obtains a connection and statements
for every invoke, and closes them? Or does it obtain a pool connection and
statements, and retains and reuses them indefinitely?
Thanks,
Joe

Post by Erik Eide
Hi
I ran this test with WLS 8.1. I set to the cache size to 5, and I call a servlet
which invokes a stored procedure to get the statement cached. I then recompile
the proc, set the statement cache size to 0 and re-execute the servlet.
java.sql.SQLException: ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "CCDB_APPS.MSSG_PROCS" has been invalidated
ORA-04065: not executed, altered or dropped package "CCDB_APPS.MSSG_PROCS"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
which seems to suggest even though the cache size has set to 0, previously cached
statements are not cleared.
Rgs
Erik

Galen Boyer

2003-12-05 23:39:52 UTC

Post by Erik Eide
Hi
I ran this test with WLS 8.1. I set to the cache size to 5, and
I call a servlet which invokes a stored procedure to get the
statement cached. I then recompile the proc, set the statement
cache size to 0 and re-execute the servlet.
java.sql.SQLException: ORA-04068: existing state of packages
has been discarded ORA-04061: existing state of package
"CCDB_APPS.MSSG_PROCS" has been invalidated
ORA-04065: not executed, altered or dropped package
"CCDB_APPS.MSSG_PROCS" ORA-06508: PL/SQL: could not find
program unit being called ORA-06512: at line 1
which seems to suggest even though the cache size has set to 0,
previously cached statements are not cleared.

This is actually an Oracle message. Do the following test.

Open two sqlplus sessions. In one, execute the package. Then,
in the other, drop and recreate that package. Then, go to the
previous window and execute that same package. You will get that
error. Now, in that same sqlplus session, execute that same line
one more time and it goes through.

In short, in your above test, execute your servlet twice and I
bet on the second execution you have no issue.

--
Galen Boyer

Joe Weinstein

2003-12-05 23:58:30 UTC

Post by Erik Eide
Hi
I ran this test with WLS 8.1. I set to the cache size to 5, and
I call a servlet which invokes a stored procedure to get the
statement cached. I then recompile the proc, set the statement
cache size to 0 and re-execute the servlet.
java.sql.SQLException: ORA-04068: existing state of packages
has been discarded ORA-04061: existing state of package
"CCDB_APPS.MSSG_PROCS" has been invalidated
ORA-04065: not executed, altered or dropped package
"CCDB_APPS.MSSG_PROCS" ORA-06508: PL/SQL: could not find
program unit being called ORA-06512: at line 1
which seems to suggest even though the cache size has set to 0,
previously cached statements are not cleared.

This is actually an Oracle message. Do the following test.
Open two sqlplus sessions. In one, execute the package. Then,
in the other, drop and recreate that package. Then, go to the
previous window and execute that same package. You will get that
error. Now, in that same sqlplus session, execute that same line
one more time and it goes through.
In short, in your above test, execute your servlet twice and I
bet on the second execution you have no issue.

Hi. We did some testing offline, and verified that even a standalone
java program:
1 - making and executing a prepared statement (calling the procedure),
2 - waiting while the procedure gets recompiled,
3 - re-executing the prepared statement gets the exception,
BUT ALSO,
4 - closing the statement after the failure, and making a new identical
statement, and executing it will also get the exception!
Joe

Galen Boyer

2004-02-12 16:20:53 UTC

Post by Erik Eide
Hi
I ran this test with WLS 8.1. I set to the cache size to 5,
and I call a servlet which invokes a stored procedure to get
the statement cached. I then recompile the proc, set the
statement cache size to 0 and re-execute the servlet.
java.sql.SQLException: ORA-04068: existing state of packages
has been discarded ORA-04061: existing state of package
"CCDB_APPS.MSSG_PROCS" has been invalidated
ORA-04065: not executed, altered or dropped package
"CCDB_APPS.MSSG_PROCS" ORA-06508: PL/SQL: could not find
program unit being called ORA-06512: at line 1
which seems to suggest even though the cache size has set to
0, previously cached statements are not cleared.

This is actually an Oracle message. Do the following test.
Open two sqlplus sessions. In one, execute the package.
Then, in the other, drop and recreate that package. Then, go
to the previous window and execute that same package. You
will get that error. Now, in that same sqlplus session,
execute that same line one more time and it goes through. In
short, in your above test, execute your servlet twice and I
bet on the second execution you have no issue.

Hi. We did some testing offline, and verified that even a
standalone java program: 1 - making and executing a prepared
statement (calling the procedure), 2 - waiting while the
procedure gets recompiled, 3 - re-executing the prepared
statement gets the exception, BUT ALSO, 4 - closing the
statement after the failure, and making a new identical
statement, and executing it will also get the exception! Joe

I just had the chance to test this within weblogic and not just
sqlplus.

MY SCENARIO:

I had one connection only in my pool. I executed a package.
Then, went into the database and recompiled that package. Next
execution from app found this error. I then subsequently
executed the same package from the app and it was successful.

What the application needs to do is catch that error and within
the same connection, resubmit the execution request. All
connections within the pool will get invalidated for that
package's execution.

Maybe Weblogic could understand this and behave this way for
Oracle connections?

--
Galen Boyer

Joe Weinstein

2004-02-12 16:37:45 UTC

Post by Erik Eide
Hi
I ran this test with WLS 8.1. I set to the cache size to 5,
and I call a servlet which invokes a stored procedure to get
the statement cached. I then recompile the proc, set the
statement cache size to 0 and re-execute the servlet.
java.sql.SQLException: ORA-04068: existing state of packages
has been discarded ORA-04061: existing state of package
"CCDB_APPS.MSSG_PROCS" has been invalidated
ORA-04065: not executed, altered or dropped package
"CCDB_APPS.MSSG_PROCS" ORA-06508: PL/SQL: could not find
program unit being called ORA-06512: at line 1
which seems to suggest even though the cache size has set to
0, previously cached statements are not cleared.

This is actually an Oracle message. Do the following test.
Open two sqlplus sessions. In one, execute the package.
Then, in the other, drop and recreate that package. Then, go
to the previous window and execute that same package. You
will get that error. Now, in that same sqlplus session,
execute that same line one more time and it goes through. In
short, in your above test, execute your servlet twice and I
bet on the second execution you have no issue.

Hi. We did some testing offline, and verified that even a
standalone java program: 1 - making and executing a prepared
statement (calling the procedure), 2 - waiting while the
procedure gets recompiled, 3 - re-executing the prepared
statement gets the exception, BUT ALSO, 4 - closing the
statement after the failure, and making a new identical
statement, and executing it will also get the exception! Joe

I just had the chance to test this within weblogic and not just
sqlplus.

Note, I wasn't using SQL-PLUS, I wrote a standalone program
using Oracle's driver...

Post by Galen Boyer
I had one connection only in my pool. I executed a package.
Then, went into the database and recompiled that package. Next
execution from app found this error. I then subsequently
executed the same package from the app and it was successful.

And this was with the cache turned off, correct?

Post by Galen Boyer
What the application needs to do is catch that error and within
the same connection, resubmit the execution request. All
connections within the pool will get invalidated for that
package's execution.

Have you tried this? Did you try to re-use the statement you had,
or did you make a new one?

Post by Galen Boyer
Maybe Weblogic could understand this and behave this way for
Oracle connections?

It's not likely that we will be intercepting all exceptions
coming from a DBMS driver to find out whether it's a particular
failure, and then know that we can/must clear the statement cache.
Note also that even if we did, as I described, the test program I
ran *did* try to make a new statement to replace the one that
failed, and the new statement also failed.
In your case, you don't even have a cache. Would you verify
in your code, what sort of inline retry works for you?
Joe

Galen Boyer

2004-02-12 19:05:28 UTC

Post by Galen Boyer
I just had the chance to test this within weblogic and not
just sqlplus.

Note, I wasn't using SQL-PLUS, I wrote a standalone program
using Oracle's driver...

Post by Galen Boyer
MY SCENARIO: I had one connection only in my pool. I executed
a package. Then, went into the database and recompiled that
package. Next execution from app found this error. I then
subsequently executed the same package from the app and it was
successful.

And this was with the cache turned off, correct?

My weblogic JDBC Connection Page says
Initial Capacity:
Maximum Capacity: 1
Capacity Increment: 1
Login Delay Seconds: seconds 0
Refresh Period: minutes 1
Supports Local Transaction Checked
Allow Shrinking Checked
Shrink Period: minutes 15 minutes
Prepared Statement Cache Size: 5
XAPrepared Statement Cache Size: 5

Post by Galen Boyer
What the application needs to do is catch that error and
within the same connection, resubmit the execution request.
All connections within the pool will get invalidated for that
package's execution.

Have you tried this? Did you try to re-use the statement you
had, or did you make a new one?

I had one connection in the pool and I was the only one executing
the app. It was a local test environment.

Post by Galen Boyer
Maybe Weblogic could understand this and behave this way for
Oracle connections?

It's not likely that we will be intercepting all exceptions
coming from a DBMS driver to find out whether it's a particular
failure, and then know that we can/must clear the statement
cache. Note also that even if we did, as I described, the test
program I ran *did* try to make a new statement to replace the
one that failed, and the new statement also failed.

I don't believe you need to make a "new" statement. I'm saying
that all the application did was retry the statement.

Post by Joe Weinstein
In your case, you don't even have a cache. Would you verify in
your code, what sort of inline retry works for you? Joe

I don't have an inline retry. I just clicked the button which
tried the operation again.

--
Galen Boyer

Joe Weinstein

2004-02-12 19:18:00 UTC

Post by Galen Boyer
I just had the chance to test this within weblogic and not
just sqlplus.

Note, I wasn't using SQL-PLUS, I wrote a standalone program
using Oracle's driver...

And this was with the cache turned off, correct?

My weblogic JDBC Connection Page says
Maximum Capacity: 1
Capacity Increment: 1
Login Delay Seconds: seconds 0
Refresh Period: minutes 1
Supports Local Transaction Checked
Allow Shrinking Checked
Shrink Period: minutes 15 minutes
Prepared Statement Cache Size: 5
XAPrepared Statement Cache Size: 5

Ok, then I am saying that your two statement caches should be zero, and
the problem *should not* occur.

Post by Galen Boyer
What the application needs to do is catch that error and
within the same connection, resubmit the execution request.
All connections within the pool will get invalidated for that
package's execution.

Have you tried this? Did you try to re-use the statement you
had, or did you make a new one?

I had one connection in the pool and I was the only one executing
the app. It was a local test environment.

Post by Galen Boyer
Maybe Weblogic could understand this and behave this way for
Oracle connections?

It's not likely that we will be intercepting all exceptions
coming from a DBMS driver to find out whether it's a particular
failure, and then know that we can/must clear the statement
cache. Note also that even if we did, as I described, the test
program I ran *did* try to make a new statement to replace the
one that failed, and the new statement also failed.

I don't believe you need to make a "new" statement. I'm saying
that all the application did was retry the statement.

Post by Joe Weinstein
In your case, you don't even have a cache. Would you verify in
your code, what sort of inline retry works for you? Joe

I don't have an inline retry. I just clicked the button which
tried the operation again.

That seems to imply that even a cached statement will work if it
is retried... If that is true, then weblogic doesn't even have
to clear the cache for the original exception. However, we still wouldn't
be able to look for that exception and interpret it to somehow reset
all the parameter values and retry under the covers.
Joe

Galen Boyer

2004-02-12 21:55:32 UTC

Post by Joe Weinstein
In your case, you don't even have a cache. Would you verify in
your code, what sort of inline retry works for you? Joe

I don't have an inline retry. I just clicked the button which
tried the operation again.

That seems to imply that even a cached statement will work if
it is retried...

Yes, this is what I was trying to point out.

Post by Joe Weinstein
If that is true, then weblogic doesn't even have to clear the
cache for the original exception. However, we still wouldn't be
able to look for that exception and interpret it to somehow
reset all the parameter values and retry under the covers. Joe

Oh well, I didn't think so but thought I'd ask. I know we will
have to code this if we want to be able to recompile database
packages without shutting down the Weblogic server.

--
Galen Boyer

Joe Weinstein

2004-02-12 22:14:45 UTC

Post by Joe Weinstein
In your case, you don't even have a cache. Would you verify in
your code, what sort of inline retry works for you? Joe

I don't have an inline retry. I just clicked the button which
tried the operation again.

That seems to imply that even a cached statement will work if
it is retried...

Yes, this is what I was trying to point out.

Oh well, I didn't think so but thought I'd ask. I know we will
have to code this if we want to be able to recompile database
packages without shutting down the Weblogic server.

Yow! Please note that you *Don't* have to cycle weblogic! At the very worst
you should only have to clear the cache, or reset the pool. Whole pools
can be destroyed and recreated with a running weblogic server. See the
weblogic.admin command.
Joe

Galen Boyer

2004-02-12 23:01:35 UTC