2025甲骨文全球AI大会：New In The Optimizer For 26ai

A

wor

d[LRN1427]What’s

Newinthe

Oracle

Optimizer?OracleAI

Database

26aiNigel

BaylissOptimizer

Product

ManagerWhat’s

Newinthe

Oracle

Optimizer1

SQLAnalysis

Report2

SQL

Diagnostic

Report3

SQL

Plan

Management4

Automatic

Error

Mitigation5

Optimizer

Support

forAIVector

Indexes6

Other

Optimizer-related

Enhancements2

Copyright

©

2025,Oracle

and/oritsaffiliates

|SQLAnalysis

ReportHelping

developerswrite

better

queriesPoorly-formed

SQL

statements

caninadvertently•Prevent

accurate

cardinality

estimates

(leadingto

compromised

execution

plan

selection)•Preventthe

use

of

optimized

access

paths

(such

as

an

index)•Missingtable

joins,

leadingto

Cartesian

products

(sometimes

‘fixed’

with

SELECT

DISTINCT!)•Implicit

type

conversionsProblems

likethis

can

be

easyto

miss•

Thousand-line

SQL

statements•Many

table

joins•The

‘not

so

obvious,’

for

example

LIKE

'%mystring%'

instead

of

LIKE

'mystring%'SQLAnalysis

Reporting4Copyright

©

2025,

Oracle

and/or

its

affiliates

|Thequeryblockcontains

UNIONwhich

may

be

expensive.ConsiderusingUNIONALLif

duplicates

are

allowed

oruniquenessisguaranteed.selectt1.p_category,t2.tpmethodfromproductst1,

products

t2whered_category

||

'_1'=andt2.method_typ

!=

'SEA'

unionselectt3.p_category,t4.tpmethodfromproductst3,

sources

t4wheret3.scid

=t4.scidThequeryblock

has

1

cartesianexpensive.Consideradding

joindisconnectedtablesorviews.Thefollowingcolumns

have

useas

keys

in

index

range

predicates."PROD_CATEGORY"'SFD_1'Thefollowingcolumnshavepredicateswhich

precludetheir

useaskeys

in

index

range

scan.Consider

rewriting

thepredicates."METHOD_TYP"productwhichmay

beconditionsor

removingthepredicateswhichprecludetheir

scan.Consider

rewritingtheandt4.carrier

=

'AAC'andt4.s_area

=

1;

Thefollowingcolumns

have

useas

keys

in

index

range

predicates."S_AREA"SQLAnalysis

Reportpredicateswhichprecludetheir

scan.Consider

rewritingthe5Copyright©

2025,

Oracle

and/or

its

affiliates

|select

t1.p_category,t2.tpmethodfrom

products

t1,

products

t2where

t1.prod_category

||

'_1'=

'SFD_1'and

t2.method_typ

!=

'SEA'unionselect

t3.p_category,t4.tpmethodfrom

products

t3,

sources

t4where

t3.scid=

t4.scidand

t4.carrier

=

'AAC'and

t4.s_area

=

1;

CHAR

column9

-access("T4"."CARRIER"='AAC')SQLAnalysisReport(identified

byoperation

id/Query

Block

Name/ObjectAlias):-------------------------------------------------------------------------------1

-

SET$1-Thequeryblock

contains

UNION

which

may

be

expensive.ConsiderusingUNIONALL

ifduplicates

are

allowed

oruniquenessis

guaranteed.3

-

SEL$1-Thequery

block

has

1

cartesian

productwhich

may

beexpensive.Consideraddingjoin

conditions

or

removingthe

disconnectedtablesorviews.4

-

SEL$1/

"T1"@"SEL$1"-Thefollowingcolumns

havepredicateswhich

preclude

their

useaskeys

in

index

range

scan.

Consider

rewritingthepredicates."PROD_CATEGORY"Id

|Operation

|Name|

Rows|Bytes

|Cost

(%CPU)

|

Time0|

SELECT

STATEMENT||||

91

(100)|1HASHUNIQUE104778382691

(13)00:00:012UNION-ALL104778382691

(13)00:00:013MERGE

JOINCARTESIAN104768380821

(15)00:00:01*

4TABLEACCESS

FULLPRODUCTS10403

(0)00:00:015BUFFER

SORT1023409218

(17)00:00:01*

6TABLEACCESS

FULLPRODUCTS102340922

(0)00:00:01*

7HASH

JOIN1186

