Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
admin:optimization:plans:reference [2012/02/02 18:59]
doc
admin:optimization:plans:reference [2016/06/28 22:38] (current)
Line 21: Line 21:
 An Omnidex query plan is designed to provide all of the essential information needed to understand how a query will be processed, as well as basic suggestions about how to further optimize the query. ​ A great deal can be learned by reviewing the query plan, and it is always the first step in understanding Omnidex query performance.  ​ An Omnidex query plan is designed to provide all of the essential information needed to understand how a query will be processed, as well as basic suggestions about how to further optimize the query. ​ A great deal can be learned by reviewing the query plan, and it is always the first step in understanding Omnidex query performance.  ​
  
-A query plan consists of two sections: the Summary and the Details. The Summary section shows the SQL statement, the version of Omnidex, any optimization ​settings or warnings ​that affect the query, and even occasional suggestions for optimizing the query. The Details section shows the steps that will be executed to satisfy the query. Steps are executed in the order displayed, and are indented as needed to show loops of instructions. ​ The example below shows the skeleton of a query plan:+A query plan consists of two sections: the Summary and the Details. The Summary section shows the SQL statement, the version of Omnidex, any optimization warnings, and even occasional suggestions for optimizing the query. The Details section shows the steps that will be executed to satisfy the query. Steps are executed in the order displayed, and are indented as needed to show loops of instructions. ​ The example below shows the skeleton of a query plan:
  
 <​code>​ <​code>​
Line 28: Line 28:
  
 Version: ​     <Omnidex version> Version: ​     <Omnidex version>
-Request Opt:  <​Requested optimization settings>​ 
-Optimization:​ <Actual optimization settings>​ 
 Warnings: ​    <​Warning flags> Warnings: ​    <​Warning flags>
 Notes: ​       <​Optimization notes> Notes: ​       <​Optimization notes>
Line 42: Line 40:
 === The SUMMARY Section === === The SUMMARY Section ===
  
-The Summary section describes the SQL Statement and provides ​general ​notes and flags about the query.  These included optimization settings and recommendations.  The Summary section is the first place to look when evaluating the optimization of a query.+The Summary section describes the SQL Statement and provides ​warnings and notes about eh optimization of the query. ​ The Summary section is the first place to look when evaluating the optimization of a query.
  
 == SQL Statement == == SQL Statement ==
Line 52: Line 50:
 The version of Omnidex, including the compilation timestamp. ​ This is important as optimization can be different between versions of Omnidex. The version of Omnidex, including the compilation timestamp. ​ This is important as optimization can be different between versions of Omnidex.
  
-== Required Optimization ​==+== Warning Flags ==
  
-If specific ​optimization ​was required ​when the query was run, then these settings are shown here.  ​If no special optimization was requiredthen this line is omitted. +Warning flags indicate an optimization ​concern that warrants attention ​when tuning a query.  ​These warnings do not indicate a certain problembut rather an area that could be contributing to poor performance. ​ Warning flags are sometimes accompanied by further notes in the Notes section.
- +
-The specific optimizations ​that can be required ​are:+
  
 +|< 100% 25% 75% >|
 ^ Setting ​                    ^ Description ​                                ^ ^ Setting ​                    ^ Description ​                                ^
-NO_XREF_FILES ​              | Do not create ​Cross-Reference files when processing ​this query. ​ Cross-Reference files are sometimes used to optimize complex, many-to-many table relationships,​ but they generally cause a query to take a lot of time, especially on large databases. | +XREF_FILES ​              | Cross-Reference files were required to process ​this query. ​ Cross-Reference files are sometimes used to optimize complex, many-to-many table join relationships,​ but they generally cause a query to take a lot of time, especially on large databases.  It is usually preferable to rewrite the query so that there is no a many-to-many table relationship. ​ This can sometimes be accomplished using subqueries.| 
-NO_UNOPTIMIZED_CRITERIA ​    | Do not allow any criteria ​to be processed without the use of Omnidex indexing. | +UNOPTIMIZED_CRITERIA ​    | One or more criteria ​could not be satisfied using Omnidex indexing, usually because an index does not exist or the indexing options prevent its used.  It is usually preferable to add an index to process the unoptimized criteria. | 
-NO_CARTESIAN_PRODUCTS ​      | Do not allow Cartesian Joins.  Cartesian Joins occur when tables are joined together without any join criteria. ​ While there are rare cases when Cartesian Joins are desireable, they are usually an indication of missing join criteria. | +CARTESIAN_PRODUCTS ​      | This query contains a Cartesian Joins, meaning that two tables are joined together without any join criteria. ​ While there are rare cases when Cartesian Joins are desireable, they are usually an indication of missing join criteria. | 
-NO_UNOPTIMIZED_AGGREGATIONS ​Do not allow any aggregations ​to be processed ​without the use of Omnidex ​indexing. | +UNOPTIMIZED_AGGREGATION  ​This query required that aggregations be performed ​without the use of Omnidex ​indexes, resulting in additional sorting. ​ It is usually preferrable to add Omnidex indexes to support this aggregation. ​ A valid exception to this rule is when a small intermediate result set is being reaggregated,​ either in Star Schema or Omnidex Grid scenarios. | 
-NO_UNOPTIMIZED_SORTS ​       ​Do not allow any sorts to be processed without the use of Omnidex ​indexing. | +UNOPTIMIZED_SORT ​        This query required that sorting ​be performed rather than using an Omnidex ​index. ​ It is usually preferrable to add Omnidex indexes to support ORDER BY clauses whenever the result set is large.| 
-NO_SEQUENTIAL_SCANS ​        Do not allow any sequential table scans. | +SEQUENTIAL_SCAN ​         ​This query required a sequential ​table scan.  Sequential ​table scans should generally be avoided when retrieving more than 5-10% of the table.| 
-NO_SEQUENTIAL_TABLE_JOINS ​  Do not allow any sequential table joins.  A sequential table join indicates that all rows in the second table will be scanned sequentially ​for each row in the first table. ​ This is the most inefficient way to perform a join, and is only necessary when Omnidex indexing, underlying database indexing, table caching and sort/​merging cannot be used. | +SEQUENTIAL_TABLE_JOIN ​   ​This query required a sequential table join.  A sequential table join scans all rows in the second table for each and every row in the first table. ​ This is the most inefficient way to perform a join, and is only necessary when Omnidex indexing, underlying database indexing, table caching and sort/​merging cannot be used. | 
-NO_GRID_AGGREGATIONS ​       ​Do not allow any aggregations ​to be performed on the grid controller that require reaggregating ​data on the grid controller. |+GRID_AGGREGATION ​        This query required that additional ​aggregations be performed on the Grid Controller within an Omnidex Grid.  If the Grid Nodes are sending a large amount of data to the Grid Controller for reaggregation,​ this can be alleviated using DISTINCT table constraints or alternative partitioning strategies |
  
 +== Notes ==
  
 +Notes describing optimization decisions or suggestions that were made during the analysis of the query. ​ These notes often contain valuable instructions about how to improve the optimization of the query, and are usually the most valuable aspect of the query plan when tuning a query.
  
