1 2 Previous Next 16 Replies Latest reply: Jul 7, 2009 1:34 PM by damorgan RSS

    Tunning ALL_OBJECTS view

    417675
      Hi,

      How can I implement more performance in the ALL_OBJECTS view?

      I need execute a query like this: SELECT DISTINCT OWNER FROM SYS.ALL_OBJECTS;

      Important: I can't alter the query, because it's executed by an independent software.

      Any suggestions?

      Thanks in advance.
        • 1. Re: Tunning ALL_OBJECTS view
          Toon Koppelaars
          Maybe with this?

          http://technology.amis.nl/blog/447/how-to-drive-your-colleagues-nuts-dbms_advanced_rewrite-oracle-10g

          Dirty, but it should work.
          • 2. Re: Tunning ALL_OBJECTS view
            Hoek
            Important: I can't alter the query, because it's executed by an independent software.
            In that case you lose...your independent software vendor should have been smarter than using this kind of queries.
            I'm afraid you're forced to work with some 'generic/dynamic model' here?
            • 3. Re: Tunning ALL_OBJECTS view
              Hoek
              :D
              • 4. Re: Tunning ALL_OBJECTS view
                Toon Koppelaars
                I just realized...

                There is a restriction in that package: it won't do rewrites of queries that involve SYS objects.
                (last time I played with it)

                So Martijn is right.

                Too bad they have the 'SYS. ' prefix in there, otherwise you could have redirected the query to an ALL_OBJECTS you write yourself, using a private synonym.
                • 5. Re: Tunning ALL_OBJECTS view
                  417675
                  Thanks Hoek,

                  But I really can't modify the query. Is the first step executed by Crystal Reports 2008 to obtain the objects then I have acess...
                  • 6. Re: Tunning ALL_OBJECTS view
                    MadWorld
                    Hello Freitas,

                    I don't think there's a way to tune Oracle views that are owned by SYS...

                    I suggest you create materialized views with DISTINCT clause you need. You can then query the view.

                    I tried this but it is slower ( about 30%) than DISTINCT

                    WITH dist_owner AS(
                         SELECT aob.owner
                                   ,row_number() over(PARTITION BY aob.owner ORDER BY aob.owner) AS rownumber
                         FROM all_objects aob
                    )
                    SELECT *
                    FROM dist_owner
                    WHERE rownumber = 1;
                    • 7. Re: Tunning ALL_OBJECTS view
                      damorgan
                      You can't. Most likely you have a version of the Oracle database with a bug that has been fixed in the most current version and backported into some of the most recent patches.

                      One possibility is to create an object in the current schema with the same name. Based on how Oracle does its name resolution it will find the local object.
                      SQL> SELECT owner FROM all_views WHERE view_name = 'ALL_OBJECTS';
                      
                      OWNER
                      ------------------------------
                      SYS
                      
                      SQL> create view all_objects as select * from dual;
                      
                      View created.
                      
                      SQL> SELECT owner FROM all_views WHERE view_name = 'ALL_OBJECTS';
                      
                      OWNER
                      ------------------------------
                      SYS
                      UWCLASS
                      
                      SQL> 
                      • 8. Re: Tunning ALL_OBJECTS view
                        Hoek
                        Well, sorry, but it looks like it's out of your hands then...
                        What DB version are you on, by the way?
                        • 9. Re: Tunning ALL_OBJECTS view
                          417675
                          Damargon,

                          My DB version is: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
                          • 10. Re: Tunning ALL_OBJECTS view
                            damorgan
                            The bug is definitely fixed in 11.1.0.7 ... I was told it was in the 10.2.0.4 patch but apparently not if you are still having the issue which was caused by recursing through XML schema objects. Name resolution should solve the problem. Since you are working with Crystal Reports one easy solution for you is a table in the current schema named "ALL_OBJECTS" that you load as required. I demonstrated the technique above.
                            • 11. Re: Tunning ALL_OBJECTS view
                              Toon Koppelaars
                              ALL_OBJECTS is prefixed with SYS. in his query...
                              • 12. Re: Tunning ALL_OBJECTS view
                                Hoek
                                Your 'independent third party software vendor' made this mistake, imo, and you can't change/aren't allowed to touch it:
                                where username not in ( 'SYS', 'SYSTEM' )
                                See also:
                                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5792247321358#5797742102713
                                and there's much more if you search on it.

                                They're causing your system way overhead.
                                SYS is not to be (ab)used, especially from 10G and onwards...
                                • 13. Re: Tunning ALL_OBJECTS view
                                  417675
                                  Thanks Damorgan,

                                  But the Crystal is using SYS.ALL_OBJECTS internally.
                                  An approach was alter the internal code of the view, adding Oracle hints.

                                  I will try to convince the DBA...
                                  • 14. Re: Tunning ALL_OBJECTS view
                                    417675
                                    Exactly Hoek. In this case, I'm hands tied.
                                    1 2 Previous Next