Query from dba_type_attrs upon logon takes cca 50 seconds.

[sentimex]

Hi,

PL/SQL Developer upon logging on to database is executing the following query:

select * from sys.dba_type_attrs where rownum < 1;

it completes in around 50 seconds on our production system.

This is pretty annoying.

Thanks.
Best regards,
Daniel.

 

[sentimex]

This is on ver. 13.0.3.1902 (x32) registered.

Database version 12.1.0.2 x64 (AIX)

 

[sentimex]

Exec plan is at:
https://ufile.io/6hst9

Filename: exec_plan.txt

 

[sentimex]

select count(1) from dba_objects;

334 747

 

[Marco Kalter]

The query is not executed. It is merely described. If it takes a long time it may be that one or more dictionary views are invalid.

To prevent this operation, go to Preferences > Oracle / Options and disable the "Use DBA views if available" option.

 

[sentimex]

The query is executing ... what makes you think it's not executed?

Dictionary views are valid.

I'd like to use DBA views.

 

[sentimex]

The query IS executing during the logon when PL/SQL Developer is used.

This is the total time for it to complete.

SQL> set timing on
SQL> select * from sys.dba_type_attrs where rownum < 1;

no rows selected

Elapsed: 00:00:39.34
SQL>

What is this query suppose to check anyway?

 

[sentimex]

Event during the SQL execution: DB file sequential read

 

[sentimex]

This is instant if you want to verify DBA views are accessible by currently logged on user.

select * from sys.dba_type_attrs where 0=1;

 

[T-Gergely]

Have you tried the following procedures?
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

See this old thread.

 

[sentimex]

Have you tried the following procedures?
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Yes, completed successfully.

Execution time still ~ 50 sec.

 

[T-Gergely]

Those did not help us a lot, either, but we had better parsing times to begin with. Can you open a service request at Oracle Support? (Not that I had much luck with them, either.)

 

[sentimex]

For this ?

select * from sys.dba_type_attrs where rownum < 1;

You're joking right?

The devs at allroundautomata can just use:

select * from sys.dba_type_attrs where rownum 0=1;

Which is instant.

I presume this is only suppose to check if you have access to DBA% views.

 

[T-Gergely]

If I were joking, I would say "A horse walks into a bar, and the bartender asks him, 'Why the long face?'" I've patched this and several other bugs in PSD in a terrible way as neither Allround, nor Oracle cares.

 

[Dylan_K]

T-Gergely,

How did you patch this? I need to use DBA_ views and I'm sick of waiting almost a minute to get connected in PL/SQL Developer. I'm willing to Hex-Edit the binary but cannot find where these queries are coming from! Any hints would be greatly appreciated. The DBA_ views keep getting slower and slower with each new version of Oracle when queried in this way, now I'm on Oracle Database 12.2 and it's so very bad!

Dylan.

 

[T-Gergely]

I did it in a very hackish way: you don't want to do that. I find the problems in a machine code level debugger, then write patches in assembly.

You may want to look for a 17 character Delphi string " where rownum < 1" encoded with 16 bit characters. It's at offset 0226a2c0 in plsqldev.exe in plsqldev1304x32.msi. If "where 0 = 1" works for you, fine. If it doesn't, well, it's going to be tricky, as I had to change the previous string "select * from sys." to "select /*+rule*/ * from sys.", but it won't fit there. (I know the rule hint is not supported any more, but it was the only way I could find to make parsing fast.)

 

[sentimex]

Editing EXE files in 2019, LOL ))

 

[sentimex]

I can confirm the 0=1 change at 0226a2c0 offset works.

 

[T-Gergely]

Editing EXE files in 2019, LOL ))

Hey, that's what viruses still do, too. And it's easier than having an official bugfix.

 

[sentimex]

Hey, that's what viruses still do, too. And it's easier than having an official bugfix.

:grin:

 

[Dylan_K]

So I tried an alternative to hacking the executable because 0=1 is just as slow for me as rownum < 1.

I found that you can edit CANames.sql in the C:\Program Files\PLSQL Developer ## folder, wipe everything out and leave this:

/*
The name + type results of these queries will be used by the Code Assistant
if the "Describe Context" option is enabled. After typing 3 or more characters
the Code Assistant will show a list of matching names.
Separate multiple queries with semi-colons and use the :schema bind variable
to restrict names to the currently connected user.
In case of an error the query results will be omitted. No error message will
be displayed.
Place this file in the PL/SQL Developer installation directory for all users,
or in the "%APPDATA%\PLSQL Developer" directory for a specific user.
*/
select DBPROC.PLSQL_DEVELOPER_FIX_SLOW_CONNECTschema) as OBJECT_NAME, NULL as OBJECT_TYPE FROM DUAL WHERE 0=1
;

Then the function looks like this:

create or replace function dbproc.PLSQL_DEVELOPER_FIX_SLOW_CONNECT(schema VARCHAR2)
return VARCHAR2 is
begin
EXECUTE IMMEDIATE 'ALTER SESSION SET OPTIMIZER_MODE=RULE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_squ_bottomup" = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_cost_based_transformation=''off''';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_cbqt_or_expansion"=off';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_max_permutations"=1000';
return(schema);
end ;

These were all suggestions from Metalink knowledgebase articles to fix slow parses of dictionary views. None of those parameters helps me. Maybe someone else can riff on this idea and find one that does?

Dylan.

 

[Dylan_K]

I should have also said - if you do the above and then run plsqldev.exe DebugSQL, you can see in the debug log that the function gets called before any references to the dictionary views.

Dylan.

 

[sentimex]

Thanks, nice to know.

How many objects do you have in your DB?

select count(1) from dba_objects;

We have close to 335K objects.

