Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Dynamic sql enhancements in 11g

Oracle completes its dynamic SQL implementation for PL/SQL. February 2008 (updated June 2010)

"Optimizer Internals" - Celebrity Seminar in Netherlands

On June 15th 2010 I will be speaking about internals of the Cost Based Optimizer in Netherlands. This is a one day seminar organized by Oracle University which goes in details in some sections of the cost based optimization. The topics are related to hot themes for which people would like to get a good explanation. Here is a detailed table of contents.

This seminar is one of four that I have contributed to the series of celebrity seminars organized by Oracle University.

Hotsos Symposium 2010

I’ve attended and presented at numerous Oracle conferences and events over the years but one conference I’ve never had the opportunity to attend is the Hotsos Symposium, held each year in Dallas, Texas, USA. It’s one of the premier Oracle events, especially for those of us DBAs with a special interest in performance tuning and management and [...]

Sepaking at New York Oracle Users Group

I had the opportunity to speak at the New York Oracle Users Group (http://nyoug.org/info/metro_area_user_group/Metro_Area_User_Group_Meeting_Sep_2009_Agenda.pdf) today at Hotel New Yorker. I spoke about reucing the risk during the 10g to 11g upgrade using various tools and techniques available to us as a DBA. Here is the slide deck, if you are iterested.

Some of the technologies I have described during the talk have been explained in detail on various articles I have written. Here they are:

Database Replay: http://www.oracle.com/technology/oramag/oracle/08-jan/o18dbasereplay.html
SQL Performance Analyzer (SPA):
http://www.oracle.com/technology/oramag/oracle/08-mar/o28sqlperf.html
SQL Plan Management: http://www.oracle.com/technology/oramag/oracle/09-mar/o29spm.html

As always, I highly appreciate folks coming to me after the talk and provide the feedback. Please keep the feedbacks coming, via emails, phone calls, telepathy, whatever. I really look forward to them.

The CPU Costing Model: A Few Thoughts Part I

In the coming days, I’ll post a series of little entries highlighting a specific point in relation to the use of system statistics and the CPU cost model. In my previous post, we looked at how the cost of a FTS is calculated using the CPU costing model and how this generally results in an increase in the associated FTS [...]

C. J. Date: All Systems Go

Enrollment is open for the course taught by Christopher J. Date that we'll host 26–28 January in the Dallas/Fort Worth area. For many of us, this will be a once-in-a-lifetime opportunity to sit in a classroom for three days with one of the pioneers who created the field we live in each day.

I'm looking forward to this course myself. It is so easy to use Oracle in non-relational ways. But not understanding how to use SQL relationally leads to countless troubles and unnecessary complexities. Chris's focus in this course will be the discipline to use Oracle in a truly relational way, the mastery of which will make your applications faster, easier to prove, and more fun to write, maintain, and ehnance.

I'm particularly looking forward to his section about missing values—the ages-old debate about NULL—which is not covered in Chris's book upon which the course is based.

What is the purpose of SYS_OP_C2C internal function

Recently I was involved in one problem with CBO's selectivity estimations on NVARCHAR2 data type column. What I spotted in predicate information was the usage of internal Oracle function SYS_OP_C2C.

Here is an example of the run-time execution plan using bind variables:

SQL_ID 1fj17ram77n5w, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ *
from x1 where cn >= to_nchar(:a1) and cn <= to_nchar(:a2)

Plan hash value: 2189453339

-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 9999 |00:00:00.08 | 102 |
|* 2 | TABLE ACCESS FULL| X1 | 1 | 1 | 9999 |00:00:00.03 | 102 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SYS_OP_C2C(:A1)<=SYS_OP_C2C(:A2))
2 - filter(("CN"<=SYS_OP_C2C(:A2) AND "CN">=SYS_OP_C2C(:A1)))

As you can see the bind variables are converted to national character set using TO_NCHAR function. Column X1.CN is of type NVARCHAR2. It is easy to spot the difference between the SQL statement and the predicate information from the execution plan: to_nchar(:a2) from SQL statement is transformed to SYS_OP_C2C(:A2) in predicate information. The internal Oracle function SYS_OP_C2C performs conversion from one character set to another character set - C(haracterSet)2C(haracterSet). There are situations when one will see this conversion going on without explicit command as in this case what should be a sign that the data types are not the same and implicit conversion is taking place and this might be also a problem from performance perspective as it may disable index usage.

Jonathan Lewis and Charles Hooper, my fellows from the OakTable, had a discussion about this internal function back in 2007.

A fairly unique opportunity...

CJ Date will be giving a seminar in Dallas Texas next month. I wish I could get to it myself - but I'm already fully committed that week.

Maybe we can get the answer to using NULLs. Or at least some more input :)

OakTable Book "Oracle Expert Practices"

I haven't had much time in the recent past to write interesting blog posts, and the main reason for this is that I was very busy during the last couple of months - in particular contributing to the latest OakTable book "Oracle Expert Practices: Oracle Database Administration from the OakTable" by APress. It has been a very interesting experience - I've been co-authoring two chapters about Performance Optimization Methods together with another OakTable member: Charles Hooper.

This was a real collaborative work, a joint-effort if you want to say so. We exchanged the chapter contents rather frequently via Internet and I think this approach worked out quite well. I also have to thank Charles for spending a lot of time in rewording most of my "german" English into something that was consistent with his style.

It actually worked so well that what was originally planned as a single chapter grew so fast that it was finally decided to split it into two chapters - so we ended up with two chapters each co-authored by two authors.

Although it is obvious that something as complex as Performance Optimization Methods can't be covered to a full extend in a chapter (or even two) and therefore sometimes only the surface could be scratched and countless omissions were necessary I hope that the two chapters help to get a good overview of the available Performance Monitoring and Optimization methods.

I guess that these two chapters are not an easy read - we have packed a lot of details into them, but they should really be worth spending some time to dig through. We have also prepared numerous scripts that can be downloaded at the APress website to reproduce the described methods.

For a full coverage of the Performance Optimization area to me personally Christian Antognini's "Troubleshooting Oracle Performance" is still the benchmark - a very remarkable book.

I really hope that the same will be true for the "Oracle Expert Practices" book - it is supposed to be shipping real soon now.

By the way - it is still a bit early I know, but Charles and myself plan to perform a presentation together about our book chapters at the "Michigan OakTable Symposium (MOTS)" which will take place right before the OOW 2010 on the 16th and 17th of September 2010. So if you're looking for a "technical" conference rather than the more marketing oriented stuff at OOW - this might be interesting for you.

We have some very good ideas about this presentation - it will probably be more or less "zero-slide" and cover lots of demonstrations I guess, but it's too early obviously to reveal too much.

notes from UKOUG

I am back from my third visit to UKOUG and as always, the trip was wonderful, the conference was excellent and if I could have stayed, I would have. Of course, after dealing with trains all morning and then being stuck in an airplane seat watching movies for hours on end, sleeping in my own bed last night was absolute heaven. But the UK must be a wonderful place to live: the people are lovely,