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
On 7 August 2018 at 19:09, sachin pawar <getsach@xxxxxxxxx> wrote:
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
On Tue, Aug 7, 2018 at 12:58 PM, Daniel Fink <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
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
