Multiple SQL_IDs....why?

Giganews Newsgroups
Subject: Multiple SQL_IDs....why?
Posted by:  Guy Peleg (makleeengineeri…@gmail.com)
Date: Tue, 23 Feb 2010

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

Replies