Oracle Database 23ai Release Update 9 introduces yet another new feature aimed at improving the developer experience with the database: group by all.
Just like the new select without from dual clause this might not sound like a big deal, but after a little while working with it, you don’t really want to miss it anymore. You can find more details about it in the SQL language reference’s SELECT statement, or on oracle-base, as usual. Here is the railroad diagram as seen on docs.oracle.com:
What does group by all allow you to do? Let’s have at the example most performance engineers might be able to relate to: querying data from Active Session History (ASH).
Querying performance data
In many troubleshooting scenarios I typically find myself checking on the status of an application – Swingbench in this case – monitoring and observing what its various sessions do. In all releases up to 23.9, this is the query to run (or a variation thereof) to get a feel for the system you are looking at. Remember that you need to be appropriately licensed to query ASH data!
select
count(*),
session_id,
event,
session_state
from
v$active_session_history
where
client_info = 'Swingbench Load Generator'
and sample_time > sysdate - interval '15' minute
group by
session_id,
event,
session_state
order by
session_id,
count(*) desc;
In other words, I want to see what each Swingbench session has been waiting for over the last 15 minutes. On my Oracle Database 23ai Free instance, I can see this output:
COUNT(*) SESSION_ID EVENT SESSION_STATE
___________ _____________ __________________________________________ ________________
310 19 resmgr:cpu quantum WAITING
204 19 ON CPU
52 19 log file sync WAITING
33 19 log file switch (checkpoint incomplete) WAITING
10 19 buffer busy waits WAITING
9 19 library cache: mutex X WAITING
3 19 db file sequential read WAITING
3 19 enq: HW - contention WAITING
3 19 latch free WAITING
2 19 enq: CR - block range reuse ckpt WAITING
2 19 cursor: mutex X WAITING
2 19 cursor: pin S WAITING
2 19 latch: In memory undo latch WAITING
2 19 latch: cache buffers chains WAITING
1 19 latch: enqueue hash chains WAITING
316 20 resmgr:cpu quantum WAITING
197 20 ON CPU
57 20 log file sync WAITING
34 20 log file switch (checkpoint incomplete) WAITING
15 20 library cache: mutex X WAITING
8 20 buffer busy waits WAITING
3 20 db file sequential read WAITING
3 20 latch free WAITING
2 20 Allocate UGA memory from OS WAITING
2 20 Free private memory to OS WAITING
1 20 latch: redo allocation WAITING
1 20 enq: HW - contention WAITING
1 20 cursor: mutex S WAITING
1 20 cursor: pin S WAITING
1 20 latch: object queue header operation WAITING
1 20 direct path sync WAITING
1 20 latch: cache buffers chains WAITING
1 20 library cache: bucket mutex X WAITING
33 rows selected.
Oh dang, I forgot to add the wait_class, let’s do add it:
select
count(*),
session_id,
event,
wait_class,
session_state
from
v$active_session_history
where
client_info = 'Swingbench Load Generator'
and sample_time > sysdate - interval '15' minute
group by
session_id,
event,
session_state
order by
session_id,
count(*) desc;
If you have keen eyes you undoubtedly know what’s going to happen next. And yes, it happens to me all the time:
Error starting at line : 1 in command -
select
count(*),
session_id,
event,
wait_class,
session_state
from
v$active_session_history
where
client_info = 'Swingbench Load Generator'
and sample_time > sysdate - interval '15' minute
group by
session_id,
event,
session_state
order by
session_id,
count(*) desc
Error at Command Line : 5 Column : 2
Error report -
SQL Error: ORA-00979: "WAIT_CLASS": must appear in the GROUP BY clause or be used in an aggregate function
https://docs.oracle.com/error-help/db/ora-00979/00979. 00000 - "%s: must appear in the GROUP BY clause or be used in an aggregate function"
*Cause: The specified expression was not part of either the GROUP BY
clause, an aggregate function, or a constant but appeared in a part
of the query that is processed after the GROUP BY clause, such as
the SELECT clause, the ORDER BY clause, or the HAVING clause.
*Action: Add the expression to the GROUP BY clause or add an
aggregate function to specify which value of the expression
to use for each group. Ensure that any columns in the SELECT list
that are not part of an aggregate function are present in the
GROUP BY clause.
*Params: 1) expression
clause.
More Details :
https://docs.oracle.com/error-help/db/ora-00979/
Two things are worth noting:
- The error messages in Oracle Database 23ai are much more precise
- I forgot to add the wait_class to the group by clause
Enter group by all
Wouldn’t it be nice if I didn’t have to care about the group by clause at all? The good news is: you don’t. By changing the SQL statement to the new syntax, I don’t need to worry about the columns in the select list to be present in the group by clause:
select
count(*),
session_id,
event,
wait_class,
session_state
from
v$active_session_history
where
client_info = 'Swingbench Load Generator'
and sample_time > sysdate - interval '15' minute
group by
all
order by
session_id,
count(*) desc;
The major change is highlighted in line 13. That’s all you need to do from now on – nice 👌
Summary
Oracle Database 23ai introduces many new features with every Release Update. Make sure to check the New Features Guide every now and then to see what’s new and how to benefit from the enhanced developer experience. I am in love with select without “from dual” and group by all, and many other cool new features.
Martins Blog 