Re: Oracle Locator performance issue (10046 trace data)

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 24 Oct 2019 20:16:08 +0000
Message-ID: <CWXP265MB175097A5B1C7BF76F7697C4EA56A0_at_CWXP265MB1750.GBRP265.PROD.OUTLOOK.COM>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Chris Stephens <cstephens16_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 24 2019 - 22:16:08 CEST