RE: tirggers
That's right, Oracle seems to pull in all blocks needed to satisfy the query you send, which makes sense.. So if you do a select * from your_table; it should pull everything in to the buffer. One thing that you do have to consider, is that there is enough space in your KEEP buffer pool to store all of these tables.. Back to the root of your message though Kevin, could you not just write a script, that does a select * from your_tables, and execute this at start-up? How large are these tables also, AFAIK the KEEP buffer pool should really be used for smaller, lookup or code tables.. HTH Mark -Original Message- Kostyszyn Sent: Sunday, April 08, 2001 10:00 To: Multiple recipients of list ORACLE-L If you fts the tables, won't Oracle just place them into the Keep pool because it will think that it needs them? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 08, 2001 8:55 AM Alex, Further testing has proved that only the blocks needed to satisfy the query are loaded, and not the entire table. I can post the details if you wish, but there's your answer:) Mark -Original Message- Alex Sent: Friday, April 06, 2001 06:27 To: Multiple recipients of list ORACLE-L Looks like oracle reads into keep buffer pool only blocks that it needed but then keep it there. Would be intersting to know if let say table has more then 1 block and access is using indexes - so oracle needs to read only 1 block - will be all table loaded into keep buffer pool or only block needed to satisfy query. Alex Hillman -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 06, 2001 11:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: tirggers I thought that as well Tim, but wasn't sure whether Oracle loads the table at startup even if this is specified in the storage clause. The following test seems to show that it doesn't though: SQL create table DUMMY_TABLE (id number(3), dummy varchar2(5)) 2 storage(BUFFER_POOL KEEP); Table created. SQL select DATA_OBJECT_ID, OBJECT_TYPE 2from USER_OBJECTS 3 where OBJECT_NAME = 'DUMMY_TABLE'; DATA_OBJECT_ID OBJECT_TYPE -- -- 26408 TABLE SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 SQL connect internal/password Connected. SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 73701404 bytes Fixed Size75804 bytes Variable Size 56770560 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. Database opened. SQL connect mark/password SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 0 SQL select * from DUMMY_TABLE; no rows selected SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 Not sure on the trigger though, PL/SQL is not one of my strong points :) Mark -Original Message- Sawmiller Sent: Friday, April 06, 2001 02:07 To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject: tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman
RE: tirggers
Once accessed they should stay in memory the duration of the session. [EMAIL PROTECTED] 04/06/01 01:20PM I do specify Buffer_pool_keep but I was under the impression that you needed to FTS the tables to get them into memory? -Original Message- Sawmiller Sent: Friday, April 06, 2001 9:07 AM To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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 Sawmiller 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: Kevin Kostyszyn 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 Sawmiller 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: tirggers
Kevin, In Oracle 8.1.6 you can have a database trigger as: create database trigger name AFTER STARTUP (or something like that) which fires when the database is opened. So you could fire off a procedure to pin objects, eg it's good for pinning things like sys.standard or other packages at startup. Ian -Original Message- Sent: Monday, 9 April 2001 23:01 To: Multiple recipients of list ORACLE-L That's basically what I am doing at this time. I was just curious whether or not there was a database trigger that could fire off once the instance was started. Thanks for all of the help and input people. Sincerely Kevin -Original Message- Sent: Monday, April 09, 2001 6:10 AM To: Multiple recipients of list ORACLE-L That's right, Oracle seems to pull in all blocks needed to satisfy the query you send, which makes sense.. So if you do a select * from your_table; it should pull everything in to the buffer. One thing that you do have to consider, is that there is enough space in your KEEP buffer pool to store all of these tables.. Back to the root of your message though Kevin, could you not just write a script, that does a select * from your_tables, and execute this at start-up? How large are these tables also, AFAIK the KEEP buffer pool should really be used for smaller, lookup or code tables.. HTH Mark -Original Message- Kostyszyn Sent: Sunday, April 08, 2001 10:00 To: Multiple recipients of list ORACLE-L If you fts the tables, won't Oracle just place them into the Keep pool because it will think that it needs them? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 08, 2001 8:55 AM Alex, Further testing has proved that only the blocks needed to satisfy the query are loaded, and not the entire table. I can post the details if you wish, but there's your answer:) Mark -Original Message- Alex Sent: Friday, April 06, 2001 06:27 To: Multiple recipients of list ORACLE-L Looks like oracle reads into keep buffer pool only blocks that it needed but then keep it there. Would be intersting to know if let say table has more then 1 block and access is using indexes - so oracle needs to read only 1 block - will be all table loaded into keep buffer pool or only block needed to satisfy query. Alex Hillman -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 06, 2001 11:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: tirggers I thought that as well Tim, but wasn't sure whether Oracle loads the table at startup even if this is specified in the storage clause. The following test seems to show that it doesn't though: SQL create table DUMMY_TABLE (id number(3), dummy varchar2(5)) 2 storage(BUFFER_POOL KEEP); Table created. SQL select DATA_OBJECT_ID, OBJECT_TYPE 2from USER_OBJECTS 3 where OBJECT_NAME = 'DUMMY_TABLE'; DATA_OBJECT_ID OBJECT_TYPE -- -- 26408 TABLE SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 SQL connect internal/password Connected. SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 73701404 bytes Fixed Size75804 bytes Variable Size 56770560 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. Database opened. SQL connect mark/password SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 0 SQL select * from DUMMY_TABLE; no rows selected SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 Not sure on the trigger though, PL/SQL is not one of my strong points :) Mark -Original Message- Sawmiller Sent: Friday, April 06, 2001 02:07 To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject: tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see
RE: tirggers
My understanding was that it will be in the keep pool until database close or until something else will push it from the pool ( if one has more blocks for keep pool than keep pool size) - not until session which used these block ends. Alex Hillman -Original Message- From: Tim Sawmiller [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 8:35 AM To: Multiple recipients of list ORACLE-L Subject:RE: tirggers Once accessed they should stay in memory the duration of the session. [EMAIL PROTECTED] 04/06/01 01:20PM I do specify Buffer_pool_keep but I was under the impression that you needed to FTS the tables to get them into memory? -Original Message- Sawmiller Sent: Friday, April 06, 2001 9:07 AM To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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 Sawmiller 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: Kevin Kostyszyn 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
RE: tirggers
I agree with that, but don't you assing tables to the keep pool? And if that is the case and they are all in the keep pool then that would leave me to believe that they will not get booted from the keep pool until the db is shutdown and restarted? Kev -Original Message- Alex Sent: Monday, April 09, 2001 2:40 PM To: Multiple recipients of list ORACLE-L My understanding was that it will be in the keep pool until database close or until something else will push it from the pool ( if one has more blocks for keep pool than keep pool size) - not until session which used these block ends. Alex Hillman -Original Message- From: Tim Sawmiller [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 8:35 AM To: Multiple recipients of list ORACLE-L Subject:RE: tirggers Once accessed they should stay in memory the duration of the session. [EMAIL PROTECTED] 04/06/01 01:20PM I do specify Buffer_pool_keep but I was under the impression that you needed to FTS the tables to get them into memory? -Original Message- Sawmiller Sent: Friday, April 06, 2001 9:07 AM To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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 Sawmiller 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: Kevin Kostyszyn INET: [EMAIL
RE: tirggers
KEEP pool has the same LRU algoritm as default one (maybe there are some small differences) - but main thing is that if you assigned to KEEP pool objects with combined size than KEEP pool size (also CR blocks need to be considered) blocks of these objects will be pushed out of KEEP pool - same as it was for default pool. Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 3:26 PM To: Multiple recipients of list ORACLE-L Subject:RE: tirggers I agree with that, but don't you assing tables to the keep pool? And if that is the case and they are all in the keep pool then that would leave me to believe that they will not get booted from the keep pool until the db is shutdown and restarted? Kev -Original Message- Alex Sent: Monday, April 09, 2001 2:40 PM To: Multiple recipients of list ORACLE-L My understanding was that it will be in the keep pool until database close or until something else will push it from the pool ( if one has more blocks for keep pool than keep pool size) - not until session which used these block ends. Alex Hillman -Original Message- From: Tim Sawmiller [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 8:35 AM To: Multiple recipients of list ORACLE-L Subject:RE: tirggers Once accessed they should stay in memory the duration of the session. [EMAIL PROTECTED] 04/06/01 01:20PM I do specify Buffer_pool_keep but I was under the impression that you needed to FTS the tables to get them into memory? -Original Message- Sawmiller Sent: Friday, April 06, 2001 9:07 AM To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: tirggers
So let me see if I have it straight, as long as the keep pool is larger than the tables and/or objects that I want to put in it, I should be ok? Also, sorry about that spelling error earlier, I don't want to be assing tables to the pool I want to assign them to the pool:) Kev - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 09, 2001 7:05 PM KEEP pool has the same LRU algoritm as default one (maybe there are some small differences) - but main thing is that if you assigned to KEEP pool objects with combined size than KEEP pool size (also CR blocks need to be considered) blocks of these objects will be pushed out of KEEP pool - same as it was for default pool. Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 3:26 PM To: Multiple recipients of list ORACLE-L Subject: RE: tirggers I agree with that, but don't you assing tables to the keep pool? And if that is the case and they are all in the keep pool then that would leave me to believe that they will not get booted from the keep pool until the db is shutdown and restarted? Kev -Original Message- Alex Sent: Monday, April 09, 2001 2:40 PM To: Multiple recipients of list ORACLE-L My understanding was that it will be in the keep pool until database close or until something else will push it from the pool ( if one has more blocks for keep pool than keep pool size) - not until session which used these block ends. Alex Hillman -Original Message- From: Tim Sawmiller [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 8:35 AM To: Multiple recipients of list ORACLE-L Subject: RE: tirggers Once accessed they should stay in memory the duration of the session. [EMAIL PROTECTED] 04/06/01 01:20PM I do specify Buffer_pool_keep but I was under the impression that you needed to FTS the tables to get them into memory? -Original Message- Sawmiller Sent: Friday, April 06, 2001 9:07 AM To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject: tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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 Sawmiller 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
RE: tirggers
Alex, Further testing has proved that only the blocks needed to satisfy the query are loaded, and not the entire table. I can post the details if you wish, but there's your answer:) Mark -Original Message- Alex Sent: Friday, April 06, 2001 06:27 To: Multiple recipients of list ORACLE-L Looks like oracle reads into keep buffer pool only blocks that it needed but then keep it there. Would be intersting to know if let say table has more then 1 block and access is using indexes - so oracle needs to read only 1 block - will be all table loaded into keep buffer pool or only block needed to satisfy query. Alex Hillman -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 06, 2001 11:54 AM To: Multiple recipients of list ORACLE-L Subject:RE: tirggers I thought that as well Tim, but wasn't sure whether Oracle loads the table at startup even if this is specified in the storage clause. The following test seems to show that it doesn't though: SQL create table DUMMY_TABLE (id number(3), dummy varchar2(5)) 2 storage(BUFFER_POOL KEEP); Table created. SQL select DATA_OBJECT_ID, OBJECT_TYPE 2from USER_OBJECTS 3 where OBJECT_NAME = 'DUMMY_TABLE'; DATA_OBJECT_ID OBJECT_TYPE -- -- 26408 TABLE SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 SQL connect internal/password Connected. SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 73701404 bytes Fixed Size75804 bytes Variable Size 56770560 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. Database opened. SQL connect mark/password SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 0 SQL select * from DUMMY_TABLE; no rows selected SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 Not sure on the trigger though, PL/SQL is not one of my strong points :) Mark -Original Message- Sawmiller Sent: Friday, April 06, 2001 02:07 To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex
Re: tirggers
If you fts the tables, won't Oracle just place them into the Keep pool because it will think that it needs them? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 08, 2001 8:55 AM Alex, Further testing has proved that only the blocks needed to satisfy the query are loaded, and not the entire table. I can post the details if you wish, but there's your answer:) Mark -Original Message- Alex Sent: Friday, April 06, 2001 06:27 To: Multiple recipients of list ORACLE-L Looks like oracle reads into keep buffer pool only blocks that it needed but then keep it there. Would be intersting to know if let say table has more then 1 block and access is using indexes - so oracle needs to read only 1 block - will be all table loaded into keep buffer pool or only block needed to satisfy query. Alex Hillman -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 06, 2001 11:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: tirggers I thought that as well Tim, but wasn't sure whether Oracle loads the table at startup even if this is specified in the storage clause. The following test seems to show that it doesn't though: SQL create table DUMMY_TABLE (id number(3), dummy varchar2(5)) 2 storage(BUFFER_POOL KEEP); Table created. SQL select DATA_OBJECT_ID, OBJECT_TYPE 2from USER_OBJECTS 3 where OBJECT_NAME = 'DUMMY_TABLE'; DATA_OBJECT_ID OBJECT_TYPE -- -- 26408 TABLE SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 SQL connect internal/password Connected. SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 73701404 bytes Fixed Size75804 bytes Variable Size 56770560 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. Database opened. SQL connect mark/password SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 0 SQL select * from DUMMY_TABLE; no rows selected SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 Not sure on the trigger though, PL/SQL is not one of my strong points :) Mark -Original Message- Sawmiller Sent: Friday, April 06, 2001 02:07 To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject: tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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 Sawmiller INET: [EMAIL PROTECTED] Fat City Network
RE: tirggers
Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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 Sawmiller 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: tirggers
I thought that as well Tim, but wasn't sure whether Oracle loads the table at startup even if this is specified in the storage clause. The following test seems to show that it doesn't though: SQL create table DUMMY_TABLE (id number(3), dummy varchar2(5)) 2 storage(BUFFER_POOL KEEP); Table created. SQL select DATA_OBJECT_ID, OBJECT_TYPE 2from USER_OBJECTS 3 where OBJECT_NAME = 'DUMMY_TABLE'; DATA_OBJECT_ID OBJECT_TYPE -- -- 26408 TABLE SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 SQL connect internal/password Connected. SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 73701404 bytes Fixed Size75804 bytes Variable Size 56770560 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. Database opened. SQL connect mark/password SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 0 SQL select * from DUMMY_TABLE; no rows selected SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 Not sure on the trigger though, PL/SQL is not one of my strong points :) Mark -Original Message- Sawmiller Sent: Friday, April 06, 2001 02:07 To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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 Sawmiller 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: Mark Leith 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
RE: tirggers
Looks like oracle reads into keep buffer pool only blocks that it needed but then keep it there. Would be intersting to know if let say table has more then 1 block and access is using indexes - so oracle needs to read only 1 block - will be all table loaded into keep buffer pool or only block needed to satisfy query. Alex Hillman -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 06, 2001 11:54 AM To: Multiple recipients of list ORACLE-L Subject:RE: tirggers I thought that as well Tim, but wasn't sure whether Oracle loads the table at startup even if this is specified in the storage clause. The following test seems to show that it doesn't though: SQL create table DUMMY_TABLE (id number(3), dummy varchar2(5)) 2 storage(BUFFER_POOL KEEP); Table created. SQL select DATA_OBJECT_ID, OBJECT_TYPE 2from USER_OBJECTS 3 where OBJECT_NAME = 'DUMMY_TABLE'; DATA_OBJECT_ID OBJECT_TYPE -- -- 26408 TABLE SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 SQL connect internal/password Connected. SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 73701404 bytes Fixed Size75804 bytes Variable Size 56770560 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. Database opened. SQL connect mark/password SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 0 SQL select * from DUMMY_TABLE; no rows selected SQL select count(*) buffers 2from V$BH 3 where OBJD = 26408; BUFFERS -- 1 Not sure on the trigger though, PL/SQL is not one of my strong points :) Mark -Original Message- Sawmiller Sent: Friday, April 06, 2001 02:07 To: Multiple recipients of list ORACLE-L Why not just specify BUFFER POOL KEEP in an alter table statement? [EMAIL PROTECTED] 04/05/01 05:56PM What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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
RE: tirggers
What is the problem to write something like Select * from table_name for all tables that you need or if there are too many such tables - create a new table with names of the tables and use dynamic SQL . Alex Hillman -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 5:28 PM To: Multiple recipients of list ORACLE-L Subject:tirggers Hi Intelligent DBA's I was wondering if anyone knows how to create a trigger that would fire off at database startup time and run a script to do full table scans on several tables to get them into the buffer cache keep pool? Right now I do it manually and would like to automate the task. Sincerely, Kevin Kostyszyn DBA Dulcian, Inc www.dulcian.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn 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: Hillman, Alex 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).
