Seeking more information on 10g feature: Window Buffer

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Tue, 6 Feb 2007 08:39:16 -0600
Message-ID: <7b8774110702060639y73c91a2dwc5bdfc4aadcb0d70@mail.gmail.com>

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

Received on Tue Feb 06 2007 - 08:39:16 CST