(17)00:00:01*

8TABLEACCESSBY

INDEX

ROWID

BATCHED

RANGE

SCANSOURCESSRC

CARR

IDX1142

(0)00:00:01*

9

10|

TABLEACCESS

FULL|

PRODUCTS|

1024|

4096|

3

(0)|

00:00:01SQLAnalysis

ReportSELECT*

FROMtable(DBMS_XPLAN.8

-

SEL$2/

"T4"@"SEL$2"-Thefollowingcolumns

havepredicateswhich

preclude

their

useaskeys

in

index

range

scan.

Consider

rewritingthepredicates."S_AREA"6

-

SEL$1/

"T2"@"SEL$1"-Thefollowingcolumns

havepredicateswhich

preclude

their

useaskeys

in

index

range

scan.

Consider

rewritingthepredicates."METHOD_TYP"4

-filter("T1"."PROD_CATEGORY"||

'_1'='SFD_1')6

-filter("T2"."METHOD_TYP"<>'SEA')

"SCID"="T4"7

-access("T3"

.."SCID")DISPLAY_CURSOR());6

Copyright

©

2025,Oracle

and/oritsaffiliates

|PredicateInformation

(identifiedby

operation

id):8

-filter(TO_NUMBER("T4"."S_AREA")=1)||||||||||||00:00:011

(0)INDEX1SQLAnalysis

Report–

Checking

Cursor

CacheSQL8

-SEL$2

/

"T4"@"SEL$2"-The

following

columns

have

predicates

which

preclude

theiruseaskeysin

index

range

scan.

Consider

rewriting

thepredicates."S_AREA"SQL>selectsql_id,child_number,sql_textfromv$sql

a,table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>a.sql_id,cursor_child_no=>a.child_number))bwhereb.plan_table_outputlike

'%useas

keys

in

index

range

scan%'andsql_text

like

'%products%'orderby

sql_id,child_number/6drrgyb7cfpjy0selectt3.p_category,t4.tpmethodfrom

productst3,

sourcest4wheret3.scid=

t4.scidandt4.carrier

=

'AAC'andt4.s_area

=

:bind1var

bind1

numberSQLID

CHILD_NUMBER

SQL_TEXT7Copyright

©

2025,

Oracle

and/or

its

affiliates

|In

SQL

MonitorActive

Reports8Copyright

©

2025,

Oracle

and/or

its

affiliates

|SQL

Diagnostic

ReportHelping

developersand

DBAsdiagnose

SQL

performanceissuesSQL

Diagnostic

ReportDBMS_SQLDIAG

has

a

newfunction

called

REPORT_SQL•Generate

an

HTML

deep-level

diagnostic

report

for

a

SQL

statement•No

requirement

to

install

additional

objects/components•Plan

history,

non-default

database

parameters,

stats

history,

indexes,

and

more

PL/SQLfunction:•DBMS_SQLDIAG.REPORT_SQL

(sql_id,directory,level)

RETURNS

CLOBExample

(to

output):•exec

:report

:=dbms_sqldiag.report_sql('9y5vtn2knayhs');select

:report

report

from

dual;Example

(to

a

directory):•exec

:report

:=dbms_sqldiag.report_sql('9y5vtn2knayhs',

'MYDIR');

Significant

enhancements

are

planned

for

release

soonBackportedto

Oracle

Database

19c

(Autonomous

and

RU19.28)In

RU

19.28

andADB

19c10Copyright

©

2025,Oracle

and/or

its

affiliates

|SQL

Diagnostic

Report11Copyright

©

2025,Oracle

and/or

its

affiliates

|SQL

Plan

ManagementPlan

stability

and

resolving

SQL

statement

performance

regressionsSQL

Performance

Metricsinthe

DatabaseSQL

Cursor

Cache13Copyright

©

2025,Oracle

and/or

its

affiliates

|TheAutomatic

SQL

Tuning

Set

-

a

repository

for:•Application

SQL

statements•SQL

Execution

statistics•SQL

Execution

plansDBA_SQLSET_STATEMENTSTheAutomatic

SQLTuning

Set

(ASTS)Oracle

Database

19cPeriodic

Background

Task

(15mins)SQL

Cursor

Cache14Copyright

©

2025,Oracle

and/or

its

affiliates

|TheAutomatic

SQLTuning

Set

(ASTS)Oracle

Database

19cThe

automatic

SQLtuning

set

is

part

ofthe

database

infrastructure•Criticalfor

