DB2 Optimizer Analysis

2. Timeron Concept:

   DB2 provides visual interpretation, Timeron is a unit of cost measurement used by the DB2 optimizer to calculate the amount of time and resources required for a full execution of a query.
   The Timeron is a synthesis of time, CPU utilization (in the number of instructions), disk I/O (number of seeks and page conversions), and a number of other factors.
   Because the values of these parameters are constantly changing, the number of Timeon required to execute a query is also dynamic, with each execution being different.
   Timeron is also a unit of measurement that is created, so there is no formula to convert the number of Timeron required to execute a query into seconds.
   In addition, Timeron can help you determine whether one query execution path is faster than another. (If the number of Timeron required to execute a query differs by 10 or Timeron between two compilations, this may only be due to changes in CPU activity, disk activity, or database usage.) )
   

3. Common Visual Explain operators:
   

   The

   operator	actions that are performed
   Cmpexp	Evaluates an expression. (For debug mode only.) ）
   DELETE	Deletes a row from the table.
   EISCAN	Scans a user-defined index, resulting in a series of simplified rows.
   FETCH	Gets the column from the table using the specified record identifier.
   FILTER	Filter data by applying one or more predicates.
   Genrow	Generates a row table.
   Grpby	organizes rows by the public value of a specified column or function, and evaluates the aggregate function.
   Hsjoin	Displays a hash join in which one or more tables are mixed on the join column.
   INSERT	Inserts a row into the table.
   IXAND	An and operation on the resulting row identifier (RID) for two or more index scans.
   IXSCAN	The table index is scanned using an optional start/stop condition, resulting in an orderly flow of rows.
   Msjoin	Displays the merge connection, where the outer and inner tables must be arranged in the order of the join predicates.
   Nljoin	Displays nested loop joins, accessing internal tables once for each row in the external table.
   PIPE	Translation line. (For debug mode only.) ）
   RETURN	Displays the data returned by the query to the user.
   Ridscn	Scans a list of row identifiers (RIDs) that are obtained from one or more indexes.
   RPD	The operator for the remote plan. Very similar to the ship operator in V8 (the rquery operator in previous versions), the only difference is that it does not contain SQL or XQuery statements.
   Ship	Retrieves data from the remote database source. Used in federated systems.
   SORT	You can choose to de-duplicate entries by sorting rows in the order of specific classes.
   TBSCAN	Retrieve rows by reading all the data directly from the data page.
   TEMP	Store the data in a temporary table for reading back (most likely to read back multiple times).
   Tqueue	Transfer table data between database agents.
   UNION	Concatenates row flows from multiple tables.
   UNIQUE	Eliminate duplicate rows for specific column values.
   UPDATE	Update the rows in the table.
   Xiscan	Scans the index of an XML table.
   Xscan	Navigate in an XML document section subtree.
   Xandor	Allows the anded and ORed predicates to be applied for multiple XML indexes.

DB2 Optimizer Analysis