Solved

How to quickly enable constraint with ENABLE VALIDATE for a big table in 12c

Posted on 2016-10-24
13
42 Views
Last Modified: 2016-10-27
Have a relatively big table T1 with 200 million rows. Its FK points to a table T2 with 10 millions. In 11gR2, it took 3 hours to enable the FK constraint, but in 12c, it takes days to do it. T1 and T2 in the 2 environments were created with the same setting.
Can any gurus shed some light on it? How to speed up the valid process in 12c? Thanks in advance.
0
Comment
Question by:jl66
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 125 total points
ID: 41856999
The first thing I would look for is a missing index on T2.  Whatever the foreign key field(s) is that is referenced in T2 by T1 should have an index.
0
 
LVL 23

Assisted Solution

by:David
David earned 125 total points
ID: 41857058
Following that line of thought, are the indexes all valid? Are the table statistics current? And there's always the obvious question, what's different? Is something running against the 12C database that did not exist before?
0
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 250 total points
ID: 41857130
enable it with novalidate first

from that point onwards, only valid entries will get in
1
 

Author Comment

by:jl66
ID: 41857406
Thanks a lot for the info.
1) T1, T2 on the 2 environments are identical. The indexes are valid.
2) I tried to enable it with novalidate, but it is also very slow.

In 12c, there are no users who perform anything.

Which parts can I check to find the culprit?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41857428
Quite possible that you are waiting on a table lock then.
0
 

Author Comment

by:jl66
ID: 41857457
johnsone,
Yes. But no users used it then. How to fix this?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jl66
ID: 41857462
BTW, it took much longer time in 12c than 11gr2 if I do simple query: select count(1) from T1;
0
 

Author Comment

by:jl66
ID: 41857518
Further, I noticed the explain plans for the same query against the 2 databases are different.
12c:
...
2 |   INDEX FULL SCAN| IDX1

11gr2:
...
2 |   BITMAP CONVERSION COUNT     |                |   201M| 19049   (1)| 00:00:02 |
3 |   BITMAP INDEX FAST FULL SCAN| IDX2

Can any gurus share some knowledge on forcing to execute certain plan?

Both IDX1 and IDX2 are created in 11g and 12c databases.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41857572
To me it looks like an indexing issue.  To validate a constraint it shouldn't be doing a full scan on the index.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41858063
how much physical memory do you have
and how much do you allocate to the database ?

if you have allocated more than you have physical, it will be swapping
0
 

Author Comment

by:jl66
ID: 41859231
Total physical memory is 36GB, among which 6GB is for this database. There are the other databases on the same server. The total memory for databases is less than a half of 36GB.
0
 
LVL 23

Expert Comment

by:David
ID: 41859342
jj, did you ever happen to look into the optimizer statistics?
0
 

Author Closing Comment

by:jl66
ID: 41862539
Thanks. The kill shot is that the storage parameters are adjusted, which made huge difference.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Insert and update a row at the same time 4 52
Oracle Syntax 8 56
automatic email alert 1 41
PL SQL Search Across Columns 4 20
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now