Seeking more information on 10g feature: Window Buffer
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 Feb 2007 08:39:16 -0600
In a recent SR with Oracle Support, I came face to face with another one of
those infamous 10g New Features, the Window Buffer. I could have sworn I saw
somebody discuss it breifly on this list, but I could not find the thread
(tried the freelist.org archives and google). I will try to squeeze as many
details in this email without blowing the max number of characters.
Essentially, however, there is some kind of interplay between the query
predicates and the Window Buffer - I am not sure which is causing which, but
I think the Window Buffer is causing the predicates to be processed
differently. Example to follow.
RBO
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID | SPRIDEN |
|* 4 | INDEX FULL SCAN | SPRIDEN_INDEX_ID |
|* 5 | TABLE ACCESS BY INDEX ROWID | SGBSTDN |
|* 6 | INDEX UNIQUE SCAN | PK_SGBSTDN |
| 7 | SORT AGGREGATE | |
|* 8 | TABLE ACCESS BY INDEX ROWID| SGBSTDN |
|* 9 | INDEX RANGE SCAN | PK_SGBSTDN |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<1001)
4 - filter("SPRIDEN_CHANGE_IND" IS NULL)
5 - filter("SGBSTDN_CAMP_CODE"='100')
6 - access("SGBSTDN_PIDM"="SPRIDEN_PIDM" AND
"SGBSTDN_TERM_CODE_EFF"= (SELECT
MAX("A"."SGBSTDN_TERM_CODE_EFF") FROM
"SATURN"."SGBSTDN" "A" WHERE "A"."SGBSTDN_PIDM"=:B1 AND
"A"."SGBSTDN_CAMP_CODE"='100'))
8 - filter("A"."SGBSTDN_CAMP_CODE"='100')
9 - access("A"."SGBSTDN_PIDM"=:B1)
CBO
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 968
| 27 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 11 | 968
| 27 (4)| 00:00:01 |
|* 2 | COUNT STOPKEY | | |
| | |
|* 3 | VIEW | VW_WIF_1 | 11 | 968
| 26 (0)| 00:00:01 |
| 4 | WINDOW BUFFER | | 11 | 1166
| 26 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 11 | 1166
| 26 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| SGBSTDN | 662K|
46M| 16 (0)| 00:00:01 |
| 7 | INDEX FULL SCAN | PK_SGBSTDN | 28 |
| 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | SPRIDEN_KEY_INDEX | 1 | 33
| 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<1001)
3 - filter("VW_COL_11" IS NOT NULL)
6 - filter("SGBSTDN_CAMP_CODE"='100')
8 - access("SGBSTDN_PIDM"="SPRIDEN_PIDM" AND "SPRIDEN_CHANGE_IND" IS
NULL)
filter("SPRIDEN_CHANGE_IND" IS NULL)
The Support Analyst found that modifying the following parameters rearranges
the predicates enough to give us a plan that runs faster:
- alter session set "_remove_aggr_subquery"=FALSE
- alter session set optimizer_index_cost_adj=5
- alter session set optimizer_index_caching=90
Where can I find more information about "Window Buffer", and how can I learn
about the ramifications of the Window Buffer with predicates? Yes, I have
been reading Jonathan Lewis's tome ("CBO Fundamentals"); he mentions
_remove_aggr_subquery breifly on page 237, and talks a bit about predicates
elsewhere, which I am still trying to understand. I have asked the Support
guy, but he is basically saying "Sorry, it is not well documented."
--
Charles Schultz
Other related posts:
