Thanks. It took going over the 10053 traces line by line. It was discovered
that the poorly running query had histograms on the PS_VCHR_AF_XREF table
whereas the database where the query ran well lacked histograms on that table.
Ian A. MacGregor
SLAC National Accelerator Laboratory
________________________________
From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: Thursday, September 21, 2017 8:23:17 AM
To: MacGregor, Ian A.; oracle-l@xxxxxxxxxxxxx
Subject: Re: Production and Test Environment Queries Behaving Differently
Someone else has mentioned the difference in parsing user id.
One possibility connected with this is the observation that the SYS plan looks
as if it had two existence subqueries unnested but not merged, while the other
plan shows one aggregate subquery being used as a filter while the other
subquery has disappeared (along with one of the tables from the vw_sq_1
unnested version). This suggests a difference in parameters relating to
transformatios - in particular the "secure_view_merging" parameter. As a quick
and dirty check I'd force a parse in both environments with event 10053 (CBO
trace) set and check the "Optimizer Parameters" section to check that they
match.
Another possibility that jumps out is a variation in the statistics for the
column(s) joining PS_EOAW_USERINST and PS_SL_PROJ_ATTR_ED in the SYS plan. The
prediction for the join is 37M, the actual (before the query was interrupted,
presumably) was 800M. The scale of the error could have produced an massive
under-estimate for the subsequence hash group by that, if corrected, might have
persuaded the optimizer that this was a bad plan. I'd look at the stats on the
join columns, particularly the low/high values, and any indications of
misleading histograms.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Michael McMullen <ganstadba@xxxxxxxxxxx>
Sent: 21 September 2017 15:50
To: MacGregor, Ian A.; oracle-l@xxxxxxxxxxxxx
Subject: Re: Production and Test Environment Queries Behaving Differently
are your ps specific hidden parameters the same in both environments?
That's gotten me a few times.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of MacGregor, Ian A. <ian@xxxxxxxxxxxxxxxxx>
Sent: September 18, 2017 3:58 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Production and Test Environment Queries Behaving Differently
Of course the test environment things are fine
SELECT * FROM PS_SL_VCHR_APP_VW A
WHERE A.APPROVAL_DATE > '31-JUL-2017'
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Number of plan statistics captured: 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
25845463 25845463 25845463 VIEW VW_SQ_1 (cr=6673 pr=135696
pw=3967287 time=522162423 us cost=3346 size=343604 card=10106)
25845463 25845463 25845463 HASH GROUP BY (cr=6673
pr=135696 pw=3967287 time=518358907 us cost=3346 size=485088 card=10106)
806300000 806300000 806300000 MERGE JOIN (cr=6673 pr=0 pw=0
time=298743322 us cost=1931 size=1793359392 card=37361654)
27904 27904 27904 SORT JOIN (cr=5543 pr=0 pw=0
time=733681 us cost=1497 size=646425 card=25857)
27904 27904 27904 TABLE ACCESS FULL
PS_EOAW_USERINST (cr=5543 pr=0 pw=0 time=36534830 us cost=1495 size=646425
card=25857)
806300000 806300000 806300000 SORT JOIN (cr=1130 pr=0 pw=0
time=186306940 us cost=310 size=664677 card=28899)
28982 28982 28982 TABLE ACCESS FULL
PS_SL_PROJ_ATTR_ED (cr=1130 pr=0 pw=0 time=375208 us cost=309 size=664677
card=28899)
