Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA Administration
and in this video we will discuss SQL trace.
This video has been recorded on release SAP HANA 2.0 SPS 03 (April 2018)
and SAP HANA Cockpit 2.0 SP 06 (April 2018, as well).
For earlier or later releases, please check the playlist on the SAP HANA Academy.
Hi, I am Denys van Kempen.
SQL trace, or S-Q-L, if you prefer, collects information about the SQL statements.
No surprise there.
Here, we are looking at such a SQL trace file and you might be thinking
hmmm, that is looking a bit complex, and you know what? You are right.
In fact, this particular trace captures a single SQL statement
SELECT ‘Hello World’ FROM DUMMY
and, as you can see, a lot is going on in the background,
ending with an ALTER SYSTEM ALTER CONFIGURATION statement
to disable SQL Trace.
SQL Trace is one of the tools you can use to analyze SAP HANA system performance,
it is very comprehensive, lots of data in other words, which also makes it not the easiest to use
and, typically, you are most likely only working SQL trace when dealing with SAP Support.
For development and SQL statement analysis, the Expensive Statement Trace or Explain Plan
are easier to use, and for overall system performance, the Performance Monitor
would be a much more obvious choice.
However, if you are cu rious, or dealing with a very complex issue, you could certainly
take a look at what SQL Trace has to offer.
In this video, we will take a look at how we can enable SQL trace.
In the next video, we will discuss the SQL Trace Analyzer, which can help us to get some
useful information out of the trace files.
Apart from the Database Trace, all tracing needs to be activated.
So, also, SQL trace is inactive by default.
This is a good thing, as tracing typically generates a fair amount of data and the trace
files tend to grow in size rather fast and can easily, when left unattended,
fill up your entire file system.
OK.
How to enable?
Pretty straight forward.
We can use SAP HANA studio. We can use SAP HANA database explorer.
Both provide a UI, a user interface, to make it a bit easier to make a selection for the different options
and parameters, but in the end, as always, it just gets converted to SQL, so let’s
take a look first at the syntax.
ALTER SYSTEM ALTER CONFIGURATION, sqltrace, trace = on.
This you can use in scripts, for example.
To capture the SQL of a tenant database, you would use indexserver.ini (the actual database engine).
For the system database, you would use nameserver.ini.
You can specify the name of the file, and there are a couple of more parameters which
we can specify and to switch it off, you can set the trace parameter to off,
or just unset the parameter, which will return it back to its default value, which for SQL Trace is off.
If we right-click the connection in the Database Explorer, we can select Trace Configuration,
and this will return a menu very similar to that of SAP HANA studio.
You may have seen this before.
There are some older videos in this playlist, which focus on SQL Trace configuration for
SAP HANA studio.
Here on the Trace Configuration tab, you can enable the different trace configurations.
As mentioned, some are quite useful, like the Expensive Statement Trace or Plan Trace.
Others probably will only be used by SAP Support, if ever, like the Kernel Profiler.
Select the pencil, to edit the different parameters.
We already looked at this when we examined the SQL statement.
Set the status to Active, Then you can select the trace level,
You can name the file. Maybe filter on a particular database or application user
Maybe filter on a particular table, or application,
Or a specific statement type: DML, DDL, Procedures.
Like I said, you would typically use SQL trace to investigate a particular issue and even
a single statement already produces quite some output.
So, in most cases, you really want to filter on some areas to zoom in on the issue.
OK - Now, in the Database Explorer this all pretty much looks the same.
The file extension, by default, is .py - for Python script file, which is what it is, in
fact, and in the next video, we will use the Trace Analyzer
to work with the trace file.
OK - If you want to know more, SQL Trace is documented in the SAP HANA Administration Guide.
Here, you can read about the different trace levels
Additional configuration Options And how to configure trace file rotation and
setting a file size limit, which is a very good idea as the files can grow in size very
quickly and may even fill up the entire file system, something we all like to avoid.
That’s it for how to enable SQL trace
Thanks for watching.
You can find more video tutorials on our YouTube channel.
If you would like to be informed about new video tutorials, please subscribe to our channel.
You can connect with us on LinkedIn or follow us on Twitter, as well for updates, and if
you are watching this video on YouTube, do not hesitate to leave your comments to the
video page and, if you like, give us your vote on this video.
Thank you for watching.