My Oracle Support Banner

Very slow "ALTER TABLE ... COLUMN NOT NULL" on exadata (Doc ID 2625465.1)

Last updated on JULY 27, 2020

Applies to:

Oracle Exadata Storage Server Software - Version 11.2.1.2.0 to 11.2.1.2.0 [Release 11.2]
Linux x86-64

Symptoms

 "ALTER TABLE ... COLUMN NOT NULL" take long time on exadata if the table is very big.

From sql trace, you can see full table scan occurred on the table and smart scan is not used.

Example
-------------------------------------------------------
SQL ID: 2u49x6t549ff0 Plan Hash: 2832004635

select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3
from
"TYOUTAN"."TBLTEST" A where( "A" is null)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0       1200          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0       1200          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       0          0          0  TABLE ACCESS FULL TBLTEST (cr=1200 pr=0 pw=0 time=4464 us starts=1 cost=344 size=23 card=1)
-------------------------------------------------------

Changes

 None

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.