Preparing for my talk at the HK Open Source Conference I wanted to confirm some of the things I had read about these plugins, mostly from Matthias Crauwels excellent article on the subject.

My lab environment consists of Windows 2016 Domain controller and client machines, with MySQL 8 running on Oracle Linux 7.

First I configure the database to use the server-side plugin, configure the plugin to point to the domain controller, and create a database user associated via the plugin with my Windows account.

mysql> INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so';
Query OK, 0 rows affected (0.05 sec)

mysql> SET GLOBAL authentication_ldap_simple_server_host='win-dc.windows.domain';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'patrick'@'%'
    ->        IDENTIFIED WITH authentication_ldap_simple
    ->        BY 'CN=patrick,CN=Users,DC=WINDOWS,DC=DOMAIN';
Query OK, 0 rows affected (0.08 sec)

Next I successfully connect from my Windows client to this database account passing in my Windows credentials.

[patrick@WIN-CLIENT] C:\> mysql --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --password=Password123           `
>>                              --enable-cleartext-plugin
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 8.0.13-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Checking the network packets between the Windows client and database server, observe that (almost all) the traffic is encrypted with a self-signed certificate.

[root@lnx-mysql8 ~]# tcpflow -c port 3306
tcpflow: listening on enp0s3
192.168.002.004.03306-192.168.002.002.49801: U
<UdYcaching_sha2_passwordLR%
192.168.002.002.49801-192.168.002.004.03306:
192.168.002.002.49801-192.168.002.004.03306: ANi<l[}y~yn+,/0#'$(g@kj
239-.<>%=h&/05612?)i*17
U


0H1212013500Z0@1>0<U5MySQL_Server_8.0.13_Auto_Generated_Server_Certificate0"0
DH<@_Cn%VR9A)[QLMVjAYe1Kju2\)Wk7SqOG02Xl;>n)i&gjV:/,J^f("qBDH8kW:lKQ+B 3;K^!.$5BxJ=0XWD,00U00
^uBey@/e;m4sQ
lJDiU{?s;[72FeLkS
p{WFXdr**yLPp'ij_(z`E"{Lxu|1DX$Jp`w;Ti<}BlLA@?
0H1212013500Z0<1:08U1MySQL_Server_8.0.13_Auto_Generated_CA_Certificate0"0 2 |s?jupq&GG]5`-2$1'$,AT"`OA/^d((~:n0Z'~O?$+az]y(De"5Klwiv(B"ST~rE0'7qIWZc%R$D8v9MzF|\blAK00U00 9()~I?-Nq(#LRACVU>eOWB["IOm$\]fvNa7W\m?
dYt^dT)-Y&UWUBlnmOA}?%YW>D
C*<*f,OD^^G"
'#-F*/=@=]mnDRVQ,RG(a|la^!fH)5"{EbynK4{q:CiV%#(f_hr_/-X~S/:
(@C{jB&{%ddU-F0fG/2t_aPUw\A%&+:{K"t(+}Q~+|#XxJNS\XhDz);=79.o{Q<sp??D^0\v:6[a|$ooBZ(K0Zt}. WjeoWOi4AE]YkQXUH0E2;'US-/Dw0[4@&3]]/c`GCEEzmU@oG2{%Z&`0!A}]A[.:m 7q@w]Rv3-nKZP$N''}yjKssvFvc:O$'rU)f(=@,Wup,>b+xF[Lv6;
192.168.002.004.03306-192.168.002.002.49801: ,
{xsGdN>
XEUyH`?T'd7fI JwN%:eq2#;y'Nh(hm}c$dG'zs
zT@(W$]#Wm4nw2t7(`X-5lK'SXwk0qS3
192.168.002.002.49801-192.168.002.004.03306: =@,Wa{q%)}aH9;.%~k$hoKI+a8\B}@NR@Dp`JFDwK\(1%9 %5XqO f:Pgmvi|>N^&=k/~egl]i@s;p
\8&?4AKg>r63E
192.168.002.004.03306-192.168.002.002.49801: H`XmX2:@~Oq)BY-|<`GS6ew 192.168.002.002.49801-192.168.002.004.03306: 0=@,W.la:QC0,%>G~L6
192.168.002.004.03306-192.168.002.002.49801: #`X-$~v4_L3
|s
192.168.002.002.49801-192.168.002.004.03306: ==@,W.BFu?6'F6|P)EC]?%n)ww
nSHK*+@6FS(9l|Y2>apy;-192.168.002.002.49801: |`X,

Note however it is possible to disable this encryption with the –ssl-mode=disabled flag.

[patrick@WIN-CLIENT] C:\> mysql --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --password=Password123           `
>>                              --enable-cleartext-plugin        `
>>                              --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 8.0.13-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Observe that the password is now transmitted unencrypted during authentication

[root@lnx-mysql8 ~]# tcpflow -c port 3306
tcpflow: listening on enp0s3
192.168.002.004.03306-192.168.002.002.49899: U
8.0.13-commercialAi95M2)P3(f}3caching_sha2_password
192.168.002.002.49899-192.168.002.004.03306: patrick lv
J5T{W0J-rcaching_sha2_passwordq_pid172program_namemysql_client_namelibmysql_thread2640_client_version8.0.13_osWin64_platformx86_64
192.168.002.004.03306-192.168.002.002.49899: mysql_clear_password
192.168.002.002.49899-192.168.002.004.03306: Password123
192.168.002.004.03306-192.168.002.002.49899:
192.168.002.002.49899-192.168.002.004.03306: !select @@version_comment limit 1
192.168.002.004.03306-192.168.002.002.49899: 'def@@version_comment$%$MySQL Enterprise Server - Commercial

Such connections can be prevented with the require_secure_transport=ON variable

mysql> SET GLOBAL require_secure_transport=ON;
Query OK, 0 rows affected (0.00 sec)

In this case connections which disable encryption will be rejected

[patrick@WIN-CLIENT] C:\> mysql --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --password=Password123           `
>>                              --enable-cleartext-plugin        `
>>                              --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
[patrick@WIN-CLIENT] C:\>

Unfortunately even though the connection is rejected, the password is still transmitted unencrypted during authentication process

[root@lnx-mysql8 ~]# tcpflow -c port 3306
tcpflow: listening on enp0s3
192.168.002.004.03306-192.168.002.002.49867: U
8.0.13-commercial@=l?K/sbq!Qa&sG{{Bcaching_sha2_password
192.168.002.002.49867-192.168.002.004.03306: patrick Au'"?..`%85n]~caching_sha2_passwordr_pid3416program_namemysql_client_namelibmysql_thread4992_client_version8.0.13_osWin64_platformx86_64
192.168.002.004.03306-192.168.002.002.49867: mysql_clear_password
192.168.002.002.49867-192.168.002.004.03306: Password123
192.168.002.004.03306-192.168.002.002.49867: aW#HY000Connections using insecure transport are prohibited while --require_secure_transport=ON.

