Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: WebLogic and statement_cache_size

Re: WebLogic and statement_cache_size

From: M Rafiq <rafiq9857_at_hotmail.com>
Date: Fri, 20 Aug 2004 11:44:43 -0400
Message-ID: <BAY2-F35YQwK0gIRRJz00096249@hotmail.com>

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 */ 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

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

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_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
-----------------------------------------------------------------

_________________________________________________________________
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US