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@xxxxxx> 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@12.1 > set autotr traceonly arrays 5000 lines 300 pages 5000 feedb on
sys@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@!-.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@xxxxxxxxxxxxx>
*An:* oracle-l@xxxxxxxxxxxxx
*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?
-- //www.freelists.org/webpage/oracle-l
