| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: WebLogic and statement_cache_size
From: Senthil Ramanujam <senthil.ramanujam_at_verizon.net>
Reply-To: oracle-l_at_freelists.org
To: oracle-l_at_freelists.org
Subject: Re: WebLogic and statement_cache_size
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 */ empnofrom 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;
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 */ empnofrom 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;
PL/SQL procedure successfully completed.
>> trace content
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;
value: 7369 value: 7499 value: 7369 value: 7499
PL/SQL procedure successfully completed.
>> trace content
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>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- _________________________________________________________________ Don’t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---------------------------------------------------------------- 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 Fri Aug 20 2004 - 10:45:14 CDT
 |
 |