WebLogic and statement_cache_size
- From: Melanie Caffrey <melanie_caffrey@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Aug 2004 19:00:07 -0700 (PDT)
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
