Multiple SQL_IDs....why?

From: Guy Peleg <makleeengineering_at_gmail.com>
Date: Tue, 23 Feb 2010 06:39:21 -0800 (PST)
Message-ID: <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 Received on Tue Feb 23 2010 - 08:39:21 CST