1 2 Previous Next 17 Replies Latest reply: Apr 22, 2008 10:05 AM by 277993 RSS

    PERFORMANCE PROBLEM - SYS OBJECTS

    187628
      Hi all,

      I'm having something strange here! The performance of queries that run against SYS objects like view DBA_OBJECTS , DBA_SEGMENTS, DBA_EXTENTS are VERY SLOWLY!! It's hang everytime and i need to kill the session and the PID process.

      One example is the following:

      SELECT owner, object_name, object_type FROM sys.all_objects
      WHERE object_type IN ('PACKAGE', 'PROCEDURE', 'SEQUENCE', 'TABLE', 'TYPE',
      'VIEW', 'FUNCTION') ORDER BY owner, object_name;

      The objects of schema SYS was analyzed recently.
      I have run the query with trace and nothing was found on that trace. The trace says the the query runs in few seconds but it didn't.

      Any help?

      Tks,
      Paulo Portugal
        • 1. Re: PERFORMANCE PROBLEM - SYS OBJECTS
          181444
          What version of Oracle?
          Are you running in SQLPlus?
          By any chance do you have set pause on?

          Your query produced 6507 rows in 2.7 seconds on my 9.2.0.6 system running on AIX 5.2L.

          If you remove the statistics does the behavior change?

          HTH -- Mark D Powell --
          • 2. Re: PERFORMANCE PROBLEM - SYS OBJECTS
            187628
            I think the problem is statistics!


            I put the HINT "RULE" and it run in few seconds. I'm thinking in remove the SYS statistics. First i will export then.

            I will put the results here!

            Tks,
            Paulo Portugal
            • 3. Re: PERFORMANCE PROBLEM - SYS OBJECTS
              625995
              Paulo

              try this:

              as SYS (You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.)

              DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

              but dictionary stats should be gathered automatically by the stats maintenance job.

              MM
              • 4. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                Eduardo Legatti
                Hi,

                >>I think the problem is statistics!
                Basic question: What is your Oracle version/release?

                Cheers

                Legatti
                • 5. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                  Hans Forbrich
                  Paulo,

                  You have asked enough questions here to realize how important it is to provide Oracle version number and operating system information. I have seen some of the symptoms you describe under older versions of the database.

                  So - please provide Oracle version number and operating system information.


                  /Hans
                  • 6. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                    Madrid
                    It is important to clearly specify your Oracle version, since there are mean differences if you are talking about 9i or if you are talking about 10g.

                    Take a look at my notes on this issue --> http://hrivera99.blogspot.com/2008/03/data-dictionary-gathering.html


                    ~ Madrid
                    • 7. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                      187628
                      Hi all,

                      The version that i'm using here is 10.2.0.2.0.

                      I have deleted SYS statistics but it didn't help so i have generated statistics again. The problem still happens. The only workaround here is to put hint RULE on query. I don't know what to do anymore.

                      Tks,
                      Paulo
                      • 8. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                        181444
                        Tune the query like you would any other query. Instead of the RULE hint look at the plan and see if a different set of hints might get you the same result since RULE is on its way out the door.

                        HTH -- Mark D Powell --
                        • 9. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                          601585
                          Have you tried
                          - dbms_stats.gather_dictionary_stats
                          - dbms_stats.gather_fixed_objects_stats
                          ?

                          It's known issue that queries on dictionary views perform poor on 10g without proper statistics.
                          • 10. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                            187628
                            Hi,

                            Yes i tried these too but didn't help.

                            Regards,
                            Paulo
                            • 11. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                              Charles Hooper
                              Please take a look at the DBMS_XPLAN for the query to determine if there is a problem, and see if you are able to use DBA_OBJECTS rather than ALL_OBJECTS.

                              For example, on one of my databases, querying ALL_OBJECTS:
                              -----------------------------------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                                               | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                              -----------------------------------------------------------------------------------------------------------------------------------------------------------
                              |*  1 |  TABLE ACCESS BY INDEX ROWID                            | SUM$       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              |*  2 |   INDEX UNIQUE SCAN                                     | I_SUM$_1   |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              |   3 |  SORT ORDER BY                                          |            |      1 |    294 |   7329 |00:00:01.10 |     151K|   407K|   407K|  361K (0)|
                              |*  4 |   FILTER                                                |            |      1 |        |   7329 |00:00:01.07 |     151K|       |       |          |
                              |*  5 |    HASH JOIN                                            |            |      1 |  14668 |  14672 |00:00:00.09 |     196 |  1155K|  1155K| 1175K (0)|
                              |   6 |     TABLE ACCESS FULL                                   | USER$      |      1 |    157 |    157 |00:00:00.01 |      15 |       |       |          |
                              |*  7 |     TABLE ACCESS FULL                                   | OBJ$       |      1 |  14668 |  14672 |00:00:00.02 |     181 |       |       |          |
                              |*  8 |    TABLE ACCESS BY INDEX ROWID                          | IND$       |   2575 |      1 |   2409 |00:00:00.04 |    5157 |       |       |          |
                              |*  9 |     INDEX UNIQUE SCAN                                   | I_IND1     |   2575 |      1 |   2575 |00:00:00.02 |    2577 |       |       |          |
                              |  10 |     NESTED LOOPS                                        |            |   9134 |     28 |   4291 |00:00:00.83 |     145K|       |       |          |
                              |  11 |      FIXED TABLE FULL                                   | X$KZSRO    |   9134 |     26 |  68239 |00:00:00.13 |       0 |       |       |          |
                              |* 12 |      INDEX RANGE SCAN                                   | I_OBJAUTH1 |  68239 |      1 |   4291 |00:00:00.55 |     145K|       |       |          |
                              |* 13 |       FIXED TABLE FULL                                  | X$KZSPR    |      1 |     14 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 14 |        FIXED TABLE FULL                                 | X$KZSPR    |      1 |      6 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 15 |         FIXED TABLE FULL                                | X$KZSPR    |      1 |      3 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 16 |          FIXED TABLE FULL                               | X$KZSPR    |      1 |      3 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 17 |           FIXED TABLE FULL                              | X$KZSPR    |      1 |      3 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 18 |            FIXED TABLE FULL                             | X$KZSPR    |      1 |      6 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 19 |             FIXED TABLE FULL                            | X$KZSPR    |      1 |     11 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 20 |              FIXED TABLE FULL                           | X$KZSPR    |      1 |      3 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 21 |               FIXED TABLE FULL                          | X$KZSPR    |      0 |     14 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 22 |                FIXED TABLE FULL                         | X$KZSPR    |      1 |     11 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 23 |                 FIXED TABLE FULL                        | X$KZSPR    |      1 |     11 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 24 |                  FIXED TABLE FULL                       | X$KZSPR    |      1 |      3 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 25 |                   FIXED TABLE FULL                      | X$KZSPR    |      1 |      3 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 26 |                    FIXED TABLE FULL                     | X$KZSPR    |      1 |      6 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 27 |                     FIXED TABLE FULL                    | X$KZSPR    |      1 |     11 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 28 |                      FIXED TABLE FULL                   | X$KZSPR    |      1 |      6 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 29 |                       FIXED TABLE FULL                  | X$KZSPR    |      1 |      6 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 30 |                        FIXED TABLE FULL                 | X$KZSPR    |      1 |      6 |      1 |00:00:00.01 |       0 |       |       |          |
                              |* 31 |                         FIXED TABLE FULL                | X$KZSPR    |      0 |     14 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 32 |                          FIXED TABLE FULL               | X$KZSPR    |      0 |     11 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 33 |                           FIXED TABLE FULL              | X$KZSPR    |      0 |      6 |      0 |00:00:00.01 |       0 |       |       |          |
                              |  34 |                            VIEW                         |            |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              |  35 |                             FAST DUAL                   |            |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 36 |                              TABLE ACCESS BY INDEX ROWID| SUM$       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 37 |                               INDEX UNIQUE SCAN         | I_SUM$_1   |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              -----------------------------------------------------------------------------------------------------------------------------------------------------------

                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                                 1 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
                                 2 - access("S"."OBJ#"=:B1)
                                 4 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (INTERNAL_FUNCTION("O"."OWNER#") OR ("O"."TYPE#"<>11 AND
                                            "O"."TYPE#"<>14 AND  IS NOT NULL) OR (INTERNAL_FUNCTION("O"."TYPE#") AND  IS NOT NULL) OR ("O"."TYPE#"=13 AND  IS NOT NULL) OR ("O"."TYPE#"=11 AND
                                             IS NOT NULL) OR ("O"."TYPE#"=12 AND  IS NOT NULL) OR ("O"."TYPE#"=6 AND  IS NOT NULL) OR (INTERNAL_FUNCTION("O"."TYPE#") AND  IS NOT NULL) OR
                                            ("O"."TYPE#"=22 AND  IS NOT NULL) OR ("O"."TYPE#"=14 AND  IS NOT NULL) OR INTERNAL_FUNCTION("O"."TYPE#") OR ("O"."TYPE#"=33 AND  IS NOT NULL) OR
                                            ("O"."TYPE#"=46 AND  IS NOT NULL) OR ("O"."TYPE#"=62 AND  IS NOT NULL) OR ("O"."TYPE#"=66 AND  IS NOT NULL) OR ("O"."TYPE#"=48 AND  IS NOT NULL)
                                            OR ("O"."TYPE#"=44 AND  IS NOT NULL) OR ("O"."TYPE#"=59 AND  IS NOT NULL) OR ("O"."TYPE#"=23 AND  IS NOT NULL) OR ("O"."TYPE#"=68 AND  IS NOT
                                            NULL) OR (INTERNAL_FUNCTION("O"."TYPE#") AND  IS NOT NULL) OR ("O"."TYPE#"=42 AND  IS NOT NULL) OR ("O"."TYPE#"=32 AND  IS NOT NULL) OR
                                            ("O"."TYPE#"=81 AND  IS NOT NULL) OR ("O"."TYPE#"=55 AND =1)) AND (DECODE("O"."TYPE#",0,'NEXT
                                            OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
                                            BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
                                            SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',55,'XML
                                            SCHEMA',56,'JAVA DATA',57,'SECURITY PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB
                                            CLASS',69,'WINDOW',72,'WINDOW GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='PACKAGE' OR DECODE("O"."TYPE#",0,'NEXT
                                            OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
                                            BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
                                            SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',55,'XML
                                            SCHEMA',56,'JAVA DATA',57,'SECURITY PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB
                                            CLASS',69,'WINDOW',72,'WINDOW GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='PROCEDURE' OR DECODE("O"."TYPE#",0,'NEXT
                                            OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
                                            BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
                                            SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',55,'XML
                                            SCHEMA',56,'JAVA DATA',57,'SECURITY PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB
                                            CLASS',69,'WINDOW',72,'WINDOW GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='SEQUENCE' OR DECODE("O"."TYPE#",0,'NEXT
                                            OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
                                            BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
                                            SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT)
                                 5 - access("O"."OWNER#"="U"."USER#")
                                 7 - filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL))
                                 8 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))
                                 9 - access("I"."OBJ#"=:B1)
                                12 - access("OBJ#"=:B1 AND "GRANTEE#"="KZSROROL")
                                     filter((INTERNAL_FUNCTION("PRIVILEGE#") AND "GRANTEE#"="KZSROROL"))
                                13 - filter((((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND
                                            "INST_ID"=USERENV('INSTANCE')))
                                14 - filter((((-"KZSPRPRV")=(-184) OR (-"KZSPRPRV")=(-181)) AND "INST_ID"=USERENV('INSTANCE')))
                                15 - filter(((-"KZSPRPRV")=(-141) AND "INST_ID"=USERENV('INSTANCE')))
                                16 - filter(((-"KZSPRPRV")=(-152) AND "INST_ID"=USERENV('INSTANCE')))
                                17 - filter(((-"KZSPRPRV")=(-109) AND "INST_ID"=USERENV('INSTANCE')))
                                18 - filter((((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND "INST_ID"=USERENV('INSTANCE')))
                                19 - filter((((-"KZSPRPRV")=(-189) OR (-"KZSPRPRV")=(-190) OR (-"KZSPRPRV")=(-191) OR (-"KZSPRPRV")=(-192)) AND "INST_ID"=USERENV('INSTANCE')))
                                20 - filter(((-"KZSPRPRV")=(-181) AND "INST_ID"=USERENV('INSTANCE')))
                                21 - filter((((-"KZSPRPRV")=(-200) OR (-"KZSPRPRV")=(-201) OR (-"KZSPRPRV")=(-202) OR (-"KZSPRPRV")=(-203) OR (-"KZSPRPRV")=(-204)) AND
                                            "INST_ID"=USERENV('INSTANCE')))
                                22 - filter((((-"KZSPRPRV")=(-251) OR (-"KZSPRPRV")=(-252) OR (-"KZSPRPRV")=(-253) OR (-"KZSPRPRV")=(-254)) AND "INST_ID"=USERENV('INSTANCE')))
                                23 - filter((((-"KZSPRPRV")=(-246) OR (-"KZSPRPRV")=(-247) OR (-"KZSPRPRV")=(-248) OR (-"KZSPRPRV")=(-249)) AND "INST_ID"=USERENV('INSTANCE')))
                                24 - filter(((-"KZSPRPRV")=(-265) AND "INST_ID"=USERENV('INSTANCE')))
                                25 - filter(((-"KZSPRPRV")=12 AND "INST_ID"=USERENV('INSTANCE')))
                                26 - filter((((-"KZSPRPRV")=(-222) OR (-"KZSPRPRV")=(-223)) AND "INST_ID"=USERENV('INSTANCE')))
                                27 - filter((((-"KZSPRPRV")=(-258) OR (-"KZSPRPRV")=(-259) OR (-"KZSPRPRV")=(-260) OR (-"KZSPRPRV")=(-261)) AND "INST_ID"=USERENV('INSTANCE')))
                                28 - filter((((-"KZSPRPRV")=(-177) OR (-"KZSPRPRV")=(-178)) AND "INST_ID"=USERENV('INSTANCE')))
                                29 - filter((((-"KZSPRPRV")=(-268) OR (-"KZSPRPRV")=(-267)) AND "INST_ID"=USERENV('INSTANCE')))
                                30 - filter((((-"KZSPRPRV")=(-265) OR (-"KZSPRPRV")=(-266)) AND "INST_ID"=USERENV('INSTANCE')))
                                31 - filter((((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND
                                            "INST_ID"=USERENV('INSTANCE')))
                                32 - filter((((-"KZSPRPRV")=(-205) OR (-"KZSPRPRV")=(-206) OR (-"KZSPRPRV")=(-207) OR (-"KZSPRPRV")=(-208)) AND "INST_ID"=USERENV('INSTANCE')))
                                33 - filter((((-"KZSPRPRV")=(-277) OR (-"KZSPRPRV")=(-278)) AND "INST_ID"=USERENV('INSTANCE')))
                                36 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
                                37 - access("S"."OBJ#"=:B1)
                              Querying DBA_OBJECTS produces a much more compact plan that executes faster:
                              -------------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                        | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                              -------------------------------------------------------------------------------------------------------------------------------------
                              |   1 |  SORT ORDER BY                   |             |      1 |    814 |   7329 |00:00:00.24 |    5353 |   407K|   407K|  361K (0)|
                              |   2 |   VIEW                           | DBA_OBJECTS |      1 |    814 |   7329 |00:00:00.21 |    5353 |       |       |          |
                              |   3 |    UNION-ALL                     |             |      1 |        |   7329 |00:00:00.21 |    5353 |       |       |          |
                              |*  4 |     FILTER                       |             |      1 |        |   7329 |00:00:00.19 |    5353 |       |       |          |
                              |*  5 |      HASH JOIN                   |             |      1 |  14668 |  14672 |00:00:00.09 |     196 |  1155K|  1155K| 1209K (0)|
                              |   6 |       TABLE ACCESS FULL          | USER$       |      1 |    157 |    157 |00:00:00.01 |      15 |       |       |          |
                              |*  7 |       TABLE ACCESS FULL          | OBJ$        |      1 |  14668 |  14672 |00:00:00.03 |     181 |       |       |          |
                              |*  8 |      TABLE ACCESS BY INDEX ROWID | IND$        |   2575 |      1 |   2409 |00:00:00.04 |    5157 |       |       |          |
                              |*  9 |       INDEX UNIQUE SCAN          | I_IND1      |   2575 |      1 |   2575 |00:00:00.02 |    2577 |       |       |          |
                              |* 10 |       TABLE ACCESS BY INDEX ROWID| SUM$        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 11 |        INDEX UNIQUE SCAN         | I_SUM$_1    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 12 |     FILTER                       |             |      1 |        |      0 |00:00:00.01 |       0 |       |       |          |
                              |  13 |      NESTED LOOPS                |             |      0 |      5 |      0 |00:00:00.01 |       0 |       |       |          |
                              |  14 |       INDEX FULL SCAN            | I_LINK1     |      0 |      5 |      0 |00:00:00.01 |       0 |       |       |          |
                              |  15 |       TABLE ACCESS CLUSTER       | USER$       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              |* 16 |        INDEX UNIQUE SCAN         | I_USER#     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                              -------------------------------------------------------------------------------------------------------------------------------------

                              Predicate Information (identified by operation id):
                              ---------------------------------------------------

                                 4 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (DECODE("O"."TYPE#",0,'NEXT
                                            OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
                                            BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
                                            PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
                                            RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
                                            GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'SECURITY
                                            PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB
                                            CLASS',69,'WINDOW',72,'WINDOW GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='PACKAGE' OR
                                            DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTIO
                                            N',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
                                            PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
                                            RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
                                            GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'SECURITY
                                            PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB
                                            CLASS',69,'WINDOW',72,'WINDOW GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='PROCEDURE' OR
                                            DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTIO
                                            N',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
                                            PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
                                            RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
                                            GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'SECURITY
                                            PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB
                                            CLASS',69,'WINDOW',72,'WINDOW GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='SEQUENCE' OR
                                            DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTIO
                                            N',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
                                            PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
                                            RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
                                            GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'SECURITY
                                            PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB
                                            CLASS',69,'WINDOW',72,'WINDOW GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='TABLE' OR
                                            DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTIO
                                            N',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
                                            PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
                                            RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
                                            SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSU)
                                 5 - access("O"."OWNER#"="U"."USER#")
                                 7 - filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL))
                                 8 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
                                            "I"."TYPE#"=9))
                                 9 - access("I"."OBJ#"=:B1)
                                10 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
                                11 - access("S"."OBJ#"=:B1)
                                12 - filter('DATABASE LINK'='FUNCTION')
                                16 - access("L"."OWNER#"="U"."USER#")
                              Example of using DBMS_XPLAN to retrieve actual timing:
                              http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

                              Charles Hooper
                              IT Manager/Oracle DBA
                              K&M Machine-Fabricating, Inc.
                              • 12. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                                Charles Hooper
                                Try this query (found here https://metalink.oracle.com/metalink/plsql/f?p=200:27:5000154048035945504::::p27_id,p27_show_header,p27_show_help:525959.996,1,1) :
                                SELECT 
                                  *
                                FROM
                                  SYS.TAB_STATS$
                                If it returns no rows, execute the following:
                                EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);
                                This might also work:
                                EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE); 
                                Charles Hooper
                                IT Manager/Oracle DBA
                                K&M Machine-Fabricating, Inc.
                                • 13. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                                  Eduardo Legatti
                                  Hi,

                                  Just an insight ... How many rows do you have in dba_recyclebin?

                                  Cheers

                                  Legatti
                                  • 14. Re: PERFORMANCE PROBLEM - SYS OBJECTS
                                    601585
                                    Found similar symptoms in metalink.
                                    Check metalink doc# 364822.1.

                                    The problem is that in brief, Oracle 10gR2 rephrased the definition of all_objects. This sometimes causes unwanted side effects.

                                    Check it yourself.

                                    Dion Cho
                                    1 2 Previous Next