jl66
asked on
How to quickly enable constraint with ENABLE VALIDATE for a big table in 12c
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.
Can any gurus shed some light on it? How to speed up the valid process in 12c? Thanks in advance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Quite possible that you are waiting on a table lock then.
ASKER
johnsone,
Yes. But no users used it then. How to fix this?
Yes. But no users used it then. How to fix this?
ASKER
BTW, it took much longer time in 12c than 11gr2 if I do simple query: select count(1) from T1;
ASKER
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.
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.
To me it looks like an indexing issue. To validate a constraint it shouldn't be doing a full scan on the index.
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
and how much do you allocate to the database ?
if you have allocated more than you have physical, it will be swapping
ASKER
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.
jj, did you ever happen to look into the optimizer statistics?
ASKER
Thanks. The kill shot is that the storage parameters are adjusted, which made huge difference.
ASKER
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?