WebLogic and statement_cache

From: Melanie Caffrey <melanie_caffrey_at_yahoo.com>
Date: Thu, 19 Aug 2004 19:00:07 -0700 (PDT)
Message-ID: <20040820020008.53213.qmail@web51910.mail.yahoo.com>

Hello Listers,

We are recently in the middle of an upgrade from WebLogic 6.1 to WebLogic 8.1. (Oracle 9.2.0.4)

We tried a little test today to see what kind of effects some of the features in WebLogic would have on parsing SQL in Oracle.

Test 1:

>From Java (using connection pooling)

PrepareStatement call with a single SQL statement.

Followed by a close() connection statement.

And another PrepareStatement call inside of a loop, looping 10 times.

Followed by a close() connection statement.

The parse result: 2. As expected.

Ran the test again, and the parse result increased by 2, again. Resulting in 4 total parses. Each time we ran the test, each statement resparsed (soft or hard, but either way the parse count steadily increased).

Test 2:

Set the statement_cache_size parameter in the WebLogic config to 10.

Then began, again.

The parse result: 2. As expected.

With each subsequent test, 2, forever more. Never more was there an increase in the parsing.

Most of the documentation I found on the statement_cache_size parameter states the following:

"The number of cached prepared statements to keep for an entire connection pool. The default value is 100. Any non-negative integer is a valid value. When a statement is cached, it helps performance, because a statement is retrieved from the cache if a matching statement is found, instead of creating a new prepared statement, which is a more costly operation. The statement cache size does not change the programming model, only the performance of the application. The statement cache size is the number of cached statements for the entire pool, not per connection."

BTW, the close() statement at the end of each prepareStatement in our test scenario was not removed.

The caching the docs are talking about appear to take place on the app server side.

When I monitored what was going on the DB side, the executions and fetches steadily increased, as expected. And the number of parses did not change after the initial run.

I like the fact that this is working as WebLogic said it would. I'm just curious as to what's happening on the Oracle side to help this along.

Is anyone familiar with this? And if so, would you care to share your theories/facts as to what Oracle is doing behind the scenes here?

I'm not a Java programmer. So, I'm quite certain that there is a lot I am overlooking here. On both the Java and the Oracle side.

TIA,
Melanie

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Aug 19 2004 - 20:55:49 CDT

WebLogic and statement_cache_size