Fetch timing-Performance

From: oracledba <oracledba71_at_gmail.com>
Date: Tue, 1 Oct 2013 11:58:32 -0400
Message-ID: <CA+eRr5FbmwNNGQhPe-EaT-rF6Rsaj2VM+iBAhXjvtSmQhy=x8A_at_mail.gmail.com>

Folks,
We have two identical databases (cloned from same copy of prod) sitting on the same server.Both of them have exactly same init.ora parameters and stats are collected same way.
Ran a SQL on db1 and db2.Both SQL's execution plan from 10046 shown below are same.But the SQL on db2 took more time than db1. Any thoughts why?

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.97       1.01          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch    79897    692.59    1152.05    3706124    2769429          5

1198431
------- ------ -------- ---------- ---------- ---------- ----------

total 79899 693.57 1153.07 3706124 2769429 5 1198431

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105

Rows Row Source Operation
------- ---------------------------------------------------
1198431 HASH JOIN RIGHT OUTER (cr=2769429 pr=3706124 pw=964978 time=861421317 us cost=2018438 size=5093556298 card=11219287)

867 MAT_VIEW ACCESS FULL TEST_MV (cr=279 pr=262 pw=0 time=2573 us cost=88 size=43350 card=867)
1198431 HASH JOIN RIGHT OUTER (cr=2769150 pr=3705862 pw=964978 time=858087245 us cost=2018314 size=3094918356 card=7660689)

844 MAT_VIEW ACCESS FULL TEST_MV (cr=279 pr=0 pw=0 time=7396 us cost=88 size=42200 card=844)
1198431 HASH JOIN RIGHT OUTER (cr=2768871 pr=3705862 pw=964978 time=855054584 us cost=2018201 size=1866829974 card=5273531)

9118 VIEW (cr=16590 pr=890 pw=0 time=2558533 us cost=4592 size=810231 card=9313)

9118 HASH GROUP BY (cr=16590 pr=890 pw=0 time=2555234 us cost=4592 size=810231 card=9313)

9207 VIEW (cr=16590 pr=890 pw=0 time=2545205 us cost=4591 size=810231 card=9313)

9207 SORT UNIQUE (cr=16590 pr=890 pw=0 time=2542006 us cost=4591 size=371075 card=9313)

   9229         UNION-ALL  (cr=16590 pr=890 pw=0 time=157458 us)
   1603          MAT_VIEW ACCESS FULL TEST1_MV (cr=15712 pr=495 pw=0
time=141018 us cost=4324 size=48396 card=1308)
   6706          MAT_VIEW ACCESS FULL TEST2_MV (cr=727 pr=245 pw=0
time=22486 us cost=210 size=290444 card=7084)
    920          MAT_VIEW ACCESS FULL TEST3_MV (cr=151 pr=150 pw=0
time=36090 us cost=53 size=32235 card=921)
1198431     HASH JOIN RIGHT OUTER (cr=2752281 pr=3704972 pw=964978
time=849060306 us cost=2013593 size=1408032777 card=5273531)
      2      MAT_VIEW ACCESS FULL TEST4_MV (cr=2 pr=2 pw=0 time=2211 us
cost=13 size=14 card=2)
1198431      HASH JOIN RIGHT OUTER (cr=2752279 pr=3704970 pw=964978
time=845618754 us cost=2013562 size=1371118060 card=5273531)
   1015       MAT_VIEW ACCESS FULL TEST5_MV (cr=87 pr=87 pw=0 time=26700 us
cost=36 size=9135 card=1015)
1198431       HASH JOIN RIGHT OUTER (cr=2752192 pr=3704883 pw=964978
time=841740385 us cost=2013509 size=1323656281 card=5273531)
2751771        MAT_VIEW ACCESS FULL TEST6_MV (cr=42237 pr=42235 pw=0
time=8675452 us cost=11544 size=24678873 card=2742097)
1198431        HASH JOIN  (cr=2709955 pr=3645092 pw=954828 time=824612883
us cost=1935880 size=1276194502 card=5273531)
15768699         INDEX FAST FULL SCAN TEST7_MV_IDX1 (cr=75591 pr=75323 pw=0
time=13774306 us cost=20219 size=266959245 card=15703485)(object id 210790)
1234421         HASH JOIN  (cr=2634364 pr=3510125 pw=896548 time=813786908
us cost=1828067 size=1324678725 card=5887461)
18096625          MAT_VIEW ACCESS FULL TEST8_MV (cr=220012 pr=218960 pw=0
time=7446131 us cost=57638 size=543480840 card=17531640)
1234421          HASH JOIN RIGHT OUTER (cr=2414352 pr=3191128 pw=787741
time=308160557 us cost=1676987 size=1149316140 card=5924310)
  89403           MAT_VIEW ACCESS FULL TEST9_MV (cr=1367 pr=37 pw=0
time=31651 us cost=384 size=714944 card=89368)
1234421           HASH JOIN RIGHT OUTER (cr=2412985 pr=3191091 pw=787741
time=306683843 us cost=1676583 size=1101921660 card=5924310)
 112372            VIEW  (cr=5191 pr=3990 pw=0 time=1930045 us cost=5260
size=10166000 card=156400)
 112372             HASH GROUP BY (cr=5191 pr=3990 pw=0 time=1878962 us
cost=5260 size=5630400 card=156400)
 592588              HASH JOIN  (cr=5191 pr=3990 pw=0 time=1208488 us
cost=2370 size=20768256 card=576896)
 112372               MAT_VIEW ACCESS FULL TEST10_MV (cr=983 pr=14 pw=0
time=35139 us cost=281 size=2434014 card=110637)
 599666               MAT_VIEW ACCESS FULL TEST11_MV (cr=4208 pr=3976 pw=0
time=3867835 us cost=1169 size=8386280 card=599020)
1234421            HASH JOIN OUTER (cr=2407794 pr=3187101 pw=787741
time=303391491 us cost=1633469 size=716841510 card=5924310)
1234421             HASH JOIN RIGHT OUTER (cr=820311 pr=868223 pw=56296
time=49864926 us cost=291476 size=408777390 card=5924310)
10419387              MAT_VIEW ACCESS FULL TEST12_MV (cr=148797 pr=146955
pw=0 time=15225378 us cost=40184 size=135353205 card=10411785)
1234421              HASH JOIN  (cr=671514 pr=687540 pw=22568 time=23888401
us cost=219858 size=331761360 card=5924310)
2502803               MAT_VIEW ACCESS FULL TEST13_MV (cr=490883 pr=484364
pw=0 time=17431145 us cost=129870 size=168274124 card=5802556)
17393219               MAT_VIEW ACCESS FULL TEST14_MV (cr=180631 pr=180608
pw=0 time=4686498 us cost=47586 size=454360077 card=16828151)
44029438             VIEW  (cr=1587483 pr=2309764 pw=722362 time=741267887
us cost=1183366 size=2331410432 card=44834816)
44029438              HASH GROUP BY (cr=1587483 pr=2309764 pw=722362
time=719813126 us cost=1183366 size=986365952 card=44834816)
233541065               MAT_VIEW ACCESS FULL TEST15_MV (cr=1587483

pr=1587402 pw=0 time=102764611 us cost=429931 size=5227415732 card=237609806)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.94       1.01          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch    79897   3640.81    4290.46    3698368    2763968          0

