topic Not able to understand Internal_Function in explain plan ? in Application Development and Automation Discussions

Not able to understand Internal_Function in explain plan ?

Former Member — Thu, 16 Sep 2010 11:46:52 GMT

Hi,

I am getting the folowing filter predicate for table CRMD_ORDER_INDEX :

(INTERNAL_FUNCTION("T_00"."OBJECT_TYPE") AND

"T_00"."CLIENT"=:A1 AND

"T_00"."TEMPLATE_TYPE_IX"=:A9 AND

INTERNAL_FUNCTION("T_00"."STAT_QUOT"))

The query is :

SELECT

/*+

FIRST_ROWS (89)

DISTINCT T_00 . "HEADER"

FROM

"CRMD_ORDER_INDEX" T_00 INNER JOIN "CRMD_ORDERADM_H" T_01 ON T_01 . "CLIENT" = :A0 AND T_01 . "GUI

D" = T_00 . "HEADER"

WHERE

T_00 . "CLIENT" = :A1 AND T_01 . "OBJECT_TYPE" = :A2 AND T_00 . "OBJECT_TYPE" IN ( :A3 , :A4 ,

:A5 , :A6 , :A7 , :A8 ) AND T_00 . "TEMPLATE_TYPE_IX" = :A9 AND ( T_00 . "STAT_QUOT" = :A10 OR

T_00 . "STAT_QUOT" = :A11 ) AND ( T_01 . "PROCESS_TYPE" IN ( :A12 , :A13 , :A14 , :A15 , :A16 ,

:A17 , :A18 , :A19 , :A20 , :A21 ) AND ( T_00 . "SALES_ORG" = :A22 OR T_00 . "SERVICE_ORG" = :A23

OR T_00 . "DIS_CHANNEL" = :A24 OR T_00 . "SALES_OFFICE" IN ( :A25 , :A26 , :A27 , :A28 , :A29 ,

:A30 , :A31 , :A32 , :A33 , :A34 , :A35 , :A36 , :A37 , :A38 , :A39 , :A40 , :A41 , :A42 , :A43 ,

:A44 , :A45 , :A46 , :A47 , :A48 , :A49 , :A50 , :A51 , :A52 , :A53 , :A54 , :A55 ) OR T_00 .

"SALES_GROUP" IN ( :A56 , :A57 , :A58 , :A59 , :A60 , :A61 , :A62 , :A63 , :A64 , :A65 , :A66 ,

:A67 , :A68 , :A69 , :A70 , :A71 , :A72 , :A73 , :A74 , :A75 , :A76 , :A77 , :A78 , :A79 , :A80 ,

:A81 , :A82 , :A83 , :A84 , :A85 , :A86 ) OR T_00 . "SALES_ORG" = :A87 AND T_00 . "SALES_OFFICE" =

:A88 AND T_00 . "SALES_GROUP" = :A89 AND T_00 . "DIS_CHANNEL" = :A90 AND T_00 . "SERVICE_ORG" =

:A91 ) OR T_01 . "PROCESS_TYPE" IN ( :A92 , :A93 , :A94 , :A95 , :A96 , :A97 , :A98 , :A99 ,

:A100 , :A101 ) AND T_00 . "SALES_ORG" IN ( :A102 , :A103 ) AND T_00 . "SERVICE_ORG" IN ( :A104 ,

:A105 ) AND T_00 . "DIS_CHANNEL" IN ( :A106 , :A107 ) AND T_00 . "SALES_OFFICE" IN ( :A108 ,

:A109 ) AND T_00 . "SALES_GROUP" IN ( :A110 , :A111 ) )

What index could be created since both tables involved are going for full scan ?

Re: Not able to understand Internal_Function in explain plan ?

HermannGahm — Thu, 16 Sep 2010 15:35:53 GMT

Hi,

looks like you are doing a sql cache analysis with some consulting help from sdn...

the internal function is usually a data type conversion.

reg. the index... the fields that limit the result set most should be in the index

where the fields used with equal or in operators should be in the beginning of

the index. you have to get hte bind variables and check e.g. with se16 which are

the selective fields.

Kind regards,

Hermann

Re: Not able to understand Internal_Function in explain plan ?

Former Member — Thu, 16 Sep 2010 16:14:35 GMT

Hi Hermann,

I am currently working in Performance tuning for Expensive SQLs in SAP CRM. It may rightfully seem as you have mentioned. I have had some success with DB Free Cache rising from 92 to 98.7%.

Can I know any source of comprehensive text or tutorial about the concepts you are talking, if you like ?

I would really be thankful.

Regards,

Chitwanjit Singh

Re: Not able to understand Internal_Function in explain plan ?

HermannGahm — Thu, 16 Sep 2010 18:22:58 GMT

Hi,

> Can I know any source of comprehensive text or tutorial about the concepts you are talking, if you like ?

check the final section of Note 618868 and 766349.

Besides that, try to learn from somebody with experience. Learning SQL tuning takes time...

Kind regards,

Hermann