DBAzine Oracle Space Management handbook - Beware !!
In the interests of trying to highlight Oracle related material of questionable merit, I would like to draw your attention to (yet another) www.DBAzine.com document. This time, it's the recently available free download Oracle Space Management Handbook that has caught my attention. I have as usual contacted the Series Editor, the one and only Donald K. Burleson with my concerns and I have as usual received no reply. Within the pages of the above mentioned Handbook, you will find the following samples of expert advice: a.. Separate indexes from their tables to improve performance via reduced disk contention (with classic example) a.. Set pctincrease to 1 to coalesce fragmented tablespaces a.. Oracle guarantees that the undo entries will not be overwritten within the undo_retention period a.. A physical I/O is 10s of thousands of times slower than a memory I/O a.. Actually, a physical I/O is 14,000 times slower than a memory I/O a.. More that 1024 extents leads to performance problems with LMTs and look out for more that 5 extents generally a.. Rebuild tables that have the above numbers of extents to reduce performance problems a.. After rebuiulding a table, coalesce the tablespace a.. After a table move, a fast index rebuild can be used rather than a slow drop/re-create (as the invalid index is used during the rebuild) a.. Deleted space within an index is evidently not reused (with clear example) a.. Index access is so fast because deleted space is not reused a.. Any index with more than 4 extents should be rebuilt (even if using LMTs) a.. Ideally, indexes should fit into one extent a.. As deleted space is never reused, indexes must periodically be rebuilt a.. Place indexes into separate tablespaces with a uniform size that ensures no index has more than 4 extents a.. Index Row length is calculated as (sum of data length) + 1 (with the 10 byte rowid being of no consequence) a.. If the number of leaf blocks + branch blocks is less than the number of blocks in dba_segments, rebuild the index a.. Don't just rebuild indexes the once, but rebuild them twice, once in another tablespace so you can defragment the original tablespace and then re-create them again back in the original tablespace a.. Multiple block sizes should be used to improve performance, unconditionally a.. The System tablespace can (and should) have a block size different from the DB_BLOCK_SIZE a.. The size of the Default Pool is calculated as DB_CACHE_SIZE - (DB_RECYCLE_CACHE + DB_KEEP_CACHE) a.. . As remarkable as it might sound, the above recommendations are all found within the handbook, I kid you not !! Many of the articles appear to be years old (5 years + ?) with several of the chapters referring to Locally Managed Tablespaces as being relatively new. The issue of course is that all the above (and more) is utter tripe but the more unfortunate issue is that there is some good stuff in there, it's just that it's been buried among the rubbish. And as the handbook is obviously aimed at the newer Oracle audience (due to it's modest level of technical details), the truth and the myths become hopelessly mixed. The end result is a new bunch of Oracle folk who believe that more than 1 or 4 or 5 or 1024 extents is bad, believe indexes need to be rebuilt all the time, believe the Default pool is 1/2 it's actual size and are confused why the System TS block size can't differ from the db_block_size. With handbooks such as these still being developed, it's no wonder some of these myths never die as newbies simply don't have a fine enough sieve and fast water flow to separate the crap from the gold. Good grief !! All I can do is highlight these things in the hope it might do some good and urge some of these so-called experts to lift their game and produce materials that actually helps to advance the level of understanding in the Oracle community, rather than complicate, confuse and confound. Hence my definition of a real expert ... If experts can't correctly calculate the size of the Default Pool, perhaps they should spend more time reading than writing !! Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: ora-1555 under automatic undo management (resend ?)
I'm having an ora-1555 under Oracle9 database and not sure what I can do to get rid of it. I had some recollecions from Oracle8 days , but the things like adding a new rollback segment or shrinking the segments I don't think are applicable under the auto undo management. Besides separating the long queries from batch programs, is ther anything that I can do here? Hi Gene, Increase UNDO_RETENTION (which determines how long Oracle will attempt to keep your undo before being overwritten) and/or increase the size of your undo tablespace (to ensure Oracle will succeed in meeting your undo_retention target). Check out V%UNDOSTAT to see how it's going. Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Should we stop analyzing?
Don, Comments inline... Yes! IME, there ARE still problems in the CBO, especially with complex subqueries. I have more than a dozen systems where management insists on staying with the RBO! [TG]: With all due respect, what does management know about this stuff anyway? They do not work with it, they do not research it, and they do not understand the issues if technical people do not research, understand, and inform them. Management makes decisions based on information provided. That is their job. Bad information, bad decisions. Hi Tim, Went to a management meeting the other day to discuss the statuses of a number of projects. At the meeting I asked the assembled managers Hey guys, what are your opinions on what type of Oracle optimizer we should use ? They kinda looked at me with a glazed look in their eyes and one of them was brave enough to ask What's an optimizer ?. OK, it's not entirely true but I were (stupid enough) to ask the question, I'm sure it's the reaction I would receive, if not a lot worse. Can't say I've (yet) worked in an organisation where management decides how to tune the databases !! Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: ORACLE-L Digest -- Volume 2004, Number 008 (Out of Office
Let's hope he's not on long service leave :) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 8:44 PM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: table reorganizations
I'm surprised at these responses. I'm asking what sql statement most people use to identify tables that need reorganization because of holes. We had an Oracle consultant here and he uses Select table_name, blocks-((num_rows*avg_row_len/block_size)*(1+(pct_free/100))) blkdiff From dba_tables Where blkdiff 100; To determine reorganization need. Hi Jolene You already received a number of replies why there are issues with using a general formula as above. IF a table is commonly accessed via a FTS AND, IF sufficient deletes without subsequent re-inserts (permanent table shrinkage, ouch, it's a male thing ;) or IF you've set a shocking PCTUSED which prevents inserts reclaiming deleted space, or IF you've set a shocking PCTFREE with no subsequent row size increase (etc) AND FTS access performance causes notable performance issues, you might have a case for a table re-org. The above conditions are not particularly common (perhaps a table containing future bookings for sleepovers at Michael Jackson's place ? ;) but if they do, consider the clustering factor of your most significant index access while you're at it, assuming there is one. The point I'll like to make are a couple of issues with your formula above. Firstly, it doesn't consider general block overhead details which means for largish tables with a sum of 100 block or more of overhead, the (rather expensive) re-org would achieve nothing. Secondly, it doesn't consider blocks above the HWM which could quite easily exceed the 100 mark depending on extent size. Again the re-org would result in a somewhat disappointing outcome. The formula above will potentially call for the re-org of *all* your larger tables for absolutely no benefit. Glad you asked the question Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: another OCP question -- help me guys
1. Assume boson is right and recheck your answer. I disagree. Based on the questions and answers I've seen here, I would recommend that one assumes Boson is *wrong*, scratch one's head in mild confusion, utter a few expletives under one's breath and move on. Cheers ;) Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: table reorganizations
Title: Message Hi Thomas, Never say never (oh bugger, I've just gone and done it myself). A large table accessed via a FTS for various important reporting requirementshas permanently shrunk in size from 10G to 100M (say list of Informix customers ;) Business requirements have changed and you need to add some columnsto a table resulting in muchorow migration. You were told (incorrectly) that rows would grow significantly after loading (honestly) but now the 80 pctfree value you've set is causing problems for other really important reports. There are of courseother cases butyou get my point ;) Cheers Richard - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Thursday, January 08, 2004 6:34 AM Subject: RE: table reorganizations Jolene, Tables should never *need* to be reorganized. This is an old falacy. If you know how big a table is going to grow, say in a year, then place it in a Locally Managed tablespace with extent sizes to hold enough data for one year (say 1M). You should never have to reorganize a table. Tom Mercadante Oracle Certified Professional -Original Message-From: Shrake, Jolene [mailto:[EMAIL PROTECTED]Sent: Wednesday, January 07, 2004 2:39 PMTo: Multiple recipients of list ORACLE-LSubject: table reorganizations What SQL statement do you use to identify tables that need reorganization? How do you identify tables that are used in full table scans? How often do you run this query? Thanks, Jolene
Re: Re[1]: OCP question from Boson practice tests
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 1:59 AM My question, Richard, is can a person pass the exam just by studying what is correct? Or is it necessary to work harder to acquire some veneer of false knowledge specifically in order to pass the exam? Hi Cary Yes you can. That's why you're allowed to get 1/3 of the questions wrong and still pass the exam !! In defence of the questions, it's actually not that easy to write a multiple choice question that is both challenging AND non-ambiguous. The more complex an issue, the less likely you're going to successfully bind up a comprehensive and accurate answer in a single statement. That's why so many of the questions are so trivial, that's why the exams are so trivial and that's why the multiple choice method is so awful in determining an OCP. Attempts of questions to go beyond syntax, parameter settings, etc. often cause confusion and debate because the answer to the more complex issue isn't as simplistic as the question writers hoped it to be. Interestingly, during beta testing, questions get selected based on how many people actually get the same (hopefully) correct answer. If most people agree with an answer, then the question is less likely to be ambiguous. So by intentional design, the OCP exams are actually aimed and geared towards the lowest common denominator. So in short Cary, you have a fair to average chance of passing the exams :) Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Re[1]: OCP question from Boson practice tests
Hi Prem Comments in line. Hi Richard , Many a thanx for both of your replies . All my worry is : do such questions appear in the real exams also ? Although there are certainly some dodgy questions and correspondingly suz answers, I think you'll find the majority of the OCP exam will have 'relatively' clear answers. Certainly enough to make a failure be a deservable event :) My biggest wish would be for there to be 3 additional selections to most questions: F) what does it matter so who cares G) you would check the syntax, correct parameter name, etc. in the manuals H) it depends because the above 3 answers are generally (often collectively) the *correct* answer to the questions. And your reply has increased my self-confidence. particularly the line : RFTrust what you *see*, not what you *read*. /RF RFYou actually proven this yourself and yet you still have doubts? /RF yes Richard : ( hope i will not repeat this as time goes and my experience grows. i.e., i will be more confident with my answers . Experience only comes with time. And in time, your confidence will grow. Trust me, you'll get there. okay , coming back to the sizing of temp tablespace question . if suppose , such a question appears in the exam too ( my bad luck ) , what will be your two best answers ? Honestly, with this question, I would keep a mental picture of the thing, pick any 2 answers (users and sort_area_size would be my pick but as I said, they're simply not correct), move on to the next question and report back to Oracle what the hell they meant by the bloody thing. Cheers (and good luck) Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: OCP Question (Perf Tuning)
We'll thats exactly what I'm doing right now, studying Oracle University instructor guides to temporarily start thinking like OU myself again - I'll be instructing an OCP Review course next week, meant for people who want to pass OCP. And in order to not distract the students, I won't even mention the real life situation too much, except in really misleading cases... Hi Tanel I have a Dr. friend of mine who teaches heart surgeons how to pass their medical exams. Basically he simply teaches them to rip out the offending organ (generally found towards the left hand side of the upper chest), give it a bit of a squeeze, shake out any crap that might be inside, measure the Beats outside Chest Heart Ratio (BCHR), stick it back in, hope it's done some good and that the patient at least survives until they're 10 miles from the hospital. He tells the students that it's important to do the above steps in the correct order as it's in the medical exam. After the students have scribbled everything down, he then leans forward and quietly whispers to them that in the real world, heart surgeons actual first check whether or not it's actually necessary to cut out the heart *beforehand*. He then gives them a little wink and a nod, the students usually reply with an hh and the class moves on to discuss how to remove blood stains from their white surgical outfits. IMHO it's all a little scary and a touch surreal and yet it all sounds strangely familiar ... Cheers ;) Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: another OCP question -- help me guys
Hi Prem, Firstly, thank you for bringing back fond memories of when I used to teach this stuff for Oracle. If we ever meet one day, I'll show you my highlighter penned copy of the notes showing the various errors and inaccuracies (except the Performance Tuning course where I highlighted the correct bits ;) Just a personal opinion (don't get me started on OCP) but if I were going for a certification classifying me as a Professional, I would like to display a greater air of confidence in that I know what all this stuff actually means. I mean once you get the certificate, you'll actually be expected to know how to tune a temp tablespace, drop a tablespace, etc, right ... You're heading in the right direction by questioning these questions but knowing the answers to questions is not the same as being able to solve real-life problems which should be the mandatory skill of any so-called certified professional. I guess I'm suggesting that before you pin on the certification badge, you should have the skills to determine the correct answers to these questions yourself. Now you've gone and got me started on OCP, but hopefully you know what I mean. Question 1) Answer B - Statement will fail. You actually proven this yourself and yet you still have doubts? Trust what you *see*, not what you *read*. The cascade constraints clause if used must go at the end of the statement. Question 2) Answer A and C. Again, a simple query or describe of these views would do the trick. I think we can safely say that Boson (whatever he/she/it is) is not a very reliable source ... Good luck with your OCP Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 9:24 PM Hi list , sorry to pester you with questions regarding boson OCP questions . i have scheduled for #1Z0-031 exam and so desperately need help from this list . please bear with me for while . look at the 2 questions below . - QUESTION #1 what happens when you issue the command below . drop tablespace testtbs including contents cascade constraints and datafiles; A.the tablespace will be dropped , constraints will be droppped and the datafiles will be taken out of the o/s. B. statement will fail C.you must drop constraints before issuing this command. this is what happens when i try on 9.2.0.4 : ERROR at line 1: ORA-02173: invalid option for DROP TABLESPACE but boson's choice is A. i wonder how ??? - QUESTION #2 you need to determine how much space has been allocated for a table. which view would give you this information ? A. dba_extents B. dba_ts_quotas C. dba_segments my choice is C . but boson's choice is A . it says other views cannot give the required details . a metalink doc says that dba_segments.blocks gives the total number of blocks allocated to the table. what will be the choice that you would go for ? - so now i have the question whether boson is reliable ? how many in this list have used it ? or am i missing something : (( Regards, Prem. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Richard Foote 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: OCP question from Boson practice tests
Hi again Prem, OK, I've changed my opinion of dear Boson. At first I simply thought they had no idea, now I suspect it's all a big joke designed to make DBAs have a bit of a giggle on warm summer nights ... Sizing the temp tablespace by the formula you've given is really quite witty. Perhaps a little on the silly side but I love Monty Python so I liked it. The correct answer is actually none of the four listed (so I'm really struggling to find two of the buggers). A. Users: Not really. I could show you a DW database with only a handful of users that requires a much larger temp tablespace than an OLTP database with 1000s of users. It not the number of users but what the users *do* that's important. B. Sort Area Size. Not really. Although it can influence whether a sort is performed on temp or not, it's the size of the *sort*, not the size of the S_A_S that's important. And not just the size of one sort, but the size of the *max concurrent* sort activity. I guess a badly set S_A_S could result in needing a larger temp tablespace but I doubt that's the point of the question. And then there's hash joins, etc, ooops, the question has kinda forgotten about non sort activity in temp C. tablespace management. Not really although I guess those bitmaps do take up some space ... D. db_block_size. What the #@* ??. Thinking about all this a bit more, if Boson's practice questions puts people off doing OCPs, it might not be such a bad thing after all ... Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:44 PM Hi List, this is the question . You are calculating the proper size for a temporary tablespace . Which of the following are two most important factors to consider ? A. users B. sort_area_size C. type of tablespace management D. db_block_size my choice is A B . but boson's choice is B D . the explanation given by boson is : when sizing a temporary tablespace , the formula is db_block_size * sort_area_size . is it so ? do we need not take no. of users into account ? kindly explain me . Regards, Prem. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Richard Foote 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: oaktable people
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 9:44 AM I resemble that remark! Aussies are ALWAYS great communicators, just a bit direct for some people sometime. :) Hi Pete, Me direct ??? Never !! Richard ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Listen to Cary in Charlotte on Thursday
And if enough attend, Cary has promised to related the little tale of a certain rat called Rupert. Cary, dare ya !! Richard ;) - Original Message - From: Murali Vallath To: Multiple recipients of list ORACLE-L Sent: Wednesday, January 07, 2004 10:59 PM Subject: Listen to Cary in Charlotte on Thursday If you leave in and around the Charlotte area or in a reachable distance and have not heard throughthe user group'sregular e-mail invitations here is the opportunity. Cary presents for the Charlotte Oracle Users Group on Thursday January 8th - for more details visit www.cltoug.org Murali Vallath President, Charlotte Oracle Users Group. Do you Yahoo!?Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
Re: Re: Little competition
Hi Jonathan, SQL create tablespace bowie_test 2 datafile 'c:/bowie/bowie_test01.dbf' size 100m 3 extent management local uniform size 1m 4 segment space management auto; Tablespace created. SQL create table bowie_assm (ziggy number) 2 tablespace bowie_test 3 storage (initial 1m next 2m pctincrease 100 minextents 3); Table created. SQL select owner, segment_name, blocks from dba_extents 2 where segment_name = 'BOWIE_ASSM'; OWNER SEGMENT_NAME BLOCKS -- -- BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 7 rows selected. 3 minextents = 1 initial + 2 next + (2 next + (2 * 1.0 pctincrease) = 7 * 1M extents It's actually quite a common misconception that NEXT, PCTINCREASE and MINEXTENTS are ignored for locally managed tablespaces when in fact they're used to determine the initial size of the object and hence the number of extents initially allocated. This was all a bit of fun but I think it did prove my little (mischievous) point. That it's really quite easy to base ones belief and certainty on a fact that turns out to be totally false because the basis on why you believe something also turns out to be false. On the surface it appeared to be quite a reasonable conclusion, that pctfree is not permitted with ASSM objects because the evidence strongly supported such a claim. Unfortunately the evidence was somewhat erroneous in that it stupidly relied on incorrect syntax and so an incorrect conclusion resulted. This incorrect conclusion can then result in inappropriate behaviour and curses from DBAs as they experience all these unavoidable migrated rows. Before you know it, other Oracle myth is born ... Of course everyone makes mistakes but to publish them does come with it's own set of responsibilities. I can't stress enough that one be careful of what you read and be careful of who you read. The truth IS out there ;) Cheers Richard quote This could be a serious issue for the Oracle professional unless they remember that locally-managed tablespaces with automatic space management ignore any specified values for NEXT and FREELISTS. end quote There is another error here. For a bonus 10 points can anyone spot it ? Hint - try the following in a tablespace which is locallally managed, with automatic space management, and either system managed or uniform sized extents of no more than 1 M. create table test2(n1 number) storage (initial 1M next 2M pctincrease 100 minextents 3); Regards Jonathan Lewis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Who are certified Oracle Masters?
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: Richard Foote 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: Who are certified Oracle Masters?
Hi Jeremiah, I find the mental image of the six of you holding up your shafts for a publicity shot profoundly disturbing... Cheers ;o) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 12, 2003 5:19 PM Apology accepted. We had to do a lot of hard work to get those superman-style crystal shafts. Among the things we had to do the get the shaft: - Stand on a piece of masking tape on the stage - Not make fun of the OCM program while we were getting the award - Not make fun of any of the other honorary OCM recipients during the presentation - Act imporant and smarter than everyone else - Hold up our shafts for a publicity photo -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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).
Little competition
Little competition for you all :) It's a two part question: What's wrong with the followingpiece of expert analysis? Which well know "Oracle Guru" published this (and continues to display it on his web-page) ? "Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance." SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) *ERROR at line 7:ORA-02143: invalid STORAGE optionHowever, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings" You've gotta love it !! Sorry no clues Cheers ;) Richard
Re: rebuilding indexes - sure to cause a ruckus
Thanks Raj, Unfortunately, in my rush to get the kids to school in time, I stuffed the formatting when my cut 'n' pasting got converted to plain text. Hope you found it all useful. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 2:49 AM Richard's explanation and example from c.d.o.s now has a permanent tinyurl link ... http://tinyurl.com/yflq if anyone is interested ... this might be better for bookmarks. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 08, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. [ rest snipped ] ** 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. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Richard Foote 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: leaf node 90-10 splits
Hi Tanel, I have no idea but if you currently have 9 entries in a leaf block and the 10th entry you're about insert causes this type of split, then 9 entries (the 90% currently in the existing leaf node) remain and the new entry (10%) goes into the new leaf node. A 90-10 (%) split. Possible with small blocks (say 2K) and large index entries (200ish bytes) when 2K blocks ruled the Oracle seas. Like I said I have no real idea but it's my theory and makes a good bed-time story. Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 12:24 AM Hi! I wonder why does statistic leaf node 90-10 splits imply that right-hand index leaf block is split as 90-10, not 100-0 as it really is. (tested on 9.2.0.4 W2k). Historical reasons? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Hi Yong, One thing I should have mentioned when I posted my epic is that it not only attempts to correct the numerous technical errors in the article but also attempts to answer the various questions the article raises but totally fails to address. What I find most astonishing about the article is that the author confesses at the conclusion he has no idea when and why an index rebuild is beneficial. And as the author doesn't know, then surely it must all be so difficult, a scientific-less phenomenon. If I can convince anyone who makes it through my email that this isn't rocket science, then it's been worth the bandwidth. BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 5:44 AM Thanks, Richard. I'll read your long message more carefully later. I like your statement that rebuilding an index or not is not rocket science. One needs to measure the performance before and after the rebuild and make a conclusion himself. Many times we discuss performance issues and get very technical and sophisticated, without showing experimental results! Having been a science researcher before, I'd like to emphasize that facts speak louder than theories. There may be 10,000 24x7 databases in the world that don't easily allow even testing an index rebuild. But there may be 100 times more production databases in the world that are not 24x7. The individual DBA needs to do his control study and conclude, using experts' opinions as reference. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Terse ? You haven't heard me terse until youhear me trying to get the kids to sleep at night. Don got it easy ;) - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 10, 2003 8:14 AM Subject: Re: rebuilding indexes - sure to cause a ruckus And in case you miss it in Richard's terse message, one of the big reasons that it is not 'rocket science' is that you can perform operations that modify the index(es), and perform block dumps of the index as you go. You can see exactly what Oracle is doing with the index. Jared Yong Huang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/09/2003 11:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: rebuilding indexes - sure to cause a ruckusThanks, Richard. I'll read your long message more carefully later. I like yourstatement that rebuilding an index or not is not rocket science. One needs tomeasure the performance before and after the rebuild and make a conclusionhimself. Many times we discuss performance issues and get very technical andsophisticated, without showing experimental results! Having been a scienceresearcher before, I'd like to emphasize that facts speak louder than theories.There may be 10,000 24x7 databases in the world that don't easily allow eventesting an index rebuild. But there may be 100 times more production databasesin the world that are not 24x7. The individual DBA needs to do his controlstudy and conclude, using experts' opinions as reference.Yong Huang__Do you Yahoo!?New Yahoo! Photos - easier uploading and sharing.http://photos.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Yong HuangINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: rebuilding indexes - sure to cause a ruckus
Hi KG, O, you've got me thinking here !! I'm not too sure that I've really questioned anyone's intelligence. I've always measured someone's intelligence by: 1. How quickly the can learn and absorb new information 2. How much they know and appreciate the work of David Bowie A quick check of the Oxford Dictionary describes the word intelligence as mental ability to learn and understand things (although interestingly, there's no mention of DB). I guess the issue I have is that if intelligent people are told and feed incorrect information (and Don's article has it's share of incorrect information) then fundamentally it's one's knowledge that I begin questioning. Unfortunately, I believe there are a lot of intelligent people in the Oracle community who have a questionable knowledge of Oracle (or aspects of Oracle) as a direct result of the poor quality of information that people absorb (be it books, training courses, web-articles, etc..). And undoubtedly many of these people that write substandard materials in turn have picked up flawed knowledge due to the quality of their readings, education and lack of proper research. As I mentioned Knowledge is the key that unlocks the door of doubt. If you have no doubts about something, it by definition becomes simple !! Unfortunately, if you're presented with the wrong information, you get access to the wrong key ;) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 10:09 PM Richard: I think that is the simple way of questioning other person's capacity. Remember this statment (borrowed from some one !!) If you are telling something is simple, you are questioning the other person's intelligence !!' KG -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Hi Tanel, I recommend a strong cup of coffee and a small nap 1/2 way through ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 7:44 AM Ouch, I gotta take a day off to read this one ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Hi Paul, The long one includes a discussion on why you should generally coalesce rather than rebuild indexes ;) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 8:44 AM somewhat on the longish side??? I'd hate to see a long article! ;-) --- Richard Foote [EMAIL PROTECTED] wrote: Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Hi Steve, I agree completely, but the question is would you rebuild it afterwards ? Cheers ;-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 9:29 AM I think it needs an index. ;-) -Original Message- Paul Baumgartel Sent: Monday, December 08, 2003 3:44 PM To: Multiple recipients of list ORACLE-L somewhat on the longish side??? I'd hate to see a long article! ;-) --- Richard Foote [EMAIL PROTECTED] wrote: Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
within the index structure by using the blocks statistic which as mentioned earlier includes all blocks above the HWM. An index that consists of just one block but has an initial extent of 1M would appear a possible candidate for a rebuild but it would be a bit of a pointless exercise. Blocks above the HWM do not effect the efficiency of the index, invalidating the purpose of what you're trying to represent here. Rather than blocks, I would suggest lf_blks + br_blks would be more appropriate and meaningful value that determines the number of blocks actually in the current index structure. b.. The column Computed Empty Block C10 is (you guess it) inaccurate and totally meaningless. You again insist on incorrectly multiplying del_lf_rows by the non-existent/non meaningful sum_key_len rather than just using del_lf_rows_len (which you're trying to compute anyway) and you're still dividing by the full blocksize rather than the more meaningful lf_blk_len (the usable block size). Your C10 therefore should look like: (del_lf_rows_len / lf_blk_len) Hopefully these comments will do some good not only to Don but to anyone trying to understand this whole issue. Regards Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 06, 2003 6:29 AM Tanel, I think you're saying a query almost always runs faster right after the index rebuild and there's no point in finding the criterion whether to rebuild an index. (What is 42?) Some time ago I posted a message somewhere else showing a case where rebuilding or coalescing an index may be benefitial. A data warehouse is found to have some data errors. Deletes and updates are done. Then the database goes to mostly read-only again, and will last for a month or quarter. Then shrinking frequently used B*Tree indexes is a good idea. Now I'd like to add one more criterion as a result of reading Jonathan Lewis' dbazine article and email with him (errors are mine): the index is full scanned, or if range scanned or unique scanned, the index selectivity has to be fairly low (but not too low for the index to be ignored by CBO). In a typical working environment, a data warehouse does have plenty of relatively quiet period. I worked on a monthly data load project at an insurance company. I remember we rebuilt a partitioned IOT (one partition at a time) and fast full index scan (certain partitions) did run faster. There're some errors in Don Burleson's dbazine article (e.g. pct_used in dba_indexes) and Mike Hordila's Oramag article (structurally unbalanced index). But one thing alluded to in there is important: study Oracle performance problems as scientific research. You said setting _wait_for_sync to false improves performance. That's a fact. We can only explain and analyze it but not deny it. Similarly, when Mike says queries run 10 to 50% faster after index rebuild, we can't deny unless we find his measurement is wrong. Wouldn't it be nice if Oracle researchers write articles with sections like Abstract - Experimental - Results - Discussion in that order? Yong Huang Tanel Poder wrote: There's no point of arguing about whether a query ran faster right after you rebuilt your index. Nor there is no point in finding some ultimate algorithm for finding the point of index rebuilding, we all know the answer - it's 42. Instead, a long stress test has to be done, e.g. running 10 millions of continous transactions and queries (simulating real life). Do one 10M without rebuilding indexes in the meantime, measure total execution time, IO amount, CPU usage, segment sizes etc. Then restore your database back to starting point and do the same test again with regular index rebuilds during the operations (online or taking users offline, depending on environment type). And then measure the same statistics, especially total execution time. Note, that statistics and time also for rebuilding indexes should be accounted in totals, because in real life they don't just disappear somewhere as in some simple-minded tests. Tanel. __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Richard
Re: (looong) PCTFREE, PCTUSED and ASSM
Hi Tanel, I think there's a logical reason why ASSM is designed to behave as you describe below. There's a bit of a balancing act going on here between nice, efficient performance of inserts vs. nice compact, efficient use of storage within a segment. The issue that Oracle has is that it has no real ideal of the average row sizes to be inserted in the future. An insert will only be attempted in a block where it *could* fit, however if it still fails, then we've wasted valuable resources and have to perform additional I/O to find an appropriate block.Yuck !! So should Oracle just treat it as bad luck and hope that an appropriate row will eventually turn up or risk the case where the same failure occurs again and again and again with the same block ? Who know which of the two might happen ? Oracle has chosen the side of caution. It's given the insert some kinda chance by selecting a block where it could fit in, but has decided to make the block full and ensure that similarly size rows don't fall for the same trap again. Personally, I think an improvement could be made to relegate such blocks that fail on insert to the next lowest free boundary so that the same size row won't be tempted by the same block. Also, a change in behaviour in how Oracle deals with a row which it knows must fit in a block vs. where it might fit in a block might be beneficial and how it classes a particular row size. For example, if a row is 45% of a block, should it consider the 25-50 free space blocks where it might fit or only 50% or more free space blocks where it will fit. What about rows that are 26% of a block in size, should it consider the 25-50% free space blocks will it will very likely fit or the 50% or more free space blocks where again it will definitely fit ? By relegating a block, you help narrow down similarly based previous failures as Oracle goes for the could fit solution. As it stands, making a block (potentially prematurely) full to prevent similar failures in the near future makes some kinda sense. Cheers Richard - Original Message - In ASSM, as you said, we have currently 6 different freeness statuses for table blocks, 0 = unformatted, 1 = logically full, 2 = 0-25% free, 3 = 25-50% free, 4 = 50%-75% free, 5= 75-100% free. When a datablock is filled to some level, it's corresponding bit vector in it's level 1 bitmap block is updated to reflect its freeness. When a block is 90% full for example, it's FS will be set to 0-25% free. However, if the block freeness drops below PCTFREE, it's freeness state will go to FULL regardless what's the percentage of free space in your block - it's PCTFREE that matters. You can easily trace it using events 10612 and 10613. At least in 9.2 it seems that also these blocks are marked FULL, which are rejected for an insert because the new row would have caused the free space drop below PCTFREE. Even if the existing block is 99% free and could accommodate several smaller rows in the future. This seemed a bit odd for me, because I thought that ASSM was supposed to eliminate the problem with inserting heavily varying sized rows, but few of my tests didn't show that good results. Maybe we'll see this improved in future versions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: PCTFREE, PCTUSED and ASSM
Hi Mladen, Don't desert me now, I thought we've come a long way !! Although, I'm not a listed Oracle guru, let me try and explain further ;) PCTFREE works in almost exactly the same way as it does for non-ASSM objects. PCTFREE determines how much of the block we want to reserve for subsequent update growth. An insert that would violate this figure would result in the block being taken off the freelist (and other block being considered). However, with ASSM we don't have freelists, instead the corresponding BMB is updated to now reflect the block as being full. So an insert that would violate pctfree causes the block to be considered full and a full block is longer considered for subsequent inserts. Note an advantage of ASSM is that blocks can be filled more effectively because we know the relative free space in a given block thanks to the BMBs. So for example, if we have a row that is greater than 25% of a block in length, those blocks with less than 25% free space are not considered for inserts as we know such inserts would not succeed (in these blocks). Only blocks with over 25% free space are therefore considered. This way, rows of a relatively large size are less likely to prematurely make a block unavailable for inserts as is possible with non-ASSM tables. Thus, tables with widely variable row lengths are more suited to ASSM and could pack data in more tightly. For a block to be made available again for inserts, we need the available free space to be reduced below one of the free space boundaries. So for example, if a PCTFREE of 10% is used, once we have more than 25% free space (ie. the free space is between 25% and 50%) the block becomes unfull and the corresponding BMB is updated. Therefore the effective PCTUSED in this case is 75%. If however the PCTFREE were set to 30%, then we need to get below the 50% free space boundary (ie. free space is between 50-75%) in order to be insertable again, an effective PCTUSED of 50%. So in summary, PCTFREE is effectively used in a similar manner with ASSM, but rather than being taken off a freelist, the block is marked as full by the corresponding BMB. The PCTFREE value also has an influence on what with effective PCTUSED is used within the block as well. Hope this clears things up a bit(map ;) Cheers Richard (Non Oracle Guru / All Round Nice Guy ) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 6:24 AM Int the note 247752.1 named Oracle9i Space Management Demystified oracle says the following: -- - AUTOMATIC SEGMENT SPACE MANAGEMENT ARCHITECTURE Oracle9i introduces a new way of managing free space within a segment using bitmaps. In the new scheme, a set of bits describes the space utilization for each block in a segment as well as whether it is formatted or not. Using these Paper # 32707 Oracle9i Database bits, for example, it is possible to represent the state of any given data block as follows: Free space in the block is less than 25% Free space in the block is greater than 25% and less than 50% Free Space in the block is greater than 50% but less than 75% Free space in the block is more than 75% The block is FULL i.e. there is no free space in the block The block is unformatted. It can be noted here that unlike freelists, where a block was either available for new rows or not, bitmaps provide a more granular and accurate idea of space utilization within blocks of segment. For LOBs and indexes, the bitmap just indicate Whether the block is formatted or not and, Whether the block is considered free or not. The Automatic Segment Space Management feature can only be used with Locally Managed Tablespaces. BITMAP BLOCKS The bitmaps are contained in a set of meta-data blocks known as bitmap blocks or BMBs. The number of BMBs in a segment depends on its size and the space consumed by the bitmap blocks is typically a very small part (less than 1%) of the total segment size for any reasonable sized segment. As shown in the table below, the space overhead of bitmap blocks decreases as the segment grows and becomes close to negligible for large segments. Segment Size Block Size No. Of BMBs Space Used by BMBs BMB Space Overhead 25 MB2 KB 201 400 KB .8% 500 MB 8 KB 251 2 MB .4% 100 TB 16KB 6555941 100 GB .05% -- -- From that, it would follow that free lists as such are gone in ASSM tablespaces and are replaced by bitmaps. As Richard Foote has shown, PCTFREE is not ignored, but without free lists, it doesn't make much sense. Does any of the gurus (Cary, Steve, Jonathan, Wolfgang, Pete Sharman) have any knowledge of how exactly PCTFREE is implemented in the ASSM situation? I can see
Re: Memory consumption on HP-UX
Hi Helmut, Notice the parameter is called pga_aggregate_TARGET and not pga_aggregate_MAX_SIZE. That's because the P_A_T is just that, a target the Oracle does it's best to not exceed. It does this by controlling and rationing the tuneable component of the PGA (ie. those portions of the PGA previously controlled by the *_AREA_SIZE parameters) on a as need/on demand basis based on current system load. However, if the number of sessions/processes is such that the other non-tuneable components of the PGAs were to put pressure on the P_A_T, then Oracle may have no choice but to exceed it. This is not a good thing in that obviously more PGA memory is allocated that you ideally want and also because the workarea operations are not going to be the ideal optimal executions you're after. Increasing the P_A_T would be therefore be recommended, depending of course on your available memory. v$pgastat, v$pga_target_advice and v$process will give you useful info on how much you may have exceeded your P_A_T. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:54 PM Hi, how do I find out how much memory Oracle uses on an HP-UX box? Finding the shared memory portion (i.e. SGA) is fairly easy... But how do I find out how much memory each dedicated user process is consuming? Or is the rule of thumb like this: no matter whether you have 10 or 500 users, the memory consumed by the user processes will never exceed pg_aggregate_target? This would mean that the maximum memory consumption is SGA + PGA_AGGREGATE_TARGET. No matter how many users are on the system (of course you would size PGA_aggregate_target accordingly beforehand). This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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: Richard Foote 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: Monitor Index Usage
Note that 9.2 has the nice family of v$segment_statistic views that can give you this level of information very easily. It has advantages over v$object_usage in that is gives you an indication on how often indexes are used, rather than that they've been used. Although sampling and other factors may impact their accuracy, those indexes that have a very high ratio of logical reads to db block changes you know are highly used for legitimate index accesses, those closer to a 2-1 ratio are only being accessed mainly due to dml changes. A bit of investigation and experimentation and these views can be very useful. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 10:19 AM Check this http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:531147287 002 HTH GovindanK Oracle Certified Professional(8,8i) Brainbench Certified Master DBA(8) On Sun, 09 Nov 2003 20:59:25 -0800, Arvind Kumar [EMAIL PROTECTED] said: Hi all, is there a way to monitor index usages in oracle 8i ,like 9i v$object_usage? Thanks Arvind Kumar -- http://www.fastmail.fm - One of many happy users: http://www.fastmail.fm/docs/quotes.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GKatteri 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: Richard Foote 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: Memory consumption on HP-UX
Hi Juan, We encountered the same problem. Issue was due to OS being set in Eager swapping mode. Support viewed the fact it reserved a massive amount of swap as a feature. However, after switching the OS (HP 5.1 TRU64) to Lazy swap mode, the problem (as one would hope) disappeared and we haven't looked back. We have about 32G of swap disk doing nothing :) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 1:14 AM Take care with automatic PGA management. We have TNS12500 HPUX err 12 using it because proceses RESERVING lots of swap. We change to manual PGA (we use sort_area_size, etc.) It was on 9.2.0.1 -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Richard Foote Enviado el: martes, 11 de noviembre de 2003 13:35 Para: Multiple recipients of list ORACLE-L Asunto: Re: Memory consumption on HP-UX Hi Helmut, Notice the parameter is called pga_aggregate_TARGET and not pga_aggregate_MAX_SIZE. That's because the P_A_T is just that, a target the Oracle does it's best to not exceed. It does this by controlling and rationing the tuneable component of the PGA (ie. those portions of the PGA previously controlled by the *_AREA_SIZE parameters) on a as need/on demand basis based on current system load. However, if the number of sessions/processes is such that the other non-tuneable components of the PGAs were to put pressure on the P_A_T, then Oracle may have no choice but to exceed it. This is not a good thing in that obviously more PGA memory is allocated that you ideally want and also because the workarea operations are not going to be the ideal optimal executions you're after. Increasing the P_A_T would be therefore be recommended, depending of course on your available memory. v$pgastat, v$pga_target_advice and v$process will give you useful info on how much you may have exceeded your P_A_T. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:54 PM Hi, how do I find out how much memory Oracle uses on an HP-UX box? Finding the shared memory portion (i.e. SGA) is fairly easy... But how do I find out how much memory each dedicated user process is consuming? Or is the rule of thumb like this: no matter whether you have 10 or 500 users, the memory consumed by the user processes will never exceed pg_aggregate_target? This would mean that the maximum memory consumption is SGA + PGA_AGGREGATE_TARGET. No matter how many users are on the system (of course you would size PGA_aggregate_target accordingly beforehand). This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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: Richard Foote 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: Juan Miranda 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: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego
Re: PCTFREE and PCTUSED
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, November 10, 2003 9:54 AM
Note that ASSM bitmaps track freeness not fullness, to be correct in
terminology.
Hi Tanel,
At restaurants, I always say to the waiter that my glass of wine is only
1/10 full and can I please have a top-up ;)
Weird eh !!
I posted this little demo on metalink to hopefully prove to Mladen that
PCTFREE is not ignored :
Let's check which tablespaces to use for our test.
SQL select tablespace_name, segment_space_management from dba_tablespaces
where tablespace_name in ('USERS', 'BOWIE_STUFF');
TABLESPACE_NAMESEGMEN
-- --
BOWIE_STUFFMANUAL
USERS AUTO
First lets create two non ASSM tables, one with a low pctfree, the other
with a high pctfree.
SQL create table test_non_assm_1 tablespace bowie_stuff pctfree 5 as select
* from dba_tables;
Table created.
SQL insert into test_non_assm_1 select * from test_non_assm_1;
1103 rows created.
SQL /
2206 rows created.
SQL /
4412 rows created.
SQL /
8824 rows created.
SQL commit;
Commit complete.
SQL create table test_non_assm_2 tablespace bowie_stuff pctfree 90 pctused
10 as select * from dba_tables;
Table created.
SQL insert into test_non_assm_2 select * from test_non_assm_2;
1104 rows created.
SQL /
2208 rows created.
SQL /
4416 rows created.
SQL /
8832 rows created.
SQL commit;
Commit complete.
Let's now analyze these tables (I chose analyze because I want to see the
avg space for each table).
SQL analyze table TEST_NON_ASSM_1 compute statistics;
Table analyzed.
SQL analyze table TEST_NON_ASSM_2 compute statistics;
Table analyzed.
SQL select table_name, blocks, avg_space from dba_tables where table_name
in ('TEST_NON_ASSM_1', 'TEST_NON_ASSM_2');
TABLE_NAME BLOCKS AVG_SPACE
-- -- --
TEST_NON_ASSM_1 452569
TEST_NON_ASSM_2 4982 7395
As expected, the table with a high pctfree uses dramatically more space and
has a higher avg space value.
Let's repeat the test with ASSM tables. This should *prove* whether pctfree
is ignored or not.
SQL create table test_assm_1 tablespace users pctfree 5 as select * from
dba_tables;
Table created.
SQL insert into test_assm_1 select * from test_assm_1;
1105 rows created.
SQL /
2210 rows created.
SQL /
4420 rows created.
SQL /
8840 rows created.
SQL commit;
Commit complete.
SQL create table test_assm_2 tablespace users pctfree 90 pctused 10 as
select * from dba_tables;
Table created.
SQL insert into test_assm_2 select * from test_assm_2;
1106 rows created.
SQL /
2212 rows created.
SQL /
4424 rows created.
SQL /
8848 rows created.
SQL commit;
Commit complete.
SQL analyze table TEST_ASSM_1 compute statistics;
Table analyzed.
SQL analyze table TEST_ASSM_2 compute statistics;
Table analyzed.
SQL select table_name, blocks, avg_space from dba_tables where table_name
in ('TEST_ASSM_1', 'TEST_ASSM_2');
TABLE_NAMEBLOCKS AVG_SPACE
-- -- --
TEST_ASSM_247307347
TEST_ASSM_1 5011213
As we can see, the results are very similar. The table with a high pctfree
has a massive number of blocks and (potential) wasted space relative to the
table with a low pctfree.
PCTFREE is most definitely *NOT* ignored with ASSM !!
Cheers
Richard Foote
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Richard Foote
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: PCTFREE and PCTUSED (and ASSM)
Hi Mladen, Oh, I have no doubts ;) Hurt you still don't trust me but considering our little wager (2 tickets to the Bowie world tour) I guess it's only fair you go to a neutral referee. Bet when they confirm what I say they don't give as detailed an explanation ;) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 09, 2003 12:19 AM Richard, I asked the question on the Metalink. The mighty Metalink will, hopefully, resolve the doubt once and for all. Tom Kyte is busy so he doesn't accept new questions right now. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Richard Foote 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: PCTFREE and PCTUSED
Hi Mladen, Yes, I can offer some additional information. Firstly, let me extend your quote from the Concepts manual where immediately afterwards it says (quote) : Free lists have been the traditional method of managing free space within segments. Bitmaps, however, provide a simpler and more efficient way of managing segment space. They provide better space utilization and completely eliminate any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS attributes for segments created in the tablespace. If such attributes should be specified, they are ignored. I'm not entirely in agreement with the quote (that's a different story) but notice there is no mention of PCTFREE. The reason for that is quite easy to explain. One needs to remember the purpose for PCTFREE, it's there to determine how much of a block should be reserved in order for existing rows within the block to grow. Simplistically, the correct value for PCTFREE should be the average expected growth of a row. How can Oracle automatically determine when to stop inserting rows into a block such that these rows have sufficient space to grow ? The answer is that Oracle simply can't, it's entirely dependent on the growth characteristics of the tables which differs from table to table. ASSM is designed to automatically determine whether or not a block should be considered for inserts. It does this by using a sequence of bitmaps to describe the fullness of a block. There are different levels of fullness empty 0-25, 25-50, 50-75 and 75-*full*. However what does *full* actually mean or at what point does Oracle no longer consider the block suitable for inserts. That is determined by PCTFREE and as Oracle has no idea by how much existing rows could grow, PCTFREE is still a crucial and configurable attribute of a segment, even in a ASSM tablespace. And as we still need to set PCTFREE, we can still stuff it up (or more commonly, totally ignore it). Set it too high and Oracle prematurely considers the block full and no longer considers it for inserts, resulting in wasted space below the (now various) HWMs. Set it too low and we stuff our blocks up too full resulting in row migration due to subsequent updates. We haven't even touched the subject of ITL entries which is also unaffected by ASSM. Therefore, ASSM does little to resolve the issues you've listed because you still need to manually set the PCTFREE. Honest ;) Hope this makes some sense :) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 10:49 AM Richard, here is what the concepts manual says (quoted): Segment Space Management in Locally Managed Tablespaces When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed. Your choices are: * AUTO This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps enable Oracle to manage free space more automatically; thus, this form of space management is called automatic segment-space management. That looks to me like automating the functionality of PCTFREE/PCTUSED. Do you hae some other information? On 2003.11.07 17:59, Richard Foote wrote: Hi (again) Mladen, I'm sure I mentioned this previously but ASSM only deals with FREELISTS, FREELIST GROUPS and PCTUSED (with possibly significant overheads). You still need to set *PCTFREE*, which means you can still have over allocation of space if you set it too high, you can still have row migration if you set it too low, you still have row chaining, you can still have waits on ITL entries and other lovely things ... ASSM is most certainly *not* some magic fix. And it's only available since 9i. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 4:59 AM On the other hand, you might have overallocated the space, which would leave plenty of blocks on the free list, thus minimizing the impact. These things are best seen on almost full tables with things like row chaining, row migration, waits on ITL entries and other lovely things. Looks like you've benn lucky so far. As I've told you before, having tablespaces created with SEGMENT SPACE MANAGEMENT AUTO option takes care of that. if your tablespace is created with a command like CREATE TABLESPACE DATA01 DATAFILE '/data01/SID1/data01.dbf' size 8192M reuse AUTOEXTEND ON NEXT 4096M MAXSIZE 32769M EXTENT MANAGEMENT
Re: PCTFREE and PCTUSED
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: Richard Foote 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: shareplex: datatype unsupported
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 9:34 AM You know, from a logistics perspective I'm interested in something here that maybe those that use SharePlex can cast some light on. The only Oracle supported mechanism for mining the redo logs is LogMiner, yes? No ;) Don't forget Oracle Streams which kinda behaves like Shareplex except that Oracle stores its queues right back in the database. BTW, couldn't make lunch today as I was recovering after being taken to hospital after passing out with a ruptured calf muscle :( Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: dba interview questions
Only two questions are required to ensure you get an appropriate person for the job (any job): 1) What do you think of David Bowie, is he brilliant or what ? and providing they answer the above question positively 2) Are you any good ? Works every time ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 7:44 PM I ask things like tell me the thing you've done that you are most proud of and tell me your nightmare situation and how did you recover from it Ans: My worst nightmare, my date pick her nose infront of me, I call cab infront of her. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing 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: Richard Foote 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: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
Hi Hemant, How I dislike being immortalised ;) The note basically quoted me word for word on my feedback and that's fine, it's certainly an improvement on what was previously suggested (and yes, Oracle asked for my permission). A point I would add though is that the whole subject of how Oracle indexes function and the various cases when one should or should not rebuild indexes is not black and white and is not easily covered in a couple of paragraphs. There are always exceptions and oddities, the key is determining when these scenarios arrive and taking the appropriate action. Many books/articles emphasise the need to rebuild generally and often, I'm suggesting the emphasis should be far more considered and practical. If anyone reading the note now questions the rebuild generally and often approach, then my comments serve their intentions. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 1:19 AM Yes. However, every time he has replied to me, he has been confident that he IS right. Mind you, Richard, you are immortalised now ! Hemant At 05:04 PM 22-10-03 -0800, you wrote: So now the blame rests solely on Richard for any material in the note that's wrong. :) Check the latest update: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data b ase_id=NOTp_id=182699.1 Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Wednesday, October 22, 2003 2:35 AM To: Multiple recipients of list ORACLE-L Oops, I didn't see that part. Thanks for the catch, Hemant. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Tuesday, October 21, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Unfortunately, the lines Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about index fragmentation have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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
Re: Cache a table
Hi
It depends on how you define an LRU list I guess. When I close my eyes and
picture the cache, I still see a LRU in there somewhere. Please note I don't
often close my eyes in this manner ;)
Also when you say that the CACHE option has no effect, that's also a little
questionable. This is just a portion of a post I recently sent to
comp.databases.oracle.server in the Cache A Table thread:
Simple demo on 9.2, the BOWIE table is approximately 13,000 blocks, SMALL is
117 blocks:
SQL alter table bowie nocache;
Table altered.
SQL select object_name, object_id, data_object_id from dba_objects where
object
_name in ('BOWIE', 'SMALL');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
--- -- --
BOWIE31379 31379
SMALL31457 31457
SQL select * from bowie; (run with autotrace traceonly)
SQL select count(*) from x$bh where obj=31379;
COUNT(*)
--
18
Note that only the last few blocks from the FTS actually remain in memory.
If I repeat the select, I still have the same result from x$bh and the same
number of *physical reads occur each time.
If I run the same thing with my small table which has about 117 blocks,
the same thing happens
SQL alter table small nocache;
Table altered.
SQL select * from small;
SQL select count(*) from x$bh where obj=31457;
COUNT(*)
--
18
Note that again only the last few blocks from the FTS actually remain in
memory. If I repeat the select, I still have the same result from x$bh and
again the same number of physical reads occur each time.
OK, lets change my small table and cache the thing and see if I get a
different result ...
SQL alter table small cache;
Table altered.
SQL select * from small;
SQL select count(*) from x$bh where obj=31457;
COUNT(*)
--
117
I now see that all 117 blocks (that's all data blocks + segment header) are
all now cached as expected. Repeated reruns of the select now generate *no*
physical I/Os.
But what if I now run a select on my big BOWIE table, what effect will
this have on the SMALL cached blocks ?
SQL select * from bowie;
SQL select count(*) from x$bh where obj=31379;
COUNT(*)
--
18
Nothing new here, only the last few blocks again remain from the BOWIE table
with the same physical I/Os generated.
SQL select count(*) from x$bh where obj=31457;
COUNT(*)
--
117
and thankfully nothing has changed with the SMALL table as a result. These
blocks still remain cached and have not been dislodged as a result of the
FTS on the big BOWIE table (as they sit safely somewhere near the middle,
cold side of the LRU)
Finally, what if we play silly buggers and decide to cache the big BOWIE
table ...
SQL alter table bowie cache;
Table altered.
SQL select * from bowie;
SQL select count(*) from x$bh where obj=31379;
COUNT(*)
--
1338
We now see that a whole heap of buffers have now been cached, approximately
10%. However, again the physical I/Os remain constant because we are still
not effectively caching the table (the undocumented parameters behind the
scene kick in to prevent the whole cache from flooding).
But the effect on poor SMALL...
SQL select count(*) from x$bh where obj=31457;
COUNT(*)
--
1
only one poor block (the header) has survived the experience :(
Hope this clears something up !!
Cheers
Richard Foote
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 10:59 PM
Mike:
I guess we are aware there is no concept of LRU or MRU in current
versions of Oracle and I don't think CACHE option will influence the
behavior. With the new algorithm the MFU blocks are already in the hot
end (unless they are read using CR read in that case they will be in
cold end since we set the _db_aging_freeze_cr to TRUE) and we don't
need to cache the blocks explicitely.
You can monitor the behavior of this using the X$BH (espicially the
last two columns TCH and TIM).
=
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Richard Foote
INET: [EMAIL
Re: using temp tables for staging databases?
is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Tim Gorman 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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: Arup Nanda 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: Richard Foote 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: query's explain plan different in 8i and 9i?
I've just posted the answer to this question at the comp.databases.oracle.server newsgroup !! Here it is again. If you look closely at the execution plans, they're performing a BITMAP CONVERSION rather than using bitmap indexes per se. This where Oracle converts btree indexes to bitmaps on the fly and uses subsequent and/or row eliminations. The reason for this is behaviour is because a rather important parameter _B_TREE_BITMAP_PLANS has had it's default changed from false to true. This parameter has been undocumented for a while but like all these hidden changes, can have unwanted repercussions. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 8:44 AM Hi: I found that a query worked quite well on Oracle 8173 is running very slow on Oracle 9i. I doubled check init paramters and they are the same. The table involved has about 20M rows. The tables has been analyzed in both cases. Is there any thing I should look or set in 9i so that query can run as fast as 8i? Better yet, what could be the reason that this query is using BITMAP instead of regular index range scan? TIA. Guang PS: The query is SELECT queryid, subjid, 100.0*identity/matchlen pctfrom blastresults where ((subjspid in (456,789) and queryid = 123) or (queryspid in (456,789) and subjid = 123)) and (identity/matchlen = .200 or positive/matchlen = .400) order by blast.pvalToNumber(pval) asc, score desc; -- 9i: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6987 Card=1 Bytes=42 ) 10 SORT (ORDER BY) (Cost=6987 Card=1 Bytes=42) 21 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost=69 85 Card=1 Bytes=42) 32 BITMAP CONVERSION (TO ROWIDS) 43 BITMAP OR 54 BITMAP AND 65 BITMAP CONVERSION (FROM ROWIDS) 76 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUERYID_IN DEX' (NON-UNIQUE) (Cost=3) 85 BITMAP OR 98 BITMAP CONVERSION (FROM ROWIDS) 109 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SSPID_IN DEX' (NON-UNIQUE) (Cost=1528) 118 BITMAP CONVERSION (FROM ROWIDS) 12 11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SSPID_IN DEX' (NON-UNIQUE) (Cost=1528) 134 BITMAP AND 14 13 BITMAP CONVERSION (FROM ROWIDS) 15 14 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJID_IND EX' (NON-UNIQUE) (Cost=3) 16 13 BITMAP OR 17 16 BITMAP CONVERSION (FROM ROWIDS) 18 17 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QSPID_IN DEX' (NON-UNIQUE) (Cost=1282) 19 16 BITMAP CONVERSION (FROM ROWIDS) 20 19 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QSPID_IN DEX' (NON-UNIQUE) (Cost=1282) -- 8i: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=184 Card=2 Bytes=84) 10 SORT (ORDER BY) (Cost=184 Card=2 Bytes=84) 21 CONCATENATION 32 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost= 11 Card=1 Bytes=42) 43 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJID_INDEX' (N ON-UNIQUE) (Cost=3 Card=1) 52 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost= 11 Card=1 Bytes=42) 65 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUERYID_INDEX' ( NON-UNIQUE) (Cost=3 Card=1) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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: Richard Foote 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
Re: Block size : what is the gain ?
Hi Stephane, If you're using a conventional file system on AIX, you can expect a reduction in performance by moving to 16K from your already imperfect 8K. See Steve Adam's notes why the DB block size should = the file system buffer size (www.ixora.com). And that's *4K* on AIX. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 4:59 AM Hi, All our DB have an 8k block size (8172/aix). Even the reporting/dss database where data is accessed mainly by full scan. Can we quantify the gain in % of switching from an 8k to 16k block size from a performance point of view ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette 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: Richard Foote 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: re Rebuilding Indexes in Oracle Apps --
applications, etc. Hemant, take a look at Jonathan Lewis's article When Should You Rebuild An Index at www.dbazine.com . In it he concludes Will the total cost of rebuilding the index be a reasonable price to pay for the resulting benefit to the system ? The answer to this question is frequently a resounding NO. In fact, sometimes the overall impact of rebuilding an active index will be detrimental to the system. However, there are still plenty of misconceptions about indexes that result in DBAs the world over wasting valuable time and effort rebuilding indexes unnecessarily. Amen to that !! Cheers ;) Richard Author: Richard Foote 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Richard Foote 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: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 18, 2003 4:42 AM Hi Hermant, I wonder if it is not necessary to rebuild indexes is also a myth. It might be but I've yet to hear it. It's certainly not something I've ever claimed, unless it's a quote taken out of context (the start and end are missing) which would be unfortunate. I would re-phrase it as it is *rarely* necessary to rebuild indexes and it would be a hell of a lot more accurate than many quotations on this subject. So let's not confuse and cloud the issue. It IS in some cases necessary Yes it is but the point I'm trying to make that the some cases are relatively *rare*. The emphasis as I often hear it is that indexes usually/always need to be rebuilt. This is simply incorrect. The Metalink note claims that deleted space is not reused. This is not only incorrect but helps promote the myth that indexes hence need frequent rebuilding. One incorrect claim promotes one incorrect conclusion. 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID based on a Sequence As I've previously stated *but* and it's a big BUT only if there are subsequent sparse deletions. No spares deletions, no rebuilds are necessary. What ratio of indexes in Oracle financials actually meet this criteria ? Monotonically increasing *and* sparse deletions. or even on date columns which signify when the record is created] if the table is also purged by the same columns frequently Similar case to the above. But this implies a specific range of index values being deleted which results in a range of index nodes being emptied. These blocks therefore *can* be reused. If records are subsequently inserted *at the same rate* they are being purged, then again index rebuilds are potentially unnecessary. 2. Because the disk space used by an Index can be inordinately larged after a couple of years and index fast_full_scans are impacted How ? We covered one case above. Another is that we simply reduce the volume of data within a table (and hence index). How does time result inordinately enlarged indexes ? As previously discussed, Oracle is very efficient in the way it reuses space within an index, suggestions that indexes just become unnecessarily enlarged over time are generally false. Have you administered an Oracle Applications database ? No, but I have a number of SAP applications and they suffer from the same bad advice that indexes generally require frequently rebuilding. In actual fact, the ratio of indexes that actually benefit from rebuilding is tiny and then it's generally the table that needs rebuilding more so than the indexes directly and then the tiny tiny ratio of indexes that remain generally need coalescing rather than rebuilding. Indexes that exist in Oracle Applications are not special, they follow the same rules as those indexes in SAP, or in-house applications, etc. Hemant, take a look at Jonathan Lewis's article When Should You Rebuild An Index at www.dbazine.com . In it he concludes Will the total cost of rebuilding the index be a reasonable price to pay for the resulting benefit to the system ? The answer to this question is frequently a resounding NO. In fact, sometimes the overall impact of rebuilding an active index will be detrimental to the system. However, there are still plenty of misconceptions about indexes that result in DBAs the world over wasting valuable time and effort rebuilding indexes unnecessarily. Amen to that !! Cheers ;) Richard hemant At 03:29 AM 17-10-03 -0800, you wrote: Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared,Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says "do something, do something". So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. Theseso-called holes are fully re-usableand in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... Firstof all, create a simple table and index. I've intentionally left a value out "in the middle" of a range for extra effect. SQL create table bowie_test (ziggy number); Table created. SQL insert into bowie_test values (1); 1 row created. SQL insert into bowie_test values (2); 1 row created. SQL insert into bowie_test values (3); 1 row created. SQL insert into bowie_test values (4); 1 row created. SQL insert into bowie_test values (6); 1 row created. SQL insert into bowie_test values (7); 1 row created. SQL insert into bowie_test values (8); 1 row created. SQL insert into bowie_test values (9); 1 row created. SQL insert into bowie_test values (10); 1 row created. SQL insert into bowie_test values (100); 1 row created. SQL commit; Commit complete. SQL create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no "wasted" deleted space ... SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 10 0 0 We now delete a number of rows ... SQL delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say "nasty wasted spaces it is, gollum ..." SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL insert into bowie_test values (5); 1 row created. SQL commit; Commit complete. SQL analyze index bowie_test_idx validate structure; Index analyzed. SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 3 0 0 and we see that *all* the "wasted" deleted space within the leaf node has been freed and is available for reuse ... With few exceptions (the key is picking those rare cases), index rebuildsare redundant, wasteful and can actually be "detrimental" to performance. Cheers Richard
Re: RE: Separate Indexes and Data
Title: Message Hi All,I'm having all sorts of problems getting these emails in a logical order (if at all). This is the first post on this subject I've received since I posted to Rachael, I haven't even received my own post yet !!Anyways, going back in orderFirst to John, no, not all monotonically "here today, gone tomorrow" indexes require rebuilding. Note that fully "emptied" index blocks get placed on the freelist and are fully reusable by subsequent index splits. Therefore if you perform batch deletes over a specific period whereby most deleted entries fully empty a range of index nodes, then frequent rebuilding is highly questionable. Yes, Index Scans/Fast Full Index Scans etc.could be impacted in the interim, it kinda depends on *when* the same volume of data is to be reinserted. Jared, please do write your article (the more solid articles out there the better)!! However note that Jonathan Lewis has written a couple of nice articles over at www.dbazine.com regarding some truths about indexes and index rebuilding. Unfortunately the same site hosts truly awful articles by John Weeg and Mike Hordila who bothpromote some shocking untruths/myths regarding indexes (that Oracle indexes become unbalanced, that deleted space is never reused, that 4 extents is sufficient for an index, etc. etc.) so one needs to exercise caution when reading stuff from there.Jay, note that indexes generally *do* release space from deleted entries !! Deleted space from a index node within the current index structure can be totally reused by subsequent inserts. And as mentioned earlier, fully emptied blocks can be reused by subsequent index block splits. The requirement to rebuild an index is *extremely rare*. This subject has been raised a number of times recently on the Oracle newgroups (eg. http://groups.google.com/groups?q=g:thl4040185351ddq=hl=enlr=ie=UTF-8selm=QPThb.146517%24bo1.128474%40news-server.bigpond.net.au). It feelslike fighting a lossing battle but one can only try. Yes bulk deletes without subsequent re-inserts or without re-inserts within a "reasonable" period requires both table and hence index rebuilds (to reset HWMs). Yes *sparse* deleting of *monotonically* increasing index entries might require index rebuilds (or coalescing) to compact index structure for both range scan and fast full index scans. But these are generally *exceptions*, not the norm. Hope this mail makes it ?? Cheers Richard - Original Message - From: John Kanagaraj To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 14, 2003 5:44 AM Subject: RE: RE: Separate Indexes and Data Jared, Any indexes supporting a "In-Today; Gone-Tomorrow" status table will require index rebuilds. Most of them have monotonically increasing numbers which lends itself to a 'holey' index... (I have a bunch of them with Oracle Apps Concurrent Manager and Workflow tables) John KanagarajDB Soft IncPhone: 408-970-7002 (W)Disappointment is inevitable, but Discouragement is optional!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 13, 2003 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: RE: Separate Indexes and Datahmmm... fodder for an article I've been contemplating. "Indexes: to rebuild or not to rebuild - that is the question" There's no need to reclaim space, except in special circumstances. As Kirti pointed out once, a sequentially incrementing numeric key is possibly one of those circumstances. Not much point in rebuilding indexes in most cases. If anyone cares to submit test cases for validation of the need of an index rebuild, you may do so here. Give me some test fodder! Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/13/2003 08:59 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: Separate Indexes and DataI assume that what Rachel is referring to is the fact that indexes willgenerally not release much space when the underlying rows are deleted. Theyjust keep growing, so if you have a large indexed table that frequentlydeletes and inserts the indexes can grow to fairly ridiculous sizes over aperiod of time. We just went through the exercise of rebuilding indexes ona db supporting a 3rd party app and reclaimed about 70% of the allocatedindex space.Jay MillerSr. Oracle DBAx68355-Original Message-Sent: Sunday, October 12, 2003 7:39 AMTo: Multiple recipients of list ORACLE-LHi Rachael,You have me a
Re: RE: Separate Indexes and Data
://www.orafaq.net -- Author: Rachel Carmichael 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: Richard Foote 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: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, Good questions. As you mention, the memory Oracle says and thinks it's released and what it actually releases to the kernel has generally been two different things. However, the behaviour with P_A_T is somewhat different. A simple little test for the unconvinced is to simply issue (this btw is on Tru64, 5.1): ls -l /proc/1685047 where 1685047 is a process id of interest. It lists the sum of all memory structures associated to the process, the whole lot. With workarea_size_policy left at manual, listing this before any significant (say) sort activity and then afterwards, you'll see the amount of total memory climb but not come down . This extra memory is effectively hogged until the session closes as you describe. However when you run the same test with work_area_size set to auto, you'll notice the total memory climb during the workarea operation but importantly come back down again once complete (except for a little memory leak here or there). The point is though that the memory is being released and is no longer associated with the process. Our extra 2G of available memory on the O/S level suggests that memory is being more than efficiently reused. I'm not entirely sure how Oracle differs in it's implementation and what new O/S calls it performs (far from being obscure, it's certainly something worth an investigating). What I would certainly recommend is that one check out this new feature, see how it performs in one's environment and under one's particular workload conditions and determine whether or not it's beneficial. Maybe others have similar real life experiences to share ? Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 3:44 AM Richard, Thanks for the detailed explanation! As a C programmer of some 20 years, I can only assume that Oracle has done away with the use of the malloc(), free(), etc UNIX library calls and is now calling the UNIX system call brk() directly? It was the underlying heap-extent management in the standard malloc library, which is of course outside of Oracle's control, which made the SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating workarea memory back to the OS. Because if Oracle is continuing to call malloc() and free(), then I can only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as it did in the past), but it really isn't. Any idea if this is the case? Kind of obscure, I know, but it is this chain of reasoning that has allowed a reasonable explanation of the ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its documented purpose in the past. Thanks in advance! -Tim on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat higher than perhaps desirable (if reached) knowing it won't in fact be reached because of the low concurrency of these operations. This then increases the maximum memory capacity for each session in a controlled manner, knowing that this memory won't
Re: 8i OCP Net8 Exam
Hi Dennis, There's no need to apologise. I don't blame you for trying to get the OCP in manner that's reduces risk and improves one's chance of success. The path of least resistance is one that is followed by human nature and if I were in your shoes and had your pressures I would do exactly the same thing. I wish you the best of luck with it all. I just also wish that the OCP program was worth a little more. See my other mail to Faan regarding the OCM program. Good in principle, but ... Good Luck Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 1:34 AM Richard - My apologies that concern for passing the exam has caused some of us to exchange tips that you find offensive. And I truly admire those who have been able to just walk in the exams and pass. And I had similar gripes against the exams until I was felt the need to pass the exams. I understand that concerns like yours have caused Oracle to create the OCM. Have you taken a look at that? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 01, 2003 10:04 AM To: Multiple recipients of list ORACLE-L - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 8:39 AM Dennis, Do you have good practice exams? I have found that the best preparation for the OCP exams are good practice exams. I have reached the point where I just skim through the material in the whatever book you use (probably only possible if you have some miles on the clock as a DBA) and then drive the in depth study from the practice exams. This way you are spending your time more focused and find out what exactly the OCP exam will expect from you vs. what some author would like to teach you or even what the correct answer is in reality. Also, many of these questions in the practice exams will appear in the actual OCP exam which builds your confidence while writing the actual exam. It's comments such as these which are unfortunately all so common that really hits home what an laughable, sad and sorry affair the whole OCP program really is. I have this vision of people poring over example questions, desperately trying to memorise as many questions as possible, desperately trying to forget what is correct is reality for fear of not getting the required 65% multiple questions correct. Occasionally, they'll glance at the Inside OCP section of the Oracle Magazine and gain confidence in getting the jest of the complex concepts (and yes, further sample questions) it covers in each edition. And once they've passed and got that precious certificate, they're of course qualified to look after that banks enterprise database because they're Oracle approved Oracle Certified *Professionals*. And when the database runs like a dog, they'll open up their notebooks and decide is it: A) The Buffer Cache Hit Ratio is less than 90% B) The Library Cache Hit Ratio is less than 90% C) The DD Cache Hit Ratio is less than 90% D) The cleaning lady has accidentally pulled out the wrong plug E) Something else Good grief !! Now I too have spent many years teaching with Oracle Education and I'm Oracle 7, 8, 8i and 9i OCP (instructors were obliged to get certified) so I know a fair bit about the process. And I've seen students leave my classroom with 5 days Oracle experience behind them pass their OCP DBA Admin exam the following Tuesday (guess I was a good teacher :) Anyone see a problem ? At the time I kinda justified it as selling them water in that it doesn't really harm them, achieves nothing but at least they think it's doing them some good. Don't get me wrong, the training they received was excellent, it's the OCP bit that is fluff. But really, at the end of the day, having such an atrocious so called professional program ends up hurting the individual as they've paid a lot of money (for the exams) for very little benefit, it hurts organisations in that there's no *guarantee* of hiring anything closely resembling an Oracle Professional as the bar is so low it drags along the ground and really it ends up hurting Oracle Corp. as well. The *only* thing it does have going for it is that it motivates some people to getting training and investigating parts of Oracle they may otherwise not have much to do with. But I've always thought giving away free David Bowie Cds at training courses was a better way to go :) Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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
Re: 8i OCP Net8 Exam
Hi Faan, I really do sympathise with folks that are kinda dragged into this whole OCP debacle by people who have obsoletely no understanding of how it's implemented and administered (HR heavies, higher management, recruitment agencies, outsourcing and system integrating organisations, etc.). And of course they push you for very good and noble reasons, generally to ensure that one is competent and qualified to perform the tasks required of the OCP job role. You know, a professional. What they fail to comprehend however is that the OCP program fails utterly to achieve these goals. It's a question of process and red tape rather than of achieving it's desired purpose and results. Sure many OCPs are extremely competent and professional however you can't be sure. In fact because the exams are so trivial you really can't be sure an OCP has ever opened an Oracle database. I agree that the OCM model is a far better measurement of what the OCP program was claiming to achieve. Hearing from people such a Pete Sharman who have gone through it, it's certainly a tough examination and one what requires both a reasonable understanding and practical hands-on knowledge. But unfortunately, there's a but ... Actually, there's a number of buts. Firstly the cost is prohibitive. It ain't cheap, especially when you add in the cost of the 2 day exam, the cost of the 2 mandatory training courses (why someone capable of passing the exam is forced to attend 2 courses is just plain silly), the cost of travel and accommodation, the cost of not working for this period It's runs to many many thousands of dollars which is beyond the reach of many, especially as the cost/benefits are still somewhat dubious. OCM or a round the world holiday with the family, hu tough choice. But unfortunately, Oracle's *main* priority here is to make money, not to have a successful and meaningful certification program (which is fair enough). It might sound as if the two objectives should be mutually beneficial (I've always argued that a successful program would be profitable both directly and more importantly indirectly) but unfortunately with the over emphasis of making huge profits, Oracle ends up failing on both counts. As a result, only a handful have gone through the program, events are cancelled due to insufficient numbers and the whole thing is struggling. The other problem of course is that Oracle has got itself caught in a corner somewhat. They can't make too big a deal on the improvements of the OCM scheme, they can't really go on about how OCM lifts the bar to the point when you need a reasonable little jump to get over, that OCM is a significant improvement over OCP ... Because that would mean admitting that OCP is meaningless as a measurement of professionalism, that the P in OCP has been false advertising and that an OCP can't really be trusted to look after your invaluable information investment and what you *really* need is an OCM. The risk to the credibility of any Oracle certification program would come into doubt for what the heck has Oracle been producing all these years to warrant such a change in direction. You see the problem. Ideally, Oracle (or any vendor for that matter) shouldn't be running these certification programs, simply because they have conflicting priorities. A program that is successful in that it attracts a lot of people and that it makes a lot of money suggests that it has to be relatively easy (to get the numbers) and expensive (to make the money). Ideally, an organisation that is *focused* on the *quality* and professionalism of IT specialists that result from such a program should be running the show. Unfortunately, we don't live in an ideal world ... But perhaps by voicing one's concerns and opinions, we might just get a little closer ;) Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 4:49 AM Richard, I agree that the OCP is a laughing matter among experienced DBA's but to those that are pressurized by their non-technical management, especially HR, to obtain certification for various reasons, there is very little choice than to get it or get out...:-( I can definitely see that certification programs that model the latest 9i OCM will be the future for reliable and most importantly, credible certification. The big dilemma with this certification model is limited availability world wide and unacceptable high cost when compared to the traditional certification model...guess time will tell which model will suffice...perhaps a hybrid of the 2 models? Will be interesting to follow the certification trend from other vendors in the enterprise space e.g. Veritas, EMC, etc. I agree that it is more important to know the correct answer in the real world where the rubber meets the road, but as Carry M. stated so elegantly in an earlier post that a wise DBA will know several i.e. the answer that the OCP
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, Good questions. As you mention, the memory Oracle says and thinks it's released and what it actually releases to the kernel has generally been two different things. However, the behaviour with P_A_T is somewhat different. A simple little test for the unconvinced is to simply issue (this btw is on Tru64, 5.1): ls -l /proc/1685047 where 1685047 is a process id of interest. It lists the sum of all memory structures associated to the process, the whole lot. With workarea_size_policy left at manual, listing this before any significant (say) sort activity and then afterwards, you'll see the amount of total memory climb but not come down . This extra memory is effectively hogged until the session closes as you describe. However when you run the same test with work_area_size set to auto, you'll notice the total memory climb during the workarea operation but importantly come back down again once complete (except for a little memory leak here or there). The point is though that the memory is being released and is no longer associated with the process. Our extra 2G of available memory on the O/S level suggests that memory is being more than efficiently reused. I'm not entirely sure how Oracle differs in it's implementation and what new O/S calls it performs (far from being obscure, it's certainly something worth an investigating). What I would certainly recommend is that one check out this new feature, see how it performs in one's environment and under one's particular workload conditions and determine whether or not it's beneficial. Maybe others have similar real life experiences to share ? Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 3:44 AM Richard, Thanks for the detailed explanation! As a C programmer of some 20 years, I can only assume that Oracle has done away with the use of the malloc(), free(), etc UNIX library calls and is now calling the UNIX system call brk() directly? It was the underlying heap-extent management in the standard malloc library, which is of course outside of Oracle's control, which made the SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating workarea memory back to the OS. Because if Oracle is continuing to call malloc() and free(), then I can only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as it did in the past), but it really isn't. Any idea if this is the case? Kind of obscure, I know, but it is this chain of reasoning that has allowed a reasonable explanation of the ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its documented purpose in the past. Thanks in advance! -Tim on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat higher than perhaps desirable (if reached) knowing it won't in fact be reached because of the low concurrency of these operations. This then increases the maximum memory capacity for each session in a controlled manner, knowing that this memory won't be hogged by the sessions
Re: Huge optimization costs with 9.2
We has problems with another undocumented parameter that changed when we migrated to 9i in August last year. _B_TREE_BITMAP_PLANS change from false to true and caused a number of issues with sub-optimal execution plans. Another possible trap for the unwary ... Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:54 AM One of the undocumented init.ora parameters that changed from 8 to 9 is _UNNEST_SUBQUERY (from false to true). You could try if that is the culprit. Of course, since it is an undocumented parameter, get the blessing from Oracle support before using it in a production database. At 10:09 AM 10/1/2003, you wrote: Joan, what is the difference in the plans? What specific feature made the difference? Are the values of optimizer_index_cost_adj and optimizer_index_caching same on both versions? How about histograms? What is with db_file_multiblock_read_count,sort_area_size and hash_area_size? Is everything same as in 8i? May be setting of those parameters can be tweaked to your benefit? On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: Kirti, I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, performance is good. After upgrade, one query run time from 2 min to 12 hours. Of course, I re-analyzed all tables and indexes. The explain plan changed from hash join to nested-loop. All the parameters are same. So I have to put optimized_feature_enable=8.1.7 to make run normal as usual. I hate to disable the new feature, but no choose. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Richard Foote 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: 8i OCP Net8 Exam
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 8:39 AM Dennis, Do you have good practice exams? I have found that the best preparation for the OCP exams are good practice exams. I have reached the point where I just skim through the material in the whatever book you use (probably only possible if you have some miles on the clock as a DBA) and then drive the in depth study from the practice exams. This way you are spending your time more focused and find out what exactly the OCP exam will expect from you vs. what some author would like to teach you or even what the correct answer is in reality. Also, many of these questions in the practice exams will appear in the actual OCP exam which builds your confidence while writing the actual exam. It's comments such as these which are unfortunately all so common that really hits home what an laughable, sad and sorry affair the whole OCP program really is. I have this vision of people poring over example questions, desperately trying to memorise as many questions as possible, desperately trying to forget what is correct is reality for fear of not getting the required 65% multiple questions correct. Occasionally, they'll glance at the Inside OCP section of the Oracle Magazine and gain confidence in getting the jest of the complex concepts (and yes, further sample questions) it covers in each edition. And once they've passed and got that precious certificate, they're of course qualified to look after that banks enterprise database because they're Oracle approved Oracle Certified *Professionals*. And when the database runs like a dog, they'll open up their notebooks and decide is it: A) The Buffer Cache Hit Ratio is less than 90% B) The Library Cache Hit Ratio is less than 90% C) The DD Cache Hit Ratio is less than 90% D) The cleaning lady has accidentally pulled out the wrong plug E) Something else Good grief !! Now I too have spent many years teaching with Oracle Education and I'm Oracle 7, 8, 8i and 9i OCP (instructors were obliged to get certified) so I know a fair bit about the process. And I've seen students leave my classroom with 5 days Oracle experience behind them pass their OCP DBA Admin exam the following Tuesday (guess I was a good teacher :) Anyone see a problem ? At the time I kinda justified it as selling them water in that it doesn't really harm them, achieves nothing but at least they think it's doing them some good. Don't get me wrong, the training they received was excellent, it's the OCP bit that is fluff. But really, at the end of the day, having such an atrocious so called professional program ends up hurting the individual as they've paid a lot of money (for the exams) for very little benefit, it hurts organisations in that there's no *guarantee* of hiring anything closely resembling an Oracle Professional as the bar is so low it drags along the ground and really it ends up hurting Oracle Corp. as well. The *only* thing it does have going for it is that it motivates some people to getting training and investigating parts of Oracle they may otherwise not have much to do with. But I've always thought giving away free David Bowie Cds at training courses was a better way to go :) Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat higher than perhaps desirable (if reached) knowing it won't in fact be reached because of the low concurrency of these operations. This then increases the maximum memory capacity for each session in a controlled manner, knowing that this memory won't be hogged by the sessions. As I mentioned before, we now experience no disks sorts whatsoever. In our environment, automatic workspace management has been ideal. We have a large number of sessions most of which perform workspace operations at some stage but not concurrently in any significant numbers. Thereby, we have managed to both improve the efficiency of workspace operations by allowing sessions to acquire the necessary memory as required while at the same time dramatically reducing overall memory consumption. Best of both worlds !! Cheers - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 7:39 AM Richard, I take it that your two points are...shall we say...enhancement requests, not current functionality? :-) Following up on the discussion of space-efficiency and tabling (for the moment) my questions about the performance-efficiency side of things. Yes, there certainly is an element of performance-efficiency to space-efficiency if it keeps you from swapping... ...anyway... Using WORKAREA_SIZE_POLICY = MANUAL, only the sort workarea has ever even pretended to give memory back for the duration of the session, depending on the relationship between SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE. The hash and bitmap workareas have never had this functionality, as near as I can tell. So, I think that you're absolutely correct that sessions using WORKAREA_SIZE_POLICY = MANUAL will allocate the memory and hold onto it for a long time, essentially until they disconnect. Is this correct? Is WORKAREA_SIZE_POLICY = AUTO any different? From what I've gathered, the P_A_T algorithms only occur upon allocation of workarea memory. Is there any additional logic around de-allocation, possibly when the server process has finished using the workarea? Perhaps there is logic to de-allocate before beginning another operation requiring? Or do server processes hold onto workarea memory forever here as well? I'm prepared to accept P_A_T as the best thing since LMT, but so far I don't see it. At least not for all circumstances (as with LMT). I see it as a good thing in memory-constrained environments, but in environments with plenty of RAM I see it so far as a possible source of unnecessary instability with no upside. Thanks! -Tim on 9/29/03 5:10 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, There are couple of parts of the conversation we've missed out ;) Firstly, the server process when talking to the P_A_T instance should have said, What the hell is going on here, what do you mean I can't have my full 100M, this keeps on happening and it's just good enough. Get a bloody DBA to increase the P_A_T now because it's bloody obvious that the damn thing is set too low . (especially if the load you describe is typical
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, There are couple of parts of the conversation we've missed out ;) Firstly, the server process when talking to the P_A_T instance should have said, What the hell is going on here, what do you mean I can't have my full 100M, this keeps on happening and it's just good enough. Get a bloody DBA to increase the P_A_T now because it's bloody obvious that the damn thing is set too low . (especially if the load you describe is typical). Secondly, the server process when talking to the non P_A_T should have said upon receiving the memory, ha, thanks, and guess what, no one else can have this memory back until I decide to rack off, and no I don't care if you're running short of memory, bugger ya, page for all I care These are very important parts of the conversion !! At the site I currently work at, we had 12G of memory which at peak load was just about running out. We have 1000-1200 sessions with (generally) only a small number active at a time but the sum of the PGAs was considerable and the major contributor. We had a number of disk sorts occurring although the SAS kept the number within acceptable limits. After setting the P_A_T, we now have a comfortable buffer of free memory (generally sitting around 1G), disk sorts have disappeared entirely (in four months, we've had 2 disk sorts) and hash joins have improved considerably. Based on my experience, P_A_T is the best thing Oracle has introduced since LMT !! Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 29, 2003 6:59 AM Referencing the article mentioned in this thread, I'd also like to understand exactly what is meant by the phrase [PGA_AGGREGATE_TARGET] leads to a more efficient use of RAM memory? From what I've been able to determine about this functionality, efficient merely means space-efficient, not performance-efficient (i.e. Fewer cycles? Smarter cycles?). Is this correct? Does anyone know of anything in WORKAREA_SIZE_POLICY=AUTO which improves performance over WORKAREA_SIZE_POLICY=MANUAL? Please correct me if I'm wrong, but I think the algorithm for WORKAREA_SIZE_POLICY=AUTO can be characterized something like: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever? [instance]: OK, what do you need? [server process]: Um, I'd like 100Mb, please? [instance]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see that 150 other server processes are using 1.2Gb at the moment... [another server process]: I'm done sorting! I've released the 100Mb I was using! Thanks... [instance]: OK, so now it is 149 other server processes using 1.19Gb at the moment. So, you wanted 100Mb? Well, since the amount in use is over 50% of the target, I have to scale your request back by 25%, so I'll let you take 75Mb [server process]: Well, OK. My execution plan was originally devised under the assumption that I'd have 100Mb of sort space in memory, but... [instance]: Hey pal! Take it or leave it! Someone else just took 75Mb, so if you think about it much longer, the total amount in use might grow and then I might only be able to give you 50% of your request! [server process]: OK! OK! I'll take it. (goes off and sadly mallocs only 75Mb of sort space in private memory)... Whereas in WORKAREA_SIZE_POLICY=MANUAL, it goes: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever. I'd like 100Mb, so that's what I'll allocate... I mean, other than anthropomorphizing the whole thing, is this the general gist of it? Obviously, since the instance isn't a process and I'm not aware of another background process dedicated to this kind of thing, I'd say that it is a tally kept someplace in the SGA that is latched and updated by each server process in kind, but I thought the idea of a dialogue more amusing... :-) If this is the case, then if I have a server which is not constrained for memory, then why should I be concerned about space-efficiency? I tend to visit 2-3 different companies/organizations per week on a regular basis, and while I do find plenty of under-sized servers laboring under over-sized Oracle instances, I just as often find over-sized servers with acres of RAM, in which I'm certain entire DIMMs have never felt a volt of electricity. Typical example is a customer I started at two weeks ago, with 12 CPUs and 24 Gb of RAM and 30Gb of swap space, whose database instance is demanding about 4 Gb of virtual memory, primarily due to PGA_AGGREGATE_TARGET being set to 1.5Gb. There's typically 20Gb of untouched RAM on this thing! Customer is being hosted by Oracle Apps hosting company and I
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, There are couple of parts of the conversation we've missed out ;) Firstly, the server process when talking to the P_A_T instance should have said, What the hell is going on here, what do you mean I can't have my full 100M, this keeps on happening and it's just good enough. Get a bloody DBA to increase the P_A_T now because it's bloody obvious that the damn thing is set too low . (especially if the load you describe is typical). Secondly, the server process when talking to the non P_A_T should have said upon receiving the memory, ha, thanks, and guess what, no one else can have this memory back until I decide to rack off, and no I don't care if you're running short of memory, bugger ya, page for all I care These are very important parts of the conversion !! At the site I currently work at, we had 12G of memory which at peak load was just about running out. We have 1000-1200 sessions with (generally) only a small number active at a time but the sum of the PGAs was considerable and the major contributor. We had a number of disk sorts occurring although the SAS kept the number within acceptable limits. After setting the P_A_T, we now have a comfortable buffer of free memory (generally sitting around 1G), disk sorts have disappeared entirely (in four months, we've had 2 disk sorts) and hash joins have improved considerably. Based on my experience, P_A_T is the best thing Oracle has introduced since LMT !! Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 29, 2003 6:59 AM Referencing the article mentioned in this thread, I'd also like to understand exactly what is meant by the phrase [PGA_AGGREGATE_TARGET] leads to a more efficient use of RAM memory? From what I've been able to determine about this functionality, efficient merely means space-efficient, not performance-efficient (i.e. Fewer cycles? Smarter cycles?). Is this correct? Does anyone know of anything in WORKAREA_SIZE_POLICY=AUTO which improves performance over WORKAREA_SIZE_POLICY=MANUAL? Please correct me if I'm wrong, but I think the algorithm for WORKAREA_SIZE_POLICY=AUTO can be characterized something like: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever? [instance]: OK, what do you need? [server process]: Um, I'd like 100Mb, please? [instance]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see that 150 other server processes are using 1.2Gb at the moment... [another server process]: I'm done sorting! I've released the 100Mb I was using! Thanks... [instance]: OK, so now it is 149 other server processes using 1.19Gb at the moment. So, you wanted 100Mb? Well, since the amount in use is over 50% of the target, I have to scale your request back by 25%, so I'll let you take 75Mb [server process]: Well, OK. My execution plan was originally devised under the assumption that I'd have 100Mb of sort space in memory, but... [instance]: Hey pal! Take it or leave it! Someone else just took 75Mb, so if you think about it much longer, the total amount in use might grow and then I might only be able to give you 50% of your request! [server process]: OK! OK! I'll take it. (goes off and sadly mallocs only 75Mb of sort space in private memory)... Whereas in WORKAREA_SIZE_POLICY=MANUAL, it goes: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever. I'd like 100Mb, so that's what I'll allocate... I mean, other than anthropomorphizing the whole thing, is this the general gist of it? Obviously, since the instance isn't a process and I'm not aware of another background process dedicated to this kind of thing, I'd say that it is a tally kept someplace in the SGA that is latched and updated by each server process in kind, but I thought the idea of a dialogue more amusing... :-) If this is the case, then if I have a server which is not constrained for memory, then why should I be concerned about space-efficiency? I tend to visit 2-3 different companies/organizations per week on a regular basis, and while I do find plenty of under-sized servers laboring under over-sized Oracle instances, I just as often find over-sized servers with acres of RAM, in which I'm certain entire DIMMs have never felt a volt of electricity. Typical example is a customer I started at two weeks ago, with 12 CPUs and 24 Gb of RAM and 30Gb of swap space, whose database instance is demanding about 4 Gb of virtual memory, primarily due to PGA_AGGREGATE_TARGET being set to 1.5Gb. There's typically 20Gb of untouched RAM on this thing! Customer is being hosted by Oracle Apps hosting company and I
Re: SEGMENT SPACE MANAGEMENT AUTO hangs on 9.2.0.4 on Linux
Title: Message Hi Mladen, I can't help you with your problem, I haven't had the pleasure on NT or Tru64 but I just wanted to point out that you can't forget about PCTFREE even with ASSM. Cheers Richard - Original Message - From: Mladen Gogala To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 30, 2003 12:44 AM Subject: SEGMENT SPACE MANAGEMENT AUTO hangs on 9.2.0.4 on Linux I have RDBMS 9.2.0.4 on RH 7.3 and Iexecuted the following command: create tablespace wizard datafile '/oradata/WIZ/wizard01.dbf' size 3072M reuse autoextend on next 1024M maxsize 16385m extent management local autoallocate segment space management auto; The whole system just hung, doing I/O like crazy. I was unable to killl one of the server processes which survived even shutdown abort, so I had to bounce thw whole box. No errors, no traces, no anything. Does anybody else have experience with this? Is there a known bug (not currently known to me) with a patch that I can install? I'd really like to use "SEGMENT SPACE MANAGEMENT AUTO" and forget about pctfree/pctused stuff. --Mladen GogalaOracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
Re: SEGMENT SPACE MANAGEMENT AUTO hangs on 9.2.0.4 on Linux
Title: Message Hi Mladen, Because when you insert a row, Oracle has absolutely no idea by how much it might grow (should the insertion of that 18th row use up the remaining space in the block or should we save some, who's to know ...). Therefore the setting of some magic PCTFREE is not supported. Forget about PCTUSED, FREELISTS and FREELIST GROUPS but ASSM doesn't support the forgetting of PCTFREE. Cheers Richard - Original Message - From: Mladen Gogala To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 30, 2003 2:09 AM Subject: RE: SEGMENT SPACE MANAGEMENT AUTO hangs on 9.2.0.4 on Linux And why not? Forgetting about PCTFREE/PCTUSED is the main point of automatic segment space management. Initial/next are resolved by using LMT, because that's what takes care of your extent sizes. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard FooteSent: Monday, September 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LSubject: Re: SEGMENT SPACE MANAGEMENT AUTO hangs on 9.2.0.4 on Linux Hi Mladen, I can't help you with your problem, I haven't had the pleasure on NT or Tru64 but I just wanted to point out that you can't forget about PCTFREE even with ASSM. Cheers Richard - Original Message - From: Mladen Gogala To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 30, 2003 12:44 AM Subject: SEGMENT SPACE MANAGEMENT AUTO hangs on 9.2.0.4 on Linux I have RDBMS 9.2.0.4 on RH 7.3 and Iexecuted the following command: create tablespace wizard datafile '/oradata/WIZ/wizard01.dbf' size 3072M reuse autoextend on next 1024M maxsize 16385m extent management local autoallocate segment space management auto; The whole system just hung, doing I/O like crazy. I was unable to killl one of the server processes which survived even shutdown abort, so I had to bounce thw whole box. No errors, no traces, no anything. Does anybody else have experience with this? Is there a known bug (not currently known to me) with a patch that I can install? I'd really like to use "SEGMENT SPACE MANAGEMENT AUTO" and forget about pctfree/pctused stuff. --Mladen GogalaOracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
Re: Re: system tablespace at 50 pct_increase in 9i?
Hi Mladen, Just to avoid any confusion, you have the *option* to create a LM System tablespace, the *default* is still DM. The ODCA uses the extent management local clause in it's default scripts but to create a database manually, you need to remember the clause. Whether it should use ASSM is somewhat more debatable... Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 18, 2003 11:14 PM Actually, from 9iR2, system tablespace is created as locally managed autoallocate. They should have put in SEGMENT MANAGEMENT AUTO clause as well, but hey, you can't always get what you want, but you can try sometimes. On 2003.08.18 07:59, [EMAIL PROTECTED] wrote: i thought you should leave the system table space to the defaults? Ive never touched System. you really should change system to locally managed tablespaces? From: Tim Gorman [EMAIL PROTECTED] Date: 2003/08/17 Sun PM 11:19:23 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: system tablespace at 50 pct_increase in 9i? Better yet, use locally-managed SYSTEM tablespace and dispense with the issue altogether? on 8/17/03 5:39 PM, Ryan at [EMAIL PROTECTED] wrote: any idea why oracle has the system tablespace using 50 pct_increase in 9i? I k now it did that in the past, but why not set it to zero? Ryan -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Richard Foote 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: Is there any certification for Systems Analyst/Designer ?
I would have said fortunately ... ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 1:04 AM Unfortunately not anymore. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Is there any certification for Systems Analyst/Designer ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Richard Foote 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: STATSPACK in Oracle 8.1.6.0
Hi Scott, Although the recommendation of RTFM often has it's merits, where precisely do you suggest Seema should look for the above mentioned FM within the *8.1.6* doco set for the sp* statspack scripts ? Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 18, 2003 5:59 AM RTFM, which will point you to the sp*.sql scripts in the ?/rdbms/admin directory. Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Seema Singh [SMTP:[EMAIL PROTECTED] Sent: Thursday, July 17, 2003 2:49 PM To: Multiple recipients of list ORACLE-L Subject: STATSPACK in Oracle 8.1.6.0 Hi, I want to delete all old records of statspack from perfstat schema. What would be best way to do this either by truncating all tables or remove and recreate ? Pl advice. thx -Seema _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: 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: Richard Foote 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: DBMS_REPAIR Package
Title: Message Hi Stefick, Running stored procedures/packages is somewhat difficult with a shutdown database, so the database needs to be opened. Depending on the type of corrupted block in question answers whether or not the block is actually repaired (eg. bitmap block in ASSM segments, freelist blocks...) or simply marked as corrupt and thus could be made "skippable" meaning that your FTS will now work and skip the stuffed buggers. That said, I would recommend restoring you stuffed datafile, perform a database recovery and hope the corruption wasn't duplicated in your backup(s). dbms_repair should be used if all else fails (or you don't really mind losing that "bit" of your database). Good Luck Richard Foote - Original Message - From: Stefick Ronald S Contr ESC/HRIDD To: Multiple recipients of list ORACLE-L Sent: Thursday, July 17, 2003 11:54 PM Subject: DBMS_REPAIR Package Hasanyone ever used DBMS_REPAIR? If so, what do you think of it, does the DB have to be shutdown to run it. Does it really fix any corrupt blocks in the datafiles? We ran DBVerify andfoundseveral corrupt DB files in one of our DB's. TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED]210-565-2540
Re: Partitions of table read only
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 5:59 AM Jack, maybe this has been covered. I seem to recall from the BR module (knew it would prove useful sometime) that after you make a tablespace read-only that you should take a backup. Recovering a database with tablespaces that were read-write when backed up but are read-only now requires an extra step or two (something I never like in a recovery). Hi Dennis Backing up the tablespace files (and lets not forget the control file) is certainly not a bad idea. Something else that many don't consider is to select from all objects within the tablespace with full scans *before* making the tablespace read only. This has the effect of performing all the necessary block cleanouts (ie. for all the blocks written to disk before they could be committed and cleaned out in memory) while Oracle still can. If the tablespace is made read only and some poor blocks haven't been cleaned out, upon reading the block Oracle has no choice but to go to the rollback/undo segments in it's attempt to confirm the consistency of the block. However upon confirming that indeed the transaction is long gone and block cleanout can take place with the latest possible SCN, it can't actually perform the necessary block changes because, you guessed it, the tablespace is currently read only. This means that the overhead of checking for consistency but failing to actually perform the block cleanout continues on and on and on ... Hence the suggestion to guarantee block cleanout while the tablespace is in a position to do so (in read/write mode). Cheers Richard Foote -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Tablespace management.
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, June 02, 2003 9:54 PM Cannot create lob columns if segment space management is set to auto. Why not ? SQL create tablespace new datafile 'c:\bowie\new01.dbf' reuse 2 segment space management auto; Tablespace created. SQL create table bowie2 (x clob) lob (x) store as (disable storage in row) tablespace new; Table created. Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Tablespace management.
-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: Richard Foote 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: Need to Log on 2000 users
As well as using orastack, go a few steps further and tune the SGA to buggery (make it lean but keen) and set as high a pga_aggregate_target as possible and you might make it (depending on what the 2000 users are doing and depending on how many of them are doing what they're doing concurrently). As previously suggested, shared servers could be a goer but if dedicated is a must, consider the above. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 12:54 AM Jeremiah, Where do you get 128Gb? For 2000 users that is ~65M per user, which seems like an excessive estimate. While I probably wouldn't want to run 2k users on a single Windows server, I think you could do it for test purposes. Use orastack to reduce the memory per thread to 500k, set small sort_area_size, etc. Don't see why not. Jared On Friday 30 May 2003 02:14, Jeremiah Wilton wrote: You mean 2000 concurrent sessions? Why do you need to use dedicated server? Normally, you would accomplish this with Shared Server. You will need 128Gb of memory for the PGAs alone. Or you can use swap, but get ready to wait. Even that will probably be so slow that the connections may time out, or background thread IPC will time out, bringing the instance down. This seems like a silly exercise. Whose idea is it? Good luck with all that -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 29 May 2003, Munish Bajaj wrote: Hi Gurus, I am facing a problem. I need to log on 2000 users to my database via dedicated server connection on Oracle 9iR2 running on Windows 2000 Advanced server. Please guide me as to what all parameters need to be tuned to achieve the same. The Server is a single CPU server with 3G RAM. I need just to logon 2000 users. This is a load test that I need to perform. Thanks to all Regards Munish Bajaj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Richard Foote 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: Which method is more efficient
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 3:14 AM 1. to totally eliminate redo, load your staging records into a global temp table. it has absolutely no redo and is very fast. Hi The above is not quite true. Global temp tables *do* generate redo (albeit indirectly) and potentially quite a bit of it. The point to make here is that changes to GTT generate undo and this undo makes changes to undo segments and these changes subsequently generate redo. So depending on the type of DML (eg. deletes) and the volume of changes, you can end up generating quite a bit of the redo. Not as much as a non-GTT but enough to certainly invalidate the above statement. Cheers Richard Foote -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Tablespace management.
Hi Jared, I agree. By placing different segments in the same LMT doesn't mean that they are sharing an equal need for storage. One segment can grab 5 extents, another can grab 100, they grab as much storage as they require. And without causing fragmentation, without causing stress or contention on the DD My idea of the optimal number of extents for a segment is the number of extents you *plan* for the segment. You meet that criteria and within reason you can't go too far wrong. If were possible to set a maxextents for segments in a LMT, I would struggle to find disadvantages in comparison. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:24 AM Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Goulet, Dick 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: 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: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: skip scan index
Hi Rachel, Correct, Skip Scan Index is not a type of index but a method whereby Oracle can eliminate the need to visit leaf nodes by determining whether the leading column(s) have changed by sussing out only the branch nodes. It's possibly useful in situations where previously Oracle would not consider a concatenated index if the leading column of the index is unknown whereas now the optimizer might determine that sufficient leaf nodes can be avoided for the index to be of benefit. It's a kinda improved version of the full index scan (or not so full if you know what I mean), However this requires the leading column to have *low* cardinality, low enough for the same repeated column from one leaf node to extent across all values of it's neighbouring leaf node. If the leading column changes from one leaf node to the next, then that leaf node must be at least visited (although subsequent inspection of the index values may enable Oracle to pull out early from having to read all index values, if a subsequent change in the leading column rules out all remaining entries). A quick (and nasty) formula would be to consider the ratio of leaf nodes to distinct values (LN/DV). The higher the ratio the better with any value somewhat greater than 1 giving a skip scan index path a chance with the number representing an approximate number of leaf nodes that could be skipped per leading index value. This obviously assumes evenish distribution of leading column(s) index values. However, getting back to your actual situation, if table access is only to be made via the order date or by order date and order id (and not necessarily by order id only), then you may find a single index order date || order id would meet all your requirements. Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 8:59 PM Okay, I have a developer here who has been reading the docs (this can be dangerous!) we are adding functionality to one of our applications, this will involve using multiple fulfillment houses, so we'll be adding the fulfillment vendor id to the order table. Easy, this is not a problem. We want to be able to search by order date and by fulfillment vendor id/order date Traditional design would be to add two indexes: one on order date, and a concatenated one on fulfillment vendor id/order date. The developer is telling me to create a skip scan index instead of two different ones. MY reading in the FM tells me that skip scan index is not a type of index, but rather a way Oracle uses to use an index even if the leftmost column is not in the query. Is there any benefit in my building only the one index? Our order volume is not so high (and never will be) that there is a visible performance impact if I have the two indices. This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. Solaris Any suggestions/comments/war stories would be appreciated. I know I've seen Jonathan post on skip scan indexes before but I can't find the specific reference at the moment. Rachel __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Richard Foote 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: skip scan index
Hi Rachel, Correct, Skip Scan Index is not a type of index but a method whereby Oracle can eliminate the need to visit leaf nodes by determining whether the leading column(s) have changed by sussing out only the branch nodes. It's possibly useful in situations where previously Oracle would not consider a concatenated index if the leading column of the index is unknown whereas now the optimizer might determine that sufficient leaf nodes can be avoided for the index to be of benefit. It's a kinda improved version of the full index scan (or not so full if you know what I mean), However this requires the leading column to have *low* cardinality, low enough for the same repeated column from one leaf node to extent across all values of it's neighbouring leaf node. If the leading column changes from one leaf node to the next, then that leaf node must be at least visited (although subsequent inspection of the index values may enable Oracle to pull out early from having to read all index values, if a subsequent change in the leading column rules out all remaining entries). A quick (and nasty) formula would be to consider the ratio of leaf nodes to distinct values (LN/DV). The higher the ratio the better with any value somewhat greater than 1 giving a skip scan index path a chance with the number representing an approximate number of leaf nodes that could be skipped per leading index value. This obviously assumes evenish distribution of leading column(s) index values. However, getting back to your actual situation, if table access is only to be made via the order date or by order date and order id (and not necessarily by order id only), then you may find a single index order date || order id would meet all your requirements. Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 8:59 PM Okay, I have a developer here who has been reading the docs (this can be dangerous!) we are adding functionality to one of our applications, this will involve using multiple fulfillment houses, so we'll be adding the fulfillment vendor id to the order table. Easy, this is not a problem. We want to be able to search by order date and by fulfillment vendor id/order date Traditional design would be to add two indexes: one on order date, and a concatenated one on fulfillment vendor id/order date. The developer is telling me to create a skip scan index instead of two different ones. MY reading in the FM tells me that skip scan index is not a type of index, but rather a way Oracle uses to use an index even if the leftmost column is not in the query. Is there any benefit in my building only the one index? Our order volume is not so high (and never will be) that there is a visible performance impact if I have the two indices. This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. Solaris Any suggestions/comments/war stories would be appreciated. I know I've seen Jonathan post on skip scan indexes before but I can't find the specific reference at the moment. Rachel __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Richard Foote 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: skip scan index
: http://www.orafaq.net -- Author: Richard Foote 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: skip scan index
Hi Vivek,
In my discussion I was referring to a concatenated index as in multi
columns, not concatenated as in one column with 2 concatenated values,
although I admit the use of || didn't help.
Sorry for the confusion ;(
Richard
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, May 29, 2003 3:59 PM
Hi Richard , List
Your E-mail order date or by order date and order id (and not
necessarily by order id only), then you may find a single index order date
|| order id would meet all your requirements.
Is there any advantage having the index defined as ( order date || order
id )
over ( order date , order id ) ?
SAMPLE TEST :-
SQL desc tmp1
Name Null?Type
- --
--
TRAN_DATE DATE
TRAN_IDVARCHAR2(10)
Index defined as ( TRAN_DATE || TRAN_ID ) is NOT used as shown in the
following query :-
SQL select * from tmp1 where tran_date=('01-01-2003');
Execution Plan
--
0 SELECT STATEMENT Optimizer=RULE
10 TABLE ACCESS (FULL) OF 'TMP1'
SQL select * from tmp1 where tran_date=('01-01-2003') and tran_id='D1';
Execution Plan
--
0 SELECT STATEMENT Optimizer=RULE
10 TABLE ACCESS (FULL) OF 'TMP1'
Thanks
-Original Message-
Sent: Wednesday, May 28, 2003 7:50 PM
To: Multiple recipients of list ORACLE-L
Hi Rachel,
Correct, Skip Scan Index is not a type of index but a method whereby
Oracle can eliminate the need to visit leaf nodes by determining whether
the
leading column(s) have changed by sussing out only the branch nodes. It's
possibly useful in situations where previously Oracle would not consider a
concatenated index if the leading column of the index is unknown whereas
now
the optimizer might determine that sufficient leaf nodes can be avoided
for
the index to be of benefit. It's a kinda improved version of the full
index
scan (or not so full if you know what I mean),
However this requires the leading column to have *low* cardinality, low
enough for the same repeated column from one leaf node to extent across
all
values of it's neighbouring leaf node. If the leading column changes from
one leaf node to the next, then that leaf node must be at least visited
(although subsequent inspection of the index values may enable Oracle to
pull out early from having to read all index values, if a subsequent
change in the leading column rules out all remaining entries).
A quick (and nasty) formula would be to consider the ratio of leaf nodes
to
distinct values (LN/DV). The higher the ratio the better with any value
somewhat greater than 1 giving a skip scan index path a chance with the
number representing an approximate number of leaf nodes that could be
skipped per leading index value. This obviously assumes evenish
distribution of leading column(s) index values.
However, getting back to your actual situation, if table access is only to
be made via the order date or by order date and order id (and not
necessarily by order id only), then you may find a single index order date
|| order id would meet all your requirements.
Cheers
Richard Foote
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 8:59 PM
Okay, I have a developer here who has been reading the docs (this can
be dangerous!)
we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date
Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.
The developer is telling me to create a skip scan index instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.
Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.
This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris
Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.
Rachel
__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
Please
Re: RE: Autoallocate vs Uniform extent performance
Hi Jonathan, OK let's give it a go (note that I'm now doing this at home on my not quite so powerful PC with Best of Bowie playing on the CD so it's just me having a play) SQL create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 3000m 3 uniform size 24k; Tablespace created. SQL set timing on SQL create table lots_of_extents (x number) 2 storage (initial 1450m) 3 tablespace biggish; Table created. Elapsed: 00:01:48.02 SQL select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) -- 61887 Elapsed: 00:00:08.01 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:16.09 SQL create table lots_of_extents1 (x number) 2 tablespace biggish; Table created. Elapsed: 00:00:00.01 SQL create table lots_of_extents2 (x number) 2 tablespace biggish; Table created. Elapsed: 00:00:00.00 SQL begin 2for i in 1..61886 loop 3 execute immediate 'alter table lots_of_extents1 allocate extent'; 4 execute immediate 'alter table lots_of_extents2 allocate extent'; 5end loop; 6 end; 7 / PL/SQL procedure successfully completed. Elapsed: 00:19:29.07 SQL select count(*) from user_extents where segment_name='LOTS_OF_EXTENTS1'; COUNT(*) -- 61887 Elapsed: 00:00:04.09 SQL select count(*) from user_extents where segment_name='LOTS_OF_EXTENTS2'; COUNT(*) -- 61887 Elapsed: 00:00:04.08 SQL drop table lots_of_extents1; Table dropped. Elapsed: 00:00:06.05 SQL drop table lots_of_extents2; Table dropped. Elapsed: 00:00:05.07 It's actually somewhat faster but in case caching and the such has had an effect ... SQL create table lots_of_extents (x number) 2 storage (initial 1450m) 3 tablespace biggish; Table created. Elapsed: 00:01:13.00 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:05.06 So it's all about the same I have a little experiment in mind that could cause me to reconsider heaps of extents but it might have to wait a day or two. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, April 05, 2003 5:04 AM I think you ought to refine your test: Create two tables at one extent each, then alternately allocate one extent to each table until you get to a very large number of extents. THEN try dropping one of them. Remember to set tablespace quotas for the user creating the table. It still won't scare you off, by the way, so you don't have to do it. Check with Connor which version of Oracle introduced the modification that updates tsq$ just once one the drop, rather than once per extent as this does make a difference. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html UK___April 8th UK___April 22nd Denmark__May 21-23rd USA_(FL)_May 2nd Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK_(Manchester)_May Estonia___June (provisional) 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] Sent: 04 April 2003 09:23 Hi Pete, Stop using my favourite answer :) I'm not suggesting this is particularly scientific but here's a quick test on dropping a moderate number of extents (9.2 on XP): SQL create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 2000M 3 uniform size 16K; Tablespace created. SQL set timing on SQL create table lots_of_extents (x number) 2 storage (initial 1990M) 3 tablespace biggish; Table created. Elapsed: 00:00:49.06 SQL select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) -- 127423 Elapsed: 00:00:04.01 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:06.08 Based on the above, I could drop a table with 1,000,000 extents in under 1 minute. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote
Re: RE: Autoallocate vs Uniform extent performance
, 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: Richard Foote 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: Pete Sharman 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: Richard Foote 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: Interesting lesson on ARCHIVELOG mode
Hi James, Hopefully the other lesson you've learnt is the importance of training, even for Junior DBAs so that such fundamentally basic but potentially costly mistakes can be avoided. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:58 PM Some of you on the list might find this interesting. I just wanted to relate a story with respect to an incident experienced in the last few days on one of our test databases. Environment is Compaq Tru64 Unix / Oracle 8.1.7.4. A few days ago, I remember talking to a junior DBA who assists me in the Oracle area, concerning excessive space usage on one of the Unix machines running a test database environment. I noted that the database was running in ARCHIVELOG mode with automatic archiving (of course), and generating a great many archived logs since there was considerable activity on that instance/database. We discussed the matter and agreed that there was no need to have ARCHIVELOG mode turned on in this case. So I told my assistant DBA to go ahead and make the database NOARCHIVELOG, which I thought she understood. Yesterday, she comes to me with a host of problems she has been experiencing on that test database, one of which was many failed attempts to import a 2 Million row table from another database's export. It seemed that the import would just hang after importing about 130,000 rows. She repeatedly cancelled the import, resorted to cycling the database, creating a another table with just a subset of the columns of the original, limiting the number of rows imported at one time, fooling with the buffer parameters of the import control file, trying SQL*LOADER, and so on. Quite frustrated, she came to me for advice. I had forgotten about the ARCHIVELOG mode issue a few days earlier, so I began scratching my head as I looked unsuccessfully for signs of trouble in alert logs and traces. I thought maybe a rollback segment had run out of room, lost its brains, or maybe temp space had become a problem. But again, no sign of any of these issues in alerts or traces. Suspecting database corruption, I took a full export to see if export would report any corrupted blocks. That worked flawlessly. I began to wonder if we should just start from scratch and recreate the database. Then something interesting became apparant. Looking at V$DATABASE, I noticed that the database was still in ARCHIVELOG mode! When I asked about this, it seems that she thought that simply commenting out the init.ora parameters: log_archive_start=true log_archive_dest=whatever log_archive_format=whatever and then recycling the database would take care of the whole issue of ARCHIVELOG mode, making the database become NOARCHIVELOG mode. Well, guess what.it didn't. The lesson learned was that with the database still in ARCHIVELOG mode and automatic archiving turned off, obviously enough DML would cause the database to hang whenever it did a log switch, awaiting us DBAs to manually archive the filled redo logs. Realizing this, of course we then did the prudent thing: alter database noarchivelog and lived happily ever after. Had I continued to assume database corruption and just had her recreate the database, it WOULD have indeed solved the problem BUT ONLY because the database would have come up in NOARCHIVELOG mode. However, it certainly would have bothered me as to why the database had become corrupted in the first place. I am very happy to know what actually happened, that the database wasn't corrupted at all. It was just someone's misunderstanding in not realizing that ARCHIVELOG mode and automatic archiving are two related but totally different things! Jim Damiano -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Damiano 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: Richard Foote 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
Re: oracle full table scan
Hi Arvind, A little test for you. You have a table that contains 10,000,000 rows that is packed tightly into 1,000,000 data blocks. You have an index that has a level of 4 and has 10,000 leaf blocks. The table is well striped across a number of devices and you have 4 CPUs on the box. You write a simple select statement that queries the table based on the indexed column and *just 10%* of the data needs to be retrieved. You determine that the CBO has performed a full table scan. Do you break out into a nervous sweat or do you sigh thank goodness and worry about something else instead ? Cheers Richard (let me know if you want to know the comparative costs ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: Richard Foote 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: Autoallocate vs Uniform extent performance
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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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). = __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha 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: Steve Perry 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: Richard Foote 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: PGA
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 01, 2003 4:03 PM where can one find the other myths about oracle? Venkat -- A number of Oracle Press books ... Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: OT- Start a process after oracle on Win 2K
Hi Peter, Sounds like a job for an after startup database event trigger (check out CREATE TRIGGER doco). Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 01, 2003 10:28 PM Hi Slightly OT I have a couple of programs that need to be run after Oracle has started and want to run them without a user logging in. The likely place seems to be in the scheduled tasks running at startup or as a program under the local run key in the registry. The processes are a couple of scripts and I would envisage running them as a batch file What is best? Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Richard Foote 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: index on null column
Hi AK, It depends ... If you frequently perform searches for not null values on column A and the cardinality of values is such that the optimizer is turned on by the index, then yes, potentially an index could be useful. Also, with so many nulls, such an index would be relatively small. Cheers Richard - Original Message - Date: Friday, March 28, 2003 10:18 am I have a table with column A. there are 100 records in table out of which 500o records has not null in column A rest are null. if i created an index on A, will it benefit . thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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).
