This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 29, 2012 8:51 AM by Max Seleznev RSS

Oracle 11g with OPTIMIZER_MODE=RULE go faster!!

User393329 Newbie
Currently Being Moderated
I recently migrated Oracle 9.2.0.8 to Oracle 11g but the querys doesn't work as I hope.

The same query takes 3:20 min aprox using optimizer_mode=ALL_ROWS and 0:20 using optimizer_mode=RULE or using RULE hint.

The query in CBO makes a cartesian product between the indexes of the table.

This is one query and the "autrotrace on" log on Oracle 11g:

SELECT /*+ NO_INDEX (PK0004111303310) */MIN(BASE.ID_SCHED_TASK)+1 I
FROM M4RJS_SCHED_TASKS BASE
WHERE NOT EXISTS
(SELECT BASE2.ID_SCHED_TASK
FROM M4RJS_SCHED_TASKS BASE2
WHERE BASE2.ID_SCHED_TASK>BASE.ID_SCHED_TASK
AND BASE2.ID_SCHED_TASK<BASE.ID_SCHED_TASK+2)
ORDER BY 1 ASC


Execution Plan
----------------------------------------------------------
Plan hash value: 3937517195

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | | 328 (2)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 14 | | | |
| 2 | MERGE JOIN ANTI | | 495 | 6930 | | 328 (2)| 00:00:04 |
| 3 | INDEX FULL SCAN | PK0004111303310 | 49487 | 338K| | 119 (1)| 00:00:02 |
|* 4 | FILTER | | | | | | |
|* 5 | SORT JOIN | | 49487 | 338K| 1576K| 209 (2)| 00:00:03 |
| 6 | INDEX FAST FULL SCAN| PK0004111303310 | 49487 | 338K| | 33 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

4 - filter("BASE2"."ID_SCHED_TASK"<"BASE"."ID_SCHED_TASK"+2)
5 - access("BASE2"."ID_SCHED_TASK">"BASE"."ID_SCHED_TASK")
filter("BASE2"."ID_SCHED_TASK">"BASE"."ID_SCHED_TASK")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
242 consistent gets
8 physical reads
0 redo size
519 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed


