Issue Description

The Atlassian Notifications plugin is used by Atlassian to send in-app notifications and messages in the format of pop-ups. In big instances (>2K concurrent users), this plugin doesn't scale properly and can be accountable for around 100K queries executed against the database in the period of an hour. 

Steps to Replicate

There are no known steps to replicate, as this plugin doesn't have a logic set of steps to be triggered.

Sample Queries

Queries being triggered by the plugin includes but are not necessarily limited to below examples:

Expensive Join

SELECT message."ID", message."CONTENT" FROM "AO_21F425_MESSAGE_AO" message JOIN "AO_21F425_MESSAGE_MAPPING_AO" message_mapping ON message."ID" = message_mapping."MESSAGE_ID"

Full Table Scan

SELECT * FROM ( SELECT "ID", "CONTENT" FROM "AO_21F425_MESSAGE_AO" ) WHERE ROWNUM <= 1

Example of Plugin Usage

The plugin is used to send messages in-app like the ones below:

Diagnose Steps & Workaround

It is not easy to spot problems caused by this plugin unless you have a good way of creating reports from your database, like Oracle AWR Reports. Usually, you will see spikes in Database Connections and probably Long Running Threads. If that's a symptom you have along with slowness in the application, look into your query execution history for the queries above. If you find them, you are them affected by this bug.

The workaround by now is to disable the plugin through a Dark Feature. To do so:

1. Access <Confluence-Base-URL>/admin/darkfeatures.action
2. Add the following Dark Feature to your list:

   whisper.disabled