kunwar singh

2018-09-21 09:51:31 UTC

hi all,

I am trying to generate some load by executing many OLTP-like inserts

insert into mytab values( :b1); ( purpose of using :b1 is to not flush

shared pool)

SQL> select sql_id , sql_fulltext from v$sql where rownum <30;

SQL_ID |SQL_FULLTEXT

-------------|--------------------------------------------------------------------------------

6hrwjapxkw001|BEGIN :b1:=1974764; END;

8gnkm19fh0004|BEGIN :b1:=1964843; END;

88dnrt2984007|BEGIN :b1:=1980965; END;

1qqk2h4u2000a|BEGIN :b1:=1966014; END;

b1qvjscn4h00c|BEGIN :b1:=1956268; END;

7pxamcspqn00c|BEGIN :b1:=1992032; END;

9nqpjvwaa800h|BEGIN :b1:=1961924; END;

7vdnnakbdh00j|BEGIN :b1:=1969642; END;

But i am getting 1000s of cursors like above and it is thrashing the shared

pool.

Is it a expected behavior, never seen like this earlier.

SQL> select count(9) from v$sql where sql_text like 'BEGIN :b1%';

COUNT(9)

----------

38325

I am trying to generate some load by executing many OLTP-like inserts

insert into mytab values( :b1); ( purpose of using :b1 is to not flush

shared pool)

SQL> select sql_id , sql_fulltext from v$sql where rownum <30;

SQL_ID |SQL_FULLTEXT

-------------|--------------------------------------------------------------------------------

6hrwjapxkw001|BEGIN :b1:=1974764; END;

8gnkm19fh0004|BEGIN :b1:=1964843; END;

88dnrt2984007|BEGIN :b1:=1980965; END;

1qqk2h4u2000a|BEGIN :b1:=1966014; END;

b1qvjscn4h00c|BEGIN :b1:=1956268; END;

7pxamcspqn00c|BEGIN :b1:=1992032; END;

9nqpjvwaa800h|BEGIN :b1:=1961924; END;

7vdnnakbdh00j|BEGIN :b1:=1969642; END;

But i am getting 1000s of cursors like above and it is thrashing the shared

pool.

Is it a expected behavior, never seen like this earlier.

SQL> select count(9) from v$sql where sql_text like 'BEGIN :b1%';

COUNT(9)

----------

38325

--

Cheers,

Kunwar

Cheers,

Kunwar