Oracle tuning question

Post #237,785 by admin 12/12/05 3:30:11 PM Reply	Oracle tuning question Say you had a largish transactional database. Say also that this database has some tables that get a lot of traffic with transient data. These tables produce about 2Gig of redo log files every 5 minutes. When the logs are rolled at 2G, the procedure can cause slowdowns acorss the board for everything running SQL statements from outside the database. A good example would be a login check that checks a session table and updates a last-seen timestamp; this might go from an average duration of .5s to 3s during log rolls, sometimes as high as 10s. Question the first: is it possible to turn off transaction logging for a single table? Or at least a single tablespace? Or at least a single schema? Ie. does the table have to be in a completely different database to be log-free if other tables have transaction logs? Question the second: if the answer to the first question is no, then suppose you have a 2G file cache in the OS (Solaris). Solaris is supposedly limited to 2G. If the redo log size is 2G, then theoretically this cache is being eaten by the redo logs every 5 minutes, possibly causing the slowdown. Does this sound realistic? And if so, would cutting the redo log size to 1G possibly help? Question the third: what else can be done to gett rid of these periodic slowdowns? Thankee sai. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #237,788 by drewk 12/12/05 3:41:55 PM Reply	Try doing it in JavaScript Sorry, I've been reading [link\|http://thedailywtf.com\|The Daily WTF]. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #237,808 by ChrisR 12/12/05 6:14:54 PM Reply	Attach the table to MS Access?
Post #237,791 by boxley 12/12/05 3:59:17 PM Reply	ln /dev/null /oradata/archive/redolog.log "the reason people don't buy conspiracy theories is that they think conspiracy means everyone is on the same program. Thats not how it works. Everybody has a different program. They just all want the same guy dead. Socrates was a gadfly, but I bet he took time out to screw somebodies wife" Gus Vitelli Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 49 years. meep questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org]
Post #237,800 by ben_tilly 12/12/05 5:21:07 PM Reply	Answers It is possible to turn off transaction logging for a single table. I know it because we do it, ask a real DBA for how. I suspect that the NOLOGGING keyword plays into it but I am too lazy to check the syntax. Be sure to ONLY do this on tables that you are willing to lose data from in the event of a crash. Sessions are a good example of where to do it. There are many other things that you can also do. 1. Remove indexes on heavily used tables. If you change a block, that block has to get written back. If a table has 3 indexes, then changes to that table can wind up in 4 blocks, generating 4x the redo. If you are only really using 2 of those indexes, get rid of the last one. 2. Find ways to avoid generating transactions. For instance you might keep track of when people logged in in a permanent table. But do you care if the millisecond on their last login is right? If not then only update the session time if it is more than 10 minutes old. You'll get rid of a lot of updates, and the loss of data quality is marginal. 3. Throw hardware at it. A good DBA should be able to make a lot of recommendations about layout of multiple disks, what hardware to use, etc. 4. If you're using Oracle 10 G enterprise edition, check out the SQL Access Advisor. It allows you to take a snapshot of your database under load, examine your workload, and make recommendations on things like whether to add/remove indexes, what hardware changes are likely to help, etc. Good people can find things that it can't. But they won't generate as many good suggestions as quickly. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #237,807 by admin 12/12/05 5:48:34 PM Reply	Re: Answers NOLOGGING only turns off redo logs for direct path; this doesn't appear to do anything for normal insert or update operations. I would as a real DBA, but I'm not sure I know a real DBA, hence my questioning. Implications are left as an exercise to the reader. 1) Natch, already done. 2) This is not that kind of table. 95% of the redo log is from a single table with a lot of churn (think hundreds of changes per second). 3) Done, and cf. previous comment about a real DBA. 4) 9i. As a side question, I wonder how reliable/efficient a db link to another instance in the same server would be. That instance could be no-log. Thanks. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #237,824 by ben_tilly 12/12/05 8:33:56 PM Reply	I talked to a real DBA And you're right about NOLOGGING. I had misremembered what we do with sessions. It turns out that we just suffer lots of redo from that. :-( According to the DBA, a dblink won't work either. Oracle considers redo and transactional integrity to be a basic part of being a database. You can't turn it off. I don't know what you have done in terms of configuring things. I assume that you've done obvious things like making sure that writes are spread evenly across multiple disks and all that. If all else has been tried, you either need to use another database, or you need to scale up in some way. A large company that I am familiar with has a similar problem on a much larger scale. Their answer is to divide their data across many independent databases so that each one stays at acceptable load. There are some problems with the exact approach that they take, but the principle remains good. I have not tried Oracle's clustering solution, which they like to call a "database grid", but they claim that it can be for any combination that you want of high availability or performance. Setting it up for performance could make your problem go away in a scaleable way. (With, of course, scaleable licensing costs...) Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #237,826 by admin 12/12/05 8:43:03 PM Reply	Re: I talked to a real DBA According to the DBA, a dblink won't work either. Oracle considers redo and transactional integrity to be a basic part of being a database. You can't turn it off. My understanding is that you can at least turn off archiving, which is (I believe) when our problems happen. I don't know what you have done in terms of configuring things. I assume that you've done obvious things like making sure that writes are spread evenly across multiple disks and all that. No idea. I'm approaching this from a relatively uninformed "I can't believe they can't fix this" standpoint. :-) If all else has been tried, you either need to use another database, or you need to scale up in some way. There's a direct path thing that's being worked on, but holy bovine it's complicated and fraught with danger. A large company that I am familiar with has a similar problem on a much larger scale. Their answer is to divide their data across many independent databases so that each one stays at acceptable load. There are some problems with the exact approach that they take, but the principle remains good. Won't work here, for various reasons. I have not tried Oracle's clustering solution, which they like to call a "database grid", but they claim that it can be for any combination that you want of high availability or performance. Setting it up for performance could make your problem go away in a scaleable way. (With, of course, scaleable licensing costs...) 10g would be nice for several reasons (I know some people in ops are keen on it) but we're naturally conservative on changes that drastically affect how things work. Thanks. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #237,835 by ben_tilly 12/12/05 9:09:35 PM Reply	If the data isn't critical, then put it in MySQL. More important, threatening to has a remarkable motivational effect on Oracle DBAs. :-) Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #237,856 by admin 12/13/05 12:20:18 AM Reply	Ain't gonna happen... We're HEAVILY invested in Oracle. HEAVILY. As in, most of the web pages are generated with PL/SQL... Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #237,860 by drewk 12/13/05 12:58:57 AM Reply	You know that phonograph label? The one with the dog with his head cocked to the side? That's what I did when I read that last line. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #237,861 by folkert 12/13/05 1:20:03 AM 12/13/05 1:20:23 AM Reply	The RCA label? "The Masters Voice" -- [link\|mailto:greg@gregfolkert.net\|greg], [link\|http://www.iwethey.org/ed_curry\|REMEMBER ED CURRY!] @ *i_wethey* Freedom is not FREE. Yeah, but 10s of Trillions of US Dollars? SELECT * FROM scog WHERE ethics > 0; 0 rows returned. Edited by folkert Dec. 13, 2005, 01:20:23 AM EST Expand All History
Post #237,862 by pwhysall 12/13/05 1:36:45 AM Reply	You know what that dog's thinking of, don't you? Database permissions. Peter [link\|http://www.no2id.net/\|Don't Let The Terrorists Win] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #237,864 by drewk 12/13/05 2:04:35 AM Reply	I fixed my permissions problem I got it put on the sysadmin's TODO list. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #237,868 by admin 12/13/05 8:22:57 AM Reply	I'm not too surprised [link\|http://www.unix.org.ua/orelly/oracle/webapp/ch07_01.htm\|http://www.unix.org....ebapp/ch07_01.htm] It's difficult to find an actual "this is what it is and what it does" page, especially from Oracle, without a lot of digging. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #237,891 by ben_tilly 12/13/05 1:03:54 PM Reply	I had to use that approach once, briefly Even though I was a pretty raw programmer at the time, it didn't take me long to figure out that it was easier to write valid HTML then autogenerate PL/SQL to write that than it was to write it directly in PL/SQL. OK, that was a long time ago (Oracle 7), but I remember being distinctly unimpressed by the environment. I also remember thinking that it was inherently going to be a scalability problem, putting everything into the database. If you offload at least something to webservers, then you can easily increase the number of webservers for that component, and hit database bottlenecks later. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #237,931 by admin 12/13/05 5:02:44 PM Reply	As it turns out... The query performance behind the pages completely overshadows any hit from running HTML-producing PL/SQL. By orders of magnitude. Moving the page generation to the web servers, while a good idea for other reasons, just means that your page generation can run quickly but the queries are still just as slow, so there's no speed increase. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #237,944 by ben_tilly 12/13/05 6:23:29 PM Reply	I see your point but... performance on individual pages is very different than overall system throughput. In fact I would expect the usual webserver approach makes individual pages slower to generate (greater latency between webserver and database). What I was saying, though, is that I'd expect the overall system to be able to generate more pages/second. My two major areas of concern would be overall I/O throughput (HTML tends to be more data than query results) and CPU bottlenecks (Oracle loves to waste lots of CPU on spinlocks, generating very high loads). However this is all theoretical. I've never seen two equivalent systems created and benchmarked against each other. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #237,818 by broomberg 12/12/05 8:05:35 PM Reply	Need to have stats of what is happening. Give me disk layout, types of connections, etc. Run an "iostat -Mnx 5". Highlight the time frame of the log roll. Are you archiving? Are you using Veritas? Volume manager? Database edition? What type of file systems? What are your mount options?
Post #237,821 by admin 12/12/05 8:26:40 PM 12/12/05 8:36:10 PM Reply	That will take me a bit. Give me disk layout, types of connections, etc. Are there commands to run to find this out? Run an "iostat -Mnx 5". Highlight the time frame of the log roll. I'll do this tomorrow during more activity. Are you archiving? Are you using Veritas? Volume manager? What type of file systems? Are there easy ways to find this out other than asking? Database edition? 9i What are your mount options? read/write/setuid/intr/largefiles/logging/xattr/onerror=lock/dev=154006e Thanks. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..." Edited by admin Dec. 12, 2005, 08:36:10 PM EST Expand All History
Post #237,841 by broomberg 12/12/05 9:25:38 PM Reply	Re: That will take me a bit. df -a This will show what disk devices are mounted on what directories. We need to figure out where your tablespace data files are, and where your logs are, and if you are archiving, where your archivelog destination is. If you have many logs, you might have them placed on different file systems, which in turn might end up round robining the high IO points. The key issue is to identify points of contention. You might might be able to tell by querying some data dictionary tables. I can't give you the commands since I don't even have a login to an active Oracle 9i instance to check. You DBA should be able to just tell you. You should then validate by looking in the dirs they tell yoy about. Are you duplexing the logs? Again, a dba question. I might be able to answer some of the other questions based on the output of the df command. When I say database edition, I mean are you using the Veritas Oracle Accelerator, which is also known as Veritas Volume Manager Database Edition. Sorry for not being more specific.
Post #237,873 by admin 12/13/05 10:45:42 AM Reply	Sent you an email. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #237,875 by broomberg 12/13/05 10:52:17 AM Reply	Email had no DB info, was address stuff only
Post #237,877 by admin 12/13/05 11:47:40 AM Reply	Was just making sure before I sent it; sent. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."

Welcome to IWETHEY!