Here is a quick and interesting observation regarding the %logons% statistics. The database version is Oracle 19c and, apparently, it applies to earlier versions as well. The question that I am trying to answer here is this “How does a parallel query execution relate to %logon% statistics?”
Here is a test case.
Let’s check the statistics for the instance:
SQL> select name, value from v$sysstat where name like '%logon%';
NAME VALUE
-------------------------------- ----------
logons cumulative 68368
logons current 108
user logons cumulative 6762
SQL>
Now, do nothing but log on to the database:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
Let’s check the statistics again:
SQL> select name, value from v$sysstat where name like '%logon%';
NAME VALUE
-------------------------------- ----------
logons cumulative 68369
logons current 109
user logons cumulative 6763
SQL>
As a result, each of the statistics above gets incremented by one. So far, so good.
Now, from within that brand-new session, let’s execute a parallel query:
SQL> select /*+ parallel(8) */ count(*) from tt1;
COUNT(*)
----------
73252
SQL>
Display the statistics to compare:
SQL> select name, value from v$sysstat where name like '%logon%';
NAME VALUE
-------------------------------- ----------
logons cumulative 68377
logons current 109
user logons cumulative 6763
SQL>
As a result, the only statistics that changes is “logons cumulative“. It has grown by exactly the number of allocated parallel slaves: 8. Repetitive execution of the same query with different degrees of parallelism increments only this statistics and it does this by that degree each time.
The “logons current” statistics doesn’t change. This one doesn’t include the slave sessions but the coordinator only. The coordinator sid is the actually the sid of the session that executes the query. Another observation is that “user logons cumulative” statistics does not include slave processes either.
This may come in very handy when troubleshooting a logon storm caused by parallel execution.