This discussion is archived
1 2 3 4 Previous Next 49 Replies Latest reply: Jan 21, 2013 11:08 AM by 864103 Go to original post RSS
  • 15. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Thank you.

    I can not speak for Tom ... which is why I refer people repeatedly to his statements and websites ... but anyone that relies on a Burleson website or Rampant Press book needs to devote some time to comparing the advice they are receiving with what Oracle Corp. has published, what member of the Oak Table have written in their blogs, and what I and other ACE Directors are saying. The fact that google puts something at the top of its search results is not an endorsement of its quality.

    Seriously everyone in the community ... throw away NO LOGGING. If your system is so pathetic that not doing proper logging is going to tip it over the answer is not to do something foolish ... the answer is to invest some time into first determining the specific nature of the issue then if the answer is hardware go buy more RAM, faster CPUs, add an SSD drive or build an index, rewrite code, change a table structure ... whatever it takes to do it right. If you think NOLOGGING is the solution to the problem you may also be very sure that almost all of the most respected people in our industry would NOT want to hire you.

    One question I routinely ask during technical interviews is about NO LOGGING. Anyone that can't tell me why it is a really bad idea has almost no chance of being hired. It is right here:
    http://www.morganslibrary.org/reference/interviews.html
    in my list of interview questions on my website (DBA list #12).
  • 16. Re: nologging
    864103 Newbie
    Currently Being Moderated
    thanks for all , just when i go to google it and type increase the performance of insert query
    first thing tell me set your table to nologging so there is no data was generated in redo ....
  • 17. Re: nologging
    sb92075 Guru
    Currently Being Moderated
    861100 wrote:
    thanks for all , just when i go to google it and type increase the performance of insert query
    first thing tell me set your table to nologging so there is no data was generated in redo ....
    You can also find via GOOGLE that BUFFER CACHE HIT RATIO above 95% is needed for well tuned DB.

    Not everything posted to the WWW is true!
  • 18. Re: nologging
    864103 Newbie
    Currently Being Moderated
    thanks i will do it.
  • 19. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Once again ... the fact that something pops to the top of a google result set is not an endorsement of quality. If it was every time you typed the word "Oracle" into a search the first results returned would be something written by Tom Kyte, Jonathan Lewis, Christian Antognini, Tanel Poder, Alex Gorbachev, etc.

    Google is a great tool but it only knows what is popular ... it doesn't know what is correct. Let me give you an example ... years ago when I was with the Puget Sound Oracle Users Group and maintained psoug.org the code quality was good. Since I have been gone the code examples are obsolete. Do you think google cares? Do you think google knows the difference? You don't search for tuning by writing:
    "Oracle Database Tuning"
    you search for tuning by writing:
    "Oracle Tuning + Christian Antognini"
    "Oracle Tuning + Tanel Poder"
    "Oracle Indexes + Richard Foote"
    the first search gets you Lindsay Lohan ... the second gets you something of quality.
  • 20. Re: nologging
    Dizwell Newbie
    Currently Being Moderated
    "...with what Oracle Corp. has published, what member of the Oak Table have written in their blogs, and what I and other ACE Directors are saying ... throw away NO LOGGING"

    Just passing, but allow me to disagree, Daniel. (And I think I have the chops to do so meaningfully, as both a former Oakie and ACE).

    I can recall a case in the mid-2000s of a batch load that was routinely taking nearly 48 hours to complete with masses of redo-related waits; I suggested (with suitable dire warnings) that they time it with disablelogging=TRUE set; load time came down to about 7 hours (from memory). Turned out that they could shut the database down, perform cold backup, disable logging, restart, do the load, shutdown, perform new cold backup, restart in archivelog mode... all in less than the time needed to do the original batch load. They decided to incorporate that in their procedures, safe in the knowledge that if anything went wrong during the disabled logging period, they could revert to the prior cold backup, no harm done.

    Now that's disablelogging, which is a lot more dangerous than mere nologging. I certainly wouldn't recommend it generally. But I'd be prepared to recommend it again if the circumstances were right.

    So I can't accept that 'no-one should do nologging' is good advice. Like any blanket rule-of-thumb, it will be the right advice sometimes, and the wrong advice sometimes.

    The key is to know what advice ought to apply in what circumstances. That requires skill and experience, of course. Which the Oakies and ACEs are likely to have in spades, so their writings and comments should certainly be taken seriously. But that still doesn't make their writings Gospel, unless you believe in 'appeals to authority' (which the Oakies at least are unlikely to approve of, I think).

    Regards
    HJR
  • 21. Re: nologging
    rp0428 Guru
    Currently Being Moderated
    >
    So I can't accept that 'no-one should do nologging' is good advice. Like any blanket rule-of-thumb, it will be the right advice sometimes, and the wrong advice sometimes.
    >
    I agree. The use of NOLOGGING is indispensable when doing very large TRUNCATE-AND-LOAD ETL operations. There is virtually no risk with these type of processes because, by definition, the source data is available to redo the operation.

    One key distinction with these operations is that the tables involved are NEVER accessible to end-users during the operation or until a suitable export/backup of the tables involved has occured.

    In other words NOLOGGING is only used in 'batch' processes when there is no need for immediate recovery when problems arise.
  • 22. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Welcome back to the world Howard. All those fires in Australia ... perhaps you are the Phoenix. ;-)

    Of course I am not making an appeal to authority. Neither would I advise anyone to say "I know more than Jonathan, Tanel, Alex, and Tom" unless it is about a subject like knitting sweaters. As with everything in Oracle there are always exceptions but you will, if you are rational, acknowledge that exceptions are, like the Phoenix, very rare birds.

    For every example where disabling logging is a good idea ... there are 10,000 DBA/Developers trying the quick fix because they don't want to invest the time in figuring out why something is too slow. I've no doubt you made things faster by doing what you did. But I would be willing to bet any number of us could have achieved the same without putting the system at risk.
  • 23. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Another way of saying that is "systems that can tolerate very substantial outages." I haven't seen one of those in a very long time.

    Consider what Howard wrote in this context. His customer's system would, of necessity, be offline 7 hours every time it was loaded, and if anything went wrong be down no fewer than 14 hours. I can't think of many that would tolerate that. I know my clients wouldn't.
  • 24. Re: nologging
    Dizwell Newbie
    Currently Being Moderated
    I don't think it would be that rare, actually. I think you might be living in an OLTP world, but a lot of what applies there would not be true for different types of system (warehouses, etc).

    In the particular case I cited, their hardware was the fundamental limiter, so getting the database to stop generating redo altogether was a rational, quick fix. The correct long-term fix was well-identified (buy more and faster hard disks to improve the performance of the redo physical disk subsystem), but would have required stacks of cash and a long procurement process to have resolved. Sometimes, quick and dirty is what's required. And I explained why it didn't put the system at risk at all.

    Anyway, that was a long time ago, and all I really wanted to say was: you generalised too far. And it certainly looked like 'appeal to authority'.
  • 25. Re: nologging
    jgarry Guru
    Currently Being Moderated
    I run my test databases noarchivelog. Why? Because they are 10x bigger than my production, and much more unpredictable in redo generation. Yes, test is someone's production (including users). But as has been intimated, the question is one of defining recoverability. All the test databases only require a restore or refresh, explicitly. Archiving is the exception, only when explicitly needed.

    As in Howard's example, I still do big upgrades where it is simply more sensible to take out of archivelog and do appropriate backups at restartable points rather than wait for archiving that will never be part of a recovery.

    I also have some small XE production databases that have their data pushed to them, refresh rate is much more often than a normal backup schedule. No purpose at all for archiving redo.

    Spaghetti Lord save us from helpful consultants who change things without telling us.
  • 26. Re: nologging
    marksmithusa Journeyer
    Currently Being Moderated
    damorgan wrote:

    For every example where disabling logging is a good idea ... there are 10,000 DBA/Developers trying the quick fix because they don't want to invest the time in figuring out why something is too slow.
    Excellent comment. There is, of course, always the exception. However, I believe OP is guilty of performing a web search and picking up the top result and using that as an explanation for his query's performance/redo log generation. I think this particular post belongs to part of the 10,000, to be fair.

    Not wanting to interrupt a discussion between the great DBA minds or anything, but...

    Aren't (or won't they been soon) a significant portion of mission-critical databases Data Guarded? Certainly, that's mostly true in our world. In which case, you don't get a choice whether you do NOLOGGING or not.

    In our particular case, we only allow NOLOGGING under exceptional conditions (as mentioned earlier): you're processing a very large data load which you can re-run in its entirety if needed into a database which isn't a system of record. In one notable example, the developers decided it would be better to drop the indexes, perform the load and then rebuild the indexes once the load is complete (it was an Exadata machine). This happened to dovetailed nicely with a managerial requirement to Data Guard the database and the need for FORCE LOGGING soon afterwards.

    I've even enabled FORCE LOGGING for the non-Production databases that I'm responsible for: just so that's not an alley down which a developer can go if they're having performance issues prior to Production (but it worked great in test where we don't have FORCE LOGGING on...)
  • 27. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Sorry Howard but on this we are never going to agree. You just stated precisely what I wrote in my original post " their hardware was the fundamental limiter" so the solution was not to put the system at risk ... the system was to purchase the correct hardware. Thank you. Now would they have needed to purchase a brand new server and SAN? Or would SSD address the issue? Could they have loaded, in parallel, onto separate servers running Express Edition and then transported the tablespaces with DataPump? There are many work-arounds available.

    I hate to repeat myself but putting a production system at should never been anything other than a last resort.

    PS: My customers with DSS and DW systems also would not tolerate a 7+ hour outage. Perhaps your customers are more tolerant.
  • 28. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    How can a "test" system be someone else's production? A system either is a line-of-business system or it is not.

    The bottom line on any discussion is a risk-reward determination. What risk is the customer willing to accept to compromise on an optimum configuration.

    If your customer is willing to give you three hours, three days, three week, three months to recover a system and restore it to operational capacity then perhaps NOARCHIVE, NOLOGGING, and No RMAN can be justified.

    But to post advice in these forums based on these unusual system requirements may well lead people astray who's employers purchased the Oracle database because they want robust sytems, systems that are operational 7x24x365, systems that are their to meet business needs on demand.

    I don't even run my laptop in NOARCHIVE mode. I'm not even running the 12c Beta in NOARCHIVE mode. And I can not believe that you can truly justify doing so in your environment. I would be very interested in hearing the business arguments that appear to make this a reasonable choice juxtaposed against the cost of purchasing sufficient SSDs to eliminate the risk.
  • 29. Re: nologging
    Dizwell Newbie
    Currently Being Moderated
    If all else fails, just ignore what I wrote, huh?

    There's nothing to agree or disagree about, Daniel: you're just plain wrong. Wrong to start waving the names of Oakies and Aces around. Wrong to blanket rule out nologging. Wrong to say the database I'm talking about was ever put at risk. Wrong to ignore the fact that not every database is an OLTP one. Wrong to assume that because you've never seen a 7-hour load window acceptable to a customer, no-one else ever has, either. Your over-generalise and over-simplify (so nothing much has changed in the past 10 years, then) in your pursuit of a pithy one-liner you can add to your tally of posts.

    I repeat: there are thousands of databases out there that are recoverable by means other than redo, and in those circumstances, nologging is a perfectly acceptable option and can provide benefits which might be desirable. If you can't get the nuance, don't worry about it. But there are nuances you are missing.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points