Thanks to all !
  • 1. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    761512 Newbie
    Currently Being Moderated
    Three questions:

    1. When you analyze the both tables like
    SQL> ANALYZE table <tablename> compute statistics;

    Is the behaviour the same?

    2. When you give no hint in your select - is the behaviour the same?

    3. Why is your query not:

    SELECT MIN(BASE.ID_SCHED_TASK)+1 I
    FROM M4RJS_SCHED_TASKS BASE
    WHERE NOT EXISTS
    (SELECT BASE2.ID_SCHED_TASK
    FROM M4RJS_SCHED_TASKS BASE2
    WHERE BASE2.ID_SCHED_TASK = BASE.ID_SCHED_TASK +1)
    ORDER BY 1 ASC
  • 2. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    judging by execution stats, the plan you posted is the good one. What about the other plan, the bad one?

    Best regards,
    Nikolay
  • 3. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    User393329 Newbie
    Currently Being Moderated
    Sorry Mschnatt, I posted the wrong query, i was testing with HINTS, the correct query is your posted query.

    1* I analyzed the tables and the result is the same:

    This is the query and "autorace on" log using OPTIMIZER_MODE=RULE on Oracle 11g:

    SQL> R
    1 SELECT MIN(BASE.ID_SCHED_TASK)+1 I
    2 FROM M4RJS_SCHED_TASKS BASE
    3 WHERE NOT EXISTS
    4 (SELECT BASE2.ID_SCHED_TASK
    5 FROM M4RJS_SCHED_TASKS BASE2
    6 WHERE BASE2.ID_SCHED_TASK>BASE.ID_SCHED_TASK
    7 AND BASE2.ID_SCHED_TASK<BASE.ID_SCHED_TASK+2)
    8* ORDER BY 1 ASC

    I
    ----------
    2

    Elapsed: 00:00:00.33

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 795265574

    -------------------------------------------------
    | Id | Operation | Name |
    -------------------------------------------------
    | 0 | SELECT STATEMENT | |
    | 1 | SORT AGGREGATE | |
    |* 2 | FILTER | |
    | 3 | TABLE ACCESS FULL | M4RJS_SCHED_TASKS |
    |* 4 | INDEX RANGE SCAN | PK0004111303310 |
    -------------------------------------------------

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

    2 - filter( NOT EXISTS (SELECT 0 FROM "M4RJS_SCHED_TASKS" "BASE2"
    WHERE "BASE2"."ID_SCHED_TASK"<:B1+2 AND "BASE2"."ID_SCHED_TASK">:B2))
    4 - access("BASE2"."ID_SCHED_TASK">:B1 AND
    "BASE2"."ID_SCHED_TASK"<:B2+2)

    Note
    -----
    - rule based optimizer used (consider using cbo)

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    101509 consistent gets
    0 physical reads
    0 redo size
    519 bytes sent via SQL*Net to client
    524 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed



    This is the query and "autorace on" log using OPTIMIZER_MODE=ALL_ROWA on Oracle 11g:

    Elapsed: 00:03:14.78

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3937517195

    ----------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 12 | | 317 (2)| 00:00:04 |
    | 1 | SORT AGGREGATE | | 1 | 12 | | | |
    | 2 | MERGE JOIN ANTI | | 495 | 5940 | | 317 (2)| 00:00:04 |
    | 3 | INDEX FULL SCAN | PK0004111303310 | 49487 | 289K| | 119 (1)| 00:00:02 |
    |* 4 | FILTER | | | | | | |
    |* 5 | SORT JOIN | | 49487 | 289K| 1176K| 198 (3)| 00:00:03 |
    | 6 | INDEX FAST FULL SCAN| PK0004111303310 | 49487 | 289K| | 33 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------

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

    4 - filter("BASE2"."ID_SCHED_TASK"<"BASE"."ID_SCHED_TASK"+2)
    5 - access("BASE2"."ID_SCHED_TASK">"BASE"."ID_SCHED_TASK")
    filter("BASE2"."ID_SCHED_TASK">"BASE"."ID_SCHED_TASK")

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    242 consistent gets
    0 physical reads
    0 redo size
    519 bytes sent via SQL*Net to client
    524 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed


    3* This is an example query, the problem persist in other bigger queries.

    Thanks for you help
  • 4. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    User393329 Newbie
    Currently Being Moderated
    Hi Nikolay,

    The plan you have seen, it's de plan in 11g wiht all_rows configured. It must be the correct plan, but the time different is almos 3 min bigger than Oracle 9i execution plan. So those is my problem, the lost of performance in the migration...

    Thanks!
  • 5. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    something doesn't add up here:
    Elapsed: 00:03:14.78
    Execution Plan
    Plan hash value: 3937517195
    ...
    Statistics
    0 recursive calls
    0 db block gets
    242 consistent gets
    0 physical reads
    ...

    If there was only 242 consistent gets and 0 physical reads, then what was the query doing for over 3 minutes?! Can you check the wait events for that query -- e.g. using ASH (if you have the Diagnostic Pack License) or SQL trace with waits on?

    Best regards,
    Nikolay
  • 6. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    User393329 Newbie
    Currently Being Moderated
    Hi Nikolay,

    I can't see where exactly the query spends the time, but i take this "photo" after the query execution:

    NAME CLASS TIME_S AVG_MS order
    ---------------------------------------- --------------- ---------- ---------- ----------
    DB CPU SYS STAT 195.1 0 1
    SQL*Net message from client Idle 47.6 835.1 2
    SQL*Net message to client Network 0 0 3
    Disk file operations I/O User I/O 0 .1 4
    db file sequential read User I/O 0 .1 5

    I think it takes a long time doing the SORT JOIN.
  • 7. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    761512 Newbie
    Currently Being Moderated
    I still wondering why your subselect makes a (> and <) and do not directly ask for (= id+1) like my query does?
    With < and > no index is used, but with the equal sign it would do so if BASE2.ID_SCHED_TASK has an index or is primary key of the table.
  • 8. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    User393329 Newbie
    Currently Being Moderated
    i'm agree with you mschnatt, but this query is from develpment department and i don't know why they do this query... :-(

    If i change the code, i'm solving the problem of this query, but no the others...

    Thanks!
  • 9. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    it looks like mschnatt is right and the query is not performing well because of replacing equality with two inequalities. It's much harder for the optimizer to estimate the cardinality of a range predicate than of an equality one, and when there are two predicates instead of one, inaccuracies multiply.

    In addition to that, I suspect that this may cause additional problems when doing the join -- a couple of months ago I saw a similar case with a slow in-memory hash join, where the problem was because of non-equality predicate. I cannot say for sure (since you didn't provide enough diagnostic information), but there is some similarity.

    As for the solution -- just lock in the right plan using stored outlines or an SPM baseline.

    Best regards,
    Nikolay
  • 10. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    SomeoneElse Guru
    Currently Being Moderated
    ANALYZE table <tablename> compute statistics;
    The old Analyze statement should not be used to gather stats.

    It has been replaced by the dbms_stats package.
  • 11. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    Mohamed Houri Pro
    Currently Being Moderated
    Hi 924217,
    I recently migrated Oracle 9.2.0.8 to Oracle 11g but the querys doesn't work as I hope
    In such a situation I would have first proceed as follows
    11g> alter session set optimizer_features_enable=’9.2.0.8';
    11g> run the query again
    and would see if the performance is back to its acceptable response time


    Hi Nikolay,
    In addition to that, I suspect that this may cause additional problems when doing the join -- a couple of months ago I saw a similar case 
    with a slow in-memory hash join, where the  problem was because of non-equality predicate.
    But the hash join occurs only when the join condition is an equality. May be you are referring to a non-equality occuring in predicates that do not participate in the join condition

    right?

    Mohamed Houri
    www.hourim.wordpress.com
  • 12. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,
    Mohamed Houri wrote:
    Hi 924217,
    But the hash join occurs only when the join condition is an equality. May be you are referring to a non-equality occuring in predicates that do not participate in the join condition
    the join predicate contained both equality and non-equality parts. The equality predicate had very weak seletivity and served as an access predicate, the inequality predicate had very strong selectivity and served as a filter predicate. As a result, the hash join was similar to a cartesian join -- i.e. first it joined every row to almost every row in the second table, and then applied the filter predicate the the resulting huge data set.

    I'm not sure whether something similar is going on here -- it could be just superficial resemblance. Like I said, it's hard to say much without having proper diagnostic info.

    Best regards,
    Nikolay
  • 13. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    Max Seleznev Explorer
    Currently Being Moderated
    Could you, please, let us know how you collected the stats? What sample size did you use? Did you also collected histogram stats?

    There were so many changes in optimizer behavior between the 2 versions I would consider myself lucky to have one misbehaving query after upgrade. An advise to set OPTIMIZER_FEATURE_ENABLE to your previous version on a session level is definitely a good one. There's also a bunch of underscore parameters Oracle Support usually recommends in this kind of situations, but I'd rather doubt we're there yet.
  • 14. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
    User393329 Newbie
    Currently Being Moderated
    Hi Mohamed,

    Using the optimizer_enable_features='9.2.0.8' works perfectly.

    This could be a solution for my problem. But we want to maintain the features of 11g. But if it's not posible, we will follow your advice :)
1 2 Previous Next

Legend

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