DB Optimizer: Oracle's SQL Tuning Pack, part 2

Above its the load produced by the same load. Its a query being run by 10 users 20 times with a sleep of random interval between 1-2 seconds.
The graph measures the load on the database. Green is CPU, blue is IO and the redish brown is concurrency.

First, on the left is the load of the original default execution plan.

Second, in the middle is the load after applying a profile from the SQL Tuning Advisor (STA).

Third, on the right is the load after having been tuned by DB Optimizer.

All this goes to show that the STA's profile can be sub-optimal even when a better plan exists.

Here is the view of the same load from the "Statistics" tab of the SQL Details in OEM 10g.

First, dark blue, is load from the default path.

Second, light blue is the load from the STA suggested profile.

Third is the load after tuning the query with DB Optimizer.

Here is the query, actually a Peoplesoft query,

SELECT
A.COMPANY,
A.PAYGROUP,
E.OFF_CYCLE,
E.SEPCHK_FLAG,
E.TAX_METHOD,
E.TAX_PERIODS,
C.RETROPAY_ERNCD,
SUM (C.AMOUNT_DIFF) SUM_AMOUNT
FROM
PS_PAY_CALENDAR A,
WB_JOB B,
WB_RETROPAY_EARNS C,
PS_RETROPAY_RQST D,
PS_RETROPAYPGM_TBL E
WHERE
A.RUN_ID = 'PD2' AND
A.PAY_CONFIRM_RUN = 'N' AND
B.COMPANY = A.COMPANY AND
B.PAYGROUP = A.PAYGROUP AND
E.OFF_CYCLE = A.PAY_OFF_CYCLE_CAL AND
B.EFFDT = (SELECT MAX (F.EFFDT)
FROM WB_JOB F
WHERE
  F.EMPLID = B.EMPLID AND
  F.EMPL_RCD# = B.EMPL_RCD# AND
  F.EFFDT < = A.PAY_END_DT) AND

       B.EFFSEQ = (SELECT MAX (G.EFFSEQ)
FROM WB_JOB G
WHERE
    G.EMPLID = B.EMPLID AND
    G.EMPL_RCD# = B.EMPL_RCD# AND
    G.EFFDT = B.EFFDT) AND
C.EMPLID = B.EMPLID AND
C.EMPL_RCD# = B.EMPL_RCD# AND
C.RETROPAY_PRCS_FLAG = 'C' AND
C.RETROPAY_LOAD_SW = 'Y' AND
D.RETROPAY_SEQ_NO = C.RETROPAY_SEQ_NO AND
E.RETROPAY_PGM_ID = D.RETROPAY_PGM_ID
GROUP BY
A.COMPANY,
A.PAYGROUP,
E.OFF_CYCLE,
E.SEPCHK_FLAG,
E.TAX_METHOD,
E.TAX_PERIODS,
C.RETROPAY_ERNCD;

For the courageous or bored with idle time, the dmp and SQL are available at

http://www.perfvision.com/ftp/tcf/TC1.zip

and for kicks, the query's VST diagram

Couple of thoughts questions in my mind.

First if the reason for picking sub-optimal path in the first place is due to a bug, then it would make some sense that the the STA would still have the same bug and make the same mistakes.

Second, I wonder if the STA is really trying to find better plans, or if it is trying to pull the statistics used by the optimizer more in line with the actual statistics that would happen when running the query along the lines of TCF or tuning by cardinality feedback. When I look at the hints in profile the are of the nature of skewing stats one way or another:

OPT_ESTIMATE(@"SEL$485D066A", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=12.28434396)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=3.049755426)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=15.63082791)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=5.133606627)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "A"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=37.50606343)
OPT_ESTIMATE(@"SEL$B186933D", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=9.626410845)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=3.896066538)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("E"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=9.626410845)
OPT_ESTIMATE(@"SEL$6E19F182", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)

The above profile "hints" are mostly concerning scaling the expected rows.

Speaking of TCF it's interesting to not that in DB Optimizer the TCF values of estimate verses actual come out closer in the optimize query verses the original.

Below are the explain plans for the default plan, plan with profile and the DB Optimizer tuned plan. Notice the "Orders of Magnitude" column. This column shows the orders of magnitude that the estimated rows differed from the actual rows per line in the execution plan. The larger the difference the bigger the discrepancy between what the optimizer expected and what actually happened

Default Plan and TCF