When developing queries, always perform “An explain” on the query before you run it. Explains can give a lot of information the way optimizer will execute a query.
To perform an “Explain”, simply add the explain keyword prior to your select/ insert/ update/ delete statement and execute it.
The Explain statement is used to aid in identifying potential performance issues, it analyses the SQL and breaks it down into its low level process. Unfortunately the output can be very difficult to understand for an untrained person, but there are some points to recognize: Confidence Level and Product Joins.
Confidence Level
Customer attempts to predict the number of rows which will result at each stage in the processing, and will qualify the prediction with a confidence level as shown below:
· No Confidence – There is no statistics available.
· Low Confidence – Statistics are difficult to use precisely.
· High Confidence - Optimizer is sure of the results based on the stats available.
Explain Select * from DB1.Table1;
It will produce output like this:
1) First, we lock DB1.Table1 for access.
2) Next, we do an all-AMPs RETRIEVE step from
DB1.Table1 by way of an all-rows scan
with no residual conditions into Spool 1, which is built locally
on the AMPs. The size of Spool 1 is estimated with high
confidence to be 141 rows. The estimated time for this step is
0.15 seconds.
3) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.15 seconds.
Whilst this output is not all that user friendly (especially for complex queries) a number of useful things are present in the output, including a total estimated time to run the query, the join methods used, and the confidence levels that the optimizer has used while calculating number of rows/elapsed time that the query steps will take.
The best way to use “Explain" is to compare how your changes are affecting the way a query will run. Do this by changing the query a little at a time observing how your changes affect the complexity and total estimated time of the “Explain”.
REMEMBER TO ENSURE STATS HAVE BEEN COLLECTED ON PI, JOIN AND SELECTION COLUMNS!
If stats are not present for the required columns, one can notice a number of Low Confidence estimations in the Explain. See the statistics section of this document for further information on checking stats have been collected, and arranging their collection/refresh.
No comments:
Post a Comment