3/31/2021 0 Comments Trane Trace Cds
Trace and analyze single critical SQL statements with ST05 (Performance Trace) and HANA PlanViz.Since my main interest is the performance of CDS views, I will concentrate on the resource and runtime aspects.On top of the static complexity of the view itself, additional tables or views might be joined when associations are followed, or when the view has assigned DCLs (Data Control Language objects, that is authority checks).
This recipe is not in the SQL statement itself, since SQL is a declarative language: it just describes what to be processed, not the how. There is no specification, whether a certain join should be processed before or after doing some aggregation, nor whether the database should apply a nested-loop join, a hash join, or a semi join to get to the result. In fact, the how is left to the SQL processor, which does, based on the rules of relational algebra and with the help of database table statistics, come up with an execution plan to process the SQL statement. Ideally, join reordering is possible between all joins of the SQL statement, and also other operators like filters or aggregations can be moved around in the plan variants. It is therefore sometimes difficult to trace back a performance problem, which has been identified in the HANA PlanViz tool, to the corresponding CDS view. ![]() This leads to a situation, where optimizations that are desirable from a high-level perspective (e.g. In the second case, only the summed-up value needs to be converted. Since these cases have different semantics (and might return different results), the SQL processor may not switch the order of the operators. Since the SQL processor needs to make decisions in a short time frame, it will only evaluate a limited number (some thousands) of possible plan variants. The chosen plan might therefore be not the theoretical global best plan, if as a rule of thumb an SQL statement joins more than 5 tables. In the worst case this can lead to an erratic runtime behavior, with a statement running slow that before has been executed quite fast. Example: a CDS view has a calculated key field; here the key field is concatenated from two other fields. When used in a join condition, a calculated field may hinder the push-down of a filter to one of the joined branches. Trane Trace Cds Upgrade A CertainAfter a HANA upgrade a certain portion of SQL statements will result in different execution plans. ![]() One finds that the resulting plans are totally different; for example, the critical path containing the slowest operators (red circles) changed its position. That is why complex statements must be tested with production-like data to achieve relevant results. The screen shot below shows the SQL Plan Cache for SQL statements having a high plan preparation time (column TOTALPREPARATIONTIME). The values are given in s, so these statements took many seconds for plan preparation. As mentioned above, the SQL optimizer might not be able to identify the best plan for a SQL statement, if complexity gets too high. Also, execution plans and execution times for complex SQL statements might change after re-creation of plans.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |