《基于ORACLE SQL优化》读书笔记-游标

show parameter open_cursors; --单个session可并存sessioncursor数

 

select count(*) from v$open_cursor where sid in (select sidfrom v$mystat where rownum<2);--当前session的sessioncursor总数

select name,value from v$sysstat where name ='opened cursorscurrent'; --open 状态的session cursor总数

 

show parameter session_cached_cursors;

 

alter session set events 'immediate trace name ERRORSTACKlevel 3'; --session cursor dump

 

11gR2后,一个session cursor能够缓存在PGA中的必要条件是该cursor所对应的SQL解析和执行的次数超过3次。

Pin这个动作是通过先持有与库相关的Latch,再持有Library cache pin这个enqueue来实现。11gR1之前的版本，把CURSOR_SPACE_FOR_TIME的值设成TRUE,可以减少库缓存相关的LATCH争用，但会给Sharedpool空间带来大的压力。

 

隐式游标：

1.      SQL%FOUND: SQL执行之后记录的改变数是否大于1.（INSERT,UPDATE,DELETE,SELECTINTO）有TRUE,FALSE,NULL

2.      SQL%NOTFOUND

3.      SQL%ISOPEN 对隐式，总是FALSE

4.      SQL%ROWCUNT 记录的改变数量

显示游标：

1.      CURSORNAME%FOUND

2.      CURSORNAME%NOTFOUND

3.      CURSORNAME%ISOPEN

4.      CURSORNAME%ROWCOUNT

参考游标：

定义灵活，open方式灵活（不和固定的SQL绑定在一起）,可作为参数传入。

select count(*) from v$open_cursor where sid in (select sid from v$mystat where rownum<2);
select name,value from v$sysstat where name ='opened cursors current';

show parameter session_cached_cursors;

alter session set events 'immediate trace name ERRORSTACK level 3';


select count(*)  from t52;

select sql_text,cursor_type from v$open_cursor where user_name='TESTER' and sid = 387 and sql_text like 'select count(*)  from t52';


select * from dept;
declare
 dept_no number(4) :=1;
 begin
 delete from dept where deptno = dept_no;
 if sql%found then
 insert into dept values(4,'DATABASE','bejing',current_date);
 end if;
 commit;
 end;
 /
select * from dept;

set serveroutput on size 1000000
declare
dept_name varchar2(14);
vc_message varchar2(4000);
begin
select dname into dept_name from dept where dname ='DATABASE';
exception
when no_data_found then
dbms_output.put_line('NO data found!');
return;
when too_many_rows then
dbms_output.put_line('Too many data');
return;
when others then
vc_message := 'E' ||'_'||sqlcode ||'_'||sqlerrm;
dbms_output.put_line(vc_message);
return;
end;
/


declare 
dept_no number(4) := 3;
begin
delete from dept where deptno =dept_no;
dbms_output.put_line('Number of departments deleted:'||to_char(sql%rowcount));
commit;
end;
/

declare 
cursor c1 is select ename,sal from emp where rownum <11;
my_ename emp.ename%type;
my_salary emp.sal%type;
begin
open c1;
loop 
fetch c1 into my_ename,my_salary;
if c1%found then
dbms_output.put_line('name=' || my_ename||',salary'||my_salary);
else
exit;
end if;
end loop;
close c1;
end;
/


exception
when others then
if c1%isopen = true then
close c1;
end if;

declare 
cursor c1 is select ename,sal from emp where rownum <11;
my_ename emp.ename%type;
my_salary emp.sal%type;
begin
open c1;
loop 
fetch c1 into my_ename,my_salary;
if c1%notfound then
exit;
else
dbms_output.put_line('name=' || my_ename||',salary'||my_salary);
end if;
end loop;
close c1;
end;
/

declare 
cursor c1 is select ename,sal from emp where rownum <11;
my_ename emp.ename%type;
my_salary emp.sal%type;
begin
open c1;
loop 
fetch c1 into my_ename,my_salary;
if c1%found then
dbms_output.put_line('name=' || my_ename||'_'||c1%rowcount);
else
exit;
end if;
end loop;
close c1;
end;
/




create or replace procedure DEMO_EXPLICIT_CURSOR is
--declare
cursor c1 is select ename,sal from emp where rownum <11;
emp_rec emp%rowtype;
begin
open c1;
fetch c1 into emp_rec;
while(c1%found) loop
dbms_output.put_line('name ='|| emp_rec.ename||',salary ='||emp_rec.sal);
fetch c1 into emp_rec;
end loop;
close c1;
exception 
when others then
--o_parm :='E' ||sqlcode ||sqlerrm;
rollback;
-- write log
return;
end DEMO_EXPLICIT_CURSOR;
/