Friday, July 24, 2009

Dynamic SQL: cursor: pin S wait on X

I worked with load testing on oracle database with OLTP (query many times in the same sql statements) and found high waited events on cursor: pin S wait on X and cursor: mutex X
cursor: pin S wait on X:
A session waits on this event when requesting a mutex for sharable operations related to pins(such as executing a cursor)), but the mutex cannot be granted because it is being held exclusively by another session (which is most likely parsing the cursor).
read ideas from Tanel Poder and Alex Fatkulin
And then i investigated PL/SQL code and found dynamic sql, so i made test (wrote pl/sql with dynamic sql) and check waited events.

Example Test

Dynamice SQL:
create or replace function build_query_stmt (obj_id IN number) return varchar2
is
v_stmt varchar2(2000);
begin
v_stmt := 'select count(1) c from TMP01 ';
v_stmt := v_stmt || ' where object_id=' || obj_id ;

return v_stmt;
end;
/

create or replace procedure A (obj_id IN number)
is
type t_refcur is REF CURSOR;
crs t_refcur;
v_stmt varchar2(2000);
v_c number;
begin
v_stmt := build_query_stmt(obj_id);
open crs for v_stmt;
< < a_loop > >
LOOP
begin
fetch crs into v_c;
exit a_loop when crs%NOTFOUND;
dbms_output.put_line(v_c);
end;
end loop;
close crs;
end;
/

Test many times: exec a(:p);
Check:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
cursor: pin S wait on X 19,679 216 11 49.8 Concurrenc
CPU time 183 42.1
kksfbc child completion 445 22 50 5.1 Other
cursor: mutex S 1,017 1 1 0.2 Concurrenc
latch: library cache 1,886 1 0 0.2 Concurrenc
Static SQL:
create or replace procedure B (obj_id IN number)
is
v_c number;
begin
execute immediate 'select count(*) from TMP01 where object_id = :p1' into v_c using obj_id ;
dbms_output.put_line(v_c);
end;
/

Test many times: exec b(:p);
Check:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 80 55.7
cursor: pin S wait on X 4,364 49 11 33.6 Concurrenc
kksfbc child completion 234 12 50 8.2 Other
latch: library cache 2,556 1 1 1.0 Concurrenc
latch: shared pool 1,971 1 0 0.4 Concurrenc
Dynamic SQL use waited time event: "cursor: pin S wait on X" higher Static SQL, so be careful to use it.

2 comments:

yds said...

Interesting topic. Would the following proc even better?

create or replace procedure proc_C (obj_id IN number)
is
v_c number;
begin
select count(*) into v_c from TMP01 where object_id = obj_id;
dbms_output.put_line(v_c);
end;
/
- Denis

Surachart Opun said...

Just issue("cursor: pin S wait on X" high) to concern, when use Dynamic SQL.
And avoid to use it(Dynamic SQL).