Oracle Database processing time skills

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 earlier versions, the time unit of measurement in Oracle was per second. The most obvious problem with the use of per second is that some operations may be less than per second. 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 does 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 INTERVALDAY TO SECOND are added, which, together with several other data types, makes the time processing more accurate. TIMESTAMP, timestampwithtime zone, timestamp with localtimezone, 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:

INTERVALYEAR (year_precision) TOMONTH

INTERVALDAY (day_precision) TOSECOND (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 ("", "yyyy-mm-dd"), the former is a string, and the latter is the conversion date format. Note that the first and second must be a pair of values.

For example; to_date ("13:34:43", "yyyy-mm-ddhh24: 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. YEARTOMONTH indicates that the time interval between the hour and month must be connected by a hyphen. DAYTOSECOND indicates that a space is used to connect the time interval between the hour and the day. Example:

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

Selectto_char (current_timestamp (5), "DD-MON-YYYYHH24: MI: SSxFF") fromdual;

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

Selectto_char (current_timestamp (9), "MI: SSxFF") fromdual;

Compare the maximum value with the current time