Using Hints with SQLScript procedures

[Taesuk]

Introduction

To resolve a problematic SQL statement inside a SQLScript procedure may require adding a hint to resolve the issue. For general SQL statements, a statement hint can be used to apply and manage hints without the  changing the original source code of the calling application.

For SQLScript procedures, table variables are used to assign results of a SQLStatement and the variable can be referenced in a subsequent statement. During the execution call of the procedure, the procedure logic will be compiled and cached. Some logic will be inlined into another statement and some can be materialized into a local temp table. The name of the temp table is determined during the optimization of the procedure.

Due to such behavior, the lifecycle of an inner SQL statement referencing local temp table(s) is only valid until the procedure gets recompiled or invalidated. The recompilation can occur due to logic changes and/or dependent object changes such as metadata changes. So, to add a hint for an inner SQL statement, changing the SQLScript source application code was the only available option. If the source was part of an application standard code, it was even more difficult to add the hint.

To simplify managing hints applied to inner SQL statements, 2 features have been recently delivered for SAP HANA Cloud and HANA 2.0 SPS07.

• Adding hints to SQLScript procedure code with cascade
• Pinning SQL Hints to SQLScript procedure using statement hints

The details of the functionality can be found in the documentation for both SAP HANA Cloud, SAP HANA database and SAP HANA, platform edition.

 

Demo environment setup

1. Let's first create a very simple table with some basic data

CREATE TABLE ORDERS(PRODUCT_ID NVARCHAR(10), DESCRIPTION NVARCHAR(100), PRIMARY KEY(PRODUCT_ID));
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000001', 'Description of product 1');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000002', 'Description of product 2');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000003', 'Description of product 3');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000004', 'Description of product 4');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000005', 'Description of product 5');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000006', 'Description of product 6');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000007', 'Description of product 7');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000008', 'Description of product 8');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000009', 'Description of product 9');
insert into ORDERS(PRODUCT_ID, DESCRIPTION) VALUES ('0000000010', 'Description of product 10');

2. Then create 2 procedures that is similar to how AMDP are structured with stub or wrapper procedure and main procedure which is called by the wrapper procedure.

For this example, the procedures are manually created which is very simple. The product_id is passed as input parameter and the data record is returned from ORDERS table for the given PRODUCT_ID. 

The main procedure with the following logic to return the data record as output

CREATE OR REPLACE PROCEDURE GET_ORDERS(
	in  "IV_PRODUCT_ID" NVARCHAR(10), 
	out "ET_ORDER" ORDERS)
language sqlscript 
sql security invoker as 
begin
	ET_ORDER = SELECT * FROM ORDERS where PRODUCT_ID = :IV_PRODUCT_ID;
end;

And the stub procedure is created with a timestamp as a wrapper procedure of the main procedure.

CREATE OR REPLACE PROCEDURE "GET_ORDERS#20220227123129"
(
	IN "IV_PRODUCT_ID" NVARCHAR(10)
)
LANGUAGE sqlscript 
sql security invoker as
begin
	call GET_ORDERS("IV_PRODUCT_ID" => :IV_PRODUCT_ID, "ET_ORDER" => :ET_ORDER);
	select * from :ET_ORDER;
end;

Applying hints to inner SQL statements of SQLScript procedure

First, we will check the default behavior prior without apply hints.

call "GET_ORDERS#20220227123129"('0000000010');

  A single row will be returned for the given product_id

Looking into the SQL plan cache, only the stub procedure call was cached while the main procedure call was inlined to the stub procedure logic during the SQLScript procedure compilation.

Also, the inner select statement is cached  as the following

/* procedure: "DBADMIN"."GET_ORDERS" variable: ET_ORDER line: 7 col: 2 (at pos 141) */ SELECT * FROM ORDERS where PRODUCT_ID = CAST(N'0000000010' AS NVARCHAR(10))

The statement is prefixed with a comment indicating that the result was assigned to ET_ORDER table variable at line: 7 col:2 in GET_ORDERS procedure.

This time we would like to check the behavior of the new features starting with adding hints to SQLScript procedure code with cascade. 

Let's clear the SQL plan cache 

ALTER SYSTEM CLEAR SQL PLAN CACHE;

Now call the procedure again with adding a hint cascading to all inner statements.

call "GET_ORDERS#20220227123129"('0000000010') with hint (NO_USE_HEX_PLAN cascade);

The result is the same returning 1 record

Then check the SQL plan cache again for the inner SQL statement cached and find the hint cascaded down from the procedure call and used.

/* procedure: "DBADMIN"."GET_ORDERS" variable: ET_ORDER line: 7 col: 2 (at pos 141) */ SELECT * FROM ORDERS where PRODUCT_ID = CAST(N'0000000010' AS NVARCHAR(10)) WITH HINT(NO_USE_HEX_PLAN)

Finally, we will check the 2nd option. Let's clear the SQL plan cache again

ALTER SYSTEM CLEAR SQL PLAN CACHE;

We will now use the statement hint to add USE_HEX_PLAN directly to the inner SQL statement this time.

ALTER SYSTEM ADD STATEMENT HINT (USE_HEX_PLAN) ON PROCEDURE GET_ORDERS 
FOR SELECT * FROM ORDERS where PRODUCT_ID = :IV_PRODUCT_ID;

We call the procedure again without any hints as we had for the default call and check the default behavior prior without apply hints. 

call "GET_ORDERS#20220227123129"('0000000010');

Then finally check the SQL plan cache for the inner SQL statement. Notice that the hint was appended with a comment indicating that the hint was added by the Statement Hint.

/* procedure: "DBADMIN"."GET_ORDERS" variable: ET_ORDER line: 7 col: 2 (at pos 141) */ SELECT * FROM ORDERS where PRODUCT_ID = CAST(N'0000000010' AS NVARCHAR(10)) /*added by Statement Hint:*/ with hint(USE_HEX_PLAN)

 

Conclusion

We have checked 2 different methods of adding hints to inner SQL statements of SQLScript procedure which would be useful to manage the performance of the inner SQL statement of a SQLScript procedure without changing the SQLScript code itself.

But one thing to keep in mind is that mixing the 2 methods can cause unexpected results such as the following by adding 2 conflicting hints. 

/* procedure: "DBADMIN"."GET_ORDERS" variable: ET_ORDER line: 7 col: 2 (at pos 141) */ SELECT * FROM ORDERS where PRODUCT_ID = CAST(N'0000000010' AS NVARCHAR(10)) /*added by Statement Hint:*/ with hint(USE_HEX_PLAN, NO_USE_HEX_PLAN)