http://freekdhooge.wordpress.com/2007/12/25/can-a-select-block-a-truncate/
Can a select block a truncate ?
Filed under: Uncategorized — dhoogfr @ 14:41
Recently a discussion was ongoing on the oracle-L list about the question if a select could block a truncate or not.
The first reaction that I had was that, when a select would be active reading from a table, the truncate would fail with the message: “resource busy and acquire with NOWAIT specified”. My reasoning behind this was that a truncate is a ddl statement, which generates no undo for the “deleted” records. So to guarantee that the select would be read consistent, Oracle would have to fail the truncate.
But when discussing this at the office, my colleague Geert claimed that a select placed no protection on the structure of a table and that it would be the select statement that would fail (with the message that the object does no longer exists).
At these moments, there is only 1 thing that you can realy do, and that is to test it. So I did and I found out that is not so strait forward.
Here we go:
In a first session, I create a table with a size that will guarantee me a full table scan time that is big enough to be able to run some statements in a second session.
(The oracle db is running inside a vmware instance on my laptop, so disk access is not so fast as on a real server).
After the table has been created I run a select statement:
C:\>sqlplus fdh@loki.ragna.vm
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 22 10:47:49 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
fdh@LOKI> drop table test;
Table dropped.
fdh@LOKI> set timing on
fdh@LOKI> set time on
10:48:02 fdh@LOKI> create table test(veld1 char(2000));
Table created.
Elapsed: 00:00:00.03
10:48:10 fdh@LOKI> insert into test select 'x' from all_objects a, all_objects b where rownum commit;
Commit complete.
Elapsed: 00:00:00.00
10:49:46 fdh@LOKI> select bytes/1024/1024 from user_segments where segment_name = 'TEST';
BYTES/1024/1024
---------------
143
Elapsed: 00:00:00.04
10:49:59 fdh@LOKI>
10:50:46 fdh@LOKI> select count(*) from test;
COUNT(*)
----------
52000
Elapsed: 00:00:16.53
After I created the table, I started a second session (as a different user), in which I will check if there are anly locks held by my first session after which I will truncate the table.
Here you can see the query that will be used to check for locks (modified script from Tim Gorman)
C:\>sqlplus "sys@loki.ragna.vm as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 22 10:50:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
sys@LOKI> set timing on
sys@LOKI> set time on
10:50:33 sys@LOKI> host type c:\uptime\scripts\toolbox\locks2.sql
column os_user_name format a20
column process format a10
column oracle_username format a30
column owner format a30
column object_name format a30
column sid format 99999
column type format a30
column lmode format a15
column request format a15
column block format a15
set linesize 120
select os_user_name, lo.process, oracle_username, l.sid, s.sid, s.serial#, s.PADDR,
decode( l.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
l.type
) type,
decode( l.LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
l.lmode
) lmode,
decode( l.REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
l.request
) request,
decode( l.BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
l.block
) block,
owner, object_name
from sys.v_$locked_object lo, dba_objects do, sys.v_$lock l, v$session s
where lo.OBJECT_ID = do.OBJECT_ID
and l.SID = lo.SESSION_ID
and l.sid = s.sid;
10:50:41 sys@LOKI>
I then start the select in the first session and directly thereafter run the locks2 query in the second session, followed by the truncate statement.
You can use the timestamps to see the order of the statements:
Session A:
10:50:46 fdh@LOKI> select count(*) from test;
COUNT(*)
----------
52000
Elapsed: 00:00:16.53
10:51:11 fdh@LOKI>
Session B:
10:50:42 sys@LOKI> @locks2 no rows selected Elapsed: 00:00:00.03 10:51:00 sys@LOKI> truncate table fdh.test; Table truncated. Elapsed: 00:00:02.53 10:51:09 sys@LOKI>
Ok, this shows that both the select as the truncate succeeded and that the truncate finished before the select did.
I then ran the test a second time, now leaving less time between the start of the select and the truncate.
Session A:
10:51:12 fdh@LOKI> insert into test select 'x' from all_objects a, all_objects b where rownum commit;
Commit complete.
Elapsed: 00:00:00.00
10:53:30 fdh@LOKI> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-08103: object no longer exists
Elapsed: 00:00:03.46
10:53:43 fdh@LOKI>
Session B:
10:53:41 sys@LOKI> truncate table fdh.test; Table truncated. Elapsed: 00:00:01.03 10:53:43 sys@LOKI>
As you can see, the select statement this time failed with the message “ORA-08103: object no longer exists”.
When searching on metalink for this I found a note (170185.1), explaining that this error may occur on the next block read after a truncate, because the truncate increases the dataobj# in obj$ by 1.
SQL> select dataobj# from obj$ where name = 'TEST' and owner# = 46;
DATAOBJ#
----------
45976
SQL> truncate table fdh.test;
Table truncated.
SQL> select dataobj# from obj$ where name = 'TEST' and owner# = 46;
DATAOBJ#
----------
45977
note: you will only see this when the truncated table contained data
1 point for Geert
However, I wanted to test this “may occur on the next block read” some further, so I set up a test in which I used a ref cursor to open a query on a table, and then truncate this table from a second session.
Session A:
C:\>sqlplus fdh/fdh@loki.ragna.vm SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 22 17:22:04 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning and Data Mining options fdh@LOKI> drop table test; Table dropped. fdh@LOKI> create table test (veld1 char(2000), veld2 char(2000), veld3 char(2000), veld4 char(2000)) tablespace test pctfree 0; Table created. fdh@LOKI> insert into test select 'x', 'x', 'x', 'x' from all_objects where rownum commit; Commit complete. fdh@LOKI> set serveroutput on fdh@LOKI> set arraysize 1 fdh@LOKI> var x refcursor fdh@LOKI> BEGIN 2 3 open :x for select veld1 from test; 4 5 END; 6 / PL/SQL procedure successfully completed.
Session B:
sys@LOKI> truncate table fdh.test; Table truncated.
Session A:
fdh@LOKI> DECLARE
2
3 l_veld1 char(2000);
4 l_cnt number(3,0) := 0;
5
6 BEGIN
7
8 BEGIN
9
10 loop
11
12 fetch :x into l_veld1;
13 exit when :x%NOTFOUND;
14 l_cnt := l_cnt + 1;
15
16 end loop;
17
18 EXCEPTION
19 when others then
20 dbms_output.put_line(SQLERRM);
21
22 END;
23
24 dbms_output.put_line('fetched rows: ' || l_cnt);
25
26 END;
27 /
ORA-08103: object no longer exists
fetched rows: 0
PL/SQL procedure successfully completed.
As you can see, running the truncate between the parsing of the query, and the actual fetching results in failure of the fetch.
Time for the second test, in which I will first fetch a single record and then run the truncate.
Session A:
fdh@LOKI> drop table test;
Table dropped.
fdh@LOKI>
fdh@LOKI> create table test (veld1 char(2000)) tablespace test pctfree 0;
Table created.
fdh@LOKI>
fdh@LOKI> insert into test select 'x' from all_objects where rownum
fdh@LOKI> commit;
Commit complete.
fdh@LOKI> set serveroutput on
fdh@LOKI> -- set the arraysize to 1 to prevent oracle from "prefetching" records
fdh@LOKI> set arraysize 1
fdh@LOKI> var x refcursor
fdh@LOKI>
fdh@LOKI> BEGIN
2
3 open :x for select veld1 from test;
4
5 END;
6 /
PL/SQL procedure successfully completed.
fdh@LOKI>
fdh@LOKI> DECLARE
2
3 l_veld1 char(2000);
4
5 BEGIN
6
7 fetch :x into l_veld1;
8 dbms_output.put_line('veld1: ' || trim(l_veld1));
9
10 END;
11 /
veld1: x
PL/SQL procedure successfully completed.
Session B:
sys@LOKI> truncate table fdh.test; Table truncated.
Session A:
fdh@LOKI> DECLARE
2
3 l_veld1 char(2000);
4 l_cnt number(3,0) := 0;
5
6 BEGIN
7
8 BEGIN
9
10 loop
11
12 fetch :x into l_veld1;
13 exit when :x%NOTFOUND;
14 l_cnt := l_cnt + 1;
15
16 end loop;
17
18 EXCEPTION
19 when others then
20 dbms_output.put_line(SQLERRM);
21
22 END;
23
24 dbms_output.put_line('fetched rows: ' || l_cnt);
25
26 END;
27 /
ORA-08103: object no longer exists
fetched rows: 315
PL/SQL procedure successfully completed.
This time I did not get the exception immediate. Instead I was able to fetch 315 records after the table had been truncated, before getting the error.
This number was consisted over several test runs. Also when I changed the record size by adding aditional columns to the table, the number of rows that would be fetched before the error occurred, decreased. When I ran the test with 2 char(2000) columns, I could fetch 152 records before the error would appear and with 4 columns, 78 records.
According to the metalink note, the error would occur on the next block read. With the query I ran, oracle uses a full table scan to get the records. My db_file_multiblock_read_count is 16, so I would expect oracle to perform 1 fetch (arraysize is set to 1) and load 16 blocks into the db cache. The block_size for the holding tablespace is 8KB, so for a table with a single char(2000) column, I would have 4 records per block.
Given this information I expected that the fetch would fail after 63 records (4 * 16 – 1 record from the initial fetch before the truncate). But the test shows a consistent number of 315 records, not 63. A level 8 trace file showed that I was actually using a mbrc of 8, not 16, so I would expect the error to appear even sooner.
After getting these results I suspected oracle of cleaning up some of the db blocks (much like oracle cleans the transaction id from the db blocks after a commit). But a test with dumping db blocks (using alter system dump datafile … block ..) to check the dataobj# showed that only the first block (the header block) was updated with the new dataobj# and that the rest of the blocks would be left unchanged.
At this point I have no real explanation as for why the error occurs at this number of records fetched.
I suspect oracle of storing the locations of the blocks it has to scan during a full table in a kind of array with a limited length. After this array has been processed, Oracle would use the dataobj# value (and other keys) to get the next batch of block locations. At that moment the “object no longer exists” error would be thrown as the referenced dataobj# value no longer exists (it has been increased by 1).
But at this moment this is just pure speculation.
One thing that did became clear to me is that a truncate should not be regarded as just a cheap way to delete all the records from a table. When other sessions (end users) are reading from this table, they risk getting an error… .
Oh yeah: the truncate would get the “resource busy and acquire with NOWAIT specified” if there other transactions (updates, deletes, inserts or ddl statements), which will hold TM or DDL locks) are busy on the table you wanted to truncate.