Chronicles of 11gR1

I am still on holiday, but Oracle is like drugs :) I was reading 11g now or 11g later forum topic on oraclecommunity.net and decided to install and play with 11gR1. I am an advocate for second release by experience for a production environment, to go with first releases there must be really strong motivations for new features. But on VMvare it is always fun to play with Oracle’s new features :)

So I was playing with 11gR1 on Oracle Enterprise Linux 5 at VMware Workstation 6 on my laptop, but first things first;
– Oracle 10.2.0.4 patchset is out for Linux x86, but yet another month I guess for plathforms like Solaris and Windows.
– On February 27th apex.oracle.com will be upgraded to 3.1, so if you have some important applications to be exported don’t be too late.

Since dizwell closed his resources to the community this time I couldn’t have the pleasure to consume his fantastic installation guides, strange but I never thought of loosing a resource like this before, now I have similar fears for some blogs I frequently read and asktom. But there are some really good alternatives for quick installation guides like
http://www.oracle-base.com/articles/linux/ArticlesLinux.php
http://www.puschitz.com/
http://ivan.kartik.sk/
and of course for details there is always official installation guide documentation.
http://download.oracle.com/docs/cd/B19306_01/install.102/b15660/toc.htm
By the way if you are also missing Howard Rogers’s fantastic articles on dizwell.com and his OTN forums replies maybe this helps, see this petition on oraclecommunity.net.

I want to comment on my first test results and share my first impressions of 11gR1 briefly here;

1- Our old USER_DUMP_DEST, BACKGROUND_DUMP_DEST and CORE_DUMP_DEST is replaced with DIAGNOSTIC_DEST parameter now and we now have also xml based log files. Hard to like immediately.
But it is really easy to get the trace file name now, V$DIAG_INFO view gives the trace file name with TRACEFILE_IDENTIFIER that is set. Lovely.
With each trace file also a small binary .trm extension, trace map file, is created now. Interesting.

2- Instead of SGA_TARGET and PGA_AGGREGATE_TARGET now we have MEMORY_TARGET by default to manage both sga and pga together. Cool.
Also my DB_FILE_MULTIBLOCK_READ_COUNT was set to 52 by default. Interesting.

3- Still after a FLASHBACK TABLE TO BEFORE DROP constraints and triggers related to the table are left with BIN$% names. Unhappy.

4- sqldeveloper is a part of the installation of the 11g database now. Lovely.

5- apex is also a part of the installation of the 11g database now. Lovely.

6- Lots of new parameters, supplied packages and background processes are introduced or changed. This study of Julian Dyke summarizes the differences between Oracle 10.2.0.1 and Oracle 11.1.0.6.

Listing 202a – Chronicles of 11gR1
Listing 202b – Some simple event 10046 and 10053 tests on 11g

11g may be a real cost with its new features, I am excited to see what will be included on the 11g Express Edition which is most probably be available with the second release. Until then if you also want to go out and play below references provide much much more;

Oracle Database 11g: The Top New Features for DBAs and Developers by by Arup Nanda
http://www.oracle-base.com/articles/11g/Articles11g.php
Oracle® Database New Features Guide 11g Release 1 (11.1)
http://wordpress.com/tag/oracle-11g-new-features/

Advertisement

Interesting comments and questions of “Why Oracle?” seminar

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

This blog will be out of service for a while :)

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

Warning for the Oracle DBAs and Database Developers, taste and enjoy the rant of Application Express :)

In my recent post I discussed Oracle’s SQL and PL/SQL features’ powers. With this post I will mention some Apex specific supplied packages like; HTMLDB_APPLICATION, HTMLDB_ITEM and HTMLDB_COLLECTION.

HTMLDB_APPLICATION package is a PL/SQL package that implements the Application Express rendering engine. You can use the HTMLDB_ITEM package to create form elements dynamically based on a SQL query instead of creating individual items page by page. With HTMLDB_COLLECTION every collection contains a named list of data elements(or members) which can have up to 50 attributes(or columns). You insert, update, and delete collection information using the this API. And all HTMLDB_% supplied packages have APEX_% synonyms after the product’s name changed from Html Db to Application Express.

Before going into details of these packages with some examples please let me remind you that I am not even a beginner for web technologies, I have just simple HTML and Javascript knowledge lets say. Also no object orientations for me, all my career is based on data orientation, as a result I may say I have a little SQL and PL/SQL knowledge ;) And my hero Application Express is my enabler to create browser based Oracle database applications.

With this background I have, after completing the chapters of 2 days plus Apex developer’s guide and studied the sources of the sample application provided with Apex installation it was a child game, really fun for me to build Apex applications.

The chapter 10 of the 2 day plus Apex developer’s guide was like my cookbook, step by step I applied the rules mentioned to develop. Of course Application Express Oracle Magazine Articles, Apex OTN forum and Apex Evangelists’ blogs helped me a lot whenever I faced a trouble. And special thanks goes to my colleagues Hüsnü Şensoy, Ersin Ünkar and Hakan Ağdere for their creativity, advises and supports on some of the below examples.

First of all this application is a Terminal(simply a cellular phone) Management application; you define Terminal’s catalog properties, menus, test scenarios and results on this application. All these informations produced are supplied to the channels like call center and data warehouse over views and pl/sql package apis. So with this supplied data for example you can design campaigns to your customers based on the catalog information you have about their terminals since you have their terminal type based on their service usages etc.

Example A. Creating a Dynamic(runtime tailored based on user defined parameters) Update Form

