Re: Group by wrong results?

From: sachin pawar <getsach_at_gmail.com>
Date: Tue, 7 Aug 2018 14:09:25 -0400
Message-ID: <CAA_xQWq6WPGY7-A-TgNVFQuvRDJA_6ZinAQcyF6aMjRE8af7AQ_at_mail.gmail.com>

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 Tue Aug 07 2018 - 20:09:25 CEST

This message: [ Message body ]
Next message: Andy Sayer: "Re: Group by wrong results?"
In reply to Daniel Fink: "Re: Group by wrong results?"
Next in thread: Mark W. Farnham: "RE: Group by wrong results?"
Reply: Andy Sayer: "Re: Group by wrong results?"

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

Original text of this message