SQL Dev Extension: some queries may need SQL tuning

[rvo-cs]

On some databases, queries run by the plscope-utils for SQL Developer extension may be slow.

E.g., the SQL query run by the Identifiers tab of the package viewer may be slow on some 11.2 databases, or 12.2 non-CDB databases, even though dictionary statistics have been gathered.

Cause: in the fids subquery, view predicate push-down into the DBA_IDENTIFIERS view may not work well, causing poor performance:

   fids as (
      select 'NO' as is_sql_stmt,
             pls_ids.owner,
             pls_ids.name,
             pls_ids.signature,
             [...]
             nvl2(sig.signature, 'PUBLIC', cast(null as varchar2(7 char))) as procedure_scope,
             pls_ids.origin_con_id
        from pls_ids
        left join sys.Dba_identifiers sig
          on sig.owner = pls_ids.owner
         and sig.object_type in ('PACKAGE', 'TYPE')
         and sig.object_name = pls_ids.object_name
         and sig.usage = 'DECLARATION'
         and sig.signature = pls_ids.signature
      union all
      select 'YES' as is_sql_stmt,
             owner,
             name,
             signature,
             [...]
             origin_con_id
        from sql_ids
   ),

(Excerpted from the query for Oracle 12.2 and above; the same query for Oracle 11g does not have the UNION ALL [...] from sql_ids part.)

Solution: tuning the query may improve performance. I have found that disabling view predicate push-down in the above subquery produced acceptable results on my test databases. The global hint for doing so was:

• On Oracle 11.2: NO_PUSH_PRED(@"SEL$573CF33E" "SIG"@"SEL$1")
• On Oracle 12.2 non-CDB: NO_PUSH_PRED(@"SEL$79842869" "SIG"@"SEL$1")

(Your mileage may vary. Make sure you double-check the target query block id.)

The same query on my Oracle 19.9 test PDB did not require any particular tuning, but since the plan operation as regards the DBA_IDENTIFIERS row source was EXTENDED DATA LINK FULL, I gather that no kind of predicate push-down whatsoever was involved.

Of course the above hint must be injected into the query plan using one of the usual plan-fixing mechanisms, e.g. SQL Plan Baselines or SQL Profiles. Creating an SQL Plan Baseline worked well on 11.2, but failed on 12.2 non-CDB: the SPB was successfully created, yet the optimizer would never use it. Tracing the SQL compilation revealed that the plan was not found to be reproducible, due to internal errors:

SPM: fixed planId's of plan baseline are: 3650841288
SPM: using qksan to reproduce, cost and select accepted plan, sig = 9702904309955310184 cntRepro = 0
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3650841288
[...]
SPM: planId in plan baseline = 3650841288, planId of reproduced plan = 1570282812
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
[...]
SPM: generated non-matching plan:
[...]
SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, planId = 3650841288
[...]
SPM: OCIcode = 32034, OCImesg = ORA-32034: unsupported use of WITH clause

The above error may be due to an unfixed bug in my test 12.2 database, which is probably out-of-date as far as database patch bundles are concerned.

Creating a SQL Profile worked well, however.