Skip to Content
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

To report this post you need to login first.

28 Comments

You must be Logged on to comment or reply to a post.

    1. Lars Breddemann Post author

      Hi Kumar,

      there seem to have been problems today with the SCN blog system. Actually I thought, that this post didn’t went online at all…

      Will repost the pictures soon.

      Just republished the post with newly uploaded pictures. Should work now!

      Thanks for letting me know.

      Lars

      (0) 
  1. Vishal Soni

    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?

    (0) 
  2. Ravindra Channe

    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

    (0) 
  3. Hai Wei

    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

    (0) 
    1. Lars Breddemann Post author

      Hey Hai,

      glad you liked it and you’re asking the right questions here.

      We’re working on making the tool more useful and better to understand – but that obviously takes some time.

      Meanwhile, maybe you can proceed with the information from some of my other presentations:

      Also, it’s worth mentioning that the SPS 6 documentation had been improved (again) and it now also contains information on PlanViz. You can find it in the

      SAP HANA Administration Guide > Query Analysis in the SAP HANA Database

      And this is even included in the online help of SAP HANA Studio nowadays 🙂

      – Lars

      (0) 
      1. Ashok Oleti

        Hi Lars,

        Sounds like the 3 presentations you mentioned here are interesting but could not access them.

        This webpage has a redirect loop

        ERR_TOO_MANY_REDIRECTS

        (0) 
        1. Lars Breddemann Post author

          There had been a SCN wide problem with document links that appears to be fixed now.

          However, have a look at the date of the post in which I linked them… these are ancient now in SAP HANA time. So, take a bucket of salt with you when digesting the presentations 😉

          (0) 
  4. Sathish Kumar

    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?

    (0) 
  5. Rodrigo Monge

    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

    (0) 
      1. Rodrigo Monge

        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

        (0) 
        1. Lars Breddemann Post author

          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

          (0) 
    1. Lars Breddemann Post author

      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

      (0) 
  6. Anindita Bhowmik

    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

    (0) 
    1. Lars Breddemann Post author

      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

      (0) 
  7. Kingsley Ufumwen

    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

    (0) 
    1. Lars Breddemann Post author

      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

      (0) 
  8. Tian Song

    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

    (0) 
    1. Shirish Karandikar

      Hello Gurus,

      Small query.

      Does this “Memory Allocated” shows the figure of memory allocated per execution of the query?

      In other words, in above case, user1 and user2 execute the same query, will 742 * 2 MB will be allocated?

      Shirish.

      (0) 
      1. Lars Breddemann Post author

        Hi Shirish

        please open a new thread in the discussion forum for your question. The blog post comments are not well suited to discuss the related topics in depth.

        Cheers,

        Lars

        (0) 

Leave a Reply