key settings for jmeter3.3 JDBC connection to Oracle database (11.2.0.4)

Tested both URL works:
jdbc:oracle:thin:@//localhost:1523/ora11g
jdbc:oracle:thin:@localhost:1523/ora11g

JDBC URL takes forms:

• jdbc:oracle:thin:@host:port /databaseName
• jdbc:oracle:thin:@host:port :serviceName

Note that the "validation query" in my case should choose "select 1 from dual", otherwise I was getting "Cannot create PoolableConnectionFactory (ORA-00923: FROM
keyword not found where expected"

ojdbc8.jar is the jdbc driver downloaded from oracle.com
 

For SQL query , note that
1. There is no semi-colon (;) at the end of the sql statment, otherwise there is ora error code saying "ORA-00911: invalid character"



Above are key things I encountered when first time playing jmeter & oracle.

Also tested okay for 12.2c PDB.

SQL commands for getting bind variable value

$ cat chk_bind_value.sql
Prompt Enter SQL_ID


select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured
from V$SQL_BIND_CAPTURE i
where SQL_ID = '&1'



$ cat chk_hist_bind_value.sql
set pages 1000
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured  from dba_hist_sqlbind
where SQL_ID='&1' order by snap_id
/

Using bind variables in SQL*Plus

mark two links for quick reference

Declare bind variables in SQL*Plus

Using bind variables in SQL*Plus

You can declare a bind variable in SQL*Plus though, and select into that:

var l_test_quote varchar2(80); -- or whatever type/size you need
var l_test_id varchar2(80);

declare
    l_id varchar2(80) :='test123';
begin
    select test_quote, test_id
    into :l_test_quote, :l_test_id
    from order_link
    where id = l_id;
end;
/

print l_test_quote
print l_test_id
Note the : before the references to the variables defined outside the block, indicating they are bind variables. l_id is declared inside the block so it does not have a preceding :.

In this case you could also define l_id outside the block, and avoid PL/SQL while still using a bind variable for that:

var l_id varchar2(80);

exec :l_id := 'test123';

select test_quote, test_id
from order_link
where id = :l_id;
Because the main query isn't PL/SQL any more (although the exec is; that's just a shorthand for a one-line anonymous block), you don't need to select ... into so you don't need to declare those variables.

amazing server side result cache (2) - benchmark

select sysdate from dual for 1 million times  -- 40 seconds

set serveroutput on
DECLARE
   x NUMBER := 1000000;
   CRM_DATE varchar2(20) ;
   n1 number;
BEGIN
    n1 := dbms_utility.get_time;

   FOR i IN 1..x LOOP

--CRM_DATE :=TO_char(SYSDATE);
      select sysdate into CRM_DATE from dual;

   END LOOP;

   dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
4041

count a empty table for 1 millions times - 61.28  seconds

set serveroutput on
DECLARE
   x NUMBER := 1000000;
   CRM_DATE varchar2(20) ;
   n1 number;
   c number;
BEGIN
    n1 := dbms_utility.get_time;

   FOR i IN 1..x LOOP

--CRM_DATE :=TO_char(SYSDATE);
     -- select sysdate into CRM_DATE from dual;
SELECT COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;

   END LOOP;

   dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/

6128

18057 for looping 3 millions times



now test a function and cache it

CREATE OR REPLACE FUNCTION count_crm_date
  RETURN NUMBER
  RESULT_CACHE
AS
  c number;
BEGIN
  SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
  RETURN c;
END count_crm_date;
/

ensure it works , meanwhile cached it.

> var r number;

> exec :r :=count_crm_date(1);

PL/SQL procedure successfully completed.

> set serveroutput on

> print r

         R

----------

         0

create a procedure to loop the function.

CREATE OR REPLACE PROCEDURE run_test AS

  l_start NUMBER;

  l_loops NUMBER := 1000000;

  l_value number;

BEGIN

  l_start := DBMS_UTILITY.get_time;

  

  FOR i IN 1 .. l_loops LOOP

    l_value := count_crm_date;

  END LOOP;

  

  DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  

  l_start := DBMS_UTILITY.get_time;

  

  FOR i IN 1 .. l_loops LOOP

    l_value := count_crm_date;

  END LOOP;

  

  DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

END run_test;

/

Procedure created.

> EXEC run_test;

First Loop: 86 hsecs (0.86 second)

Second Loop: 81 hsecs  (0.81 second) 

PL/SQL procedure successfully completed.

That is much faster , 0.81 seconds compare to not-cached 61 seconds, about 75x faster

SELECT DBMS_RESULT_CACHE.status FROM dual;

STATUS

------------------------------------------------------------------------------------------------------------------------------------------------------

ENABLED

SYS@VCRMDV> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);