automation

in

OracleAutonomous

AI

Database,

but

also

available

in

Oracle

Enterprise

Edition

Overtime,

it

will

capture

most

application

SQL

statements•Some

exclusions,

and

only

SQL

statements

with

execution

plans•Itwill

throttleto

avoid

capturing

largevolumes

of

dynamic

SQLLong-term

record

of

SQL

statements,

plans,

and

SQL

performance

metrics!•Evenwithout

automation,

it’s

useful

for

diagnosing

SQL

performance

issues•Purges

unused

SQL

after

53weeksA

system-maintained,

SYS-owned

SQLtuning

set

called

SYS_AUTO_STS•Contents

can

be

seen

in

DBA_SQLSET_STATEMENTS15Copyright

©

2025,

Oracle

and/or

its

affiliates

|Manually

Creating

SQL

Plan

BaselinesBefore

OracleAI

Database

26aiLoading

plans•DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE•DBMS_SPM.LOAD_PLANS_FROM_AWR•DBMS_SPM.LOAD_PLANS_FROM_SQLSET

SPM

Evolve•DBMS_SPM.CREATE_EVOLVE_TASK•DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

There’s

also

pack/unpack

for

use

with

Datapump16Copyright

©

2025,Oracle

and/or

its

affiliates

|DBMS_SPM

has

a

newfunction

calledADD_VERIFIED_SQL_PLAN_BASELINE•Loads

plansfrom

cursor

cache,AWR,

and

automatic

SQLtuning

set

into

SQL

plan

management•Creates

new

SQL

plan

baselines•Identifies

the

best-performing

plan,

and

accepts

it

in‘one

shot’•Available

in

OracleADB19c•Backportedto

Oracle

Database19c

RU19.22

and

patch

34534504

is

available

for

earlier

RUsPL/SQLfunction:•DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE

(sql_id

IN

VARCHAR)RETURNS

CLOBUsage:•exec

:report

:=dbms_spm.add_verified_sql_plan_baseline('9y5vtn2knayhs');select

:report

report

from

dual;Quickly

IdentifyandAccept

Optimal

PlansOracleAI

Database

26ai17Copyright©

2025,Oracle

and/or

its

affiliates

|PlanHash

ValuePlan

NameReproducedAcceptedSource4201419845SQL_PLAN_czx70u327q1zwfa6c9445YESYES

SQLTUNING

SET1353559805SQL_PLAN_czx70u327q1zw50adaefdYESNO

CURSORCACHESQL

Handle

:SQL_cff4e0d0c47b07fcSQL

Text

:select/*SPM_TEST_QUERY_Q1

*/

sum(t1.amount),sum(t2.amount)from

sales_area1

t1,sales_area2

t2wheret1.sale_code=

t2.sale_codeandt1.sale_type

=

10Quickly

IdentifyandAccept

Optimal

PlansOracleAI

Database

26aiexec

:report

:=dbms_spm.add_verified_sql_plan_baseline('9y5vtn2knayhs');select

:reportreportfrom

dual;SQLPlanBaselinesverifiedfor

SQL

ID:

9y5vtn2knayhs18Copyright

©

2025,Oracle

and/or

its

affiliates

|SQL

Plan

ManagementSPMfull

automationAutomatic

SQL

Plan

ManagementOracle

Database

19c

(more

onthis

later

in

alater

slide)The

scenario:•

A

SQL

statement

has

acceptable

performance•

A

change

inthe

database

environment

or

data

change

causesthe

execution

planto

change•The

new

SQL

execution

plan

performs

poorly

The

application

has

reusable

SQL

statements•Non-dynamic,

bind

variablesA

backgroundtask

repairsthe

SQL

performance

regression:•Identify

SQL

statements

consuming

large

amounts

of

system

resources

(AWR

andASTS)•Forthese

SQL

statements

find

alternative

plans

inASTS•Test

executethe

new

and

alternative

plan

using

a

BACKGROUND

SPM

evolve

process•Create

SQL

plan

baseline(s)

and

acceptthe

best

plan20Copyright

©

2025,Oracle

and/or

its

affiliates

|Automatic

SPMin

Oracle

Database

19cAutomatic

BACKGROUNDtaskwakes

up

periodicallyand

identifies

resource-consuming

SQL[2]

IdentifyAlternativePlan[4]

EvolveTest

Execute

andCompare[3]

CapturePlans

inSPMAWR

andAutomatic

