??OPEN CURSOR?BULK COLLECT
- by Liu Maclean(???)
????T.askmaclean.com?????bulk collect?open cursor???, ????????? ??????:
???? OPEN_CURSOR ????SQL?? ???????.
?????? ?????
???????????????? ?????
test_soruce
create table zengfankun_temp01 as select * from dba_objects;select count(*) from zengfankun_temp01;–12,6826analyze table zengfankun_temp01 compute statistics;
create or replace procedure test_open_cursor istype type_owner is table of zengfankun_temp01.owner%type index by binary_integer;type type_object_name is table of zengfankun_temp01.object_name%type index by binary_integer;type type_object_id is table of zengfankun_temp01.object_id%type index by binary_integer;type type_object_type is table of zengfankun_temp01.object_type%type index by binary_integer;type type_last_ddl_time is table of zengfankun_temp01.last_ddl_time%type index by binary_integer;
l_ary_owner type_owner;l_ary_object_name type_object_name;l_ary_object_id type_object_id;l_ary_object_type type_object_type;l_ary_last_ddl_time type_last_ddl_time;
cursor cur_object isselect owner,object_name,object_id,object_type,last_ddl_timefrom zengfankun_temp01order by owner,object_name,object_type,last_ddl_time;OPEN_START number;OPEN_END number;FETCH_START number;FETCH_END number;beginDBMS_OUTPUT.ENABLE (buffer_size=>null) ;OPEN_START:=dbms_utility.get_time();open cur_object;OPEN_END :=dbms_utility.get_time();dbms_output.put_line(‘OPEN_TIME:’||TO_CHAR(OPEN_END-OPEN_START));loopFETCH_START:=dbms_utility.get_time();fetch cur_object bulk collect intol_ary_owner,l_ary_object_name,l_ary_object_id,l_ary_object_type,l_ary_last_ddl_timelimit 10000;FETCH_END:=dbms_utility.get_time();dbms_output.put_line(‘FETCH_TIME:’||TO_CHAR(FETCH_END-FETCH_START)||’ ROWCOUNT:’||cur_object%rowCount);
exit when cur_object%notfound or cur_object%notfound is null;end loop;end test_open_cursor;
OPEN_TIME:12FETCH_TIME:21 ROWCOUNT:10000FETCH_TIME:3 ROWCOUNT:20000FETCH_TIME:3 ROWCOUNT:30000FETCH_TIME:3 ROWCOUNT:40000FETCH_TIME:3 ROWCOUNT:50000FETCH_TIME:3 ROWCOUNT:60000FETCH_TIME:3 ROWCOUNT:70000FETCH_TIME:3 ROWCOUNT:80000FETCH_TIME:3 ROWCOUNT:90000FETCH_TIME:3 ROWCOUNT:100000FETCH_TIME:3 ROWCOUNT:110000FETCH_TIME:3 ROWCOUNT:120000FETCH_TIME:1 ROWCOUNT:126826
????
OPEN_TIME:0FETCH_TIME:18 ROWCOUNT:10000FETCH_TIME:3 ROWCOUNT:20000FETCH_TIME:3 ROWCOUNT:30000FETCH_TIME:3 ROWCOUNT:40000FETCH_TIME:3 ROWCOUNT:50000FETCH_TIME:3 ROWCOUNT:60000FETCH_TIME:3 ROWCOUNT:70000FETCH_TIME:3 ROWCOUNT:80000FETCH_TIME:3 ROWCOUNT:90000FETCH_TIME:3 ROWCOUNT:100000FETCH_TIME:3 ROWCOUNT:110000FETCH_TIME:3 ROWCOUNT:120000FETCH_TIME:2 ROWCOUNT:126826
SQL?????????, ????????????.??OPEN CURSOR ????0???????????3??.??N? ??????.
???? ?N? ?????????? ??????.
??????????????? ??????????. ?????????10000???
???????????????????clear???, ???????????:
?OPEN CURSOR ?????, PL/SQL????SQL????PARSE SQL????????, ??????OPEN CURSOR????SNAPSHOT SCN ??SCN, ??Oracle?????FETCH?????,????????????????
????FETCH ??????????????,???????Current Block, The most recent version of block , ?????SCN >> Snapshot scn, ????UNDO???? ???SCN ???Best Block ,???Read Consistentcy;???? ???UNDO SNAPSHOT???????????????Best Block??,???????ORA-1555???
????????, ??????????,???????????????char(2000)????, ???????????????,????bulk collect fetch??fetch 10 ???,????????OPEN CURSOR?????PARSE??SQL????????, ??????????fetch bulk collect??????????10????,??”_trace_pin_time”????Server Process?pin CR block???,??????????Fetch Bulk Collect limit 10??10?buffer?pin?
[oracle@nas ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 1 11:36:52 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
http://www.askmaclean.com
SQL> create table maclean (t1 char(2000)) tablespace users pctfree 99;
Table created.
SQL> begin
2 for i in 1..200 loop
3 insert into maclean values('MACLEAN');
4 commit ;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','MACLEAN');
PL/SQL procedure successfully completed.
SQL> select count(*) from maclean;
COUNT(*)
----------
200
SQL> select blocks,num_rows from dba_tables where table_name='MACLEAN';
BLOCKS NUM_ROWS
---------- ----------
244 200
SQL> alter system set "_trace_pin_time"=1 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 3140026368 bytes
Fixed Size 2232472 bytes
Variable Size 1795166056 bytes
Database Buffers 1325400064 bytes
Redo Buffers 17227776 bytes
Database mounted.
Database opened.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL>
SQL>
SQL> declare
2 cursor v_cursor is
3 select * from sys.maclean;
4 type v_type is table of sys.maclean%rowtype index by binary_integer;
5 rec_tab v_type;
6 begin
7 open v_cursor;
8 dbms_lock.sleep(30);
9 loop
10 fetch v_cursor bulk collect
11 into rec_tab limit 10;
12 dbms_lock.sleep(10);
13 exit when v_cursor%notfound;
14 end loop;
15 end;
16 /
?????10046 trace+ pin trace:
PARSING IN CURSOR #47499559136872 len=337 dep=0 uid=0 oct=47 lid=0 tim=1343836146412056 hv=496860239 ad='11a11dbb0' sqlid='4zh7954ftuz2g'
declare
cursor v_cursor is
select * from sys.maclean;
type v_type is table of sys.maclean%rowtype index by binary_integer;
rec_tab v_type;
begin
open v_cursor;
dbms_lock.sleep(30);
loop
fetch v_cursor bulk collect
into rec_tab limit 10;
dbms_lock.sleep(10);
exit when v_cursor%notfound;
end loop;
end;
END OF STMT
PARSE #47499559136872:c=0,e=346,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1343836146412051
=====================
PARSING IN CURSOR #47499559126280 len=25 dep=1 uid=0 oct=3 lid=0 tim=1343836146414939 hv=3296884535 ad='11a11d250' sqlid='2mb1493284xtr'
SELECT * FROM SYS.MACLEAN
END OF STMT
PARSE #47499559126280:c=1999,e=2427,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=2568761675,tim=1343836146414937
EXEC #47499559126280:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2568761675,tim=1343836146415104
????? ? SELECT * FROM SYS.MACLEAN? PARSE ????? , ????FETCH???????pin ????????, ????OPEN CURSOR?????
*** 2012-08-01 11:49:36.424
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 30009361 duration=0 p2=0 p3=0 obj#=-1 tim=1343836176424782
???30s
pin ktewh26: kteinpscan dba 0x10a6202:4 time 1039048805
pin ktewh27: kteinmap dba 0x10a6202:4 time 1039048847
pin kdswh11: kdst_fetch dba 0x10a6203:1 time 1039048898
pin kdswh11: kdst_fetch dba 0x10a6204:1 time 1039048961
pin kdswh11: kdst_fetch dba 0x10a6205:1 time 1039049004
pin kdswh11: kdst_fetch dba 0x10a6206:1 time 1039049042
pin kdswh11: kdst_fetch dba 0x10a6207:1 time 1039049089
pin kdswh11: kdst_fetch dba 0x10a6208:1 time 1039049123
pin kdswh11: kdst_fetch dba 0x10a6209:1 time 1039049159
pin kdswh11: kdst_fetch dba 0x10a620a:1 time 1039049191
pin kdswh11: kdst_fetch dba 0x10a620b:1 time 1039049225
pin kdswh11: kdst_fetch dba 0x10a620c:1 time 1039049260
kdst_fetch???fetch??????? , ??fetch?10??
???????FETCH
FETCH #47499559126280:c=0,e=536,p=0,cr=12,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836176425542
*** 2012-08-01 11:49:46.428
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002694 duration=0 p2=0 p3=0 obj#=-1 tim=134383618642829
????10s
pin kdswh11: kdst_fetch dba 0x10a620d:1 time 1049052211
pin kdswh11: kdst_fetch dba 0x10a620e:1 time 1049052264
pin kdswh11: kdst_fetch dba 0x10a620f:1 time 1049052299
pin kdswh11: kdst_fetch dba 0x10a6211:1 time 1049052332
pin kdswh11: kdst_fetch dba 0x10a6212:1 time 1049052364
pin kdswh11: kdst_fetch dba 0x10a6213:1 time 1049052398
pin kdswh11: kdst_fetch dba 0x10a6214:1 time 1049052430
pin kdswh11: kdst_fetch dba 0x10a6215:1 time 1049052462
pin kdswh11: kdst_fetch dba 0x10a6216:1 time 1049052494
pin kdswh11: kdst_fetch dba 0x10a6217:1 time 1049052525
FETCH #47499559126280:c=0,e=371,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836186428807
??pin 10????, ???fetch ??
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002864 duration=0 p2=0 p3=0 obj#=-1 tim=1343836196431754
pin kdswh11: kdst_fetch dba 0x10a6218:1 time 1059055662
pin kdswh11: kdst_fetch dba 0x10a6219:1 time 1059055714
pin kdswh11: kdst_fetch dba 0x10a621a:1 time 1059055748
pin kdswh11: kdst_fetch dba 0x10a621b:1 time 1059055781
pin kdswh11: kdst_fetch dba 0x10a621c:1 time 1059055815
pin kdswh11: kdst_fetch dba 0x10a621d:1 time 1059055848
pin kdswh11: kdst_fetch dba 0x10a621e:1 time 1059055883
pin kdswh11: kdst_fetch dba 0x10a621f:1 time 1059055915
pin kdswh11: kdst_fetch dba 0x10a6221:1 time 1059055953
pin kdswh11: kdst_fetch dba 0x10a6222:1 time 1059055992
FETCH #47499559126280:c=0,e=385,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836196432274
????
??????? DBA?????
............................
????
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002933 duration=0 p2=0 p3=0 obj#=-1 tim=1343836366495589
pin kdswh11: kdst_fetch dba 0x10a62f6:1 time 1229119497
pin kdswh11: kdst_fetch dba 0x10a62f7:1 time 1229119545
pin kdswh11: kdst_fetch dba 0x10a62f8:1 time 1229119576
pin kdswh11: kdst_fetch dba 0x10a62f9:1 time 1229119610
pin kdswh11: kdst_fetch dba 0x10a62fa:1 time 1229119644
pin kdswh11: kdst_fetch dba 0x10a62fb:1 time 1229119671
pin kdswh11: kdst_fetch dba 0x10a62fc:1 time 1229119703
pin kdswh11: kdst_fetch dba 0x10a62fd:1 time 1229119730
pin kdswh11: kdst_fetch dba 0x10a62fe:1 time 1229119760
pin kdswh11: kdst_fetch dba 0x10a62ff:1 time 1229119787
FETCH #47499559126280:c=0,e=340,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836366496067
??????DBA? 0x10a6203 , ??DBA ? 0x10a62ff
???????DBA??MACLEAN????????,???DBA???Maclean?????
getbfno?????dba???????????
CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
RETURN VARCHAR2
IS
l_str VARCHAR2 (255) DEFAULT NULL;
l_fno VARCHAR2 (15);
l_bno VARCHAR2 (15);
BEGIN
l_fno :=
DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),
'xxxxxxxx'
)
);
l_bno :=
DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),
'xxxxxxxx'
)
);
l_str :=
'datafile# is:'
|| l_fno
|| CHR (10)
|| 'datablock is:'
|| l_bno
|| CHR (10)
|| 'dump command:alter system dump datafile '
|| l_fno
|| ' block '
|| l_bno
|| ';';
RETURN l_str;
END;
/
Function created.
SQL> select getbfno('0x10a6203') from dual;
GETBFNO('0X10A6203')
--------------------------------------------------------------------------------
datafile# is:4
datablock is:680451
dump command:alter system dump datafile 4 block 680451;
SQL> select getbfno('0x10a62ff') from dual;
GETBFNO('0X10A62FF')
--------------------------------------------------------------------------------
datafile# is:4
datablock is:680703
dump command:alter system dump datafile 4 block 680703;
SQL> select dbms_rowid.rowid_block_number(min(rowid)),dbms_rowid.rowid_relative_fno(min(rowid)) from maclean;
DBMS_ROWID.ROWID_BLOCK_NUMBER(MIN(ROWID))
-----------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(MIN(ROWID))
-----------------------------------------
680451
4
SQL> select dbms_rowid.rowid_block_number(max(rowid)),dbms_rowid.rowid_relative_fno(max(rowid)) from maclean;
DBMS_ROWID.ROWID_BLOCK_NUMBER(MAX(ROWID))
-----------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(MAX(ROWID))
-----------------------------------------
680703
4
???????3???:
1.?OPEN CURSOR ?????, PL/SQL????SQL????PARSE SQL????????, ??????OPEN CURSOR????SNAPSHOT SCN ??SCN, ??Oracle?????FETCH?????,????????????????
2.????FETCH ??????????????
3. ???open cursor+ fetch bulk collect???”?????????”
