Parallel Query and Group by on SYSTIMESTAMP

From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 2 Sep 2014 08:50:09 +0800

I am looking at a couple of queries that use Parallel Query and also
include GROUP BY on SYSTIMESTAMP  (among other columns in the GROUP BY).

I suspect that, while on many occasions there are enough PQ slaves
available to start at the same time and return the same SYSTIMESTAMP, on
other occasions, Oracle uses some existing PQ slaves with a mix of
newly-started PQ slaves resulting in different PQ slaves returning
different SYSTIMESTAMP values and differences in the GROUP BY count.

Has anyone seen usage of SYSTIMESTAMP in a GROUP BY in a query that uses
PQ slaves ?  And seen different results (count of rows) at different
times, even though data in the underlying tables (on which other GROUP
BY columns are defined) doesn't change. 

For example :
SELECT  /*+ PARALLEL  (MANUAL) */
count(1), sum(col3)
FROM (SELECT /*+ PARALLEL  (MANUAL) */ col1, col2, col3, qrydttm,
otherdttm
             FROM
              (select col1, col2, col3,
              CAST (SYSTIMESTAMP AS TIMESTAMP) qrydttm,
              CAST (SYSTIMESTAMP AS TIMESTAMP) otherdttm
              FROM ....
              GROUP BY col1, col2, col3, CAST (SYSTIMESTAMP AS
TIMESTAMP) , CAST (SYSTIMESTAMP AS TIMESTAMP)
              )
           )
            

Hemant K Chitale




This email and any attachments are confidential and may also be privileged. If 
you are not the intended recipient, please delete all copies and notify the 
sender immediately. You may wish to refer to the incorporation details of 
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at 
https://www.sc.com/en/incorporation-details.html.

Parallel Query and Group by on SYSTIMESTAMP

Other related posts: