Multiple SQL_IDs....why?
in [Oracle]
|
From: Guy Peleg on 23 Feb 2010 09:39 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
From: Michel Cadot on 23 Feb 2010 11:15 "Guy Peleg" <makleeengineering(a)gmail.com> a �crit dans le message de news: e7b40657-4e16-4079-936b-7be3536974cc(a)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
From: Guy Peleg on 23 Feb 2010 11:23 On Feb 23, 4:15 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "Guy Peleg" <makleeengineer...(a)gmail.com> a écrit dans le message de news: > e7b40657-4e16-4079-936b-7be353697...(a)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
From: Michel Cadot on 23 Feb 2010 12:14 "Guy Peleg" <makleeengineering(a)gmail.com> a �crit dans le message de news: 513e35ea-858b-4b3f-bc51-270b2e6bbaba(a)j6g2000vbd.googlegroups.com... On Feb 23, 4:15 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "Guy Peleg" <makleeengineer...(a)gmail.com> a �crit dans le message de news: > e7b40657-4e16-4079-936b-7be353697...(a)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
From: Guy Peleg on 24 Feb 2010 13:52
On Feb 23, 12:14 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "Guy Peleg" <makleeengineer...(a)gmail.com> a crit dans le message de news: > 513e35ea-858b-4b3f-bc51-270b2e6bb...(a)j6g2000vbd.googlegroups.com... > On Feb 23, 4:15 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > > > > > > > "Guy Peleg" <makleeengineer...(a)gmail.com> a crit dans le message de news: > > e7b40657-4e16-4079-936b-7be353697...(a)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? |