Re: Group by wrong results?

From: Andy Sayer <andysayer_at_gmail.com>
Date: Wed, 8 Aug 2018 00:07:20 +0100
Message-ID: <CACj1VR6Lhj8BCAKi+E2XLFKZzWw1cqS9bWZ9FjMVwJdfMBSmqQ_at_mail.gmail.com>



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_at_gmail.com> 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_at_freelists.org>
> 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_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
Received on Wed Aug 08 2018 - 01:07:20 CEST

Original text of this message