help with next_time for dbms_job

[gandolf989]

We are running Oracle 9.2.0.7 on Windows 2000 Advanced Server.

We have a job that runs in DBMS_JOB. It looks at v$sql and takes a snapshot every so often and logs this snapshot to a table where presumably someone can go and look at it. The formula used to compute the next run time is sysdate+.05/(24*60). Which I take to mean run every 30 seconds. Is this really every 30 seconds?

There are times when it takes more than 60 seconds to run. I like the data that I get, but I was thinking about having it run every hour instead of twice per minute.

Here is the query:

SELECT first_load_time,          NVL(last_load_time,'NA'),
      (elapsed_time * .000001),  sql_text,
       rows_processed,           optimizer_cost,
       cpu_time,                 buffer_gets,
       disk_reads,               MODULE,
       parsing_user_id,          parsing_schema_id
 FROM v$sql
WHERE to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS') >  SYSDATE -.5/(24*60)
  AND ROWNUM < 100
ORDER BY first_load_time desc;

I want to rewrite it as:

SELECT first_load_time,          NVL(last_load_time,'NA'),
      (elapsed_time * .000001)   elapsed_time,
       sql_text,
       rows_processed,           optimizer_cost,
       cpu_time,                 buffer_gets,
       disk_reads,               MODULE,
       parsing_user_id,          parsing_schema_id
 FROM v$sql
WHERE to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS') >  SYSDATE - 1/(24*60)
  AND elapsed_time > 10;

Any thoughts on the query as well?

Thanks in advance.

[jmodic]

... sysdate+.05/(24*60). Is this really every 30 seconds?

No, that's every 3 seconds.

Any thoughts on the query as well?

Well, for the first query - using "WHERE ROWNUM < x ORDER BY y" usually means the query will not return quite the same result as the author was hoping for....

In the second query, the expression

WHERE to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS') >
      SYSDATE - 1/(24*60)

means you'll get only the cursors that were loaded in the last minute - weren't the cursors from the last hour that you are interested in?

In any case, querying V$SQL on a bussy system every 30 seconds realy sounds problematic (to put it mildly)...