Oh Where Oh Where Is My Redo Coming From
Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: Oh Where Oh Where Is My Redo Coming From
Ethan - 28-gig GAAAK! Ideas: 1. Sample your SQL buffer to start getting some ideas. 2. Use LogMiner to read some of the archive logs to see the DML statements. You can also directly see how much redo is being generated by each statement. I think you have the right idea, probably some inefficient updates. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, February 21, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: DENNIS WILLIAMS 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: Oh Where Oh Where Is My Redo Coming From
Hi, The simpler approach is to check the user level redo (or session level redo) using the v$sysstat,sesstat views and you can find the programmes associated with those huge (!) redo. Dumping the redologs and analyzing is just complex when you have a simple solution ;) = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: Oh Where Oh Where Is My Redo Coming From
It might work to turn on monitoring on the tables. alter table xyz monitoring; Then periodically check dba_tab_modifications. -Original Message- The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Oh Where Oh Where Is My Redo Coming From
Which version of Oracle ? Has someone switched on supplemental logging at the database level, perhaps ? Have you got dbms_job kicking in every 5 seconds with job_queues set to 10 ? (Honest, I have seen it happen, and the effect on redo was astonishing - and there was only one job actually ever available to run). Does the application use lots of transient tables which it fills and then deletes / rolls back. Is the volume of redo very much larger than the volume of UNDO, as this may indicate table creation, index rebuilds, mass inserts rather than update activity (which would tend to leave the redo in the ballpark of double the undo). Are there any files with an extreme number of writes (other than temporary tablespace files of course) - as you might want to track the objects in those files rather than trying to monitor the whole database. Are there any files with lots of multiblock writes - (apart from temp) as this tends to indicate table moves, index rebuilds, as well as direct mode inserts. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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: 21 February 2003 17:35 Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? also send the HELP command for other information (like subscribing). -- 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: Oh Where Oh Where Is My Redo Coming From
Ethan, monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. Note that the amount of redo does not depend on the average row size. It depends on the amount of _change_ (+ some overhead). This argument might skew the situation towards a table that has a large row size but that does not have that many updates... I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. What you _do_ need to do is to use this SQL to detect the SIDs performing redo: select sid, name, value from v$statname n, v$sesstat v where v.statistic# = n.statistic# and name like 'redo size' and value 10 order by value desc You can then look at V$OPEN_CURSORS for those SIDs... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 I don't know what the future holds for me, but I do know who holds my future! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Oh Where Oh Where Is My Redo Coming From
So here are two takes at the problem, one takes a look at costly (in regards to amount of redo) tables and the other indexes. Note this is only a way to guestimate this information. select owner, table_name, round((ratio_to_report(ttl) over ()) * 100, 1) as percent_ratio from ( select (m.inserts+m.deletes+m.updates)*t.avg_row_len ttl, t.owner, t.table_name from dba_tables t, all_tab_modifications m where t.table_name=m.table_name and t.owner=m.table_owner ) order by 3 desc; select owner, table_name, round((ratio_to_report(ttl) over ()) * 100, 1) as percent_ratio from ( select count(*)*sum((m.inserts+m.deletes+m.updates)) ttl, i.owner, i.table_name from all_indexes i, all_ind_columns c, all_tab_modifications m where i.index_name=c.index_name and i.table_name=c.table_name and i.owner=m.table_owner and i.table_name=m.table_name group by i.owner, i.table_name ) order by 3 desc; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: Oh Where Oh Where Is My Redo Coming From
Title: RE: Oh Where Oh Where Is My Redo Coming From Ethan, I think focusing on which transaction generates more redo will be more helpful than which object ... right? Let me know if I didn't understand your question completely ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Post, Ethan [mailto:[EMAIL PROTECTED]] Sent: Friday, February 21, 2003 10:44 AM To: Multiple recipients of list ORACLE-L Subject: Oh Where Oh Where Is My Redo Coming From Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: Oh Where Oh Where Is My Redo Coming From
Indexes on such tables which has DML... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 21 Feb 2003 07:44:18 -0800 Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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). _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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: Oh Where Oh Where Is My Redo Coming From
Ethan, v$sess_io will provide a list of the sessions generating block changes and, therefore, redo. Link this back to v$session, etc. for the 'offending' sessions, sql. I've used this several times with great success. Sometimes it was a data load, others it was bad sql. When you change a block (insert/update/delete), you also generate changes to indexes, undo segments (1 change for each row and 1 change for each index affected) and perhaps the data dictionary for space management. Dan Fink Post, Ethan wrote: Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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: Oh Where Oh Where Is My Redo Coming From
Yes, that is what I was saying, however large rows or tables with a lot of indexes would also be prone to generate more redo, that is why I suggest joining DBA_TAB_MODIFICATIONS to DBA_TABLES to get avg_row_len and DBA_IND_COLUMNS to get the total # of columns indexes on the table, the thought being the more columns the more likley updates, inserts and deletes will cause index generated redo. See my other post for the solution (SQL) I came up with. - Ethan -Original Message- Sent: Friday, February 21, 2003 10:44 AM To: Multiple recipients of list ORACLE-L It might work to turn on monitoring on the tables. alter table xyz monitoring; Then periodically check dba_tab_modifications. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: Oh Where Oh Where Is My Redo Coming From
Sure that is the way I would typically do it, but in this case I have an application that is running 8000 batch processes per day, redo is very consistent for most of the 24 hours. I asked myself what is the simplest way to figure out which objects likely generate all of this redo. Monitoring sesstat is not going to be the most efficient and accurate method in this case. As it turns out the results (based on the SQL I posted) show the F0911 (JDE Oneworld GL Ledger) table likely produces at least 70-80% of the redo. During the batch job tuning process I will focus on tuning jobs that effect this table. There are also a ridiculous # of indexes on this table, over 120 columns involved in all of the indexes, I am sure many are redundant. Since redo log contention is one of the primary issues with this database I should see some dramatic improvements once a few of the jobs are tuned. - Ethan -Original Message- Sent: Friday, February 21, 2003 11:24 AM To: Multiple recipients of list ORACLE-L Hi, The simpler approach is to check the user level redo (or session level redo) using the v$sysstat,sesstat views and you can find the programmes associated with those huge (!) redo. Dumping the redologs and analyzing is just complex when you have a simple solution ;) = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: Oh Where Oh Where Is My Redo Coming From
Title: RE: Oh Where Oh Where Is My Redo Coming From Since SharePlex for Oracle and LiveReorg are dependant on redo log volumes, transaction sizes, and things like that we have developed a free utility that parses through the redo logs for some pre-defined amount of time, and let's you know how many operations are on each table, average operations per second, and peak operations per second. It should give you a really good idea where the activity is coming from. Send me a private email if you want me to get you set up on this, and I can send you the files, and the instructions on how to use it. Nick ([EMAIL PROTECTED]) p.s. 28GB is good, but we've seen much more... around 100GB is massive, and we've seen maybe 2-3 databases up to 120GB per day. -Original Message- From: Post, Ethan [mailto:[EMAIL PROTECTED]] Sent: Friday, February 21, 2003 7:44 AM To: Multiple recipients of list ORACLE-L Subject: Oh Where Oh Where Is My Redo Coming From Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: Oh Where Oh Where Is My Redo Coming From
John that doesn't make sense to me. Deleting 10,000 rows from a table with 200 columns is certainly going to generate more redo than a table with 1 column (which I am sure you know, so there must be some confusion in my understanding). The avg_row_len is going to be much bigger in the table with 200 columns. I think grabbing redo info out of sesstat is a great way to do this but the drawback is that I have to sit here and poll v$sesstat every N minutes trying to capture the session and SQL generating the redo. Not very practical in all circumstances. What I wanted is a quick way to sit down at any database and get a rough guess as to the objects which likely are involved in creating the most redo. This can help me get pointing in the direction of tuning HR jobs, Finance jobs, Inventory jobs etc...pretty easy to go query V$SQLAREA for table and INSERT, UPDATE DELETE at that point. The query I posted shows that the bulk of redo is coming from single table in the JDE Oweworld schema. It is a lot more than I am use to seeing in other systems I manage from the same table so I am pretty sure they have some poor code someplace. I can get the # of jobs and how long they run from the job queue tables, F986110. This will help me identify the top 5 jobs that hit the system. From there I will focus on the one or two jobs that hit the F0911 table. This approach to tuning is more of a top down approach. I don't want to try focusing on a single SQL statement/session as a starting point, the batch jobs will be my starting point. This information combined with the top N jobs will put me right where I want to be to begin making the biggest impact with the least amount of effort. - Ethan -Original Message- Sent: Friday, February 21, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Ethan, monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. Note that the amount of redo does not depend on the average row size. It depends on the amount of _change_ (+ some overhead). This argument might skew the situation towards a table that has a large row size but that does not have that many updates... I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. What you _do_ need to do is to use this SQL to detect the SIDs performing redo: select sid, name, value from v$statname n, v$sesstat v where v.statistic# = n.statistic# and name like 'redo size' and value 10 order by value desc You can then look at V$OPEN_CURSORS for those SIDs... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: Oh Where Oh Where Is My Redo Coming From
So what about an app server process that has been handling requests for 2 weeks? In this case I still won't know anything about the process(es) causing the redo. I have monitoring scripts that can trigger execution of another script(s) to enact SQL trace on the top N sessions generating redo, commits, whatever...and send me the trace files. It is nice when I see a process that runs at some point in the night and I want trace files but I also want sleep. -Original Message- Sent: Friday, February 21, 2003 11:59 AM To: Multiple recipients of list ORACLE-L select sid, name, value from v$statname n, v$sesstat v where v.statistic# = n.statistic# and name like 'redo size' and value 10 order by value desc -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: Oh Where Oh Where Is My Redo Coming From
Title: RE: Oh Where Oh Where Is My Redo Coming From Ethan, There is a option in TOAD that automatically commits after every query ... turn that off ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Post, Ethan [mailto:[EMAIL PROTECTED]] Sent: Friday, February 21, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oh Where Oh Where Is My Redo Coming From As always Jonathan you give me plenty of areas to go looking, thanks for the great advice. One of the problems is cron kicks off various application processes in the line of over 60 per minute (log in, do something, log out, oh and commit between each row of course). That on top of hundreds of users accessing the system and oh 4 or 5 developers working in the same database doing whatever they please. I found one guy the other day had issued 290,000 commits in a single session using Toad. Argg! You get the idea. I am attempting to bring in a little sanity to the situation before they recommend buying more CPUs! - Ethan *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.*1
Re: Oh Where Oh Where Is My Redo Coming From
Until I found your comment about 8,000 sessions per day, and the 60 batch jobs in a minute, I was going to say that Dan Fink's idea sounded really good - especially if you extend it to switching on the audit trail (audit_trail=db) and auditing by session, as this leaves a log on time, log off time db block changes (qua logical writes) in the audit trail. For a more manageable number of connections per day, this might give you a clue about who is doing most data change when. (Bear in mind, that a user that does a massive data change could leave lots of dirty blocks around, and the next big report might generate a huge amount of redo due to dirty block cleanout. So some of the redo guilt could be effectively randomly distributed across innocent victims). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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: 21 February 2003 20:06 As always Jonathan you give me plenty of areas to go looking, thanks for the great advice. One of the problems is cron kicks off various application processes in the line of over 60 per minute (log in, do something, log out, oh and commit between each row of course). That on top of hundreds of users accessing the system and oh 4 or 5 developers working in the same database doing whatever they please. I found one guy the other day had issued 290,000 commits in a single session using Toad. Argg! You get the idea. I am attempting to bring in a little sanity to the situation before they recommend buying more CPUs! - Ethan -- 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).
