Performance slowness due to Spatial component

 We had a complaint from application team that their database is running slow after the quarterly patching. We had patched many databases and only one database is complaining on slowness. The environment is Exacc and the database is a container (running version 19.16) which has multiple PDBs. So patching couldn't be a reason is the initial thought. So we stepped into investigate what is causing the slowness. 

As a first step, we pulled an AWR report for the time frame where they did some DB operations. 

We can see from above that most of the waits are related to Cluster and topping them is gc cr multi block mixed, gc cr block 2-way and gc buffer busy acquire. 

So this means some query is running very frequently accessing 1 or 2 table blocks continuously. We need to figure out which query is running, accessing what table/index and how frequent and what's the elapsed time of the query. These information will get us to move forward. 

Since the waits are related to Cluster, let's see SQL ordered by Cluster Wait Time (Global) section under SQL Statistics

We see 2 queries that contribute to the most cluster waits. Both these are related to spatial component as the queries are as below and they had ~ 53k executions in the report

SELECT /* chk_journal_reg */ rowid FROM "MDSYS"."SDO_TXN_JOURNAL_REG" WHERE sid = :1 AND sdo_txn_idx_id = :2 AND indexptniden = :3 AND operation = :4 AND rid = :5

SELECT /* sel_dist_upd */ ROWID, ....., ins_no, del_no FROM "MDSYS"."SDO_DIST_METADATA_TABLE" WHERE sdo_index_owner = :own AND sdo_index_name = :nam AND sdo_partition_name = :ptn FOR UPDATE

Ok, these 2 queries look simple and not complicated, so what's causing this buffer busy issues? Investigation went in following direction

Check size of tables: They are too small. 

Check the execution plan of the first code with high cluster waits gave us the clue. Though we have primary key defined on all five columns (SID, SDO_TXN_IDX_ID, INDEXPTNIDEN, OPERATION, RID), the query took the path of INDEX STORAGE FAST FULL SCAN which would traverse through all the blocks unnecessarily. The query took around 4 second per execution. 

I hint the query to use the index already available (/*+ index(SDO_TXN_JOURNAL_REG SYS_C0014253) */) - this resulted in a much better plan and execution time. Execution time reduced to milliseconds. Remember this query executed around 53k times which would make a big difference in application performance. While checking for any plan change, I found that the index path was used until the patch was applied and the plan flipped

SQL> select distinct sql_id, plan_hash_value, id, operation, options
  2* from dba_hist_sql_plan where sql_id='fw4q2s9j3bckt' order by plan_hash_value, id;

          SQL_ID    PLAN_HASH_VALUE    ID           OPERATION                   OPTIONS
________________ __________________ _____ ___________________ _________________________
fw4q2s9j3bckt            1765869626     0 SELECT STATEMENT
fw4q2s9j3bckt            1765869626     1 INDEX               STORAGE FAST FULL SCAN
fw4q2s9j3bckt            2134037127     0 SELECT STATEMENT
fw4q2s9j3bckt            2134037127     1 INDEX               UNIQUE SCAN

SQL> 

So, we decided to create sql profile so that the plan stick to plan_hash_value 2134037127 using note Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script (Doc ID 1955195.1)

In the mean time, Oracle support note (After Applying the 19.16 DBRU, Scheduler Jobs on Spatial Tables Take a Long Time to Execute (Doc ID 2907800.1)) mentioned a bug related to this same table and query. 

Reason for slowness is 

Running a MV refresh using the DBMS_SCHEDULER package causes spatial to use the SDO_TXN_JOURNAL_REG transaction journal table, instead of the SDO_TXN_JOURNAL_GTT transaction journal table.

So, now the solution is to revert the sql profile created and apply spatial bundle bug fix Patch 34725493 or to apply RU 19.17

Since we already had applied another one off patch for a previous issue with Spatial, we couldn't apply this bug fix patch as it was conflicting with the one off patch. Raised an SR with Oracle to get the merge patch for both the conflicting patches but Oracle couldn't provide any commitment on when the merge patch will be ready.

As we need to get the production database back to normal as soon as we can, we had to roll back the entire 19.16 RU to bring it back to pre patch state (RU 19.15) and then the application verified all the jobs are running without issues. We could also not see any cluster related wait post the patch rollback. We rolled back as 19.17 is yet to be tested in our lower environments before we promote to production.

