Luiz Cláudio Pereira.
Luiz Cláudio PereiraOlá Pessoal,
sou novo no fórum, estou com uma dúvida e gostaria de ler suas opiniões.
Antes disso, o fato ocorre num ambiente EXADATA X5-2, com Oracle 12.1.0.2,
Em Oracle Linux 6.
A dúvida é a seguinte: tenho dois índices de texto (ORACLE TEXT) com SYNC MANUAL em uma tabela. Por que quando uma linha da mesma é atualizada pela aplicação vejo na gv$session a execução do sincronismo (begin ctxsys.syncrn(:idxownid, :idxoname, :idxid, :ixpid, :rtabnm, :flg);)?
Complementando:
Os índices foram criados conforme exemplo abaixo:
begin
ctx_ddl.create_preference(‘BRASIL_LEX’, ‘BASIC_LEXER’);
ctx_ddl.set_attribute(‘BRASIL_LEX’, ‘BASE_LETTER’,’YES’);
end;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE (‘BRASIL_WORDLIST’, ‘BASIC_WORDLIST’);
CTX_DDL.SET_ATTRIBUTE (‘BRASIL_WORDLIST’, ‘WILDCARD_MAXTERMS’, 15000);
END;
/
begin
ctx_ddl.create_preference(‘XXXX_CTX_STORE_TBSP’, ‘BASIC_STORAGE’);
ctx_ddl.set_attribute(‘XXXX_CTX_STORE_TBSP’, ‘I_TABLE_CLAUSE’, ‘tablespace TBSBIGT’);
ctx_ddl.set_attribute(‘XXXX_CTX_STORE_TBSP’, ‘I_INDEX_CLAUSE’, ‘tablespace TBSBIGI’);
end;
/
CREATE INDEX SCOT.EXEMPLO_IX ON SCOT.TAB_EXEMPLO
(EXEMPLO_CP)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS(‘LEXER BRASIL_LEX STORAGE XXXX_CTX_STORE_TBSP SYNC (MANUAL)’)
NOPARALLEL;
Desde já agradeço.
Luiz Cláudio.
Luiz Cláudio PereiraObtive resposta do chamado que abri na Oracle, segue abaixo:
=== ORACLE TEXT =====
Hello,
Contrary to what the name implies, the ctxsys.syncrn procedure is not doing a SYNC. It is a procedure that does an insert or update of the Text index’s underlying $R table. When rows on the base table (which has a Text index) are deleted or updated, we access the $R table and either do an insert of a new ROWID(s), or we null out a ROWID(s). Since each $R row contains many rowids, a large number of deletes/updates causes $R contention, ‘enq: TX – row lock contention’ from ctxsys.syncrn() procedure.
Fundamentally, Oracle Text is designed for query performance rather than DML performance with respect to the $R table. So when you have a large enough rate of updates or deletes to base table rows, you will experience contention on the $R lob. This is just “the way things are” – syncrn() is inherently quite expensive and gets called for every update/delete on base table.
To reduce the contention on the $R lob column apply rebuild the Text index with “small_r_row” storage option enabled.
Example:
begin
ctx_ddl.create_preference(‘my_storage’,’BASIC_STORAGE’);
ctx_ddl.set_attribute(‘my_storage’, ‘small_r_row’, ‘T’);
end;
/
— To enable small_r_row feature, run –
alter session set events ‘30579 trace name context forever, level 268435456’;
create index foox on foo(b) indextype is ctxsys.context
parameters (‘storage my_storage’);