Home » RDBMS Server » Server Administration » Does a select statment hold a lock on the table?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273323 is a reply to message #273319] |
Tue, 09 October 2007 23:55   |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
| anacedent wrote on Tue, 09 October 2007 23:43 |
Are you claiming there is no difference between DROP TABLE CARE_LESS and TRUNCATE TABLE CARE_LESS with regard to existing/ongoing SELECT?
|
No，I just want to know when a query is processing, some session drop the table,will it recieve an error?
Yes,I got it,I will recieved an error:
ORA-08103: object no longer exists.
Thanks!
Alan
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273343 is a reply to message #273323] |
Wed, 10 October 2007 01:26 |
 |
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Behaviour depends on Oracle version (you didn't give it, with 4 decimals), parameters (rollback/undo), what you did (you didn't post it), access path (FTS, index) and so on.
Ana's answer is correct, if you just did a "select * from ..." then you get your answer even if someone drop your table. For instance:
-- Session 1
08:19:03 SQL> create table t (col integer);
Table created.
08:19:03 SQL> insert into t values (1);
1 row created.
08:19:03 SQL> insert into t values (2);
1 row created.
08:19:03 SQL> commit;
Commit complete.
08:19:03 SQL> declare
08:19:03 2 cursor c is select col from t;
08:19:03 3 v integer;
08:19:03 4 begin
08:19:03 5 open c;
08:19:03 6 loop
08:19:03 7 dbms_lock.sleep (5);
08:19:03 8 fetch c into v;
08:19:03 9 exit when c%notfound;
08:19:03 10 dbms_output.put_line (to_char(sysdate,'HH24:MI:SS')||' get: '||v);
08:19:03 11 end loop;
08:19:03 12 dbms_lock.sleep (5);
08:19:03 13 close c;
08:19:03 14 end;
08:19:03 15 /
08:19:09 get: 1
08:19:14 get: 2
PL/SQL procedure successfully completed.
-- Sesssion 2
08:19:05 SQL> drop table t purge;
Table dropped.
Regards
Michel
|
|
|
|
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273427 is a reply to message #273410] |
Wed, 10 October 2007 07:34 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Hi,Michel:
This are my test cases,the result are quiet deferents.
db oracle10.2.0.3
1:drop table
2:drop table purge
3:truncate table
SQL> create table t1(id int);
Table created.
SQL> insert into t1 select object_id from all_objects;
50021 rows created.
SQL> commit;
case 1:
--session 1:
declare cursor c is select id from t1;
v int;
con int;
begin
con:=1;
open c;
dbms_lock.sleep(30);
loop
con:=con+1;
fetch c into v;
exit when c%notfound;
end loop;
dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')|| ' rows fetched: '||con);
end;
/
20:11:57SQL> rows fetched: 50023
PL/SQL procedure successfully completed.
--session 2
20:12:10SQL> drop table t1;
case 2:
--session 1
20:12:10 SQL> /
20:12:40SQL> rows fetched: 3302
PL/SQL procedure successfully completed.
--session 2
20:12:20SQL> drop table t1 purge;
case 3
--session 1
19:41:39 SQL> /
declare cursor c is select id from t1;
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 12
--session 2
19:41:39 SQL>truncate table t1;
I found
1:when I do drop ,select will fetch all rows even the table was drop before the fetch started.
2:when I do drop ... purge,select will only fetch fetch 3302 rows .
and 1 and 2 didn't return a ORA-08103 error;
3:when I do truncate table, the fetch result with error ora-08103.
Could you give me a explain for this,I am very confuzed.
Regards!
Alan.
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273430 is a reply to message #273349] |
Wed, 10 October 2007 07:52 |
|
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
There it is:
-- Session 1
14:12:41 SQL> create table t as
14:12:41 2 select *
14:12:41 3 from (select rownum objid, o.* from dba_objects o order by object_id)
14:12:41 4 where rownum <= 6000
14:12:41 5 /
Table created.
14:12:42 SQL> select max(extent_id) extent_id, file_id, block_id, max(blocks) blocks,
14:12:42 2 min(objid) min_id, max(objid) max_id
14:12:42 3 from dba_extents e, t
14:12:42 4 where e.owner=user and e.segment_name='T'
14:12:42 5 and dbms_rowid.rowid_relative_fno(t.rowid) = file_id
14:12:42 6 and dbms_rowid.rowid_block_number(t.rowid) between block_id and block_id+blocks-1
14:12:42 7 group by file_id, block_id
14:12:42 8 order by file_id, block_id
14:12:42 9 /
EXTENT_ID FILE_ID BLOCK_ID BLOCKS MIN_ID MAX_ID
---------- ---------- ---------- ---------- ---------- ----------
0 4 10273 8 1 415
1 4 10281 8 416 1037
2 4 10289 8 1038 1578
3 4 10297 8 1579 2197
4 4 10305 8 2198 2743
5 4 10313 8 2744 3417
6 4 10321 8 3418 3947
7 4 10329 8 3948 4572
8 4 10337 8 4573 5100
9 4 10345 8 5101 5690
10 4 10353 8 5691 6001
11 rows selected.
14:12:46 SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
14:12:46 SQL> set time on
14:12:46 SQL> declare
14:12:46 2 cursor c is select objid from t;
14:12:46 3 v pls_integer;
14:12:46 4 nb pls_integer := 0;
14:12:46 5 begin
14:12:46 6 open c;
14:12:46 7 loop
14:12:46 8 dbms_lock.sleep (0.1);
14:12:46 9 nb := nb + 1;
14:12:46 10 fetch c into v;
14:12:46 11 exit when c%notfound;
14:12:46 12 end loop;
14:12:46 13 dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got '||nb||' rows');
14:12:46 14 close c;
14:12:46 15 exception when others then
14:12:46 16 dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got error '||sqlcode||' at row '||nb);
14:12:46 17 raise;
14:12:46 18 end;
14:12:46 19 /
14:22:57 Got error -8103 at row 5691
declare
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 17
14:22:57 SQL> alter session set events '10046 trace name context off';
Session altered.
-- Session 2
14:12:59 SQL> drop table t purge;
Table dropped.
Actually trace shows that process still read extent per extent for all extents it has in memory from the first access.
Here's an abstract of the file:
PARSING IN CURSOR #4 len=19 dep=1 uid=49 oct=3 lid=49 tim=2341253732 hv=3872157983 ad='1ee9bbbc'
SELECT OBJID FROM T
END OF STMT
PARSE #4:c=15625,e=22047,p=0,cr=60,cu=0,mis=1,r=0,dep=1,og=1,tim=2341253726
BINDS #4:
EXEC #4:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=2341253883
WAIT #3: nam='PL/SQL lock timer' ela= 98179 duration=10 p2=0 p3=0 obj#=48672 tim=2341352853
>>> First rows accessed without file access as they are loaded in SGA <<<
>>> during parse along with segment header and bitmap <<<
FETCH #4:c=0,e=74,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=1,tim=2341353047
WAIT #3: nam='PL/SQL lock timer' ela= 108451 duration=10 p2=0 p3=0 obj#=48672 tim=2341461586
...
WAIT #3: nam='PL/SQL lock timer' ela= 109159 duration=10 p2=0 p3=0 obj#=48672 tim=2356555232
>>> Get the next 4 blocks till the end of first extent <<<
WAIT #4: nam='db file scattered read' ela= 8066 file#=4 block#=10277 blocks=4 obj#=48672 tim=2356564289
FETCH #4:c=0,e=8242,p=4,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2356564389
WAIT #3: nam='PL/SQL lock timer' ela= 100070 duration=10 p2=0 p3=0 obj#=48672 tim=2356664557
FETCH #4:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2356664730
WAIT #3: nam='PL/SQL lock timer' ela= 109160 duration=10 p2=0 p3=0 obj#=48672 tim=2356773991
...
WAIT #3: nam='PL/SQL lock timer' ela= 10669 duration=1 p2=0 p3=0 obj#=48672 tim=2386435180
FETCH #4:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2386435318
WAIT #3: nam='PL/SQL lock timer' ela= 100375 duration=10 p2=0 p3=0 obj#=48672 tim=2386535768
WAIT #3: nam='PL/SQL lock timer' ela= 10666 duration=1 p2=0 p3=0 obj#=48672 tim=2386546508
>>> Get the next extent (8 blocks) <<<
WAIT #4: nam='db file scattered read' ela= 2482 file#=4 block#=10281 blocks=8 obj#=48672 tim=2386549189
FETCH #4:c=0,e=2661,p=8,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2386549281
WAIT #3: nam='PL/SQL lock timer' ela= 99674 duration=10 p2=0 p3=0 obj#=48672 tim=2386649047
...
WAIT #3: nam='PL/SQL lock timer' ela= 10624 duration=1 p2=0 p3=0 obj#=48672 tim=2453081011
FETCH #4:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2453081147
WAIT #3: nam='PL/SQL lock timer' ela= 100691 duration=10 p2=0 p3=0 obj#=48672 tim=2453181910
WAIT #3: nam='PL/SQL lock timer' ela= 10447 duration=1 p2=0 p3=0 obj#=48672 tim=2453192418
>>> Get the next extent <<<
WAIT #4: nam='db file scattered read' ela= 15820 file#=4 block#=10290 blocks=7 obj#=48672 tim=2453208433
FETCH #4:c=0,e=16001,p=7,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2453208531
WAIT #3: nam='PL/SQL lock timer' ela= 99821 duration=10 p2=0 p3=0 obj#=48672 tim=2453308490
This until it needs to re-read the segment header (block 10275):
...
WAIT #3: nam='PL/SQL lock timer' ela= 100392 duration=10 p2=0 p3=0 obj#=48672 tim=2888674996
WAIT #3: nam='PL/SQL lock timer' ela= 11361 duration=1 p2=0 p3=0 obj#=48672 tim=2888686446
>>> Get the next extent <<<
WAIT #4: nam='db file scattered read' ela= 670 file#=4 block#=10345 blocks=8 obj#=48672 tim=2888687315
FETCH #4:c=0,e=820,p=8,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2888687381
WAIT #3: nam='PL/SQL lock timer' ela= 99821 duration=10 p2=0 p3=0 obj#=48672 tim=2888787281
...
WAIT #3: nam='PL/SQL lock timer' ela= 100371 duration=10 p2=0 p3=0 obj#=48672 tim=2951895997
>>> Get segment header block <<<
WAIT #4: nam='db file sequential read' ela= 12644 file#=4 block#=10275 blocks=1 obj#=48672 tim=2951908802
>>> Get segment header (from exception handler) <<<
WAIT #4: nam='db file sequential read' ela= 770 file#=4 block#=10275 blocks=1 obj#=48672 tim=2951909675
----- Call Stack Trace -----
...
----- End of Call Stack Trace -----
FETCH #4:c=46875,e=137753,p=2,cr=3,cu=1,mis=0,r=0,dep=1,og=1,tim=2952033868
EXEC #3:c=156250,e=610828889,p=74,cr=5756,cu=1,mis=0,r=0,dep=0,og=1,tim=2952056020
ERROR #3:err=8103 tim=122964187
At this time it sees that the object no more exists.
Note that I used ASSM, this explain that segment header is at segment block 2 and not segment block 0, the 2 first blocks are used for segment bitmap. A test with MSSM shows that error raises at row 6172 with block size 8k, just in case you want to reproduce you have to build a table with a little more rows. This is still first row of extent 10.
Regards
Michel
[Edit: last sentence]
[Updated on: Wed, 10 October 2007 08:52] Report message to a moderator |
|
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273481 is a reply to message #273443] |
Wed, 10 October 2007 10:52 |
|
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Case 1 is meaningless, it shows that your session 1 ends at 20:11:57 and the drop table is at 20:12:10, so afterward.
In addition, when recyclebin is on table is not dropped but just renamed so you still can query it.
Case 2, as I showed it if your table is not large enough you can query the whole table.
By the way how could you get 50000+ rows the fist time and 3300 the second one if it is the same test?
Case 3, it is the same as drop table purge: extents are invalidated but Oracle only sees it when it reread segment header.
Regards
Michel
[Updated on: Wed, 10 October 2007 10:53] Report message to a moderator |
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273549 is a reply to message #273481] |
Wed, 10 October 2007 21:06 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
By the way how could you get 50000+ rows the fist time and 3300 the second one if it is the same test?
Yes,it is the same test,the only different is case 1 I use drop table ,and case 2 I user drop ...purge.
I try it for some times,the result is the same ,and I alse want case 2 will rise a 8103 error,but it doesn't.
Regards!
Alan
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273570 is a reply to message #273481] |
Wed, 10 October 2007 23:27 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
A new test:
SQL> create table t1 as select object_id from all_objects order by object_id;
Table created.
SQL> set serveroutput on;
sql>declare cursor c is select id from t1;
1 v int;
2 con int;
3 begin
4 con:=1;
5 open c;
6 dbms_lock.sleep(40);
7 loop
8 con:=con+1;
9 fetch c into v;
10 exit when c%notfound;
11 end loop;
12 dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')|| ' rows fetched: '||con);
13 end;
11:03:35 SQL> /
11:04:19 rows fetched: 3287
--the totals rows was 50021.
PL/SQL procedure successfully completed.
--session 2
11:03:42 SQL> drop table t1 purge;
Table dropped.
11:04:03 SQL>
|
|
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273637 is a reply to message #273598] |
Thu, 11 October 2007 02:54 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Here is some infomation and 10046 trace file
15:22:48 SQL> create table t1 as select * from (select rownum objid,o.* from all_objects o order by o.object_id);
Table created.
15:23:25 SQL> select tablespace_name from user_tables where table_name='T1';
TABLESPACE_NAME
------------------------------
TEST
15:24:07 SQL> col tablespace_name for a10;
15:25:08 SQL> select tablespace_name,block_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name='TEST';
TABLESPACE BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN
---------- ---------- ---------- --------- ------
TEST 8192 LOCAL SYSTEM AUTO
15:44:28 SQL> select max(extent_id) extent_id,file_id,block_id,max(blocks) blocks,min(objid)min_id,max(objid) max_id from dba_extents e,t1
15:45:22 2 where e.owner='TEST' and segment_name='T1'
15:45:22 3 and
15:45:22 4 dbms_rowid.rowid_relative_fno(t1.rowid) = file_id
15:45:22 5 and
15:45:22 6 dbms_rowid.rowid_block_number(t1.rowid) between block_id and block_id+blocks-1
15:45:22 7 group by file_id,block_id
15:45:22 8 order by file_id,block_id
15:45:22 9 /
EXTENT_ID FILE_ID BLOCK_ID BLOCKS MIN_ID MAX_ID
---------- ---------- ---------- ---------- ---------- ----------
5 4 777 8 2750 3420
6 4 785 8 3421 3952
7 4 793 8 3953 4577
8 4 801 8 4578 5100
9 4 809 8 5101 5699
10 4 817 8 5700 6250
11 4 825 8 6251 6874
12 4 833 8 6875 7404
13 4 841 8 7405 7989
14 4 849 8 7990 8520
15 4 857 8 8521 9145
EXTENT_ID FILE_ID BLOCK_ID BLOCKS MIN_ID MAX_ID
---------- ---------- ---------- ---------- ---------- ----------
0 4 913 8 1 417
1 4 1001 8 418 1040
2 4 1009 8 1041 1582
3 4 1017 8 1583 2200
4 4 1025 8 2201 2749
16 4 1033 128 9146 17623
17 4 1161 128 17624 26097
18 4 1289 128 26098 34537
19 4 1417 128 34538 43168
20 4 1545 128 9081 50021
21 rows selected.
--session 1
SQL> set serveroutput on;
SQL> set time on;
15:43:56 SQL> alter session set events '10046 trace name context forever,level 1
2';
Session altered.
15:44:50 SQL>
15:46:32 SQL>
15:46:32 SQL> ed
Wrote file afiedt.buf
1 declare
2 cursor c is select object_id from t1;
3 v pls_integer;
4 nb pls_integer := 0;
5 begin
6 open c;
7 dbms_lock.sleep (40);
8 loop
9 nb := nb + 1;
10 fetch c into v;
11 exit when c%notfound;
12 end loop;
13 dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got '||nb||' rows'
);
14 close c;
15 exception when others then
16 dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got error '||sqlco
de||' at row '||nb);
17 raise;
18* end;
15:46:50 19 /
15:47:34 Got 418 rows
PL/SQL procedure successfully completed.
15:47:34 SQL>
--session 2
15:46:55 SQL> drop table t1 purge;
Table dropped.
15:47:14 SQL> spool off;
[Updated on: Thu, 11 October 2007 02:55] Report message to a moderator |
|
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273647 is a reply to message #273641] |
Thu, 11 October 2007 03:22 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Hi,Mechel!
I notice when I:
declare
cursor c is select object_id from t1;
v pls_integer;
nb pls_integer := 0;
begin
open c;
dbms_lock.sleep (40);<< put sleep before fetch start
loop
.
.
.
the result is:
15:46:50 19 /
15:47:34 Got 418 rows
PL/SQL procedure successfully completed.
15:47:34 SQL>
and the context of trace file is quiet defferent from your,the part of 'select object_id from t1' is very small,I post it here:
PARSING IN CURSOR #9 len=210 dep=2 uid=0 oct=3 lid=0 tim=21553527761 hv=864012087 ad='65a5f2b0'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #9:c=0,e=101,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=21553527751
BINDS #9:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=07ed08d0 bln=22 avl=04 flg=05
value=54914
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=07ed08ac bln=24 avl=02 flg=05
value=5
EXEC #9:c=0,e=406,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=21553528517
WAIT #9: nam='db file sequential read' ela= 33809 file#=1 block#=64530 blocks=1 obj#=355 tim=21553562452
FETCH #9:c=0,e=33999,p=1,cr=2,cu=0,mis=0,r=0,dep=2,og=3,tim=21553562584
STAT #9 id=1 cnt=0 pid=0 pos=1 obj=255 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=1 pw=0 time=34015 us)'
STAT #9 id=2 cnt=0 pid=1 pos=1 obj=257 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=1 pw=0 time=33994 us)'
=====================
---[B]this part,I think:[/B]
PARSING IN CURSOR #5 len=24 dep=1 uid=62 oct=3 lid=62 tim=21553563521 hv=346809284 ad='655476e4'
SELECT OBJECT_ID FROM T1
END OF STMT
PARSE #5:c=50072,e=524108,p=290,cr=62,cu=0,mis=1,r=0,dep=1,og=1,tim=21553563509
BINDS #5:
EXEC #5:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=21553563772
--[B]end [/B]
=====================
PARSING IN CURSOR #6 len=132 dep=1 uid=0 oct=3 lid=0 tim=21553564003 hv=2328831744 ad='65f4e068'
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #6:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=21553563994
BINDS #6:
and when I put the sleep into the loop :
declare
cursor c is select object_id from t1;
v pls_integer;
nb pls_integer := 0;
begin
open c;
loop
dbms_lock.sleep (0.01);<< put sleep into loop
.
.
.
I got the error as your test:
16:06:14 SQL> ed
Wrote file afiedt.buf
1 declare
2 cursor c is select object_id from t1;
3 v pls_integer;
4 nb pls_integer := 0;
5 begin
6 open c;
7 loop
8 dbms_lock.sleep (0.01);
9 nb := nb + 1;
10 fetch c into v;
11 exit when c%notfound;
12 end loop;
13 dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got '||nb||' rows'
);
14 close c;
15 exception when others then
16 dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got error '||sqlco
de||' at row '||nb);
17 raise;
18* end;
16:07:13 19 /
16:08:26 Got error -8103 at row 5700
declare
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 17
The trace file is also the same:
=====================
PARSING IN CURSOR #4 len=210 dep=2 uid=0 oct=3 lid=0 tim=21990584442 hv=864012087 ad='65a5f2b0'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
EXEC #4:c=0,e=414,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=21990584432
WAIT #4: nam='db file sequential read' ela= 34095 file#=1 block#=64530 blocks=1 obj#=54915 tim=21990618753
FETCH #4:c=0,e=34267,p=1,cr=2,cu=0,mis=0,r=0,dep=2,og=3,tim=21990618873
--this part we want ,I think
=====================
PARSING IN CURSOR #8 len=24 dep=1 uid=62 oct=3 lid=62 tim=21990619902 hv=346809284 ad='65ca1b80'
SELECT OBJECT_ID FROM T1
END OF STMT
PARSE #8:c=10014,e=511084,p=292,cr=64,cu=0,mis=1,r=0,dep=1,og=1,tim=21990619890
BINDS #8:
EXEC #8:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=21990620160
=====================
--end
PARSING IN CURSOR #7 len=132 dep=1 uid=0 oct=3 lid=0 tim=21990620410 hv=2328831744 ad='65f4e068'
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #7:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=21990620401
BINDS #7:
Regards!
Alan
|
|
|
|
|
|
|
|
|
|
| Re: Does a select statment hold a lock on the table? [message #273680 is a reply to message #273672] |
Thu, 11 October 2007 06:03 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Hi,Michel!
I think I don't understat the trace file,maybe I would read some doc about it:(
How can I find which part are caused by this query.
I mean what the meaning of the number in the trace file,such as:
cursor #1,#2...,does every number represent a sql?
So my sql here is CURSOR #5,so every CURSOR #5 is represent this sql?
Hope you can understand me:(
Regards!
Alan
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jun 26 02:51:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
