0

I have an Oracle 11.2.0.3 about 700GB in size running in noarchivelog mode in a pretty slow storage setup. It barely gives IOPS and there is resource crunch in terms of hardware resources. The transactions hitting the database is heavily read/write intensive and redo generation is quite high (near to 8MB/Sec) and naturally it is finding difficult to write to the disk.

What can be done in such a scenario? Can nologging in certain tables boost the performance? Data corruption because of nologging is a concern, I understand that and can be taken care of. But can someone give insight of how truly enabling nologging helps in this situation? or if it doesn't help, what are the other options I have?

2

Can nologging in certain tables boost the performance?

Not for a normal transactional load. nologging only affects some very specific cases of DML (essentially direct-path inserts) and some DDL (see logging_clause). So essentially only for bulk data-loading scenarios. If that's not what your database is doing, it won't help at all. If it is what your database does all day, then look at the most expensive statements and see if they can benefit from nologging.

Data corruption because of nologging is a concern

No it's not. Recoverability is. But since you're in noarchive mode, you don't really seem to care about that.

Can someone give insight of how truly enabling nologging helps in this situation?

As said above, it generally doesn't. It is certainly not something you should try and turn on everywhere for everything. Look at specific statements and objects. Test to make sure it does make a difference and doesn't break your application (direct-path operations have specific requirements in terms of when you need to commit in particular).

What are the other options I have?

You already know one: get hardware that can handle the load you need to process. Sounds dumb, but if you're generating 8Mb/s writes and your drives can only handle 7Mb/s, performance will suck. There's no magic.

The other is: tune your application. Poorly written code can generate way more activity than necessary on the database - sometimes massively. You need to look at what's hurting (with AWR or whatever other tools you use) and figure out if there are better ways of doing things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.