1 Reply Latest reply on Nov 14, 2006 2:23 PM by 442199

    Expression filters performance questions


      I have several question considering Expression Filters performance:

      Can you, please, give general recomendation for improving expression filter performance. I mean java pool size or things like that (not indexing) that should be changed relatively to the standard settings.

      I am working with Oracle Database 10g Enterprise Edition Release If there was some relevant patches released?

      I have defined the same simple condition "field = 'AAA'" for 2, 10 and 20 times. I got statistics and defined an index as it described in the manual. I observe that time is growing linearly with amount of conditions. Why?

      I understood that this feature is implemented in JAVA. It means that if the switch to JAVA was already done it should work relatively fast from there (not so depending of amount of rules - if there are not too much (10-100), clearly) Why it is so slow even relatively to SQL? Why it scales so much (linearly) even adding few new conditions? May be I should change some default parameter at the environment? (I use the simplest evaluate()...)

      In the manual, you are recommending to use getvarchar() and not anydata when passing the object to evaluate() clause, but I have found that in my case anydata works at least twice better then getvarchar(). How can it happen?

      Thanks you very much in advance,
        • 1. Re: Expression filters performance questions

          Expression Filter feature uses Java stored procedures to parse the conditional expressions stored in the table (CREATE INDEX implementation). It does not use Java for expression evaluation (EVALUATE operator). The runtime behavior is implemented in C and PL/SQL. So, java pool size and other parameters may not help you improve the performance.

          You said that the performance is relative to the number of expressions. Could you comment on the selectivity of the expressions ? That is, as you increase the number of expressions in the set, how is the result set size for the query with EVALUATE operator. Note that the query performance is expected to be relative to the result set size when an index is used (especially because there will be as many I/Os for fetching the corresponding rows from the table).

          You mentioned that the Expression Filter index is created for the column storing expressions. Could you please check the execution plan for the query with EVALUATE operator and confirm that the index is used ? If the index is being used for the query, could you please collect statistics on the predicate table (obtained from PREDICATE_TABLE column of the USER_EXPFIL_INDEXES view) and send them to me? Given your expression set, the EXF$SPARSEPRED column of the predicate table should have no values. If this not the case, then the index is not created using the expression statistics.

          The anydata vs getVarchar note was based on some in-house testing. For attribute sets with a few attributes, we are able to confirm this. But we realize that there are many other factors that impact the performance. For example, the getVarchar performance degrades as the number of attributes in the attribute set increases. We have removed the recommendation about getVarchar in the latest version of the doc.