Dynamic sampling (DS) was introduced in Oracle Database 9i Release 2 to improve the optimizer’s ability to generate good execution plans. The most common misconception is that DS can be used as a substitute for optimizer statistics. The goal of DS is to augment the optimizer statistics; it is used when regular statistics are not [...]
hi, Tom. I have a question regarding to_char function converting number to varchar. Somehow I got errors in PL/SQL statement saying "ORA-06502: PL/SQL: numeric or value error: character string buffer too small". Then I ended up with fi…
Recently we received several questions regarding an unusual situation where a SQL Statement has hundreds of child cursors. This is in fact the expected behavior when CURSOR_SHARING is set to similar Bind peeking is in use And a histogram is present on the column used in the where clause predicate of query You must now [...]
Now that Oracle Database 11g Release 2 is out, more and more folks are considering upgrading to 11g. However, if you are currently on Oracle 9i then you will have to tackle both the upgrade to a new release and the migration from the Rule-Based Optimizer (RBO) to the Cost-based Optimizer (CBO). The RBO was [...]
<code>Tom: Even though I have been using implicit cursors and REF cursors I am still unclear on when do you use each: 1. For regular cursors, which are pointers to a SQL statement result set, you can use either explicit or implicit. Implicit …
<code>hi Tom, i saw your FAQ regarding this but couldn’t find the answer. my initORCL.ora’s nls_date_format is being ignored! is there a way to adjust sqlplus so it uses a certain date format always – other than by using a sql statement or a…
<code>Hello Tom, I’d like to know if there is a way to locate the previous to the last SQL statement executed by a session (because the last one will be the one at V$SESSION, V$SQLTEXT to find out the statement) FROM WITHIN THE SAME SESSION. I need …
One of the features of all the versions of the Oracle Utilities Application Framework is its implementation of caching to improve performance. The idea is simple enough, load data or function into a faster media (e.g. memory) to minimize calls for the data or function on slower media. Caches are typically in a number of [...]
One of the features of all the versions of the Oracle Utilities Application Framework is its implementation of caching to improve performance. The idea is simple enough, load data or function into a faster media (e.g. memory) to minimize calls for the data or function on slower media. Caches are typically in a number of [...]