Toad World® Forums

Opening trigger takes too long.


#1

In the 6.3 latest beta, when I try to open trigger the following SQL gets executed, but it takes way too much time to complete, we have ~ 40 000 schema objects.

select :schema owner,a.object_name,a.object_id,a.created,a.last_ddl_time,decode(a.status,‘VALID’,0,‘INVALID’,1,2) status,decode(b.partitioned,‘YES’,‘Y’,‘NO’,‘N’) partitioned,‘N’ object_table,‘N’ external_table,decode(b.nested,‘YES’,‘Y’,‘N’) nested,decode(b.IOT_Type,‘IOT’,1,‘IOT_OVERFLOW’,2,0) IOT_Type,b.IOT_Name,b.temporary
from sys.dba_objects a,sys.dba_tables b
where b.owner=:schema
and b.table_name like :object_name
and a.owner=:schema
and a.object_name=b.table_name
and a.object_type=‘TABLE’
and b.dropped=‘NO’

Execution plan:

/* OPC: Instance: ORCLDEV1@puf(10.2.0.4.0), plan saved: 25.5.2010 10:11:02, by user: sys, hash_value:29ct8njvsg17a */
OPTIMIZER |COST |PLAN |OBJ_OWNER |OBJ_NAME |LAST_ANALYZED |ROWS |SIZE_MB |PART_START |PART_STOP |PART_ID |

ALL_ROWS |2509 | SELECT STATEMENT | | | | | | | | |
|2509 | HASH JOIN | | | |1 |0 | | | |
|2380 | MERGE JOIN CARTESIAN | | | |17 |0 | | | |
|2253 | NESTED LOOPS OUTER | | | |1 |0 | | | |
|2251 | NESTED LOOPS OUTER | | | |1 |0 | | | |
|2249 | NESTED LOOPS | | | |1 |0 | | | |
|2248 | NESTED LOOPS OUTER | | | |1 |0 | | | |
|2245 | NESTED LOOPS OUTER | | | |1 |0 | | | |
|2243 | NESTED LOOPS | | | |1 |0 | | | |
|2240 | NESTED LOOPS | | | |1 |0 | | | |
|1397 | NESTED LOOPS | | | |327 |0,1 | | | |
|2 | TABLE ACCESS BY INDEX ROWID -> SYS.USER$ |SYS |USER$ |21.4.2010 1:01:51 |1 of 478 |0 | | | |
|1 | INDEX UNIQUE SCAN -> SYS.I_USER1 |SYS |I_USER1 |21.4.2010 1:01:52 |1 | | | | |
|1394 | VIEW -> SYS.DBA_OBJECTS |SYS |DBA_OBJECTS | |327 |0,1 | | | |
| | UNION-ALL | | | | | | | | |
| | FILTER | | | | | | | | |
|1391 | NESTED LOOPS | | | |376 |0 | | | |
|2 | TABLE ACCESS BY INDEX ROWID -> SYS.USER$ |SYS |USER$ |21.4.2010 1:01:51 |1 of 478 |0 | | | |
|1 | INDEX UNIQUE SCAN -> SYS.I_USER1 |SYS |I_USER1 |21.4.2010 1:01:52 |1 | | | | |
|1389 | TABLE ACCESS BY INDEX ROWID -> SYS.OBJ$ |SYS |OBJ$ |25.5.2010 1:01:11 |376 of 182278 |0 | | | |
|44 | INDEX RANGE SCAN -> SYS.I_OBJ2 |SYS |I_OBJ2 |25.5.2010 1:01:14 |2191 | | | | |
|2 | TABLE ACCESS BY INDEX ROWID -> SYS.IND$ |SYS |IND$ |25.5.2010 1:01:02 |1 of 19476 |0 | | | |
|1 | INDEX UNIQUE SCAN -> SYS.I_IND1 |SYS |I_IND1 |25.5.2010 1:01:02 |1 | | | | |
|2 | TABLE ACCESS BY INDEX ROWID -> SYS.SUM$ |SYS |SUM$ |25.5.2010 1:00:22 |1 of 532 |0 | | | |
|1 | INDEX UNIQUE SCAN -> SYS.I_SUM$_1 |SYS |I_SUM$_1 |25.5.2010 1:00:23 |1 | | | | |
| | FILTER | | | | | | | | |
|5 | NESTED LOOPS | | | |1 |0 | | | |
|2 | TABLE ACCESS BY INDEX ROWID -> SYS.USER$ |SYS |USER$ |21.4.2010 1:01:51 |1 of 478 |0 | | | |
|1 | INDEX UNIQUE SCAN -> SYS.I_USER1 |SYS |I_USER1 |21.4.2010 1:01:52 |1 | | | | |
|2 | TABLE ACCESS BY INDEX ROWID -> SYS.LINK$ |SYS |LINK$ |9.3.2010 0:06:03 |1 of 40 |0 | | | |
|1 | INDEX RANGE SCAN -> SYS.I_LINK1 |SYS |I_LINK1 |9.3.2010 0:06:03 |10 | | | | |
|4 | TABLE ACCESS BY INDEX ROWID -> SYS.OBJ$ |SYS |OBJ$ |25.5.2010 1:01:11 |1 of 182278 |0 | | | |
|2 | INDEX RANGE SCAN -> SYS.I_OBJ2 |SYS |I_OBJ2 |25.5.2010 1:01:14 |1 | | | | |
|2 | TABLE ACCESS CLUSTER -> SYS.TAB$ |SYS |TAB$ |25.5.2010 1:00:53 |1 of 17651 |0 | | | |
|1 | INDEX UNIQUE SCAN -> SYS.I_OBJ# |SYS |I_OBJ# |25.5.2010 1:01:04 |1 | | | | |
|2 | TABLE ACCESS CLUSTER -> SYS.SEG$ |SYS |SEG$ |25.5.2010 1:01:00 |1 of 64718 |0 | | | |
|1 | INDEX UNIQUE SCAN -> SYS.I_FILE#_BLOCK# |SYS |I_FILE#_BLOCK# |25.5.2010 1:01:45 |1 | | | | |
|2 | TABLE ACCESS BY INDEX ROWID -> SYS.OBJ$ |SYS |OBJ$ |25.5.2010 1:01:11 |1 of 182278 |0 | | | |
|1 | INDEX UNIQUE SCAN -> SYS.I_OBJ1 |SYS |I_OBJ1 |25.5.2010 1:01:14 |1 | | | | |


#2

Hi,

I can see the query referrs to sys.dba_objects. This means you have selected the “Enable using DBA views” option in logon dialog when you created the connection. This option helps user to see more objects and be able to perform more database management actions. But it may cause the queries take more time to run when they’re referring to sys.dba_objects.

Could you uncheck the “Enable using DBA views” in logon window then create the connection and try the trigger again? You are able to find the “Enable using DBA views” checkbox after you expand the Options pane by click the button “Options >>” in logon window.

Thanks,
Vincent


#3

Hi, we need this option to be turned on, exacly it allows us to see more objects (package body).

  • Why do you need to also join dba_objects? dba_tables is sufficient.
  • You are using “b.table_name like :object_name” but this is not the same as “b.table_name = :object_name” … do you really need to use LIKE in this case?

#4

Thanks for the explanation and advise.

  • We use dba_objects because we need to get the object_id for each table.
  • You’re right. We shouldn’t use the LIKE here. I will create CR to change this and hopefully this eases the slowness of opening a trigger. I will let you know once we get this done for you.

Thanks,
Vincent