Feed items
Will Enabling a 10046 Trace Make My Query Slower (or Faster)?
February 13, 2012 (Modified February 14, 2012) It seems that I often wonder about the things that I read… is it really true? Several years ago, one of the items that I wondered about is whether or not an execution plan for a SQL statement could change magically simply because the Oracle RDBMS knows that it is [...]
SESSIONS Derived Value Formula – Doc Bug Explains
February 8, 2012 Default parameter values are great, as long as it is possible to predict the default values, and the defaults are appropriate for the environment in which the defaults are present. It is sometimes a challenge to remember all of the rules, and exceptions to those rules, that determine the defaults. I had a [...]
Oracle Query Optimizer Vanishing Acts
February 3, 2012 A couple of days ago I noticed an interesting thread in the comp.databases.oracle.server Usenet group that described a problem of vanishing tables. The title of the thread certainly caught my attention, and I was a bit disappointed when I found that the there was little to no magic involved in the vanishing act. The situation reported [...]
Parent-Child Relationships and the Questions Left Unanswered by TKPROF, Re-Learning Something Old
January 30, 2012 As we have seen in the past, TKPROF output sometimes lies, and in a recent OTN thread I was reminded of another case or two where TKPROF output may be misleading. In the OTN thread, the original poster (OP) started the thread by asking a simple question about an execution plan that appeared [...]
Non-Specific Index Hints
January 24, 2012 (Modified January 25, 2012) As I read the “Troubleshooting Oracle Performance” book for the second time a couple of months ago, I made note on page 385 that it was possible to specify table columns in an index hint, rather than specifying specific index names (or just specifying the table name). This might be [...]
Finding the Next Primary Key Value, a Pre-fixed Solution
January 20, 2012 A request for assistance came in from an ERP mailing list. The original poster (OP) is running an unspecified version of Oracle Database 9i, and is in need of a solution to generate new part numbers with prefixed characters that describe the type of part, followed by a sequential number that is [...]
What Number Immediately Follows 3,999? Oracle Database Refuses to Answer
January 16, 2012 I put together a test case to demonstrate how the physical reads autotrace statistic could exceed the consistent gets autotrace statistic if a single-pass or multi-pass workarea execution were performed during the execution of the SQL statement. If you are interested, you can see the test case in this recent OTN thread. [...]
OT: Do Search Terms Describe the Visitor?
January 12, 2012 I thought that I would start this slightly off topic blog article with a bit of humor. Seven months ago I wrote a blog article that refuses to move from the first position in the most visited articles on this blog. In the process of trying to understand why a mathematics focused article [...]
Dumping Trace Events – What You See is Not Necessarily What You Get
January 9, 2012 A recent thread in the comp.databases.oracle.server Usenet group brought back memories of when Randolf Geist and I worked on the two chapters for the “Expert Oracle Practices” book. In the chapters, among other things, we demonstrated how to enable 10046 trace files for sessions using various approaches (some of the approaches change the SQL_TRACE, SQL_TRACE_WAITS, [...]
Faulty Quotes 7 – Deadlock Kills Sessions?
January 4, 2012 (Back to the Previous Post in the Series) I thought that I would begin this blog article with a bit of a test case that demonstrates a very simple deadlock between two sessions. In Session 1 (create the test table with a primary key and insert a single row): CREATE TABLE T1 ( [...]
