Re: Fetch timing-Performance

• From: David Fitzjarrell <oratune@xxxxxxxxx>
• To: "oracledba71@xxxxxxxxx" <oracledba71@xxxxxxxxx>, Oracle-L Freelists <Oracle-L@xxxxxxxxxxxxx>
• Date: Tue, 1 Oct 2013 10:19:16 -0700 (PDT)

Not without a bit more information, such as were these queries run at the same 
time?  Are these two databases using ASM, and if so are they using the same 
diskgroups?  If they are not ASM are they using the same filesystems?  What are 
the significant waits each run is experiencing?  Are they the same?  Are they 
different?  It appears that the major difference is in the CPU timings for the 
two runs; what else was running on this server when the second query was 
executed?  What does an AWR report tell you for each database for the time 
during these runs?
 
As it is now there are too many variables in play to provide any sort of answer 
to your question.
David Fitzjarrell

 

________________________________
 From: oracledba <oracledba71@xxxxxxxxx>
To: Oracle-L Freelists <Oracle-L@xxxxxxxxxxxxx> 
Sent: Tuesday, October 1, 2013 9:58 AM
Subject: Fetch timing-Performance
  

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'69429 pr706124 pw–4978
time†1421317 us cost 18438 sizeP93556298 card219287)
    867   MAT_VIEW ACCESS FULL TEST_MV (cr'9 pr&2 pw=0 time%73 us
costˆ sizeC350 card†7)
1198431   HASH JOIN RIGHT OUTER (cr'69150 pr705862 pw–4978
time…8087245 us cost 18314 size094918356 cardv60689)
    844    MAT_VIEW ACCESS FULL TEST_MV (cr'9 pr=0 pw=0 times96 us
costˆ sizeB200 card„4)
1198431    HASH JOIN RIGHT OUTER (cr'68871 pr705862 pw–4978
time…5054584 us cost 18201 size66829974 cardR73531)
   9118     VIEW  (cr590 pr‰0 pw=0 time%58533 us costE92
size0231 card“13)
   9118      HASH GROUP BY (cr590 pr‰0 pw=0 time%55234 us costE92
size0231 card“13)
   9207       VIEW  (cr590 pr‰0 pw=0 time%45205 us costE91
size0231 card“13)
   9207        SORT UNIQUE (cr590 pr‰0 pw=0 time%42006 us costE91
size71075 card“13)
   9229         UNION-ALL  (cr590 pr‰0 pw=0 time7458 us)
   1603          MAT_VIEW ACCESS FULL TEST1_MV (cr712 prI5 pw=0
time1018 us costC24 sizeH396 card08)
   6706          MAT_VIEW ACCESS FULL TEST2_MV (crr7 pr$5 pw=0
time"486 us cost!0 size)0444 cardp84)
    920          MAT_VIEW ACCESS FULL TEST3_MV (cr1 pr0 pw=0
time6090 us costS size2235 card’1)
1198431     HASH JOIN RIGHT OUTER (cr'52281 pr704972 pw–4978
time„9060306 us cost 13593 size08032777 cardR73531)
      2      MAT_VIEW ACCESS FULL TEST4_MV (cr=2 pr=2 pw=0 time"11 us
cost size card=2)
1198431      HASH JOIN RIGHT OUTER (cr'52279 pr704970 pw–4978
time„5618754 us cost 13562 size71118060 cardR73531)
   1015       MAT_VIEW ACCESS FULL TEST5_MV (cr‡ pr‡ pw=0 time&700 us
cost6 size‘35 card15)
1198431       HASH JOIN RIGHT OUTER (cr'52192 pr704883 pw–4978
time„1740385 us cost 13509 size23656281 cardR73531)
2751771        MAT_VIEW ACCESS FULL TEST6_MV (crB237 prB235 pw=0
time†75452 us cost544 size$678873 card'42097)
1198431        HASH JOIN  (cr'09955 pr645092 pw•4828 time‚4612883
us cost35880 size76194502 cardR73531)
15768699         INDEX FAST FULL SCAN TEST7_MV_IDX1 (cru591 pru323 pw=0
time774306 us cost 219 size&6959245 card703485)(object id 210790)
1234421         HASH JOIN  (cr&34364 pr510125 pw‰6548 time3786908
us cost28067 size24678725 cardX87461)
18096625          MAT_VIEW ACCESS FULL TEST8_MV (cr"0012 pr!8960 pw=0
timet46131 us costW638 sizeT3480840 card531640)
1234421          HASH JOIN RIGHT OUTER (cr$14352 pr191128 pwx7741
time08160557 us cost76987 size49316140 cardY24310)
  89403           MAT_VIEW ACCESS FULL TEST9_MV (cr67 pr7 pw=0
time1651 us cost84 sizeq4944 card‰368)
1234421           HASH JOIN RIGHT OUTER (cr$12985 pr191091 pwx7741
time06683843 us cost76583 size01921660 cardY24310)
112372            VIEW  (crQ91 pr990 pw=0 time30045 us costR60
size166000 card6400)
112372             HASH GROUP BY (crQ91 pr990 pw=0 time78962 us
costR60 sizeV30400 card6400)
592588              HASH JOIN  (crQ91 pr990 pw=0 time08488 us
cost#70 size 768256 cardW6896)
112372               MAT_VIEW ACCESS FULL TEST10_MV (cr˜3 pr pw=0
time5139 us cost(1 size$34014 card0637)
599666               MAT_VIEW ACCESS FULL TEST11_MV (crB08 pr976 pw=0
time867835 us cost69 sizeƒ86280 cardY9020)
1234421            HASH JOIN OUTER (cr$07794 pr187101 pwx7741
time03391491 us cost33469 sizeq6841510 cardY24310)
1234421             HASH JOIN RIGHT OUTER (cr‚0311 pr†8223 pwV296
timeI864926 us cost)1476 size@8777390 cardY24310)
10419387              MAT_VIEW ACCESS FULL TEST12_MV (cr8797 pr6955
pw=0 time225378 us cost@184 size5353205 card411785)
1234421              HASH JOIN  (crg1514 prh7540 pw"568 time#888401
us cost!9858 size31761360 cardY24310)
2502803               MAT_VIEW ACCESS FULL TEST13_MV (crI0883 prH4364
pw=0 time431145 us cost9870 size8274124 cardX02556)
17393219               MAT_VIEW ACCESS FULL TEST14_MV (cr0631 pr0608
pw=0 timeF86498 us costG586 sizeE4360077 card828151)
44029438             VIEW  (cr87483 pr#09764 pwr2362 timet1267887
us cost83366 size#31410432 cardD834816)
44029438              HASH GROUP BY (cr87483 pr#09764 pwr2362
timeq9813126 us cost83366 size˜6365952 cardD834816)
233541065               MAT_VIEW ACCESS FULL TEST15_MV (cr87483
pr87402 pw=0 time2764611 us costB9931 sizeR27415732
card#7609806)


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'63968 pr698368 pw•8423
time50824034 us cost 18213 sizeP90043700 card211550)
    855   MAT_VIEW ACCESS FULL TEST_MV (cr'9 pr=0 pw=0 time'57 us
costˆ sizeB750 card…5)
1198431   HASH JOIN RIGHT OUTER (cr'63689 pr698368 pw•8423
time45160774 us cost 18089 size093810992 cardv57948)
    832    MAT_VIEW ACCESS FULL TEST_MV (cr'9 pr=0 pw=0 timew75 us
costˆ sizeA600 cardƒ2)
1198431    HASH JOIN RIGHT OUTER (cr'63410 pr698368 pw•8423
time40348133 us cost 17976 size70269792 cardR83248)
   9118     VIEW  (cr503 prQ pw=0 time71926 us costE69 sizeu7422
card‡06)
   9118      HASH GROUP BY (cr503 prQ pw=0 time68628 us costE69
sizeu7422 card‡06)
   9207       VIEW  (cr503 prQ pw=0 time54731 us costE67
sizeu7422 card‡06)
   9207        SORT UNIQUE (cr503 prQ pw=0 time51146 us costE67
size48345 card‡06)
   9229         UNION-ALL  (cr503 prQ pw=0 time27051 us)
   1603          MAT_VIEW ACCESS FULL TEST1_MV (cr712 prQ pw=0
time˜4036 us costC24 size6963 card™9)
   6706          MAT_VIEW ACCESS FULL TEST2_MV (crr7 pr=0 pw=0 timed68
us cost!0 size'8226 cardg86)
    920          MAT_VIEW ACCESS FULL TEST3_MV (crd pr=0 pw=0 timeR1 us
cost0 size3156 card’1)
1198431     HASH JOIN RIGHT OUTER (cr'46907 pr698317 pw•8423
time34470126 us cost 13390 size10627216 cardR83248)
      2      MAT_VIEW ACCESS FULL TEST4_MV (cr=2 pr=0 pw=0 timeg us
cost size card=2)
1198431      HASH JOIN RIGHT OUTER (cr'46905 pr698317 pw•8423
time28665172 us cost 13360 size73644480 cardR83248)
   1003       MAT_VIEW ACCESS FULL TEST5_MV (cr‡ prh pw=0 time126 us
cost6 size27 card03)
1198431       HASH JOIN RIGHT OUTER (cr'46818 pr698249 pw•8423
time21552574 us cost 13307 size26095248 cardR83248)
2744178        MAT_VIEW ACCESS FULL TEST6_MV (crB236 prB235 pw=0
timeA38935 us cost544 size$678873 card'42097)
1198431        HASH JOIN  (cr'04582 pr638472 pw”8273 time4206514
us cost35562 size78546016 cardR83248)
15768699         INDEX FAST FULL SCAN TEST7_MV_IDX1 (cru549 pru101 pw=0
time&733243 us cost877 size&7451106 card732418)(object id 220252)
1234403         HASH JOIN  (cr&29033 pr503727 pwˆ9993 time5614654
us cost28052 size24678725 cardX87461)
18090410          MAT_VIEW ACCESS FULL TEST8_MV (cr!9015 pr!8948 pw=0
timeW52544 us costW638 sizeT3480840 card531640)
1234403          HASH JOIN RIGHT OUTER (cr$10018 pr184928 pwx1386
time52178325 us cost76972 size49316140 cardY24310)
  89319           MAT_VIEW ACCESS FULL TEST9_MV (cr67 pr=0 pw=0
time3970 us cost84 sizeq4552 card‰319)
1234403           HASH JOIN RIGHT OUTER (cr$08651 pr184928 pwx1386
time50684012 us cost76568 size01921660 cardY24310)
112372            VIEW  (crQ91 pr pw=0 time51659 us costR48
size113870 card5598)
112372             HASH GROUP BY (crQ91 pr pw=0 time95964 us
costR48 sizeV01528 card5598)
592588              HASH JOIN  (crQ91 pr pw=0 time`5467 us
cost#67 size 718432 cardW5512)
112372               MAT_VIEW ACCESS FULL TEST10_MV (cr˜3 prq pw=0
time3763 us cost(1 size$20528 card0024)
597115               MAT_VIEW ACCESS FULL TEST11_MV (crB08 pr pw=0
time 6789 us cost69 sizeƒ59470 cardY7105)
1234403            HASH JOIN OUTER (cr$03460 pr184838 pwx1386
time47993476 us cost33469 sizeq6841510 cardY24310)
1234403             HASH JOIN RIGHT OUTER (cr1645 pr†7920 pwV296
time†075318 us cost)1476 size@8777390 cardY24310)
10415162              MAT_VIEW ACCESS FULL TEST12_MV (cr6866 pr6861
pw=0 time255846 us cost@184 size5353205 card411785)
1234403              HASH JOIN  (crf4779 prh7331 pw"568
time0442429 us cost!9858 size31761360 cardY24310)
2500348               MAT_VIEW ACCESS FULL TEST13_MV (crH4885 prH4872
pw=0 time)117663 us cost9870 size8274124 cardX02556)
17386839               MAT_VIEW ACCESS FULL TEST14_MV (cr9894 pr9891
pw=0 time465992 us costG586 sizeE4360077 card828151)
44029438             VIEW  (cr91815 pr#07804 pwq6007 timeh5501566
us cost83366 size#31410432 cardD834816)
44029438              HASH GROUP BY (cr91815 pr#07804 pwq6007
timef3312375 us cost83366 size˜6365952 cardD834816)
233541065               MAT_VIEW ACCESS FULL TEST15_MV (cr91815
pr91797 pw=0 time4294475 us costB9931 sizeR27415732
card#7609806)

Thanks


--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l

• References:
  • Fetch timing-Performance
    • From: oracledba

Other related posts:

• » Fetch timing-Performance- oracledba
• » Re: Fetch timing-Performance - David Fitzjarrell
• » Re: Fetch timing-Performance- David Fitzjarrell

1. Home
2. oracle-l
3. Archive
4. 10-2013

---

• » Previous by Date
• » Next by Date
• » Related Posts
• » View list details
• » Manage your subscription

---