Time on 4 decimals

SQL Optimizer SQL Optimizer for Oracle
1

I do remember that before, SQL Optimizer was showing more second decimals 6than now (it shows 2).

2018-11-06_10-03-47
2018-11-06_10-03-47.png1247×293 28.7 KB

Please return 4 digits in resulting grid.

Second, before 9.x version, decimal character was "." (point) and not the "," (comma), as it is now. And it was much easier to handle custom formats in Excel later. Could that at least be introduced as an option in some setting?
Brg
Damir

2

Any news on this topic?

3

Bumps is not a complete sentence so i am writing this beside to be able to post a "bump"

4

Hi Damir,

Thank you for the feedback. We will revise the decimal digits to display. However, please note that the "Elapsed Time" is accurate to 0.01 seconds in our measurement so it seems not a problem to display 2 decimal digits. However, in the "Average Elapsed Time" in the "Test Run Different Bind Values", I agreed that more decimal digits would be needed for the calculated average.

For the "." or "," used as the decimal separator or thousand separator, the grid is now displaying numbers according to the "Region and Language" settings in your Windows. It was actually a bug in the old release to hard-coding to use "." as the decimal separator in some columns (resulting in confusion where some columns using "." and some using "," as the decimal separator in the same grid).

Thanks,
Alex

5

"Elapsed Time" is accurate to 0.01 seconds i

Sorry but on many databases this is not enough. In mine practice I see many sqls that runs very shortly, and it is completely unusable now to monitor them on 2 decimals.

8(

So please place the whole number (as is) or reduce to 5 decimal values.

6

Hi Damir,

I may not have explained it clear enough about the 0.01 seconds accuracy so let me try again.

We use the DBMS_UTILITY.GET_TIME to measure the run time of a SQL statement. We get the time before and after the execution of a SQL and then subtract both time to get the difference. This difference is the run time of the SQL. The accuracy of the measurement depends on the accuracy of the DBMS_UTILITY.GET_TIME in Oracle, which is accurate up to 0.01 seconds. So even if we display more digits after the decimal point, those digits will be 0's after the 2nd digit. Hope this help clarify about the accuracy.

Thanks,
Alex

7

Ok fair to say that...thx for explanation.

Case closed.

8

I was investigating a little and must say you have wrong way of measuring time...

here is small sample that shows how time is measured in many decimals!

09:27:01 SQL>DECLARE
09:27:30   2      tsStartTime TIMESTAMP;
09:27:31   3      tsEndTime TIMESTAMP;
09:27:31   4  BEGIN
09:27:31   5    for i in 1..10 loop
09:27:31   6      tsStartTime := CURRENT_TIMESTAMP;
09:27:31   7      tsEndTime := CURRENT_TIMESTAMP;
09:27:31   8      DBMS_OUTPUT.PUT_LINE('Time elapsed:' || to_char(tsEndTime - tsStartTime));
09:27:31   9    end loop;
09:27:31  10  END;
09:27:31  11  /
Time elapsed:+000000000 00:00:00.000025000
Time elapsed:+000000000 00:00:00.000005000
Time elapsed:+000000000 00:00:00.000004000
Time elapsed:+000000000 00:00:00.000004000
Time elapsed:+000000000 00:00:00.000004000
Time elapsed:+000000000 00:00:00.000004000
Time elapsed:+000000000 00:00:00.000004000
Time elapsed:+000000000 00:00:00.000004000
Time elapsed:+000000000 00:00:00.000004000
Time elapsed:+000000000 00:00:00.000004000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

So is there anyway to implement such a logic instead of get_time method?

It would be really nice step forward.

9

Hi Damir,

Thank you for looking deeper into this. We will consider the CURRENT_TIMESTAMP suggestion. I do appreciate your example script.

Thanks,
Alex

10

Hi Alex,
How that previous version was having 4 decimals?
Did you change the logic or what?
This all seems to me a little bit fuzzy
Brg
Damir

11

Hi Damir,

On top of my head, I don't remember we have changed the number of digits. I have also tested a few old versions and I see only 2 digits after the decimal points.

Could you please let me know where you see more digits and the version of the SQL Optimizer?

Thanks,
Alex

12

Do not know, but found this picture in mine documentation. If you compare time, maybe you can see that...I am always on the newest version in that moment.

13

I am not sure which picture you were refering to. Would you please post the picture here?

Thanks,
Alex

15

Sorry, thought that I post one with more decimals.
Frankly do not have now that proof, but can tell you that when you change decimal character (from that time have to write macro to fix this new feature) in Export to Excel, in that time this number of decimals reduced.
I cannot help you more closely, sorry.
brg
Damir

16

No worries. We will consider changing the measurement to use CURRENT_TIMESTAMP which will give more digits anyway.

Thanks,
Alex