Re: Production and Test Environment Queries Behaving Differently
Date: Mon, 18 Sep 2017 20:29:46 -0400
Message-ID: <334741ba-b8a0-0dbc-4885-71e399404904_at_gmail.com>
Is your statistics the same on both production and test? If not, you should import statistics from the production to the test and see what happens.
On 09/18/2017 03:58 PM, MacGregor, Ian A. wrote:
> Of course the test environment things are fine
>
> SELECT* FROM PS_SL_VCHR_APP_VW A
> WHERE A.APPROVAL_DATE > '31-JUL-2017'
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 1.71 1.71 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 358 1.55 8.63 10097 42690 0
> 5344
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 360 3.26 10.34 10097 42690 0
> 5344
>
> Misses in library cache during parse: 1
> Optimizer mode: ALL_ROWS
> Parsing user id: 64
> Number of plan statistics captured: 1
>
> Rows (1st) Rows (avg) Rows (max) Row Source Operation
> ---------- ---------- ----------
> ---------------------------------------------------
> 5344 5344 5344 VIEW PS_SL_VCHR_APP_VW (cr=42690
> pr=10097 pw=0 time=8621057 us cost=9112 size=3780 card=18)
> 5344 5344 5344 SORT UNIQUE (cr=42690 pr=10097 pw=0
> time=8619491 us cost=9112 size=7992 card=18)
> 8557 8557 8557 FILTER (cr=42690 pr=10097 pw=0
> time=8609302 us)
> 38551 38551 38551 SORT GROUP BY (cr=42690 pr=10097
> pw=0 time=8611200 us cost=9112 size=7992 card=18)
> 275786 275786 275786 FILTER (cr=42690 pr=10097 pw=0
> time=7391194 us)
> 336109 336109 336109 HASH JOIN (cr=37281 pr=9996
> pw=0 time=7313019 us cost=8122 size=292596 card=659)
> 571 571 571 TABLE ACCESS FULL PS_EOAW_STEP
> (cr=30 pr=25 pw=0 time=8284 us cost=9 size=49106 card=571)
> 343974 343974 343974 HASH JOIN (cr=37251 pr=9971
> pw=0 time=7144258 us cost=8113 size=235922 card=659)
> 28803 28803 28803 TABLE ACCESS FULL
> PS_SL_PROJ_ATTR_ED (cr=1130 pr=19 pw=0 time=21585 us cost=309
> size=662469 card=28803)
> 46352 46352 46352 HASH JOIN (cr=36121 pr=9952
> pw=0 time=6788411 us cost=7804 size=2181855 card=6513)
> 8706 8706 8706 NESTED LOOPS (cr=34991
> pr=9952 pw=0 time=9442151 us cost=7495 size=833710 card=3170)
> 8706 8706 8706 NESTED LOOPS (cr=20923
> pr=9417 pw=0 time=3774550 us cost=7495 size=833710 card=3582)
> 4858 4858 4858 HASH JOIN (cr=12658
> pr=9362 pw=0 time=3055947 us cost=3912 size=262680 card=1194)
> 4858 4858 4858 HASH JOIN (cr=12216
> pr=9351 pw=0 time=3076973 us cost=3776 size=220248 card=1197)
> 16656 16656 16656 HASH JOIN (cr=10582
> pr=9295 pw=0 time=10926350 us cost=2902 size=3013602 card=25539)
> 16656 16656 16656 TABLE ACCESS FULL PS_EOAW_USERINST
> (cr=5417 pr=5344 pw=0 time=70453130 us cost=1495 size=1328028 card=25539)
> 503593 503593 503593 TABLE ACCESS FULL PS_EOAW_STEPINST
> (cr=5165 pr=3951 pw=0 time=444626 us cost=1405 size=33236544 card=503584)
> 76202 76202 76202 TABLE ACCESS FULL
> PS_VCHR_AF_XREF (cr=1634 pr=56 pw=0 time=26786 us cost=444
> size=3867072 card=58592)
> 12503 12503 12503 TABLE ACCESS FULL
> PSOPRDEFN (cr=442 pr=11 pw=0 time=1251637 us cost=136 size=450108
> card=12503)
> 8706 8706 8706 INDEX RANGE SCAN
> PSADISTRIB_LINE (cr=8265 pr=55 pw=0 time=381992 us cost=2 size=0
> card=3)(object id 112885)
> 8706 8706 8706 TABLE ACCESS BY INDEX ROWID
> PS_DISTRIB_LINE (cr=14068 pr=535 pw=0 time=3231618 us cost=3 size=129
> card=3)
> 29328 29328 29328 TABLE ACCESS FULL
> PS_SL_PROJ_ATTR_ED (cr=1130 pr=0 pw=0 time=13669 us cost=309
> size=2111616 card=29328)
> 2921 2921 2921 SORT AGGREGATE (cr=5409 pr=101
> pw=0 time=813982 us)
> 3889 3889 3889 TABLE ACCESS BY INDEX ROWID
> PS_SL_PROJ_ATTR_ED (cr=5409 pr=101 pw=0 time=809210 us cost=3 size=26
> card=1)
> 3897 3897 3897 INDEX RANGE SCAN
> PS_SL_PROJ_ATTR_ED (cr=2940 pr=101 pw=0 time=802139 us cost=2 size=0
> card=1)(object id 8430914)
>
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total Waited
> ---------------------------------------- Waited ----------
> ------------
> Disk file operations I/O 9 0.00 0.00
> SQL*Net message to client 358 0.00 0.00
> db file scattered read 234 0.06 2.05
> db file sequential read 834 0.06 5.04
> SQL*Net message from client 358 1535.31 1538.89
> ********************************************************************************
>
> But not so much so in production
>
> SELECT* FROM sysadm.ps_SL_VCHR_APP_VW A
> WHERE A.APPROVAL_DATE > '31-JUL-2017'
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 1.80 1.81 0 0 0 0
> Execute 1 0.00 0.00 0 0 0 0
> Fetch 1 482.10 534.13 135734 6703 15278
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 483.90 535.94 135734 6703 15278
> 0
>
> 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
> ---------- ---------- ----------
> ---------------------------------------------------
> 0 0 0 VIEW PS_SL_VCHR_APP_VW (cr=0 pr=0
> pw=0 time=12 us cost=7078 size=210 card=1)
> 0 0 0 SORT UNIQUE (cr=0 pr=0 pw=0 time=12
> us cost=7077 size=426 card=1)
> 0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0
> time=4 us cost=7076 size=426 card=1)
> 0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0
> time=3 us cost=7076 size=426 card=1)
> 0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0
> time=3 us cost=7075 size=403 card=1)
> 0 0 0 HASH JOIN (cr=0 pr=0 pw=0
> time=3 us cost=6721 size=43896 card=118)
> 0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0
> time=2 us cost=6196 size=40560 card=120)
> 0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0
> time=2 us cost=6196 size=40560 card=341)
> 0 0 0 HASH JOIN (cr=0 pr=0 pw=0
> time=2 us cost=6182 size=96844 card=341)
> 571 571 571 TABLE ACCESS FULL
> PS_EOAW_STEP (cr=30 pr=0 pw=0 time=1453 us cost=9 size=42254 card=571)
> 0 0 0 NESTED LOOPS (cr=0 pr=0
> pw=0 time=45 us cost=6173 size=71610 card=341)
> 0 0 0 NESTED LOOPS (cr=0 pr=0
> pw=0 time=44 us cost=6173 size=71610 card=341)
> 0 0 0 HASH JOIN (cr=0 pr=0 pw=0
> time=44 us cost=5150 size=49786 card=341)
> 0 0 0 HASH JOIN (cr=0 pr=0
> pw=0 time=43 us cost=4841 size=29326 card=341)
> 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)
> 0 0 0 TABLE ACCESS FULL
> PS_EOAW_USERINST (cr=0 pr=0 pw=0 time=0 us cost=1495 size=1344564
> card=25857)
> 0 0 0 TABLE ACCESS FULL
> PS_SL_PROJ_ATTR_ED (cr=0 pr=0 pw=0 time=0 us cost=308 size=1766100
> card=29435)
> 0 0 0 INDEX RANGE SCAN
> PSAEOAW_STEPINST (cr=0 pr=0 pw=0 time=0 us cost=2 size=0
> card=1)(object id 115443)
> 0 0 0 TABLE ACCESS BY INDEX ROWID
> PS_EOAW_STEPINST (cr=0 pr=0 pw=0 time=0 us cost=3 size=64 card=1)
> 0 0 0 INDEX UNIQUE SCAN PS_VCHR_AF_XREF
> (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 153926)
> 0 0 0 TABLE ACCESS BY INDEX ROWID
> PS_VCHR_AF_XREF (cr=0 pr=0 pw=0 time=0 us cost=1 size=54 card=1)
> 0 0 0 VIEW VW_SQ_2 (cr=0 pr=0 pw=0 time=0
> us cost=524 size=982566 card=28899)
> 0 0 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0
> us cost=524 size=751374 card=28899)
> 0 0 0 TABLE ACCESS FULL
> PS_SL_PROJ_ATTR_ED (cr=0 pr=0 pw=0 time=0 us cost=309 size=751374
> card=28899)
> 0 0 0 TABLE ACCESS BY INDEX ROWID
> PS_DISTRIB_LINE (cr=0 pr=0 pw=0 time=0 us cost=3 size=31 card=1)
> 0 0 0 INDEX RANGE SCAN PSADISTRIB_LINE
> (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=3)(object id 112885)
> 0 0 0 INDEX UNIQUE SCAN PS_PSOPRDEFN (cr=0
> pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 8426521)
> 0 0 0 TABLE ACCESS BY INDEX ROWID PSOPRDEFN
> (cr=0 pr=0 pw=0 time=0 us cost=1 size=23 card=1)
>
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ---------- ------------
> library cache lock 9 0.00
> 0.00
> library cache pin 9 0.00
> 0.00
> KJC: Wait for msg sends to complete 103 0.00
> 0.00
> Disk file operations I/O 6 0.00 0.00
> SQL*Net break/reset to client 4 0.00 0.00
> SQL*Net message to client 2 0.00 0.00
> SQL*Net message from client 2 1422.79
> 1448.16
> row cache lock 1 0.00 0.00
> gc cr multi block request 238 0.00 0.49
> gc current block 2-way 205 0.00 0.06
> asynch descriptor resize 1 0.00 0.00
> CSS initialization 2 0.00 0.00
> CSS operation: action 2 0.00
> 0.00
> CSS operation: query 6 0.00
> 0.00
> direct path write temp 33291 0.07 25.74
> gc current grant busy 9 0.00 0.00
> gc current grant 2-way 144 0.00 0.02
> db file sequential read 481 0.00 0.19
> local write wait 20400 0.02 13.18
> enq: TS - contention 67 0.00 0.02
> DFS lock handle 206 0.25
> 10.98
> gc current grant congested 1 0.00 0.00
> direct path read temp 3446 0.00 2.06
> ********************************************************************************
>
>
> The sizes of the tables are approximately the same. Test was a
> refreshed from production recently. The big difference is that
> Production is RAC, where as this particular test environment is not.
> Yes we do have a RAC test environment. I don’t think RAC is the
> problem here.
>
> The production query fails because it exhausts the temporary
> tablespace. The test query was also failing until statistics were
> regenerated for tables involved. This has not worked for the
> production environment.
>
> I verified that the views are the same and the indexing is as well.
>
> Any suggestions??
>
> Ian MacGregor
> SLAC National Accelerator Laboratory
>
>
>
-- Mladen Gogala Oracle DBA Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 19 2017 - 02:29:46 CEST