The short answer to this question is “yes”, it does. Unfortunately, the distribution costs are not externalized through the execution plans and, as a result, this limitation (yes, it is really a limitation in the current implementation, not a bug) confuses everyone that carefully look at the information provided in an execution plan of a […]
Edition-Based Redefinition
Upgrading critical applications can be very difficult. One of the main problems is that for reasons of availability, long downtimes cannot be periodically scheduled. Therefore, for such applications, it is desirable to implement online upgrades. This requires that the application in question, as well as any software used by the application (e.g. the database engine) […]
Join Elimination
In some specific situations the query optimizer is able to completely avoid executing a join even if a SQL statement explicitly calls for it. Two are the cases currently covered by this optimization technique, which is called join elimination. The first one was introduced in Oracle Database 10g Release 2, the second one in Oracle […]
Does CREATE INDEX Gather Global Statistics?
You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of […]
Instance Caging
Instance caging is another small but useful feature of Oracle Database 11g Release 2. Thanks to it the database resource manager is able, for the first time, to limit the number of CPUs that can be used by a given instance. (By the way, note that this limit has no “impact” on the number of […]
Zero-Size Unusable Indexes and the Query Optimizer
Zero-size unusable indexes and index partions is a small but useful feature of Oracle Database 11g Release 2. Simply put, its aim is to save space in the database by immediately releasing the segment associated to unusable indexes or index partitions. To illustrate this, let’s have a look to an example… Create a partitioned table, […]
Hints for Direct-path Insert Statements
Up to Oracle Database 10g Release 2, direct-path inserts are supported only by INSERT INTO … SELECT … statements (including multitable inserts), MERGE statements (for the part inserting data), and applications using the OCI direct-path interface (for example, the SQL*Loader utility). At the statement level two methods are available to specify that a direct-path insert […]
Ad: Oracle Database 11g Release 2 Seminars
The company I work for, Trivadis, organizes a series of 1-day seminars covering the most important features of Oracle Database 11g Release 2. The following dates and locations are currently planed (some more might be added later on): 2009-12-10 Zurich (CH) 2010-01-19 Frankfurt (DE) 2010-01-20 Munich (DE) 2010-02-02 Stuttgart (DE) 2010-02-04 Vienna (AT) My involvement […]
Script to Download 11gR2 Documentation
I don’t know you, but I like to work with the PDF version of the documentation. The problem is that the PDF files have names that are not very useful (or do you remember that the SQL Reference Guide is named e10592.pdf?). Hence, when I download the documentation, I like to store each PDF with […]
Deferred Segment Creation
As you know Oracle Database 11g Release 2 was just released. As a result, it is time to start a series of post about some new features. I’ll start with one that is not really related to performance… In fact, this post is a kind of follow-up to a comment written by Bernd Eckenfels about […]