Aw: Group by wrong results?

From: <rogel_at_web.de>
Date: Tue, 7 Aug 2018 18:53:53 +0200
Message-ID: <trinity-3b2d1275-49f8-4cef-bf0e-9ec28e75bed0-1533660833767_at_3c-app-webde-bs38>

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 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

( 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 Received on Tue Aug 07 2018 - 18:53:53 CEST

This message: [ Message body ]
Next message: Daniel Fink: "Re: Group by wrong results?"
Previous message: Daniel Fink: "Group by wrong results?"
In reply to: Daniel Fink: "Group by wrong results?"
Next in thread: Daniel Fink: "Re: Group by wrong results?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message