Monday, April 28, 2008

Tips for better performance and tuning in Oracle SQL and PL/SQL

1) FTS (Full Table Scans) are always bad and Index usage is always good.

2) Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.

3) Empty Space in an index that gets created due to the DML operations do not get used.

4) Indexes should be rebuilt at regular intervals.

5) Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.

6) Usage of cursors is always bad so avoid them like the plague.

7) Truncate command cannot be rolled back because it is a non-logged operation.

8 ) Table variables in SQL Server are always only memory resident.

9) Column order in a covered index does not matter.

10) In the case of SQL Server, one can separate the clustered index from the table.

11) Only committed data gets written to the disk.

12) Logical I/Os (LIO) are not a cause of concern, only Physical IO (PIO) are.

13) Count(1) is better performing than count(*).

14) Issue frequent commits in the application to make the transaction faster and also improve concurrency.

15) Views are evil evil DB Objects that always slow down performance.

28 comments:

Anonymous said...

Speaking to your statements about Oracle, you are totally wrong on every count, except perhaps for the first half of statement #7 (i.e. "truncates can't be rolled back") while still getting the other half of the statement wrong (i.e. "because they are a non-logged operation").

Not only are most of your comments simply incorrect, but many of them are in fact 180 degrees opposed to the truth.

And why include comments about SQL Server in a post about Oracle?

Riyaj Shamsudeen said...

Thanks for posting performance myths . Nearly every one of these points are totally incorrect and dangerous to beginners.

I really really urge you to do your own research and understand performance, BEFORE posting some thing so blatantly incorrect.

Anonymous said...

Пиздец.

Anonymous said...

+1 адназначна

Anonymous said...

кг/ам

Zloxa said...

Using Oracle is allways bad. Dont use Oracle database never!

Anonymous said...

Пиздец. А мужики-то не знают !

Anonymous said...

"Плохие советы" :)))

Anonymous said...

Аффтар, убейся ап стену!
ПИЗДЕЦ!

Anonymous said...

Гы ... это пиздец

Anonymous said...

Ужос! :-)

Anonymous said...

Бугага :)

Anonymous said...

pizdec!

Anonymous said...

АААаа блять, я сча умру !!!! :D

Anonymous said...

звезда в шоке -=:-O

Anonymous said...

Нормальная статья, спасибо за полезную информацию,
Распечатаю и буду показывать так называемым "Бывалым" и докажу что я был прав!

Anonymous said...

жесткачь.
аффтар, выпей йаду!

Anonymous said...

нельзя такие сказки на ночь детям рассказывать
http://olegon.ru

Anonymous said...

Oracle is very very pizdec

Anonymous said...

пипец... смеялсо до усрачки...

Anonymous said...

Have you even used Oracle? I doubt it as no person who has worked on Oracle and is of sound mind would ever give such crap "Tuning" tips.

In case you have a mentor just run away from him as far as possible.

Cheers mate.

Anonymous said...

What a pile of rubbish!!

Anonymous said...

This is a joke right?

Anonymous said...

人因夢想而偉大,要堅持自己的理想哦.................................................................

Anonymous said...

Try to learn the oracle database technology before writing such blog.

13 years young kids will do better!!!

Manoj Pandey said...

All tips are worst performance tips and should not be considered.

First Test yourself before putting them in your blog.

Unknown said...

Looking for Web Design Company in Chennai India? Sivashree is a best website design company offers ecommerce website design, flash design & logo design.

Anonymous said...

Nice blog..! I really loved reading through this article. Thanks for sharing such an amazing post with us and keep blogging...Well written article Thank You for Sharing with Us pmp training institute in chennai | pmp training class in chennai | pmp training near me | pmp training courses online | pmp training fee