H.Tonguç Yılmaz - Oracle Blog

Recently I didn’t blog much, still ~650 views a day I monitor from wordpress dashboard. It is like yesterday, I decided to move to this wordpress domain at December 2006 from tonguc.oracleturk.org because of some administrative reasons. My first post was a short one :)
http://tonguc.wordpress.com/2006/12/26/detecting-unrecoverable-operations/

Being a blogger has alternative costs, life passes by quickly and you choose to sit and write on something you experienced. But no pain no gain, being a blogger is an important opportunity to study topics further. It is always harder to write on something. Also as you are a blogger you may build up friendships with other bloggers, this is another good opportunity to share opinions closer. I always think blogging is different than writing an article, it is much more like a fast food, a blog post is nice for me if it can be consumed fast and easily. :)

There are a lot of very experienced people around me who thinks they can not blog because they are very busy, which makes me laugh because as a result I become very non-busy. :) For me this is just another choice of course with its alternative costs. Also some of mt friends think that if they can not make a difference why to start blogging since there are a lot of bloggers already. But I strongly believe that anyone’s experience on any topic can be unique and valuable, they can be simple and nice since we are not doing rocket science anyway. :)

I love to read and learn from forums and blogs as a result I believe I benefited more than I shared in my opinion. So I hope these kind of web 2.0 information sharing opportunities never stop growing.

As far as I can see XE, APEX and SQL Tracing was the most attractive topics to my readers, naturally these three areas were also personally mine most liked blogging areas. I may say in the future I do not think I will be blogging as much as I did especially like at 2007, but whenever I experience something I think is valuable to share, I promise I will do my best to blog about it.

Blog Stats

Total views: 243,303
Busiest day: 1,339 — Monday, March 10, 2008

Totals

Posts: 193
Comments: 344
Categories: 18
Tags: 35

All Time Top Posts

Introduction to Oracle Trace Utulity and Understanding The Fundamental Performance Equation, 14,652 views
Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management, 11,426 views
Oracle Best Practices Part 1, 9,429 views

All Time Referrer

tonguc.yilmaz.googlepages.com 2,494
google.com/reader/view 1,689
dba-village.com/village/dvp_links.Lin… 1,044
blogs.oracle.com 962
orana.info 944

All Time Search

about me 1,285
happy new year 784
new year 528
oracle 11g express 486
oracle trace 472
oracle 11g express edition 346
oracle 11g xe 321
oracle xe 11g 257
dbms_sqltune 218
oracle best practices 214

All Time URL

tonguc.yilmaz.googlepages.com 676
tonguc.yilmaz.googlepages.com/Code_li… 458
tonguc.yilmaz.googlepages.com/Code_li… 393
tonguc.yilmaz.googlepages.com/Code_li… 369
otn.oracle.com/xe 350

11.1 vs. 2008
http://www.microsoft.com/sqlserver/2008/en/us/sql2008-oracle11g.aspx

or

10.2 vs. 2005
http://psoug.org/reference/sqlserver.html

but always two things to keep in mind;

- Oracle is not cheap and getting more expensive with its new options on recent releases.
- SQL Server is cheaper but does not run on any other operating system but just Microsoft platform.

so why not going after another path :)
http://postgresql.org

1. Support Issues

I would wish Oracle to segment its customers due to both their financial value and Oracle know-how.

I really do want to see someone who knows more than me when I open an SR, after all those years of experience if I am opening a SR this really means something is wrong and I need immediate, to the point support.

In my company if one of our VIP customer calls our call-center we recognize him and guide him to a special team of call-center agents. There he experiences the SEVERITY 0 type of support, this is 21st century basic need for any company to full fill its customer loyalty.

2. Licensing Issues

I would wish Oracle to enlarge XEs limitations, enrich APEX and SQL Developer to the ends.

MS is so active at Universities that nearly no Computer Science student can have the chance to meet Oracle Database software until he is recruited. But after XE things started to change.

3. Acquired Software Company Issues

I would wish Oracle to provide “Oracle Database Friendly” versions of acquired softwares starting with Siebel and BEA.

I want to utilize my database investment, I do not want to experience another common database independent solution because I am not running MySQL but Oracle.

So what would you wish? :)

It was the good old days of Client-Server applications and at that times my company was migrating our applications from Powerbuilder to Oracle Forms. I was so fan of Powerbuilder and also so young, this change was making me sad and crazy.

Last week I met and worked with Power Designer, a very good data modeling tool to my first impressions. This success reminded me how I was miserable after Forms migrations and still after 10+ years Oracle’s only answer to data modeling seems to be Designer.

I did several searches on OTN and Oracle documentation, only alternative poped up to be Jdeveloper which Oracle database developer’s rarely installs after all those years;

http://www.oracle.com/pls/db111/search?remark=quick_search&word=%22Relationship+Diagram%22&partno=

http://search.oracle.com/search/search?default=true&keyword=%22Relationship+Diagram%22&start=1&nodeid=&fid=&showSimilarDoc=true&group=Oracle+Technology+Network

Today’s Designer is still inside Oracle Developer Suite 10g and is still seems to be closely integrated with Oracle Forms only.

We now have APEX inside 11g database installation, cool
We now have OWB inside 11g database installation, cool+
We now have SQL Developer inside 11g database installation, cool++

But what about today’s Oracle database developers’ business process and data modeling needs? I hope Oracle has plans for SQL Developer at least to have some designer capabilities soon.

Until that day Power Designer rocks! :)

PS : one need, several solutions, so who says SQL is always faster than PL/SQL? :)
http://forums.oracle.com/forums/thread.jspa?threadID=666829

Not to miss these kind of discussions you may register to OTN Forums > SQL and PL/SQL Forum > Popular Threads RSS listed here;
http://forums.oracle.com/forums/rss.jspa?feed=rss/rsspopularthreads.jspa?forumID=75

After seeing Eddie Awad’s Oracle in 3 Minutes series I planed to play with the trial of Camtasia Player. Camtasia Player is a simple, standalone video player, optimized for playing screen recordings.

I receive lots of comments on why I am not writing in Turkish, my native language. So I decided to use this tool as an opportunity to summaries some of my most read posts. After a small introduction to the tool I directly tried myself on my Oracle Quality and Performance for Free summary. My first recording experience was really fun, but I know I have to study more on my voice and tool’s options like pointer style, resolution etc. for the possible future videos. I am not sure if our community has a video blog but after experiencing Camtasia for some of us I am sure writing will be harder :)

Download and extract this zip file to a directory and use oracle-for-free.html file to play the video. (size ~58 Mb and download password is “lnk_psswd”)

Last week I spend lots of my time on our 10g multi-terabyte billing systems’ migration from hp tru64 to ibm aix environment. Cross Plathform Transportable Tablespace database migration or Data Pump options were not excepted by the management because of their downtime requirements. And the project was based on a redo change data capture tool Golden Gate, I will be blogging about this method and its risks later.

And also I hope to study Oracle’s Warehouse Builder next week and blog about my experiences, with its new version OWB is much more strong now, not only for ETL purposes but also for our daily needs ;)

I thought this was not a permanent problem but it has been over 6 months now, so I thought to create a workaround for the regular Turkish readers of this blog. My old blog at http://tonguc.oracleturk.org is still accessible, so from now on I will be publishing all my posts both here and there.

Under WordPress’s administration dashboard under blog stats we have All Time Top Posts and I also choosed most read 5 posts of this blog and imported them as well. But still Turkish Oracle blog followers will need some stuff mentioned here to access other wordpress blogs of course, even their own maybe. Here are some of my favorite Oracle blogs at WordPress, so really thanks to OpenDNS :)

- Tanel Poder’s blog: Core IT for geeks and pros
- Richard Foote’s Oracle Blog
- Jonathan Lewis’s Oracle Scratchpad
- Lutz Hartmann as sysdba

ps: ApEx 3.1 is now downloadable, check out below link for further information like how to use 3.1 on XE, OBEs of 3.1 and sample 3.1 applications; http://www.oraclecommunity.net/profiles/blog/show?id=1988559%3ABlogPost%3A4704

There were several interesting comments and questions during last Saturday’s seminar.

1. Can we flashback a truncated or drop purged table?

Thanks to Ogan for showing that this can be done by flashback database feature. Undo and Flashback Logs are different concepts. I created an additional demo to show that NOLOGGING options can be problematic here;

Code Listing 201a - Flashback database and NOLOGGING operations problem demo

2. How multi-versioning can be able to return rows of a dropped table since drop does not produce undo for the rows it has, only undo for the metadata?

Thanks to Baran for raising this problem. Tom Kyte has a similar example in his book but he uses DELETE and COMMITs, this is an accepted behavior if you are in a single user environment because the undo that DELETE produced will still be available after even you commit. But how DROP is covered here is a question mark, and TRUNCATE raises an error, there is a related Asktom thread for this question.

Code Listing 201b - Multi-versioning returning rows of a dropped table demo

3. Committing in a loop after each N rows during a big update over ROWIDs is much more efficient than a single sql update.

This was not a question in deed, some fellow challenged me during my locking demo with this comment. He even cried out that he couldn’t believe me not knowing this since this was Tom Kyte’s own claim! Pufff, this was an interesting experience, after all those years reading Tom’s books and Asktom threads I just could smile for a while and promised him to demonstrate that this claim both is not Tom’s and wrong.

I guess there can be several reasons that people can not understand what they read;
- they are unable to understand since their native language is not English,
- some forum threads are so long for an average newbie who even is unable to find the topic on the documentation they can not follow,
- the information provided do not have simple demos to assist understanding,
- on purpose they are trying to provocate or mislead the crowd to gain attention etc.
- what else do you think?

But still this was a unique experience since I saw lots of Oracle myth believers before but never saw someone crying out that this kind of a myth is Kyte’s claim. Tom Kyte is so dedicated to not to be one “Question Authority” that his efforts inspired lots of us for years. And here are two related threads of him;

“How to Update millions or records in a table”

“snapshot too old error”

Still I feel an Oracle exorcism is needed within my community after this seminar, so I will be preparing a presentation on some popular myths like;
- dividing a bulk update into pieces and committing frequently will relax your system compared to a single sql update
- we need to rebuild indexes regularly(never ending story..)
- hit ratio based tuning is enough
- bind variables are like aspirin, use them and gain performance anywhere anytime
- what else do you think?

With our native language in my presentation I will try to be short and to the point, try to prepare demos which will assist me since testing is believing. I will use DBMS_SCHEDULER to simulate concurrent access since there are important critiques that a single user environment test can not produce outputs that can be used as a proof for loaded OLTP environments. DBMS_ALERT will help to control the scheduled jobs execution timing, here is an example of this technique. So please comment on the myth topics, your advices on the technique I mentioned in order to have the most efficient output for this exorcism session :)

4. We know that you are not the best but how come that you are the most famous?

This was another interesting comment, FAME is something google promoted me somehow after years of blogging I guess, ironic when I do a Google search even I find my own posts sometimes :) I always thought that this is all about SHARING, but not sharing anything, sharing a valuable and trustable information, after all who thinks that he or she can be the BEST on some software which is this wide ranged and closed source? Also I am not interested to be the BEST but simply I am interested in to understand how things are implemented with the software I use daily, that’s all, and this is a rational human’s default behavior right?

ps: check out this SQL, really incredible :)

Testing Information : the scripts mentioned are tested on Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

References Used :

NA

I will completely get lost for the following two weeks, I will try to stay away from Oracle and Internet, plug back to life, especially to my family. Before my departure I have some notes to share;

