analyzing table Joins in HANA

In SAP HANA, if a query on joined tables takes a long time, you can analyze how the tables are joined and in what order. A join evaluation (je) trace of joined SAP HANA tables provides the details you need for such an analysis.

join eval=debug  (enable the parameter)

Context : The je trace provides a lot of information that is hard to consume if you are not an SAP HANA join engine expert; however, it does provide some useful information for performance analysis. From the information within the trace files, you can see which table is used as the first table
when processing a join and how the order of tables in the join is defined. You can use this information to determine whether query performance is negatively impacted by the table join.

To start a je trace and analyze the relevant trace information, proceed as follows:

Procedure :

- In the Administration editor, choose Trace Configuration and create a new user-specific trace.
- The Trace Configuration dialog box opens.
- Specify a context name.
- The context name appears as part of the trace file name and should be easy for you to recognize and later find.
- Specify your database user or application user.
- Select the Show All Components checkbox.
- Enter join as filter text and search for the join_eval component.
- For the join_eval component, select DEBUG as the system trace level.
- Choose Finish.
- Run the query you want to trace.
- Switch off the trace by deleting the user-specific trace configuration.
- On the Diagnosis Files tab, search for the indexserver trace file, open the file, and select Show Entire File.
- From the end of the file, search backwards for the beginning of the trace section.
- The trace section starts with i TraceContext TraceContext.cpp.
- In the trace section, analyze the following trace information:
        Estimations for the WHERE conditions
        Table size and join conditions
        Join decision