Slow performance connecting to 12c

E

Emoracle

Member
When we're creating a connection to our main 12c db, Developer seems to hang for a while. Background investigation gave us that the statement
select * from sys.dba_type_attrs where rownum < 1
is the main cause.

We have this in investigation, but is there a way to start PLSQLDeveloper without firing of this statement?
 
Thanks. I tackled the issue by the way:
setting dynamic sampling to 11 for the whole database is not a good thing.

The db was sampling for statements like
select * from dba_whatever where rownum < 1
 
We have the same problem, but optimizer_dynamic_sampling is unset (2 by default). Do you have any other idea?

We've run the following:
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

These probably helped, but all the selects from dba_... views with "where rownum < 1" take some time that add up.
 
Marco, could this be worked around in PSD 12?
What is the purpose of these queries? Only checking the read (select) privileges? Even a simple (deprecated, unsupported) RULE hint would help a lot. Also, a somewhat tricky query could be written that would consider both object and system privileges whether granted directly or through roles.

Code: 
00:00:04.970 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_objects where rownum < 1
00:00:05.081 TOracleQuery 730660 End
             Duration = 0,109

00:00:05.083 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_tab_columns where rownum < 1
00:00:05.412 TOracleQuery 7303A0 End  (100 records processed)
             Duration = 1,794

00:00:06.709 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_col_comments where rownum < 1
00:00:06.821 TOracleQuery 730660 End
             Duration = 0,109

00:00:06.823 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_triggers where rownum < 1
00:00:07.198 TOracleQuery 730660 End
             Duration = 0,375

00:00:07.200 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_constraints where rownum < 1
00:00:07.785 TOracleQuery 7303A0 End  (100 records processed)
             Duration = 1,748

00:00:08.087 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_indexes where rownum < 1
00:00:08.336 TOracleQuery 730660 End
             Duration = 0,25
00:00:08.341 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_cons_columns where rownum < 1
00:00:08.459 TOracleQuery 730660 End
             Duration = 0,124
00:00:08.462 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_ind_columns where rownum < 1
00:00:08.606 TOracleQuery 730660 End
             Duration = 0,141
00:00:08.609 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_dependencies where rownum < 1
00:00:08.698 TOracleQuery 730660 End
             Duration = 0,078
00:00:08.702 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_tables where rownum < 1
00:00:08.957 TOracleQuery 730660 End
             Duration = 0,25
00:00:08.958 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_tab_comments where rownum < 1
00:00:09.160 TOracleQuery 730660 End
             Duration = 0,202
00:00:09.162 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_sequences where rownum < 1
00:00:09.184 TOracleQuery 730660 End
             Duration = 0,016
00:00:09.190 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_errors where rownum < 1
00:00:09.364 TOracleQuery 730660 End
             Duration = 0,171
00:00:09.365 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_source where rownum < 1
00:00:09.770 TOracleQuery 730660 End
             Duration = 0,406
00:00:09.773 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_views where rownum < 1
00:00:10.018 TOracleQuery 730660 End
             Duration = 0,249
00:00:10.025 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_synonyms where rownum < 1
00:00:10.227 TOracleQuery 730660 End
             Duration = 0,203
00:00:10.232 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_users where rownum < 1
00:00:10.323 TOracleQuery 730660 End
             Duration = 0,078
00:00:10.325 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_object_tables where rownum < 1
00:00:11.495 TOracleQuery 730660 End
             Duration = 1,17
00:00:11.498 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_type_attrs where rownum < 1
00:00:12.217 TOracleQuery 730660 End
             Duration = 0,718
00:00:12.219 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_type_methods where rownum < 1
00:00:12.320 TOracleQuery 730660 End
             Duration = 0,094
00:00:12.323 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_method_params where rownum < 1
00:00:12.578 TOracleQuery 730660 End
             Duration = 0,25
00:00:12.580 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_method_results where rownum < 1
00:00:13.090 TOracleQuery 730660 End
             Duration = 0,515
00:00:13.097 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_tab_privs where rownum < 1
00:00:13.403 TOracleQuery 730660 End
             Duration = 0,312
00:00:13.419 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_clusters where rownum < 1
00:00:13.473 TOracleQuery 730660 End
             Duration = 0,047
00:00:13.478 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_libraries where rownum < 1
00:00:13.531 TOracleQuery 730660 End
             Duration = 0,046
00:00:13.533 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_directories where rownum < 1
00:00:13.553 TOracleQuery 730660 End
             Duration = 0,032
00:00:13.555 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_jobs where rownum < 1
00:00:13.570 TOracleQuery 730660 End
             Duration = 0,015
00:00:13.572 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_db_links where rownum < 1
00:00:13.593 TOracleQuery 730660 End
             Duration = 0,016
00:00:13.596 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_mviews where rownum < 1
00:00:13.744 TOracleQuery 730660 End
             Duration = 0,156
00:00:13.746 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_snapshots where rownum < 1
00:00:13.779 TOracleQuery 730660 End
             Duration = 0,031
00:00:13.782 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_queues where rownum < 1
00:00:13.800 TOracleQuery 730660 End
             Duration = 0,016
00:00:13.802 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_queue_tables where rownum < 1
00:00:13.858 TOracleQuery 730660 End
             Duration = 0,047
00:00:13.860 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_types where rownum < 1
00:00:14.397 TOracleQuery 730660 End
             Duration = 0,546
00:00:14.401 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_scheduler_jobs where rownum < 1
00:00:14.785 TOracleQuery 730660 End
             Duration = 0,374
00:00:14.787 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_scheduler_program_args where rownum < 1
00:00:14.893 TOracleQuery 730660 End
             Duration = 0,094
00:00:14.895 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_scheduler_job_args where rownum < 1
00:00:15.002 TOracleQuery 730660 End
             Duration = 0,094
00:00:15.006 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_scheduler_wingroup_members where rownum < 1
00:00:15.087 TOracleQuery 730660 End
             Duration = 0,078
00:00:15.089 TOracleQuery 730660 Start Query.Describe
             SQL = select * from sys.dba_scheduler_windows where rownum < 1
00:00:15.105 TOracleQuery 730660 End
             Duration = 0,016
 
No, you didn't.
Still not good in Beta8, e.g.:

Code: 
00:00:05.488 TOracleQuery 538CC0 Start Query.Describe
             SQL = select * from sys.dba_constraints where rownum < 1
00:00:06.190 TOracleQuery 5381C0 End  (100 records processed)
             Duration = 1,248
 
You must log in or register to reply here.

Similar threads

Marco Kalter
  • Locked
  • Sticky
  • Discussion Discussion
Plyxon Beta 4 released
Replies
0
Views
766
Marco Kalter
Marco Kalter
C
  • Discussion Discussion
Check connection does not work in Multisession mode which leads to freezing app.
Replies
2
Views
1K
czdavidm
C
P
  • Discussion Discussion
Performance Issues ?
Replies
2
Views
3K
lost
L
F
  • Discussion Discussion
Dual Session slow on connecting/disconnect
Replies
3
Views
2K
Felix
F
J
  • Discussion Discussion
Jobs tree ridiculous slow open in 12c.
Replies
3
Views
5K
jareeq
J
Back
Top