Query that take the wrong plan

[lou1s]

When we start a connection with plsql_dev apps, we see some session on database with bad explain plan.

c3gyw2zbwqttf

SELECT s.synonym_name AS object_name,
o.object_type
FROM all_synonyms s,
sys.all_objects o
WHERE s.owner IN "SYS_B_0", :schema)
AND o.owner = s.table_owner
AND o.object_name = s.table_name
AND o.object_type IN "SYS_B_1", :"SYS_B_2", :"SYS_B_3",:"SYS_B_4", :"SYS_B_5", :"SYS_B_6", :"SYS_B_7")

8105qhn37k4wn

SELECT s.synonym_name object_name,
o.object_type
FROM all_synonyms s,
sys.all_objects o
WHERE s.owner IN "SYS_B_0", USER)
AND o.owner = s.table_owner
AND o.object_name = s.table_name
AND o.object_type IN "SYS_B_1", :"SYS_B_2", :"SYS_B_3",:"SYS_B_4", :"SYS_B_5", :"SYS_B_6", :"SYS_B_7")

Q1) Do you have any information on what trigger this probleme to append.

Q2) Do you have a workaround

 

[lou1s]

I see the other forum question

< background query on logon >

You explain the workaround but nothing about the reason thar trigger the situation.

Can you tell me more about that

 

[lou1s]

we have oracle 11.2.0.3.0 and plsql_dev 9.0.5.1648

 

[IvanZ]

SQL seems OK.
Why do you have bad execution plan anyway?
Do you have proper system statistics?

 

[Hilarion]

Apart from gathering proper system statistics to make the execution plan better, you may also try to use the "Use DBA Views if available" option (in Tools / Preferences / Oracle / Options). It should change the query to use DBA_SYNONYMS and DBA_OBJECTS instead of ALL_SYNONYMS and ALL_OBJECTS (if you do have rights to access DBA_* views), which should make the query much more effective (as DBA_* views do not include user rights internal tables).
We had similar issues on databases that had millions of private synonyms and no statistics tuning would make the query run fast.

 

[Marco Kalter]

I don't know why this query causes problems, but you can remove it from the CANames.sql file. As a result the Code Assistant will no longer display synonyms when typing partial synonym names. For example, when a user types

dbms_ou

the Code Assistant will no longer suggest "dbms_output" (which is a public synonym for sys.dbms_output).