Hi
A bit of an odd one - possibly a bug.
DROP TABLE xxibpc_test;
CREATE TABLE xxibpc_test AS
WITH data AS
(SELECT LEVEL l_no
FROM dual
CONNECT BY LEVEL <= 10000)
SELECT 1 instance_number
,1 + l_no session_id
,10 + l_no session_serial#
,CAST (systimestamp - ((1 / 24 / 60) * l_no) AS TIMESTAMP(3)) sample_time
FROM data;
SELECT trunc(sample_time,'HH24') sample_hr
,COUNT(*) cnt
FROM xxibpc_test
GROUP BY trunc(sample_time,'HH24')
ORDER BY 1;
SELECT trunc(sample_date,'HH24') sample_hr
,trunc(AVG(sessions)) avg_sessions
,MAX(sessions) max_sessions
FROM (SELECT trunc(sample_time,'Mi') sample_date
,COUNT(DISTINCT to_char(instance_number) || to_char(session_id) || to_char(session_serial#)) sessions
FROM xxibpc_test
WHERE 1=1
--AND rownum < 1e28 -- Bug Fix
GROUP BY trunc(sample_time,'Mi'))
GROUP BY trunc(sample_date,'HH24')
ORDER BY 1;
If you run the first SELECT you get 167 rows, which is correct.
But if you run the second SELECT you get 10,000 rows, which is not correct.
If you uncomment the rownum predicate, you get the correct 167 rows.
I'm using a version of this against dba_hist_active_sess_history to get the average and maximum number of session in a minute for each hour.
Am I missing something? The only way I can get it to work is to add the rownum < 1e28 predicate, which I assume is stopping the CBO from rewriting the query.
Thanks and Regards
Ian
Yes - this is a bug. But it's fixed in 12.2 and there's a patch available for 12.1.0.2.
In the meantime, you can work around it by setting _optimizer_aggr_groupby_elim to false:
alter session set "_optimizer_aggr_groupby_elim"=true;
select count(*) from (
SELECT trunc(sample_date,'HH24') sample_hr
,trunc(AVG(sessions)) avg_sessions
,MAX(sessions) max_sessions
FROM (SELECT trunc(sample_time,'Mi') sample_date
,COUNT(DISTINCT to_char(instance_number) || to_char(session_id) || to_char(session_serial#)) sessions
FROM xxibpc_test
WHERE 1=1
GROUP BY trunc(sample_time,'Mi'))
GROUP BY trunc(sample_date,'HH24')
);
COUNT(*)
10000
alter session set "_optimizer_aggr_groupby_elim"=false;
select count(*) from (
SELECT trunc(sample_date,'HH24') sample_hr
,trunc(AVG(sessions)) avg_sessions
,MAX(sessions) max_sessions
FROM (SELECT trunc(sample_time,'Mi') sample_date
,COUNT(DISTINCT to_char(instance_number) || to_char(session_id) || to_char(session_serial#)) sessions
FROM xxibpc_test
WHERE 1=1
GROUP BY trunc(sample_time,'Mi'))
GROUP BY trunc(sample_date,'HH24')
);
COUNT(*)
167 See MOS note 21826068.8 for further details.