jcon.no: Oracle blog

Recently I was facing an issue with an SQL coming from Apache Solr. The query was working on delta updates (since last check), and was fired from three different Solr instances keeping 3 cpus pretty busy . The query was one of the top SQLs regarding LIO, and was doing 9 FULL TABLE SCANs (where only two of the tables was relatively small with only 24 and 68 blocks). The query was written into the Solr configuration file, and looked something like this (when caught in v$sql) :

SELECT *
FROM a_big_view
WHERE TAB4_ROW_DELETED != :"SYS_B_01"
  AND ( :"SYS_B_02" = :"SYS_B_03"
      OR TAB1_UPDATED > to_date(:"SYS_B_04", :"SYS_B_05") 
      OR TAB2_UPDATED > to_date(:"SYS_B_06", :"SYS_B_07")  
      OR TAB3_UPDATED > to_date(:"SYS_B_08", :"SYS_B_09")
);

The “:SYS_B_02 = :SYS_B_03” filter really makes it impossible for the optimizer to use an appropriate index. As you might recognize the bind variables are set by sessions running with cursor_sharing=FORCE. The original query in the configuration file looked something like this:

SELECT *
FROM a_big_view
WHERE TAB4_ROW_DELETED != 1
  AND ( 1 = $search_date
      OR TAB1_UPDATED > to_date($a_datetime, 'DD.MM.YYYY HH24:MI:SS') 
      OR TAB2_UPDATED > to_date($a_datetime, 'DD.MM.YYYY HH24:MI:SS')  
      OR TAB3_UPDATED > to_date($a_datetime, 'DD.MM.YYYY HH24:MI:SS')
);

The trouble was that the Solr configuration needed one definition of a query, which would give totally different outcomes or number of rows. The solution we choose was a pipelined function, which would allow us to do something like this:

SELECT * FROM table(solr_util.getbigview($check_name,$search_date)

The pipelined function would look something like this:

CREATE OR REPLACE TYPE t_big_view_row AS OBJECT (
  col1 number, 
  col1 varchar2(100),
  ...
)
/

CREATE OR REPLACE TYPE t_big_vew_tab AS TABLE OF t_big_view_row
/

CREATE OR REPLACE PACKAGE solr_util AS 
  -------------------------------
  -- Functions and procedures
  -------------------------------
  FUNCTION getbigview(check_name_i IN number, 
                      gt_date_i IN date default sysdate-1) return t_big_vew_tab pipelined;
  
END solr_util;
/

CREATE OR REPLACE PACKAGE BODY solr_util AS
  FUNCTION getbigview(check_name_i IN number, 
                      gt_date_i IN date default sysdate-1) return t_big_vew_tab pipelined IS 
  BEGIN
    IF (check_name_i=1) THEN
      FOR rec in (
        SELECT t_big_view_row(
                col1, col2, ...
             ) row
        FROM (SELECT * 
              FROM a_big_view
              WHERE TAB1_UPDATED  > gt_date_i              
                AND TAB4_ROW_DELETED = 0
            UNION
            SELECT * 
              FROM a_big_view
              WHERE TAB2_UPDATED > gt_date_i
                AND TAB4_ROW_DELETED = 0
            UNION
            SELECT * 
              FROM a_big_view
              WHERE TAB3_UPDATED > gt_date_i
                AND TAB4_ROW_DELETED = 0)
        )
      LOOP
        PIPE ROW (rec.row);
      END LOOP;
    ELSE 
      FOR rec IN (
        SELECT t_big_view_row(
                col1, col2, ...
             ) row
        FROM a_big_view
        WHERE TAB4_ROW_DELETED = 0
        )
      LOOP
        PIPE ROW (rec.row);
      END LOOP;
    END IF;
    
  END getbigview;
END solr_util;
/