Friday, February 12, 2016

SELECTs Do Not Block UPDATEs

I went on my first DBA course in 1997 and the lecturer there explained that readers do not block writers in an Oracle database. I had an issue recently which appeared to contradict this so I have reproduced it below in an Oracle 11.2.0.1 database.
 
I noticed in OEM that there was some issue in the database. As usual, click on the images to enlarge them and bring them into focus if you need to:


I looked at the Blocking Sessions screen and saw that Andrew was blocking Fred:


I clicked on the link to see the SQL which Fred was running:


I clicked on the link to see the SQL which Andrew was running:


So it seemed that: 

SELECT COUNT(*)
FROM DBA_TABLES
 
was blocking:
 
update andrew.tab1
set col1 = 2
where col1 = 1
 
As you might have guessed, I set this test up on purpose and this is what really happened. Andrew created a table:
 
SQL> conn andrew/reid
Connected.
SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL>
 
He then allowed Fred to update it:
 
SQL> grant update on tab1 to fred
  2  /
 
Grant succeeded.
 
SQL>
 
He added a row to the table, saved it and updated it, setting up a lock in the process:
 
SQL> insert into tab1
  2  values (1)
  3  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> update tab1
  2  set col1 = 2
  3  where col1 = 1
  4  /
 
1 row updated.
 
SQL>
 
Finally, he counted the rows in DBA_TABLES again and again:
 
SQL> declare
  2    row_count number;
  3  begin
  4    while (1=1) loop
  5    select count(*) into row_count
  6    from dba_tables;
  7    end loop;
  8  end;
  9  /
 
Fred then came along and tried to update the same row in TAB1 but had to wait:
 
SQL> conn fred/bloggs
Connected.
SQL> update andrew.tab1
  2  set col1 = 2
  3  where col1 = 1
  4  /
 
So, what is the point of this example?
 
If you look at OEM’s Blocking Sessions screen and see a user blocking another user, the SQL shown against the blocking user is the SQL he/she is currently running. This may or may not be the cause of the lock which is holding up the user(s) underneath.

Thursday, January 28, 2016

INSERT /*+ APPEND */ Hint Does Not Seem to Work Consistently

I ran the following SQL in an Oracle 11.2.0.1 database:

SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> insert /*+ append */ into tab1 select 1 from dual
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> insert /*+ append */ into tab1 values(2)
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
         2
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>

Then I ran the trace file through tkprof. For some reason, the first SQL used a Direct Path load:

SQL ID: f57gyxg0uqcgb
Plan Hash: 1432430773
insert /*+ append */ into tab1 select 1 from dual
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02       0.02          0          3         30           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0          3         30           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8354  (ORACLE)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD AS SELECT  (cr=0 pr=0 pw=0 time=0 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD AS SELECT OF 'TAB1'
      1        FAST DUAL

 
... but the second one didn’t. The strange reformatting of the insert statement took place in tkprof, not in Blogger:

SQL ID: 6pb8960hm1hpy
Plan Hash: 0
insert /*+ append */ into tab1
values
(2)
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7         25           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          7         25           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8354  (ORACLE)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=7 pr=0 pw=0 time=0 us)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'TAB1'


P.S. Shortly after publishing this post, a number of people corrected it in the comments below. I won't be changing the post; if I did, the comments would then be out of place. However, I will be testing and understanding the comments before using them as the basis for another post in the near future.