Hello dear experts,
I wonder if HANA provides some sort of performance data collection functionality for retrospective analysis.
Ideally it should be some functionality with options like the following:
- logging performance metrics per request (CPU, memory, exec time etc.) along with SQL text of request;
- set of service tables accumulating such data;
- ability to define logging settings on database/user/application level (to enable/disable logging for a user, level of detail to log);
- system level logging settings like "log detailed information for all queries with execution time>5ms" and "log only summary stats for all queries less 5ms";
- ability to set data retention rules for performance data collection history.
What we need is to track trends, compare and analyze performance before vs after hardware, software or application changes on a view/ procedure/ request levels. Also we'd like to see how performance is changing because of growing number of users, data volume growth etc.
Existing traces and views seem not enough for this. As a newbie, I can't find HANA functionality and objects which could help us to build such solution either.
I'd appreciate it if someone could tell me if there is a way.
Cheers,
Elena
Hi Michael,
I guess you mean this set of SQLs in SQL data collection, namely, ActiveStatements and ExpensiveStatements.
If I understood it right, ActiveStatements is a snapshot whereas ExpensiveStatements is only subset of statements.
What we need is to grab data about all SQLs in our predefined scope. Moreover, we need to log measures for a completed statement, how much CPU it took to fulfill a request etc.
We could fetch ActiveStatements data (or something similar) into our custom table(s) regularly (every sec or even every ms) but this would not guarantee we log the full picture.
Or maybe we could somehow change settings so all completed statements are logged in ExpensiveStatements. I understand it's a kind of crazy decision but it could give us the full log. Right?
Hi,
No, what you are referring to is the system views.The system views come standard with the system. The SQL scripts in the note were separately written and give far more detail and ease with option to modify in the MODIFY section. Please look at the note 1969700, its completely different. There is also a different note which explains each script and what it can do for you (searching you'll find this). I don't fully understand why you would like to analyse each SQL execution fully?. What is the business requirement for doing such a thing? Usually the situations I have seen just require analyse on a statement that is seeing some performance issues, rather than looking at every full execution?. There is a performance trace you can run, along with Plan Viz tracing? These should be sufficient for most cases I have seen.
Hi Michael,
I meant the same set of scripts. I just imported this set into HANA System Information tab. Mentioned ActiveStatements and Expensive Statements belong to the set (please see the screen attached in my previous comment). I looked through other SQLs in this set as well.
I think I misinterpreted nature of ActiveStatements and its underlying source M_ACTIVE_STATEMENTS initially. I believed it represented all statements running right at the moment. What means "active" statement? How long data is retained in M_ACTIVE_STATEMENTS?
Before joining HAHA project, I had been working with Teradata for many years, mainly in performance management projects. Teradata provides very detailed logging on a query instance level and you can manage what you want to log and for how long you retain it. Maybe Oracle 12 has similar functionality, I am not sure (at least in version 10 they didn't). So why you might want to have it:
- you observe some overall performance degradation. What means "degradation"? Just because users complain so?Was it different yesterday, one month ago etc.? How would you prove it was better or it was the same? You would need performance history to see how picture had been changing before.
- you face an issue with some specific business query, batch process etc. Now users are complaining it's getting longer to execute. The same issue. Yes, you can run performance trace to get the current picture but you can't analyze and support your point with retrospective data. Btw it can be the situation when nothing is wrong with this query but something has changed on a higher level (e.g. increased numbers of users, new applications rolled out to the prod system, some other very heavy and suboptimal query spoils overall picture regularly, and so on and so forth). If you don't have history, it's not always easy to find the root of the issue and prove it.
So if you have finely-grained history at your fingertips, you can do a lot. As far as I understand we don't have history in HANA. We have up-to date summary both in system views and the set of SQLs from the note. I think we could use it to build some solution to accumulate summary snapshots to track and analyze trends at least. But I do hesitate what source would be better to use, M_ACTIVE_STATEMENTS or M_SQL_PLAN_CACHE or some other data I am not aware about.
Regards,
Elena