Search

Top 60 Oracle Blogs

Recent comments

11.2.0.2

Dynamic Sampling - Public Synonyms and 11.2.0.2

This is just a short heads-up note regarding a bug that obviously has been introduced with 11.2.0.2: If you happen to have a public synonym for a table that is called differently than the original object then dynamic sampling will not work in 11.2.0.2.

The reason is that the generated query used for the dynamic sampling does not resolve the synonym name properly - it resolves the object owner but uses the synonym name instead of the actual table name. The same issue happens by the way when using a private synonym, however the query is then still valid and works even when using the synonym name.

The bug can only be reproduced in 11.2.0.2, in all previous versions including 11.2.0.1 the synonym resolution seems to work as expected for the dynamic sampling query, so it seems to be a problem introduced in that patch set.

Scalar subquery unnesting

Here is a nice example of what Oracle 11.2.0.2 is able to do with a subquery inside an expression. It can unnest it – that is a new 11.2.0.2 feature of the transformation part of the CBO.

Flashback Query "AS OF" - Tablescan costs

This is just a short note prompted by a recent thread on the OTN forums. In recent versions Oracle changes the costs of a full table scan (FTS or index fast full scan / IFFS) quite dramatically if the "flashback query" clause gets used.

It looks like that it simply uses the number of blocks of the segment as I/O cost for the FTS operation, quite similar to setting the "db_file_multiblock_read_count" ("dbfmbrc"), or from 10g on more precisely the "_db_file_optimizer_read_count", to 1 (but be aware of the MBRC setting of WORKLOAD System Statistics, see comments below) for the cost estimate of the segment in question.

This can lead to some silly plans depending on the available other access paths as can be seen from the thread mentioned.

Transitive Closure - Outer Joins

The Cost Based Optimizer (CBO) supports since at least Oracle 9i the automatic generation of additional predicates based on transitive closure.

In principle this means:

If a = b and b = c then the CBO can infer a = c

As so often with these optimizations the purpose of these automatically generated additional predicates is to allow the optimizer finding potentially more efficient access paths, like an index usage or earlier filtering reducing the amount of data to process.

ASSM bug reprise - part 2

Introduction

In the first part of this post I've explained some of the details and underlying reasons of bug 6918210. The most important part of the bug is that it can only be hit if many row migrations happen during a single transaction. However, having excessive row migrations is usually a sign of poor design, so this point probably can't be stressed enough:

If you don't have excessive row migrations the bug can not become significant

Of course, there might be cases where you think you actually have a sound design but due to lack of information about the internal workings it might not be obvious that excessive row migrations could be caused by certain activities.

ORA-14404 / 14405 and Deferred Segment Creation

Have a look at the following SQL*Plus output snippet:

SQL>> select count(*) from dba_segments where tablespace_name in ('TS_TO_DROP', 'TS_TO_NOT_DROP');

COUNT(*)
----------
0

SQL>> drop tablespace ts_to_drop including contents and datafiles cascade constraints;
drop tablespace ts_to_drop including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

How is it possible to get a message about an object that is supposed to have partitions in the tablespace to be dropped and in some other tablespaces if there is no segment contained in the tablespace to be dropped?

May be a dictionary corruption, or a problem with the recyclebin?

Integration of Editions with Services in 11.2.0.2…

There’s a neat new feature for editioning in 11.2.0.2 that allows you to associate an edition with a service. I’ve added it to the end of my Edition-Based Redefinition article here.

Cheers

Tim…




Segment Creation on Demand (Deferred Segment Creation) in Oracle 11.2.0.2…

It looks like 11.2.0.2 has improved most of the original shortfalls of segment creation on demand that were present in 11.2.0.1.

Cheers

Tim…




RAC One Changes in 11.2.0.2 Patchset

Oracle patchsets used to be simple - they were bugfixes only; no additional functionalities were added. Oracle added stuff quietly in 10.2.0.4 patchset (the workload capture); but that was part of a new functionality anyway.


The 11.2.0.2 patchset changed all the rules. Several new functionalities were added to the patchsets. Several functionalities have been added to RACOne. Unfortunately they didn't make it to the manuals. Some of them I discovered only recently. If you have been using RACOne, or considering it, you will be delighted to know these.

Things worth to mention and remember (II) - Parallel Execution Control 2

Continuing from the previous installment of this series I'll cover in this post some of the inevitable classics regarding Parallel Execution Control. So forgive me if you're bored by the repetition of known facts - however I still see these things too often used incorrectly, therefore I decided: This is worth to mention and remember!

- Up to and including version 10.2 PARALLEL (without any parameters) and NOLOGGING are valid keywords only in DDL commands

- Applies to all versions: NOLOGGING can not be used as a hint. It can only be specified as part of DDL, for example ALTER INDEX ... REBUILD PARALLEL NOLOGGING.