Re: Seeking more information on 10g feature: Window Buffer
- From: "Ghassan Salem" <salem.ghassan@xxxxxxxxx>
- To: sacrophyte@xxxxxxxxx
- Date: Tue, 6 Feb 2007 16:02:28 +0100
Charles,
having your query would have helped a bit, no?
Next window buffer is seen when the query does some 'analytical'
calculation, but does not need to sort the data, just 'buffers' it,
otherwise, you would have seen 'window sort'. I don't think it has much to
do with predicates and filters.
rgds
On 2/6/07, Charles Schultz <sacrophyte@xxxxxxxxx> wrote:
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:
