Here are some things you can do.
-
Did you collect statistics on the SYS schema after the database ugprade? Do so if you did not. It doesn’t hurt to do that again if you aren’t sure. I’ll paste in a script to do that at the end of this reply.
-
It looks like you are logged as one user (KNL) that does not have the SELECT ANY DICTIONARY or SELECT CATALOG privilege. If you have either of these, Toad will use DBA views instead of ALL views. That should help. If you do have these privileges, go into options and make sure “Check for access to DBA_ views” is checked.
-
in the schema browser table filter (the funnel icon on the left when looking at tables), if you don’t have any object tables, go into “tables to hide” and check “object tables” . In that case, Toad will skip the query to dba/all/user_object_tables, which tends to be a little bit slow.
-
on the script tab, click the first toolbar button. Go through each tab under “Script Options” and uncheck whatever you aren’t interested in. This will reduce the number of queries that Toad executes while building the script.
DECLARE
SQLText Varchar2(4000);
DegreeValue Varchar2(64);
BEGIN
DegreeValue := SYS.DBMS_STATS.GET_PREFS(‘DEGREE’’);
SQLText := ‘BEGIN’ || CHR(10) ||
’ SYS.DBMS_STATS.GATHER_DICTIONARY_STATS (’ || CHR(10) ||
’ Granularity => ‘‘DEFAULT’’’ || CHR(10) ||
’ ,Options => ‘‘GATHER’’’ || CHR(10) ||
’ ,Estimate_Percent => NULL’ || CHR(10) ||
’ ,Method_Opt => ‘‘FOR ALL COLUMNS SIZE 1’’’ || CHR(10) ||
’ ,Degree => ’ || DegreeValue || CHR(10) ||
’ ,Cascade => TRUE’ || CHR(10) ||
’ ,No_Invalidate => FALSE);’ || CHR(10) ||
‘END;’;
execute immediate(SQLText);
END;
/
DECLARE
SQLText Varchar2(4000);
BEGIN
SQLText := ‘BEGIN’ || CHR(10) ||
’ SYS.DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (’ || CHR(10) ||
’ No_Invalidate => FALSE);’ || CHR(10) ||
‘END;’;
execute immediate(SQLText);
END;
/