5 Replies Latest reply: Nov 10, 2012 7:48 AM by Srini Chavali-Oracle RSS

    SYS table very slow after 11g upgrade

    user12207083
      Hi All,


      All my sys tables are very slow after my database upgrade from 10.2.0.4 to 11.2.0.3 on AIX 6.1

      For example
      select * from ALL_TAB_COLUMNS; -- taking 19 seconds in 11.2.0.3 and few millisec in 10.2.0.4

      I have deleted and updated fixed and dictionary table statistics , till I facing this issue

      Thanks
        • 1. Re: SYS table very slow after 11g upgrade
          Osama_Mustafa
          Read MOS notes :
          High execution time for SYS tables and views. [ID 215347.1]
          • 2. Re: SYS table very slow after 11g upgrade
            user12207083
            Note refers 9i Database and mine is 11.2.0.3

            And my optimizer_mode is ALL_ROW
            • 3. Re: SYS table very slow after 11g upgrade
              Srini Chavali-Oracle
              Pl confirm you followed all of the steps in the Upgrade Guide - http://docs.oracle.com/cd/E11882_01/server.112/e23633/toc.htm

              Create a trace for the select statement - see these threads

              When your query takes too long ...

              HOW TO: Post a SQL statement tuning request - template posting

              HTH
              Srini
              • 4. Re: SYS table very slow after 11g upgrade
                user12207083
                Hi Srini,

                Yes.. all steps where followed.

                I updated sys statistics and performacne impoved...but not same as before

                Explain plan after system statistic update:
                SQL> SELECT l.user_id, l.terminal_id, l.login_name, r.resp_appl_id,
                r.responsibility_id, f.form_id, f.form_appl_id, l.pid,
                2 3 l.process_spid, f.audsid, s.SID, s.serial#,
                4 NVL (f.start_time, NVL (r.start_time, l.start_time)) TIME,
                5 usr.user_name, rsp.responsibility_name, frm.user_form_name,
                6 s.inst_id
                7 FROM fnd_responsibility_tl rsp,
                8 fnd_form_tl frm,
                9 fnd_user usr,
                10 fnd_logins l,
                11 fnd_login_responsibilities r,
                12 fnd_login_resp_forms f,
                13 gv$session s
                14 WHERE r.login_id = f.login_id
                15 AND r.login_resp_id = f.login_resp_id
                16 AND l.login_id = r.login_id
                17 AND l.end_time IS NULL
                18 AND r.end_time IS NULL
                19 AND f.end_time IS NULL
                20 AND r.responsibility_id = rsp.responsibility_id
                21 AND r.resp_appl_id = rsp.application_id
                22 AND rsp.LANGUAGE = USERENV ('LANG')
                23 AND f.form_id = frm.form_id
                24 AND f.form_appl_id = frm.application_id
                25 AND frm.LANGUAGE = USERENV ('LANG')
                26 AND f.audsid = s.audsid
                27 AND l.user_id = usr.user_id;

                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 3794654726

                --------------------------------------------------------------------------------------------------------------------
                | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                --------------------------------------------------------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 231 | 57288 | 1450 (2)| 00:00:03 |
                |* 1 | HASH JOIN | | 231 | 57288 | 1450 (2)| 00:00:03 |
                |* 2 | HASH JOIN | | 231 | 48972 | 1402 (2)| 00:00:03 |
                |* 3 | HASH JOIN | | 231 | 39963 | 1372 (2)| 00:00:03 |
                | 4 | NESTED LOOPS | | | | | |
                | 5 | NESTED LOOPS | | 231 | 36498 | 1350 (2)| 00:00:03 |
                | 6 | NESTED LOOPS | | 231 | 26565 | 884 (2)| 00:00:02 |
                | 7 | NESTED LOOPS | | 231 | 17556 | 702 (2)| 00:00:02 |
                | 8 | NESTED LOOPS | | 231 | 7161 | 3 (100)| 00:00:01 |
                | 9 | NESTED LOOPS | | 231 | 2772 | 2 (100)| 00:00:01 |
                | 10 | FIXED TABLE FULL | X$KSLWT | 231 | 1848 | 1 (100)| 00:00:01 |
                |* 11 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 4 | 0 (0)| 00:00:01 |
                |* 12 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 | 19 | 0 (0)| 00:00:01 |
                |* 13 | TABLE ACCESS BY INDEX ROWID| FND_LOGIN_RESP_FORMS | 1 | 45 | 3 (0)| 00:00:01 |
                |* 14 | INDEX RANGE SCAN | FND_LOGIN_RESP_FORMS_N2 | 1 | | 2 (0)| 00:00:01 |
                |* 15 | TABLE ACCESS BY INDEX ROWID | FND_LOGIN_RESPONSIBILITIES | 1 | 39 | 1 (0)| 00:00:01 |
                |* 16 | INDEX UNIQUE SCAN | FND_LOGIN_RESPONSIBILITIES_U1 | 1 | | 0 (0)| 00:00:01 |
                |* 17 | INDEX UNIQUE SCAN | FND_LOGINS_U1 | 1 | | 1 (0)| 00:00:01 |
                |* 18 | TABLE ACCESS BY INDEX ROWID | FND_LOGINS | 1 | 43 | 2 (0)| 00:00:01 |
                | 19 | TABLE ACCESS FULL | FND_USER | 1280 | 19200 | 22 (5)| 00:00:01 |
                |* 20 | TABLE ACCESS FULL | FND_RESPONSIBILITY_TL | 2555 | 99645 | 29 (11)| 00:00:01 |
                |* 21 | TABLE ACCESS FULL | FND_FORM_TL | 4687 | 164K| 47 (13)| 00:00:01 |
                --------------------------------------------------------------------------------------------------------------------

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

                1 - access("F"."FORM_ID"="FRM"."FORM_ID" AND "F"."FORM_APPL_ID"="FRM"."APPLICATION_ID")
                2 - access("R"."RESPONSIBILITY_ID"="RSP"."RESPONSIBILITY_ID" AND
                "R"."RESP_APPL_ID"="RSP"."APPLICATION_ID")
                3 - access("L"."USER_ID"="USR"."USER_ID")
                11 - filter("W"."KSLWTEVT"="E"."INDX")
                12 - filter(BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND "S"."INDX"="W"."KSLWTSID")
                13 - filter("F"."END_TIME" IS NULL)
                14 - access("F"."AUDSID"="S"."KSUUDSES")
                15 - filter("R"."END_TIME" IS NULL)
                16 - access("R"."LOGIN_ID"="F"."LOGIN_ID" AND "R"."LOGIN_RESP_ID"="F"."LOGIN_RESP_ID")
                17 - access("L"."LOGIN_ID"="R"."LOGIN_ID")
                18 - filter("L"."END_TIME" IS NULL)
                20 - filter("RSP"."LANGUAGE"=USERENV('LANG'))
                21 - filter("FRM"."LANGUAGE"=USERENV('LANG'))


                My 10.2.0.4 explain is
                SQL> SELECT l.user_id, l.terminal_id, l.login_name, r.resp_appl_id,
                2 r.responsibility_id, f.form_id, f.form_appl_id, l.pid,
                3 l.process_spid, f.audsid, s.SID, s.serial#,
                4 NVL (f.start_time, NVL (r.start_time, l.start_time)) TIME,
                5 usr.user_name, rsp.responsibility_name, frm.user_form_name,
                6 s.inst_id
                7 FROM fnd_responsibility_tl rsp,
                8 fnd_form_tl frm,
                9 fnd_user usr,
                10 fnd_logins l,
                11 fnd_login_responsibilities r,
                12 fnd_login_resp_forms f,
                13 gv$session s
                14 WHERE r.login_id = f.login_id
                15 AND r.login_resp_id = f.login_resp_id
                16 AND l.login_id = r.login_id
                17 AND l.end_time IS NULL
                18 AND r.end_time IS NULL
                19 AND f.end_time IS NULL
                20 AND r.responsibility_id = rsp.responsibility_id
                21 AND r.resp_appl_id = rsp.application_id
                22 AND rsp.LANGUAGE = USERENV ('LANG')
                23 AND f.form_id = frm.form_id
                24 AND f.form_appl_id = frm.application_id
                25 AND frm.LANGUAGE = USERENV ('LANG')
                26 AND f.audsid = s.audsid
                27 AND l.user_id = usr.user_id;

                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 1918296388

                -------------------------------------------------------------------------------------------------------------------
                | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                -------------------------------------------------------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 1 | 317 | 10 (0)| 00:00:01 |
                | 1 | NESTED LOOPS | | 1 | 317 | 10 (0)| 00:00:01 |
                | 2 | NESTED LOOPS | | 1 | 278 | 9 (0)| 00:00:01 |
                | 3 | NESTED LOOPS | | 1 | 242 | 8 (0)| 00:00:01 |
                | 4 | NESTED LOOPS | | 1 | 227 | 7 (0)| 00:00:01 |
                | 5 | NESTED LOOPS | | 1 | 186 | 5 (0)| 00:00:01 |
                | 6 | NESTED LOOPS | | 1 | 147 | 3 (0)| 00:00:01 |
                | 7 | NESTED LOOPS | | 1 | 104 | 0 (0)| 00:00:01 |
                |* 8 | FIXED TABLE FULL | X$KSUSE | 1 | 91 | 0 (0)| 00:00:01 |
                |* 9 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
                |* 10 | TABLE ACCESS BY INDEX ROWID| FND_LOGIN_RESP_FORMS | 1 | 43 | 3 (0)| 00:00:01 |
                |* 11 | INDEX RANGE SCAN | FND_LOGIN_RESP_FORMS_N2 | 1 | | 2 (0)| 00:00:01 |
                |* 12 | TABLE ACCESS BY INDEX ROWID | FND_LOGIN_RESPONSIBILITIES | 1 | 39 | 2 (0)| 00:00:01 |
                |* 13 | INDEX UNIQUE SCAN | FND_LOGIN_RESPONSIBILITIES_U1 | 1 | | 1 (0)| 00:00:01 |
                |* 14 | TABLE ACCESS BY INDEX ROWID | FND_LOGINS | 1 | 41 | 2 (0)| 00:00:01 |
                |* 15 | INDEX UNIQUE SCAN | FND_LOGINS_U1 | 1 | | 1 (0)| 00:00:01 |
                | 16 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 15 | 1 (0)| 00:00:01 |
                |* 17 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
                | 18 | TABLE ACCESS BY INDEX ROWID | FND_FORM_TL | 1 | 36 | 1 (0)| 00:00:01 |
                |* 19 | INDEX UNIQUE SCAN | FND_FORM_TL_U1 | 1 | | 0 (0)| 00:00:01 |
                | 20 | TABLE ACCESS BY INDEX ROWID | FND_RESPONSIBILITY_TL | 1 | 39 | 1 (0)| 00:00:01 |
                |* 21 | INDEX UNIQUE SCAN | FND_RESPONSIBILITY_TL_U1 | 1 | | 0 (0)| 00:00:01 |
                -------------------------------------------------------------------------------------------------------------------

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

                8 - filter(BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
                9 - filter("S"."KSUSEOPC"="E"."INDX")
                10 - filter("F"."END_TIME" IS NULL)
                11 - access("F"."AUDSID"="S"."KSUUDSES")
                12 - filter("R"."END_TIME" IS NULL)
                13 - access("R"."LOGIN_ID"="F"."LOGIN_ID" AND "R"."LOGIN_RESP_ID"="F"."LOGIN_RESP_ID")
                14 - filter("L"."END_TIME" IS NULL)
                15 - access("L"."LOGIN_ID"="R"."LOGIN_ID")
                17 - access("L"."USER_ID"="USR"."USER_ID")
                19 - access("F"."FORM_APPL_ID"="FRM"."APPLICATION_ID" AND "F"."FORM_ID"="FRM"."FORM_ID" AND
                "FRM"."LANGUAGE"=USERENV('LANG'))
                21 - access("R"."RESP_APPL_ID"="RSP"."APPLICATION_ID" AND
                "R"."RESPONSIBILITY_ID"="RSP"."RESPONSIBILITY_ID" AND "RSP"."LANGUAGE"=USERENV('LANG'))

                Thanks
                • 5. Re: SYS table very slow after 11g upgrade
                  Srini Chavali-Oracle
                  This is an EBS database - pl post EBS version. For the database upgrade, have all of the steps in this MOS Doc completed without any errors ?

                  Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]
                  Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]

                  Have all required init.ora parameters for 11gR2 been set (or removed) correctly ? Pl see

                  bde_chk_cbo.sql - EBS initialization parameters - Healthcheck [ID 174605.1]

                  HTH
                  Srini