| dbms_lock.sleep problems [message #430875] |
Thu, 12 November 2009 16:13  |
Arokh
Messages: 6
Registered: November 2009
|
Junior Member |
|
|
Hi all,
I'm trying to create a procedure to run in sql plus that will read the number of rows in a table wait 10 second then do it again for an indefinite period of time.
In my testing it appears the dbms_lock.sleep procedure will wait the time of the entire loop process at the beginning and quickly spit out the results at the end. In short I expected to see the result followed by 10 sec delay followed by result, etc. However its not working as expected and fail to understand what I'm doing wrong. Any help or suggestions would be appreciated. Thanks.
SET SERVEROUTPUT ON
SPOOL C:\Temp\OracleTest.log
DECLARE
i NUMBER :=1;
r NUMBER;
BEGIN
LOOP
dbms_lock.sleep(10);
select count(*) into r from spacetypes;
dbms_output.put_line(r);
EXIT when i>10;
i := i + 1;
END LOOP;
END;
/
Ed
|
|
|
|
|
|
| Re: dbms_lock.sleep problems [message #430882 is a reply to message #430875] |
Thu, 12 November 2009 18:31  |
Arokh
Messages: 6
Registered: November 2009
|
Junior Member |
|
|
In otherwords your telling me that if I want to loop through a table and retrieve 1000 records your saying that I can not dbms_output each record to be worked on I have to output the entire 1000 into a temporary area to be worked on later by another function?
If that is the case how would I loop through and produce each result and wait for 10 seconds before producing the next result?
Unfortunately I don't have a programming background so just trying to take what I want to do and figure out how to do it.
[Updated on: Thu, 12 November 2009 18:34] Report message to a moderator |
|
|
|
|
|
| Re: dbms_lock.sleep problems [message #430923 is a reply to message #430883] |
Fri, 13 November 2009 03:00 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Oracle's default transaction isolation level is read committed - this ensures that each query executed by a transaction sees only data that was committed before the query started, but not before the transaction started began.
Thus by looping through and repeatedly running the same query, selecting a different value each time is entirely possible.
It is possible to get the behaviour that @BlackSwan describes, but you need to set the transaction level to serializable.
|
|
|
|
| Re: dbms_lock.sleep problems [message #430924 is a reply to message #430882] |
Fri, 13 November 2009 03:04 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you want to write output from your code that happens at the time, rather than at the end of the procedure, you want to look at UTL_FILE
As Black Swan says, what problem are you actually trying to solve - if you an describe what you want, rather than what you're actually doing, then we will probably be able to help better.
|
|
|
|
| Re: dbms_lock.sleep problems [message #430966 is a reply to message #430875] |
Fri, 13 November 2009 08:06 |
Arokh
Messages: 6
Registered: November 2009
|
Junior Member |
|
|
For the past 5 months I've been searching for problem with an application that disconnects from Oracle with a broken connection
at random times with no discernable pattern. There are no errors in the listener logs only signs of the reconnection. There are no errors in with the OS. There are no errors on the switches. We've removed the application from VMWare and moved to physical.
I would like to prove if this problem is in the database or in the application so I came up with running a constant query through SQL Plus and wait for the next disconnect. If SQL Plus disconnects then I know it is in the database/network. If it does not then I know its in the application.
Oracle 10.2.0.4 client and back-end DB running on Unix. I don't know what flavor of unix since its not a database I have access to.
Hopefully this clears the air a bit.
[Updated on: Fri, 13 November 2009 08:11] Report message to a moderator |
|
|
|
|
|
|
|
| Re: dbms_lock.sleep problems [message #431043 is a reply to message #430875] |
Fri, 13 November 2009 14:14 |
Arokh
Messages: 6
Registered: November 2009
|
Junior Member |
|
|
I appreciate all the ideas and assistance. I finally came up with this to prove my point that either the database or application is deciding to drop connection. If my calculations are correct the following will execute this query 6 times per minute for a 24 hour period. Yes I wont get any results, however by tracing the session I should see if the connection gets destroyed somehow by the database. Furthermore if an error is generated by sql plus I should also get that in the mytest.sql spool file. Either way I'm hopeful this will point the finger as to where this very elusive problem resides. Thanks again all!
spool c:\temp\mytest.sql
alter session set sql_trace = true;
show parameter user_dump_dest
show parameter optimizer
declare x passtypes.passtype%type;
begin
for i in 1..86400 loop
select passtype into x from passtypes where rownum = 1;
dbms_lock.sleep(10);
end loop;
end;
/
|
|
|
|
| Re: dbms_lock.sleep problems [message #431642 is a reply to message #431043] |
Wed, 18 November 2009 16:22 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
you need to be very careful with your test case to ensure it mimics your application. Your client can influence whether it uses dedicated or possibly shared server connection. JDBC vs Sqlplus OCI etc are all different. You also need to know how your app connects - does it open a new connection each time, use a connection pool etc. I hope you know that your script listed below passes 1 anonomous PL/SQL to the database, so there's probably no further communication (even background stuff you can't see) with your SQLplus client until it's complete.
If you have a firewall and are using connection pools or are opening new connections - ensure the firewall isn't blocking a range of ports that you are hitting "randomly". I assume you've considered this because you mention the network is clear.
Enabling more verbose sqlnet logging (in client's sqlnet.ora) and the database listener can help to do better tracing.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
