Problema com o Oracle text ao executar a consulta pela 1x Options

[Rafael Pinheiro]

Boa noite!!

Eu estou começando a trabalhar com oracle text e estou com um problema.

As consultas estão lentas ao executá-las pela primeira vez. As vezes, ocorre erro de timeout. A cláusula da consulta segue abaixo:

( CONTAINS(texto,'tc OR {turbo-compressor} OR turbocompressor',1) > 0 )

Ao executar a mesma consula por 2 ou mais vezes, não acontece nenhum problema.

Alguém sabe me dizer o porque deste problema?

Obrigado!
Rafael Pinheiro

[burleson]

Hoi Rafief,



I had to translate this:
Good night! I am starting to work with oracle text and am with a problem.

>> The consultations are slow for the first time when executing them.

Get an execution plan for the SQL:

http://www.dba-oracle.com/t_sql_execution_plan_tips.htm

******************************************
The times, occur error of timeout.

What specific error number?

*****************************************************

>> When executing same consula for 2 or more times, does not happen no problem.

Because the blocks are in the RAM buffer.

http://www.dba-oracle.com/art_tuning_instance.htm

Please post in English . . . .

[Rafael Pinheiro]

Hi,

When i execute the query for the fisrt time, occurs the error ORA-01013...

After the first execution of query, the response for the same query is very fast...

Why this happens?

P.S: The indexes of Oracle text were created as follow above:

create index ie02_text on doc(full_doc) indextype is ctxsys.context parameters ('sync (on commit)');

[burleson]

Hi Rafael,


>> 'sync (on commit)'

Do you really want to have this HUGE overhead? Context indexes are mostly for READ ONLY tables . . .

Context indexes can take a great deal of time to create and during the index build there MUST BE NO updating of the database.

Your ORA-01013 was a timeout, waiting for a row to be released.

http://www.dba-oracle.com/t_ora_01013_user...t_operation.htm

**********************************************
>> After the first execution of query, the response for the same query is very fast...

Like I said, it's is loading your data buffers.

[Rafael Pinheiro]

the table that has the index is updated every night by only a pl/sql. What should i do to solve this problem? change the parameter?

[Rafael Pinheiro]

can i use the parameter 'SYNC (EVERY "SYSDATE+1/24")'?

[burleson]

Drop the index, load with PL/SQL and re-build the Context index nightly.

>> What should i do to solve this problem?

If the table is read-only the refresh does not matter but see here:

http://dba-oracle.com/oracle_tips_like_sql_index.htm

>> can i use the parameter 'SYNC (EVERY "SYSDATE+1/24")'?

Try it and see