APEX

Hello all,

we are using on our project 11g with APEX 4.1.1. One of the new features of 11g is the Result Cache. With this feature the performance of the application is much better. In certain cases we need this feature in in-line views in a select statements. In this cases the performance was not as expected. After a while of digging into the explain plans I noticed that there is a difference between the explain plan running from APEX and one from SQL Developer. To verify this theory I created a short test case.

-----

-- Create Table
CREATE TABLE t (
id NUMBER
);

-- Insert Data
INSERT INTO t SELECT level FROM dual CONNECT BY level <= 1000000;

COMMIT;

--Run the following SQL from SQL Developer or SQL Plus
EXPLAIN PLAN FOR SELECT /*+ RESULT_CACHE(name=TEST1) */ * FROM (SELECT /*+ RESULT_CACHE(name=TEST2) */ SUM(id) FROM t);

SELECT * FROM table(dbms_xplan.display);

-----

In the resulting explain plan you can see the usage of the result cache:

1 - column-count=1; dependencies=(T); attributes=(single-row); name="TEST1"
3 - column-count=1; dependencies=(T); attributes=(single-row); name="TEST2"

Run the same SQL from above in an APEX SQL Workspace and you will notice the difference in the explain plan:

1 - column-count=1; dependencies=(T); attributes=(single-row); name="TEST1";

If the SQL is encapsulated in a procedure or PLSQL package you will see the same behavior. For me it looks like that the APEX environment only allows the usage of the result cache in the top query and not in an in-line view.

Why does it not work as expected and documented?

Best regards

Detlef