It wasn't a caching issue

Post #157,302 by ben_tilly 5/27/04 5:44:43 PM Reply	It wasn't a caching issue It was CPU spinning due to searching through the table for data. Though I admit to being puzzled as to why IF/THEN written in PL/SQL would beat a hash lookup from adding the right index to a table. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #157,311 by admin 5/27/04 6:36:08 PM Reply	No file IO In memory always beats file IO. The algorithm produced a hard-coded binary search in IF/THENs. :-) Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,314 by ben_tilly 5/27/04 6:42:57 PM Reply	I understand how it worked It just surprises me that binary search in PL/SQL beats an index lookup. After all index lookups can be implemented many ways, including binary search or a hash lookup. Personally with 2000 things I'd expect a properly coded hash lookup to beat a binary search. Oh well. Optimization often has little surprises like that for obscure implementation reasons. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #157,317 by admin 5/27/04 6:51:28 PM Reply	Index lookup in code, or table index? Table index requires file IO. If you're talking about using string keyed hashes in the programming language, keep in mind that this is v8 PL/SQL. There ain't no sich beastie. Integer index only. 9i has associative arrays, but there are still some deficiencies to them. Even if we had decent hashes, since the connection state is blown away between pages there's no place to keep the hash without it being recreated every time. Persistence in this situation requires that the data be represented by code. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,323 by ben_tilly 5/27/04 7:14:44 PM Reply	That requirement would shock me Table index requires file IO. Why? Oracle should be smart enough to cache a frequently used table in RAM. If you change it, then you have to hit disk. But if you don't, then there is no reason in the world for them to be so stupid, and I don't think that they are stupid. Furthermore your problem was that the query was spinning lots of CPU. Slowness from hitting I/O won't show up on your CPU usage statistics. If I was going to guess the cause of the problem, I'm going to bet on low-level implementation details. An index lookup is fast. But before Oracle gets there the execution path has to include getting a latch (that's a kind of lock), look for the cached query plan for the current query, find it there (let's ignore the parse route since most of the time the common query has a parse in cache), release the latch, interpret that plan, realize that the plan says to do an index lookup, locate the appropriate index, realize that it is in cache, do the index lookup, look for the appropriate row, find it in cache, read it and return it. I've probably missed something that it does. You'll note that several of these steps involve string comparisons that are going to take CPU time. That's the overhead which I think makes it possible to beat an index lookup using straight PL/SQL. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #157,332 by admin 5/27/04 10:10:43 PM Reply	Re: That requirement would shock me Why? Oracle should be smart enough to cache a frequently used table in RAM. And if they're all frequently used? :-) Furthermore your problem was that the query was spinning lots of CPU. If I gave that impression, it was erroneously. Performance was decreased, but that doesn't mean more CPU necessarily. Basically the question you ask the profiling tool is "how much time is being spent doing foo?" Whether that time is spent doing IO or spinning the CPU doesn't matter. It's still time spent. And if the time spent is 15% of the overall time spent across the system then it's a good candidate for optimization. I'll have to talk to the DBA on Tuesday to get the particulars. The developer who rewrote it was a little fuzzy on why it was so slow in the first place (this was two years ago). He just remembered that it had something to do with file IO, and pinning the table made no difference. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,334 by ChrisR 5/27/04 10:33:28 PM Reply	Question about Oracle tables Is it possible to define a memory based partition that holds selectable tables? Not that this is a question about the problem/solution you are talking about, but I've always thought that allowing the programmer to set up memory based tables for optimization purposes might be a useful optimization technique for certain lookup tables that you know are used frequently.
Post #157,341 by admin 5/27/04 11:06:22 PM Reply	You can pin them in memory. Assuming you have enough memory. As I said, I'm going to have to take it up with the DBA as to why that wasn't sufficient. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #158,339 by admin 6/3/04 10:56:28 PM Reply	Oracle tables pinned in memory. As it turns out, I was wrong all around, and right for the wrong reasons. The actual problem was lock contention. The table was pinned, but Oracle places micro locks for reads, effectively serializing reads on single blocks. The CPU was churned by grabbing and releasing locks on the parm data repeatedly. Since we make extensive use of that data (as I indicated, this is Bryce's dream architecture), the lock management became a significant consumer of CPU. Why Oracle needed to lock read-only data I neglected to find out. This is also a significantly dumbed-down version of the explanation I was given. :-P Also I was misremembering the %cpu being used. The actual figure was MUCH higher. The DBA estimates that we would have been maxed out at 25% of our current capacity had the change not been made. An interesting comment he made: Oracle considers the heavy use of a single parm table such as we were doing to be an application design flaw. :-) Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,338 by ben_tilly 5/27/04 11:04:10 PM Reply	You did give the impression that CPU was the issue In your description at [link\|http://z.iwethey.org/forums/render/content/show?contentid=157019\|http://z.iwethey.org...?contentid=157019] it said 15% of CPU time was spent on this query. I've been working from the assumption that this was the problem that needed solving. If that is wrong, then reasonable theories to explain what didn't happen are, of course, superfluous. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #157,340 by admin 5/27/04 11:05:24 PM Reply	Whoops, my mistake. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."

Welcome to IWETHEY!