ORDS 21.x make sure you have the latest

ORDS version 21 was released in May, we tested and rolled it out in June. But once live, a problem popped up: numerous executions of this statement,
SELECT COUNT(1) FROM SYS.ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'APEX_RELEASE' AND TABLE_NAME = 'APEX_RELEASE';
which appears to be run whenever you initialize a connection through the ORDS connection pools. It is a not a nice query. This is a typical exec plan:
atest> SELECT COUNT(1) FROM SYS.ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'APEX_RELEASE' AND TABLE_NAME = 'APEX_RELEASE';
COUNT(1)
---------------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 4162468211
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 198 | | 4488 (7)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 198 | | | | | |
| 2 | VIEW | ALL_SYNONYMS | 20002 | 3867K| | 4488 (7)| 00:00:01 | | |
| 3 | SORT UNIQUE | | 20002 | 13M| 14M| 4488 (7)| 00:00:01 | | |
| 4 | UNION-ALL | | | | | | | | |
| 5 | PARTITION LIST ALL | | 1 | 369 | | 23 (100)| 00:00:01 | 1 | 2 |
|* 6 | EXTENDED DATA LINK FULL | INT$DBA_SYNONYMS | 1 | 369 | | 23 (100)| 00:00:01 | | |
|* 7 | VIEW | _ALL_SYNONYMS_TREE | 20001 | 6699K| | 1545 (100)| 00:00:01 | | |
|* 8 | CONNECT BY WITHOUT FILTERING | | | | | | | | |
|* 9 | HASH JOIN RIGHT SEMI | | 1 | 475 | | 112 (100)| 00:00:01 | | |
| 10 | VIEW | VW_SQ_1 | 1190 | 153K| | 87 (100)| 00:00:01 | | |
|* 11 | FILTER | | | | | | | | |
| 12 | PARTITION LIST ALL | | 20000 | 5664K| | 87 (100)| 00:00:01 | 1 | 2 |
| 13 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_AO | 20000 | 5664K| | 87 (100)| 00:00:01 | | |
|* 14 | FILTER | | | | | | | | |
| 15 | NESTED LOOPS | | 1 | 107 | | 6 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 95 | | 5 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS | | 1 | 71 | | 4 (0)| 00:00:01 | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 18 | | 1 (0)| 00:00:01 | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | | 0 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 53 | | 3 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | I_OBJ5 | 1 | | | 2 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN | I_USER2 | 1 | 24 | | 1 (0)| 00:00:01 | | |
|* 23 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 12 | | 1 (0)| 00:00:01 | | |
|* 24 | FIXED TABLE FULL | X$KZSRO | 1 | 6 | | 0 (0)| 00:00:01 | | |
|* 25 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 | | |
|* 26 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 | | |
|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 | | |
|* 28 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 | | |
| 29 | NESTED LOOPS SEMI | | 1 | 29 | | 2 (0)| 00:00:01 | | |
|* 30 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 | | |
|* 31 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | | 1 (0)| 00:00:01 | | |
| 32 | PARTITION LIST ALL | | 20000 | 6699K| | 21 (100)| 00:00:01 | 1 | 2 |
| 33 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_SYN | 20000 | 6699K| | 21 (100)| 00:00:01 | | |
| 34 | PARTITION LIST ALL | | 20000 | 6699K| | 21 (100)| 00:00:01 | 1 | 2 |
| 35 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_SYN | 20000 | 6699K| | 21 (100)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
You can see the problem: it is a UNION ALL query. The first branch (operations 5 and 6) is simple and low cost. It is what you get if you query dba_synonyms instead of all_synonyms:
atest> SELECT COUNT(1) FROM SYS.dba_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'APEX_RELEASE' AND TABLE_NAME = 'APEX_RELEASE';
COUNT(1)
---------------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1145150501

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 198 | 23 (100)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 198 | | | | |
| 2 | PARTITION LIST ALL | | 1 | 198 | 23 (100)| 00:00:01 | 1 | 2 |
|* 3 | EXTENDED DATA LINK FULL| INT$DBA_SYNONYMS | 1 | 198 | 23 (100)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------
but the second branch (operations 7 through 355) is ghastly. It is a query against the SYS._ALL_SYNONYMS_TREE view. That view is a hierarchical query, meaning that it has to be materialized and cannot be merged. It must be run to completion, and against a database with zillions of synonyms, it is slow. Possibly several seconds. Why is it there? To account for the possibility that you might have synonyms pointing to synonyms, which ORDS really doesn’t need to know about. There is no reason for ORDS to be doing this.
We were fortunate: in the databases where I noticed the issue, usage was light and the query was usually running in under a second but it was still hammering the system.
The solution, if you haven’t done so already, is to upgrade your ORDS pronto. Oracle rushed out a quick fix last month, which is ORDS 21.1.3 and this weekend released the ORDS 21.2.0 which should be the real solution.