Re: Multiple SQL_IDs....why?

From: Guy Peleg <makleeengineering_at_gmail.com>
Date: Tue, 23 Feb 2010 08:23:38 -0800 (PST)
Message-ID: <513e35ea-858b-4b3f-bc51-270b2e6bbaba_at_j6g2000vbd.googlegroups.com>

On Feb 23, 4:15 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Guy Peleg" <makleeengineer..._at_gmail.com> a écrit dans le message de news:
> e7b40657-4e16-4079-936b-7be353697..._at_s17g2000vbs.googlegroups.com...
> | Oracle 10.2.0.3 on solaris, running a select query on a test machine
> | takes one second, execution
> | plan shows that index range scan is used. On the production node same
> | statement takes more than
> | a minute and execution plan shows full table scan.
> |
> | On the production node, looking at V$SQL I can see that I have two
> | execution plans for the query, one
> | seems fast fast with index range scan and the slow one with full table
> | scan.
> |
> | I'm trying to understand why I have multiple versions of the same
> | statement.
> |
> | Any ideas?
> |
> |
> | SQL> select
> | sql_id,child_number,executions,optimizer_cost,optimizer_mode from v
> | $sql where sql_id='79tg4h3uhwncc';
> |
> | SQL_ID        CHILD_NUMBER EXECUTIONS OPTIMIZER_COST OPTIMIZER_
> | ------------- ------------ ---------- -------------- ----------
> | 79tg4h3uhwncc            0         94             49 ALL_ROWS
> | 79tg4h3uhwncc            1         60           4716 ALL_ROWS
> |
> | SQL> select child_number, bind_mismatch B, optimizer_mode_mismatch O
> | from v$sql_shared_cursor
> |  2      where sql_id='79tg4h3uhwncc';
> |
> | CHILD_NUMBER B O
> | ------------ - -
> |           0 N N
> |           1 N N
> |
> |
> | Not shown here but BIND_DATA, PARSING_SCHEMA_NAME are the same.
> |
> | Regards,
> |
> | Guy Peleg
> | Maklee Engineering

>

> There are about 40 mismatch columns, you queried only 2,
> have a look at the other ones.

>

> Regards
> Michel

ROLL_INVALIDATE_MISMATCH is set to Y, is it possible to determine why the execution plan
has changed?

SQL> select * from v$sql_shared_cursor where sql_id='79tg4h3uhwncc';

SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L

• ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

D B P C S R P T M B M R O P M F L
- - - - - - - - - - - - - - - - -
79tg4h3uhwncc 0000000532754E90 0000000532753F60 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

N N N N N N N N N N N N N N N N N

79tg4h3uhwncc 0000000532754E90 000000051BDF2DC0 1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

N N N N N N N N N N N Y N N N N N Received on Tue Feb 23 2010 - 10:23:38 CST