Oracle DB 10.2.0.3 on IBM AIX
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
Optimizer is favoring the hasj join and as result it doing full table scan on tables which could have been easliy accessed by index.when I am adding Nested loops hint then the plan is getting better and sql is performing better.
optimizer_index_caching and optimizer_index_cost_adj are set to default values, and as per most of the experts it shld be fine for OLTP environment, then why Oracle is not using Nested loop?
Below is the output from the trace file for the sql without and with HINT run from 2 different sessions.
SELECT
DISTINCT mirroredworkitem.workitemhandle AS workitemhandle,
mirroredworkitem.processinstancehandle
AS processinstancehandle,
mirroredworkitem.performablestate AS performablestate,
mirroredworkitem.assignee AS assignee,
mirroredworkitem.performer AS performer,
mirroredworkitem.NAME AS NAME,
mirroredworkitem.transactionid AS transactionid,
TRANSACTION.decisiondescription AS decisiondescription,
TRANSACTION.datecreated AS datecreated,
mirroredworkitem.applicationurl AS applicationurl,
mirroredworkitem.relativetargetdate AS targetdate,
MONTHS_BETWEEN (relativetargetdate, SYSDATE) AS deadlinepast,
mirroredworkitem.saveforlater AS saveforlater,
TRANSACTION.TYPE AS TYPE,
TRANSACTION.creditapplicationnumber
AS creditapplicationnumber,
consumersubject.firstname AS firstname,
consumersubject.lastname AS lastname,
consumersubject.ssn AS ssn, businesssubject.NAME AS name_1,
businesssubject.taxid AS taxid
FROM TRANSACTION TRANSACTION,
mirroredworkitem mirroredworkitem,
subject subject,
consumersubject consumersubject,
businesssubject businesssubject
WHERE mirroredworkitem.transactionid = TRANSACTION.transactionid
AND TRANSACTION.transactionid = subject.transactionid
AND subject.subjecttype <> 'principal'
AND subject.subjectid = consumersubject.subjectid(+)
AND subject.subjectid = businesssubject.subjectid(+)
AND (( ( mirroredworkitem.performer = 'A'
OR mirroredworkitem.performablestate =
'open.running.ready'
)
AND mirroredworkitem.roleid IN ('XYZ', 'ABC')
)
)
AND (( mirroredworkitem.saveforlater IS NOT NULL
AND mirroredworkitem.saveforlater <> 0
)
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1914 10.82 64.21 85825 349473 0 28695
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1916 10.82 64.23 85825 349473 0 28695
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 123
Rows Row Source Operation
------- ---------------------------------------------------
28695 HASH UNIQUE (cr=349473 pr=85825 pw=1035 time=63817502 us)
28695 NESTED LOOPS OUTER (cr=349473 pr=84790 pw=0 time=61706267 us)
28695 HASH JOIN OUTER (cr=261563 pr=84790 pw=0 time=61218222 us)
28695 HASH JOIN (cr=248376 pr=81551 pw=0 time=133774418 us)
28695 NESTED LOOPS (cr=119676 pr=2963 pw=0 time=1731352 us)
28695 TABLE ACCESS FULL MIRROREDWORKITEM (cr=3306 pr=2963 pw=0 time=1157438 us)
28695 TABLE ACCESS BY INDEX ROWID TRANSACTION (cr=116370 pr=0 pw=0 time=548234 us)
28695 INDEX UNIQUE SCAN PKEY_TRANSACTIONTRANSACTIONID (cr=86085 pr=0 pw=0 time=319156 us)(object id 22820)
18626567 TABLE ACCESS FULL SUBJECT (cr=128700 pr=78588 pw=0 time=45192474 us)
583862 TABLE ACCESS FULL BUSINESSSUBJECT (cr=13187 pr=3239 pw=0 time=2733997 us)
1825 TABLE ACCESS BY INDEX ROWID CONSUMERSUBJECT (cr=87910 pr=0 pw=0 time=403038 us)
1825 INDEX UNIQUE SCAN PKEY_CONSUMERSUBJECTSUBJECTID (cr=86085 pr=0 pw=0 time=337337 us)(object id 22405)
SELECT /*+ USE_NL(TRANSACTION,subject) index(subject) */
DISTINCT mirroredworkitem.workitemhandle AS workitemhandle,
mirroredworkitem.processinstancehandle
AS processinstancehandle,
mirroredworkitem.performablestate AS performablestate,
mirroredworkitem.assignee AS assignee,
mirroredworkitem.performer AS performer,
mirroredworkitem.NAME AS NAME,
mirroredworkitem.transactionid AS transactionid,
TRANSACTION.decisiondescription AS decisiondescription,
TRANSACTION.datecreated AS datecreated,
mirroredworkitem.applicationurl AS applicationurl,
mirroredworkitem.relativetargetdate AS targetdate,
MONTHS_BETWEEN (relativetargetdate, SYSDATE) AS deadlinepast,
mirroredworkitem.saveforlater AS saveforlater,
TRANSACTION.TYPE AS TYPE,
TRANSACTION.creditapplicationnumber
AS creditapplicationnumber,
consumersubject.firstname AS firstname,
consumersubject.lastname AS lastname,
consumersubject.ssn AS ssn, businesssubject.NAME AS name_1,
businesssubject.taxid AS taxid
FROM TRANSACTION TRANSACTION,
mirroredworkitem mirroredworkitem,
subject subject,
consumersubject consumersubject,
businesssubject businesssubject
WHERE mirroredworkitem.transactionid = TRANSACTION.transactionid
AND TRANSACTION.transactionid = subject.transactionid
AND subject.subjecttype <> 'principal'
AND subject.subjectid = consumersubject.subjectid(+)
AND subject.subjectid = businesssubject.subjectid(+)
AND (( ( mirroredworkitem.performer = 'A'
OR mirroredworkitem.performablestate =
'open.running.ready'
)
AND mirroredworkitem.roleid IN ('XYZ', 'ABC')
)
)
AND (( mirroredworkitem.saveforlater IS NOT NULL
AND mirroredworkitem.saveforlater <> 0
)
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1915 2.59 30.50 22235 339782 0 28697
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1917 2.60 30.52 22235 339782 0 28697
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 123
Rows Row Source Operation
------- ---------------------------------------------------
28697 HASH UNIQUE (cr=339782 pr=22235 pw=1050 time=30071301 us)
28697 NESTED LOOPS OUTER (cr=339782 pr=21185 pw=0 time=26745183 us)
28697 HASH JOIN OUTER (cr=251865 pr=21143 pw=0 time=25826878 us)
28697 TABLE ACCESS BY INDEX ROWID SUBJECT (cr=238685 pr=8123 pw=0 time=22126618 us)
77957 NESTED LOOPS (cr=234815 pr=5819 pw=0 time=42876104 us)
28697 NESTED LOOPS (cr=119684 pr=2970 pw=0 time=1652746 us)
28697 TABLE ACCESS FULL MIRROREDWORKITEM (cr=3306 pr=2966 pw=0 time=791819 us)
28697 TABLE ACCESS BY INDEX ROWID TRANSACTION (cr=116378 pr=4 pw=0 time=630894 us)
28697 INDEX UNIQUE SCAN PKEY_TRANSACTIONTRANSACTIONID (cr=86091 pr=2 pw=0 time=372007 us)(object id 22820)
49259 INDEX RANGE SCAN SUBJECT_TRANSACTIONID_SUBJECT (cr=115131 pr=2849 pw=0 time=14200354 us)(object id 22775)
583823 TABLE ACCESS FULL BUSINESSSUBJECT (cr=13180 pr=13020 pw=0 time=5442767 us)
1826 TABLE ACCESS BY INDEX ROWID CONSUMERSUBJECT (cr=87917 pr=42 pw=0 time=524136 us)
1826 INDEX UNIQUE SCAN PKEY_CONSUMERSUBJECTSUBJECTID (cr=86091 pr=25 pw=0 time=440455 us)(object id 22405)