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

April 16, 2008

Event 10046 as a vitamin

Filed under: Oracle How To,Oracle Performance — H.Tonguç Yılmaz @ 11:53 am

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 :)

1 Comment »

  1. Materialized view refresh change in 10G and sql tracing –
    http://oracletoday.blogspot.com/2008/04/materialized-view-refresh-change-in-10g.html

    Comment by H.Tonguç Yılmaz — April 19, 2008 @ 5:58 am | 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

Create a free website or blog at WordPress.com.

%d bloggers like this: