RE: Copying statistics : used a lot ????
What a timely thread! We've been discussing this very issue back and forth for some time now in our organization. We are getting a new application, which presumably will run on something like lower-end Superdome 16-way, ~30GB RAM type of box. The question is whether 4-way, 4GB RAM Rp5470 (entry-level) will do as a test server or it should be a close mirror of the production one? The argument of those against a similar to a Prod box is simple: Can't afford another one for this project. Don't you know how to use dbms_stats to convince CBO it's on Superdome with 16 CPUs and millions of rows of data and not on a 4-way, couple of thousands in row sources? So if we can't afford what Raj describes - is a smaller server a viable solution for a test box? Or we have to convince damanagement that their can't afford is going to cost them more in the long run? (easlier said than done) --- As Cris mentioned I've read Tom's take on this, but it only confused me futher. Tom states: Some people adopt the strategy of importing the prod statistics ... and think they can get optimizer to generate the plans that will be used in prod and test using that data ... That approach will work only if you can read a query plan and be 100% confident that the plan is good and will give subsecond response times ... I don't think I can make such a judgment call... I don't follow. Does this imply that with importing stats we can't get 100% identical CBO decisions/executions plans in a DB on a smaller machine? Or is it that we have to be 100% confident that we replicated all the stats from Prod and it is not a simple task? Or something else? ... Most people are striving to get query plans that use indexes all of the time, without realizing that as you scale up, indexes may not be the best solution .. This part I understood even less. After reading Cary's excellent paper on scalability I thought that O(n) type of scalability of FTS is worse than say O(log2, n) of IRS? Wouldn't it be correct to say than, that if today on a thousand row tables I get index access path delivering better response time that table scan, I can expect this to stay the same (or better) when my data gets to a million rows range? Is it the scalability of NL vs HJ Tom is taking about? Bitmap/Domain indexes? Or is it a general statement? ... This is not to say that ... import statistics is not very useful. Quite the contrary - I've seen people use (with great success) the ability to import/export statistics, but ***not to tune in test***. Instead they take the results of statistics gathering done in test and import into production! Quite the reverse of what most people initially consider using dbms_stats for... The last remark certainly applies to me. With all due respect to Tom, I got only more confused ater reading the above. Can somebody enlighten me? TIA, Boris Dali. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Our production and test systems are same ... test lags 24 hours behind production that's all. But I have successfully used dbms_stats to copy over stats from production to test on a table by table basis to verify explain plans. My opinion WAD - Works as designed ... remember to take a backup of existing stats on test in a separate table so you can reload them when needed quickly. My experience is on 9ir2 only for this feature. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Friday, September 12, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Well no, I have bought the Expert one on one book. I check on his web site and I found one reference where he addresses the use of changing the stats. Usually you can find what is its opinion just by the tone, but this time I was not able to see if he's against or not on this. Can you share more of what's in the book ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Grabowy, Chris Sent: Thursday, September 11, 2003 5:44 PM To: Multiple recipients of list ORACLE-L By chance, do you have Tom Kyte's latest book? Effective Oracle by Design?? He states his opinion on this approach on page 30, section entitled Test Against Representative Data. -Original Message- Stephane Paquette Sent: Thursday, September 11, 2003 4:38 PM To: Multiple recipients of list ORACLE-L HI, I was wandering if a lot of people are copying statistics using dbms_stats from production to test environment to see what will be the access plan. If not used, why ? no time to look at it, bugged, not usefull ,... ?
RE: Copying statistics : used a lot ????
What a timely thread! We've been discussing this very issue back and forth for some time now in our organization. We are getting a new application, which presumably will run on something like lower-end Superdome 16-way, ~30GB RAM type of box. The question is whether 4-way, 4GB RAM Rp5470 (entry-level) will do as a test server or it should be a close mirror of the production one? The argument of those against a similar to a Prod box is simple: Can't afford another one for this project. Don't you know how to use dbms_stats to convince CBO it's on Superdome with 16 CPUs and millions of rows of data and not on a 4-way, couple of thousands in row sources? So if we can't afford what Raj describes - is a smaller server a viable solution for a test box? Or we have to convince damanagement that their can't afford is going to cost them more in the long run? (easlier said than done) --- As Cris mentioned I've read Tom's take on this, but it only confused me futher. Tom states: Some people adopt the strategy of importing the prod statistics ... and think they can get optimizer to generate the plans that will be used in prod and test using that data ... That approach will work only if you can read a query plan and be 100% confident that the plan is good and will give subsecond response times ... I don't think I can make such a judgment call... I don't follow. Does this imply that with importing stats we can't get 100% identical CBO decisions/executions plans in a DB on a smaller machine? Or is it that we have to be 100% confident that we replicated all the stats from Prod and it is not a simple task? Or something else? ... Most people are striving to get query plans that use indexes all of the time, without realizing that as you scale up, indexes may not be the best solution ... This part I understood even less. After reading Cary's excellent paper on scalability I thought that O(n) type of scalability of FTS is worse than say O(log2, n) of IRS? Wouldn't it be correct to say than, that if today on a thousand row tables I get index access path delivering better response time that table scan, I can expect this to stay the same (or better) when my data gets to a million rows range? Is it the scalability of NL vs HJ Tom is taking about? Bitmap/Domain indexes? Or is it a general statement? ... This is not to say that ... import statistics is not very useful. Quite the contrary - I've seen people use (with great success) the ability to import/export statistics, but ***not to tune in test***. Instead they take the results of statistics gathering done in test and import into production! Quite the reverse of what most people initially consider using dbms_stats for... The last remark certainly applies to me. With all due respect to Tom, I got only more confused ater reading the above. Can somebody enlighten me? TIA, Boris Dali. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Our production and test systems are same ... test lags 24 hours behind production that's all. But I have successfully used dbms_stats to copy over stats from production to test on a table by table basis to verify explain plans. My opinion WAD - Works as designed ... remember to take a backup of existing stats on test in a separate table so you can reload them when needed quickly. My experience is on 9ir2 only for this feature. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Friday, September 12, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Well no, I have bought the Expert one on one book. I check on his web site and I found one reference where he addresses the use of changing the stats. Usually you can find what is its opinion just by the tone, but this time I was not able to see if he's against or not on this. Can you share more of what's in the book ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Grabowy, Chris Sent: Thursday, September 11, 2003 5:44 PM To: Multiple recipients of list ORACLE-L By chance, do you have Tom Kyte's latest book? Effective Oracle by Design?? He states his opinion on this approach on page 30, section entitled Test Against Representative Data. -Original Message- Stephane Paquette Sent: Thursday, September 11, 2003 4:38 PM To: Multiple recipients of list ORACLE-L HI, I was wandering if a lot of people are copying statistics using dbms_stats from production to test environment to see what will be the access plan. If not used, why ? no time to look at it, bugged, not usefull ,... ?
Re: Copying statistics : used a lot ????
wait a second. the CBO takes into consideration your system statistics when you analyze? Is that new in 9i? I thought the export stats and import stats were used if you wanted a 'smaller subset' of data. so you mimic the data stats? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, September 21, 2003 3:34 PM What a timely thread! We've been discussing this very issue back and forth for some time now in our organization. We are getting a new application, which presumably will run on something like lower-end Superdome 16-way, ~30GB RAM type of box. The question is whether 4-way, 4GB RAM Rp5470 (entry-level) will do as a test server or it should be a close mirror of the production one? The argument of those against a similar to a Prod box is simple: Can't afford another one for this project. Don't you know how to use dbms_stats to convince CBO it's on Superdome with 16 CPUs and millions of rows of data and not on a 4-way, couple of thousands in row sources? So if we can't afford what Raj describes - is a smaller server a viable solution for a test box? Or we have to convince damanagement that their can't afford is going to cost them more in the long run? (easlier said than done) --- As Cris mentioned I've read Tom's take on this, but it only confused me futher. Tom states: Some people adopt the strategy of importing the prod statistics ... and think they can get optimizer to generate the plans that will be used in prod and test using that data ... That approach will work only if you can read a query plan and be 100% confident that the plan is good and will give subsecond response times ... I don't think I can make such a judgment call... I don't follow. Does this imply that with importing stats we can't get 100% identical CBO decisions/executions plans in a DB on a smaller machine? Or is it that we have to be 100% confident that we replicated all the stats from Prod and it is not a simple task? Or something else? ... Most people are striving to get query plans that use indexes all of the time, without realizing that as you scale up, indexes may not be the best solution .. This part I understood even less. After reading Cary's excellent paper on scalability I thought that O(n) type of scalability of FTS is worse than say O(log2, n) of IRS? Wouldn't it be correct to say than, that if today on a thousand row tables I get index access path delivering better response time that table scan, I can expect this to stay the same (or better) when my data gets to a million rows range? Is it the scalability of NL vs HJ Tom is taking about? Bitmap/Domain indexes? Or is it a general statement? ... This is not to say that ... import statistics is not very useful. Quite the contrary - I've seen people use (with great success) the ability to import/export statistics, but ***not to tune in test***. Instead they take the results of statistics gathering done in test and import into production! Quite the reverse of what most people initially consider using dbms_stats for... The last remark certainly applies to me. With all due respect to Tom, I got only more confused ater reading the above. Can somebody enlighten me? TIA, Boris Dali. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Our production and test systems are same ... test lags 24 hours behind production that's all. But I have successfully used dbms_stats to copy over stats from production to test on a table by table basis to verify explain plans. My opinion WAD - Works as designed ... remember to take a backup of existing stats on test in a separate table so you can reload them when needed quickly. My experience is on 9ir2 only for this feature. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Friday, September 12, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Well no, I have bought the Expert one on one book. I check on his web site and I found one reference where he addresses the use of changing the stats. Usually you can find what is its opinion just by the tone, but this time I was not able to see if he's against or not on this. Can you share more of what's in the book ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Grabowy, Chris Sent: Thursday, September 11, 2003 5:44 PM To: Multiple recipients of list ORACLE-L By chance, do you have Tom Kyte's latest book? Effective Oracle by Design?? He
RE: Copying statistics : used a lot ????
Well no, I have bought the Expert one on one book. I check on his web site and I found one reference where he addresses the use of changing the stats. Usually you can find what is its opinion just by the tone, but this time I was not able to see if he's against or not on this. Can you share more of what's in the book ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Grabowy, Chris Sent: Thursday, September 11, 2003 5:44 PM To: Multiple recipients of list ORACLE-L By chance, do you have Tom Kyte's latest book? Effective Oracle by Design?? He states his opinion on this approach on page 30, section entitled Test Against Representative Data. -Original Message- Stephane Paquette Sent: Thursday, September 11, 2003 4:38 PM To: Multiple recipients of list ORACLE-L HI, I was wandering if a lot of people are copying statistics using dbms_stats from production to test environment to see what will be the access plan. If not used, why ? no time to look at it, bugged, not usefull ,... ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette 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: Copying statistics : used a lot ????
Title: RE: Copying statistics : used a lot Our production and test systems are same ... test lags 24 hours behind production that's all. But I have successfully used dbms_stats to copy over stats from production to test on a table by table basis to verify explain plans. My opinion WAD - Works as designed ... remember to take a backup of existing stats on test in a separate table so you can reload them when needed quickly. My experience is on 9ir2 only for this feature. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Stephane Paquette [mailto:[EMAIL PROTECTED]] Sent: Friday, September 12, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: Copying statistics : used a lot Well no, I have bought the Expert one on one book. I check on his web site and I found one reference where he addresses the use of changing the stats. Usually you can find what is its opinion just by the tone, but this time I was not able to see if he's against or not on this. Can you share more of what's in the book ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Grabowy, Chris Sent: Thursday, September 11, 2003 5:44 PM To: Multiple recipients of list ORACLE-L By chance, do you have Tom Kyte's latest book? Effective Oracle by Design?? He states his opinion on this approach on page 30, section entitled Test Against Representative Data. -Original Message- Stephane Paquette Sent: Thursday, September 11, 2003 4:38 PM To: Multiple recipients of list ORACLE-L HI, I was wandering if a lot of people are copying statistics using dbms_stats from production to test environment to see what will be the access plan. If not used, why ? no time to look at it, bugged, not usefull ,... ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette 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). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Copying statistics : used a lot ????
I don't use it a lot, since our test systems are currently complete copies of our li'l ol' 30GB production DB. But it's nice to have for things like the 9i RAC test, where we can't have a complete copy of the data (although I've yet to try it on there). And it has worked well so far from 8.1.7.4 to 8.1.7.4, all on HP/UX 11.0. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Stephane Paquette [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 3:38 PM To: Multiple recipients of list ORACLE-L Subject: Copying statistics : used a lot HI, I was wandering if a lot of people are copying statistics using dbms_stats from production to test environment to see what will be the access plan. If not used, why ? no time to look at it, bugged, not usefull ,... ? Stephane Paquette -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Copying statistics : used a lot ????
By chance, do you have Tom Kyte's latest book? Effective Oracle by Design?? He states his opinion on this approach on page 30, section entitled Test Against Representative Data. -Original Message- Stephane Paquette Sent: Thursday, September 11, 2003 4:38 PM To: Multiple recipients of list ORACLE-L HI, I was wandering if a lot of people are copying statistics using dbms_stats from production to test environment to see what will be the access plan. If not used, why ? no time to look at it, bugged, not usefull ,... ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grabowy, Chris 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: Copying statistics : used a lot ????
I use it a lot. I create mock databases where I just load the schema (from export rows=no) and then copy the statistics on top. That lets me do what-if scenarios, changing sql, init parameters, statistics, or even the Oracle version, without impacting anyone. When I find something that looks promising I can then try that out in a test database with actual data. At 12:37 PM 9/11/2003 -0800, you wrote: HI, I was wandering if a lot of people are copying statistics using dbms_stats from production to test environment to see what will be the access plan. If not used, why ? no time to look at it, bugged, not usefull ,... ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Copying statistics : used a lot ????
Stephane, No time to look at it enough to do it. Also, not a lot of time is spent tuning our test/dev environments. But I'd like to do it. Stephen [EMAIL PROTECTED] 09/11/03 01:37PM HI, I was wandering if a lot of people are copying statistics using dbms_stats from production to test environment to see what will be the access plan. If not used, why ? no time to look at it, bugged, not usefull ,... ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert 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).
