Comparison of time problems in Oracle databases

Guidance:When monitoring, diagnosing, and processing database performance problems, time information is often an important basis for judgment. Sometimes we may use some proportions to determine the performance, but using percentages instead of time often leads us to the wrong direction. In previous versions,Oracle DatabaseThe time unit of measurement is seconds. The most obvious problem with the use of seconds is that some operations may be less than seconds. It seems that this is not quite common, but in fact many operations on the operating system are in a subtle unit, which means that the start and end of the operation is completed in less than seconds, from the second level, it seems that nothing happened, because the duration is almost 0. Sometimes the operation lasts less than seconds, but the start and end occur in two connected seconds, so the operation time is less than seconds but is recorded as seconds, the time record is inaccurate. Oracle databases do not have a built-in method to record the passage of time. Data of the DATE type is used to record individual time points. To express a time amount (that is, an interval), the Database Designer must convert the time interval to the original unit of seconds, then, use a NUMBER column to save it.

Although the NUMBER data type can accurately represent time in seconds, it makes time calculation very difficult. For example, 60 seconds is 1 minute, 60 minutes is 1 hour, and 24 hours is equal to 1 day-these numbers are very impractical in a decimal-based number system.

In Oracle, according TO the SQL 99 standard, the time INTERVAL data INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are added, which, together with several other data types, makes the time processing more accurate. TIMESTAMP, timestamp with time zone, timestamp with local time zone, and other data types all accurately express the TIME to several points per second, and the latter two also solve the TIME changes caused by geographical locations.

The SQL statement's wait time is equal to ELAPSED_TIME minus CPU_TIME, but it is difficult to see the exact wait time. In the V $ SYSTEM_EVENT view, you can see the wait time at the database instance level (not for each SQL statement), but not the wait time on the operating system.

In SQL and PL/SQL, you can use time interval data, which are defined in the same way:

Interval year [(year_precision)] TO MONTH
Interval day [(day_precision)] to second [(fractional_seconds_precision)]

The conversion function, which has the maximum relationship with the date operation, is the two conversion functions: to_date (), to_char ()

To_date () converts the character type to the date type in a certain format:

Specific usage: to_date ('1970-11-27 ', 'yyyy-mm-dd'). The former is a string, and the latter is a conversion date format. Note that the first and second data must be in the correct format.

For example, to_date ('2017-11-27 13:34:43 ', 'yyyy-mm-dd hh24: mi: ss') will get the specific time

For exact values, the default value is: Year and day are two digits, and one second is six digits.

The size of the INTERVAL is represented by INTERVAL, followed by an expression placed in single quotes, and the text used to explain the expression. Use year to month to indicate that the time interval between the hour and MONTH must be connected by a hyphen. Day to second indicates that a space is used TO connect the time interval between DAY and time. Example:

Returns the current time, year, month, hour, minute, second, millisecond.

Select to_char (current_timestamp (5), 'DD-MON-YYYY HH24: MI: SSxFF') from dual;

Returns the second millisecond of the current time. You can specify the precision after the second (max = 9)

Select to_char (current_timestamp (9), 'mi: SSxFF ') from dual;

Compare the maximum value with the current time

The maximum range is obtained by adding the specified month to the maximum time in the record and comparing the current system time with CURRENT_DATE. If the limit is exceeded, the system determines that the system does not exist, indicating that the system time is 0, otherwise, it is 1.

SQL:
Select count (*) contains
From dual t
WHERE CURRENT_DATE <(SELECT (ADD_MONTHS (MAX (t. med_time), 14 ))
FROM red_meettingrecord t
WHERE t. med_tar_id = '123'
AND t. med_usr_id = '20140901 ')

The maximum range is obtained by adding the specified month to the maximum time in the record and comparing the current system time with CURRENT_DATE. If the limit is exceeded, the system determines that the system does not exist, indicating that the system time is 0, otherwise, it is 1.

I will continue to explain the required knowledge about the comparison of time issues in Oracle databases. I hope this will help you.