Friday, 8 July 2011

The performance enhancing drug called Table function

Finally a post on APEX :)

I have seen a number of situations in development of both Oracle APEX (Interactive reports) and in Oracle BI Publisher where the developer has to implement complex functionality but he only has the freedom to write SQL statements and not PL SQL logic. Due to this he ends up writing the complex logic using ‘and clause’ and ‘or clause’.

Let me explain it by an example
I found the below query in a company which had some serious performance issues
All bind variables below are page items of APEX.
p_view  and p_category  are select lists in APEX.
The report is expected to behave differently for different values of these variables

SELECT st.assignment_number, st.assignment_name
  FROM some_table st
 -- View check starts here
WHERE  (   (:p_view = 'My Assignments' AND st.requestor = 'Requestor Role')
        OR (:p_view = 'Global Read-Only')
        OR (:p_view = 'Admin' AND st.typee <> 'Confidential')
   --View check ends here
       -- Category check starts here
   AND (   (:p_category = 'Global Search')
        OR (    :p_category = 'Organization'
            AND (:p_bureau IS NULL OR :p_bureau = st.bureau_id)
            AND (:p_division IS NULL OR :p_division = st.division_id)
       )                                           -- Category check ends here

So basically we end up producing the dynamic report with a bad plan and hence performance problems. We can clearly see that our query will always have all the predicates even when they might not be needed.

Again in one of my projects we had a requirement of running the same report from both APEX and BI Publisher. Since the report is the same so it makes sense that the same piece of code generates both of these reports as this helps in maintenance and bug fixing

Okie .. now lets jump into the solution

1.      Create  a row type which matches with the structure of the returned result set. So in our case we create the following type
CREATE OR REPLACE type rprt_type AS OBJECT (assignment_number number, assignment_name VARCHAR2 (300))
Now create a table of this type
CREATE OR REPLACE TYPE rprt_tbl_type AS TABLE OF rprt_type;
2.      Write a function that returns the newly created table type i.e. rprt_tbl_type
   p_view       VARCHAR2,
   p_category   VARCHAR2,
   p_bureau     VARCHAR2,
   p_division   VARCHAR2
   RETURN rprt_tbl_type PIPELINED

   v_assignment_number   NUMBER;
   v_assignment_name     VARCHAR2 (200);
   l_rc                  rc;
   v_rprt_type           rprt_type        := NULL;
   v_query               VARCHAR2 (32767)
      := 'SELECT st.assignment_number, st.assignment_name
  FROM some_table st
 WHERE  ';
-- View check starts here
   IF p_view = 'My Assignments'
      v_query := v_query || ' st.requestor = ''Requestor Role''';
   ELSIF p_view = 'Global Role'
      v_query := v_query || ' 1 = 1 ';
   ELSIF p_view = 'Admin'
      v_query := v_query || ' st.typee <> ''Confidential''';
   END IF;

   --View check ends here
       -- Category check starts here
   IF p_category = 'Global Search'
   ELSIF p_category = 'Organization'
      IF p_bureau IS NULL
         v_query := v_query || ' and st.bureau_id = :p_bureau ';
      END IF;

      IF p_division IS NULL
         v_query := v_query || ' and st.division_id = :p_division ';
      END IF;
   END IF;

-- Category check ends here
   IF p_category = 'Organization' AND p_bureau IS NOT NULL
      OPEN l_rc FOR v_query USING p_bureau;
   END IF;

   IF p_category = 'Organization' AND p_division IS NOT NULL
      OPEN l_rc FOR v_query USING p_division;
   END IF;

      FETCH l_rc
       INTO v_assignment_number, v_assignment_name;

      v_rprt_type := rprt_type (v_assignment_number, v_assignment_name);
      PIPE ROW (v_rprt_type);

   CLOSE l_rc;

END interactive_rprt;

The above function dynamically creates a query which is lighter than the original query and stores it in a variable called v_query. The use of bind variables here allows us to pass values into the SQL engine (when the dynamic query is executed) and it also helps performance because only the soft parse of the query happens at run time if this query exists in the library cache

The use of pipelining the output again helps performance as the report does not wait for the entire output to be generated before displaying it

3.      Now call this function from the desired place (Like APEX or BI Publisher) using table function
SELECT assignment_number, assignment_name
  FROM TABLE (interactive_rprt (:p_view, :p_category, :p_bureau, :p_division));


Peter A. said...

Very good explanation!!

Vishal Pathak said...

Thanks Peter. Appreciation is always an encouragement :)

Lennel said...

Wow!!! - That basically sums up my findings after implementing this on an Apex Interactive Report that was extremely slow. Performance jumped up from 10+ min to load the IR to +-20sec!!!

The report was quite complex, with +-10 parameters that was used in the query. I wanted to use dynamic SQL to exclude all the parameters that were not used. Numerous post suggested that dynamic sql can't be used in an Apex's IR. Well, this workaround accomplished just that. Although the workaround is a long one, it is absolutely worth the effort.

Well done and thank you for this great post!

Vishal Pathak said...

;-) always a pleasure to help

ravi teja said...

hi vishal,
I have few question regarding Bi Publisher below are those.
1)I have data model with 10 datasets and when I run the publisher report the order of the 10 datasets is one after the other, but is there a way to execute all 10 at once .The main idea is to improve the performance
2)I have a evaluate function in my back end report and I need to pass the variables from BI publisher using logical sql to that report which will execute the package in the back end . Now the problem is we are not able to pass the variables through the select statement is there a way to pass the variable using select statement from BI publisher logical sql .
can you please provide any info on these ..


Vishal Pathak said...

Comment not related to the post

Anonymous said...

Hello, I build a table function like this one, with only dates as parameters,and put it's code in an APEX IR region source.
The report has 3 date ranges, wich are selected from datepickers. When I choose the dates I want and click search button, I always get an Invalid Character error... it's driving me crazy!
The query runs fine in SQL developer, can you help me on this one?


Vishal Pathak said...

Please engage consulting services for this

Jennifer N said...

Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check