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 APRIL 17, 2023

Applies to:

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

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