Thursday, May 18, 2006

A silent but important change

While learning Oracle and working with it, sometimes I’m getting really surprised. Especially when I discover odd differences between reality and what documentation states.

Few months ago I noticed one silent change in Oracle locking strategy that was introduced since Oracle 9.2.0.6.0 (and 10.1.0.4.0, for 10g family). I called it “silent” because it’s not reflected anywhere in Oracle documentation yet despite the fact that it may break logic of some applications where manual locking statements (e.g. LOCK TABLE) are used. And what is more confusing, there are only two Metalink bug notes (4115353 and 4252394), from which one can conclude that locking behavior was changed intentionally, to fix some locking-related bug in RAC environments.

Here are details. One can repeat this small test in Oracle 9.2.0.5.0 and earlier:

SQL> show release
release 902000500
SQL>
SQL> create table t(x) as
2 select * from dual;

Table created.

SQL> select * from t for update;

X
-
X

SQL> select type, lmode
2 from v$lock
3 where request = 0 and
4 sid = (select sid
5 from v$mystat
6 where rownum = 1);

TY LMODE
-- ----------
TX 6
TM 2


So everything is as expected: SELECT ... FOR UPDATE applied TX lock in mode 6 as well as TM lock in mode 2 (also called "row share" lock).

But look what will happen if we rerun our small test in 9.2.0.6.0:

SQL> show release
release 902000600

...
< rerun "create table" and
"select ... for update" here >
...

SQL> select type, lmode
2 from v$lock
3 where request = 0 and
4 sid = (select sid
5 from v$mystat
6 where rownum = 1);

TY LMODE
-- ----------
TX 6
TM 3


Well, that was a bit surprising. Now Oracle applied TM mode 3 lock (e.g. "row exclusive" table-level lock) as we issued SELECT ... FOR UPDATE statement.

The impact of this change on existing applications is not so critical unless they have implemented some manual locking strategy. For example, look what may happen in 9.2.0.6.0 and above:

Session #1> show release
release 902000600
Session #1>
Session #1> lock table t in share mode;

Table(s) Locked.

Session #2> show release
release 902000600
Session #2>
Session #2> select * from t for update nowait;
select * from t for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SELECT ... FOR UPDATE NOWAIT statement in session 2 failed, but it succeeds in Oracle 9.2.0.5.0 and earlier (10.1.0.3.0 and earlier).

Small change, but it may cause a bit of headache to someone who doesn’t know about it.

Andrew.

Tuesday, May 16, 2006

It's neat and useful

Things change rapidly.
In world of Oracle Database technologies things change so rapidly so it's hardly possible to keep track of them all. But I believe that one should learn something new about Oracle each day - it's the only way to become successful developer or DBA.

Recently, I was reading and enjoying new Jonathan's book, an outstanding work of art for everyone who wants to explore Oracle CBO in detail. While reading it, I noticed small but pretty fascinating remark about a feature I never saw before.

How many ways to obtain statement execution plan do you know? I'm not going to discuss them all, but I think that EXPLAIN PLAN statement and AUTOTRACE feature are most widely used. For sure, they are simple and convenient but one should be careful when using them, because they can be misleading to some extent. The most important thing to remember is that EXPLAIN PLAN is always doing hard parse. Therefore, for example, side-effects of bind variable peeking will be probably missed.

And if we want to look at "real" execution plan, which opportunities do we have? I think these are most familiar ones:
1. Using SQL_TRACE and TKPROF, and then analyzing produced trace files.
2. Querying V$SQL and V$SQL_PLAN dynamic views.

Both options are good, but they require some extra efforts. Well, maybe I'm too lazy, but from Jonathan's book I knew exactly what I was dreaming about. In first release of Oracle 10g, several important enhancements were made to DBMS_XPLAN package, and in particular, new function DISPLAY_CURSOR was added.

It's nice, and easy to use:

SQL> show release
release 1001000200
SQL>
SQL> create table test(x primary key, y) as
2 select rownum, rpad('*', 100, '*')
3 from dual
4 connect by level <= 1000;

Table created.

SQL> begin
2 dbms_stats.gather_table_stats
3 (ownname => user,
4 tabname => 'TEST',
5 estimate_percent => null,
6 cascade => true);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> var x number
SQL>
SQL> exec :x := 1000

PL/SQL procedure successfully completed.

SQL> explain plan for
2 select * from test
3 where x < :x;

Explained.

SQL> -- Now, EXPLAIN PLAN tells us
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------
Plan hash value: 633647876

------------------------------------------------------------ ...
| Id | Operation | Name | Rows | ...
------------------------------------------------------------ ...
| 0 | SELECT STATEMENT | | 50 | ...
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 50 | ...
|* 2 | INDEX RANGE SCAN | SYS_C005782 | 9 | ...
------------------------------------------------------------ ...

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("X"<TO_NUMBER(:X))

14 rows selected.

SQL> -- Ok, let's run it...
SQL> select * from test
2 where x < :x;

X Y
---------- -------------------------------------------------- ...
1 ************************************************** ...
2 ************************************************** ...
... ...
... ...
999 ************************************************** ...

999 rows selected.

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID 5r7q3102zwvh3, child number 0
-------------------------------------
select * from test where x < :x

Plan hash value: 3836831076

------------------------------------------------------------- ...
| Id | Operation | Name | Rows | Bytes | Cost (%CPU ...
------------------------------------------------------------- ...
| 0 | SELECT STATEMENT | | | | 6 (100 ...
|* 1 | TABLE ACCESS FULL| TEST | 999 | 101K| 6 (0 ...
------------------------------------------------------------- ...

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("X"<:X)


18 rows selected.


However, one thing to remember: you should disable SERVEROUTPUT before using this feature or else you'll be slightly surprised:

SQL> set SERVEROUTPUT ON
SQL>
SQL> select * from test
2 where x < :x;

X Y
---------- -------------------------------------------------- ...
1 ************************************************** ...
2 ************************************************** ...
... ...
... ...
999 ************************************************** ...

999 rows selected.

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
------------------------------------------------------------- ...
SQL_ID 9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify SQL_ID and CHILD_NUMBER; maybe the plan is
no longer available in V$SQL_PLAN



8 rows selected.


Neat feature. I liked it.

Andrew.

Tuesday, May 02, 2006

Too late

Recently I’ve decided to remove my previous post because things turned out to be worse than I expected. Briefly speaking, April 2006 CPU released by Oracle Corp. does not solve the problem: unfortunately, that serious vulnerability is still relevant.


Now, I must stress: at the moment I published my previous post, I was pretty sure that problem is nearly fixed: Oracle Support told me that "it’s well-known security vulnerability that will be solved by April CPU". That CPU is already released, however, as I already mentioned, it does not suggest a fix to that issue. Seems that Oracle support guys didn’t get me right from the very beginning, but I’m not going to speculate about possible reasons...


And now, I feel that I’m in a tricky situation of some kind: being sure that the problem is about to be fixed soon, I published pretty critical information here. Though I didn’t mention any details, it’s still critical I think.


So I decided to remove it once and forever, but then I eventually discovered that it’s just too late. The same web page, http://www.red-database-security.com/advisory/oracle_modify_data_via_views.html, was updated days ago with information from my blog. For sure, I don’t know Alexander Kornbrust personally (a man who seem to head up that company) but I believe that he understands responsibility and won’t publish any critical details on his website.


It seems that there’s no way back, and it obviously does not make me happy. So I am leaving my previous post "as it is", with sincere hope for better future.


Andrew.