Troubleshoot SQL with SAP HANA Database Explorer

The SQLScript code analyzer can be used to identify patterns indicating problems in code quality, security, or performance. Follow the steps below to try it out.

1. Create a new procedure to generate fictitious hotel reservations. The procedure accepts a number of reservations to generate and a room type.

   CREATE OR REPLACE PROCEDURE HOTEL.RESERVATION_GENERATOR2(
   		IN numToGenerate INTEGER,
   		IN rmType STRING
   	)
   	LANGUAGE SQLSCRIPT AS
   BEGIN
   	USING SQLSCRIPT_PRINT AS PRTLIB;
   	DECLARE val INT := 0;
   	DECLARE stmt VARCHAR(256) := '';
   	DECLARE rno INT := 0;
   	DECLARE cno INT := 0;
   	DECLARE hno INT :=0;
   	DECLARE arriveDate DATE := null;
   	DECLARE arriveDateString STRING := '';
   	DECLARE departDate DATE := null;
   	DECLARE departDateString STRING := '';
   	DECLARE randomDaysFromCurrent INT :=0;
   	DECLARE randomLengthOfStay INT :=0;
   	DECLARE unusedVar INT :=0;
   	DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
   	DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 301
   	SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
   	unusedVar := unusedVar + 1;
   	-- IF IS_SQL_INJECTION_SAFE(rmType) <> 1 THEN
   	--     SIGNAL MYCOND SET MESSAGE_TEXT = 'Invalid field ' || rmType;
   	-- END IF;
   
   	WHILE (val < numToGenerate) DO
   		-- generate random room number from 100-300
   		rno := FLOOR(RAND_SECURE() * 200) + 1 + 100;
   		-- generate random customer number from 1000-1014
   		cno := FLOOR(RAND_SECURE() * 14) + 1 + 1000;
   		-- generate random hotel number from 10-26
   		hno := FLOOR(RAND_SECURE() * 16) + 1 + 10;
   
           -- generate random number of days to be used for arrival date.  
   		-- date range is one year in the past to one year in the future
   		randomDaysFromCurrent := FLOOR(RAND_SECURE() * 730) + 1 - 365;
   		arriveDate := ADD_DAYS( TO_DATE( CURRENT_DATE, 'YYYY-MM-DD' ), randomDaysFromCurrent );
   		arriveDateString := '''' || TO_VARCHAR( arriveDate, 'YYYY-MM-DD' ) || '''';
   		-- generate a random number of days to stay
   		randomLengthOfStay := FLOOR(RAND_SECURE() * 7) + 1;
   		departDate := ADD_DAYS( arriveDate, randomLengthOfStay );
   		departDateString := '''' || TO_VARCHAR( departDate, 'YYYY-MM-DD' ) || '''';
   
           -- Reservations Columns: RNO, CNO, HNO, Type, Arrival, Departure
   		stmt := 'INSERT INTO HOTEL.RESERVATION (RNO, CNO, HNO, TYPE, ARRIVAL, DEPARTURE) VALUES(' || rno || ',' || cno || ',' || hno || ',' || rmType || ',' || arriveDateString || ',' || departDateString || ');';
       PRTLIB:PRINT_LINE(stmt);
       EXEC(stmt);
   		val := val + 1;
   	END WHILE;
   	PRTLIB:PRINT_LINE('Rows inserted: ' || val);
   END;
   

2. Try it out.

   CALL "HOTEL"."RESERVATION_GENERATOR2"(NUMTOGENERATE => 3,RMTYPE => '''suite''');
   

   

3. Right-click the procedure and choose Analyze SQLScript Code.

   

4. Notice that three issues were raised.

   

   A double-click on an issue will open another tab containing the SQL of the procedure and the issue will be highlighted.

   

5. To address these issues, make the following changes and rerun the SQL in the SQL Console tab.

   • The first issue can be resolved by commenting out unusedVar on lines 19 and 23.
   • The second issue can be resolved by checking the input parameter rmType with the IS SQL INJECTION SAFE function. Uncomment lines 24 - 26 to try this.

6. Right-click the procedure and choose Analyze SQLScript Code.

   Notice that only one warning remains

   

Explain plan provides a compiled plan in tabular form without executing it. This can be useful in analyzing certain situations when it is hard to reproduce issues without causing the issue.

1. Run the following SQL.

   SELECT HOTEL_NAME, count(*) AS "NUMBER OF RESERVATIONS" FROM HOTEL.RESERVATION_VIEW group by HOTEL_NAME order by HOTEL_NAME;
   

   

2. Choose Analyze | Explain Plan to see the complied plan without executing the statement.

   

For further details see the links below.

View Execution Plans for SQL Statements, Procedures, and Anonymous Blocks

Explain Plan

The SQL Analyzer provides a graphical view of how an analyzed SQL statement was executed which can provide additional insights into query execution. The latest version is available as an extension for Visual Studio Code. A .plv file can be generated in the SAP HANA database explorer which can then be opened in the SQL Analyzer. A previous version of the SQL Analyzer is also included in the on-premise SAP HANA database explorer, the on-premise SAP HANA cockpit as well as SAP HANA studio.

