On 7/3/07, Sandra Becker <sbecker6925@xxxxxxxxx> wrote:
>
> RHEL4, 9.2.0.8
>
> Last Tuesday night we put some new code into production. The
> performance has been slower than usual, but no one seems to believe that it
> was the new code.
>
Prime reason that DBA's often to not trust developers.
>
> SELECT NULL FROM DUAL FOR UPDATE NOWAIT;
>
> No one is owning up to writing this code and I can prove it came from
> the application.
>
Of course not.
> Questions:
> 1) Could this statement have contributed to the overall problems?
>
Possibly.
If they are silly enough to include use that statement to start with ( if
they wont' own up to
it, there's probably not a good reason for using it) they might also be
silly enough to do
something like this:
declare
not_ready exception;
pragma exception_init(not_ready,-54);
x integer;
begin
loop
begin
select null into x from dual for update nowait;
exit;
exception
when not_ready then
dbms_lock.sleep(1);
when others then
raise;
end;
end loop;
end;
/
Who knows?
Do they, um, "test" their changes by any chance?
In a system that has realistic data and number of users?
> 2) Is this something I should concern myself with or would I be
> spinning my wheels?
>
Escalate it. After all, the database is being blamed, which implies to
management that
you are somehow not doing your job properly, which doesn't appear to be
the case.
How you escalate it is very dependent on company culture.
3) Why would anyone write a statement like this? What would be the
> point? Since I don't know which developer wrote it and no one is owning up
> to it, I haven't been able to find out what the desired result was supposed
> to be.
>
Perhaps someones ingenious method to get a user lock, as implied by the
preceding PL/SQL.
Run the PL/SQL in 2 sessions.
Perhaps the developers should be introduced to dbms_lock?
Or maybe not, not knowing your devleopers.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
