It would be nicer if you ran the trace file through tkprof before posting that
bit, but what it shows is that your query does an index range scan against a
domain index on a partitioned table and you're sorting the returned data in
some way then selecting the first 20 rows.
Oracle's estimate (which might be fairly arbitrary since I think it's just
assuming 0.1% of the data (suggested by the SEL 0.10000% on the domain index
line) is that you will acquire and sort 691,000 rows from the table before
sorting and discarding most of them Is there any way you could reduce the
number of rows you acquire from the table before sorting and discarding.
("Report the "top 20" is a fairly common source of performance problems where
Oracle has to do a lot of work to return a small result.)
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Chris Stephens <cstephens16@xxxxxxxxx>
Sent: 24 October 2019 20:01
To: oracle-l
Subject: Oracle Locator performance issue (10046 trace data)
We are starting to mess with Oracle Locator some and immediately hit
performance issue. Trace data shows a single fetch call accounts for vast
majority of response time. I don't know if this is due to lack of
instrumentation w/ locator or something else. Does anyone have experience w/
this kind of this that might be able to provide some insight?
Oracle 12.2
*** 2019-10-24T12:57:20.329694-05:00
FETCH
#140556737969632:c=70530,e=580279740,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=1,plh=2353454263,tim=10624057091673
STAT #140556737969632 id=1 cnt=20 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=0
pr=0 pw=0 str=1 time=580294149 us)'
STAT #140556737969632 id=2 cnt=20 pid=1 pos=1 obj=0 op='PX COORDINATOR (cr=0
pr=0 pw=0 str=1 time=580294109 us)'
STAT #140556737969632 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX SEND QC (ORDER)
:TQ10001 (cr=0 pr=0 pw=0 str=0 time=0 us cost=24196 size=64309407 card=691499)'
STAT #140556737969632 id=4 cnt=0 pid=3 pos=1 obj=0 op='VIEW (cr=0 pr=0 pw=0
str=0 time=0 us cost=24196 size=64309407 card=691499)'
STAT #140556737969632 id=5 cnt=0 pid=4 pos=1 obj=0 op='SORT ORDER BY STOPKEY
(cr=0 pr=0 pw=0 str=0 time=0 us cost=24196 size=43564437 card=691499)'
STAT #140556737969632 id=6 cnt=0 pid=5 pos=1 obj=0 op='PX RECEIVE (cr=0 pr=0
pw=0 str=0 time=0 us cost=13731 size=43564437 card=691499)'
STAT #140556737969632 id=7 cnt=0 pid=6 pos=1 obj=0 op='PX SEND RANGE :TQ10000
(cr=0 pr=0 pw=0 str=0 time=0 us cost=13731 size=43564437 card=691499)'
STAT #140556737969632 id=8 cnt=0 pid=7 pos=1 obj=0 op='PX PARTITION RANGE ALL
PARTITION: 1 16 (cr=0 pr=0 pw=0 str=0 time=0 us cost=13731 size=43564437
card=691499)'
STAT #140556737969632 id=9 cnt=0 pid=8 pos=1 obj=0 op='SORT ORDER BY STOPKEY
(cr=0 pr=0 pw=0 str=0 time=0 us)'
STAT #140556737969632 id=10 cnt=0 pid=9 pos=1 obj=714991 op='TABLE ACCESS BY
LOCAL INDEX ROWID Y6A1_LOCATOR PARTITION: 1 16 (cr=0 pr=0 pw=0 str=0 time=0 us
cost=13731 size=43564437 card=691499)'
STAT #140556737969632 id=11 cnt=0 pid=10 pos=1 obj=715087 op='DOMAIN INDEX
(SEL: 0.100000 %) Y6A1_LOCATOR_GEO_BTX (cr=0 pr=0 pw=0 str=0 time=0 us cost=5
size=0 card=0)'
EXEC
#140556737964464:c=87,e=87,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=10624057093221
XCTEND rlbk=0, rd_only=1, tim=10624057093262
--
//www.freelists.org/webpage/oracle-l
