Re: Production and Test Environment Queries Behaving Differently
Date: Thu, 21 Sep 2017 20:38:42 +0000
Message-ID: <CY1PR0701MB1882E2F6850384B3E9CE3A42E2660_at_CY1PR0701MB1882.namprd07.prod.outlook.com>
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_at_jlcomp.demon.co.uk> Sent: Thursday, September 21, 2017 8:23:17 AM To: MacGregor, Ian A.; oracle-l_at_freelists.org 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Michael McMullen <ganstadba_at_hotmail.com> Sent: 21 September 2017 15:50
To: MacGregor, Ian A.; oracle-l_at_freelists.org 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of MacGregor, Ian A. <ian_at_slac.stanford.edu> Sent: September 18, 2017 3:58 PM
To: oracle-l_at_freelists.org
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)
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 21 2017 - 22:38:42 CEST