SQLcl: MCP Server & SQL Prompt

Database: Oracle 23c FREE / OL8.7 in VirtualBox

• Time to run the install.sql script of utPLSQL 3.13 in SQL*Plus: approx 50 s
• Time to run the same script in SQLcl 23.1: approx 14 mins

Cause: looking at session activity in ASH, or using the JDBC profiler in VisualVM makes it abundantly clear that SQLcl's runtime is dominated by repeated executions of a single SQL query, with SQL id cgky22310n1hg.

Screenshot of a sample JDBC profile:

Remark: we've seen that query in at least one prior post; please see @User_2DKLA's comment from 18 Feb 2023 in this thread.

The problem with that query is that it can be very slow: 3 to 4 s / exec on my box, even after gathering dictionary stats and fixed object stats…

The text of that query (reformatted) looks as follows:

select object_type,
       owner,
       object_name,
       rank
  from (
          select object_type,
                 owner,
                 object_name,
                 0 rank
            from all_objects
           where object_name = :NAME
             and UPPER(owner) = nvl(:OWNER, :SCHEMA_CONTEXT)
             and object_type not in ('SYNONYM')
          union all
          select ao.object_type,
                 ao.owner,
                 ao.object_name,
                 decode(UPPER(syn.owner), nvl(:OWNER, :SCHEMA_CONTEXT), 10, 20)
            from all_objects ao, all_synonyms syn
           where ao.owner = syn.table_owner
             and ao.object_name = syn.table_name
             and syn.synonym_name = :NAME
             and (UPPER(syn.owner) = nvl(:OWNER, :SCHEMA_CONTEXT)
                    or UPPER(syn.owner) = nvl(:OWNER, 'PUBLIC'))
       )
 where ((:BODY_WHERE = 'Y') or (object_type not in ('PACKAGE BODY')))
   and rownum < 100
 order by rank,
       case object_type
          when 'TABLE' then
             0
          when 'VIEW' then
             1
          when 'INDEX' then
             2
          when 'PACKAGE' then
             3
          when 'PACKAGE BODY' then
             4
          else
             5
       end

There are several factors contributing to make it slow:

1. First and foremost, ALL_SYNONYMS is a complex and inherently slow view in modern Oracle DBs
2. Further, the conditions on UPPER(owner) or UPPER(syn.owner) guarantee that the owner columns will not be used as index access conditions (*)
3. Due to the OR logical operator in the compound condition (UPPER(syn.owner) = nvl(:OWNER, :SCHEMA_CONTEXT)
or UPPER(syn.owner) = nvl(:OWNER, 'PUBLIC')), OR-expansion might make the 2nd subquery in the UNION ALL even slower.

(*) Why was UPPER used here? I don't know, but I suspect that this query (or the Java method which calls it) may be used in a wide variety of cases, some of which may require to do a case-insensitive search. On the other hand, the 2 columns object_name and synonym_name in the same query are used “as is”, not as UPPER(object_name) or UPPER(synonym_name)…

In this case, the JDBC profile shows that this query (called by the oracle.dbtools.db.OracleUtil.resolveName method?) is run when processing the errors (SHOW ERRORS) after compiling PL/SQL objects.

In the case of the above-mentioned post, where the same SQL id (cgky22310n1hg) was possibly causing slowness, using @User_2DKLA's way of reproducing the background activity, the JDBC profile (using SQL Dev 21.4.3 here) is different, with the bottom of the stack hinting that SQL Dev is attempting to resolve names in a background Java thread for the sake of providing the user with richer information about names in the PL/SQL block…

In the context of the SHOW ERRORS command in SQLcl, it's a little harder to figure out why running such a query would be needed… And perhaps a more focused, simpler and faster query could be used in that context?

Regards,