IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 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]
New 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..."
New 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.
New 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..."
New 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..."
New 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]
New Whoops, my mistake.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
     Re: Login checks and declarative processing - (admin) - (130)
         Re Re: Login checks and declarative processing - (tablizer) - (129)
             Declarative languages can easily be turing complete - (ChrisR) - (9)
                 I am assuming he means XML-like -NT - (tablizer) - (8)
                     There are a multitude of XML based languages.... - (ChrisR) - (7)
                         If your config files are TC, then why have 2 langs? -NT - (tablizer) - (5)
                             Do you really have to ask? - (ChrisR) - (2)
                                 TC != Easy -NT - (tablizer) - (1)
                                     Exactly. - (ChrisR)
                             One's a hammer, the other is a screwdriver. -NT - (admin)
                             As an aside, that question has popped up in another context - (ChrisR)
                         BTW, Turing machines don't require much. - (ChrisR)
             Re: Login checks and declarative processing - (admin) - (118)
                 Login checks and declarative processing - (tablizer) - (117)
                     Re: Login checks and declarative processing - (admin) - (116)
                         Polymorphism - (tablizer) - (115)
                             Raison d'etre d'composition - (FuManChu) - (3)
                                 Epicycles - (tablizer) - (2)
                                     Re: Epicycles - (admin)
                                     Once more with feeling - (FuManChu)
                             So the best organization of code is... - (ChrisR) - (8)
                                 The funny thing is... - (admin) - (1)
                                     Noticed that too - (drewk)
                                 That is NOT what I said - (tablizer) - (5)
                                     Re: That is NOT what I said - (admin)
                                     Poor dichotomy on which to base your opinion of OO - (FuManChu) - (3)
                                         re: Poor dichotomy on which to base your opinion of OO - (tablizer) - (2)
                                             re: Poor dichotomy on which to base your opinion of OO - (admin) - (1)
                                                 How Lisp of you. Just add some TOP now. -NT - (tablizer)
                             Who says it's hard-coupled to a single factor? - (admin) - (101)
                                 so you say - (tablizer) - (100)
                                     Sounds like another... - (admin) - (99)
                                         Text is linear - (tablizer) - (98)
                                             Re: Text is linear - (admin) - (97)
                                                 Delt-A-Matic - (tablizer) - (96)
                                                     Re: Delt-A-Matic - (admin) - (95)
                                                         problem is treed-files, not procedural - (tablizer) - (94)
                                                             Re: problem is treed-files, not procedural - (admin) - (21)
                                                                 Re: problem is treed-files, not procedural - (tablizer) - (20)
                                                                     Re: problem is treed-files, not procedural - (admin) - (19)
                                                                         Re: problem is treed-files, not procedural - (tablizer) - (18)
                                                                             Re: problem is treed-files, not procedural - (admin) - (17)
                                                                                 misunderstanding - (tablizer) - (16)
                                                                                     Re: misunderstanding - (admin) - (15)
                                                                                         Well... I pack up my development database and... - (ChrisR)
                                                                                         How do you prime it in the first place? - (folkert) - (2)
                                                                                             I prefer Kilz myself. -NT - (Another Scott) - (1)
                                                                                                 That works well... - (folkert)
                                                                                         The file system *is* a database - (tablizer) - (9)
                                                                                             Re: The file system *is* a database - (admin) - (8)
                                                                                                 over-the-phone brain surgery - (tablizer) - (7)
                                                                                                     Re: over-the-phone brain surgery - (admin) - (6)
                                                                                                         a single command does not like databases? - (tablizer) - (5)
                                                                                                             Re: a single command does not like databases? - (admin) - (3)
                                                                                                                 Re: single command - (tablizer) - (2)
                                                                                                                     Naw... you are thinking... - (folkert)
                                                                                                                     Re: single command - (admin)
                                                                                                             Database migrations (new thread) - (admin)
                                                                                         Nit: some code does belong in a database. (sp's) -NT - (mmoffitt)
                                                             Exactly. - (FuManChu) - (71)
                                                                 Amazing, isn't it? - (admin)
                                                                 OO takes credit for sunrises even - (tablizer) - (69)
                                                                     Scheme and the Lambda Calculus - (ChrisR)
                                                                     Re: OO takes credit for sunrises even - (admin) - (60)
                                                                         No way Jose -- Gotta go to DB anyhow - (tablizer) - (59)
                                                                             Er... - (admin) - (27)
                                                                                 "Use OO because OO is faster" is the best you can do? - (tablizer) - (26)
                                                                                     Serious question - (drewk) - (2)
                                                                                         Interrelated - (tablizer) - (1)
                                                                                             Re: Interrelated - (admin)
                                                                                     Re: "Use OO because OO is faster" is the best you can do? - (admin) - (22)
                                                                                         Table != Disk - (tablizer) - (21)
                                                                                             Re: OR mappers slowing things down - (ChrisR) - (17)
                                                                                                 Re: OR mappers slowing things down - (tablizer) - (16)
                                                                                                     Re: OR mappers slowing things down - (ChrisR) - (15)
                                                                                                         Re: OR mappers slowing things down - (tablizer) - (14)
                                                                                                             Procedural abstraction - (ChrisR) - (2)
                                                                                                                 My abstraction can beat up your abstraction - (tablizer) - (1)
                                                                                                                     re: Relational is more than storage (new thread) - (ChrisR)
                                                                                                             It wasn't a caching issue - (ben_tilly) - (10)
                                                                                                                 No file IO - (admin) - (9)
                                                                                                                     I understand how it worked - (ben_tilly) - (8)
                                                                                                                         Index lookup in code, or table index? - (admin) - (7)
                                                                                                                             That requirement would shock me - (ben_tilly) - (6)
                                                                                                                                 Re: That requirement would shock me - (admin) - (5)
                                                                                                                                     Question about Oracle tables - (ChrisR) - (2)
                                                                                                                                         You can pin them in memory. - (admin) - (1)
                                                                                                                                             Oracle tables pinned in memory. - (admin)
                                                                                                                                     You did give the impression that CPU was the issue - (ben_tilly) - (1)
                                                                                                                                         Whoops, my mistake. -NT - (admin)
                                                                                             Re: Table != Disk - (admin) - (2)
                                                                                                 This is an area in which I'm proud of Dejavu - (FuManChu) - (1)
                                                                                                     Nifty. - (admin)
                                                                             Another little anecdote for you: - (admin) - (7)
                                                                                 OT: Scott, can we please do something about long lines? - (jb4) - (6)
                                                                                     Dang, this was SUPPOSED to go into the Suggestions forum - (jb4) - (3)
                                                                                         The other way, besides cut and paste, is to re-type it :-) -NT - (ChrisR) - (2)
                                                                                             There isn't enough time in the world... - (jb4) - (1)
                                                                                                 HTH: As with Perl, There's More Than One Way To Do It -NT - (ChrisR)
                                                                                     Perhaps one might play with CSS clip and overflow...? -NT - (FuManChu)
                                                                                     Not that I'm aware of. - (admin)
                                                                             Retrieving the customer record anyway: - (admin) - (22)
                                                                                 question - (tablizer) - (21)
                                                                                     Re: question - (admin) - (20)
                                                                                         The other method is to have one column.... - (ChrisR) - (2)
                                                                                             Re: The other method is to have one column.... - (admin) - (1)
                                                                                                 That's the spirit!!! -NT - (ChrisR)
                                                                                         I would have to look at the nature of the data -NT - (tablizer) - (16)
                                                                                             Are you kidding?? - (admin) - (15)
                                                                                                 suggestion 3 - (tablizer) - (14)
                                                                                                     Re: suggestion 3 - (admin) - (13)
                                                                                                         bottleneck black box - (tablizer) - (12)
                                                                                                             Storing Dictionary as Session variables in ASP - (ChrisR) - (8)
                                                                                                                 It has gotta go *somewhere* - (tablizer) - (7)
                                                                                                                     ASP is OO - (ChrisR) - (6)
                                                                                                                         re: ASP is OO - (tablizer) - (5)
                                                                                                                             ASP = COM - (ChrisR) - (4)
                                                                                                                                 re: ASP = COM - (tablizer) - (3)
                                                                                                                                     ChiliSoft ASP - (ChrisR) - (2)
                                                                                                                                         Interesting terminology - (FuManChu) - (1)
                                                                                                                                             You're probably correct convention-wise - (ChrisR)
                                                                                                             Re: bottleneck black box - (admin) - (2)
                                                                                                                 how they relate - (tablizer) - (1)
                                                                                                                     Re: how they relate - (admin)
                                                                     I don't understand why you mention "eval" -NT - (FuManChu) - (6)
                                                                         re: I don't understand why you mention "eval" - (tablizer) - (5)
                                                                             I get it now. - (FuManChu) - (4)
                                                                                 OO is just a (bad) reinvention of 60's databases with - (tablizer) - (3)
                                                                                     No. You are a proponent of OO programming. - (folkert) - (2)
                                                                                         The question that launched a thousand arguments - (tablizer) - (1)
                                                                                             That explains a lot - (FuManChu)

This isn't AOL, despite the best efforts of a nameless few.
268 ms