| oracle context - performance rel Question ? [message #366409] |
Tue, 07 December 1999 16:27 |
subu kors
Messages: 1
Registered: December 1999
|
Junior Member |
|
|
I need help in optimizing a oracle context search. I am new to the product and dont
know where to tune to improve the response time of the search.
Right now, two step search takes around 3 minutes to retrive records based on a
key work from a table of 10 million rows. We have a policy and associated indexes
created on the table.column. We are using two step query to search, retrieve the
details and are using forms 5.0 as client to display the results on the screen. After a
thorough investigation, i found out that ctx_query.contains(policy_name,
query_string, resulttab, sharelevel) part of the step which populates the result table
with the relavant records for query_string is taking considerable amount of time like
2 3/4 minutes and rest is done in 15 secs.
Any suggestions on how i can improve the performance, where do i tune ? what do i
modify ? etc
your suggestions are highly appreciated!
thanx in advance.
Subu
-------------------------------------------------------
Find the following steps that i follow
ctx_query.gettab(1,temp_tab,1);
query_string := '%PHOTO%';
ctx_query.contains(policy_name => 'EMPLOYER_NAME',
text_query => query_string,
restab => temp_tab,
sharelevel => 0);
build default where clause in the basetable of the form.
execute query to retrieve the rows on the screen.
ctx_query.reltab(temp_tab);
Step 3 takes most of the time (3 min) in the whole process. Step 5 does not retrieve all the rows, it retrieves a set based on the buffer defined in the block level in the form. but step 5 takes 2 seconds only. The bottle neck looks like step 3.
I don't know, how to tune this - I really appreciate your help on this.
Subu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
