Re: window function vs. PARTITION RANGE ITERATOR
Date: Mon, 8 Oct 2018 13:52:49 +0100
Message-ID: <CACj1VR6iu-eCqLn0Mydm6cyK4PyC7D05o9A9x8Ywviq3yFTjQQ_at_mail.gmail.com>
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-lReceived on Mon Oct 08 2018 - 14:52:49 CEST