2

I need help in tuning following query involving querying from Oracle data dictionary ALL_OBJECTS.

SELECT OBJECT_NAME ,OBJECT_TYPE 
FROM ALL_OBJECTS OBJ 
WHERE (OBJ.OWNER ='HR') 
AND (OBJ.OBJECT_TYPE IN ('TABLE','VIEW'));

I tried tuning query using hint as following but not much of help.

SELECT /*+ STAR_TRANSFORMATION */ OBJECT_NAME
       ,OBJECT_TYPE 
FROM ALL_OBJECTS OBJ 
WHERE (OBJ.OWNER ='HR') 
AND (OBJ.OBJECT_TYPE IN ('TABLE','VIEW'));

Any help will be highly appreciated.Thanks a lot.

APC
  • 144,005
  • 19
  • 170
  • 281
Sid
  • 582
  • 3
  • 7
  • 28
  • 1
    Why do you think that hint would make a difference here? Are you running this as the HR user - if so you could use `user_objects`, or even the `_tables` and `_views` instead? – Alex Poole Aug 15 '14 at 18:45
  • 1
    In addition to @AlexPoole's suggestions, what version of Oracle are you using and how (if at all) have you gathered statistics on the data dictionary objects? There are lots of version-specific considerations but if you have a query against a single data dictionary table that has a less-than-ideal plan, the problem would generally be a problem with statistics. – Justin Cave Aug 15 '14 at 19:31
  • My telepathy is on the fritz this week. Can you please describe the performance issues are you experiencing? I run queries similar to this regularly and get sub-second response time (Oracle 11.1). Have you gathered full statistics on the tables which are queried by the `ALL_OBJECTS` view? – Bob Jarvis - Слава Україні Aug 15 '14 at 23:54
  • @BobJarvis - not sure why you think gather stats on non-data dictionary tables would help here. – APC Aug 16 '14 at 08:51

1 Answers1

4

Having problems querying the data dictionary is unusual but not unknown. One situation where I have found it to be an issue is with databases which see a lot of schema rebuilding - such as shared development or continuous integration targets. In such situations the statistics for the data dictionary objects can be extremely inaccurate. If that sounds like it might fit your case you can ask a friendly DBA (or anybody else with the requisite privilege) to run GATHER_DICTIONARY_STATS(). Find out more.

Incidentally, I'm really not sure why you thought STAR_TRANSFORMATION might have helped you: that hint is intended for converting joins into sub-queries to mimic a star schema in a data-warehouse style. You have no joins so this is just magical thinking.

APC
  • 144,005
  • 19
  • 170
  • 281
  • 1
    @Bacs - you might find this answer instructive. – APC Aug 16 '14 at 08:45
  • I stand by my answer (poorly expressed, so deleted) that there's nothing to be done to tune the query. It didn't occur to me that tuning the database might help, so yes, thank you, that was instructive. – Bacs Aug 18 '14 at 09:23