Re: WebLogic and statement_cache_size
- From: Senthil Ramanujam <senthil.ramanujam@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 20 Aug 2004 07:19:47 -0400
>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.
>
>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
>
>
>
Melanie,
I am not a java programmer either. Interestingly, I ran into the same
scenario only a couple of weeks ago. I guess I was able to figure out
what weblogic's statement_cache is doing undercover.
In a nutshell, if the prepared statement gets closed in the application
side and if the statement_cache parameter is enabled, weblogic silently
caches the parse information(the handler) into its storage(?). how many
statements it caches? it depends on the parameter thats been set. if the
same statement gets executed again, weblogic scans through its storage
and if the statement is found, it uses the handler to execute the
statement. Hence, it avoids parsing. IIRC, there's nothing been done on
the database side.
Following example uses the database codes to depict the weblogic
statement cache behavior(apologies for the long reply).
SQL>
SQL> select table_name, tablespace_name from user_tables where
table_name = 'EMP';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP TSAUTO
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> alter session set sql_trace = true;
Session altered.
SQL>
SQL> -- don't cache the statement
SQL>
SQL> declare
2 l_cursor integer default dbms_sql.open_cursor;
3 l_columnValue varchar2(4000);
4 l_status integer;
5 l_query varchar2(1000) default 'select /* parse */ empno
from emp where empno > 0 and rownum <= 2';
6 begin
7 -- parse
8 dbms_sql.parse(l_cursor, l_query, dbms_sql.native );
9
10 -- define
11 dbms_sql.define_column( l_cursor, 1, l_columnValue, 4000 );
12
13 -- execute
14 l_status := dbms_sql.execute(l_cursor);
15
16 -- fetch
17 loop
18 exit when (dbms_sql.fetch_rows(l_cursor) <= 0);
19 dbms_sql.column_value(l_cursor, 1, l_columnValue);
20 dbms_output.put_line('value: ' || l_columnValue);
21 end loop;
22
23 -- close
24 dbms_sql.close_cursor(l_cursor);
25 end;
26 /
value: 7369
value: 7499
PL/SQL procedure successfully completed.
SQL>
SQL> declare
2 l_cursor integer default dbms_sql.open_cursor;
3 l_columnValue varchar2(4000);
4 l_status integer;
5 l_query varchar2(1000) default 'select /* parse */ empno
from emp where empno > 0 and rownum <= 2';
6 begin
7 -- parse
8 dbms_sql.parse(l_cursor, l_query, dbms_sql.native );
9
10 -- define
11 dbms_sql.define_column( l_cursor, 1, l_columnValue, 4000 );
12
13 -- execute
14 l_status := dbms_sql.execute(l_cursor);
15
16 -- fetch
17 loop
18 exit when (dbms_sql.fetch_rows(l_cursor) <= 0);
19 dbms_sql.column_value(l_cursor, 1, l_columnValue);
20 dbms_output.put_line('value: ' || l_columnValue);
21 end loop;
22
23 -- close
24 dbms_sql.close_cursor(l_cursor);
25 end;
26 /
value: 7369
value: 7499
PL/SQL procedure successfully completed.
>> trace content
-- The above example simulates your first test case. It closes the
cursor as soon as the first one gets executed. It reopens it again when
the same statement gets executed. we see 2 parses here.
select /* parse */ empno
from
emp where empno > 0 and rownum <= 2
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2 0.02 0.01 0 8
0 0
Execute 2 0.00 0.00 0 0
0 0
Fetch 6 0.00 0.00 0 8
0 4
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 10 0.02 0.01 0 16
0 4
>> trace content
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> -- cache the statement here...
SQL> declare
2 l_cursor integer default dbms_sql.open_cursor;
3 l_columnValue varchar2(4000);
4 l_status integer;
5 l_query varchar2(1000) default 'select /* no_parse */
empno from emp where empno > 0 and rownum <= 2';
6 begin
7 -- parse
8 dbms_sql.parse(l_cursor, l_query, dbms_sql.native );
9
10 -- define
11 dbms_sql.define_column( l_cursor, 1, l_columnValue, 4000 );
12
13 -- execute
14 l_status := dbms_sql.execute(l_cursor);
15
16 -- fetch
17 loop
18 exit when (dbms_sql.fetch_rows(l_cursor) <= 0);
19 dbms_sql.column_value(l_cursor, 1, l_columnValue);
20 dbms_output.put_line('value: ' || l_columnValue);
21 end loop;
22
23 -- execute, but reuse the cursor handler(weblogic
statement_cache uses the handler as we used in here)
24 l_status := dbms_sql.execute(l_cursor);
25
26 -- fetch
27 loop
28 exit when (dbms_sql.fetch_rows(l_cursor) <= 0);
29 dbms_sql.column_value(l_cursor, 1, l_columnValue);
30 dbms_output.put_line('value: ' || l_columnValue);
31 end loop;
32
33 -- close
34 dbms_sql.close_cursor(l_cursor);
35 end;
36 /
value: 7369
value: 7499
value: 7369
value: 7499
PL/SQL procedure successfully completed.
>> trace content
-- This simulates your second example. I know here we *dont* close the
cursor(statement). What weblogic does, i think, is when the same
statement gets executed again, weblogic doesnt execute it, but sends
back the cursor handler to the application. hence, the parse is being
avoided.
select /* no_parse */ empno
from
emp where empno > 0 and rownum <= 2
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.02 0.00 0 4
0 0
Execute 2 0.00 0.00 0 0
0 0
Fetch 6 0.00 0.00 0 8
0 4
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 9 0.02 0.01 0 12
0 4
>> trace content
SQL>
SQL> alter session set sql_trace = false;
Session altered.
SQL>
----------------------------------------------------------------
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:
