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.

68 comments:

Tim Gorman 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...

кг/ам

Антон said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

Ужос! :-)

pin 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?

Poll - Email Script said...

Its highly informative. I would be visiting your blog hereafter regularly to gather valuable information.
Real Estate Script

Web Design Company Chennai said...

Hey really I read love your blog… I am really thanks to you to share this informations in internet to my kind of persons… really I loved loved your blog…. I will come back to your blog every updates…
http://www.winwebsolutionz.com

mathew said...

Very nice.......I'm sure it will help many people.....

job without office

david said...

Interesting… I might try some of this on my blog, too. It’s quite interesting how you sometimes stop being innovative and just go for an accepted solution without actually trying to improve it… you make a couple of good points


. online work

proswet654 said...

若對自己誠實,日積月累,就無法對別人不忠了。..................................................

任何時間 said...

很用心的blog,推推哦 ........................................

web development company said...

Great post and very well written, that will really help you to learn Web Design, web development and SEO Strategies to help businesses web design company . You can find out many useful information about web design, seo and his work by visiting his blog and I Just wanna say thanks you for the information you have shared. Web Design Company India

pass said...

台灣本土性愛影片性愛遊性愛文性愛性交自拍走光貼色情片無碼色情片貼片色情片影片色情卡通貼圖色情光牒色情米克色情自拍圖貼色情自拍網站色情免費成人無碼電影片色情片gogo色情片色情文說色美眉寫真色美眉影片色站排行榜色情!色情DVD色情qvod色情人無碼影片色情上床色情女圖片色情介紹色情分享片色情天室色情文小說少年阿賓系列小說言情小說免費線上閱讀 貼圖片區女同志聊天室

v辰原 said...

TAHNKS FOR YOUR SHARING~~~VERY NICE.................................................

明偉誠秋 said...

I do like ur article~!!!..................................................

有南 said...

KK777一夜激情聊天live show成人自拍貼圖自慰少婦自拍裸體圖片台灣色情成人網站情人視訊網情色留言板視訊美女免費視訊聊天室限制級極度震撼情色論壇色情特區自拍裸女貼圖潮吹性影片觀賞小穴情色片a圖片sex story性愛影片美女做愛成人色情網站性愛圖片成人情色貼圖全裸寫真集圖片走光圖女生陰毛自慰影片色情av1007成人色情聊天室女生自慰裸體照成人影音聊天台灣色情網站色情片打手槍情色天堂成人視訊聊天免費情色網站av網超性感辣妹激突成人論壇情色視訊聊天鹹濕成人網站av成人論壇免費美女視訊

劉WileyMares said...

看看blog調整心情,又要來繼續工作,大家加油........................................

采瑩 said...

nice job! waiting for your new artical. ........................................

WillyA_Mellinger said...

文章很棒~感謝!!..................................................

2013LesleyR_Bradshaw0901 said...

期待你的下次更新喔^____^........................................

韋于倫成 said...

It is no use crying over spilt milk.......................................................

竹青 said...

45avdvd 本土av自拍天堂 免費a長片 性感照片xxx383 av1688情人天使娛樂網,sex 台中援交34c甜心寶貝直播貼片 hhh重鹹系列免費 寶貝視訊台中援交友留言 桃園視訊聊天室 小魔女a自拍 上班族視訊 高雄援留言視訊交友 彩虹性愛巴士 夢中情人情趣用品 mmshow tw網愛 苗栗人聊天室 寶貝視訊ok論壇 0941電愛專線 520sex-情色視訊 免費影片無碼卡通 85cc情色視訊交友 免費視訊美女影音觀賞 色瞇瞇影片網 jp成人 免費a影片線上直播 h 視訊自拍美女聊天室 性感沙灘3,性感照片 視訊交友90739,限制級,777美女dvd 洪爺sex免費看a片論壇 微風成人區情色文學 微風論壇短片區 免費成人動畫 玩美辣妹有約 1111av女優天堂 嘟嘟成人網 視訊聊天1799 成人圖片區 台灣kiss,微風成人 show live 視訊聊天 g8成人下載 yam視訊交友 a 免費影片線上直播 情色視訊交友辣妹no3 日本a片援交妹 拓網天空交友高雄視訊 6k情人網辣妹視訊 正妹視訊elove交友 免費視訊formosa car 視訊聊天qk176 成人文學

韋于倫成 said...

聊天勢苗栗聊天室ut中部人聊天聊天尋夢080中聊天室一葉情成人貼圖區一葉情成人影片區一葉情成人論壇一葉情貼貼片區一葉情貼圖片區,一葉情圖片區一葉情語音一葉情語音聊天一葉情語音聊天網一葉成人網一業晴成人一業情貼圖ㄧ葉晴貼片ㄧ葉晴貼片區ㄧ葉晴貼圖區一本道影片一本道線上觀看一夜情天室一夜情無碼影片一夜情貼圖區一夜晴貼片一夜晴貼圖片區一夜晴貼圖區一情夜一貼片成人小說 言情小說 美女寫真ut聊天室

紫倫妍勳 said...

Better say nothing than nothing to the purpose...................................................

宛AshleyRemley1218儒 said...

As a man sows, so he shall reap...................................................

sdas said...

If you can, you can. ............................................................

SadeRa盈君iford0412 said...

Where did you purchase this product?.............................................................

曉薇 said...

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

江婷 said...

生存乃是不斷地在內心與靈魂交戰;寫作是坐著審判自己。......................................................................

黃怡 said...

在莫非定律中有項笨蛋定律:「一個組織中的笨蛋,恆大於等於三分之二。」.................................................................

清鴻 said...

人有兩眼一舌,是為了觀察倍於說話的緣故。............................................................

羅承霞 said...

It takes all kinds to make a world.............................................................

玉婷良DGFHFJ瑋黃吳 said...

hi!~~leave you a message to say hello, and thanks for your share!..................................................................

育隆 said...

看著你的BLOG 好多朋友都回應 真厲害..................................................................

陳倩江陳倩江 said...

haha~ funny! thank you for your share~............................................................

陳佑發 said...

你的部落內容真棒,一定要持續下去! ............................................................

琬安琬安 said...

Joy shared with others are more enjoyed.............................................................

許惠吟許惠吟 said...

thx rfor you sharing~~learn it by heart............................................................

奕蕭君 said...

一時的錯誤不算什麼,錯而不改才是一生中永遠且最大的錯誤..................................................

雅王任 said...

相見亦無事,不來常思君......................................................................

王辛江淑萍康 said...

心平氣和~祝你也快樂~~..................................................

tongtong said...

人有兩眼一舌,是為了觀察倍於說話的緣故。............................................................

璇陳陳陳竹 said...

一個人的際遇在第一次總是最深刻的,有時候甚至會讓人的心變成永遠的絕緣。......................................................................

耿麗旺麗旺麗旺綺 said...

Learning makes life sweet.......................................................................

黃英吳思潔吳思潔邦 said...

用心經營blog,人氣百分百~^^ 加油

鲁涵淞 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.

Siva Shree 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...

Read all the related Posts:

64 Software Manual Testing Interview Questions

Answers To Common Job Interview Questions

Behavioral Questions In Interviews

Questions to Ask at an Interview

Competency based Interview Questions


Read all the related Posts:

How to avoid missing defect in Software Testing?

Defect Management Process

What is the difference between a Test Strategy and Test Plan?

Beginners Guide to ETL Testing

Step by step guide from Test Case Development to Test Execution