Re: Seeking more information on 10g feature: Window Buffer

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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l