This blog is about understanding how Oracle database works.The learning process never ends and this is espeacially true when one tries to understand what is hidden in the magic box called Oracle database.

Tuesday, 24 November 2009

Recently I was involved in discussion on OTN form (see http://forums.oracle.com/forums/thread.jspa?threadID=986657) and I found an interesting information inside the posted execution plan which was in "Predicate information" section and was

filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='HQPRM003')

Googling showed some interesting information in Vlad Sadilovski's blog that KOKBF$ is a generic alias given the table expressions. What was more interesting was undocumented SYS_OP_ATG function which attracted my interest. So I decided to create a simple case to observe what is going on.

Here CBO transformed CARDINALITY hint to a new undocumented hint used in SQL Profiles OPT_ESTIMATE (TABLE "B" ROWS=1.000000 ) which does exactly the same - scales down the number of rows to 1 in this case.

Another very interesting piece of information here is how the statement is internally transformed to use SYS_NC_ROWINFO$ which is a virtual column that references the row XMLType instance and is there since Oracle9i. In later releases this was replaced with a new OBJECT_VALUE pseudo column but obviousl CBO still uses that internally (for more details see "Oracle Database SQL Language Reference")

Here Oracle uses undocumented function sys_op_atg which returns a column from object type. The first parameter specifies the starting column, the second one specifies the ending column and for the third parameter I was not able to figure out what does it mean. In all cases which I have found on Interenet or in Oracle manuals this parameter had always value of 2.

Then CBO was trying to push the filter condition inside function execution what was of course not possible.

FPD: Considering simple filter push in query block SEL$2B5797BE (#0)

In next step CBO generated transitive predicate which could be observed under the "Predicate Information" section of execution plan.

SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=8 AND SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2)try to generate transitive predicate from check constraints for query block SEL$2B5797BE (#0)finally: SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=8 AND SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2) AND SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=8

From my presentation at IUOG back in 2005 about the Extensible optimizer I used the trick to associate the function with statistics type. Of course I could use also DYNAMIC_SAMPLING but this is more elegant way. Here is what I have done:

From this plan we can see that each row source is returned by "COLLECTION ITERATOR PICKLER FETCH" and that function DEMO_FUNC is executed only once (Starts = 1).

Of course there is no chance to push predicate as a.id=b.id and a.id=8 inside the function execution. Therefore Oracle has to use SYS_OP_ATG function to decompose returned object and perform the join operation only after function calls complete there execution.

We can see that this could be very inefficient. To resolve the possible performance issues one should not use functions (this is true also for pipelined functions) if the function result is used in later join operations or the majority of returned rows is filtered out later on. One possible solution would be to pass some conditions as function parameters, but the best solution is to replace a function call with inline view which contains the query used inside function. Then the CBO has more possibility and place for optimization and can optimize the whole statement.

If I return back to the origin for this post - we got the best result when we had replaced function call with inline view containing the query from the function. The CBO can't really push any kind of predicates inside the function call and the only possibility is to use function parameter for passing them.

This was also a good example to point out the problems of cardinality estimates for table functions and how one can use extensible optimizer to resolve it.

3 comments:

a small addtition to this:>The estimated cardinality is 8168 rows what is a default value used for 8k block size.I remember reading metalink note which explained it as "we assume a default of a 100 blocks table with 100 bytes per row for table() functions", which results in a figure of 8168. Unfortunately today I can't find the doc ID.

My training seminars

About Me

I have more than 20 years of experience in working with Oracle products. I began in 1988 with Oracle Database version 4 while working for the City of Ljubljana, where I had charge over the city's municipal and geographic information systems.

More recently, I have specialized in performance optimization, having developed my own tool set for monitoring performance and analyzing trace files.

In addition to sharing my knowledge through conference talks, I am conducting technical seminars organized either by Oracle University or myself.

I am very proud of two highly respected memberships: OakTable network in 2005 and Oracle ACE in 2009 and Oracle ACE Director in July 2010.

When not working with Oracle products I enjoy flying as private pilot (PPL(A) with night rating) with my Mooney M20E year 1966, call-sign G-ATOU. Prior Mooney I had an old Piper PA-28-140B Cherokoee, call sign D-EHFK and I have crossed almost all Europe with her.