-== Actual Optimization == 
-== Warning Flags == 
-== Notes == 
 === The DETAILS Section === === The DETAILS Section ===
 +
 +The Details section describes the processing steps needed to process the query. ​ Steps are processed in the order shown, and steps that occur in a loop are indented if needed to show the processing flow.  Ultimately, these processing steps are the key to fully understanding the optimization of a query.
 +
 == Query processing steps == == Query processing steps ==
 +
 +The following processing steps are show  in this section:
 +
 +|<100% 25% 75% >|
 +^ Step                        ^ Description ​                                ^
 +| Aggregate ​                  | Aggregate rows using one or more Omnidex indexes, honoring grouped counts, distinct counts, sums, averages, mins and maxes. |
 +| Build cache                 | Create a memory-resident Hashed Data Cache for a result set to be referenced later in subsequent steps. |
 +| Build xref                  | Create a cross-reference file to aid in the processing of a many-to-many table relationship. |
 +| Create segment ​             | Create a data or index segment from the current results to be reused in subsequent steps. |
 +| Create table                | Create a table in the underlying database to receive the result set of the query. |
 +| Export ​                     | Export data values or index pointers into a file to be reused in subsequent steps. | 
 +| Fetchkeys ​                  | Fetch primary keys or rowids from the indexes for use in retrieve data from the underlying database. |
 +| Filter ​                     | Process criteria using in-memory comparisons rather than using an Omnidex index. |
 +| Insert ​                     | Insert rows into a table produced by an earlier Create Table step. |
 +| Join                        | Join from one table to another table using the Omnidex indexes. |
 +| Merge queues ​               | Combine and merge two intermediate queues generated by earlier Pass to Queue steps. |
 +| Pass to queue               | Pass intermediate data into a queue for sorting or use in subsequent steps. |
 +| Processing subquery ​        | Begin processing of a subquery. |
 +| Qualify ​                    | Process criteria against an Omnidex index. |
 +| Read from xref              | Read entries from a previously created cross-reference file while processing a many-to-many table relationship. |
 +| Retrieve ​                   | Retrieve data from the underlying database. |
 +| Retrieve from index         | Retrieve select items from a query or subquery using an Omnidex index, rather than the underlying database. |
 +| Return ​                     | Return the result set of the query. |
 +| Returning from subquery ​    | End processing of a subquery, returning results for use in subsequent steps. |
 +| Sample ​                     | Reduce the result set using an Omnidex index to a certain number of rows per control break, optionally favoring the min or max of a column. |
 +| Sort                        | Sort a queue for use in subsequent steps. |
  
  
Line 80: Line 105:
 ---- ----
 \\ \\
-**[[admin:​optimization:​home|Prev]]** | +**[[admin:​optimization:plans:​home|Prev]]** | 
-**[[admin:​optimization:​example|Next]]**+**[[admin:​optimization:plans:​example|Next]]**
  
  
 ====== Additional Resources ====== ====== Additional Resources ======
 See also: See also:
-{{page>:​admin:​indexing:​see_also&​nofooter&​noeditbtn}}+{{page>:​admin:​optimization:​see_also&​nofooter&​noeditbtn}}
  
 {{page>:​bottom_add&​nofooter&​noeditbtn}} {{page>:​bottom_add&​nofooter&​noeditbtn}}
 
Back to top
admin/optimization/plans/reference.1328209168.txt.gz · Last modified: 2016/06/28 22:38 (external edit)