Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

February 22, 2009

On Troubleshooting Oracle Performance book by Christian Antognini

Filed under: Oracle Best Practices — H.Tonguç Yılmaz @ 9:01 pm

I want to advise Christian Antognini‘s book Troubleshooting Oracle Performance to the newbies and especially Oracle database application developers. For years I advised Efficient Oracle by Design by Thomas Kyte to the beginners because of similar reasons and now this book also has lots of practical hints and additionally lots of 11g new features are mentioned.

Especially I loved Chapter 9 Optimizing Data Access, 11 Beyond Data Access-Join Optimization and 12 Optimizing the Physical Design. The last two chapters has important performance tips for the Oracle database application developers, for example column retrieval cost in CBO calculation and the following example on best practices in datatype selection are in my opinion some of the most simple and discarded ones. Below example on the NUMEBR dataype shows how the same value may have different scales and since the main datatype used to store floating-point numbers and integers is NUMBER with Oracle database applications this kind of tips are really important to be aware of.


set numwidth 18

CREATE TABLE t (n1 NUMBER, n2 NUMBER(*,2));
INSERT INTO t VALUES (1/3, 1/3);
COMMIT;
SELECT * FROM t;

                N1                 N2
------------------ ------------------
,33333333333333333                ,33

SELECT vsize(n1), vsize(n2) FROM t;

         VSIZE(N1)          VSIZE(N2)
------------------ ------------------
                21                  2
				

Additionally related to the NUMBER datatype, it is mentioned not to be efficient when supporting number-crunching loads and as of 10g two new datatypes are available, BINARY_FLOAT and BINARY_DOUBLE which implement the IEEE 754 standard so a CPU can directly process them and they are fixed-length. Please check Binary_double vs. Number in compute intensive processes post for an example how this decision may affect the performance.

So if you also find yourself requested tuning of applications on production databases again and again like me, I strongly believe that it will be a very wise action in long term to let the Oracle database application developers around you to know about this book and make sure they read and understand the last two chapters so that they can be aware of the different datatypes, table types and index types options with Oracle database during the design and implementation of their database applications.

About these ads

2 Comments »

  1. Telling the database that one-to-one mandatory relationship exists on the join columns with a foreign key, you can eliminate the unnecessary table’s join cost over a view after 10g Release 2 – http://www.oracle.com/technology/oramag/oracle/09-mar/o29asktom.html

    (this was another interesting topic, so I wanted to share above example)

    Comment by H.Tonguç Yılmaz — March 5, 2009 @ 9:05 am | Reply

  2. […] The book is already reviewed as a blog post by many other bloggers like Carry Millsap, Jonathan Lewis (they  also wrote forewords for the book), Jason Arneil and  Tonguc Yilmaz. […]

    Pingback by TOP by Christian Antognini « Coskan’s Approach to Oracle — May 20, 2009 @ 4:30 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: