Optimizing Oracle by Desing – my impressions after Jonathan Lewis’s three days seminar

I have been working on Oracle databases for more than 10 years now. I started with version 7.3 at a local bank and with 8i I moved to the third largest Telco in Europe. If you do value, I also choosed to get certification. I have attended several educations, seminars, conferences, experienced lot of risky projects and weird problems. But my last three days experience was completely something different. Since my native language is not English and this post mostly will not be a technical one, it may get harder for me to express my feelings here.

For years I have been reading Jonathan Lewis‘s articles, books, forum posts and of course after 2007 his blog. In my opinion he is a very special Oracle database expert, the reason why I think this way is not his deep knowledge about the technology but his willingness of sharing all his years of hard work and experiences every time with a very gentle way. Also Jonathan is one of the best teachers I experienced in my whole life. Nowadays it is getting harder to find someone having all this attributes together. IMHO some are very experienced but they do not care about sharing or some “huge mr.ego” experts try to beat people all around forums.

Jonathan’s experiences for all those years produced two important books for the community. But let me talk a little on the seminar book; A4 sized ~400 pages, each page supported with at least three or four paragraphs related to Jonathan’s previous on-site consultancy experiences, which you may not learn from documentation or any google search. These are all nasty things we experience during our daily lives, but Jonathan documented them so well that this course book’s content is like a best of summary of my all time best Oracle books. Even very advanced internals were explained with perfect simple powerpoint slides and supported with example scripts which are all provided in a CD.

Normally after a seminar I share my seminar notes(highlights, topics I liked most lets call), but after this kind of a knowledge storm this time I do not have the hearth for it. Instead I will be sharing the course topics briefly, the new questions I have now after the seminar and some hints you may find useful if you want to attend this seminar in the future.

Yes I have lots of new questions now, normally I love this kind of a feeling, having questions is always good for motivation. But this time I also feel some fear(this may not be the exact word to define, anyway), because Jonathan showed me that even on fundamental topics like redo or undo I still have lots of research to do. This is so normal I guess, because I am limited with the experience that I can get from the databases around me. When operational stuff takes lots of time there is no space for new experiences usually. So for sure I may say in consultancy market you get to learn and develop a lot. I admired Jonathan very much and I felt strongly that in near future I will love to accept its risk and quit to do much more dedicated research on Oracle database.

Course table of contents
Some hints you may like
Lots of new questions I have (if you feel you know some answers please comment :)

Jonathan also uses some extreme examples on purpose from his past experiences, I believe to have and keep the interest all during his long sessions, this way of teaching is something I sympathized. Jonathan shared the SQL scripts to reproduce the outputs for our own databases which he might have tested on 9i or his laptop and shared the results.

I think Oracle community must feel very lucky to have some independent(non-Oracle employee) experts who are being honest, also mentioning the dark sides(bugs, don’t DOs) of this great software, advise to test in details, to be doubtful about so called “guru”s and each new feature for our own comfort. This is unique and precious in todays “marketing” world. For Jonathan’s efforts on developing this course I can not find the right words to explain how thankful I am. I will strongly suggest for the readers of my blog who feel them as an experienced Oracle DBA to attend his seminar at any cost.

ps1: also Graham Woods was in Istanbul, he did a very similar presentation he did on UKOUG last December which I already shared my notes before here.

ps2: UKOUG 2008 call for papers submission deadline for Oracle technology category is getting closer, 9 May 2008. This year I also want to come front, do at least one presentation, I have RMAN, APEX and of course Tuning related topics in my mind. So let’s come front, share our experiences with world’s largest independent Oracle User group community.

ps3: In my 20th month now I am very close to my 200,000th hit(wordpress’s counter). Recently my wordpress dashboard automatically evolved somehow and now I have lots of new stats. As far as I see people once loved event 10046 tracing, flashing back options after 10g and of course my initial best practices posts and no matter what I post after these three seems to be dominant and to stay on top visited list forever I guess. So anyway, thank you for reading and I really hope to have more comments in the future since sometimes I really feel I am alone here :)

Event 10046 as a vitamin

This event is not only useful for tuning purposes, whenever I experience a weird behavior of the database engine usually also Oracle support requests the trace file produced with this event since the content shows any database calls made inside the traced application.

Today one of my colleagues asked me if he can unwrap a wrapped pl/sql application. I asked him if he really needs to unwrap it or what he is after may be the SQL calls inside that wrapped application. And showed him the tkprof report of the event 10046 trace of a sample wrapped application, he loved it since it was a kind of a reverse engineering of the pl/sql api they were supplied by a third party company which is not around anymore.

