I have a set of complex optimized selects that suffer from physical reads. Without them they would be even faster!
These physical reads occur due to the WITH clause, one physical_read_request per WITH sub-query. They seem totally unnecessary to me, I'd prefer Oracle keeping the sub-query results in memory instead of writing them down to disk and reading them again.
I'm looking for a way how to get rid of these phys reads.
A simple sample having the same problems is this:
alter session set STATISTICS_LEVEL=ALL;
create table T as
select level NUM from dual
connect by level <= 1000;
with /*a2*/ TT as (
select NUM from T
where NUM between 100 and 110
)
select * from TT
union all
select * from TT
;
SELECT * FROM TABLE(dbms_xplan.display_cursor(
(select sql_id from v$sql
where sql_fulltext like 'with /*a2*/ TT%'
and sql_fulltext not like '%v$sql%'
and sql_fulltext not like 'explain%'),
NULL, format=>'allstats last'));
and the corresponding execution plan is
SQL_ID bpqnhfdmxnqvp, child number 0
-------------------------------------
with /*a2*/ TT as ( select NUM from T where NUM between 100 and
110 ) select * from TT union all select * from TT
Plan hash value: 4255080040
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 22 |00:00:00.01 | 20 | 1 | 1 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 22 |00:00:00.01 | 20 | 1 | 1 | | | |
| 2 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 8 | 0 | 1 | 266K| 266K| 266K (0)|
|* 3 | TABLE ACCESS FULL | T | 1 | 11 | 11 |00:00:00.01 | 4 | 0 | 0 | | | |
| 4 | UNION-ALL | | 1 | | 22 |00:00:00.01 | 9 | 1 | 0 | | | |
| 5 | VIEW | | 1 | 11 | 11 |00:00:00.01 | 6 | 1 | 0 | | | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6646_63A776 | 1 | 11 | 11 |00:00:00.01 | 6 | 1 | 0 | | | |
| 7 | VIEW | | 1 | 11 | 11 |00:00:00.01 | 3 | 0 | 0 | | | |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6646_63A776 | 1 | 11 | 11 |00:00:00.01 | 3 | 0 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("NUM">=100 AND "NUM"<=110))
Note
-----
- dynamic sampling used for this statement (level=2)
See the (phys) Write upon each WITH view creation, and the (phys) Read upon the first view usage. I also tried the RESULT_CACHE hint (which wouldn't be reflected in this sample select, but was reflected in my original queries), but it doesn't remove the disk accesses either (which is understandable).
How can I get rid of the phys writes/reads?