Group by wrong results?

Discussion:

"Daniel Fink" (Redacted sender "daniel.fink" for DMARC)

2018-08-07 16:26:28 UTC

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
WITH session_count AS
( SELECT sample_time, count(1) sess_count
FROM dba_hist_active_sess_history
WHERE sample_time >= TRUNC(sysdate - (1/24))
GROUP BY sample_time
)
SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,
MAX(sess_count) max_sessions
FROM session_count
GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi')
ORDER BY sample_minute
/
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?

r***@web.de

2018-08-07 16:53:53 UTC

Permalink

<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div> <div>Daniel,</div> <div> </div> <div>obviously a bug, have you account to MOS to file a bug ?</div> <div>If not, let me know, I will do so.</div> <div> </div> <div>Query 1 gives for me</div> <div> </div> <div> <div>***@12.1 > set autotr traceonly arrays 5000 lines 300 pages 5000 feedb on 
***@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  /</div>

<div>4812 rows selected.</div>

<div> 
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2604173274</div>

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

<div>Predicate Information (identified by operation id): 
---------------------------------------------------</div> <div>   6 - filter("ASH"."SAMPLE_TIME">=TRUNC(SYSDATE@!-.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"(+))</div>

<div>Note 
----- 
   - dynamic statistics used: dynamic sampling (level=2) 
   - this is an adaptive plan 
   - 1 Sql Plan Directive used for this statement</div>

<div> 
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</div> <div> </div> <div>Have a look at the execution plan, only one GROUP BY, definitively one too little.</div> <div> </div> <div>Matthias</div> </div> <div>  <div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"> <div style="margin:0 0 10px 0;">Gesendet: Dienstag, 07. August 2018 um 18:26 Uhr Von: "Daniel Fink" <dmarc-***@freelists.org> An: oracle-***@freelists.org Betreff: Group by wrong results?</div> <div name="quoted-content"> <div>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. <div> </div> <div>Query 1</div> <div> <div> </div> <div>/</div> </div> <div>Wrong Results example</div> <div> 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 </div> <div>If I add another factored subquery, I get the right aggregation</div> <div> 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
</div>

<div>Correct results</div>

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

<div>Why is the first query (which looks correct to me) not properly aggregating?</div>
</div>
</div>
</div>
</div>
</div></div></body></html>
--
http://www.freelists.org/webpage/oracle-l

"Daniel Fink" (Redacted sender "daniel.fink" for DMARC)

2018-08-07 16:58:04 UTC

Permalink

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

Post by r***@web.de
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
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 |
| |
------------------------------------------------------------------------------------------------------------------------------*
---------------------------------------------------*
* 6 -
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
*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

sachin pawar

2018-08-07 18:09:25 UTC

Permalink

Hi Daniel,

Oswatcher..no :D

If you provide the below , for both sqls, that should get the support start
the investigation.

<<<<<<<<<<<<<<<<<<<<<<<<<<
1. Please , for this sql id , provide the output from the SQLT utility,
using the XTRACT method (it is important use this method for this issue)
for:
--- The XTRACT method will take the SQLID of the problem SQL as input and
will *NOT* execute the SQL.
--- For information on obtaining and using SQLT, please refer to:
SQLT Diagnostic Tool (Doc ID 215187.1)
SQLT Usage Instructions (Doc ID 1614107.1)

For example the following file is from a successful SQLT run using the
XTRACT method:

sqlt_s45774_xtract_fp48hh5dkm529.zip

2. A trace of the SQL using the following steps:
++++++++++++
a. Connect to SQL*Plus as the query user.
b. Issue the following:

ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
ALTER SESSION SET tracefile_identifier = '_SQL_TRACE_1_';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

c. Within the same session, run the query in question.

d. Once the query completes (or has run for while and has been terminated,
*only* in the case of non-completing query), issue the following to close
the cursor:
SELECT * FROM DUAL;

e. Run tkprof against the trace file generated by the 10046 event, using
below syntax.

tkprof <trace file name >.trc <trace file name>.txt
sort=prsela,exeela,fchela

Important: The comment tag in the SQL needs to be changed every time the
SQL is run (e.g. test1, test2, test3...).
This is needed to ensure the hard parse that is required for the 10053
trace to work correctly.
+++++++++++++
<<<<<<<<<<<<<<<<<<<<<<<<<<

You may also refer this note ,if you are interesting in researching further
on it.

* Wrong Results Issues - Recommended Actions (Doc ID 150895.1)

Rgds,
Sachin Pawar
https://twitter.com/sach_pwr

Post by "Daniel Fink" (Redacted sender "daniel.fink" for DMARC)
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...

Post by r***@web.de
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
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
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 |
| |
------------------------------------------------------------------------------------------------------------------------------*
---------------------------------------------------*
* 6 -
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
*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

Andy Sayer

2018-08-07 23:07:20 UTC

Permalink

I'm guessing this is around 12.1.0.2 ? There were some group by bugs that
did the rounds in the first year of patch sets.

You'll probably find that setting optimizer_features_enable to be 11.1.0.7
or below will allow you to get the correct results from this SQL. If that's
the case then you are definitely hitting a bug and you should try patching
to the most recent patch available.

Hope that helps,
Andrew

Post by sachin pawar
Hi Daniel,
Oswatcher..no :D
If you provide the below , for both sqls, that should get the support
start the investigation.
<<<<<<<<<<<<<<<<<<<<<<<<<<
1. Please , for this sql id , provide the output from the SQLT utility,
using the XTRACT method (it is important use this method for this issue)
--- The XTRACT method will take the SQLID of the problem SQL as input and
will *NOT* execute the SQL.
SQLT Diagnostic Tool (Doc ID 215187.1)
SQLT Usage Instructions (Doc ID 1614107.1)
For example the following file is from a successful SQLT run using the
sqlt_s45774_xtract_fp48hh5dkm529.zip
++++++++++++
a. Connect to SQL*Plus as the query user.
ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
ALTER SESSION SET tracefile_identifier = '_SQL_TRACE_1_';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
c. Within the same session, run the query in question.
d. Once the query completes (or has run for while and has been terminated,
*only* in the case of non-completing query), issue the following to close
SELECT * FROM DUAL;
e. Run tkprof against the trace file generated by the 10046 event, using
below syntax.
tkprof <trace file name >.trc <trace file name>.txt
sort=prsela,exeela,fchela
Important: The comment tag in the SQL needs to be changed every time the
SQL is run (e.g. test1, test2, test3...).
This is needed to ensure the hard parse that is required for the 10053
trace to work correctly.
+++++++++++++
<<<<<<<<<<<<<<<<<<<<<<<<<<
You may also refer this note ,if you are interesting in researching
further on it.
* Wrong Results Issues - Recommended Actions (Doc ID 150895.1)
Rgds,
Sachin Pawar
https://twitter.com/sach_pwr

Post by r***@web.de
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
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
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 |
| |
------------------------------------------------------------------------------------------------------------------------------*
---------------------------------------------------*
* 6 -
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
*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

Sayan Malakshinov

2018-08-08 00:07:06 UTC

Permalink

Hi Daniel,

It seems like like this bug:
https://jonathanlewis.wordpress.com/2014/09/04/group-by-bug/amp/

Best regards,
Sayan Malakshinov
http://orasql.org

Mark W. Farnham

2018-08-08 18:11:27 UTC

Permalink

Hmm.

with session_count as

(

select sample_time, count(1) sess_count, min(trunc(sample_time,âMIâ) sample_minute

from dba_hist_active_sess_history

where sample_time > trunc(sysdate-(1/24))

group by sample_time

)

select sample_minute, max(sess_count) max_sessions

from session_count

group by sample_minute

order by sample_minute

If I understand, you want at most a single time row per minute with that row being the sample with the most sessions (the high sampled session_count in each minute sampled).

I donât believe your code is WRONG, but Iâm curious whether calculating the minute in the initial view gets you the correct answer. I just typed this in, so itâs possible I fubarâd the syntax.

mwf

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Daniel Fink (Redacted sender "daniel.fink" for DMARC)
Sent: Tuesday, August 07, 2018 12:26 PM
To: oracle-***@freelists.org
Subject: 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

WITH session_count AS

( SELECT sample_time, count(1) sess_count

FROM dba_hist_active_sess_history

WHERE sample_time >= TRUNC(sysdate - (1/24))

GROUP BY sample_time

)

SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,

MAX(sess_count) max_sessions

FROM session_count

GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi')

ORDER BY sample_minute

/

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?

"Daniel Fink" (Redacted sender "daniel.fink" for DMARC)

2018-08-08 18:33:37 UTC

Permalink

The 2nd query I posted is returning the data I want (max number of sessions
per minute, not a total count of sessions per minute). I pulled the queries
apart and checked the data output.

Post by Mark W. Farnham
Hmm.
with session_count as
(
select sample_time, count(1) sess_count, min(trunc(sample_time,âMIâ)
sample_minute
from dba_hist_active_sess_history
where sample_time > trunc(sysdate-(1/24))
group by sample_time
)
select sample_minute, max(sess_count) max_sessions
from session_count
group by sample_minute
order by sample_minute
If I understand, you want at most a single time row per minute with that
row being the sample with the most sessions (the high sampled session_count
in each minute sampled).
I donât believe your code is WRONG, but Iâm curious whether calculating
the minute in the initial view gets you the correct answer. I just typed
this in, so itâs possible I fubarâd the syntax.
mwf
sender "daniel.fink" for DMARC)
*Sent:* Tuesday, August 07, 2018 12:26 PM
*Subject:* 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
WITH session_count AS
( SELECT sample_time, count(1) sess_count
FROM dba_hist_active_sess_history
WHERE sample_time >= TRUNC(sysdate - (1/24))
GROUP BY sample_time
)
SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,
MAX(sess_count) max_sessions
FROM session_count
GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi')
ORDER BY sample_minute
/
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?

Mark W. Farnham

2018-08-09 14:22:40 UTC

Permalink

two things: your âcorrectâ query appears as blank to me, and I thought this revision might be useful to pin-pointing the bug IF not already solved and IF it returns correct results.

Glad you have got it solved.

mwf

From: Daniel Fink [mailto:***@returnpath.com]
Sent: Wednesday, August 08, 2018 2:34 PM
To: ***@rsiz.com
Cc: oracle-***@freelists.org
Subject: Re: Group by wrong results?

The 2nd query I posted is returning the data I want (max number of sessions per minute, not a total count of sessions per minute). I pulled the queries apart and checked the data output.

On Wed, Aug 8, 2018 at 12:12 PM Mark W. Farnham <***@rsiz.com> wrote:

Hmm.

with session_count as

(

select sample_time, count(1) sess_count, min(trunc(sample_time,âMIâ) sample_minute

from dba_hist_active_sess_history

where sample_time > trunc(sysdate-(1/24))

group by sample_time

)

select sample_minute, max(sess_count) max_sessions

from session_count

group by sample_minute

order by sample_minute

If I understand, you want at most a single time row per minute with that row being the sample with the most sessions (the high sampled session_count in each minute sampled).

I donât believe your code is WRONG, but Iâm curious whether calculating the minute in the initial view gets you the correct answer. I just typed this in, so itâs possible I fubarâd the syntax.

mwf

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Daniel Fink (Redacted sender "daniel.fink" for DMARC)
Sent: Tuesday, August 07, 2018 12:26 PM
To: oracle-***@freelists.org
Subject: 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

WITH session_count AS

( SELECT sample_time, count(1) sess_count

FROM dba_hist_active_sess_history

WHERE sample_time >= TRUNC(sysdate - (1/24))

GROUP BY sample_time

)

SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,

MAX(sess_count) max_sessions

FROM session_count

GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi')

ORDER BY sample_minute

/

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?

Rich J

2018-08-09 14:46:46 UTC

Permalink

Post by Mark W. Farnham
The 2nd query I posted is returning the data I want (max number of sessions per minute, not a total count of sessions per minute). I pulled the queries apart and checked the data output.

It's interesting that:

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.1' SCOPE=BOTH;

...has the query returning the correct results, as inferred by
Jonathan's blog post linked in a previous response.

I'm getting the same incorrect grouping as you with my 12.1.0.2 DB, and
this setting appears to be a workaround, although with the distinct
possibility of other side-effects.

Rich

Rich J

2018-08-09 16:19:24 UTC

Permalink

Post by Rich J

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.1' SCOPE=BOTH;
...has the query returning the correct results, as inferred by Jonathan's blog post linked in a previous response.
I'm getting the same incorrect grouping as you with my 12.1.0.2 DB, and this setting appears to be a workaround, although with the distinct possibility of other side-effects.

BTW, this seems to match BUG 19567916 and the bugs/fixes that supersede
it.

Rich

"Daniel Fink" (Redacted sender "daniel.fink" for DMARC)

2018-08-09 18:02:58 UTC

Permalink

Bingo! Thanks for finding that. I just tested it with the suggested
workaround and it returns the data as I expect.

Post by "Daniel Fink" (Redacted sender "daniel.fink" for DMARC)
The 2nd query I posted is returning the data I want (max number of
sessions per minute, not a total count of sessions per minute). I pulled
the queries apart and checked the data output.
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.1' SCOPE=BOTH;
...has the query returning the correct results, as inferred by Jonathan's
blog post linked in a previous response.
I'm getting the same incorrect grouping as you with my 12.1.0.2 DB, and
this setting appears to be a workaround, although with the distinct
possibility of other side-effects.
BTW, this seems to match BUG 19567916 and the bugs/fixes that supersede it.
Rich

Jonathan Lewis

2018-08-14 10:15:19 UTC

Permalink

I think the specific bug is more likely to be
20537092 : WRONG RESULT FOR NESTED QUERY GROUP BY

I'm not sure if this got through to the list on an earlier previous post - everything I've sent to the list in the last six weeks seems to have disappeared silently.
Ditto the post about using the/*+ no_elim_groupby(@queryblock) */ hint as a less aggressive way of working around the problem.

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of Rich J <***@society.servebeer.com>
Sent: 09 August 2018 17:19
To: oracle-***@freelists.org
Subject: Re: Group by wrong results?

On 2018/08/09 09:46, Rich J wrote:

On 2018/08/08 13:33, Daniel Fink wrote:

The 2nd query I posted is returning the data I want (max number of sessions per minute, not a total count of sessions per minute). I pulled the queries apart and checked the data output.

It's interesting that:

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.1' SCOPE=BOTH;

...has the query returning the correct results, as inferred by Jonathan's blog post linked in a previous response.

I'm getting the same incorrect grouping as you with my 12.1.0.2 DB, and this setting appears to be a workaround, although with the distinct possibility of other side-effects.

BTW, this seems to match BUG 19567916 and the bugs/fixes that supersede it.

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

Rich J

2018-08-14 13:16:40 UTC

Permalink

Post by Jonathan Lewis
I think the specific bug is more likely to be
20537092 : WRONG RESULT FOR NESTED QUERY GROUP BY
I'm not sure if this got through to the list on an earlier previous post - everything I've sent to the list in the last six weeks seems to have disappeared silently.

I replied to Daniel offlist that I had the same reproducible symptoms
that were resolved by patch 25838690. That patch was the latest
superseded fix of the original BUG 19567916 that I had posted. It fixes
5 similar situations to the bug you found, which makes me wonder if one
or more of these one-off patches would conflict...

Rich

p.s. I've seen a few posts from you lately, including one I'm hoping to
find time to follow up on! :)