SQLTuning

SetSQL

Plan

Management(Plan

History)[1]

IdentifyResource-intensive

SQLAutomatic

SQL

Tuning

Set[5]Accept

the

Best

PlanSQL

Plan

BaselinesPeriodic

BackgroundTask

(1

hour)21Copyright

©

2025,Oracle

and/or

its

affiliates

|Automatic

SQL

Plan

ManagementChallengeswith

backgroundverificationPerformance

analysis/verification

is

asynchronous

withtheforeground

process•There’s

a

lagTesting/verifying

long-running

SQL

statementsThe

same

plan

may

not

be

reproduced

inthe

background

process22Copyright

©

2025,Oracle

and/or

its

affiliates

|Real-time

SQL

Plan

ManagementOracleAI

Database

26aiThe

scenario:•

A

SQL

statement

has

acceptable

performance•

A

change

inthe

database

environment

or

data

change

causesthe

execution

planto

change•The

new

SQL

execution

plan

performs

poorly

For

reusable

SQL

statements•Non-dynamic,

bind

variablesReal-time

SPM

repairsthe

SQL

performance

regression:•Detect

a

plan

change

(automatic

SQLtuning

set

lookup)•Executethe

new

plan

and

checkif

its

performance

is

better/worse/similar

to

a

previous

plan•The

better

plan

is

accepted

as

a

SQL

plan

baseline•The

SQL

statement

subsequently

usesthe

better

plan,

controlled

by

SQL

plan

management23Copyright

©

2025,Oracle

and/or

its

affiliates

|Lookforthe

same

SQLstatementwith

a

different

planReal-time

SPMDetect

a

plan

changeTheAutomaticSQL

Tuning

Set(ASTS)Detect

a

planchange:24Copyright

©

2025,Oracle

and/or

its

affiliates

|Hard

ParseReal-time

SPMExecutethe

new

plan

usingthe

FOREGROUND

processTheAutomaticSQL

Tuning

Set(ASTS)inthe

FOREGROUND

processExecutethe

new

plan25Copyright

©

2025,

Oracle

and/or

its

affiliates

|ExecuteReal-time

SPMFigure

out

ifthe

new

plan

causes

a

SQL

performance

regressionCompare

Performance

StatisticsAlso,

sanity

checktheASTS

plan

costExecuted

SQL

Tuning

Set(ASTS)TheAutomatic26Copyright

©

2025,Oracle

and/or

its

affiliates

|Real-time

SPMAcceptthe

best

plan,

preventinga

regressioniftheASTS

plan

has

better

performanceTheAutomaticSQL

Tuning

Set(ASTS)sowe

have

prevented

a

performance

regressionInthis

case,the

new

planwas

worseThe

Best

Plan

is

AcceptedSQL

Plan

Baselines27Copyright

©

2025,Oracle

and/or

its

affiliates

|Ifthe

optimizer

choosesthis

plan

again,butthe

other

(ASTS)

planwas

previously

acceptedReal-time

SPMReverseverificationTheAutomaticSQL

Tuning

Set(ASTS)SQL

Plan

Baselines28Copyright

©

2025,Oracle

and/or

its

affiliates

|Ifthe

optimizer

choosesthis

plan

again,butthe

other

(ASTS)

planwas

previously

acceptedThen

executethis

plan

asexpected,

but

alsocheck

performance

statsTheAutomaticSQL

Tuning

Set(ASTS)Real-time

SPMReverseverificationSQL

Plan

Baselines29Copyright

©

2025,Oracle

and/or

its

affiliates

|Real-time

SPMReverseverificationTheAutomaticSQL

Tuning

Set(ASTS)Reversethe

decision

if

necessarySQL

Plan

Baselines30Copyright

©

2025,Oracle

and/or

its

affiliates

|DemoVideo31Copyright©

2025,Oracle

and/or

its

affiliates

|Enable

automatic

SQL

plan

management

(backgroundtask)•Previous

default

for

OracleAutonomous

AI

Database•

Available

from

RU

19.22•exec

dbms_spm.configure('AUTO_SPM_EVOLVE_TASK',

'ON')

Enable

real-time

SQL

plan

management•OracleAutonomous

AI

Database

is

migratingtoAUTO

(almost

complete)•exec

dbms_spm.configure('AUTO_SPM_EVOLVE_TASK',

'AUTO')

Disabling

(OFF

isthe

default

in

self-managed

database)•exec

