Re: Multiple SQL_IDs....why?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 23 Feb 2010 17:15:33 +0100
Message-ID: <4b83ff23$0$9142$426a74cc_at_news.free.fr>


"Guy Peleg" <makleeengineering_at_gmail.com> a écrit dans le message de news: e7b40657-4e16-4079-936b-7be3536974cc_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 Received on Tue Feb 23 2010 - 10:15:33 CST

Original text of this message