1198431
------- ------ -------- ---------- ---------- ---------- ----------

total 79899 3641.76 4291.48 3698368 2763968 0 1198431

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109

Rows Row Source Operation
------- ---------------------------------------------------
1198431 HASH JOIN RIGHT OUTER (cr=2763968 pr=3698368 pw=958423 time=350824034 us cost=2018213 size=5090043700 card=11211550)

855 MAT_VIEW ACCESS FULL TEST_MV (cr=279 pr=0 pw=0 time=2757 us cost=88 size=42750 card=855)
1198431 HASH JOIN RIGHT OUTER (cr=2763689 pr=3698368 pw=958423 time=345160774 us cost=2018089 size=3093810992 card=7657948)

832 MAT_VIEW ACCESS FULL TEST_MV (cr=279 pr=0 pw=0 time=7775 us cost=88 size=41600 card=832)
1198431 HASH JOIN RIGHT OUTER (cr=2763410 pr=3698368 pw=958423 time=340348133 us cost=2017976 size=1870269792 card=5283248)

9118 VIEW (cr=16503 pr=51 pw=0 time=371926 us cost=4569 size=757422 card=8706)

9118 HASH GROUP BY (cr=16503 pr=51 pw=0 time=368628 us cost=4569 size=757422 card=8706)

9207 VIEW (cr=16503 pr=51 pw=0 time=354731 us cost=4567 size=757422 card=8706)

