Re: Multiple SQL_IDs....why?

From: Guy Peleg <makleeengineering_at_gmail.com>
Date: Wed, 24 Feb 2010 10:52:07 -0800 (PST)
Message-ID: <6585598e-5ec2-45f4-9246-81e5a0dab9e6_at_v25g2000yqk.googlegroups.com>



On Feb 23, 12:14pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Guy Peleg" <makleeengineer..._at_gmail.com> a crit dans le message de news:
> 513e35ea-858b-4b3f-bc51-270b2e6bb..._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
>
> ----------------------------------------------------------------------------------------------
>
> Read the following thread, abovie last but one Dion Cho's post.http://forums.oracle.com/forums/thread.jspa?threadID=592771
>
> Regards
> Michel- Hide quoted text -
>
> - Show quoted text -

Something still does not make sense to me. I understand that the execution plan was being invalidated,
most probably after statistics was collected at 10pm.

The database was started Monday morning. Throughout Monday, it was using the fast execution plan, Tuesday it switched to the slow execution plan and today it switched back to the fast version. The table this query operate against has 700K rows and it may grow by ~10,000 per day. So why would we switch back and forth between these plans? Received on Wed Feb 24 2010 - 12:52:07 CST

Original text of this message