RE: Changed execution plans..
Tim, Thanks for your thoughts. I'm thinking that the import and the resulting changes is likely the issue. RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Monday, January 20, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Robert, Just thought I'd ask, since it hasn't been mentioned in the thread previously, but... ...has query performance worsened or improved, or is any change unnoticeable? What is the before- and after-readings for elapsed time? After all, if the rows are packed more "densely" into the blocks, then perhaps an FTS is not such a bad plan... Also, don't forget that the import may have changed the physical order of the rows in the table, so now the "clustering factor" of the various indexes may have changed enough to the point where the CBO (correctly!) decided that using the index for a RANGE SCAN may not be optimal. Since CLUFAC is judged on a scale between the DBA_TABLES.BLOCKS and DBA_TABLES.NUM_ROWS, even a CLUFAC which hasn't changed much (due to the table being reimported with rows in the same physical order) would still be greatly affected by a change in DBA_TABLES.BLOCKS... Thanks! -Tim - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, January 19, 2003 9:28 PM > Thanks Cary... I've actually considered most of those already. This isn't my > database, I'm coming in to help someone else. In this case, the database > schema was accidentally dropped, and recovered from an export. I'm wondering > if the import might have created blocks that are packed more densely and > that this might be the cause of the problem. I'm told the parameters are the > same, that the physical structure is the same, they are not using stored > outlines and the SQL has not been changed. No patches have been applied, so > it's apples for apples with the exception of the statistics and, possibly, > the data density. They have some old stored statistics that they generated > pre-schema drop that they are supposed to send me, so I'm going to look at > that tomorrow and run a 10053 trace on one of the changed queries and see > what I can find. I was just wondering if I could be missing something > obvious. Seems like that is just the way, it's the obvious things that > get missed... :-) > > Thanks so much for your comments! > > RF > > -Original Message- > Millsap > Sent: Sunday, January 19, 2003 8:54 PM > To: Multiple recipients of list ORACLE-L > > > Robert, > > Seven reasons I can think of include changes to: > > 1. Oracle instance parameter values (changes when you edit the > parameters, whether in the stored "init.ora" way, or via ALTER SYSTEM or > ALTER SESSION commands) > 2. Database table and index statistics (changes, e.g., when you run > dbms_stats.gather_database_stats) > 3. System CPU and I/O statistics (changes, e.g., when you run > dbms_stats.gather_system_stats) > 4. Database schema configuration (changes when you create/drop indexes, > etc.) > 5. Stored outlines (changes when you create or reassign outlines) > 6. SQL text (changes when you manipulate the application SQL) > 7. Oracle query cost model (changes when you upgrade or patch your > Oracle kernel) > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - 2003 Hotsos Symposium, Feb 9-12 Dallas > - RMOUG Training Days 2003, Mar 5-6 Denver > - Hotsos Clinic 101, Mar 26-28 London > > > -Original Message- > Freeman > Sent: Sunday, January 19, 2003 1:24 AM > To: Multiple recipients of list ORACLE-L > > Hey Ya'all... (still got my southern roots even up here in Chicago!) > > Anyone want to throw in some possible reasons why an execution plan > might > change for a given table queryThis is on Oracle9iR2 on SUN. I've > looked > at the obvious causes: > > 1. Object has changed - Appears not to have changed. > 2. Database parameters have changed - Appears that no parameters have > changed. > 3. Statistics (data volumes, distribution, cardinality, etc) have > changed - > Still looking into this, but the volumes have not changed dramatically > even > if they have changed. > 4. Other physical database changes. > > None of these seem to apply. I've got a database that a few weeks ago > were > doing indexed > lookups using a partitioned index on a partitioned table. Now, it seems > that > these queries > are doing full table scans on this partitioned table. I'm still > gathering up > the details for the items above (e.g how much have the objects changed) > and > I'll probably run a 10053 trace on one of the bad queries to see what > the > optimizer is doing on Monday, but I'd like to just poll for some > additional > ideas. I *AM* getting partition elimination (thank goodness) but I've > got > two FTS on one partition of this table that a
RE: Changed execution plans..
Oh, and to answer your question directly: >>...has query performance worsened or improved, or is any change >> unnoticeable? What is the before- and after-readings for elapsed time? I do not have hard facts (old documented response times or execution plans) to be able to say for sure. What I do have is are statements that it is slower, much slower. I do have the old statistics which they will be sending me. All in all, I felt certain that it was the results of the import and after this thread I'm feeling more confident about that assessment. RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Monday, January 20, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Robert, Just thought I'd ask, since it hasn't been mentioned in the thread previously, but... ...has query performance worsened or improved, or is any change unnoticeable? What is the before- and after-readings for elapsed time? After all, if the rows are packed more "densely" into the blocks, then perhaps an FTS is not such a bad plan... Also, don't forget that the import may have changed the physical order of the rows in the table, so now the "clustering factor" of the various indexes may have changed enough to the point where the CBO (correctly!) decided that using the index for a RANGE SCAN may not be optimal. Since CLUFAC is judged on a scale between the DBA_TABLES.BLOCKS and DBA_TABLES.NUM_ROWS, even a CLUFAC which hasn't changed much (due to the table being reimported with rows in the same physical order) would still be greatly affected by a change in DBA_TABLES.BLOCKS... Thanks! -Tim - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, January 19, 2003 9:28 PM > Thanks Cary... I've actually considered most of those already. This isn't my > database, I'm coming in to help someone else. In this case, the database > schema was accidentally dropped, and recovered from an export. I'm wondering > if the import might have created blocks that are packed more densely and > that this might be the cause of the problem. I'm told the parameters are the > same, that the physical structure is the same, they are not using stored > outlines and the SQL has not been changed. No patches have been applied, so > it's apples for apples with the exception of the statistics and, possibly, > the data density. They have some old stored statistics that they generated > pre-schema drop that they are supposed to send me, so I'm going to look at > that tomorrow and run a 10053 trace on one of the changed queries and see > what I can find. I was just wondering if I could be missing something > obvious. Seems like that is just the way, it's the obvious things that > get missed... :-) > > Thanks so much for your comments! > > RF > > -Original Message- > Millsap > Sent: Sunday, January 19, 2003 8:54 PM > To: Multiple recipients of list ORACLE-L > > > Robert, > > Seven reasons I can think of include changes to: > > 1. Oracle instance parameter values (changes when you edit the > parameters, whether in the stored "init.ora" way, or via ALTER SYSTEM or > ALTER SESSION commands) > 2. Database table and index statistics (changes, e.g., when you run > dbms_stats.gather_database_stats) > 3. System CPU and I/O statistics (changes, e.g., when you run > dbms_stats.gather_system_stats) > 4. Database schema configuration (changes when you create/drop indexes, > etc.) > 5. Stored outlines (changes when you create or reassign outlines) > 6. SQL text (changes when you manipulate the application SQL) > 7. Oracle query cost model (changes when you upgrade or patch your > Oracle kernel) > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - 2003 Hotsos Symposium, Feb 9-12 Dallas > - RMOUG Training Days 2003, Mar 5-6 Denver > - Hotsos Clinic 101, Mar 26-28 London > > > -Original Message- > Freeman > Sent: Sunday, January 19, 2003 1:24 AM > To: Multiple recipients of list ORACLE-L > > Hey Ya'all... (still got my southern roots even up here in Chicago!) > > Anyone want to throw in some possible reasons why an execution plan > might > change for a given table queryThis is on Oracle9iR2 on SUN. I've > looked > at the obvious causes: > > 1. Object has changed - Appears not to have changed. > 2. Database parameters have changed - Appears that no parameters have > changed. > 3. Statistics (data volumes, distribution, cardinality, etc) have > changed - > Still looking into this, but the volumes have not changed dramatically > even > if they have changed. > 4. Other physical database changes. > > None of these seem to apply. I've got a database that a few weeks ago > were > doing indexed > lookups using a partitioned index on a partitioned table. Now, it seems > that > these q
Re: Changed execution plans..
Robert, Just thought I'd ask, since it hasn't been mentioned in the thread previously, but... ...has query performance worsened or improved, or is any change unnoticeable? What is the before- and after-readings for elapsed time? After all, if the rows are packed more "densely" into the blocks, then perhaps an FTS is not such a bad plan... Also, don't forget that the import may have changed the physical order of the rows in the table, so now the "clustering factor" of the various indexes may have changed enough to the point where the CBO (correctly!) decided that using the index for a RANGE SCAN may not be optimal. Since CLUFAC is judged on a scale between the DBA_TABLES.BLOCKS and DBA_TABLES.NUM_ROWS, even a CLUFAC which hasn't changed much (due to the table being reimported with rows in the same physical order) would still be greatly affected by a change in DBA_TABLES.BLOCKS... Thanks! -Tim - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, January 19, 2003 9:28 PM > Thanks Cary... I've actually considered most of those already. This isn't my > database, I'm coming in to help someone else. In this case, the database > schema was accidentally dropped, and recovered from an export. I'm wondering > if the import might have created blocks that are packed more densely and > that this might be the cause of the problem. I'm told the parameters are the > same, that the physical structure is the same, they are not using stored > outlines and the SQL has not been changed. No patches have been applied, so > it's apples for apples with the exception of the statistics and, possibly, > the data density. They have some old stored statistics that they generated > pre-schema drop that they are supposed to send me, so I'm going to look at > that tomorrow and run a 10053 trace on one of the changed queries and see > what I can find. I was just wondering if I could be missing something > obvious. Seems like that is just the way, it's the obvious things that > get missed... :-) > > Thanks so much for your comments! > > RF > > -Original Message- > Millsap > Sent: Sunday, January 19, 2003 8:54 PM > To: Multiple recipients of list ORACLE-L > > > Robert, > > Seven reasons I can think of include changes to: > > 1. Oracle instance parameter values (changes when you edit the > parameters, whether in the stored "init.ora" way, or via ALTER SYSTEM or > ALTER SESSION commands) > 2. Database table and index statistics (changes, e.g., when you run > dbms_stats.gather_database_stats) > 3. System CPU and I/O statistics (changes, e.g., when you run > dbms_stats.gather_system_stats) > 4. Database schema configuration (changes when you create/drop indexes, > etc.) > 5. Stored outlines (changes when you create or reassign outlines) > 6. SQL text (changes when you manipulate the application SQL) > 7. Oracle query cost model (changes when you upgrade or patch your > Oracle kernel) > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - 2003 Hotsos Symposium, Feb 9-12 Dallas > - RMOUG Training Days 2003, Mar 5-6 Denver > - Hotsos Clinic 101, Mar 26-28 London > > > -Original Message- > Freeman > Sent: Sunday, January 19, 2003 1:24 AM > To: Multiple recipients of list ORACLE-L > > Hey Ya'all... (still got my southern roots even up here in Chicago!) > > Anyone want to throw in some possible reasons why an execution plan > might > change for a given table queryThis is on Oracle9iR2 on SUN. I've > looked > at the obvious causes: > > 1. Object has changed - Appears not to have changed. > 2. Database parameters have changed - Appears that no parameters have > changed. > 3. Statistics (data volumes, distribution, cardinality, etc) have > changed - > Still looking into this, but the volumes have not changed dramatically > even > if they have changed. > 4. Other physical database changes. > > None of these seem to apply. I've got a database that a few weeks ago > were > doing indexed > lookups using a partitioned index on a partitioned table. Now, it seems > that > these queries > are doing full table scans on this partitioned table. I'm still > gathering up > the details for the items above (e.g how much have the objects changed) > and > I'll probably run a 10053 trace on one of the bad queries to see what > the > optimizer is doing on Monday, but I'd like to just poll for some > additional > ideas. I *AM* getting partition elimination (thank goodness) but I've > got > two FTS on one partition of this table that are just killing it. They > want > to quantify the reason why this access has changed so I'm trying to > think of > what kinds of stuff I can look at to try to do this. > > I will add that this table was just rebuilt recently (through > imp/exp)... > can the change in row to block density make the difference h > > Any ideas?? > > RF > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net
RE: Changed execution plans..
Yes, be we recomputed statistics afterwards... RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Monday, January 20, 2003 7:39 AM To: Multiple recipients of list ORACLE-L Robert, Was imp run with default options? The way it computes stats after table imports, may not be appropriate. - Kirti -Original Message- Sent: Sunday, January 19, 2003 10:29 PM To: Multiple recipients of list ORACLE-L Thanks Cary... I've actually considered most of those already. This isn't my database, I'm coming in to help someone else. In this case, the database schema was accidentally dropped, and recovered from an export. I'm wondering if the import might have created blocks that are packed more densely and that this might be the cause of the problem. I'm told the parameters are the same, that the physical structure is the same, they are not using stored outlines and the SQL has not been changed. No patches have been applied, so it's apples for apples with the exception of the statistics and, possibly, the data density. They have some old stored statistics that they generated pre-schema drop that they are supposed to send me, so I'm going to look at that tomorrow and run a 10053 trace on one of the changed queries and see what I can find. I was just wondering if I could be missing something obvious. Seems like that is just the way, it's the obvious things that get missed... :-) Thanks so much for your comments! RF -Original Message- Millsap Sent: Sunday, January 19, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Robert, Seven reasons I can think of include changes to: 1. Oracle instance parameter values (changes when you edit the parameters, whether in the stored "init.ora" way, or via ALTER SYSTEM or ALTER SESSION commands) 2. Database table and index statistics (changes, e.g., when you run dbms_stats.gather_database_stats) 3. System CPU and I/O statistics (changes, e.g., when you run dbms_stats.gather_system_stats) 4. Database schema configuration (changes when you create/drop indexes, etc.) 5. Stored outlines (changes when you create or reassign outlines) 6. SQL text (changes when you manipulate the application SQL) 7. Oracle query cost model (changes when you upgrade or patch your Oracle kernel) Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- Freeman Sent: Sunday, January 19, 2003 1:24 AM To: Multiple recipients of list ORACLE-L Hey Ya'all... (still got my southern roots even up here in Chicago!) Anyone want to throw in some possible reasons why an execution plan might change for a given table queryThis is on Oracle9iR2 on SUN. I've looked at the obvious causes: 1. Object has changed - Appears not to have changed. 2. Database parameters have changed - Appears that no parameters have changed. 3. Statistics (data volumes, distribution, cardinality, etc) have changed - Still looking into this, but the volumes have not changed dramatically even if they have changed. 4. Other physical database changes. None of these seem to apply. I've got a database that a few weeks ago were doing indexed lookups using a partitioned index on a partitioned table. Now, it seems that these queries are doing full table scans on this partitioned table. I'm still gathering up the details for the items above (e.g how much have the objects changed) and I'll probably run a 10053 trace on one of the bad queries to see what the optimizer is doing on Monday, but I'd like to just poll for some additional ideas. I *AM* getting partition elimination (thank goodness) but I've got two FTS on one partition of this table that are just killing it. They want to quantify the reason why this access has changed so I'm trying to think of what kinds of stuff I can look at to try to do this. I will add that this table was just rebuilt recently (through imp/exp)... can the change in row to block density make the difference h Any ideas?? RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the messag
RE: Changed execution plans..
Robert, Was imp run with default options? The way it computes stats after table imports, may not be appropriate. - Kirti -Original Message- Sent: Sunday, January 19, 2003 10:29 PM To: Multiple recipients of list ORACLE-L Thanks Cary... I've actually considered most of those already. This isn't my database, I'm coming in to help someone else. In this case, the database schema was accidentally dropped, and recovered from an export. I'm wondering if the import might have created blocks that are packed more densely and that this might be the cause of the problem. I'm told the parameters are the same, that the physical structure is the same, they are not using stored outlines and the SQL has not been changed. No patches have been applied, so it's apples for apples with the exception of the statistics and, possibly, the data density. They have some old stored statistics that they generated pre-schema drop that they are supposed to send me, so I'm going to look at that tomorrow and run a 10053 trace on one of the changed queries and see what I can find. I was just wondering if I could be missing something obvious. Seems like that is just the way, it's the obvious things that get missed... :-) Thanks so much for your comments! RF -Original Message- Millsap Sent: Sunday, January 19, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Robert, Seven reasons I can think of include changes to: 1. Oracle instance parameter values (changes when you edit the parameters, whether in the stored "init.ora" way, or via ALTER SYSTEM or ALTER SESSION commands) 2. Database table and index statistics (changes, e.g., when you run dbms_stats.gather_database_stats) 3. System CPU and I/O statistics (changes, e.g., when you run dbms_stats.gather_system_stats) 4. Database schema configuration (changes when you create/drop indexes, etc.) 5. Stored outlines (changes when you create or reassign outlines) 6. SQL text (changes when you manipulate the application SQL) 7. Oracle query cost model (changes when you upgrade or patch your Oracle kernel) Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- Freeman Sent: Sunday, January 19, 2003 1:24 AM To: Multiple recipients of list ORACLE-L Hey Ya'all... (still got my southern roots even up here in Chicago!) Anyone want to throw in some possible reasons why an execution plan might change for a given table queryThis is on Oracle9iR2 on SUN. I've looked at the obvious causes: 1. Object has changed - Appears not to have changed. 2. Database parameters have changed - Appears that no parameters have changed. 3. Statistics (data volumes, distribution, cardinality, etc) have changed - Still looking into this, but the volumes have not changed dramatically even if they have changed. 4. Other physical database changes. None of these seem to apply. I've got a database that a few weeks ago were doing indexed lookups using a partitioned index on a partitioned table. Now, it seems that these queries are doing full table scans on this partitioned table. I'm still gathering up the details for the items above (e.g how much have the objects changed) and I'll probably run a 10053 trace on one of the bad queries to see what the optimizer is doing on Monday, but I'd like to just poll for some additional ideas. I *AM* getting partition elimination (thank goodness) but I've got two FTS on one partition of this table that are just killing it. They want to quantify the reason why this access has changed so I'm trying to think of what kinds of stuff I can look at to try to do this. I will add that this table was just rebuilt recently (through imp/exp)... can the change in row to block density make the difference h Any ideas?? RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Changed execution plans..
I read in one article, that if oracle found there are more than 1 index can be used for our query indicate by first column in index, oracle will use the latest index. except we directly use index hint in our query. hopes this help --- Robert Freeman <[EMAIL PROTECTED]> wrote: > Thanks Cary... I've actually considered most of > those already. This isn't my > database, I'm coming in to help someone else. In > this case, the database > schema was accidentally dropped, and recovered from > an export. I'm wondering > if the import might have created blocks that are > packed more densely and > that this might be the cause of the problem. I'm > told the parameters are the > same, that the physical structure is the same, they > are not using stored > outlines and the SQL has not been changed. No > patches have been applied, so > it's apples for apples with the exception of the > statistics and, possibly, > the data density. They have some old stored > statistics that they generated > pre-schema drop that they are supposed to send me, > so I'm going to look at > that tomorrow and run a 10053 trace on one of the > changed queries and see > what I can find. I was just wondering if I could be > missing something > obvious. Seems like that is just the way, it's > the obvious things that > get missed... :-) > > Thanks so much for your comments! > > RF > > -Original Message- > Millsap > Sent: Sunday, January 19, 2003 8:54 PM > To: Multiple recipients of list ORACLE-L > > > Robert, > > Seven reasons I can think of include changes to: > > 1. Oracle instance parameter values (changes when > you edit the > parameters, whether in the stored "init.ora" way, or > via ALTER SYSTEM or > ALTER SESSION commands) > 2. Database table and index statistics (changes, > e.g., when you run > dbms_stats.gather_database_stats) > 3. System CPU and I/O statistics (changes, e.g., > when you run > dbms_stats.gather_system_stats) > 4. Database schema configuration (changes when you > create/drop indexes, > etc.) > 5. Stored outlines (changes when you create or > reassign outlines) > 6. SQL text (changes when you manipulate the > application SQL) > 7. Oracle query cost model (changes when you upgrade > or patch your > Oracle kernel) > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - 2003 Hotsos Symposium, Feb 9-12 Dallas > - RMOUG Training Days 2003, Mar 5-6 Denver > - Hotsos Clinic 101, Mar 26-28 London > > > -Original Message- > Freeman > Sent: Sunday, January 19, 2003 1:24 AM > To: Multiple recipients of list ORACLE-L > > Hey Ya'all... (still got my southern roots even up > here in Chicago!) > > Anyone want to throw in some possible reasons why an > execution plan > might > change for a given table queryThis is on > Oracle9iR2 on SUN. I've > looked > at the obvious causes: > > 1. Object has changed - Appears not to have changed. > 2. Database parameters have changed - Appears that > no parameters have > changed. > 3. Statistics (data volumes, distribution, > cardinality, etc) have > changed - > Still looking into this, but the volumes have not > changed dramatically > even > if they have changed. > 4. Other physical database changes. > > None of these seem to apply. I've got a database > that a few weeks ago > were > doing indexed > lookups using a partitioned index on a partitioned > table. Now, it seems > that > these queries > are doing full table scans on this partitioned > table. I'm still > gathering up > the details for the items above (e.g how much have > the objects changed) > and > I'll probably run a 10053 trace on one of the bad > queries to see what > the > optimizer is doing on Monday, but I'd like to just > poll for some > additional > ideas. I *AM* getting partition elimination (thank > goodness) but I've > got > two FTS on one partition of this table that are just > killing it. They > want > to quantify the reason why this access has changed > so I'm trying to > think of > what kinds of stuff I can look at to try to do this. > > I will add that this table was just rebuilt recently > (through > imp/exp)... > can the change in row to block density make the > difference h > > Any ideas?? > > RF > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Robert Freeman > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > > -- > Please see the official ORACLE-L FAQ:
RE: Changed execution plans..
Thanks Cary... I've actually considered most of those already. This isn't my database, I'm coming in to help someone else. In this case, the database schema was accidentally dropped, and recovered from an export. I'm wondering if the import might have created blocks that are packed more densely and that this might be the cause of the problem. I'm told the parameters are the same, that the physical structure is the same, they are not using stored outlines and the SQL has not been changed. No patches have been applied, so it's apples for apples with the exception of the statistics and, possibly, the data density. They have some old stored statistics that they generated pre-schema drop that they are supposed to send me, so I'm going to look at that tomorrow and run a 10053 trace on one of the changed queries and see what I can find. I was just wondering if I could be missing something obvious. Seems like that is just the way, it's the obvious things that get missed... :-) Thanks so much for your comments! RF -Original Message- Millsap Sent: Sunday, January 19, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Robert, Seven reasons I can think of include changes to: 1. Oracle instance parameter values (changes when you edit the parameters, whether in the stored "init.ora" way, or via ALTER SYSTEM or ALTER SESSION commands) 2. Database table and index statistics (changes, e.g., when you run dbms_stats.gather_database_stats) 3. System CPU and I/O statistics (changes, e.g., when you run dbms_stats.gather_system_stats) 4. Database schema configuration (changes when you create/drop indexes, etc.) 5. Stored outlines (changes when you create or reassign outlines) 6. SQL text (changes when you manipulate the application SQL) 7. Oracle query cost model (changes when you upgrade or patch your Oracle kernel) Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- Freeman Sent: Sunday, January 19, 2003 1:24 AM To: Multiple recipients of list ORACLE-L Hey Ya'all... (still got my southern roots even up here in Chicago!) Anyone want to throw in some possible reasons why an execution plan might change for a given table queryThis is on Oracle9iR2 on SUN. I've looked at the obvious causes: 1. Object has changed - Appears not to have changed. 2. Database parameters have changed - Appears that no parameters have changed. 3. Statistics (data volumes, distribution, cardinality, etc) have changed - Still looking into this, but the volumes have not changed dramatically even if they have changed. 4. Other physical database changes. None of these seem to apply. I've got a database that a few weeks ago were doing indexed lookups using a partitioned index on a partitioned table. Now, it seems that these queries are doing full table scans on this partitioned table. I'm still gathering up the details for the items above (e.g how much have the objects changed) and I'll probably run a 10053 trace on one of the bad queries to see what the optimizer is doing on Monday, but I'd like to just poll for some additional ideas. I *AM* getting partition elimination (thank goodness) but I've got two FTS on one partition of this table that are just killing it. They want to quantify the reason why this access has changed so I'm trying to think of what kinds of stuff I can look at to try to do this. I will add that this table was just rebuilt recently (through imp/exp)... can the change in row to block density make the difference h Any ideas?? RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please s
RE: Changed execution plans..
Robert, Seven reasons I can think of include changes to: 1. Oracle instance parameter values (changes when you edit the parameters, whether in the stored "init.ora" way, or via ALTER SYSTEM or ALTER SESSION commands) 2. Database table and index statistics (changes, e.g., when you run dbms_stats.gather_database_stats) 3. System CPU and I/O statistics (changes, e.g., when you run dbms_stats.gather_system_stats) 4. Database schema configuration (changes when you create/drop indexes, etc.) 5. Stored outlines (changes when you create or reassign outlines) 6. SQL text (changes when you manipulate the application SQL) 7. Oracle query cost model (changes when you upgrade or patch your Oracle kernel) Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- Freeman Sent: Sunday, January 19, 2003 1:24 AM To: Multiple recipients of list ORACLE-L Hey Ya'all... (still got my southern roots even up here in Chicago!) Anyone want to throw in some possible reasons why an execution plan might change for a given table queryThis is on Oracle9iR2 on SUN. I've looked at the obvious causes: 1. Object has changed - Appears not to have changed. 2. Database parameters have changed - Appears that no parameters have changed. 3. Statistics (data volumes, distribution, cardinality, etc) have changed - Still looking into this, but the volumes have not changed dramatically even if they have changed. 4. Other physical database changes. None of these seem to apply. I've got a database that a few weeks ago were doing indexed lookups using a partitioned index on a partitioned table. Now, it seems that these queries are doing full table scans on this partitioned table. I'm still gathering up the details for the items above (e.g how much have the objects changed) and I'll probably run a 10053 trace on one of the bad queries to see what the optimizer is doing on Monday, but I'd like to just poll for some additional ideas. I *AM* getting partition elimination (thank goodness) but I've got two FTS on one partition of this table that are just killing it. They want to quantify the reason why this access has changed so I'm trying to think of what kinds of stuff I can look at to try to do this. I will add that this table was just rebuilt recently (through imp/exp)... can the change in row to block density make the difference h Any ideas?? RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
