This discussion is archived
2 Replies Latest reply: Nov 3, 2012 3:40 AM by Srini Chavali-Oracle RSS

optimizer_index_cost_adj

user12207083 Newbie
Currently Being Moderated
Hi All,

Last week we upgraded our DB and Application

DB from 10.2.0.4 to 11.2.0.3
Appliction 11.5.10.2 to 12.1.3
OS AIX 6.1

After upgrade following query started taking long time.Explain plan also changed
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,
l.process_spid, f.audsid, s.SID, s.serial#,
NVL (f.start_time, NVL (r.start_time, l.start_time)) TIME,
usr.user_name, rsp.responsibility_name, frm.user_form_name,
s.inst_id
FROM fnd_responsibility_tl rsp,
fnd_form_tl frm,
fnd_user usr,
fnd_logins l,
fnd_login_responsibilities r,
fnd_login_resp_forms f,
gv$session s
WHERE r.login_id = f.login_id
AND r.login_resp_id = f.login_resp_id
AND l.login_id = r.login_id
AND l.end_time IS NULL
AND r.end_time IS NULL
AND f.end_time IS NULL
AND l.user_id = usr.user_id
AND r.responsibility_id = rsp.responsibility_id
AND r.resp_appl_id = rsp.application_id
AND rsp.LANGUAGE = USERENV ('LANG')
AND f.form_id = frm.form_id
AND f.form_appl_id = frm.application_id
AND frm.LANGUAGE = USERENV ('LANG')
AND f.audsid = s.audsid;
Execution Plan
----------------------------------------------------------
Plan hash value: 1631937643

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 6944 | 230 (1)| 00:00:03 |
| 1 | NESTED LOOPS | | 28 | 6944 | 230 (1)| 00:00:03 |
|* 2 | HASH JOIN | | 28 | 6832 | 230 (1)| 00:00:03 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 28 | 6412 | 212 (1)| 00:00:03 |
|* 5 | HASH JOIN | | 28 | 5208 | 156 (1)| 00:00:02 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 28 | 4116 | 133 (0)| 00:00:02 |
| 8 | NESTED LOOPS | | 28 | 3108 | 105 (0)| 00:00:02 |
| 9 | NESTED LOOPS | | 28 | 2016 | 84 (0)| 00:00:02 |
| 10 | NESTED LOOPS | | 28 | 756 | 0 (0)| 00:00:01 |
| 11 | FIXED TABLE FULL | X$KSLWT | 28 | 224 | 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_FORM_TL_U1 | 1 | | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | FND_FORM_TL | 1 | 36 | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | FND_RESPONSIBILITY_TL | 2551 | 99489 | 22 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | FND_LOGINS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | FND_LOGINS | 1 | 43 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | FND_USER | 1278 | 19170 | 17 (0)| 00:00:01 |
|* 23 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------


But in 10g its using indexes 'FND_USER_U1' and FND_RESPONIBILITIES_TL_U1'

But in 11g tough the indexes exist,its not ben used.Table statistics are up-to-date.

But after changing the parameter 'optimizer_index_cost_adj' from 100 to 20,its started using indexes.But in 10g also the value was 1000,but till it was using indexes.

Can someone explain why this indexes as not used in 11g while optimizer_index_cost_adj is set to 100 ...(while its working in 10g)

Execution plan after setting optimizer_index_cost_adj=20
Execution Plan
----------------------------------------------------------
Plan hash value: 1092556752

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 6944 | 50 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 28 | 6944 | 50 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 28 | 6832 | 50 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 28 | 6412 | 45 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 28 | 5208 | 34 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 28 | 4116 | 28 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 28 | 3108 | 22 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 28 | 2016 | 17 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 28 | 756 | 0 (0)| 00:00:01 |
| 9 | FIXED TABLE FULL | X$KSLWT | 28 | 224 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 | 19 | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| FND_LOGIN_RESP_FORMS | 1 | 45 | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | FND_LOGIN_RESP_FORMS_N2 | 1 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | FND_LOGIN_RESPONSIBILITIES | 1 | 39 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | FND_LOGIN_RESPONSIBILITIES_U1 | 1 | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | FND_FORM_TL | 1 | 36 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | FND_FORM_TL_U1 | 1 | | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | FND_RESPONSIBILITY_TL | 1 | 39 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | FND_RESPONSIBILITY_TL_U1 | 1 | | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | FND_LOGINS | 1 | 43 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | FND_LOGINS_U1 | 1 | | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 15 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 1 (0)| 00:00:01 |
|* 23 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 4 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------


Thanks
  • 1. Re: optimizer_index_cost_adj
    JohnWatson Guru
    Currently Being Moderated
    Going from 10.2.x to 11.2.0.3 I have often noticed two points:
    First, you must re-gather system statistics. If you run
    select * from sys.aux_stats$;
    what do you get? Have they ever been gathered? I suspect the 11.x makes more use of them than earlier releases.
    Secondly, the default for serialdirect_read is now auto, in 10g it was (I think) false. This can have a big effect on pushing the optimizer away from indexes.
  • 2. Re: optimizer_index_cost_adj
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post EBS related questions in the EBS forums - https://forums.oracle.com/forums/category.jspa?categoryID=3

    Pl confirm you followed all of the steps in this MOS Doc

    Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]

    Pl do not change init.ora parameters randomly - they need to be set as per this MOS Doc

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

    HTH
    Srini

Legend

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