Re: Group by wrong results?

Glad to see confirmation and not that it was a problem with this carbon-based peripheral.

I do have a Oracle support account and will open an SR...I wonder if they will ask me to upload oswatcher files...

On Tue, Aug 7, 2018 at 10:54 AM <rogel_at_web.de> wrote:

> Daniel,
>
> obviously a bug, have you account to MOS to file a bug ?
> If not, let me know, I will do so.
>
> Query 1 gives for me
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *sys_at_12.1 > set autotr traceonly arrays 5000 lines 300 pages 5000 feedb on
> sys_at_12.1 > WITH session_count AS 2 ( SELECT sample_time, count(1)
> sess_count 3 FROM dba_hist_active_sess_history 4 WHERE
> sample_time >= TRUNC(sysdate - (1/24)) 5 GROUP BY sample_time 6 )
> 7 SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,
> 8 MAX(sess_count) max_sessions, 9 count(*) count 10
> FROM session_count 11 GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi')
> 12 ORDER BY sample_minute 13 /*
> *4812 rows selected.*
>
>
>
> *Execution Plan ----------------------------------------------------------
> Plan hash value: 2604173274*
>
>
>
>
>
>
>
>
>
>
>
>
>
> *------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name |
> Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
> ------------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> 1 | 64 | 3666 (1)| 00:00:01 | | | | 1 | SORT ORDER
> BY | | 1 | 64 | 3666
> (1)| 00:00:01 | | | | 2 | HASH GROUP BY
> | | 1 | 64 | 3666 (1)| 00:00:01
> | | | | 3 | NESTED LOOPS OUTER
> | | 1 | 64 | 3664 (1)| 00:00:01
> | | | | 4 | NESTED LOOPS OUTER
> | | 1 | 47 | 3663 (1)| 00:00:01
> | | | | 5 | PARTITION RANGE ALL
> | | 1 | 33 | 3662 (1)| 00:00:01 |
> 1 | 6 | |* 6 | TABLE ACCESS FULL |
> WRH$_ACTIVE_SESSION_HISTORY | 1 | 33 | 3662 (1)| 00:00:01 | 1
> | 6 | |* 7 | INDEX RANGE SCAN |
> WRH$_EVENT_NAME_PK | 1 | 14 | 1 (0)| 00:00:01 |
> | | |* 8 | TABLE ACCESS BY INDEX ROWID|
> WRM$_SNAPSHOT | 1 | 17 | 1 (0)| 00:00:01 |
> | | |* 9 | INDEX UNIQUE SCAN |
> WRM$_SNAPSHOT_PK | 1 | | 0 (0)| 00:00:01 |
> | |
> ------------------------------------------------------------------------------------------------------------------------------*
>
> *Predicate Information (identified by operation id):
> ---------------------------------------------------*
>
>
>
>
> * 6 -
> filter("ASH"."SAMPLE_TIME">=TRUNC(SYSDATE_at_!-.0416666666666666666666666666666666666667))
> 7 - access("ASH"."DBID"="EVT"."DBID"(+) AND
> "ASH"."EVENT_ID"="EVT"."EVENT_ID"(+)) 8 - filter("STATUS"(+)=0) 9 -
> access("ASH"."DBID"="DBID"(+) AND "ASH"."SNAP_ID"="SNAP_ID"(+) AND
> "ASH"."INSTANCE_NUMBER"="INSTANCE_NUMBER"(+))*
>
>
>
>
> *Note ----- - dynamic statistics used: dynamic sampling (level=2) -
> this is an adaptive plan - 1 Sql Plan Directive used for this statement*
>
>
>
>
>
>
>
>
>
>
>
>
>
> *Statistics ----------------------------------------------------------
> 0 recursive calls 0 db block gets 11614
> consistent gets 0 physical reads 9440 redo size
> 44387 bytes sent via SQL*Net to client 500 bytes received via
> SQL*Net from client 2 SQL*Net roundtrips to/from client
> 1 sorts (memory) 0 sorts (disk) 4812 rows
> processed*
>
> Have a look at the execution plan, only one GROUP BY, definitively one too
> little.
>
> Matthias
>
> *Gesendet:* Dienstag, 07. August 2018 um 18:26 Uhr
> *Von:* "Daniel Fink" <dmarc-noreply_at_freelists.org>
> *An:* oracle-l_at_freelists.org
> *Betreff:* Group by wrong results?
> A script to extract session count from ash data is not returning the right
> results using a TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') group by unless I
> use a factored subquery.
>
> Query 1
>
> /
> Wrong Results example
>
> SAMPLE_MINUTE SESS_COUNT
>
> -------------- ----------
>
> 20180727 09:09 5
>
> 20180727 09:09 1
>
> 20180727 09:09 1
>
> 20180727 09:09 5
>
> 20180727 09:09 2
>
> 20180727 09:09 4
>
> 20180727 09:10 3
>
> 20180727 09:10 5
>
> 20180727 09:10 1
>
> 20180727 09:10 4
>
> 20180727 09:10 7
>
> 20180727 09:10 9
> If I add another factored subquery, I get the right aggregation
>
> WITH session_count AS
>
> ( SELECT sample_time, count(1) sess_count
>
> FROM dba_hist_active_sess_history
>
> WHERE sample_time >= sysdate - (1/24)
>
> GROUP BY sample_time
>
> ),
>
> session_minutes
>
> AS
>
> ( SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,
>
> sess_count
>
> FROM session_count
>
> )
>
> SELECT sample_minute,
>
> MAX(sess_count)
>
> FROM session_minutes
>
> GROUP BY sample_minute
>
> ORDER BY sample_minute
> Correct results
>
> SAMPLE_MINUTE MAX(SESS_COUNT)
>
> -------------- ---------------
>
> 20180727 09:01 22
>
> 20180727 09:02 22
>
> 20180727 09:03 15
>
> 20180727 09:04 10
>
> 20180727 09:05 11
>
> 20180727 09:06 10
>
> 20180727 09:07 20
>
> 20180727 09:08 20
>
> 20180727 09:09 5
>
> 20180727 09:10 9
>
> 20180727 09:11 12
>
> 20180727 09:12 12
>
> 20180727 09:13 9
>
> 20180727 09:14 6
>
> 20180727 09:15 3
> Why is the first query (which looks correct to me) not properly
> aggregating?
> -- http://www.freelists.org/webpage/oracle-l

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