Interestingly, Oracle maintains a note on all the patches that needs to be applied post RU patch apply under What Is The Latest Spatial Patch Bundle for 12c and Higher Databases? (Doc ID 2514624.1) So every time we apply RU to the database, we need to refer this note and apply the patch that is mentioned for the related RU as well. 

This investigation is not only a trouble shooting experience but also a knowledge gathering experience with Oracle Spatial component regarding the bugs related to Spatial and separate Spatial patch bundle, etc.,. 

Happy Trouble shooting and Patching...!!!

Oracle Database and Anti Virus Softwares

 If you do a search for antivirus related issues in Oracle support, you will hit many notes talking about AVs causing issues with the Oracle database and this is not specific to any AV but in general to all the AV software. 

Taking a look at Oracle support note How To Configure Anti-Virus On Oracle Database Server (Doc ID 782354.1), Oracle mentions the below

we recommend the following files to be excluded from online anti-virus scanning.

• Oracle datafiles
• Control files
• Redo-log files
• Archived redo-log files if database is in archive log mode
• Files with extension '.ora'
• Password file
• File in ADR directory
• Oracle Software directories (including Oracle_HOME and Oracle base)
• /dev/shm on Linux platform

So, is that it? Are we good if we just exclude all the files mentioned above? 

I'll discuss regarding the 2 scenarios which caused outages to the database w.r.to AV software (I'm not mentioning the names of the AV software here)

Scenario 1: 

We had installed one of the leading AV software on all our DB servers. During the testing phase everything goes smooth. Few files were asked to be excluded as suggested by Oracle and finally agreed to deploy them on production servers after rigorous testing in the lower environments. 

Soon after the production deployment, we started having issues with ASM disk writes as the ASM disks were invisible to Oracle. The following error pops up frequently on busy and high load databases. 

ORA-00202: control file: '+DATA/ABC/cntrlabc.dbf'~ORA-15081: failed to submit an I/O operation to a disk

ORA-00312: online log 5 thread 1: '+LOG/ABC/ONLINELOG/group_5.261.981173379'~ORA-15081: failed to submit an I/O operation to a disk

So what's really happening here? 

We are using udev rules to define the ASM disks on all our environments (old school but still works effectively). Our assumption is the AV software locks or prevents other processes from reading the udev rules files or the ASM disk header exactly during Oracle tries read/write udev rules file or the ASM disk header. The exact cause is not known till now as the incident is not reproducible at will.

To get out of this situation, we need to reload the udev rules and things will get back to normal. This is no joke as the busy database might lose data or the operations will get stalled when the control file/data file is missing from where it has to be. 

 

We worked with Oracle support who couldn't figure out anything specific from the logs provided and also we worked closely with the AV software vendor close to 8 months to trap the error/incident by trying different tracing and testing same workload during same previous issue time, etc., but as I mentioned we couldn't trap the issue and the tracing can't be directly enabled on the production database server for long times due to enormous logs the tracing produces, performance impact, etc. 

In the mean time, multiple version updates with minor to major file handling updates w.r.to Oracle database were introduced to the AV software but none of them help prevent the issue from occurring infrequently all of a sudden which made the business people unhappy.  

Finally, business agreed to move to another AV vendor 😜

The horror story continues... 

Scenario 2: 

During the 8 months time where all the R&D was happening with the previous vendor, there comes another AV software vendor with all the super cool features that one system can have to get rid of all the virus (just like the complete dose of COVID vaccine and on top of that a booster dose too). 

So just like the previous one, the testing went with flying colors. No outages like disk not visible, etc. was seen and no performance slowness reported in the lower environments and also a pilot was done on one of low risk production database server as well. 

Finally, it was decided to take the AV software to production along with other changes we usually do such as OS and DB patching every quarter. The deployment went fine for all the production DB servers with no issues reported during the maintenance window and post maintenance testing window. All are happy, all went to celebrate and sleep peacefully. 

Next day, business began as usual.. Time passed slowly.. Application team noticed slight delay in batch processing for one or other jobs.. They still were monitoring thinking it's an one off case and waited for sometime.. Performance is now getting a huge hit on one of the very critical OLTP database and the entire database was slowing down.. we had multiple tickets bombarding and eventually a high priority ticket was assigned and members from different groups (Application team, OS admins, Infra admins, DBAs) jumped on to the bridge to check from their end.