However if we re-run the original connection attempt (with encrypted traffic between database client and server)  capturing the network traffic between database and domain controller, we can see password is transferred unencyrpted as this point

[root@lnx-mysql8 .passwords]# tcpflow -c port 389
tcpflow: listening on enp0s3
192.168.002.004.43068-192.168.002.001.00389: 0PcK

NtVer0mainWINDOWS.DOMAIN
netlogon
192.168.002.001.00389-192.168.002.004.43070: 0d00znetlogon1jhl)X0K4fWINDOWSDOMAINWIN-DCWINDOWSWIN-DCDefault-First-Site-NameE0e

192.168.002.004.43070-192.168.002.001.00389: 0B
192.168.002.004.42990-192.168.002.001.00389: 0?`:(CN=patrick,CN=Users,DC=WINDOWS,DC=DOMAINPassword123
192.168.002.001.00389-192.168.002.004.42990: 0a

The documentation alludes to some of the restrictions, although to my mind it seems to focus on the encryption between database client and server, but not between database server and Domain Controller (unless I’ve missed something).

The server-side authentication_ldap_simple plugin performs simple LDAP authentication. For connections by accounts that use this plugin, client programs use the client-side mysql_clear_password plugin, which sends the password to the server in clear text. No password hashing or encryption is used, so a secure connection between the MySQL client and server is recommended to prevent password exposure.

Based on the above observations, as I was expecting, this plugin combination is not really suitable for implementation for any environment which takes security seriously.

If that wasn’t enough reason to avoid the plugin, during the course of my investigation I discovered some other surprising behavior.  Based on discussion with Oracle support this should be resolved in the next release of SQL Server, so I’ll wait till that is released before sharing.

Recently some of our developers have moved forwards with fixing some code that has been using literals, instead of bind variables.  A complication is that they are using lists of values, so the SQL they were generating was of format

WHERE COL1 IN (1,2,3,4)

They resolved this by using an array in Java and construct similar to the following:

WHERE COL1 IN (SELECT * FROM TABLE(?))

However when moving to the new method performance degraded, they were getting full table scans where previously they were getting index access.
Initially I thought we could resolve this by using a cardinality hint on the TABLE select ie:

WHERE COL1 IN (SELECT /*+CARDINALITY(t 1) */ * FROM TABLE(?) t)

However this didn’t help much. I’ve managed to reproduce the problem to the testcase below (running on 18c):

SQL> CREATE TABLE t1
  2      AS
  3          SELECT
  4              ROWNUM id,
  5              rpad('x', 100) padding
  6          FROM
  7              dual
  8          CONNECT BY
  9              level  <= 4000; -- comment to avoid WordPress format issue  

Table T1 created.

SQL> create index t1_i1 on t1(id);

Index T1_I1 created.

SQL> exec dbms_stats.gather_table_stats(null, 't1');

PL/SQL procedure successfully completed.

SQL> create or replace type n_t as table of number;
  2  /

Type N_T compiled

SQL> SELECT /*+ gather_plan_statistics */  NULL
  2      FROM
  3        (SELECT DISTINCT a.id
  4              FROM t1   a)
  5      WHERE
  6          id IN (1)
  7  /
NULL



SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
SQL_ID  a18bwsyqx37gs, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */  NULL     FROM       (SELECT
DISTINCT a.id             FROM t1   a)     WHERE         id IN (1)

Plan hash value: 405044659

---------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  VIEW               |       |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   SORT UNIQUE NOSORT|       |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN | T1_I1 |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."ID"=1)



21 rows selected.

SQL> SELECT /*+ gather_plan_statistics */  NULL
  2      FROM
  3        (SELECT DISTINCT a.id
  4              FROM t1   a)
  5      WHERE
  6          id IN (SELECT /*+cardinality(nt 1) */ column_value FROM TABLE ( n_t(1) ) nt)
  7  /
NULL



SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
SQL_ID  cwcsdhm543ph2, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */  NULL     FROM       (SELECT
DISTINCT a.id             FROM t1   a)     WHERE         id IN (SELECT
/*+cardinality(nt 1) */ column_value FROM TABLE ( n_t(1) ) nt)

Plan hash value: 1445712880

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |      1 |        |      1 |00:00:00.01 |      64 |       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI                   |         |      1 |      1 |      1 |00:00:00.01 |      64 |  2546K|  2546K|  303K (0)|
|   2 |   JOIN FILTER CREATE                    | :BF0000 |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|         |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|   4 |   VIEW                                  |         |      1 |   4000 |      1 |00:00:00.01 |      64 |       |       |          |
|   5 |    HASH UNIQUE                          |         |      1 |   4000 |      1 |00:00:00.01 |      64 |  2294K|  2294K|  514K (0)|
|   6 |     JOIN FILTER USE                     | :BF0000 |      1 |   4000 |      1 |00:00:00.01 |      64 |       |       |          |
|*  7 |      TABLE ACCESS FULL                  | T1      |      1 |   4000 |      1 |00:00:00.01 |      64 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=VALUE(KOKBF$))

PLAN_TABLE_OUTPUT
   7 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"A"."ID"))



27 rows selected.

SQL>

Fundmentally I think the problem is the optimzer is unable to push the TABLE function into the inner select.
For the moment we’re having to revert to a hybrid solution where they generate SQL such as the following:

WHERE COL1 IN (?,?,?,?)

Each value has to be bound seperately. It’s not ideal as shared pool is somewhat full of each variation depending on how many values there are.  Also they are having to do a soft parse of the cursor each time, rather than parsing once and re-using.

However other than getting the developers to rewrite all their queries I don’t see any better solution at the moment.

After implementing a recent change, and I pulled of a quick AWR report to compare performance.  At first glance I was impressed by significant improvement in “log file parallel write” event, which had halved from 2 millisecond to 1 millisecond, but something about the numbers didn’t quite look right.

Before:

After:

Let’s calculate the average wait times ourselves:
Before:  2,346s/1,482,747=0.00158219844s=1.58 ms.
After:  2,658s/1,785,172=0.00148893215s=1.49 ms.

So AWR rounds to the nearest millisecond, and this has turned an actual 6% (=(1.58-1.49)/1.58*100)  improvement into a 100% improvement.

Note that in Oracle 12.2 AWR handles this much better by reporting wait events in microseconds, one more reason to upgrade!

Update (2018-07-28) Lukas Eder highlighted via twitter that my comparison was invalid dues to some PL/SQL optimizations. I have updated the testcase, there is still significant improvement, just not as much as my previous test

Recently I’ve been noticing a lot of frequently called PL/SQL code that has structure similar to the following:

SELECT TO_CHAR(l_date, 'YYYY-MM-DD') INTO l_string FROM DUAL;

I really don’t know how people got into the habit of doing things this way, isn’t the following much simpler?

l_string := TO_CHAR(l_date, 'YYYY-MM-DD');

Does it really matter? Isn’t it just a coding preference? Let’s benchmark to see the difference:

SQL> declare
  2      l_string VARCHAR2(10);
  3      l_len NUMBER := 0;
  4      l_date DATE := SYSDATE;
  5  begin
  6
  7      for loopy in 1..1000000
  8      loop
  9          SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') into l_string FROM DUAL;
 10          l_len := l_len + LENGTH(l_string);
 11      end loop;
 12      dbms_output.put_line(l_len);
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.171
SQL>

We executed the statement one million times in just over 17 seconds. Not too shabby. However let’s try the other way:

SQL> declare
  2      l_string VARCHAR2(10);
  3      l_len NUMBER := 0;
  4  begin
  5      for loopy in 1..1000000
  6      loop
  7          l_string := TO_CHAR(SYSDATE, 'YYYY-MM-DD');
  8          l_len := l_len + LENGTH(l_string);
  9      end loop;
 10      dbms_output.put_line(l_len);
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.082

Same amount of work, eight times faster. Why? Probably something to do with context switches between PL/SQL and SQL Engines, but don’t stress too much about that, just look at the figures.

Oracle Database CPU cycles are expensive, don’t waste them doing things inefficiently. Please stop abusing the DUAL table.

We’ve just upgraded the database containing our recovery catalog to 12.2. Although rman operations seemed to be completing OK, we noticed the following slightly worrying error message in the alert log:

PDB(3):WARNING: too many parse errors, count=44106 SQL hash=0x2473a808
PDB(3):PARSE ERROR: ospid=398064, error=6550 for statement:
2018-07-22T17:06:22.313262+08:00
PDB(3):BEGIN :owner := dbms_rai_owner; END;
PDB(3):Additional information: hd=0x26ee29a560 phd=0x26ee122418 flg=0x101476 cisid=343 sid=343 ciuid=343 uid=343
2018-07-22T17:06:22.313360+08:00
PDB(3):----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x26afed3ed8        36  package body RCAT.DBMS_RCVVPC.IS_RA
0x26afed3ed8         8  package body RCAT.DBMS_RCVVPC.__pkg_init
0x240a892a20         1  anonymous block
0x23cb60d9d0         4  anonymous block

I checked the source of the package DBMS_RCVPC in the recovery catalog (it is unwrapped PL/SQL):

FUNCTION is_ra
  RETURN BOOLEAN
  IS
    l_owner all_users.username%TYPE;
  BEGIN
    EXECUTE IMMEDIATE 'BEGIN :owner := dbms_rai_owner; END;'
      USING OUT l_owner;
    RETURN (l_owner IS NOT NULL);
  EXCEPTION
    WHEN OTHERS
    THEN RETURN FALSE;
  END is_ra;

I cannot find any object named dbms_rai_owner in the database.
It seems the package is designed to fail with a parse error and that will be caught by the “WHEN OTHERS” handler and return false, and that is what had been happening when this database was at version  12.1.
What has changed is that Oracle now writes details about such parse errors (see Oracle support document 2320935.1 for more details).

Ideally the Oracle developers need to re-write the package logic so it doesn’t rely on throwing parsing exceptions, but until then, I think this message in the alert log can be safely ignored.

Update (2018-07-27): Bug 28223871 – RMAN12C.DBMS_RCVVPC.IS_RA WARNING: TOO MANY PARSE ERRORS, COUNT=4519

Symptom can be fixed by disabling VPD on recovery catalog using dbmsrmanvpc.sql -novpd. Obviously weigh up the pros and cons of this before proceeding.

Update (2018-03-16) Seems WordPress or SQLPlus messed up my testcase, I have updated post to include unedited testcase as well as output.

Update (2018-03-22) Oracle Support have created a new bug for this issue, Bug 27730925 – WRONG CARDINALITY ESTIMATION FROM HYBRID HISTOGRAM ON NCHAR COLUMN

Update (2018-07-24) Tested the patch for bug 27730925 resolves issue. Note the following is required for fix to take effect: alter session set “_fix_control”=’27730925:1′;

Paraphrasing Jonathan Lewis it’s very easy to start ‘breaking’ Oracle as soon as you start combining a few features that most people are not running. The following demonstrates that if you are using NCHAR datatypes (in our case this is forced on us because due to our JDEdwards application) and if you have a hybrid histogram then cardinality estimates vary greatly depending on whether or not you pad your predicates.

Of course with NCHAR datatypes, the results and hence cardinality should be unchanged whether or not predicates are padded.  The issue does not reproduce the symptoms with CHAR datatypes.

SQL>  CREATE TABLE HH_NC_TAB (COL NCHAR(10));

Table created.

SQL> insert /*+APPEND*/ into HH_NC_TAB
  2      SELECT 'AAA'||mod(rownum, 10) FROM DUAL CONNECT BY LEVEL < 100000
  3      union all
  4      SELECT 'AAA'||rownum FROM DUAL CONNECT BY LEVEL  < 100000
  5  ;

199998 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'HH_NC_TAB', METHOD_OPT=>'FOR COLUMNS COL SIZE 254');

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR SELECT * FROM HH_NC_TAB WHERE COL = 'AAA1';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2981390417

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    21 |   206   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| HH_NC_TAB |     1 |    21 |   206   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("COL"=U'AAA1')

13 rows selected.

SQL> EXPLAIN PLAN FOR SELECT * FROM HH_NC_TAB WHERE COL = RPAD('AAA1', 10);

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2981390417

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 10236 |   209K|   204   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| HH_NC_TAB | 10236 |   209K|   204   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("COL"=U'AAA1      ')

13 rows selected.

SQL>

Issue reproduces on 12.1, 12.2 and I’ve uploaded to LiveSQL so you can verify for yourself on 18.1

I’ll try to write this up further when I make progress, but just wanted somewhere to share testcase. I’m guessing it’s a multi-tenant bug, but if that’s the case I’m a little surprised why no-one else has hit it.

SQL> CREATE TABLE DUMMY AS SELECT * FROM DBA_OBJECTS WHERE 1=0;

Table created.

SQL> SELECT resource_consumer_group
  2  FROM v$session
  3  WHERE sid IN (SELECT sid FROM v$mystat);

RESOURCE_CONSUMER_GROUP
--------------------------------
OTHER_GROUPS

SQL> BEGIN
  2    DBMS_SCHEDULER.create_job (
  3      job_name        => 'RC_JOBA',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'BEGIN INSERT INTO DUMMY SELECT * FROM DBA_OBJECTS; DELETE FROM DUMMY; COMMIT; END;',
  6      enabled         => TRUE);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT consumer_group
  2  FROM
  3      v$active_session_history ash,
  4      dba_rsrc_consumer_groups cg
  5  WHERE
  6      action = 'RC_JOBA'
  7      AND   ash.consumer_group_id = cg.consumer_group_id;

CONSUMER_GROUP
--------------------------------------------------------------------------------
SYS_GROUP

SQL>

Edit: I guess if you are testing you may need to update the task the job does to ensure it writes something into v$active_session_history, if you’ve got suggestion for better way to do that let me know, I can’t think of one right now.

Update 2018-03-02: Worryingly this doesn’t seem to be simply a reporting error. I updated the current database resource plan such that any SQL estimated to take over 1 second to complete would be cancelled.

Running a bad SQL directly as user caused query to be cancelled as expected, running via dbms_scheduler attempted to execute the bad SQL.

SQL> CREATE OR REPLACE PROCEDURE KILL_ME_PROC AS
  2  BEGIN
  3     FOR LOOPY IN (SELECT NULL FROM ALL_OBJECTS A1, ALL_OBJECTS A2, ALL_OBJECTS A3, ALL_OBJECTS A3)
  4     LOOP
  5        NULL;
  6     END LOOP;
  7  END;
  8  /

Procedure created.

SQL>
SQL> EXEC KILL_ME_PROC;
BEGIN KILL_ME_PROC; END;

*
ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted
ORA-06512: at "C##PATRICKJOLLIFFE.KILL_ME_PROC", line 3
ORA-06512: at line 1


SQL> SELECT resource_consumer_group
  2  FROM v$session
  3  WHERE sid IN (SELECT sid FROM v$mystat);

RESOURCE_CONSUMER_GROUP
--------------------------------
OTHER_GROUPS

SQL> BEGIN
  2      DBMS_SCHEDULER.create_job (
  3        job_name        => 'KILL_ME_JOB2',
  4        job_type        => 'STORED_PROCEDURE',
  5        job_action      => 'KILL_ME_PROC',
  6        enabled         => TRUE);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> SELECT ERRORS FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'KILL_ME_JOB2' AND LOG_DATE > SYSDATE-1/24;

ERRORS
--------------------------------------------------------------------------------
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-06512: at "C##PATRICKJOLLIFFE.KILL_ME_PROC", line 3


SQL>