Given: A table with 6 million records. We need to collect some statistics for the quarter. 650 thousand lines fall into the quarter.
The data filtering parameters come into the procedure. Some of them are transmitted as an "array".

In the products and categories fields, for example, there are only 30 unique values (in reality 27 and 22).

There is a data type(collection):

create or replace type strings is table of varchar2(256);


  Filter1 IN VARCHAR2, 
  Filter2 IN VARCHAR2, 
  Filter3 IN strings,
  Filter4 IN strings,
  ) IS
  b1 strings;
  b2 strings;
  bindNum number := 0;
    tSQL := 'SELECT count(*) FROM MyTable t WHERE to_char(t.dateInsert, 'Q.YYYY') = to_char(sysdate, 'Q.YYYY') ';
    IF (Filter3 IS NOT NULL) THEN
      tSQl := tSQL || ' AND nvl(products, 'notProduct') IN (SELECT column_value FROM TABLE(:b1)) ';
      b1 := Filter3;
      bindNum := bindNum + 1;
    END IF;

    IF (Filter4 IS NOT NULL) THEN
      tSQl := tSQL || ' AND nvl(categories, 'notCateg') IN (SELECT column_value FROM TABLE(:b2)) ';
      b2 := Filter4;
      bindNum := bindNum + 1;
    END IF;
    IF (bindNum = 1) then
      OPEN res FOR tSQL USING b1;
    elsif (bindNum = 2) then
      OPEN res FOR tSQL USING b1, b2;
      OPEN res FOR tSQL;
    END IF;

If you perform the procedure without data, i.e. with a filter for the current quarter, the request is executed in 2-3 seconds.

Let's add filter3 to the parameters with the number of elements in the collection 28 (out of 30 unique across the entire table) - the query works out in 2-3 seconds

Let's remove filter3 and add filter4 to the parameters with the number of elements in the collection of 28 (out of 30 unique throughout the table) - the query works out in 2-3 seconds

Let's execute the request with the passed filter3 and filter4 with filling of 28 elements in each collection and the request works out in 3-5 minutes!

OK, maybe something with the data and statistics on the table, we write a query manually and change the selection from the collection to an enumeration of incoming values into `IN

SELECT count(*) 
FROM MyTable
WHERE to_char(dateInsert, 'Q.YYYY') = to_char(sysdate, 'Q.YYYY')
AND nvl(products, 'notProduct') IN ('prod1', 'prod2', ...) 
AND nvl(categories, 'notCateg') IN ('categ1', 'categ2', ...)

The request is processed in 2-3 seconds...

How does this happen?

Why is the selection by date and by one of the filters by collection performed quickly, and by two collections performed orders of magnitude longer? And why doesn't this happen if you replace SELECT COLUMN_VALUE from table(collection) on an explicit enumeration of values?

Oracle OEM monitoring shows high CPU consumption.

I understand with my head that the problem lies somewhere on the surface and is clearly related to the work of collections inside queries, but I can't understand... It seems that with two collections, either some kind of data lock occurs for the object, or it somehow begins to re-read the values from the collection many times for each row...



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.