Initial checks with DB showed almost all the queries were running slow. This is not an ideal situation where all the queries would get hit. So the issue seems to be something out of database. Checks on OS shows that run queue for the CPU is too high and the time spent running the kernel is taking high percentage of the CPU. This is definitely unusual. 

You can see the load average is near 400 and this system is a 72 core system which translates to a load of ~ 5.5 which is too high. Interestingly, unlike all other days the kernel processing is taking > 50% (ideally should be < 10%) is a hint saying something messed up at the OS/kernel. 

So the first thing we did is to check for the AV activity and stop that right there as it's the only alien on the system. OS admin disabled the AV software. Still the CPU usage didn't come down. So we guessed it to be some other thing that's causing the issue. Remember we did OS and Database patching as well. So the plan now is to rollback both the patches one after another to figure out the cause. 

Meanwhile, support from vendors such as Oracle, Redhat and SAP (since the application is SAP) were also involved via high priority tickets to each of them. Oracle and SAP clearly mention that the cause might be from OS as the kernel processing is unusually high. So first we rolled back the kernel to previous version and rebooted the entire system. The issue was still persistent. Database patch was then rolled back and still the CPU usage didn't reduce to normal levels. 

We then installed perf tool which is not a standard package that comes along with Linux as Redhat needs some finer details to pin point the cause. Take a look here to learn much about perf

With the OS team providing the required details to Redhat support (I have limited knowledge on what traces were provided but guess perf stat trace), Redhat support got back to us with this module event_collector_2_3_xxx which is a third party module got loaded to the kernel and causing the kernel to suck up all the CPU. This module is from the AV software. We have to uninstall completely of the AV software and take a clean reboot to get rid of the module from kernel. 

One thing to understand is that though we disabled the AV software as first step of our troubleshooting, the same AV software was identified as culprit causing the slowness issue after very long investigation. This seriously would have been avoided if we know how the AV would behave if we just disable it.

So, what's the solution for this? 

Oracle doesn't certify any AV software to work properly with Oracle databases. We can't leave our production databases/servers unprotected. So we can't totally get rid of anti virus softwares at all. In the same time, we can't risk unexpected production downtime as we saw in the above 2 cases of the multiple other probabilities that might occur. 

What we can try is other alternatives than relying upon AVs such as below

1. DB server should be restricted only for DB admin login, specifically only a senior DBA who does admin work (such as startup and shutdown using a sys account) and not the management work like adding/extending tablespaces kind of tasks. These other activities can be done remote using system or customized DBA account.  This makes sure very limited logins are available on the server directly unlike we have 1 common account (in most cases oracle) where all the members in team use to login. This poses a very serious security risk
2. Place all DB servers under a subnet with tight firewalls such as auto log off in x seconds/minutes, trace entire activity and audit frequently on what commands been run, etc. Only SA and DBA should have direct login accounts. 
3. OS login account should be coupled with AD and should use MFA to authenticate. The configuration seems complicated but this is one time configuration that needs the initial efforts to set up and we are done. 
4. To limit direct access to DB, use kerberos/radius authentication or Centrally Managed Users (available from version 18c) which implements coupling enterprise users with AD accounts and groups which would allow users with their enterprise logins. Again this provides double protection as once the member leaves the organization, his/her account is totally wiped out making the server/DB secure 
5. Use TCP.VALIDNODE_CHECKING = YES , TCP.EXCLUDED_NODES, TCP.INVITED_NODES sqlnet parameters to accept connections only from specific ip address or subnet making the application access to database super strong. 

These are the few things I can think of to secure the database/server from exploitation which would eliminate or minimize the requirement for an anti virus software. 

Let me know in comments of various other implementations that you come across to secure the database/servers in your organization so that we can help each other learn and secure our databases. 

References:

How To Configure Anti-Virus On Oracle Database Server (Doc ID 782354.1)

perf Examples

What is TCP_VALIDNODE_CHECKING and How to Use It (Doc ID 462933.1)

What the first five lines of Linux’s top command tell you

Happy Anti Virusing...!!!