My Practical Approach to Create Domain Index
In the past weeks, I have been challenging with the issue of creating domain index on one of our production databases. The problem we were experiencing is that host memory has been eaten up shortly after we started create index command as simple as below.
create index text_search_index
on searchable_attribute (attribute_text)
indextype is ctxsys.context
parameters (’datastore ctxsys.VISTA_CLOB_DATASTORE
storage ctxsys.vista_def_storage
lexer ctxsys.vista_default_lexer
wordlist ctxsys.vista_def_wordlist
memory 1024M
stoplist ctxsys.default_stoplist’)
parallel 4;
Successful tryout on development database
The script above was sent by application vendor and we were told that the creation of this domain index takes from 24 hours to 48 hours depending the size of indexable data. But we were still shocked by the enormous resource (CPU and Memory) consumption it introduced. After testing on our development database, we realized that our server is unaffordable for it. And then, we simply reduced the memory to 256M from 1024M and also disabled parallel parameter by changing degree to 1 from 4 (see below for changed sql statement).
create index text_search_index
on searchable_attribute (attribute_text)
indextype is ctxsys.context
parameters (’datastore ctxsys.VISTA_CLOB_DATASTORE
storage ctxsys.vista_def_storage
lexer ctxsys.vista_default_lexer
wordlist ctxsys.vista_def_wordlist
memory 256M
stoplist ctxsys.default_stoplist’)
parallel 1;
My attempt to create domain index with new setting seemed working even though it’s still expensive because performance degradation on development server didn’t make me uncomfortable. Eventually, the domain index was created successfully and the whole process took 44 hours.
Failed tryout on production database
Upon the success on development server, I took it for grant that we are going to get it done for sure. Unfortunately, I was surprised the memory consumption was increasing crazily. Only 30 minutes after the sql statement was started, the host memory utility hit over 95%, which pushed me to kill that process in no time. If there no exceptional issue, the index creation should be quite smooth because we got success on development server and the production server is much more powerful the development one both on speed and number of CPU, and size of memory.
To figure out the possible reasons which resulted in that, I completed the following comparison between development and production databases.
- oracle products and patches installed by using OPatch (command: opatch lsinventory –detail )
- database parameter and schema setting
But, the above comparison couldn’t give me a light because there is no difference between two databases. It also made sense to me because the development database was duplicated from production database weeks ago.
My persistent attempt to create index by changing parameter of memory and parallel kept causing memory problem. The typical appearance looks like below while we monitored this process with Unix command “prstat”.
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
25873 oracle 10763M 1694M cpu10 0 0 00:21:46 12% oracle/43
18709 oracle 1641M 1601M sleep 42 0 6:12:20 0.7% oracle/11
The SIZE column kept increasing crazily and shortly hit over 10G. Checking with PGA_ALLOC_MEM also showed that the threshold of 10G. And then, I didn’t hesitate to contact oracle support for it since it’s very likely a bug.
Approaching from Oracle Support
With acceptable delay, Oracle Support showed the finding in alert log file (listed below) and indicated that it’s a bug which could be eliminated by applying patch 7706062.
Tue Apr 27 09:23:35 2010
Errors in file /dbWEBCT/bdump/webct_p003_28942.trc:
ORA-00600: internal error code, arguments: [17087], [0×3BF7F0918], [], [], [], [], [], []
Tue Apr 27 09:23:36 2010
Errors in file /dbWEBCT/bdump/webct_p003_28942.trc:
ORA-07445: exception encountered: core dump [kksumc()+164] [SIGSEGV] [Address not mapped to object] [0×00000007F] [] []
ORA-00600: internal error code, arguments: [17087], [0×3BF7F0918], [], [], [], [], [], []
Tue Apr 27 09:31:19 2010
I believed we’ll be benefit from applying that patch. Unfortunately, the downtime of all oracle services on production server is not acceptable because we have more than one oracle production databases sitting on that server. What I’m thinking is that I won’t do that unless I exhausted all possible attempts on application level. My further attempts showed that creating domain index with parameter PARALLEL at this case very likely trigger the known bug as Oracle Support mentioned.
Further investigation
The next investigation I conducted is on Unix level. I would like to make sure if required OS packages have been installed properly, especially the UTF8 related ones, because this domain index is created upon UTF8-based data set. To do this kind of checking, one of the best tool we can use is Remote Diagnostic Agent (RDA) 4 provided by Oracle via metalink.
By following RDA 4 - Health Check / Validation Engine Guide, I implemented check of “Oracle Database 10g R2 (10.2.0) Preinstall (Solaris)” and found that two of oracle required OS packages were missing. They are:
- SUNWi15cs X11 ISO8859-15 Codeset Support
- SUNWi1cs X11 ISO8859-1 Codeset Support
Besides, one more suspicious OS package (SUNWeuluf UTF-8 L10N For Language Environment User Files) was found missing on production server. Unfortunately, adding these three OS packages only didn’t bring any luck to me. The memory problem was still there.
Further attempts and getting success
My further research on domain index came across the following informative articles and official oracle documents.
- Setting up and Index Maintenance Regime for Oracle Text
- Strategy for creating Oracle Text Index
- How InterMedia Processes Text DML
- Oracle Text Reference 10g Release 2 (10.2) B14218-01
- Oracle Text Application Developer’s Guide 10g Release 2 (10.2) B14217-01
As stated, “Oracle Text index is ‘DOMAIN’ index, oracle provided INDEXTYPE build using Extensible Indexing framework. Text index is not singe object in database, it is implemented using number of underlying ‘normal’ tables and indexes.” I then realized that we might can get help from “SQL Access Advisor” and “SQL Tuning Advisor” via Oracle Enterprise Manager. Afterwards, I was guided to table CTXSYS.DR$INDEX_ERROR. This table is actually log table for index creation. One of problem we had with this table is that the data inside is stale and I analyzed this table by following recommendation from “SQL Access Advisor”. Another significant error I noticed is that CTXSYS has no execute privilege on one of package.
Also, I’ve conducted the following enhancement according to above documents.
- analyzing related database objects
- increasing size of oracle parameter SORT_AREA_SIZE
- lowering preference parameter setting of “MAX_INDEX_MEMORY” AND “DEFAULT_INDEX_MEMORY” by using procedure CTX_ADM.SET_PARAMETER
- granting more privileges to user who create domain index
GRANT RESOURCE, CONNECT, CTXAPP TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO myuser; GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser; GRANT EXECUTE ON CTXSYS.CTX_DOC TO myuser; GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO myuser; GRANT EXECUTE ON CTXSYS.CTX_QUERY TO myuser; GRANT EXECUTE ON CTXSYS.CTX_REPORT TO myuser; GRANT EXECUTE ON CTXSYS.CTX_THES TO myuser; GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO myuser;
Finally, the sql statement I decided to use is listed below with explicitly setting NOPARALLEL instead of PARALLEL 1.
create index text_search_index
on searchable_attribute (attribute_text)
indextype is ctxsys.context
parameters (’datastore ctxsys.VISTA_CLOB_DATASTORE
storage ctxsys.vista_def_storage
lexer ctxsys.vista_default_lexer
wordlist ctxsys.vista_def_wordlist
memory 256M
stoplist ctxsys.default_stoplist’)
NOPARALLEL;
It’s unbelievable that it seemed working because the memory consumed was not increasing crazily and it did increase by 2M gradually. And, it stopped growing once it hit around 1.7 GB. (monitored by OS command prstat) That’s exact size of memory used while I created same index on development database. Next, there is no any exceptional issue about the creation of domain index and it eventually finished in 28 hours. The success of index creation was then approved by the subsequent application activity.
Well done!!
How and Why I got success
Wait seconds. I started to question myself “how and why I got success?”. Did above actions, including analyzing database objects, granting privileges, adjusting preference parameters, and increasing database init parameter, help me out indeed? The answer probably is NO because we got success on development database without doing these. Let me recall what else I did additionally.
That’s it. One more thing I did right before the successful attempt is that I ever try to simply rebuild domain index with following sql statement after I killed the problematical process again.
alter index text_search_index rebuild parameters (‘resume memory 2M’);
If the process to create domain index is killed while it’s still underway, the index status should be in-progress state. I did get success with this “alter index rebuild” statement upon the in-progress index. But, without setting other parameters, this index was not the one I’m really going to create. After issuing this index rebuild statement, I then followed the standard cycle to create this index. That is, dropping index and creating again.
Is this rebuilding attempt key point in guiding me to success? It’s very likely.
Did this approach avoid to trigger that bug issue? Definitely possible.
All above actions have been done within the same session without exiting. It looks like the clause “resume memory 2M” of “alter index … rebuild” statement stopped the “memory leaks” and kept resuming memory wisely.
To be continued
To verify my conclusion, I’ll ask both AskTom and Oracle Support.
In Closing
Eventually, Oracle Support got back to me and they didn’t have any answer and explanation on that. The only concern for that guy is if the service call could be closed. I have no reason to say “NO”.
Popularity: 22% [?]
Tags:oracle, memory, bug, case, domain index
