|
|
| Re: Sysdate how to improve. [message #23257 is a reply to message #23252] |
Mon, 25 November 2002 12:09   |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
For a single SQL statement it's only evaluated once (not sure about all subqueries though). In pl/sql you can obviously initialise a variable outside a loop. Even in a loop, it takes less than 3.35/100000 sec on my machine.
DECLARE
timing PLS_INTEGER;
x date;
BEGIN
timing := DBMS_UTILITY.get_time;
FOR i IN 1 .. 100000
LOOP
x := sysdate;
END LOOP;
DBMS_OUTPUT.put_line ('x:= sysdate sec = ' || TO_CHAR (DBMS_UTILITY.get_time - timing)/100 );
END;
/
x:= sysdate sec = 3.35
|
|
|
|
| Re: Sysdate how to improve. [message #23267 is a reply to message #23257] |
Tue, 26 November 2002 00:28 |
Anthony V
Messages: 2
Registered: November 2002
|
Junior Member |
|
|
But if you assign a sysdate to a variable from a pl/sql, internally oracle will issue a 'select sysdate from dual'. I'm having a problem this because we are executing it for 300000 times and it takes time just to get the current date and time. Any more ideas out there?
FOR i IN 1 .. 100000
LOOP
x := sysdate; -- oracle will issue a 'select sysdate from dual;' verify it with tkprof.......
END LOOP;
|
|
|
|
| Re: Sysdate how to improve. [message #23279 is a reply to message #23267] |
Tue, 26 November 2002 11:57 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Well, if you monitor DBMS_UTILITY.get_time, you can limit the calls to SYSDATE to 1 per sec (assuming you only want down to 1 sec resolution as in pre-9i timestamps).
DECLARE
timing PLS_INTEGER := DBMS_UTILITY.get_time;
t0 PLS_INTEGER := DBMS_UTILITY.get_time;
x DATE := SYSDATE;
n PLS_INTEGER := 0;
BEGIN
FOR i IN 1 .. 1000000
LOOP
IF (DBMS_UTILITY.get_time - t0) >= 100
THEN
t0 := DBMS_UTILITY.get_time;
x := SYSDATE;
n := n + 1;
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('1,000,000 loops sec = '|| TO_CHAR (DBMS_UTILITY.get_time - timing) / 100);
DBMS_OUTPUT.put_line ('calls to sysdate in loop = ' || n);
END;
/
1,000,000 loops sec = 6.93
calls to sysdate in loop = 6
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
