This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jun 27, 2013 5:10 AM by user12016307 RSS

SLOW SQL ON ORACLE 11r2 fast on 9i

802461 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    Hello,
    can you post a execution plan from both versions?

    regards
    Peter
  • 2. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
    jgarry Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    Have you gathered dictionary statistics in the 11.2 database?

    Justin
  • 14. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
    802461 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points