I talked to a real DBA

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)

Welcome to IWETHEY!