Skip to Content

Show me the timelines, baby!

Based on revision 46 (SPS 5) of SAP HANA Studio and Database

Hello once again!

While reading through the recently released SPS 05 RKT material (here) (that is, IF you have access to this RKT material), specifically the “What’ New Admin & Monitoring” document I found that the authors mentioned that the plan visualization feature we got in SPS 04 had been improved by adding an execution timeline to the display.

Most people that have been involved with HANA performance analysis already know this feature from the unsupported (but not yet superfluous) HDBAdmin tool.

When I read about this, I immediately checked where this new feature was hidden in my rev. 46 HANA studio and I did have to look for a while.

Thus I thought you might be interested to get the bread crumbs that lead to the nice feature.

Let’s start at the beginning …

The plan visualizer is available from multiple places in HANA studio.

The two places I know of are:

  1. any SQL window
    just mark your SQL statement and right-click to get the context menu -> there it is)
  2. the SQL Plan Cache data dump grid (Administration perspective -> Performance -> SQL Plan Cache)
    Again, right click on the line you want to work with gets you to the option.

For this example I just used option 1:

PlanViz2.png

Visualize it!

The next thing you’ll see is a graphical version of explain plan, which is not really what you’re looking for in most of the cases.

You want more 😉

And you get it.

Take an educated guess how…

Correct!

The path to enlightment through more information involves using the context menu again.

So, pimp up your right mouse-button click skills and right-click on the graphic and select “EXECUTE” from the context menu.

PlanViz3.png

Now, the query is actually run in HANA and run time statistics are gathered.

Depending on the query this could take quite some time.

After some waiting you’ll get what you asked for, looking similar to this:PlanViz.png

If you don’t see the timeline (lower part of the scree, red box around it… got it?) window now already, you’ve to manually open it.
To do so, use

“Window” menu

  -> “Show View”

     -> “Other”

       -> Folder “PlanViz”

        -> “Timeline”

You’ll notice that there are fancy little triangles in those blue-ish boxes upper right corner.

Click on these to expand the boxes and see what happens “inside” of them.

This will also expand the operations on the time line display automatically.

Finally we have this functionality in HANA Studio and we don’t need to create performance traces anymore, just to get an idea on how a statement was processed by HANA.

Nice!

Thanks for that dev-Team and have a great holiday season and a happy 2013!

Cheers, Lars

28 Comments
You must be Logged on to comment or reply to a post.
  • Nice article. Unfortunately i don’t have access to  the RKT material. But i think i got enough information at least to get started with my own exploration.

    BTW, do we have any link/resource which mentions about version-by-version additions/enhancements in SAP HANA debugging features?

  • Every time I press Ctrl+Shift+V, I remember this blog. In last few days, I have spent more time understanding the query performance than actually writing queries. 🙂 (Still haven’t understood it quite well)

    Thanks Lars for such an informative blog.

    Regards,

    Ravi

  • Hi Lars,

    Thanks! Let me know such good tool like Visualized Plan in HANA studio. It would be gread, if you can tell more about the tool Visualized Plan:) I used it but the notation and dataflow is not so easy to understand and i cannot find any detail document about it.

    Best Regards,

    Hai

  • The SQL Plan Cache and any other way is not working out for Procedures. It would be tough to rewrite the procedure as seperate SQL queries. Is there a way to get the SQL performance when using a Stored Procedure?

  • Lars,

           Most of the time I have no problems to visualize a plan quickly, but there are some cases where the HS takes so long to display the plan after i click on Execute. As you can see in the image, the SQL execution completes in 7 seconds, but the tree might take 15 minutes to show up, then the timeline shows 99% in grey out of the 7 seconds consumed by the sql itself. Is this the normal behaviour?

    Capture.PNG

    Thanks

      • Lars, thanks for your quick response. I did further testings and found most of the time is spent in the fetch step, then if the resultset is small, the plan is immediately retrieved but if the query retrieves thousands of rows takes for ever. So I added the TOP clause to reduce the resultset, is this a valid approach to focus only on the plan? or there’s a chance this plan is not exactly the same?

        thanks

        • Well, if possible SAP HANA tries to push down as many restrictions as  possible, so introducing a TOP or LIMIT clause can change the actual execution path.

          However, if the result set really is small, the fetch time shouldn’t be too large.

          In case you want to further discuss this, please open a thread for it in the forum as this doesn’t relate directly to the blog.

          Cheers, Lars

    • The easiest way would be to just open the PlanViz files next to each other.

      Other than that, an integrated “compare two plans” feature is not released to public use.

      – Lars

  • Hi Lars,

    A very helpful blog post i must say. 🙂

    But, does the Vizualize plan work for attribute views and analytical views. Or is this a feature set only for Calculation views.

    I am using Sp09.

    Regards,

    Anindita Bhowmik

    • Thanks for the nice feedback! I am happy to hear that you find it still useful after such a long time since it’s publication.

      PlanViz works on all information views as well as SQL statements and even SQLScript procedures & functions.

      But instead of asking and waiting for my reply you could have just tested it yourself by trying out. 😉

      Cheers, Lars

  • Hi Lars,

    This is still very useful again and again i come back to this place because i have the link already

    I just wanted to say that it would be very good if you can change the title to reflect what the page is about

    What you currently have “Show me the timelines, baby!” is somewhat…

    Hope you understand what i mean

    thanks again for all your contributions

    Cheers

    Kingsley

    • Hi Kingsley

      thanks for the positive feedback on this blog post.

      In fact it is one of my most popular posts so far and it makes me proud that this little thing is still popular after a few years since I published it.

      While I appreciate your opinion on the title I chose, I don’t share it.

      I don’t find anything offending, discriminating, rude or otherwise disturbing in the title.

      And as this is in no way the official SAP documentation of the feature, but a piece of personal experience with the technology, over-applied political correctness speech is something I am free to avoid.

      Which I do.

      Anyone who knows me and my writing knows, that I communicate frank, a little bit provocative and sometimes peppered with some humour and irony.


      So, whoever takes offence by this blog posts title: take it easy!


      Cheers,

      Lars

  • Hi Lars,

    I have an interesting found in my customer’s environment, I would like to ask your opinion 🙂

    We see in Visual Plan, the execution time is 606.91 ms fetching 790,434 records.

    But when I gathering the the info from SQL Plan Cache, the same record executing around 11770 ms.

    So I was wondering WHY? I guess the Visual Plan just give you an estimated time of execution, but it might not be the case, maybe the SQL Plan in plan cache is not optimized caused it was executed slower than in Visual Plan?

    Thanks a lot!!

    2.JPG

    1.JPG