Re: window function vs. PARTITION RANGE ITERATOR

Thank you Andy & Lothar,

I have to see if I can change the table to this slightly modified subpartition method.
Should not be too difficult, but there are always some dependencies around.

as always, very helpful answers ;-)
 thnx
  Martin

Am Mo., 8. Okt. 2018 um 14:52 Uhr schrieb Andy Sayer <andysayer_at_gmail.com>:

> 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