window function vs. PARTITION RANGE ITERATOR

Discussion:

Martin Berger

2018-10-08 11:58:21 UTC

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@!-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@!-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@!-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"
...

l***@bluewin.ch

2018-10-08 12:31:03 UTC

Permalink

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 : ***@gmail.com
Datum : 08/10/2018 - 13:58 (GMT)
An : oracle-***@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@!-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@!-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@!-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"
...

Andy Sayer

2018-10-08 12:52:49 UTC

Permalink

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@!-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@!)-3)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Hope this helps

Andy

Post by l***@bluewin.ch
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----
Datum : 08/10/2018 - 13:58 (GMT)
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).
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 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
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)
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.
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 | | |
| |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
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 | | |
|
--------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
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)
thank you for any advise,
Martin
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"
...

Martin Berger

2018-10-08 13:43:20 UTC

Permalink

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

Post by Andy Sayer
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
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|
-------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
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
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Hope this helps
Andy

Post by l***@bluewin.ch
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----
Datum : 08/10/2018 - 13:58 (GMT)
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).
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 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
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)
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.
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 | | |
| |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
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 | | |
|
--------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
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)
thank you for any advise,
Martin
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"
...