Inconsistent Query Performance (Doc ID 2296383.1)

Last updated on APRIL 01, 2020

Applies to:

Goal

On an 11.2.0.4 database, the initial execution of the following takes a long time the first time it is run, but subsequent searches are really fast.

declare
rs clob;
begin
dbms_lob.createtemporary(rs, true, dbms_lob.session);
ctx_query.result_set('FT_IDCTEXT1', '(((((((((SDATA(xCIG_DocOriginationDate >= "2017/03/01 00:00:00") and ((ARIC65\_IM) WITHIN dDocAuthor) and ((imm\_oper\_files) WITHIN xIdcProfile) and ((1) WITHIN xCIG_Agency) and ((1) WITHIN xCIG_Function) and ((1) WITHIN xCIG_Activity))))))))))', '
<ctx_result_set_descriptor>
<count exact="false"/>
<group sdata="sdDrillDown">
<count exact="true"/>
</group>
<hitlist start_hit_num="1" end_hit_num="20" order="dInDate Desc">
<sdata name="dID"/>
<sdata name="sddDocName"/><score />
</hitlist>
</ctx_result_set_descriptor>', rs);
dbms_output.put_line(rs);
dbms_lob.freetemporary(rs);
exception
when others then
dbms_lob.freetemporary(rs);
raise;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 2 13 0 0
Execute 1 7.43 105.19 21460 50399 41 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.43 105.24 21462 50412 41 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 85

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 6 0.00 0.00
db file sequential read 11999 0.44 57.17
db file parallel read 95 2.00 42.65
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

Are the ctx_query.result_set results cached?
And if so, how does it work?
Is there a way to speed this up?
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.