SQL Plan Directives: Gotta Purge ‘Em All

A tip I picked up from Nigel Bayliss regards purging SQL Plan Directives, I’ve been using it a lot recently and can’t see it documented elsewhere.

As some background these records, exposed via the DBA_SQL_PLAN_DIRECTIVES view, are cardinality corrections created and used when running with Adaptive Statistics enabled.  There is a job that should automatically purge all records unused for longer than the value of SPD_RETENTION_WEEKS, but we’ve experienced occasions when this job doesn’t work as expected.

The records can be individually purged by calling DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE, but that’s a pain if you’ve got a lot of them

However what the documentation doesn’t mention is that you can call the procedure, passing in NULL for the mandatory directive_id parameter:

exec sys.DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE (NULL);

This will purge all records based on retention rules that the auto-purge job follows.  If you really want to Purge ‘Em All then you can set the retention to 0 before calling the procedure.

exec sys.DBMS_SPD.SET_PREFS('SPD_RETENTION_WEEKS', '0');

 

Planned (Cursor) Obsolescence

I’ll start this blog-post by posing a question. Is it possible to have multiple records in v$sql for a given sql_id and child_number combination? While the title of this blog post may give you some clues, I’ll admit I’d always assumed that those values uniquely identified a child cursor.

As a bit of background we had a database availability situation this week, which we narrowed down to SGA issues, specifically bug 15881004 “Excessive SGA memory usage with Extended Cursor Sharing”. Some of our more complex SQL Statements were getting many (more than 700) child cursors. The reported reason for the child was “Bind mismatch(33)”.  Probably caused by bug 14176247 “Many child cursors using Adaptive Cursor Sharing with binds (due to BIND_EQUIV_FAILURE)”, although that is listed as fixed in 12.1 and this instance is running on 12.2.

We resolved the immediate issue by flushing the shared pool (admittedly not a great solution, but sometimes you got to do what you got to do), and created SQL Plan Baselines for those problem SQL statements so they would each just get one plan and child cursor.

We plan to monitor more closely for any SQL statements that do have many child cursors, however we need to make sure that even if that does happen it doesn’t break the system again.  One thing that seemed promising is the _cursor_obsolete_threshold parameter.  We had already reduced this parameter down to 1024 from it’s default of 8192 based on Mike Deitrich’s blog post but with this incident were considering reducing it further.  I think it’s wise to be wary of messing too much with underscore parameters but per Doc ID 2431353.1 Oracle Support say “the … parameter can be adjusted case-to-case basis should there be a problem”. For sure we had a significant problem with the setting at 1024 so plan to reduce further to 512.

We involved super consultant Stefan Koehler to review our findings and action plan, he was broadly in agreement, even recommending further reduction of the parameter value to 256.  However something puzzling me which I asked him was “What actually happens if the number of child cursors hit the value specified by this parameter”.  His answer “Well what happens is this … if your parent cursor got more than _cursor_obsolete_threshold child cursors it invalidates the parent (and in consequence all childs) and it starts from 0 again”

I was skeptical, my expectation was that Oracle would just invalidate the oldest unused child cursor and then re-use that child number.  Another thing puzzling me was happens if some of the child cursors were still held open?  Time to test this out for myself…
First let me demonstrate how I can get 4 child cursors for a given SQL Statement using different values of optimizer_index_cost_adj as a quick hack.
SQL> alter system flush shared_pool;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> select prev_sql_id from v$session where sid=sys_context('userenv','sid');
PREV_SQL_ID
9tz4qu4rj9rdp

SQL> alter session set optimizer_index_cost_adj=1;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=2;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=3;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> select sql_id, child_number, executions from v$sql where sql_id = '9tz4qu4rj9rdp';
SQL_ID            CHILD_NUMBER   EXECUTIONS
9tz4qu4rj9rdp                0            1
9tz4qu4rj9rdp                1            1
9tz4qu4rj9rdp                2            1
9tz4qu4rj9rdp                3            1

Let me reduce _cursor_obsolete_threshold at session level and re-run the test.