9207 SORT UNIQUE (cr=16503 pr=51 pw=0 time=351146 us cost=4567 size=348345 card=8706)

   9229         UNION-ALL  (cr=16503 pr=51 pw=0 time=1027051 us)
   1603          MAT_VIEW ACCESS FULL TEST1_MV (cr=15712 pr=51 pw=0
time=984036 us cost=4324 size=36963 card=999)
   6706          MAT_VIEW ACCESS FULL TEST2_MV (cr=727 pr=0 pw=0 time=6468
us cost=210 size=278226 card=6786)
    920          MAT_VIEW ACCESS FULL TEST3_MV (cr=64 pr=0 pw=0 time=521 us
cost=30 size=33156 card=921)
1198431     HASH JOIN RIGHT OUTER (cr=2746907 pr=3698317 pw=958423
time=334470126 us cost=2013390 size=1410627216 card=5283248)
      2      MAT_VIEW ACCESS FULL TEST4_MV (cr=2 pr=0 pw=0 time=67 us
cost=13 size=14 card=2)
1198431      HASH JOIN RIGHT OUTER (cr=2746905 pr=3698317 pw=958423
time=328665172 us cost=2013360 size=1373644480 card=5283248)
   1003       MAT_VIEW ACCESS FULL TEST5_MV (cr=87 pr=68 pw=0 time=12126 us
cost=36 size=9027 card=1003)
1198431       HASH JOIN RIGHT OUTER (cr=2746818 pr=3698249 pw=958423
time=321552574 us cost=2013307 size=1326095248 card=5283248)
2744178        MAT_VIEW ACCESS FULL TEST6_MV (cr=42236 pr=42235 pw=0
time=4138935 us cost=11544 size=24678873 card=2742097)
1198431        HASH JOIN  (cr=2704582 pr=3638472 pw=948273 time=174206514
us cost=1935562 size=1278546016 card=5283248)
15768699         INDEX FAST FULL SCAN TEST7_MV_IDX1 (cr=75549 pr=75101 pw=0
time=26733243 us cost=19877 size=267451106 card=15732418)(object id 220252)
1234403         HASH JOIN  (cr=2629033 pr=3503727 pw=889993 time=135614654
us cost=1828052 size=1324678725 card=5887461)
18090410          MAT_VIEW ACCESS FULL TEST8_MV (cr=219015 pr=218948 pw=0
time=5752544 us cost=57638 size=543480840 card=17531640)
1234403          HASH JOIN RIGHT OUTER (cr=2410018 pr=3184928 pw=781386
time=1252178325 us cost=1676972 size=1149316140 card=5924310)
  89319           MAT_VIEW ACCESS FULL TEST9_MV (cr=1367 pr=0 pw=0
time=33970 us cost=384 size=714552 card=89319)
1234403           HASH JOIN RIGHT OUTER (cr=2408651 pr=3184928 pw=781386
time=1250684012 us cost=1676568 size=1101921660 card=5924310)
 112372            VIEW  (cr=5191 pr=90 pw=0 time=1351659 us cost=5248
size=10113870 card=155598)
 112372             HASH GROUP BY (cr=5191 pr=90 pw=0 time=1295964 us
cost=5248 size=5601528 card=155598)
 592588              HASH JOIN  (cr=5191 pr=90 pw=0 time=605467 us
cost=2367 size=20718432 card=575512)
 112372               MAT_VIEW ACCESS FULL TEST10_MV (cr=983 pr=71 pw=0
time=33763 us cost=281 size=2420528 card=110024)
 597115               MAT_VIEW ACCESS FULL TEST11_MV (cr=4208 pr=19 pw=0
time=206789 us cost=1169 size=8359470 card=597105)
1234403            HASH JOIN OUTER (cr=2403460 pr=3184838 pw=781386
time=1247993476 us cost=1633469 size=716841510 card=5924310)
1234403             HASH JOIN RIGHT OUTER (cr=811645 pr=867920 pw=56296
time=86075318 us cost=291476 size=408777390 card=5924310)
10415162              MAT_VIEW ACCESS FULL TEST12_MV (cr=146866 pr=146861
pw=0 time=16255846 us cost=40184 size=135353205 card=10411785)
1234403              HASH JOIN  (cr=664779 pr=687331 pw=22568
time=190442429 us cost=219858 size=331761360 card=5924310)
2500348               MAT_VIEW ACCESS FULL TEST13_MV (cr=484885 pr=484872
pw=0 time=29117663 us cost=129870 size=168274124 card=5802556)
17386839               MAT_VIEW ACCESS FULL TEST14_MV (cr=179894 pr=179891
pw=0 time=18465992 us cost=47586 size=454360077 card=16828151)
44029438             VIEW  (cr=1591815 pr=2307804 pw=716007 time=685501566
us cost=1183366 size=2331410432 card=44834816)
44029438              HASH GROUP BY (cr=1591815 pr=2307804 pw=716007
time=663312375 us cost=1183366 size=986365952 card=44834816)
233541065               MAT_VIEW ACCESS FULL TEST15_MV (cr=1591815

pr=1591797 pw=0 time=114294475 us cost=429931 size=5227415732 card=237609806)

Thanks

--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 01 2013 - 17:58:32 CEST

This message: [ Message body ]
Next message: David Fitzjarrell: "Re: Fetch calls and increasing arraysize"
Previous message: Justin Mungal: "Re: 11.2.0.3.7 patches"
Next in thread: David Fitzjarrell: "Re: Fetch timing-Performance"
Maybe reply: David Fitzjarrell: "Re: Fetch timing-Performance"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message