dbms_spm.configure('AUTO_SPM_EVOLVE_TASK',

'OFF')

Viewingthe

current

mode•select

parameter_name,

parameter_value

fromDBA_SQL_MANAGEMENT_CONFIGwhere

parameter_name=

'AUTO_SPM_EVOLVE_TASK';Automatic

SQL

Plan

Management32Copyright

©

2025,Oracle

and/or

its

affiliates

|Upgrade

database

in

place

or

moveASTSfrom

one

databaseto

another•Pack,

export,

import

and

unpack

SYS_AUTO_STSEnable

automatic

SQL

plan

management

in

upgraded

database•

You

can

use

background

verified

or

real-timeIf

you’re

upgrading

an

OracleAutonomous

Database,

real-time

SPM

protectsyou

without

manual

stepsEnablethe

automatic

SQLtuning

set

in

pre-upgrade

Oracle

Database19c

(EE)begindbms_auto_task_admin.enable(client_Name=>

'Auto

STS

Capture

Task',operation=>

NULL,window_name=>

NULL);end;/Mitigating

Plan

Change

RisksWhen

Upgradingor

MigratingProtecting

SQL

performanceduring

upgradesfrom

Oracle

Database19c

RU19.22

(EE)33Copyright©

2025,Oracle

and/or

its

affiliates

|Summary:Automatic

SQL

Plan

ManagementAddressing

SQL

performance

regressions

without

manual

interventionCreates

SQL

plan

baselines

automaticallyProtects

upgrades

in

OracleAutonomous

Database

without

requiring

anymanual

intervention•For

example,

ifyou

upgrade

an

OracleAutonomous

Database19cto

OracleAutonomous

AI

Database

26ai,

you

don’t

needto

do

anythingto

gain

protectionfrom

real-time

SPM34Copyright

©

2025,Oracle

and/or

its

affiliates

|Automatic

ErrorMitigationResolving

critical

errors

automaticallyAuto

Error

Mitigation

Use

CaseIt’s

rare,

but:•

A

new

application

SQL

statementfails

with

ORA-600

onthe

production

system•

A

pre-existing

SQL

statement

experiences

an

ORA-600

after

a

change

is

madetothe

production

system

Without

automatic

error

mitigation:•Thefailure

may

occur

everytimethe

application

attemptsto

parse

and

executethe

SQL

statement•

The

application

may

suffer

an

outage•

With

automatic

error

mitigation:•

Thefailure

is

repairedtransparently

–

no

manual

intervention•No

exception

seen

bythe

application•No

application

outage36Copyright

©

2025,Oracle

and/or

its

affiliates

|Automatic

Error

Mitigation

DemoVideo37Copyright©

2025,Oracle

and/or

its

affiliates

|Automatic

Error

MitigationWorks

around

optimizer-related

(compile-time)

ORA-600

issuesWithout

manual

interventionA

mitigation

strategy

is

initiated

inthe

user

sessionthat

encountersthe

error

conditionThe

application/user

will

not

seethe

ORA-600

ifthe

mitigation

strategy

succeedsAn

incident

is

generated

(for

bugfiling

purposes)It

is

nota

substitute

forfixing

bugs!A

SQL

Patch

is

generatedto

repairthe

SQL

statement•DBA_SQL_PATCHESAuto

error

mitigation

details

arewrittentothe

dictionary

and

exposed

to

aid

diagnosis•DBA_SQL_ERROR_MITIGATIONS38Copyright

©

2025,Oracle

and/or

its

affiliates

|OptimizerSupportforAIVector

IndexesVector

IndexesVectors

represent

points

in

multi-dimensional

spaces•

For

example,

when

encoding

documentsthey

can

represent

wordfrequenciesComputing

VECTOR_DISTANCE

inthe

databasetofindthetop

matches

with100%

accuracy

can

be

slowApproximate

indexes

are

usedtotrade-off

search

accuracyfor

search

speed•

Hierarchical

Navigable

Small

World

(HNSW)•

Inverted

Flat

(IVF)HNSW

indexes

are

in-memory

and

IVFindexes

are

storage-residentcreatevectorindexvector_idx1onmodel_tab(image_vector)organizationinmemory

neighborgraphdistance

cosinewithtargetaccuracy

95;40Copyright

©

2025,Oracle

and/or

its

affiliates

|Optimizer

SupportforAIVector

IndexesThe

optimizer

must

choose

whenandhow

tofilter

rowswhenvector