1. Steven Feuerstein was in Istanbul for a two days seminar, The Best of Oracle PL/SQL. Unfortunately I couldn’t attend to Steven’s class so no seminar notes to share this time, but Steven’s all presentations can be downloaded from here.

As Steven mentioned after his seminar we had some great time together with Steven, he is a real legend :)

2. There are 184 registered people for our FREE “Why Oracle?(10g Release 2)” seminar on this Saturday. After all the feedbacks following was our final content;

11:00 - 11:50 / H.Tonguç Yılmaz
Oracle Database Concepts
Locking, Concurrency and Multi-Versioning Concepts, NULL value with Oracle

12:00 - 12:50 / Ertürk Diriksoy
Oracle Database Application Tuning Options
Materialized Views, Bitmap Indexes, Clusters

13:00 - 13:50 / Özgür Macit
Oracle Database Advanced SQL Options
Analytic functions, Hierarchical Queries, Model Clause, Regular Expressions

14:00 - 14:50 / Ersin Ünkar
Oracle Database Options
Flashback, Cost Based Query Optimizer

15:00 - 15:50 / Hüsnü Şensoy
Oracle Database Backup&Recovery Options
Recovery Manager(RMAN), Flash Recovery Area(FRA), Automatic Storage Management(ASM)

16:00 - 17:00 / Ogün Heper
Oracle Database High Availability Options
Real Application Clusters(RAC), Data Guard

It is a very cold time for Istanbul so hopefully I expect to have ~100 of these students attending at most and up to the RAC session at most 20 of them will be still alive with us I guess :) And also we had a poster this time!

3. ubTools Suite 3.2.0 is now available

With FETCH_THREADS parameter one table, partition or sub-partition now can be divided into pieces and each FECTH THREAD reads its own region in this release. Initial tests shows that this way ubSQL is even faster than Oracle’s own Parallel Query :) This is mostly because with PQ even we have N slaves one will do the FETCH and others have to wait on the Query Cordinator. But with FETCH_THREADS parameter of ubSQL we have N real sessions, each fetching, so no contention.

I will be blogging about ubSQL more later, for now please check this presentation, download and test it at your own environment, available platforms are HP-UX-IA64-B11.23 / Linux-2.6.9-5-X86-64 / SunSolaris-Sparc64-5.9 / Tru64-5.1

4. Last weekend we studied Jonathan Lewis’s latest book Cost Based Fundamentals of Oracle and as I promised the presentations we prepared are shared here. They are Turkish mostly of course and you need to be a member of this Turkish Oracle Users Group to download them, sorry :)

Okey, now it is time to say goodbye, I will be at Fethiye, my homeland, if you are some how nearby come and find me at Er-Öz Hotel so that we can chat on Oracle :)P

This morning I saw at oraclecommunity.net that Cary Millsap of hotsos started blogging at http://carymillsap.blogspot.com/ so I wanted to share. If you already read Cary Millsap’s book Optimizing Oracle Performance, you also might be very excited about this :)

Thanks to Marco Gralike for his feedback!

Golden rules of application design, don’t design your application to do anything that isn’t absolutely necessary;

1- Don’t run reports that nobody reads,

2- Don’t generate more output than you need,

3- Don’t execute a business process any more often than the business needs,

4- Don’t write SQL that visits more blocks in the database buffer cache than necessary,

5- Don’t update a column’s value to the same value it already has,

6- Push data when it’s ready instead of forcing applications to poll to see if there’s any work to do,

7- Don’t generate redo and undo when you don’t need the recoverability benefits provides by generating it,

8- Don’t parse any SQL statement that you could have pre-parsed and shared,

9- Don’t process DML one row at a time; use array fetches, bulk inserts, etc.

10- Don’t lock data any more often or for any longer than is absolutely necessary.

Reference - Optimizing Oracle Performance, Chapter 11, Responding to the diagnosis, Attributes of a scalable application