Re: Buffer Pool Testing
The reason that I consider it a problem is that I have increased the physical IO on my database. I see Full Table Scans on these two tables, which are large tables relative to the others. I moved them to they RECYCLE pool so that they would have less of an impact on the DEFAULT pool. By doing so, it looks like I am forcing the database to do more physical reads than it may have been doing with these tables in the DEFAULT pool. I thought the FTS blocks would "fight" with each other (the blocks queried the most often would stay in the pool) over the RECYCLE pool, but the algorithm keeps the first blocks in place. Knowing this, I think I need to re-evaluate whether or not these tables were causing a problem in the DEFAULT pool to begin with. I tried _db_percent_hot_recycle=10, but didn't really notice much of a difference. Since all of my blocks are read into this pool by FTS, I was hoping that it would consider some of them to be hot, thereby causing the rest to be flushed out faster. I just wanted the hottest FTS blocks to stay in the RECYCLE pool, but that wasn't the case. Jay >>> [EMAIL PROTECTED] 01/02/03 06:44PM >>> The 'problem' is, as you say, related to the tablescan. Craig is correct that NORMALLY a full tablescan will only permit a limited number of blocks to get into the cache at the LRU end of the chain - the number is typically the size of db_file_multblock_read_count. There are a couple of special cases though, the first being when there are free blocks in the buffer, Oracle will just keep packing in the multiblock reads into the cache until there are no free blocks left. (And specifically "free" means state=0). The other special case I can think of at the moment is when you have multiple tablescans going on concurrently, and depending on precise timing you can end up with multiples of db_file_multiblock_read_count blocks from different tables near the LRU end of the cache. There was a period, I believe, when the RECYCLE pool did behave a little differently (can anyone confirm this ?) but in 9.2.0.2, it handles tablescans just the same way as the default pool. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 02 January 2003 19:03 > So it seems like my problem is the full table scan. Craig Shallahammer mentions this in his "All About Oracle's Touch-Count Data Block Buffer Algoithm" paper - "The modified LRU algorithm places full-table scanned blocks read into the buffer cache at the LRU end of the LRU chain and only permits a limited number of these blocks to exist in the cache at once." > Using my second example (query ALRA_TRANSACTION_HISTORY then WORK_ORDER_STEP), I can get more blocks of WORK_ORDER_STEP into the cache if I run queries that don't do full table scans. > I still expected multiple queries against a table (full-scan or otherwise) to replace the cache blocks that I was no longer using - especially in the RECYCLE pool. But it appears as though the algorithm doesn't work that way. > >Thanks, >Jay > also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Buffer Pool Testing
At a high level, I think the various pools operate in pretty much the same way - but there are some differences, some of which may be affected by various hidden parameters. For example, Steve Adams has this note (dated Oct 2000) on his website as one of several observations on the 'new' LRU mechanism. Some details may have evolved since then, of course, but it is a good indication of how the pools can be "the same, but different". By default, the KEEP and RECYCLE buffer pools do not have a hot region, but this can be changed by setting the _db_percent_hot_keep and _db_percent_hot_recycle parameters to non-zero values. Otherwise, the RECYCLE buffer pool has exactly the same LRU semantics as the DEFAULT buffer pool - it is only the name that is different - and the KEEP buffer pool differs only in that buffers read for long table scans are cached just below the hot region rather than at the LRU end and consistent read buffers are immediately frozen. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 03 January 2003 02:23 >2. The KEEP and RECYCLE Pools aren't really special in any sense: the buffer >algorithms are the same for these as for the DEFAULT pool - it is just that >these pools are named so that tables can be defaulted to load into any one >of them. By using this feature judiciously, you might be able to avoid the >problems of unneccesary buffering during FTS on tables smaller than the >threshhold without actually changing the undocumented parameter. > >Jonathan: Awaiting your expert comments on this new twist :) > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Buffer Pool Testing
Jay, Have you considered the default value of '_small_table_threshold'? Apparently it defaults to 2% of the Buffer cache in 8i (haven't seen any official docs to support this though) Also wondering how this works in the case of the 'special' buffer pools in combination with the 'CACHE' and 'NOCACHE' option on the tables in question. Is my understanding that the kernel would load a 'small' table into the MRU end rather than the LRU correct? Two myths here: 1. The 'old' docs (V6, V7.0??) said that small tables were 4 blocks or lesser (or was it 5 blocks?). This carried over into the later versions 2. The KEEP and RECYCLE Pools aren't really special in any sense: the buffer algorithms are the same for these as for the DEFAULT pool - it is just that these pools are named so that tables can be defaulted to load into any one of them. By using this feature judiciously, you might be able to avoid the problems of unneccesary buffering during FTS on tables smaller than the threshhold without actually changing the undocumented parameter. Jonathan: Awaiting your expert comments on this new twist :) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** > -Original Message- > From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 02, 2003 3:44 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Buffer Pool Testing > > > > The 'problem' is, as you say, related to the tablescan. > Craig is correct that NORMALLY a full tablescan will > only permit a limited number of blocks to get into > the cache at the LRU end of the chain - the number > is typically the size of db_file_multblock_read_count. > > There are a couple of special cases though, the first > being when there are free blocks in the buffer, Oracle > will just keep packing in the multiblock reads into > the cache until there are no free blocks left. (And > specifically "free" means state=0). > > The other special case I can think of at the moment > is when you have multiple tablescans going on > concurrently, and depending on precise timing you can > end up with multiples of db_file_multiblock_read_count > blocks from different tables near the LRU end of the > cache. > > > There was a period, I believe, when the RECYCLE pool > did behave a little differently (can anyone confirm this ?) > but in 9.2.0.2, it handles tablescans just the same way > as the default pool. > > > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon a new one-day tutorial: > Cost Based Optimisation > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > England__January 21/23 > > > 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: 02 January 2003 19:03 > > > > So it seems like my problem is the full table scan. Craig > Shallahammer mentions this in his "All About Oracle's Touch-Count Data > Block Buffer Algoithm" paper - "The modified LRU algorithm places > full-table scanned blocks read into the buffer cache at the LRU end of > the LRU chain and only permits a limited number of these blocks to > exist in the cache at once." > > Using my second example (query ALRA_TRANSACTION_HISTORY then > WORK_ORDER_STEP), I can get more blocks of WORK_ORDER_STEP into the > cache if I run queries that don't do full table scans. > > I still expected multiple queries against a table (full-scan or > otherwise) to replace the cache blocks that I was no longer using - > especially in the RECYCLE pool. But it appears as though the > algorithm doesn't work that way. > > > >Thanks, > >Jay > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > 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: Buffer Pool Testing
The 'problem' is, as you say, related to the tablescan. Craig is correct that NORMALLY a full tablescan will only permit a limited number of blocks to get into the cache at the LRU end of the chain - the number is typically the size of db_file_multblock_read_count. There are a couple of special cases though, the first being when there are free blocks in the buffer, Oracle will just keep packing in the multiblock reads into the cache until there are no free blocks left. (And specifically "free" means state=0). The other special case I can think of at the moment is when you have multiple tablescans going on concurrently, and depending on precise timing you can end up with multiples of db_file_multiblock_read_count blocks from different tables near the LRU end of the cache. There was a period, I believe, when the RECYCLE pool did behave a little differently (can anyone confirm this ?) but in 9.2.0.2, it handles tablescans just the same way as the default pool. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 02 January 2003 19:03 > So it seems like my problem is the full table scan. Craig Shallahammer mentions this in his "All About Oracle's Touch-Count Data Block Buffer Algoithm" paper - "The modified LRU algorithm places full-table scanned blocks read into the buffer cache at the LRU end of the LRU chain and only permits a limited number of these blocks to exist in the cache at once." > Using my second example (query ALRA_TRANSACTION_HISTORY then WORK_ORDER_STEP), I can get more blocks of WORK_ORDER_STEP into the cache if I run queries that don't do full table scans. > I still expected multiple queries against a table (full-scan or otherwise) to replace the cache blocks that I was no longer using - especially in the RECYCLE pool. But it appears as though the algorithm doesn't work that way. > >Thanks, >Jay > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Buffer Pool Testing
So it seems like my problem is the full table scan. Craig Shallahammer mentions this in his "All About Oracle's Touch-Count Data Block Buffer Algoithm" paper - "The modified LRU algorithm places full-table scanned blocks read into the buffer cache at the LRU end of the LRU chain and only permits a limited number of these blocks to exist in the cache at once." Using my second example (query ALRA_TRANSACTION_HISTORY then WORK_ORDER_STEP), I can get more blocks of WORK_ORDER_STEP into the cache if I run queries that don't do full table scans. I still expected multiple queries against a table (full-scan or otherwise) to replace the cache blocks that I was no longer using - especially in the RECYCLE pool. But it appears as though the algorithm doesn't work that way. Thanks, Jay >>> [EMAIL PROTECTED] 01/02/03 10:20AM >>> I think you are seeing expected behaviour. Blocks subject to tablescan are loaded in to the LRU end of the cache, even when using a RECYCLE cache. However, if there are free blocks in the cache (state = 0) Oracle uses those rather than flushing other blocks. Consequently, when you startup and scan a 400 block table with a 1,000 block cache, the whole 400 blocks will get into memory. Then the next 10,000 block scan will start by using the last 600 blocks of the cache before recycling the last db_file_multiblock_read_count blocks. By this time, though, the 1st 400 blocks are at the MRU end of the chain, and are not moved by the subsequent tablescan. If you start with the 10,000 block scan, the whole cache is filled. The second scan then keeps recycling the last db_file_multiblock_read_count blocks (though in your case I guess it's plus one - possibly a cleanout block, possibly the segment header block which may go into the Default pool in v9 - without pushing out any more of the first 1,000 blocks from the first scan. Periods of time shortly after startup are always likely to show anomalous behaviour. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 31 December 2002 19:31 >I'm spending some time today experimenting with buffer pools in 8.1.7. I have two tables that I have assigned to the RECYCLE pool. I have been running various queries that perform full table scans, then checking the buffers to see what gets aged out. During my testing, it seems like the first blocks to get into the RECYCLE buffer pool will stay there. The following two tables are assigned to the RECYCLE pool. No other segments are assigned to it: > >WORK_ORDER_STEP - 428 blocks of data >ALRA_TRANSACTION_HISTORY - 14152 blocks of data > >The RECYCLE pool has 1000 blocks. > >I startup the database, query the WORK_ORDER_STEP table (1 time), then run multiple queries against the ALRA_TRANSACTION_HISTORY table (6 times), I see the following in the buffers (the source for this query is at the end of my email): > >BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH > --- -- - - - - - >RECYCLE BIS ALRA_TRANSACTION_HISTORY 56914 0 .02 >RECYCLE WRKORD WORK_ORDER_STEP 431 1 0 .00 > >If I startup the database, query the ALRA_TRANSACTION_HISTORY table (1 time), then run multiple queries against the WORK_ORDER_STEP table (6 queries), I get these results: > >First query - 1000 blocks are used as expected >BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH > --- -- - - - - - >RECYCLE BIS ALRA_TRANSACTION_HISTORY 1000 2 0 .00 > > >After querying the second table multiple times, I expected more than just 9 blocks to be given up. I expected more like 431 blocks. >BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH > --- -- - - - - - >RECYCLE BIS ALRA_TRANSACTION_HISTORY 991 2 0 .00 >RECYCLE WRKORD WORK_ORDER_STEP 9 4 0 .44 > >I expected the blocks (from the table that was queried first) to be aged out as I queried the second table (over and over). This does not occur. Am I hi
Re: Buffer Pool Testing
I think you are seeing expected behaviour. Blocks subject to tablescan are loaded in to the LRU end of the cache, even when using a RECYCLE cache. However, if there are free blocks in the cache (state = 0) Oracle uses those rather than flushing other blocks. Consequently, when you startup and scan a 400 block table with a 1,000 block cache, the whole 400 blocks will get into memory. Then the next 10,000 block scan will start by using the last 600 blocks of the cache before recycling the last db_file_multiblock_read_count blocks. By this time, though, the 1st 400 blocks are at the MRU end of the chain, and are not moved by the subsequent tablescan. If you start with the 10,000 block scan, the whole cache is filled. The second scan then keeps recycling the last db_file_multiblock_read_count blocks (though in your case I guess it's plus one - possibly a cleanout block, possibly the segment header block which may go into the Default pool in v9 - without pushing out any more of the first 1,000 blocks from the first scan. Periods of time shortly after startup are always likely to show anomalous behaviour. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 31 December 2002 19:31 >I'm spending some time today experimenting with buffer pools in 8.1.7. I have two tables that I have assigned to the RECYCLE pool. I have been running various queries that perform full table scans, then checking the buffers to see what gets aged out. During my testing, it seems like the first blocks to get into the RECYCLE buffer pool will stay there. The following two tables are assigned to the RECYCLE pool. No other segments are assigned to it: > >WORK_ORDER_STEP - 428 blocks of data >ALRA_TRANSACTION_HISTORY - 14152 blocks of data > >The RECYCLE pool has 1000 blocks. > >I startup the database, query the WORK_ORDER_STEP table (1 time), then run multiple queries against the ALRA_TRANSACTION_HISTORY table (6 times), I see the following in the buffers (the source for this query is at the end of my email): > >BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH > --- -- - - - - - >RECYCLE BIS ALRA_TRANSACTION_HISTORY 56914 0 .02 >RECYCLE WRKORD WORK_ORDER_STEP 431 1 0 .00 > >If I startup the database, query the ALRA_TRANSACTION_HISTORY table (1 time), then run multiple queries against the WORK_ORDER_STEP table (6 queries), I get these results: > >First query - 1000 blocks are used as expected >BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH > --- -- - - - - - >RECYCLE BIS ALRA_TRANSACTION_HISTORY 1000 2 0 .00 > > >After querying the second table multiple times, I expected more than just 9 blocks to be given up. I expected more like 431 blocks. >BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH > --- -- - - - - - >RECYCLE BIS ALRA_TRANSACTION_HISTORY 991 2 0 .00 >RECYCLE WRKORD WORK_ORDER_STEP 9 4 0 .44 > >I expected the blocks (from the table that was queried first) to be aged out as I queried the second table (over and over). This does not occur. Am I hitting a bug or just misunderstanding the buffer management algorithms? ALRA_TRANSACTION_HISTORY blocks should be LRU as I hit the WORK_ORDER_STEP table over and over. > >Thanks, >Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Buffer Pool Testing
I'm spending some time today experimenting with buffer pools in 8.1.7. I have two tables that I have assigned to the RECYCLE pool. I have been running various queries that perform full table scans, then checking the buffers to see what gets aged out. During my testing, it seems like the first blocks to get into the RECYCLE buffer pool will stay there. The following two tables are assigned to the RECYCLE pool. No other segments are assigned to it: WORK_ORDER_STEP - 428 blocks of data ALRA_TRANSACTION_HISTORY - 14152 blocks of data The RECYCLE pool has 1000 blocks. I startup the database, query the WORK_ORDER_STEP table (1 time), then run multiple queries against the ALRA_TRANSACTION_HISTORY table (6 times), I see the following in the buffers (the source for this query is at the end of my email): BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- -- - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 569 14 0 .02 RECYCLE WRKORD WORK_ORDER_STEP 431 1 0 .00 If I startup the database, query the ALRA_TRANSACTION_HISTORY table (1 time), then run multiple queries against the WORK_ORDER_STEP table (6 queries), I get these results: First query - 1000 blocks are used as expected BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- -- - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 1000 2 0 .00 After querying the second table multiple times, I expected more than just 9 blocks to be given up. I expected more like 431 blocks. BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- -- - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 991 2 0 .00 RECYCLE WRKORD WORK_ORDER_STEP 9 4 0 .44 I expected the blocks (from the table that was queried first) to be aged out as I queried the second table (over and over). This does not occur. Am I hitting a bug or just misunderstanding the buffer management algorithms? ALRA_TRANSACTION_HISTORY blocks should be LRU as I hit the WORK_ORDER_STEP table over and over. Thanks, Jay Here is the query that I use to check what is in the buffer pools: select bpd.bp_name, u.name obj_owner, o.name, count(*) BLOCKS, max(tch) max_touch, min(tch) min_touch, avg(tch) avg_touch from x$kcbwds wds, /* working data sets */ x$kcbwbpd bpd, /* buffer pools */ x$bh bh, /* buffer headers */ obj$ o,/* objects */ user$ u where wds.set_id >= bpd.bp_lo_sid and wds.set_id <= bpd.bp_hi_sid and bpd.bp_size != 0 and bh.indx between wds.start_buf# and wds.end_buf# and o.dataobj# = bh.obj and bh.state !=0 and o.owner# !=0 /* exclude sys */ and o.owner# = u.user# group by bpd.bp_name, u.name, o.name order by bpd.bp_name, u.name, o.name ; **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Which objects to pin in which Multiple Buffer Pool?
My recommendation is small lookup type tables that are used very often. Things like Employees tables, common LOV (list of values) tables should go to the KEEP pool. With 9i, you can have a pool for each db_block_size, and a corresponding TS for each. I have not done this in practice yet, but this is the one feature that DB2 has had that as an Oracelite I had been envious of, which we now finally have, having multiple buffer_pools per TS (well, kind of). HTH. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia Only Robinson Crusoe had all his work done by Friday -Original Message- Sent: Monday, 24 June 2002 10:48 PM To: Multiple recipients of list ORACLE-L Of course, if anyone has some queries to help identify buffer pool pinning candidates, they would be more than welcome. Thanks, Lou Avrami >From the article "Oracle8i Buffer Cache: New Features", identifying "hot blocks" (DEFAULT cache?) can be done with the query: PCSS:SYS> select obj object, 2 DBARFIL file#, 3 DBABLK block#, 4 tch touches 5 from x$bh 6 where tch > 100 7 order by 1,2; OBJECT FILE# BLOCK#TOUCHES -- -- -- -- 61 1187337 83 1246661 83 1 21865797 99 1280370 102 1286 1319 102 1 24617 1317 102 1 24618 1334 195 1465792 31658 10 1289152 31668 10 1929148 31678 10 2569152 The article also suggest the following query for identifying Recycle Pool candidates: SQL> select obj object, 2 count(1) buffers, 3 100 * (count(1)/totsize) pct_cache 4 from x$bh, 5 (select value totsize from v$parameter 6 where name = 'db_block_buffers') 7 where tch = 1 8 group by obj, totsize 9 having 100 * (count(1)/totsize) > 5; >--- Original Message --- >From: "Louis Avrami" <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Date: 6/25/02 12:35:32 AM > Hi all, > >I'm in an environment where we're running RDBMS 8.1.7.2 on multiple Solaris 2.8 servers. > >I would like to set up multiple buffer pools in several of our databases and pin objects appropriately in the KEEP, RECYCLE and default buffer cache. Unfortunately, many of the applications that we work with are developed by outside vendors with whom we have very little contact or documentation (don't ask). > >I did come across an article titled "Oracle8i Buffer Cache: New Features" in the July 2000 issue of the ORACLE INTERNALS newsletter, which has some interesting queries which might help to identify candidates for the various buffer pools. > >For the KEEP pool, the article suggests the following SQL: > > 1 select obj object, > 2 count(1) buffers, > 3 avg(tch) avg_touches > 4 from x$bh > 5 group by obj > 6 having avg(tch) > 5 > 7* and count(1) > 20 >SQL> / > >OBJECTBUFFERS AVG_TOUCHES >-- -- --- > 2271 7.90405904 > 6 23 19 > 8 52 14.4038462 >18299 9.18394649 >33 31 12.9354839 >34219 6.6667 > 32365151 145.748344 > 32369 22 72.8181818 > 32376 21 5.38095238 > 32383 23 94.7391304 > 32433 86 5.69767442 > > >In the above query, I'm not sure how to map back the OBJ (O
Which objects to pin in which Multiple Buffer Pool?
Sorry if this is a repeat, I received a message that my original message was being return because of "locking problems" = Hi all, I'm in an environment where we're running RDBMS 8.1.7.2 on multiple Solaris 2.8 servers. I would like to set up multiple buffer pools in several of our databases and pin objects appropriately in the KEEP, RECYCLE and default buffer cache. Unfortunately, many of the applications that we work with are developed by outside vendors with whom we have very little contact or documentation (don't ask). I did come across an article titled "Oracle8i Buffer Cache: New Features" in the July 2000 issue of the ORACLE INTERNALS newsletter, which has some interesting queries which might help to identify candidates for the various buffer pools. For the KEEP pool, the article suggests the following SQL: 1 select obj object, 2 count(1) buffers, 3 avg(tch) avg_touches 4 from x$bh 5 group by obj 6 having avg(tch) > 5 7* and count(1) > 20 SQL> / OBJECTBUFFERS AVG_TOUCHES -- -- --- 2271 7.90405904 6 23 19 8 52 14.4038462 18299 9.18394649 33 31 12.9354839 34219 6.6667 32365151 145.748344 32369 22 72.8181818 32376 21 5.38095238 32383 23 94.7391304 32433 86 5.69767442 In the above query, I'm not sure how to map back the OBJ (OBJECT) number to a database table/object, so that it could subsequently be pinned in the KEEP pool. A better question might be this: Can someone help map X$BH.obj, X$BH.file# and X$BH.block# so that they can be identified as database tables, indexes, data files, etc.? Thanks, Lou Avrami -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Louis Avrami 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: Which objects to pin in which Multiple Buffer Pool?
Of course, if anyone has some queries to help identify buffer pool pinning candidates, they would be more than welcome. Thanks, Lou Avrami >From the article "Oracle8i Buffer Cache: New Features", identifying "hot blocks" (DEFAULT cache?) can be done with the query: PCSS:SYS> select obj object, 2 DBARFIL file#, 3 DBABLK block#, 4 tch touches 5 from x$bh 6 where tch > 100 7 order by 1,2; OBJECT FILE# BLOCK#TOUCHES -- -- -- -- 61 1187337 83 1246661 83 1 21865797 99 1280370 102 1286 1319 102 1 24617 1317 102 1 24618 1334 195 1465792 31658 10 1289152 31668 10 1929148 31678 10 2569152 The article also suggest the following query for identifying Recycle Pool candidates: SQL> select obj object, 2 count(1) buffers, 3 100 * (count(1)/totsize) pct_cache 4 from x$bh, 5 (select value totsize from v$parameter 6 where name = 'db_block_buffers') 7 where tch = 1 8 group by obj, totsize 9 having 100 * (count(1)/totsize) > 5; >--- Original Message --- >From: "Louis Avrami" <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Date: 6/25/02 12:35:32 AM > Hi all, > >I'm in an environment where we're running RDBMS 8.1.7.2 on multiple Solaris 2.8 servers. > >I would like to set up multiple buffer pools in several of our databases and pin objects appropriately in the KEEP, RECYCLE and default buffer cache. Unfortunately, many of the applications that we work with are developed by outside vendors with whom we have very little contact or documentation (don't ask). > >I did come across an article titled "Oracle8i Buffer Cache: New Features" in the July 2000 issue of the ORACLE INTERNALS newsletter, which has some interesting queries which might help to identify candidates for the various buffer pools. > >For the KEEP pool, the article suggests the following SQL: > > 1 select obj object, > 2 count(1) buffers, > 3 avg(tch) avg_touches > 4 from x$bh > 5 group by obj > 6 having avg(tch) > 5 > 7* and count(1) > 20 >SQL> / > >OBJECTBUFFERS AVG_TOUCHES >-- -- --- > 2271 7.90405904 > 6 23 19 > 8 52 14.4038462 >18299 9.18394649 >33 31 12.9354839 >34219 6.6667 > 32365151 145.748344 > 32369 22 72.8181818 > 32376 21 5.38095238 > 32383 23 94.7391304 > 32433 86 5.69767442 > > >In the above query, I'm not sure how to map back the OBJ (OBJECT) number to a database table/object, so that it could subsequently be pinned in the KEEP pool. > >A better question might be this: > >Can someone help map X$BH.obj, X$BH.file# and X$BH.block# so that they can be identified as database tables, indexes, data files, etc.? > >Thanks, >Lou Avrami > > > > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Louis Avrami 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-M
BUFFER POOL KEEP/RECYCLE
Hi all, init.ora -- db_block_size = 2048 db_block_buffers = 10240 db_block_lru_latches=3 buffer_pool_keep=(buffer:1024, lru_latches:1) buffer_pool_recycle=(buffer:512, lru_latches:1) After instance startup, SYS@SECDB> select name, value from v$parameter where name like 'buffer_pool%' NAME VALUE - buffer_pool_keep buffers:1024, lru_latches:1 buffer_pool_recycle Q1. Why here buffer pool recycle showing null value? and, SYS@SECDB> select table_name, BLOCKS, BUFFER_POOL from DBA_tables where buffer_pool ='KEEP' TABLE_NAMEBLOCKS BUFFER_ -- - --- PPS_SERVICE 418 KEEP EMPPP 2 KEEP TEST1796 KEEP Here the total blocks in buffer pool keep or exceeded than the defied value, but oracle didn't give any error while assing the object for buffer pool. Q2. What is the logic behind on it, then? Thanks to all., Nirmal. <>
Re: buffer pool
Look up BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE Basically you can have three separate buffer pools in 8.0+. hth connor --- "Wong, Bing" <[EMAIL PROTECTED]> wrote: > Has anyone heard of partitioning buffer pool in > Oracle? > > I don't know this. > > Bing > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Wong, Bing > 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
buffer pool
Has anyone heard of partitioning buffer pool in Oracle? I don't know this. Bing -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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).