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
CREATE OR REPLACE FUNCTION interactive_rprt (
   p_view       VARCHAR2,
   p_category   VARCHAR2,
   p_bureau     VARCHAR2,
   p_division   VARCHAR2
)
   RETURN rprt_tbl_type PIPELINED
IS
   TYPE rc IS REF CURSOR;

   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  ';
BEGIN
-- View check starts here
   IF p_view = 'My Assignments'
   THEN
      v_query := v_query || ' st.requestor = ''Requestor Role''';
   ELSIF p_view = 'Global Role'
   THEN
      v_query := v_query || ' 1 = 1 ';
   ELSIF p_view = 'Admin'
   THEN
      v_query := v_query || ' st.typee <> ''Confidential''';
   END IF;

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

      IF p_division IS NULL
      THEN
         NULL;
      ELSE
         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
   THEN
      OPEN l_rc FOR v_query USING p_bureau;
   END IF;

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

   LOOP
      FETCH l_rc
       INTO v_assignment_number, v_assignment_name;

      v_rprt_type := rprt_type (v_assignment_number, v_assignment_name);
      EXIT WHEN l_rc%NOTFOUND;
      PIPE ROW (v_rprt_type);
   END LOOP;

   CLOSE l_rc;

   RETURN; 
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));

8 comments:

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

Unknown 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 ..

Thanks

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?

Tkx!!!

Vishal Pathak said...

Please engage consulting services for this