with clause performance issue in oracle

Here’s my experience with a performance problem in the Oracle database. After several months of work on a product and after successful tests, it was time to carry out the integration tests at the customer. Now the customer has noticed that the GUI (JAVA) is quite slow in some situations.

What happened then?! Everything works for us perfect. What is special about the customer? Now I was assigned to analyze the problem.

The first thing I did was to look in our application log and to compare it with the logs in our UAT environment. Surprisingly, the suspicious SQL was easy to find. It was a simple SQL. It should return at most dozens of records and is finished in our UAT environmentin in one second. But by the customer takes more than a minute. The Statement should be called several times, each time it takes more than a minute. This of course makes the GUI very slow.

The SQL ist:

WITH CFG_TBL_AS AS
   (SELECT 
     OWNER, TABLE_NAME, COLUMN_NAME, FUNCTION_NAME, TABLE_NAME_NEW
      FROM TMP_$_ANONY$CONFIG$
     WHERE TABLE_NAME_NEW IS NOT NULL)
  SELECT A.TABLE_NAME, A.COLUMN_NAME, B.TABLE_NAME_NEW, B.FUNCTION_NAME
    FROM ALL_TAB_COLS A
    LEFT JOIN CFG_TBL_AS B
      ON A.OWNER = B.OWNER
     AND A.TABLE_NAME = B.TABLE_NAME
     AND A.COLUMN_NAME = B.COLUMN_NAME
   WHERE (A.OWNER, A.TABLE_NAME) IN
         (SELECT OWNER, TABLE_NAME FROM CFG_TBL_AS)
   ORDER BY A.TABLE_NAME, A.COLUMN_ID

looks easy. I get the Execute Plan from the Customer. I will only show the important here. (which is actually the problem)

It looks like oracle is trying to materialize a subquery. (A powrful feature of With Clause is materializing subqueries) In our environment the execute plan looks like this

Then I tried the SQL with the materialize hint and as expected I get the same execute paln and it takes also more than one minutes.

WITH CFG_TBL_AS AS
   (SELECT --+materialize
     OWNER, TABLE_NAME, COLUMN_NAME, FUNCTION_NAME, TABLE_NAME_NEW
      FROM TMP_$_ANONY$CONFIG$
     WHERE TABLE_NAME_NEW IS NOT NULL)
  SELECT A.TABLE_NAME, A.COLUMN_NAME, B.TABLE_NAME_NEW, B.FUNCTION_NAME
    FROM ALL_TAB_COLS A
    LEFT JOIN CFG_TBL_AS B
      ON A.OWNER = B.OWNER
     AND A.TABLE_NAME = B.TABLE_NAME
     AND A.COLUMN_NAME = B.COLUMN_NAME
   WHERE (A.OWNER, A.TABLE_NAME) IN
         (SELECT OWNER, TABLE_NAME FROM CFG_TBL_AS)
   ORDER BY A.TABLE_NAME, A.COLUMN_ID

Why is it even trying to materialize a few records (which fit into a single block)? And why should something take forever? But the important question for me was why does the statement behave as if the hint is inline?

This is because of the hidden system parameter: _with_subquery. It turns out that this is set to inline on our UAT environment!

ALTER SYSTEM SET "_with_subquery" = INLINE;

Someone forgot to remove this parameter, so we could not spot this issue before delivery. After adding the hint everythings work fine.

For more information about „With Clause“ I found this very interessant blog: http://dbaora.com/with-clause-and-hints-materialize-and-inline/