1. Run the following SQL.

   SELECT HOTEL_NAME, count(*) AS "NUMBER OF RESERVATIONS" FROM HOTEL.RESERVATION_VIEW group by HOTEL_NAME order by HOTEL_NAME;
   

   

2. If using SAP HANA Cloud, HANA database, choose Analyze | Generate SQL Analyzer Plan File.

   

   Once the file has been saved it can then be opened in Visual Studio Code as shown below.

   • Visual Studio

     Install the plugin

     

     Add the plan file

     

3. If using an on-premise edition, choose Analyze | Analyze SQL to execute the SQL and to view graphically how the SQL was executed. Alternatively, choose to download the plv file and open it in the SQL Analyzer in Visual Studio Code or the SAP Business Application Studio.

   

4. Information is shown about the top 5 dominant operators, the top 10 critical path operators, the time it took to compile and execute the SQL statement, the peak memory, the result record count and accessed tables.

   

5. Switch to the PLAN GRAPH tab to visually see details of the query execution.

   

Refer to Analyzing Statement Performance for more information.

A SQL trace can be helpful when debugging a problem or in identifying SQL statements that a particular application is using. The following steps demonstrate an example of enabling a SQL trace and then viewing the resulting trace file by using the SAP HANA database explorer.

1. The database overview presents general information about a database. Right-click a database and choose Show Overview.

   

   Different information and statistics are shown for the selected database.

   

2. To determine the SQL requests that were executed in order to populate the various fields of the overview (e.g. memory used), enable a SQL trace by running the SQL statements below in a SQL console.

   ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('sqltrace', 'trace') = 'on', ('sqltrace', 'application') = 'sap_xsac_hrtt', ('sqltrace', 'user') = 'DBAdmin' WITH RECONFIGURE;
   

   ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('sqltrace', 'trace') = 'on', ('sqltrace', 'tracefile') = 'database_overview', ('sqltrace', 'application') = 'sap_xsac_hrtt', ('sqltrace', 'user') = 'SYSTEM' WITH RECONFIGURE;
   

   Additional details can be found in the Configuration Parameter Reference.

3. Press the refresh button on the database overview.

   

4. Turn off the SQL trace as tracing can have an effect on performance and takes up storage space.

   ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('sqltrace', 'trace') = 'off' WITH RECONFIGURE;
   

   Note that when using the SAP HANA database explorer running in SAP HANA on-premise or HANA as a Service, it is possible to configure traces using a graphical interface.

   

   A set of tiles display that provide a graphical user interface to configure different trace types.

   

   The SQL Trace dialog is shown below.

   

5. Examine the trace file.

   

   Note that in SAP HANA Cloud, HANA database, the file can be found under the sqltrace folder while with SAP HANA on-premise, the trace file appears under the other folder.

   The SQL queries that return information about the memory being used by the HANA instance can be viewed in the file. For example, the highlighted query is one such SQL request.

   

6. If you are unsure where to find a trace file, choose Show Files.

   

   Notice that trace files can be found by using sorting and filtering.

   

For additional details, consult the Traces topic in the SAP HANA Administration Guide for SAP HANA Platform and SAP Note 2119087 - How-To: Configuring SAP HANA Traces.

It can be important to examine SQL statements that consume large amounts of time, CPU or memory. The following steps demonstrates how to enable an expensive statements trace.

1. The following SQL will enable tracing of expensive statements, set the threshold values, run some statements that will exceed the thresholds, and then disable expensive statement tracing.

   ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'DATABASE') SET ('expensive_statement', 'enable') = 'on' WITH RECONFIGURE;
   ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'DATABASE') SET ('expensive_statement', 'threshold_memory') = '41943040' WITH RECONFIGURE;   -- 40 MB
   ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'DATABASE') SET ('expensive_statement', 'threshold_duration') = '3000000' WITH RECONFIGURE;  -- 3 sec
   
   CALL HOTEL.RESERVATION_GENERATOR(1000);  --consumes more than 40 MB of memory
   
   DO BEGIN
     -- Wait for a few seconds
     USING SQLSCRIPT_SYNC AS SYNCLIB;
     CALL SYNCLIB:SLEEP_SECONDS( 3 );  --runs for longer than 3 seconds
     -- Now execute a query
     SELECT * FROM M_TABLES;
   END;
   
   ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'DATABASE') SET ('expensive_statement', 'enable') = 'off' WITH RECONFIGURE;
   

2. The list of statements that exceed the expensive statement threshold values can be found in the view M_EXPENSIVE_STATEMENTS.

   select TOP 2 DURATION_MICROSEC/1000000, CPU_TIME/1000000, MEMORY_SIZE/1048576, START_TIME, RECORDS, STATEMENT_STRING from  M_EXPENSIVE_STATEMENTS order by start_time desc;
   

   

3. When using SAP HANA Cloud, database explorer, the messages tab also contains detailed information about the duration, CPU, and memory consumed by a statement.

   

Congratulations! You have now explored the available tools to help diagnose and debug SQL or SQLScript.