SQL> alter system flush shared_pool;
SQL> alter session set "_cursor_obsolete_threshold"=2;
SQL> alter session set optimizer_index_cost_adj=100;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=1;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=2;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=3;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> select sql_id, child_number, executions from v$sql where sql_id = '9tz4qu4rj9rdp';
SQL_ID            CHILD_NUMBER   EXECUTIONS
9tz4qu4rj9rdp                0            1
9tz4qu4rj9rdp                1            1
9tz4qu4rj9rdp                0            1
9tz4qu4rj9rdp                1            1

Whoah…. each combination of sql_id and child number has two entries (not what I was expecting to see). To get a more full picture we need to look at a couple of additional fields, namely ‘address’ and ‘is_obsolete’.

SQL> select sql_id,  address, child_number, is_obsolete from v$sql where sql_id = '9tz4qu4rj9rdp';
SQL_ID          ADDRESS              CHILD_NUMBER IS_OBSOLETE
9tz4qu4rj9rdp   00000000610AB500                0 Y
9tz4qu4rj9rdp   00000000610AB500                1 Y
9tz4qu4rj9rdp   0000000073DDE788                0 N
9tz4qu4rj9rdp   0000000073DDE788                1 N

Although we tend to use sql_id as our handle for the parent cursor, Oracle actually uses the ‘Address’ field, and when the _cursor_obsolete_threshold value is exceeded, Oracle allocates a new parent cursor with a new ‘Address’.  This explains how Oracle copes when old child cursors are held open, they still stay in the shared pool, keeping their address, but are marked as obsolete, able to be aged out when they are no longer in use.

The other lessons here, firstly that Stefan knows his stuff, but also whenever someone tells you something, don’t just take it on trust, it’s normally easy to validate for yourself, and you may learn something about how Oracle works along the way

OGB Appreciation Day: Implicit Cursors

Recently I’ve been working with SQL Server and while it’s not all bad, sometimes it does helps to highlight some of the neat features available in Oracle.  One of these is IMPLICIT cursors, which I shall demonstrate.

First I’ll show how to populate some data in a table and then loop over it in TSQL (SQL Server’s equivalent of PL/SQL):

1> CREATE TABLE demo (col1 TINYINT, col2 TINYINT, col3 TINYINT);
2> GO

1> INSERT INTO demo (col1, col2, col3)
2>           VALUES (11,   12,   13),
3>                  (21,   22,   23),
4>                  (31,   32,   33);
5> GO
(3 rows affected)

1>  DECLARE @col1 tinyint, @col2 tinyint, @col3 tinyint;
2>  DECLARE explicit_cursor CURSOR LOCAL FAST_FORWARD FOR
3>     SELECT col1, col2, col3 FROM dbo.demo;
4>
5>  OPEN explicit_cursor;
6>  FETCH NEXT FROM explicit_cursor INTO @col1, @col2, @col3;
7>
8>  WHILE @@FETCH_STATUS = 0
9>  BEGIN
10>    PRINT CONCAT(@col1, ':', @col2, ':', @col3);
11>    FETCH NEXT FROM explicit_cursor INTO @col1, @col2, @col3;
12> END
13>
14> CLOSE explicit_cursor;
15> DEALLOCATE explicit_cursor;
16>
17> GO
11:12:13
21:22:23
31:32:33

By the way, note the neat way it’s possible to insert 3 records with a single INSERT statement. I didn’t say there weren’t some things that SQL Server does a little better 🙂

Next check out the equivalent SQL statements and PL/SQL code in the Oracle Database. Note my Oracle demos are running on an Autonomous Transaction Processing Database in Oracle Cloud although should work in all versions including Oracle XE, the free to use database.

SQL> CREATE TABLE demo (col1 NUMBER, col2 NUMBER, col3 NUMBER);
Table DEMO created.

SQL> INSERT INTO demo (col1, col2, col3) VALUES (11, 12, 13);
1 row inserted.

SQL> INSERT INTO demo (col1, col2, col3) VALUES (21, 22, 23);
1 row inserted.

SQL> INSERT INTO demo (col1, col2, col3) VALUES (31, 32, 33);
1 row inserted.

