Re: good value for optimizer_index_cost_adj
My 1/2 cents on this... Another way of thinking of scattered -vs- sequential reads is that a scattered read is used when you know up front that you want to read several blocks and know which blocks they are. You can then use a vectored read to do this or farm them all out in parallel using asynchronous read. Sequential reads implies that you need the result of each block before you can determine the next block to read. Therefore you do single block I/O. Bill --- Jonathan Lewis [EMAIL PROTECTED] wrote: Well, Tim did say he was making a long story short. scattered reads = multiblock reads which are typically associated with tablescans, but can be index fast full scans. sequential reads = single block reads which are typically associated with index block reads, table blocks accessed as a consequence of index block reads, but can be one-off blocks in table scans and index fast full scans due to the existence of previously buffered blocks or tail-end blocks on extents. And then there are cached LOB reads and Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 25 April 2002 00:21 | db file scattered read (associated with FULL | table scans) and db file sequential | read (associated with indexed scans) | |But see, Tuning 101, p. 35, where a db file sequential read is |investigated and the waits are found to be reads from a *table*. | |And other examples elsewhere, where db file scattered read is found to be |associated with reads from an index. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: good value for optimizer_index_cost_adj
Based on documentation, OPTIMIZER_INDEX_COST_ADJ seems to provide additional comparison information for the CBO in terms of the relative cost of different types of I/O. To make a long story short, I believe that guidance can come from examining timing statistics from the wait-events db file scattered read (associated with FULL table scans) and db file sequential read (associated with indexed scans) and looking at their respective average wait times: select event, average_wait from v$system_event where event like 'db file s%' Not to use that dirty word ratio lightly in this forum :-), you should calculate the ratio of:: (avg-wait-for-db-file-sequential-read / avg-wait-for-db-file-scattered-read) * 100 which can be considered as a possible setting for OPTIMIZER_INDEX_COST_ADJ. Of course, all of the common-sense caveats apply: don't adhere to this formula slavishly because there might be any number of anomalies in the AVERAGE_WAIT information from V$SYSTEM_EVENT due to low uptime, etc. Take several samples over time, if possible (i.e. the axiom of measure twice, cut once works as well in database administration as in carpentry). Test, test, test before implementing in production... At IOUG-A, I heard discussion that the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were two separate approaches developed by different development teams within Oracle that had the exact same purpose. So, the argument was advanced that setting *either* one *or* the other was sufficient, but not *both*. Not having any access to the internal goings-on in Oracle ST Development, I'm sticking with the idea that these two parameters are addressing *different* and very specific issues, so they both should be considered and used independently of one another... I have a paper on this topic at http://www.EvDBT.com/SearchIntelligenceCBO.doc that discusses these issues in more depth... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 8:58 AM Hi, Oracle 817/Solaris 8. Users are doing select joining using the PKs of 2 partitionned tables. Partitionned key and the primary key are the same. The access plan is a nested loop with a full table scan on the first table which hold 700 000 rows. The block size is 16K, I assume that's why Oracle is doing FTS. By using optimizer_index_cost_adj, I can make Oracle use the PK of the first table. I've used 50 as a value for optimizer_index_cost_adj. Is that too much ? Where can I get some metrics on that parameter ? TIA = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: good value for optimizer_index_cost_adj
Best place to look is probably Tim Gorman's paper titled something like 'The search for intelligent life'. To be found on www.evdbt.com His argument, which I think is very sound, is that the most correct value for the parameter is the relative cost of a single block read compared to a multi block read. This is captured in oracle 9 through system_stats where you can capture for a given time period: average single block read time average multiblock read time average actual size of multiblock read. I'll leave it to Tim's paper to give you guidelines on estimating the average times and average multiblock read size. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 24 April 2002 16:02 Hi, Oracle 817/Solaris 8. Users are doing select joining using the PKs of 2 partitionned tables. Partitionned key and the primary key are the same. The access plan is a nested loop with a full table scan on the first table which hold 700 000 rows. The block size is 16K, I assume that's why Oracle is doing FTS. By using optimizer_index_cost_adj, I can make Oracle use the PK of the first table. I've used 50 as a value for optimizer_index_cost_adj. Is that too much ? Where can I get some metrics on that parameter ? TIA = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: good value for optimizer_index_cost_adj
It's interesting that you should have heard that. My first interpretation of the optimizer_index_cost_adj was that it was an estimate of the table logical I/O that would become physical I/O (and ignore the fact that this was allowed to go above 100%) - which brings it into line, somewhat, with the optimizer_index_caching in terms of 'addressing the same issue'. However, I decided that your interpretation was a much nicer, more intuitive, way of appreciating the significance of the number and deciding on a rational setting for it. But I'm inclined to agree with you - even if they were supposed to be addressing the same problem in different ways, they do seem to be jointly and separately (as the lawyers say) viable. Have you tried any experiments yet which mix dbms_stats.system_stats figures with the effects of these two parameters ? That ought to be a case of when we should do one or the other. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- At IOUG-A, I heard discussion that the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were two separate approaches developed by different development teams within Oracle that had the exact same purpose. So, the argument was advanced that setting *either* one *or* the other was sufficient, but not *both*. Not having any access to the internal goings-on in Oracle ST Development, I'm sticking with the idea that these two parameters are addressing *different* and very specific issues, so they both should be considered and used independently of one another... I have a paper on this topic at http://www.EvDBT.com/SearchIntelligenceCBO.doc that discusses these issues in more depth... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: good value for optimizer_index_cost_adj
db file scattered read (associated with FULL table scans) and db file sequential read (associated with indexed scans) But see, Tuning 101, p. 35, where a db file sequential read is investigated and the waits are found to be reads from a *table*. And other examples elsewhere, where db file scattered read is found to be associated with reads from an index. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: good value for optimizer_index_cost_adj
The naming of these two wait-events is unfortunate in that they are counter-intuitive. You would normally think that the phrase sequential implied one thing and the phrase scattered implied the opposite. They do, but not the way one would guess... Db file scattered read is the wait-event for sequential, multi-block read I/O. This type of I/O is usually associated with FULL table scans and fast full index scans. Db file sequential read is the wait-event for random, single-block read I/O, usually associated with reads by data-block address (DBA) and/or ROWID. This includes reads on branch and leaf blocks in an index, as well as ROWID access to data blocks in tables and clusters. This wait-event is also associated with reads where ROWID is specified in the SQL statement, as well as reads of segment headers and some stuff like that. I'm sure that there are more scenarios that I've omitted... Tuning 101 isn't incorrect and neither is my reply. Fast full index scans are sequential, multi-block reads against the entire index segment, just one of four index scan method alternatives (i.e. unique, range, full, and fast-full). And, of course, random single-block I/O is performed on tables/clusters all the time... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 4:23 PM db file scattered read (associated with FULL table scans) and db file sequential read (associated with indexed scans) But see, Tuning 101, p. 35, where a db file sequential read is investigated and the waits are found to be reads from a *table*. And other examples elsewhere, where db file scattered read is found to be associated with reads from an index. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: good value for optimizer_index_cost_adj
Well, Tim did say he was making a long story short. scattered reads = multiblock reads which are typically associated with tablescans, but can be index fast full scans. sequential reads = single block reads which are typically associated with index block reads, table blocks accessed as a consequence of index block reads, but can be one-off blocks in table scans and index fast full scans due to the existence of previously buffered blocks or tail-end blocks on extents. And then there are cached LOB reads and Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 25 April 2002 00:21 | db file scattered read (associated with FULL | table scans) and db file sequential | read (associated with indexed scans) | |But see, Tuning 101, p. 35, where a db file sequential read is |investigated and the waits are found to be reads from a *table*. | |And other examples elsewhere, where db file scattered read is found to be |associated with reads from an index. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: good value for optimizer_index_cost_adj
I've got it on my list of things I'd like to do (i.e. understand DBMS_STATS.GATHER_SYSTEM_STATS), but I don't see any time to do it anytime soon. :-( It makes sense that the use of this procedure should obsolesce both OPTIMIZER_INDEX_ parameters... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 2:48 PM It's interesting that you should have heard that. My first interpretation of the optimizer_index_cost_adj was that it was an estimate of the table logical I/O that would become physical I/O (and ignore the fact that this was allowed to go above 100%) - which brings it into line, somewhat, with the optimizer_index_caching in terms of 'addressing the same issue'. However, I decided that your interpretation was a much nicer, more intuitive, way of appreciating the significance of the number and deciding on a rational setting for it. But I'm inclined to agree with you - even if they were supposed to be addressing the same problem in different ways, they do seem to be jointly and separately (as the lawyers say) viable. Have you tried any experiments yet which mix dbms_stats.system_stats figures with the effects of these two parameters ? That ought to be a case of when we should do one or the other. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- At IOUG-A, I heard discussion that the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were two separate approaches developed by different development teams within Oracle that had the exact same purpose. So, the argument was advanced that setting *either* one *or* the other was sufficient, but not *both*. Not having any access to the internal goings-on in Oracle ST Development, I'm sticking with the idea that these two parameters are addressing *different* and very specific issues, so they both should be considered and used independently of one another... I have a paper on this topic at http://www.EvDBT.com/SearchIntelligenceCBO.doc that discusses these issues in more depth... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: good value for optimizer_index_cost_adj
Check out Jeff Holt's Why are Oracle's Read Events 'Named Backwards'? (www.hotsos.com/dnloads/1.Holt2000.02.01-Backwards.pdf) for a description of why these events are named the way they are. Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- Sent: Wednesday, April 24, 2002 8:10 PM To: Multiple recipients of list ORACLE-L The naming of these two wait-events is unfortunate in that they are counter-intuitive. You would normally think that the phrase sequential implied one thing and the phrase scattered implied the opposite. They do, but not the way one would guess... Db file scattered read is the wait-event for sequential, multi-block read I/O. This type of I/O is usually associated with FULL table scans and fast full index scans. Db file sequential read is the wait-event for random, single-block read I/O, usually associated with reads by data-block address (DBA) and/or ROWID. This includes reads on branch and leaf blocks in an index, as well as ROWID access to data blocks in tables and clusters. This wait-event is also associated with reads where ROWID is specified in the SQL statement, as well as reads of segment headers and some stuff like that. I'm sure that there are more scenarios that I've omitted... Tuning 101 isn't incorrect and neither is my reply. Fast full index scans are sequential, multi-block reads against the entire index segment, just one of four index scan method alternatives (i.e. unique, range, full, and fast-full). And, of course, random single-block I/O is performed on tables/clusters all the time... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 4:23 PM db file scattered read (associated with FULL table scans) and db file sequential read (associated with indexed scans) But see, Tuning 101, p. 35, where a db file sequential read is investigated and the waits are found to be reads from a *table*. And other examples elsewhere, where db file scattered read is found to be associated with reads from an index. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
