My Oracle Support Banner

Why Is The Cost Of An "AS OF" Query 3 To 4 Times Higher Than The Original Query? (Doc ID 2794392.1)

Last updated on MARCH 30, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Query execution plan changes when the AS OF clause is added to a query and the query plan cost, consequently can be much higher. The Elapsed time for the query and the access paths may well be different also.

10053 trace shows lines like

Considering Query Transformations on query block SEL$1 (#0)
 **************************
 Query transformations (QT)
 **************************
 CBQT: copy not possible on query block SEL$1 (#0) because as-of table  
 CBQT bypassed for query block SEL$1 (#0): Cannot copy query block

 

The execution plan will change and the costs may be much higher. Using Outline hints or standard hints or profiles will not solve the problem.

Changes

 A simple query such as

SELECT /*+ FAST - no AS OF clause */ count(*), sum(quantity), min(quantitymin) from Table_1910 where ...

is changed to

SELECT /*+ SLOW - AS OF clause */ count(*), sum(quantity), min(quantitymin) from Table_1910 AS OF SCN timestampt_to_scn(sysdate) where ...

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.