SQL> COMMIT;
Commit complete.

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED;
SQL>
SQL> DECLARE
  2      CURSOR explicit_cursor IS
  3         SELECT col1, col2, col3 FROM demo;
  4      explicit_record explicit_cursor%ROWTYPE;
  5  BEGIN
  6      OPEN explicit_cursor;
  7      LOOP
  8          FETCH explicit_cursor INTO explicit_record;
  9          EXIT WHEN explicit_cursor%NOTFOUND;
 10          sys.dbms_output.put_line(explicit_record.col1 || ':' ||
 11                                   explicit_record.col2 || ':' ||
 12                                   explicit_record.col3          );
 13      END LOOP;
 14      CLOSE explicit_cursor;
 15  END;
 16  /

11:12:13
21:22:23
31:32:33

PL/SQL procedure successfully completed.

Already I prefer a few things about the Oracle solution.  The ability to use a cursor %ROWTYPE rather than having to define and use variables for individual columns, the fact there is only one fetch command required and the use of the %NOTFOUND cursor attribute rather than the somewhat arbitrary @@FETCHSTATUS == 0 check.

However Oracle offers an even better method, namely an implicit cursor.

SQL> BEGIN
  2      FOR implicit_record IN (SELECT col1, col2, col3 FROM demo)
  3      LOOP
  4          sys.dbms_output.put_line(implicit_record.col1 || ':' ||  
  5                                   implicit_record.col2 || ':' ||  
  6                                   implicit_record.col3          );
  7      END LOOP;
  8  END;
  9  /

11:12:13
21:22:23
31:32:33

PL/SQL procedure successfully completed

A few things to note.  We’re down from 15 to 8 lines of code which makes this easier to write, and just as importantly with less chance of bugs.  No need to worry about defining rowtypes, or opening or closing cursors, Oracle just does the right thing under the covers including tidying up in case exceptions are thrown.

 

 

 

 

ORDS Under Siege: Introduction

I’ve been playing around with researching ORDS over the summer particularly trying to optimize performance on Tomcat. Trying something a little different I’ve created a Vagrant box that should allow anybody interested to verify my findings, find mistakes I’ve made or identify performance optimizations I’ve missed.

If you’re new to Vagrant, Tim Hall provides a good introduction for the Oracle DBA.

You can clone or download the Vagrant box from my github page hopefully the instructions should be clear, you need to download Oracle 18cXE and ORDS releases and put into the software directory.  I’ve allocated 6GB RAM and 4 CPUs to the virtual machine, you may need to adjust these values depending on your test machine resources.  Doing “vagrant up” should automatically configure the database, and configure ORDS running in Tomcat and with some reverse proxies.  It will also generate a self-signed certificate and configure the SSL handling in both Tomcat and the reverse proxies.  Most of the database and ORDS configuration scripts were taken from the Oracle Vagrant boxes or Tim’s Vagrant boxes.

The bench-marking tool I am using is Siege.  There are many alternatives available but I chose Siege for a few reasons.  Firstly it is Free and Open Source software.  Secondly it is easy to configure, simply populate a file, urls.txt, with the URLs to hit and then run the executable with suitable parameters.  Finally it is lightweight, being written in C, whereas many other similar tools are written in Java, as I am running the bench-marking tool on the same virtual machine that hosts the software components I’m trying to measure this is important.

Once the vagrant machine is up, you can connect to it via “vagrant ssh” and then type “ords-demo” to run the entire test-suite. I’ll go through the individual tests in the following blog posts and share my findings.

 

 

 

 

Adding a little TLS complexity to authentication_ldap_simple

In a previous post I showed that by default when authentication_ldap_simple communicates with a Windows Domain Controller (or any other LDAP service), then the password is transmitted unencrypted during authentication.

This time I’ll demonstrate how to close this loophole.  A pre-requisite is that the Domain Controller needs to be configured to accept secure connections. This is done by installing a certificate, the process is well documented elsewhere so I won’t repeat it here.

There are two different ways to configure secure communication.  The first method is to set authentication_ldap_simple_server_port to 636.

As of MySQL 8.0.14, if the LDAP port number is configured as 636 or 3269, the plugin uses LDAPS (LDAP over SSL) instead of LDAP. (LDAPS differs from startTLS.)

The second method is to set authentication_ldap_simple_tls to ON.

For simple LDAP authentication, whether connections by the plugin to the LDAP server are secure. If this variable is enabled, the plugin uses TLS to connect securely to the LDAP server.