PL/SQL procedure successfully completed.

> set serveroutput on

> l

  1* SELECT DBMS_RESULT_CACHE.status FROM dual

> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);

R e s u l t   C a c h e   M e m o r y   R e p o r t

[Parameters]

Block Size          = 1K bytes

Maximum Cache Size  = 5248K bytes (5248 blocks)

Maximum Result Size = 262K bytes (262 blocks)

[Memory]

Total Memory = 5516768 bytes [1.028% of the Shared Pool]

... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]

....... Memory Mgr = 200 bytes

....... Cache Mgr  = 208 bytes

....... Bloom Fltr = 2K bytes

....... State Objs = 2896 bytes

... Dynamic Memory = 5511416 bytes [1.027% of the Shared Pool]

....... Overhead = 137464 bytes

........... Hash Table    = 64K bytes (4K buckets)

........... Chunk Ptrs    = 24K bytes (3K slots)

........... Chunk Maps    = 12K bytes

........... Miscellaneous = 35064 bytes

....... Cache Memory = 5248K bytes (5248 blocks)

........... Unused Memory = 0 blocks

........... Used Memory = 5248 blocks

............... Dependencies = 2 blocks (2 count)

............... Results = 5246 blocks

................... PLSQL   = 5246 blocks (5246 count)

PL/SQL procedure successfully completed.

open another session to see if caching works globally.

--in another session

> conn perfstat

Enter password:

Connected.

> set serveroutput on

> exec run_test;

First Loop: 92 hsecs

Second Loop: 83 hsecs

PL/SQL procedure successfully completed.

It works !

loopig for 3000000 times,

exec run_test;

First Loop: 267 hsecs

Second Loop: 276 hsecs

PL/SQL procedure successfully completed.

SQL> SELECT *  FROM V$RESULT_CACHE_STATISTICS;

        ID

----------

NAME

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

         1

Block Size (Bytes)

1024

         2

Block Count Maximum

3936

         3

Block Count Current

32

         4

Result Size Maximum (Blocks)

196

         5

Create Count Success

1

         6

Create Count Failure

0

         7

Find Count

6000001

         8

Invalidation Count

0

         9

Delete Count Invalid

0

        10

Delete Count Valid

0

        11

Hash Chain Length

1

        12

Find Copy Count

6000001

        13

Latch (Share)

0

13 rows selected.

I also captured AWR snapshot before and after executing test .

From AWR report, I  don't  see  millions of executions of such query.

This helped me to solve a production issue due to such query selecting from DUAL table, which DUAL table is a dictionary table causing latch contention (row cache objects) in extreme high concurrency environment. 

Nice feature in 11gR2 comes default - no caching related parameter to adjust in my case. 

amazing server side result cache (1)

We are facing a functioned called about 5 thousands times per second, by hundreds of stored procedure from 20+ app server.

The result is static, so server side result cache come into my picture.

started from  PL/SQL result cache after I learned it is different from sql*plus.  (I am not PL/SQL expert).

a.)  RESULT_CACHE is the key word when defining a funciton.


i.e below is wrong

set serveroutput on
DECLARE
   x NUMBER := 3000000;
   CRM_DATE varchar2(20) ;
   n1 number;
   c number;
BEGIN
    n1 := dbms_utility.get_time;

   FOR i IN 1..x LOOP

--CRM_DATE :=TO_char(SYSDATE);
     -- select sysdate into CRM_DATE from dual;
SELECT /*+ RESULT_CACHE */ COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
   END LOOP;

   dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/

b.)  DBMS_RESULT_CACHE.memory_report is immediate tool I like, as long as I see few rows as output -- nothing is cached.

-- simple function for test

CREATE OR REPLACE FUNCTION count_crm_date
  2    RETURN NUMBER
  3    RESULT_CACHE
  4  AS
  5    c number;
  6  BEGIN
  7    SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
  8    --C :=P1;
  9    -- Pause for 1 second.
 10    --DBMS_LOCK.sleep(1);
 11    RETURN c;
 12  END count_crm_date;
 13  /

Function created.


set serveroutput on

> var r number;
> print r;

         R
----------


> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 166400 bytes [0.031% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 161048 bytes [0.030% of the Shared Pool]
....... Overhead = 128280 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 25880 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)

PL/SQL procedure successfully completed.

> EXEC DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

>  EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 5352 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

> exec :r :=count_crm_date;

PL/SQL procedure successfully completed.

> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 165152 bytes [0.031% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 159800 bytes [0.030% of the Shared Pool]
....... Overhead = 127032 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 24632 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 28 blocks
........... Used Memory = 4 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 1 blocks
................... PLSQL   = 1 blocks (1 count)

PL/SQL procedure successfully completed.

> print r;

         R
----------
         0