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
