This content has been marked as final. Show 5 replies
I see there is still a bug registrated about optimize_index in 22.214.171.124, "Bug 11856000: CTX_DDL.OPTIMIZE_INDEX MUCH SLOWER AFTER UPGRADE TO 11.2". If this is a bug which hit also your case is uncertain. In this bug is stated that the optimize runs faster if the dr$ tables and indexes are analyzed, but it is still slower than 10.2.
How do you optimize, which level (full, rebuild or other)? How big are your memory setting, see
Defaults can be too low, so maybe a higher setting also helps (adjust the size to the possibilities of your database)
select * from ctx_parameters where par_name like '%_INDEX_MEMORY';
Also, be aware that the $S table is done serial. So with a lot of data it can be time consuming.
exec ctx_adm.set_parameter ('max_index_memory','1024M'); exec ctx_adm.set_parameter ('default_index_memory','256M');
Herald ten Dam
I would be glad to attach an AWR report - but can't find this functionality here.
My TOP-SQL is this:
select /*+DYNAMIC_SAMPLING(0) */ sdata_id, sdata_last, sdata_data from "SEQDEV"."DR$GEN_GES_DESCRIPTION_CTX_I$S" where sdata_id = :sdata_id and sdata_last >= :sdata_last for update order by sdata_id, sdata_last
Every execution lasts around 2 minutes. The IOT-index on sdata_id and sdata_last is the Top segment by DB Block Changes in this hour (14.763.776).
The $I table has been optimized in around 40 minutes. It has 138.598.683 rows! In contrast to that the $S table has only 644813 rows. It is running for more than 18 hours and has optimized only 52.000 records so far !
My conclusion is: The optimize process still needs to be optimized for the $S table (CDI feature). Correct me if I'm wrong.