In a previous post I said I would provide an alternative query for SQL id cgky22310n1hg, so here goes.
For those who have never heard of SQL id cgky22310n1hg, it's a query run automatically by SQLcl or SQL Developer in some circumstances—please see this thread, or this thread. I call it the “resolveName” query because it (seemingly) originates from the resolveName method in the oracle.dbtools.db.OracleUtil class.
Please note that I would never have given it much attention, if it weren't that it turned out to perform slowly on my test 23c FREE database.
Additionally, I must say upfront that this query turned out to be surprisingly difficult to tune, therefore I have taken shortcuts, so that the replacement query is not 100% equivalent, from a theoretic viewpoint, to the original query. I suppose it's acceptable from a practical viewpoint, but only developers of SQL Dev, who know all the use cases, might confirm or refute that.
Therefore, the point of this post is neither to claim that I have the best query—I haven't—nor that one may confidently use it as a permanent replacement… What I'm going to show here is a possible replacement query, and how we can test it in SQLcl / SQL Developer: we're going to use the SQL Translation Framework in order to use the replacement query automatically, in the current session only.
Original query: (SQL id cgky22310n1hg)
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;
Original query, reformatted:
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;
Tentative replacement query:
select object_type,
owner,
object_name,
rank
from ( select object_type,
owner,
object_name,
0 as rank
from all_objects
where object_name = :NAME
and UPPER(owner) = nvl(:OWNER, :SCHEMA_CONTEXT)
and object_type not in ('SYNONYM')
union all
select ( select ao.object_type
from all_objects ao
where ao.owner = syn.table_owner
and ao.object_name = syn.table_name
and ao.namespace = 1 -- added
and ao.subobject_name is null -- added
and ao.object_type <> 'DATABASE LINK' -- added
) as object_type,
syn.table_owner as owner,
syn.table_name as object_name,
case
when UPPER(syn.owner) = nvl(:OWNER, :SCHEMA_CONTEXT) then
10
else
20
end as rank
from all_synonyms syn
where syn.synonym_name = :NAME
and UPPER(syn.owner) in (nvl(:OWNER, :SCHEMA_CONTEXT), nvl(:OWNER, 'PUBLIC'))
)
where object_type is not null
and ((: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;
I have changed the 2nd subquery in the UNION ALL subquery (in the main FROM clause).
The biggest change is that I have used a scalar subquery in order to retrieve the object_type column from ALL_OBJECTS, as opposed to an inner join in the original query. The reason for doing this is that I could not get an acceptable plan otherwise—one which would gently get the synonyms from the ALL_SYNONYMS view, then join ALL_OBJECTS using a NESTED LOOPS operation, and push the relevant predicates (ao.owner = syn.table_owner and ao.object_name = syn.table_name) into the ALL_OBJECTS view when doing so. In other words, whatever I tried, JPPD into ALL_OBJECTS didn't happen as I had hoped it would… The scalar subquery is the closest thing to that, but of course we must ensure that this subquery will always return no more than a single row, and therefore the ao.subobject_name is null condition is added in its WHERE clause. The other 2 added conditions are:
ao.namespace = 1 : synonyms always point to objects in that namespaceao.object_type <> 'DATABASE LINK' : DB links don't have synonyms
Adding these 2 conditions may (or not) help by further simplifying the lookup in the ALL_OBJECTS view. In any case, I suppose they are harmless, even though (as said before) the replacement query is not 100% equivalent to the original SQL, from a logical viewpoint.
Remark: we've added the object_type is not null condition to the main WHERE clause, in order to filter out the row if there is no matching object in ALL_OBJECTS for a given (dangling) synonym.
1/ Setup
1.a/ Prerequisites
Oracle DB version ≥ 12c (tested in 12.2, 19.9, 23c FREE)
Setting up an SQL Translation profile requires the following privileges:
- EXECUTE on the SYS.DBMS_SQL_TRANSLATOR package (granted to PUBLIC in 23c FREE)
- The CREATE SQL TRANSLATION PROFILE system privilege
(You may have to ask your DBA for help here…)
Further, in order for an unprivileged user, e.g. SCOTT, to use an SQL Translation profile belonging to someone else, 2 additional requirements must be met:
- SCOTT must have been granted the USE privilege on the SQL Translation profile
- The creator of the SQL Translation profile must have the TRANSLATE SQL ON USER SCOTT object privilege, or the TRANSLATE ANY SQL system privilege
1.b/ Create the SQL Translation profile
declare
co_sql_trns_prof_name constant varchar2(20) := 'SQLDEV_HACK';
begin
dbms_sql_translator.create_profile(
profile_name => co_sql_trns_prof_name
);
dbms_sql_translator.set_attribute (
profile_name => co_sql_trns_prof_name,
attribute_name => dbms_sql_translator.attr_foreign_sql_syntax,
attribute_value => dbms_sql_translator.attr_value_false
);
dbms_sql_translator.set_attribute (
profile_name => co_sql_trns_prof_name,
attribute_name => dbms_sql_translator.attr_translate_new_sql,
attribute_value => dbms_sql_translator.attr_value_false
);
dbms_sql_translator.set_attribute (
profile_name => co_sql_trns_prof_name,
attribute_name => dbms_sql_translator.attr_trace_translation,
attribute_value => dbms_sql_translator.attr_value_false
);
end;
/
Here we have created an SQL Translation profile named "SQLDEV_HACK" (note: it's an object in the creator's schema), and set its attributes.
1.c/ Register the query translation
declare
co_sql_trns_prof_name constant varchar2(20) := 'SQLDEV_HACK';
l_orig_stmt clob := q'<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>';
l_repl_stmt clob := q'<select object_type,
owner,
object_name,
rank
from ( select object_type,
owner,
object_name,
0 as rank
from all_objects
where object_name = :NAME
and UPPER(owner) = nvl(:OWNER, :SCHEMA_CONTEXT)
and object_type not in ('SYNONYM')
union all
select ( select ao.object_type
from all_objects ao
where ao.owner = syn.table_owner
and ao.object_name = syn.table_name
and ao.namespace = 1 -- added
and ao.subobject_name is null -- added
and ao.object_type <> 'DATABASE LINK' -- added
) as object_type,
syn.table_owner as owner,
syn.table_name as object_name,
case
when UPPER(syn.owner) = nvl(:OWNER, :SCHEMA_CONTEXT) then
10
else
20
end as rank
from all_synonyms syn
where syn.synonym_name = :NAME
and UPPER(syn.owner) in (nvl(:OWNER, :SCHEMA_CONTEXT), nvl(:OWNER, 'PUBLIC'))
)
where object_type is not null
and ((: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>';
begin
dbms_sql_translator.register_sql_translation (
profile_name => co_sql_trns_prof_name,
sql_text => l_orig_stmt,
translated_text => l_repl_stmt,
enable => true
);
end;
/
2/ Use the SQL Translation profile in the current session
2.a/ In order to use the SQL Translation profile (assuming it's been created in the current schema):
alter session set sql_translation_profile = sqldev_hack;
2.b/ In order for SCOTT to use the SQL Translation profile created in PDB_ADMIN's schema:
i. Required grants:
-- Using an account having DBA rights:
grant translate sql on user scott to pdb_admin;
-- or:
grant translate any sql to pdb_admin;
-- As creator of the profile:
grant use on sql translation profile sqldev_hack to scott;
ii. As SCOTT:
alter session set sql_translation_profile = pdb_admin.sqldev_hack;
3/ Stop using the SQL Translation profile
alter session set sql_translation_profile = null;
4/ Checking if the profile is being used
select o.owner, o.object_name,
m.sql_id, m.mapped_sql_id,
m.sql_fulltext, m.mapped_sql_fulltext,
m.translation_timestamp, m.translation_cpu_time, m.translation_elapsed_time,
m.translation_method,
m.dictionary_sql_id,
m.use_count,
m.con_id
from v$mapped_sql m,
dba_objects o
where m.sql_translation_profile_id = o.object_id
and o.object_type = 'SQL TRANSLATION PROFILE'
and m.mapped_sql_id is not null;
How fast is the replacement query?
Don't expect miracles here in general: the replacement query still queries ALL_SYNONYMS (obviously), and that view is slow on modern, multi-tenant databases (e.g. 23c FREE), especially for unprivileged users. If you have the DBA role (or even better, the SYSDBA privilege) that query may be faster because some time-consuming authorization checks can be skipped or run faster.
As regards the performance of the SHOW ERRORS command in SQLcl, and the duration of the installation of utPLSQL: with the SQL Translation profile in place, the install.sql script took 1 min and 40 s on my 23c FREE test PDB, using an account with the DBA role (not SYSDBA). This is far better from what I had seen without the SQL Translation profile.
Finally, a word of caution
Of course you do all this at your own risk! Using an SQL Translation profile in order to “enhance” (for better or worse) SQL queries run by SQLcl or SQL Developer is absolutely unsupported.
But it's fun to do anyway ;-)
Regards,