In order to minimize the development need end users wanted to define new terminal parameters and based on their inputs update forms should be generated on runtime. From first day we knew this need could be handled within Apex because Apex’s own development environment is also written with Apex :) By the way when we say Apex remember we are talking about pure PL/SQL inside a schema of your Oracle database.

In order to tailor the screen we used HTMLDB_ITEM and to update the dynamic form generated back to the database HTMLDB_APPLICATION apis.

Example B. A Custom Excel Report

Within Apex it is only a click away to export your regions’ data to Excel. But one of the end user report need was to create an Excel sheet with lots of tabs having grouped all the information for a spesific terminal.

With the help of DBMS_LOB, OWA_UTIL, HTP and WPG_DOCLOAD supplied PL/SQL packages we created an XML based output file and let the users to download this file to their client to open and work with Excel.

Example C. Creating a Dynamic(runtime tailored based on user inputs) Report Form

Here the users wanted to have reports based on their dynamic filters like “report me the terminal having an internal antenna and 100 grams weighted and supporting browser types of HTML, WAP 1.2”

In order to record the user’s dynamic filters we used HTMLDB_COLLECTION api, with each filter iteration we added the filter to the collection and when user requested the report’s output we had the dynamic query ready to be passed as a source of a report region.

Example D. AJAX Tree Based Input Form

Apex has tree region type but when you have some thousands of leaves manipulated frequently, users didn’t like the performance of this component since it always submits the whole page when you want to move on the tree. So after a little research we learned about some technology called AJAX(which I still do not have any detail idea:) but an Oramag article Building an Ajax Memory Tree by Oracle ACE Scott Spendolini helped us to solve this need.

Some Sample Pseudo Code like references from the Application

Some Sample Screen shots from the Application

If you are an Oracle DBA or a Database Developer, for happy customers and management I will advise you to try Application Express as soon as possible because;
– Apex is FREE to use with any Oracle Editions and with Apex you do not need any other kind of orientation to develop browser based applications,
– Apex is strong since it is completely SQL and PL/SQL integrated, and will be stronger with its new releases,
– Some think Apex is just a desktop application alternative, like you may use Oracle database as a data pump you can use Apex for an Access or Excel alternative of course. But there is a very important potential if you want to take its advantages and I think no extra proof is needed for this potential when you consider my own background I mentioned above and what I am enabled to do with Apex!

Oracle provides SQL and PL/SQL features so that we can do a lot more with fewer lines of code

Recently I started a discussion on oraclecommunity.net database forum with subject “Why Oracle?”. Hans Forbrich and Billy Verreynne gave important feedbacks. As a group we are preparing a free half day seminar to the computer science students in Istanbul.

When I started to study 10g I tried to blog my experiences on new pl/sql packages with a series called Expanded Supplied Packages with 10g. If you do not want to waste your time and money on developing some features which are already available in your database, it is better to be aware of them. As Tom Kyte‘s saying; no need to re-invent a dumper wheel :) For 11g here is a start point for the new and updated ones;

11g Release 1 New Features in the SQL Language
11g Release 1 New and Updated PL/SQL Packages
11g Release 1 All New Features Grouped by Guides

Recently I was playing with DBMS_ADVANCED_REWRITE package which was also a new feature on 10g. You may want to use this package for rewriting a bad query on your production database until it is tuned or even to make your friends go mad with changed results to their queries on your development database :)

Code Listing 198 : DBMS_ADVANCED_REWRITE demo

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

References Used :
http://www.oracle-base.com/articles/10g/dbms_advanced_rewrite.php
http://technology.amis.nl/blog/?p=447
http://psoug.org/reference/dbms_adv_rewrite.html

Cary Millsap started blogging

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

Performance Tuning Protocol – Part 2

In Part 1 I tried to summaries why we need these tools and when to use which one. Also additional to the post at the comments I mentioned dbms_sqltune(10g), dbms_monitor(10g) packages and _trace_files_public parameter. With this additional post I will mention some more tools of course :)

Wait Interface related tools

As I mentioned in Part 1 10g introduces a number of enhancements designed to automate and simplify the performance diagnostic process. Grid control and dynamic performance views like v$session, v$session_wait, v$active_session_history etc. will simply guide you to the root causes of your performance problems. Perform Without Waiting article on Oracle Magazine July/August 2004 by Arup Nanda is a very good starting point as usual. Also Kyle Hailey’s Oracle Wait Interface Introduction presentation is another good reference.

Open source tools

There are a lot of these kind of scripts, tools around. One thing to remember is always to test them on your test databases first.

Kyle Hailey’s direct SGA access project and ASH monitor

Tanel Poder’s Oracle Session Snapper and Session Wait script

Metalink tools and references

If you have access to Oracle support site I suggest to download and try these tools on your test system, especially I love SQLTXPLAIN.SQL since it provides a summary report like pilot’s console during a flight, anything you may need related to a query is in front of you :)

Note:215187.1 – SQLTXPLAIN.SQL – Enhanced Explain Plan and related diagnostic info for one SQL statement

Note:243755.1 – Implementing and Using the PL/SQL Profiler

Note:224270.1 – Trace Analyzer TRCANLZR – Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

Note:238684.1 – SQLAREAT.SQL – SQL Area, Plan and Statistics for Top DML (expensive SQL in terms of logical or physical reads)

Note 39817.1 – Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

Note:62294.1 – The DBMS_SUPPORT Package

And if you still need more tools and have a budget Quest’s http://www.quest.com/spotlight-on-oracle and http://www.quest.com/SQL-Optimizer-for-Oracle are my favorites :)