1 2 Previous Next 16 Replies Latest reply: Jun 27, 2013 7:10 AM by user12016307 RSS

    SLOW SQL ON ORACLE 11r2 fast on 9i

    802461
      Hello guru´s

      we have one sql that runs for more then 3 minutes on databases with oracle11r2 but the same sql finish in 1 second on oracle 9i databases
      the databases for oracle 11r2 normally are upgraded from 9i, i have tested on databases created on 11r2 already and the results are the same more then 3 or 4 minutes on 11r2.

      tested couple of different servers around with oracle 9i and always result is the same about 1 or 2 seconds to reply.

      all databases are running on hpux servers some itanium some riscs but makes no difference on results, doubled PGA already for testing and makes no difference also.

      sql is below:

      select * from (select null TABLE_CATALOG, decode (owner, 'PUBLIC', null, owner) TABLE_SCHEMA, object_name TABLE_NAME, decode(owner, 'SYS', decode(object_type,'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'SYSTEM', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'DMSYS', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'ORDSYS', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'EXFSYS', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'WMSYS', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'MDSYS', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'CTXSYS', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'OLAPSYS', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'WKSYS', decode(object_type, 'TABLE','SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), object_type)TABLE_TYPE, null TABLE_GUID, null DESCRIPTION, null TABLE_PROPID, created DATE_CREATED, last_ddl_time DATE_MODIFIED from all_objects where object_type in ('TABLE', 'VIEW') union select null table_catalog,decode (o2.owner,'PUBLIC', NULL, o2.owner) table_schema,o2.object_name table_name,o2.object_type table_type,null table_guid,null description,null table_propid,o2.created DATE_CREATED,o2.last_ddl_time DATE_MODIFIED from all_objects o2, all_objects o3,all_synonyms s where o2.object_type = 'SYNONYM' and (o3.object_type = 'TABLE' or o3.object_type = 'VIEW') and o2.owner = s.owner and o2.object_name = s.synonym_name and s.table_owner = o3.owner and s.table_name = o3.object_name) DBSCHEMA_TABLES order by 4 desc, 3 asc, 2 asc
        • 1. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
          phaeus
          Hello,
          can you post a execution plan from both versions?

          regards
          Peter
          • 2. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
            jgarry
            Also see what the plan becomes if you set the optimizer to rule for the session. I know, decremented, but you are looking at some objects that are special. I'm wondering if you are seeing an effect of system statistics. (search for bug in http://structureddata.org/2008/01/02/what-are-your-system-statistics/ )
            • 3. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
              JohnWatson
              I ran your query on my little laptop (release 11.2.0.3, memory_target=600m, Win32 dual core) and it returned 11885 rows in 17.46 seconds. Hope this comparison helps.
              • 4. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                phaeus
                Only for compare...the same query on my vm (11.2.0.3 OEL)

                12038 rows selected.
                Elapsed: 00:00:03.55

                memory_max_target          big integer 800M
                memory_target               big integer 800M

                Edited by: phaeus on 14.11.2012 23:54
                • 5. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                  Nikolay Savvinov
                  Hi,

                  in 11g you have plenty of performance tools to tell you what exactly the database is doing when running your query, like SQL Real Time Monitor (provided you have the Diagnostic Pack License, of course). You should use them to establish the root cause of the slowness, rather than randomly try things like doubling PGA.

                  Another thing you can do is force the 9i plan on 11g (display it using dbms_xplan.display(<sql_id>, null, 'outline') and then paste the outline into the query right after the SELECT, same way you would place a regular hint) and see whether it gives the same performance.

                  Best regards,
                  Nikolay
                  • 6. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                    Mohamed Houri
                    In such a situation the first thing I would have done is to execute the query in the new 11gR2 database but setting the optimizer back to its 9i version

                    something like this
                    11gR2> alter session set OPTIMIZER_FEATURES_ENABLE = '9.2.0.8'; -- set here you exact 9i release
                    11gR2> execute your query
                    If, thanks to this setting, you are back to your 9i execution time (few seconds) then you have a temporary workaround.

                    The next step would be to compare the 11gR2 execution plan (few minutes of execution time) to the 9i execution plan (few seconds) and look where things started to go wrong.

                    Hope this helps

                    Mohamed Houri
                    www.hourim.wordpress.com
                    • 7. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                      802461
                      hello thanks to reply, here are the results for explain on oracle9i ( 9.2.0.5), the plan for oracle11 is too big cannot past here even in separate message, about 374 lines

                      STATEMENT_ID,TIMESTAMP,REMARKS,OPERATION,OPTIONS,OBJECT_NODE,OBJECT_OWNER,OBJECT_NAME,OBJECT_INSTANCE,OBJECT_TYPE,OPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,POSITION,COST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,OTHER,DISTRIBUTION
                      ,15/11/2012 15:46:30,,SELECT STATEMENT,,,,,,,CHOOSE,,0,,,,,,,,,,,
                      ,15/11/2012 15:46:30,,SORT,ORDER BY,,,,,,,,1,0,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,VIEW,,,SYS,,1,,,,2,1,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,SORT,UNIQUE,,,,,,,,3,2,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,UNION-ALL,,,,,,,,,4,3,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,FILTER,,,,,,,,,5,4,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,OBJ$,6,,,,6,5,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,7,6,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,FULL,,SYS,USER$,7,,,,8,7,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,RANGE SCAN,,SYS,I_OBJ2,,UNIQUE,,2,9,7,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,IND$,8,,,,10,5,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,UNIQUE SCAN,,SYS,I_IND1,,UNIQUE,,1,11,10,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,OBJAUTH$,9,,,,12,5,3,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,13,12,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSRO,,,,,14,13,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,RANGE SCAN,,SYS,I_OBJAUTH2,,NON-UNIQUE,,2,15,13,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,16,5,4,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,17,5,5,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,18,5,6,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,19,5,7,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,20,5,8,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,21,5,9,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,22,5,10,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,23,5,11,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,24,5,12,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,25,5,13,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,26,5,14,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,27,5,15,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,28,5,16,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,29,5,17,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,30,5,18,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,31,5,19,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,32,5,20,,,,,,,,,
                      ,15/11/2012 15:46:30,,FILTER,,,,,,,,,33,4,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,OBJ$,128,,,,34,33,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,35,34,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,36,35,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,37,36,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,38,37,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,39,38,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,40,39,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,FULL,,SYS,OBJ$,64,,,,41,40,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,SYN$,63,,,,42,40,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,UNIQUE SCAN,,SYS,I_SYN1,,UNIQUE,,1,43,42,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,USER$,73,,,,44,39,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,UNIQUE SCAN,,SYS,I_USER1,,UNIQUE,,1,45,44,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,CLUSTER,,SYS,USER$,62,,,,46,38,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,UNIQUE SCAN,,SYS,I_USER#,,NON-UNIQUE,,1,47,46,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,USER$,129,,,,48,37,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,UNIQUE SCAN,,SYS,I_USER1,,UNIQUE,,1,49,48,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,OBJ$,72,,,,50,36,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,RANGE SCAN,,SYS,I_OBJ2,,UNIQUE,,3,51,50,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,RANGE SCAN,,SYS,I_OBJ2,,UNIQUE,,3,52,35,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,FILTER,,,,,,,,,53,33,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,54,53,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,55,54,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,USER$,67,,,,56,55,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,UNIQUE SCAN,,SYS,I_USER1,,UNIQUE,,1,57,56,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,OBJ$,66,,,,58,55,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,RANGE SCAN,,SYS,I_OBJ2,,UNIQUE,,2,59,58,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,RANGE SCAN,,SYS,I_OBJAUTH1,,UNIQUE,,1,60,54,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSRO,,,,,61,53,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,62,33,3,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,IND$,74,,,,63,33,4,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,UNIQUE SCAN,,SYS,I_IND1,,UNIQUE,,1,64,63,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,OBJAUTH$,75,,,,65,33,5,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,66,65,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSRO,,,,,67,66,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,RANGE SCAN,,SYS,I_OBJAUTH2,,NON-UNIQUE,,2,68,66,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,69,33,6,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,70,33,7,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,71,33,8,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,72,33,9,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,73,33,10,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,74,33,11,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,75,33,12,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,76,33,13,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,77,33,14,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,78,33,15,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,79,33,16,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,80,33,17,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,81,33,18,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,82,33,19,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,83,33,20,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,84,33,21,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,85,33,22,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,IND$,130,,,,86,33,23,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,UNIQUE SCAN,,SYS,I_IND1,,UNIQUE,,1,87,86,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,TABLE ACCESS,BY INDEX ROWID,,SYS,OBJAUTH$,131,,,,88,33,24,,,,,,,,,
                      ,15/11/2012 15:46:30,,NESTED LOOPS,,,,,,,,,89,88,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSRO,,,,,90,89,1,,,,,,,,,
                      ,15/11/2012 15:46:30,,INDEX,RANGE SCAN,,SYS,I_OBJAUTH2,,NON-UNIQUE,,2,91,89,2,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,92,33,25,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,93,33,26,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,94,33,27,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,95,33,28,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,96,33,29,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,97,33,30,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,98,33,31,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,99,33,32,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,100,33,33,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,101,33,34,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,102,33,35,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,103,33,36,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,104,33,37,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,105,33,38,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,106,33,39,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,107,33,40,,,,,,,,,
                      ,15/11/2012 15:46:30,,FIXED TABLE,FULL,,SYS,X$KZSPR,,,,,108,33,41,,,,,,,,,

                      Edited by: 799458 on Nov 15, 2012 7:07 AM
                      • 8. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                        802461
                        Hello , thanks to reply
                        using the optimizer settings to 9 as you suggest on oracle11 database i got really good reply 3s instead of 1min or more.

                        what could be done now ??

                        thanks a lot
                        • 9. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                          Mohamed Houri
                          Hello , thanks to reply
                          using the optimizer settings to 9 as you suggest on oracle11 database i got really good reply 3s instead of 1min or more.
                          
                          what could be done now ??
                          
                          thanks a lot
                          Ok now that you have a temporary work arround you should start investigating the problem.

                          I would suggest you to do this
                          11gR2> alter session set statistics_level=ALL;
                          11gR2> execute your query (with the optimizer feature set to 9i)
                          11gR2> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
                          This will give you the good execution plan

                          Then proceed to the same thing but with optimizer feature set to 11gR2
                          11gR2> alter session set statistics_level=ALL;
                          11gR2> execute your query (using the optimizer feature of the new database 11gR2)
                          11gR2> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
                          This will give you the bad execution plan.

                          Post back here the two plans for comparison and suggestions

                          Hope this helps

                          Mohamed Houri
                          www.hourim.wordpress.com
                          • 10. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                            Nikolay Savvinov
                            Hi,

                            you can generate the stored outline to force the good plan.

                            Best regards,
                            Nikolay
                            • 11. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                              Dom Brooks
                              Without adequate information or unless we can reproduce the same poblem, we can only guess.
                              Look at the execution plan and see if you can spot the critical differences.
                              Look for query transformation features.

                              See Oracle Support doc:
                              Data Dictionary View Queries are Slow - Potential Solutions [ID 222671.1]
                              • 12. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                                802461
                                hello, the good plan is the one i past above, the bad plan has around 180k characters can´t past here easy will need to cut in 10x around :P
                                • 13. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                                  JustinCave
                                  Have you gathered dictionary statistics in the 11.2 database?

                                  Justin
                                  • 14. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
                                    802461
                                    not we don´t get even on 9i but the sql gets immediate response after alter session set OPTIMIZER_FEATURES_ENABLE = '9.2.0.8';

                                    i got this workaround, but if i set back to 11.2.0.1 then slowness for this sql returns.

                                    thanks a lot

                                    Edited by: 799458 on Nov 15, 2012 1:26 PM
                                    1 2 Previous Next