Re: window function vs. PARTITION RANGE ITERATOR

You can only push the filter if it is used in the partition by clause. Since your partition by group uses trunc(snap_time), you could also filter on that but this doesn't help much as your table partitioning is done against snap_time.

But, it seems to me like it should be easy enough to just partition by trunc(snap_time) using virtual columns:

CREATE TABLE "H3G_SYSSTAT_METRICS"
   ("STAT_NAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"SNAP_TIME" DATE NOT NULL ENABLE,
"DATABASE_ID" NUMBER NOT NULL ENABLE,
"INSTANCE_NAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"SERVER" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"STARTUP_TIME" DATE NOT NULL ENABLE,
"VALUE" NUMBER NOT NULL ENABLE
,trunc_snap_time date as (trunc(snap_time)) -- virtual column

   )
  TABLESPACE "USERS"
  PARTITION BY LIST ("STAT_NAME")
  SUBPARTITION BY RANGE (trunc_snap_time) -- altered column  (PARTITION "P2" VALUES ('CPU used by this session')   TABLESPACE "USERS"
 COMPRESS BASIC
 ( SUBPARTITION "P2_000000" VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   TABLESPACE "USERS"
 COMPRESS BASIC ,
  SUBPARTITION "P2_201600" VALUES LESS THAN (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   TABLESPACE "USERS"
))
/

with raw_data as (select /*+ NO_INDEX( h ) */ h.*, h.snap_time snap_time2

   from H3G_SYSSTAT_METRICS h
   where 1=1
-- and snap_time >= sysdate - 3
), better as (
select stat_name, instance_name, database_id, server, trunc_SNAP_TIME trunc_snap, snap_time, value, rank() -- altered trunc_snap

    OVER (PARTITION BY STAT_NAME, instance_name,database_id, server, trunc_SNAP_TIME ORDER BY SNAP_TIME desc) as rr -- altered partition by from raw_data)
select
-- BX012
*
from better
where rr=1
  and stat_name='physical read bytes'
  and snap_time >= sysdate - 3
  and trunc_snap >= trunc(sysdate) - 3 -- extra filter ;

Plan hash value: 1756919075

| Id  | Operation                   | Name                | Rows  | Bytes |
Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |   159
|     3  (34)| 00:00:01 |       |       |
|*  1 |  VIEW                       |                     |     1 |   159
|     3  (34)| 00:00:01 |       |       |
|*  2 |   WINDOW SORT PUSHED RANK   |                     |     1 |   146
|     3  (34)| 00:00:01 |       |       |
|   3 |    PARTITION LIST EMPTY     |                     |     1 |   146
|     2   (0)| 00:00:01 |INVALID|INVALID|
|   4 |     PARTITION RANGE ITERATOR|                     |     1 |   146
|     2   (0)| 00:00:01 |   KEY |     2 |
|*  5 |      TABLE ACCESS FULL      | H3G_SYSSTAT_METRICS |     1 |   146
|     2   (0)| 00:00:01 |INVALID|INVALID|
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   1 - filter("RR"=1 AND "SNAP_TIME">=SYSDATE_at_!-3)    2 - filter(RANK() OVER ( PARTITION BY
"H"."STAT_NAME","H"."INSTANCE_NAME","H"."DATABASE_ID","H"."SERVER"
              ,"H"."TRUNC_SNAP_TIME" ORDER BY INTERNAL_FUNCTION("SNAP_TIME") DESC )<=1)

   5 - filter("H"."STAT_NAME"='physical read bytes' AND
"H"."TRUNC_SNAP_TIME">=TRUNC(SYSDATE_at_!)-3)

Note

• dynamic statistics used: dynamic sampling (level=2)

Hope this helps

Andy

On Mon, 8 Oct 2018 at 13:31, l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> wrote:

> Hi Martin,
>
> it seems that the snap_time condition can not be pushed down into the
> better view.
> Probably the window function is preventing that. You might want to
> experiment based on that. (
> https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:3469884600671
> ).
> It should be possible to push a condition based on column included in the
> partition clause of wndow function.
> You use trunc(SNAP_TIME) there.
> Try using trunc(SNAP_TIME) everywhere or just snap_time plain.
>
> Regards
>
> Lothar
>
> ----Ursprüngliche Nachricht----
> Von : martin.a.berger_at_gmail.com
> Datum : 08/10/2018 - 13:58 (GMT)
> An : oracle-l_at_freelists.org
> Betreff : window function vs. PARTITION RANGE ITERATOR
>
> Dear list,
>
> I have a DB (12.1.0.2 - Jan 2018 BP - 2 node RAC)
> with a table "SYSSTAT_METRICS" which holds regular snapshots of sysstat
> metrics of many DBs.
> the table has
> columns STAT_NAME, SNAP_TIME, DATABASE_ID, INSTANCE_NAME, SERVER, STARTUP_TIME, VALUE.
> The table is partitioned by LIST ("STAT_NAME") and subpartitioned  BY
> RANGE ("SNAP_TIME")
> The SNAP_TIME subpartitions are monthly partitions. (details at the end of
> this email)
>
> I want to have a View query which gives daily last snap_time & value for
> every stat, db, instance, server. In this view I'd like to efficient filter
> by STAT_NAME (most only 1 statistic of interest) and SNAP_TIME (e.g. last
> month or last 3 month - or December-2017).
>
> I have a select like this:
> with raw_data as (select /*+ NO_INDEX( h ) */ h.*, h.snap_time snap_time2
>    from sysstat_metrics h
>    where 1=1
> *     and snap_time >= sysdate - 3*
> ), better as (
> select stat_name, instance_name, database_id, server,  trunc(snap_time)
> trunc_snap, snap_time, value, rank()
>     OVER (PARTITION BY STAT_NAME, instance_name,database_id, server,
> trunc(SNAP_TIME) ORDER BY SNAP_TIME desc) as rr
> from raw_data)
> select
> -- BX011
> *
> from better
> where rr=1
>   and stat_name='physical read bytes'
> --  and snap_time >= sysdate - 3
> ;
>
> The Plan shows
> Plan hash value: 1030407344
>
>
> ---------------------------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation                   | Name            | Starts | E-Rows |
> A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
>
> ---------------------------------------------------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT            |                 |      1 |        |
>  2868 |00:00:00.21 |    1921 |     11 |       |       |          |
> |*  1 |  VIEW                       |                 |      1 |      3 |
>  2868 |00:00:00.21 |    1921 |     11 |       |       |          |
> |*  2 |   WINDOW SORT PUSHED RANK   |                 |      1 |      3 |
>  2868 |00:00:00.21 |    1921 |     11 |  6715K|  1041K| 5968K (0)|
> |   3 |    PARTITION LIST SINGLE    |                 |      1 |      3 |
> 51612 |00:00:00.04 |    1921 |     11 |       |       |          |
> |   4 |     *PARTITION RANGE ITERATOR*|                 |      1 |      3
> |  51612 |00:00:00.04 |    1921 |     11 |       |       |          |
> |*  5 |      TABLE ACCESS FULL      | SYSSTAT_METRICS |      4 |      3 |
> 51612 |00:00:00.03 |    1921 |     11 |       |       |          |
>
> ---------------------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    1 - filter("RR"=1)
>    2 - filter(RANK() OVER ( PARTITION BY
> "H"."STAT_NAME","H"."INSTANCE_NAME","H"."DATABASE_ID","H"."SERVER",TRUNC(INTERNAL_FUNCTION("H"."
>               SNAP_TIME")) ORDER BY INTERNAL_FUNCTION("SNAP_TIME") DESC
> )<=1)
>    *5 - filter("SNAP_TIME">=SYSDATE_at_!-3)*
>
> and it's nice as there is a PARTITION RANGE ITERATOR (so not all range
> partitions are used at all) and the Filter is quite early.
>
> But if I change the same filter down to the "outer" subquery:
>
> with raw_data as (select /*+ NO_INDEX( h ) */ h.*, h.snap_time snap_time2
>    from sysstat_metrics h
>    where 1=1
> *--*     and snap_time >= sysdate - 3
> ), better as (
> select stat_name, instance_name, database_id, server,  trunc(snap_time)
> trunc_snap, snap_time, value, rank()
>     OVER (PARTITION BY STAT_NAME, instance_name,database_id, server,
> trunc(SNAP_TIME) ORDER BY SNAP_TIME desc) as rr
> from raw_data)
> select
> -- BX012
> *
> from better
> where rr=1
>   and stat_name='physical read bytes'
> *  and snap_time >= sysdate - 3*
> ;
>
> The plan changes to ugly
> Plan hash value: 2132163184
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation                | Name            | Starts | E-Rows |
> A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem
> | Used-Tmp|
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT         |                 |      1 |        |
>  2868 |00:00:34.24 |     100K|  38026 |  32252 |       |       |
> |         |
> |*  *1* |  VIEW                    |                 |      1 |   9387K|
>  2868 |00:00:34.24 |     100K|  38026 |  32252 |       |       |
> |         |
> |*  2 |   WINDOW SORT PUSHED RANK|                 |      1 |   9387K|
> 388K|00:00:34.06 |     100K|  38026 |  32252 |   204M|  4789K| 8362K (3)|
>    192K|
> |   3 |    PARTITION LIST SINGLE |                 |      1 |   9387K|
>  9503K|00:00:03.45 |     100K|   5774 |      0 |       |       |
> |         |
> |   4 |     *PARTITION RANGE ALL*  |                 |      1 |   9387K|
>  9503K|00:00:02.51 |     100K|   5774 |      0 |       |       |
> |         |
> |   5 |      TABLE ACCESS FULL   | SYSSTAT_METRICS |     27 |   9387K|
>  9503K|00:00:01.49 |     100K|   5774 |      0 |       |       |
> |         |
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    *1* - filter(("RR"=1 AND *"SNAP_TIME">=SYSDATE_at_!-3*))
>    2 - filter(RANK() OVER ( PARTITION BY
> "H"."STAT_NAME","H"."INSTANCE_NAME","H"."DATABASE_ID","H"."SERVER",TRUNC(INTERNAL_FUNCTION("H"."SNAP_TIME"))
>               ORDER BY INTERNAL_FUNCTION("H"."SNAP_TIME") DESC )<=1)
>
> Here the statement uses much more partitions
> and the filter is much later.
> I am aware there is a slightly difference if I filter *snap_time >=
> sysdate - 3*  in early with clause or after the PARTITON BY window
> function - but such oddities would be acceptable for this kind of data ;-).
>
> Does anyone have an idea how I can to PARTITION RANGE ITERATOR with a
> filter on the snap_time, which is the order/rank in PARTITION BY?
>
> (before anyone asks, the NO_INDEX is to avoid this "good" plan which
> doesn't change anything, but could be confusing)
> |   4 |     PARTITION RANGE ITERATOR                  |
>  |      1 |      3 |  51612 |00:00:00.05 |   20177 |       |       |
>   |
> |   5 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SYSSTAT_METRICS
>  |      4 |      3 |  51612 |00:00:00.04 |   20177 |       |       |
>   |
> |*  6 |       INDEX RANGE SCAN                        | I_SYSSTAT_METRICS
> |      1 |      3 |  51612 |00:00:00.01 |     174 |       |       |
>   |
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    1 - filter("RR"=1)
>    2 - filter(RANK() OVER ( PARTITION BY
> "H"."STAT_NAME","H"."INSTANCE_NAME","H"."DATABASE_ID","H"."SERVER",TRUNC(INTERNAL_FUNCTION("H"."SNAP_TIME"))
>                ORDER BY INTERNAL_FUNCTION("SNAP_TIME") DESC )<=1)
>    6 - access("SNAP_TIME">=SYSDATE_at_!-3)
>
>
> thank you for any advise,
>  Martin
>
>
>
>
> Table definition:
> CREATE TABLE "H3G_SYSSTAT_METRICS"
>    ( "STAT_NAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
> "SNAP_TIME" DATE NOT NULL ENABLE,
> "DATABASE_ID" NUMBER NOT NULL ENABLE,
> "INSTANCE_NAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
> "SERVER" VARCHAR2(64 BYTE) NOT NULL ENABLE,
> "STARTUP_TIME" DATE NOT NULL ENABLE,
> "VALUE" NUMBER NOT NULL ENABLE
>    )
>   PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
>  COMPRESS BASIC
>   STORAGE(
>   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
>   TABLESPACE "USERS"
>   PARTITION BY LIST ("STAT_NAME")
>   SUBPARTITION BY RANGE ("SNAP_TIME")
>  (PARTITION "P2"  VALUES ('CPU used by this session')
> PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
>   STORAGE(
>   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
>   TABLESPACE "USERS"
>  COMPRESS BASIC
>  ( SUBPARTITION "P2_000000"  VALUES LESS THAN (TO_DATE(' 2016-01-01
> 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
>   TABLESPACE "USERS"
>  COMPRESS BASIC ,
>   SUBPARTITION "P2_201600"  VALUES LESS THAN (TO_DATE(' 2017-01-01
> 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
>   TABLESPACE "USERS"
>   ...
>
>
>

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