Code Listing – Tracing a Wrapped PL-SQL Code Example

Above tkprof report shows that wrapping a simple pl/sql code like that still adds 57 internal SQL statement calls, 20 hard parses and 208 consistent gets. So before buying a wrapped application and deploying it to your production it may be better to ask yourself twice if you could force more the vendor to get the sources to avoid any performance cost and a possible future reverse engineering effort.

Recently I used 10046 event output to troubleshoot a weird ORA-xxxxx error problem with DBMS_CDC_SUBSCRIBE supplied package and I tried to reverse the database calls of APEX application development environment by starting the trace from a database login trace trigger. After getting used to 10g’s beauties like ASH my needs to use 10046 tracing are getting very limited, but I am sure event 10046 will remain as a powerful tool in my Oracle toolbox for years.

Anyway if you need more information on wrapping a pl/sql application please check Note:341504.1 – FAQ: Wrapping PL/SQL Source Code or you persist to unwrap a wrapped pl/sql please check this presentation :)

A DDL tacking application, if CASCADE option of DBMS_STATS needed everytime, ORA_HASH post update and others

This post will be like a summary of last two weeks of mine. First of all, yesterday we celebrated my company‘s 15th year, I feel very lucky to be a part of this success story for my last 8 years :)

Lately I was and will be mostly working in two projects, in parallel;

1- Migrating our billing/crm database from Tru64 to AIX platform; there are several important lessons learned in this cross-platform xx TB Oracle database migration, I will be blogging about these in details later. For now I will be sharing below Oracle-L discussion of mine, a DDL tracking application which was requested to audit and control especially the database objects which are not supported by the change data capture product Goldengate in this post.

Oracle 10gR2 Multi-Terabyte Database Cross-Platform Migration Method
Cross-Platform Tablespace Transportation from Oracle documentation
Oracle 10gR2 Multi-Terabyte Database Cross-Platform Migration a Case Study

You may find the DDL(Data Definition Language, statements used to define the database structure or schema) types in Oracle 10gR2 here. In this small application I used a database trigger and an autonomous transaction based procedure to log the operation, db_name, login_user, object_owner, object_name, object_type, sql_text, attempt_by and attempt_date informations.

A custom DDL tacking application for Oracle database

Also in one of my previous posts I discussed on 10g’s ORA_HASH SQL function as a data validation tool. During a row by row migration(unload on source>ftp text to traget>load texts parallely on target or like in our case insert into select from over dblink plus apply changes captured by a CDC tool on production during this logical data transfer kind of strategies) a validation need becomes very important.

This validation must involve three major parts in my point of view;

1- the metadata validation,
2- the data validation,
3- the performance validation.

I made several updates on the previous post, if you are interested on this topic please follow this link.

2- Design and development of an operational data source in front of our datawarehouse; this environment will differ from datawarehouse in two major areas, first is it will not be denormalized like a typical dwh and be very similar to the operational database’s tables, so transaformation will be very limited compared to a standard ETL. Second from this environment customers want to have near-real time reporting, so Change Data Capture(CDC) kind of data transfer methods are needed.

We will be first implementing an operational data source for our CRM database which runs Siebel application. There are over 60 typical operational near-real time reports need. Oracle Warehouse Builder 11g release has a new feature to integrate a warehouse with Siebel source databases, we will also be testing the performance and capabilities of this new feature.

But as I commented here we will also be working on a logical standby implementation to have the ETL window flexibility in order to be near-real time.

During this project we also had an interesting discussion on DBMS_STAT package’s two options; CASCADE and GRANULARITY. In Choosing An Optimal Stats Gathering Strategy post of Greg Rahn I commented about this discussion. For an 10g Release 2 datawarehouse CASCADE option may be left to FALSE since after each rebuild at the end of a typical daily ETL optimizer already collects statistics. And for the GRANULARITY option to leave it to its default usually will be enough. These two things may help you avoid unnecessary gathering, and reduce total ETL time of course. Below is a small demo for CASCADE option.

Is DBMS_STATS package’s CASCADE option need to be set to TRUE always?

And for the OTHERS part my three highlights are as following;

1- My team Fenerbahçe defeated English Chelsea in Istanbul, but in London we couldn’t make it to the Champions League semi-finals, in my opinion Chelsea isn’t playing good this year and we missed a very important opportunity. My team has young and unexperienced players, but with this much hungry for success for the coming years I am nearly sure we will force the first 4 again and again :)

2- This week at last wordpress domain is back in my country, nearly for a year now we have been playing hacking games.

3- Last words are for the Oracle Critical Patch Update Announcement April 2008 which also includes two nasty vulnerabilities for APEX.

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

References Used :