indexes

are

used

in

conjunction

with

relational

predicatesSELECTchunk_id,

chunk_dataFROMdoc_chunksWHEREdoc_id

=

1ORDERBYVECTOR_DISTANCE(chunk_embedding,

:query_vector,COSINE

)

FETCHAPPROXFIRST4

ROWS

ONLYWITH

TARGETACCURACY

80;41Copyright©

2025,Oracle

and/or

its

affiliates

|FilteringPre-filtering•Filter

rows

using

relational

predicatesthenjointhese

with

a

proximity

search

on

a

vector

index

In-filtering•Processthe

proximity

search

onthevector

index

and

filterthem

using

the

relational

predicates

Post-filtering•Processthe

proximity

search

onthevector

index

and

jointhe

results

with

a

vector

index42Copyright

©

2025,Oracle

and/or

its

affiliates

|Join-backvs

No-join-back

[HNSWonly]The

optimizer

may

chooseto

process

projected

columns

fromyour

selectlist

before

or

afterthe

similarity

search

operation•If

it

does

so

after,this

is

called

ajoin-back

operation•If

it

does

so

before,

it

is

called

a

no-join-back

operation•

Thedecisionismadeusing

thenewoptimizercostmodel

introduced

for

theHNSWindexJoin-back

circled

aboveNojoin-back43Copyright©

2025,Oracle

and/or

its

affiliates

|Creation

of

an

HNSW

generates

an

auxiliary

mapping

table

(ROWID,VECT_ID)Pre-filtering–when

relationalfilters

are

highly

selective•

Apply

relationalfilters

first•Filtered

rows

arejoinedtothe

auxiliary

mapping

table

before

the

HNSW

vector

index

identifies

the

relevant

vectorsIn-filtering–when

relationalfilters

are

less

selective•Traversethe

HNSW

index

first•For

each

identified

vector,filterthe

corresponding

basetable

rowHNSW

Indexes44Copyright

©

2025,Oracle

and/or

its

affiliates

|Pre-filtering–when

relationalfilters

are

highly

selective•Oncethe

optimizer

chooseswhich

centroid

partitionsto

scan,

it

evaluatesthe

filters

for

all

the

rows

in

those

partitions•Computes

the

distancetothe

query

vectorforthe

rowsthat

passed

the

filters.Post-filtering–when

relational

filters

are

less

selective•Oncethe

optimizer

chooseswhich

centroid

partitionsto

scan,

it

computesthe

distance

to

the

query

vector

for

all

the

rows

inthose

partitions•Oncethe

optimizer

findsthe

closest

rows,

it

evaluatesthefilter

for

those

rows

and

returns

only

the

ones

that

passthe

filtersIVF

Indexes45Copyright

©

2025,Oracle

and/or

its

affiliates

|Other

OptimizerEnhancementsImproving

performancetransparentlyOn

DemandAutomatic

IndexingOn

demandAPI•DBMS_AUTO_INDEXING.RECOMMEND

DML-aware

indexing•

The

cost

of

maintaining

indexes

on

“hot”tableswith

significant

DML

activity

can

be

detrimentalto

performance47Copyright©

2025,Oracle

and/or

its

affiliates

|NewTransformations

PDF

paper:Grouping,Subsumption,Subsumption

of

Views

and

Sub-queries

•

Replaces

multiple

views

or

sub-queries

with

one•

Performance

improves

because

of

a

reduction

intable

accesses

and

joinsGroup

By

Pushdownto

UnionAll

View•Pushes

group-by

intoviews

beforeUNIONALL

Group-by

Placement

Enhancements•

Allows

group-by

operationsto

occur

earlier•

Can

reduce

number

of

rowsthat

needto

bejoinedand

DisjunctiveJoin

Optimizations48Copyright

©

2025,Oracle

and/or

its

affiliates

|in

OracleYou

have

more

control

over

how

dynamicstatistics

is

usedwith

PL/SQLfunctionsAllowingthe

use

of

PL/SQL

duringdynamicsampling

canleadto

better

cardinality

estimates

and

better

plansYou

maywantto

disablethis

ifthe

PL/SQLtakes

along

time

to

executeControl

is

available

globallyorfor

specific

packagesandfunctions•

dbms_stats.set_global_plsql_prefs

('dynamic_stats','ON')•

dbms_stats.set_plsql_prefs

(user,'my_function',null,'dynamic_stats','OFF')select

count(*)from

table(my_package