Profile Option SESSIONS_PER_USER Not Limiting Parallel Workers After Upgrading To 12.1 (Doc ID 2273312.1)

Last updated on SEPTEMBER 08, 2024

Applies to:

Symptoms

User is assigned profile support_profile. This profile has SESSIONS_PER_USER set to 4. If you try and login 5 times as that user in 5 different sql sessions, on the 5th attempt you get the error

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

But if you run a query as that user with parallel 16 hint, you get 16 parallel workers. Note resource_limit is set to TRUE.

If  you  run the same test against an 11.2 database and when you run a query with parallel hints either get 4 sessions at the most or no parallel workers. So something has changed with 12.1 where this limit is not being enforced. We need it to be enforced.

TEST CASE:

========

conn sys as sysdba

alter system set resource_limit=true;
CREATE PROFILE support_profile LIMIT SESSIONS_PER_USER 6;
alter user <USERNAME>  profile support_profile;

conn <USERNAME>/<PASSWORD>

SQL> alter session set "_px_trace"="high","compilation","high","execution","messaging","time";

Session altered.

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 10';

Session altered.

SQL> select /*+ PARALLEL(8) monitor*/ count(*) from <TABLE NAME>;

COUNT(*)
----------
918843

SQL> SET LONG 1000000
SQL> SET LONGCHUNKSIZE 1000000
SQL> SET LINESIZE 1000
SQL> SET PAGESIZE 0
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => NULL, type => 'TEXT',report_level=>'ALL') AS report FROM dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(8) monitor*/ count(*) from sales

Parallel Execution Details (DOP=8 , Servers Allocated=8)
==========================================================================================
| Name | Type | Server# | Elapsed | Cpu | Other | Buffer | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Gets | (sample #) |
==========================================================================================
| PX Coordinator | QC | | 0.11 | 0.11 | 0.00 | 48 | |
| p000 | Set 1 | 1 | 0.03 | 0.03 | | 15 | |
| p001 | Set 1 | 2 | 0.01 | | 0.01 | 15 | |
| p002 | Set 1 | 3 | 0.02 | | 0.02 | 18 | |
| p003 | Set 1 | 4 | 0.01 | | 0.01 | 18 | |
| p004 | Set 1 | 5 | 0.02 | 0.02 | | 18 | |
| p005 | Set 1 | 6 | 0.03 | 0.03 | | 18 | |
| p006 | Set 1 | 7 | 0.02 | 0.02 | | 15 | |
| p007 | Set 1 | 8 | 0.02 | 0.02 | | 15 | |
==========================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1379709461)
====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |
| 2 | PX COORDINATOR | | | | 1 | +0 | 9 | 8 | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 1 | +0 | 8 | 8 | | |
| 4 | SORT AGGREGATE | | 1 | | 1 | +0 | 8 | 8 | | |
| 5 | PX BLOCK ITERATOR | | 919K | 4 | 1 | +0 | 8 | 54 | | |
| 6 | BITMAP CONVERSION COUNT | | 919K | 4 | 1 | +0 | 44 | 54 | | |
| 7 | BITMAP INDEX FAST FULL SCAN | SALES_PROMO_BIX | | | 1 | +0 | 44 | 54 | | |
====================================================================================================================================================

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.