In both cases we have to set authentication_ldap_simple_ca_path to point to the certificate authority file used when securing the domain controller.  (Pro-tip ensure the both the file attributes of this certificate and of the directory it sits in are such that the mysql process is able to access it, you won’t believe how long I wasted due to this).

Of the two methods, I have been informed that the TLS method is optimal so that is what I will demonstrate.  Note I have found that it’s better to load the plugin and set the variables in the mysql configuration file (my.cnf) and restart the service rather than setting them dynamically (it seems the otherwise the values do not correctly propagate to the appropriate processes due to LDAP connection pooling)  so that’s what I’ll show you.

[root@lnx-mysql8 ~]# tail -5 /etc/my.cnf
plugin-load-add=authentication_ldap_simple.so
authentication_ldap_simple_server_host='win-dc.windows.domain'
authentication_ldap_simple_group_search_attr=
authentication_ldap_simple_tls=ON
authentication_ldap_simple_ca_path='/etc/certs/win-dc.crt'
[root@lnx-mysql8 ~]# 

Let’s have a look at the network traffic on port 389 (LDAP) on MySQL service start.  Observe that, after some initial negotiation regards the certificate, all traffic is encrypted.

[root@lnx-mysql8 ~]# tcpflow -C port 389
tcpflow: listening on enp0s3
0w1.3.6.1.4.1.1466.20037
0(x
1.3.6.1.4.1.1466.20037
]mK?$(UI0,($
kjih98762.*&=5/+'#g@?>3210EDCB1-)%</A C 0B10UXX10UDefault City10U 0B)JJ:7 }$kwIuI_0"%21-dc.windows.domain00 F#qI925rgCbG?{ O{R?_zm+(cx7Ju&+C0A0U0U%0 d&,(V5zVmBj2ZhZw%m@VX}5A2nDf!a)n[wn:~JTm:!0`jL4yMv"8'LH+BHQ K#F _3f]t'u)5B.^/fEKIb.Tj2?03`g5.0RJaF'pH&i=QSa[m3&j~~10&=kv)S%oiH3RvK'wE-tbJ8Tl#:B9tw;MB!FT]7AA7Gn>adwCR#I:x*#IIk8.g62~hd|N_L%OIBC#V|@)o+O_Afo-At~XHt`<fV'r]"u'}GF@<h}- eBA]vEJAu|=-t7ATiPhz(stn`[9U[_s@" (O,tD/'1RT{g6}I3% 0w1.3.6.1.4.1.1466.20037 0(x 1.3.6.1.4.1.1466.20037 )=?s@9fj& *x"80,($ kjih98762.*&=5/+'#g@?>3210EDCB1-)%</A
C

To save you having to refer to previous post here is how to create the AD authenticated user using the LDAP DN (Distinguished Name):

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

I am now able to connect using my Windows username and password:

[root@lnx-mysql8 ~]# mysql                                  \
>                          --host=lnx-mysql8.windows.domain \
>                          --enable-cleartext-plugin        \
>                          --user=patrick                   \
>                          --password=Password123
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 16
Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2019, 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>

And let’s check the network traffic to domain controller during the authentication

[root@lnx-mysql8 ~]# tcpflow -C port 389
tcpflow: listening on enp0s3
Whl+E]X=n0ucbDOBt'
.rvY* CKWLS+

No more password in plain sight. It’s still in my bash history, but that’s something I can easily resolve by not passing it as a command line argument 🙂

(MySQL) Logged and Loaded: authentication_ldap_simple_log_status or authentication_ldap_sasl_log_status not effective?

A quick one in case anyone else hits the same problem as I encountered.  The documentation for authentication_ldap_sasl_log_status and  authentication_ldap_simple_log_status states these variables can be set to a value between 1 and 5 to control the types of messages logged.

If you set them and still find nothing is logged then sett log_error_verbosity to it’s maximum value of 3 and you should find the messages are output to the error log as expected.

Thats all!

Let the wrong one in! MySQL AD Authentication with authentication_ldap_simple

I mentioned in my previous blog entry that had I encountered an issue with this plugin, and I think now that it has been fixed in the latest MySQL versions (released on 2019-04-25) it’s reasonable to share my findings.

The following tests are with MySQL Version 8.0.13. I start by installing the plugin, pointing it at my Windows Domain Controller and creating a user associated with the DN of 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 we test that everything works OK by trying to log in with my correct Windows password.

[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
...
mysql>

Also providing the wrong password denies access. Everything is as expected so far.

[patrick@WIN-CLIENT] C:\> mysql                                  `
>>                              --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --password=WrongPassword         `
>>                              --enable-cleartext-plugin
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'patrick'@'WIN-CLIENT.windows.domain'...

However what would you expect to happen if I try to log in to this account without providing a password?  Let’s see…

[patrick@WIN-CLIENT] C:\> mysql                                  `
>>                              --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --enable-cleartext-plugin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.13-commercial MySQL Enterprise Server - Commercial
...
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| patrick@%      |
+----------------+
1 row in set (0.00 sec)

Huh? It lets me in!  After double checking everything I raised an SR for this and a bug was created and fixed in 8.0.16. Apparently the 5.7 branch was also affected, and this fix is also in 5.7.26.

Bug #29637712: The authentication_ldap_simple plugin could enforce authentication incorrectly.

Let’s test in 8.0.16. Note one difference here is that I have to set variable authentication_ldap_simple_group_search_attr to ” to disable AD group checking. This behaviour, related to proxy users,  seems to have been introduced in 8.0.14, but on my lab setup this step breaks authentication completely so I disable it.

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

mysql> SET GLOBAL authentication_ldap_simple_bind_base_dn='DC=WINDOWS,DC=DOMAIN';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL authentication_ldap_simple_group_search_attr='';
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.00 sec)

I won’t repeat the tests for correct and incorrect password handling, they still behave as before, but I will show you behaviour without specifying a password.

[patrick@WIN-CLIENT] C:\> mysql                                  `
>>                               --host=lnx-mysql8.windows.domain `
>>                               --user=patrick                   `
>>                               --enable-cleartext-plugin
ERROR 1045 (28000): Access denied for user 'patrick'@'WIN-CLIENT.windows.domain' (using password: YES)
[patrick@WIN-CLIENT] C:\>

I’m not sure if there is anyone using authentication_ldap_simple due to it sending passwords unencrypted between database and domain controller, but if there is, I’d suggest checking whether you are susceptible to this issue and if so applying latest patchset ASAP.

I’m also not sure if this is specific to LDAP authentication with Active Directory or other Directory Services are affected.  I also wonder whether authentication_ldap_sasl is affected, but I don’t have configuration to check that out.

mysql_clear_password & authentication_ldap_simple password encryption

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.

 

Problems with Binding Arrays

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.

Resource manager session_pga_limit has it’s own limits

Recently we hit an issue with a complex SQL statement (formatted was 44,000 lines, maybe subject of a separate blog post), causing the CBO to struggle consuming large amounts of PGA memory, and the host to start swapping memory and impacting other database users.

The pga_aggregate_limit parameter did not appear to be kicking in (maybe because this was happening during parse phase), so while looking for a proper solution we considered other ways to limit the effect of this problem SQL.

As we are on release 12.2 one thing we tried was a (relatively new) feature of resource manager, session_pga_limit. This should limit the PGA any one session can consume (as opposed to pga_aggregate_limit which is instance wide), however new features can be a little temperamental, especially in the first few versions after they have been introduced.

After a bit of trial and error we determined that setting it to any value greater than 4G (4096 MB) causes the feature not to kick in.

The following is my testcase on a 12.2.0.1.180717 PDB. I could not reproduce this behavior on 18c (non-multitenant) implying this limitation (bug?) has likely been fixed.

First we create a resource manager plan, consumer groups and directives, and configure the instance to use this plan.

SQL> BEGIN
  2    sys.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3
  4    sys.DBMS_RESOURCE_MANAGER.create_pending_area();
  5
  6    sys.DBMS_RESOURCE_MANAGER.create_plan(
  7      plan    => 'PGA_PLAN',
  8      comment => 'Plan to demonstrate behaviour with session_pga_limit >= 4096');
  9
 10    sys.DBMS_RESOURCE_MANAGER.create_consumer_group(
 11      consumer_group => 'PGA_LIMIT_4095_GROUP',
 12      comment        => '4095 MB PGA Limit');
 13
 14    sys.DBMS_RESOURCE_MANAGER.create_consumer_group(
 15      consumer_group => 'PGA_LIMIT_4096_GROUP',
 16      comment        => '4096 MB PGA Limit');
 17
 18    sys.DBMS_RESOURCE_MANAGER.create_plan_directive (
 19      plan              => 'PGA_PLAN',
 20      group_or_subplan  => 'PGA_LIMIT_4095_GROUP',
 21      session_pga_limit => 4095);
 22
 23    sys.DBMS_RESOURCE_MANAGER.create_plan_directive (
 24      plan              => 'PGA_PLAN',
 25      group_or_subplan  => 'PGA_LIMIT_4096_GROUP',
 26      session_pga_limit => 4096);
 27
 28    sys.DBMS_RESOURCE_MANAGER.create_plan_directive (
 29      plan              => 'PGA_PLAN',
 30      group_or_subplan  => 'OTHER_GROUPS');
 31
 32    sys.DBMS_RESOURCE_MANAGER.validate_pending_area;
 33
 34    sys.DBMS_RESOURCE_MANAGER.submit_pending_area();
 35  END;
 36  /

PL/SQL procedure successfully completed.

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = PGA_PLAN;

System altered.

Then we configure my test user to use the consumer group limiting it to 4095 MB PGA.

SQL> BEGIN
  2    sys.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    sys.DBMS_RESOURCE_MANAGER.create_pending_area();
  4
  5    sys.DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (
  6      attribute      => DBMS_RESOURCE_MANAGER.oracle_user,
  7      value          => 'TEST_PGA_USER',
  8      consumer_group => 'PGA_LIMIT_4095_GROUP');
  9
 10    sys.DBMS_RESOURCE_MANAGER.validate_pending_area;
 11    sys.DBMS_RESOURCE_MANAGER.submit_pending_area();
 12  END;
 13  /

We connect as the test user, and executing some stupid PL/SQL that sits in a tight loop eating PGA. Observe that the resource manager directive is obeyed when the PGA hits 4095 MB.

SQL>   declare
  2          type vc_tt is table of VARCHAR2(32767);
  3          vc_t vc_tt := vc_tt() ;
  4      begin
  5          while TRUE
  6          loop
  7               vc_t.extend();
  8          end loop;
  9      end;
 10   /
  declare
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-10260: PGA limit (4095 MB) exceeded - process terminated
ORA-06512: at line 7

Let’s remap my test user to the consumer group limited to 4096 MB.

SQL> BEGIN
  2    sys.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    sys.DBMS_RESOURCE_MANAGER.create_pending_area();
  4
  5    sys.DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (
  6      attribute      => DBMS_RESOURCE_MANAGER.oracle_user,
  7      value          => 'TEST_PGA_USER',
  8      consumer_group => 'PGA_LIMIT_4096_GROUP');
  9
 10    sys.DBMS_RESOURCE_MANAGER.validate_pending_area;
 11    sys.DBMS_RESOURCE_MANAGER.submit_pending_area();
 12  END;
 13  /

PL/SQL procedure successfully completed.

My test program runs unchecked:

SQL> declare
  2      type vc_tt is table of VARCHAR2(32767);
  3      vc_t vc_tt := vc_tt() ;
  4  begin
  5      while TRUE
  6      loop
  7           vc_t.extend();
  8      end loop;
  9  end;
 10  /

Checking PGA allocation from another session we can see it’s up to 8546MB, way past the 4096MB it should be limited to.

SQL>  SELECT
  2      spid,
  3      resource_consumer_group,
  4      round(pga_used_mem / 1024 / 1024) pga_used_mb
  5  FROM
  6      v$session s,
  7      v$process p
  8  WHERE
  9      s.username LIKE 'TEST_PGA_USER'
 10      AND p.addr = s.paddr;

SPID                     RESOURCE_CONSUMER_GROUP          PGA_USED_MB
------------------------ -------------------------------- -----------
234513                   PGA_LIMIT_4096_GROUP                    8546

Note if you’re testing this yourself, I suggest to be careful, don’t do this on a production instance and be prepared to kill the run-away session forcefully.