SQL tuning for a Z program

[Former Member]

One SQL in the Z program is in the top SQL GET list.

The sql is:

SELECT /*+ FIRST_ROWS (1) */ "CONTENT" FROM "ZT1004" WHERE "MANDT" = :A0 AND "SURVEY_ID" = :A1 AND "QUES_ID" = :A2 AND "ORDER_GUID" = :A3 AND ROWNUM <= :A4

SQL> describe sapsr3.zt1004;

Name Null? Type

-

---

-

---

-

---

MANDT NOT NULL VARCHAR2(9)

INQUIRY_GUID NOT NULL RAW(16)

ORDER_GUID NOT NULL RAW(16)

SURVEY_ID NOT NULL VARCHAR2(30)

QUES_ID NOT NULL NUMBER(3)

CONTENT NOT NULL VARCHAR2(300)

Only UNIQUE index for this table:

SQL> select DBMS_METADATA.GET_DDL('INDEX','ZT1004~0','SAPSR3') from dual;

DBMS_METADATA.GET_DDL('INDEX','ZT1004~0','SAPSR3')

-

---

CREATE UNIQUE INDEX "SAPSR3"."ZT1004~0" ON "SAPSR3"."ZT1004" ("MANDT", "INQ

UIRY_GUID", "ORDER_GUID", "SURVEY_ID", "QUES_ID")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTI

CS

STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P

OOL DEFAULT)

TABLESPACE "PSAPSR3"

Edited by: ren qc on Sep 15, 2010 4:31 AM

[Former Member]

Explain plan;

SQL Statement

-

---

SELECT

/*+

FIRST_ROWS (1)

*/

"CONTENT"

FROM

"ZT1004"

WHERE

"MANDT" = :A0 AND "SURVEY_ID" = :A1 AND "QUES_ID" = :A2 AND "ORDER_GUID" = :A3 AND ROWNUM <= :A4

Execution Plan

-

---

System: CPD

-

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

-

---

0	SELECT STATEMENT		1	49	4896 (1)
1	COUNT STOPKEY
2	TABLE ACCESS BY INDEX ROWID	ZT1004	1	49	4895 (1)
3	INDEX RANGE SCAN	ZT1004~0	1		4895 (1)

-

---

Predicate Information (identified by operation id):

-

---

1 - filter(ROWNUM<=TO_NUMBER(:A4))

3 - access("MANDT"=:A0 AND "SURVEY_ID"=:A1 AND

"QUES_ID"=TO_NUMBER(:A2))

filter("SURVEY_ID"=:A1 AND "QUES_ID"=TO_NUMBER(:A2) AND

RAWTOHEX("ORDER_GUID")=:A3)

[Former Member]

There is only 1 row was selected but the cost is 4896, it is a little high. Then I ask our DBA to have a check. He said we set INQUIRY_GUID & ORDER_GUID to RAW format and actually sql need convert it from TEXT in the screen to RAW.

His suggestion:

1) delete Unique index and create a new index which convert RAW to text to easy select.

2) Use Oracle native SQL as below:

SELECT /+ FIRST_ROWS (1)/ CONTENT FROM SAPSR3.ZT1004 WHERE MANDT = :A1 AND SURVEY_ID = :A2 AND QUES_ID = :A3 AND ORDER_GUID = HEXTORAW(:A3) AND ROWNUM <= :A4

We've test the suggest SQL and found the explain plan is different:

SQL Statement

-

---

SELECT

/*+

FIRST_ROWS (1

*/

CONTENT

FROM

SAPSR3.ZT1004

WHERE

MANDT = :A1 AND SURVEY_ID = :A2 AND QUES_ID = :A3 AND ORDER_GUID = HEXTORAW(:A3) AND ROWNUM <= :A4

Execution Plan

-

---

System: CPD

-

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

-

---

0	SELECT STATEMENT		1	49	232 (3)
1	COUNT STOPKEY
2	TABLE ACCESS BY INDEX ROWID	ZT1004	1	49	232 (3)
3	INDEX SKIP SCAN	ZT1004~0	1		231 (3)

-

---

Predicate Information (identified by operation id):

-

---

1 - filter(ROWNUM<=TO_NUMBER(:A4))

3 - access("MANDT"=:A1 AND "ORDER_GUID"=HEXTORAW(:A3) AND

"SURVEY_ID"=:A2 AND "QUES_ID"=TO_NUMBER(:A3))

filter("SURVEY_ID"=:A2 AND "QUES_ID"=TO_NUMBER(:A3) AND

It seems to me both of them are not SAP standard suggestion. GUID is commonly set to RAW format in CRM system as I know.

Need your experts' opinion.

Thanks,

James

[former_member192616]

Hi James,

first of all: thank you very much for the detailed information. (It is not very common that all relevant information is given for questions here... .)

Your main problem is that the field INQUIRY_GUID comes before the otherfields "SURVEY_ID" = :A1 AND "QUES_ID" = :A2 AND "ORDER_GUID" = :A3. Therefore we can only do rather big scan operations (may it be a big range scan with client only as in your first case, or a index skip scan as in your second case or a full table scan which was not chosen here.

In order to optimize you have either to specify INQUIRY_GUID in the where clause or create a secondary index with some or all of the fields "SURVEY_ID" = :A1 AND "QUES_ID" = :A2 AND "ORDER_GUID" = :A3. Check with SE16 which field or field combination limits the resultset most and create an index for those if you cant specify INQUIRY_GUID.

Hope this helps,

Hermann

[Former Member]

Thanks Hermann,

It sounds reasonable.

Below is the information for UNIQUE Index ZT1004~0:

Column Name #Distinct

MANDT 2

INQUIRY_GUID 1,127

ORDER_GUID 600,130

SURVEY_ID 35

QUES_ID 33

Last statistics date 2010.08.25

Analyze Method Sample 128,972 Rows

Levels of B-Tree 3

Number of leaf blocks 48,857

Number of distinct keys 4,299,067

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 824,733

We will try to build another index based on ORDER_GUID & SURVEY_ID & QUES_ID. If this works then use this one to replace the Unique index.

System is frozen now so will try this later.

[volker_borowski2]

Hi,

> 1) delete Unique index and create a new index which convert RAW to text to easy select.

no need for a function based index on this.

Oracle uses the index for GUID (RAW) Columns, only EXPLAIN fails for this without hextoraw.

Check note 551400 on this !

But an index with the other columns or changing the field sequence will help.

Volker