We're using these init params on 12.1.0.2 :

_gby_hash_aggregation_enabled = TRUE
_optimizer_enhanced_filter_push = FALSE
_optimizer_push_pred_cost_based = FALSE
_optimizer_unnest_scalar_sq = FALSE
optimizer_adaptive_features = FALSE
optimizer_adaptive_reporting_only = TRUE
optimizer_secure_view_merging = FALSE
parallel_adaptive_multi_user = FALSE

Good luck!

 

[sentimex]

Also, when the "rownum < 1" query is executed on your environment, what is the EVENT in v$session for that db session?

 

[sentimex]

I would try:

1) Remove all of your custom init params, and leave everything to defaults.

2) Try only the ones I've provided.

3) The 0=1 should be evaluated immediately, but I'm suspecting maybe the optimizer_secure_view_merging parameter could have something to do with this because you're selecting from a view that is pretty complex and can't be merged.

 

[Dylan_K]

select count(1) from dba_objects;

97240

I changed my function to just set your parameters and it had little to no effect on the connect time. We are running 12.2.0.1.

I will try the Hex Edit thing to change rownum < 1 to 0=1 because I actually do seem to be seeing an improvement in runtime when I run that query vs rownum < 1. I'll let you guys know how that goes.

 

[sentimex]

You could always just describe sys.dba_type_attrs
and create empty table with the same structure (and same name length,
so you can replace it in the EXE)

Gather stats on the empty table and query from this would be definitely instant.

 

[Dylan_K]

Nothing seems to help, not the parameters you provided, not changing rownum < 1 to 0=1. I was just getting fooled by the library cache having recently parsed the same query. After a while, it ages out and goes back to dead slow.

I did consider doing an ALTER SESSION SET CURRENT_SCHEMA=ME in the CANames.sql, creating empty physical tables in the ME schema, hex-editing the hardcoded sys. out of the executable, and then resetting CURRENT_SCHEMA in the login script. I'll chip away at that...

 

[sentimex]

Just tried 13.0.5, unbf...beliveable all of these are executed during the log-on:

select * from sys.dba_tab_columns where rownum < 1;
select * from sys.dba_object_tables where rownum < 1;
select * from sys.dba_type_attrs where rownum < 1;

Congrats the logon takes 1 minute and 20 seconds now !!!

Switching back to SQL Navigator....

 

[Dylan_K]

Okay, so I came back to this connect time problem. It was not simple, but I finally cracked it. I will make an official enhancement request to allow for a script to be run on the Primary Connection before any of the catalog queries.

I wanted to avoid hex editing altogether but could not find a way. I tried to use the CANames.sql script which gets called before the catalog queries, but found that the CANames.sql script gets called on a different/temporary connection than the catalog queries:

00:00:06.632 TOracleSession $0AE18890 Start Session.LogOn as dkucera@xxxxxx

....

00:00:07.107 TOracleSession $0AE18CB0 Start Session.LogOn as dkucera@xxxxxx

....

00:00:07.260 TOracleQuery $0DDB5060 Start Query.Execute
SQL = /*
The name + type results of these queries will be used by the Code Assistant
if the "Describe Context" option is enabled. After typing 3 or more characters
the Code Assistant will show a list of matching names.
Separate multiple queries with semi-colons and use the :schema bind variable
to restrict names to the currently connected user.
In case of an error the query results will be omitted. No error message will
be displayed.
Place this file in the PL/SQL Developer installation directory for all users,
or in the "%APPDATA%\PLSQL Developer" directory for a specific user.
*/
SELECT * FROM (select DBPROC.PLSQL_DEVELOPER_FIX_SLOW_CONNECTschema) as OBJECT_NAME, NULL as OBJECT_TYPE FROM DUAL) WHERE OBJECT_NAME='X'
:SCHEMA = DKUCERA

(just above is the CANames script executing)

....

00:00:07.374 TOracleQuery $0DDB5480 Start Query.Describe
SQL = select * from sys.dba_objects where rownum < 1

Notice how that Hex ID for the query on sys.dba_objects is different than the Hex ID for the CANames.sql script.

I found this useless query on the same Hex ID as the catalog queries that happens just prior:

00:00:07.328 TOracleQuery $0DDB5480 Start Query.Execute
SQL = select null from all_synonyms where 1=0

I found that in plsqldev.exe using "FlexHEX" and changed it to this:

00:00:07.328 TOracleQuery $0DDB5480 Start Query.Execute
SQL = select plsd "NULL" from dual

FlexHEX PLSQL Developer

plsd is a public synonym pointing to this procedure:

create or replace function dbproc.plsd_p
return VARCHAR2 is
begin
EXECUTE IMMEDIATE 'ALTER SESSION SET "_gby_hash_aggregation_enabled" = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_enhanced_filter_push" = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_push_pred_cost_based" = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_unnest_scalar_sq" = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_adaptive_features = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_adaptive_reporting_only = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "optimizer_mode"=RULE';

RETURN NULL;
end ;

I don't know which combination of those parameters does the trick, but it connects instantly now instead of taking 45+ seconds.

Maybe if some of you guys frustrated by the same problem can try this and confirm that it works for you as well to improve connect time, perhaps we can get some traction from Allround Automations to give us a way to do this without resorting to hex-editing their executable.

 

[T-Gergely]

I don't know which combination of those parameters does the trick

I'm pretty sure it's optimizer_mode=RULE as I reported that to Oracle Support and AA here a long time ago. In vain.

 

[Dylan_K]

My experience with Oracle Database 12.2.0.1 is the optimizer_mode=RULE was in and of itself insufficient to fix the problem. I combined that with a recommendation earlier in this thread and that solved the problem.

 

[T-Gergely]

OK, thanks.