Re: Fetch timing-Performance

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 2 Oct 2013 12:17:35 -0700 (PDT)
Message-ID: <1380741455.65579.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>



Oracle version?  I don't find it stated anywhere.

David Fitzjarrell  



 From: oracledba <oracledba71_at_gmail.com> To: David Fitzjarrell <oratune_at_yahoo.com> Cc: Oracle-L Freelists <Oracle-L_at_freelists.org> Sent: Wednesday, October 2, 2013 12:48 PM Subject: Re: Fetch timing-Performance   

The OS is RedHat Linux 2.6.18-308.4.1.el5.I did parameters comparison b/n both database at first place.They're all same including SGA and PGA sizes.Both DBs are running out of same ORACLE_HOME.  

I also ran the SQL in an isolation with just one session on both DBs without any other connections to the database.Still the same result.

Ran ashrpt as well.It shows the following:

Top User events
Event
Event Class
% Event
Avg Active Sessions
CPU + Wait for CPU CPU 86.83 0.89
direct path read temp User I/O 3.97 0.04 direct path read User I/O 1.64 0.02
Top Call Types
Call Type
Count
% Activity
Avg Active Sessions
FETCH 3,700 79.91 0.82
V8 Bundled Exec 603 13.02 0.13
Top Sessions
CPU + Wait for CPU 85.87
direct path read temp 3.97
direct path read
 1.64  

On Wed, Oct 2, 2013 at 1:59 PM, David Fitzjarrell <oratune_at_yahoo.com> wrote: More questions:

>What is the operating system in use?
>Have you checked both spfiles/pfiles for conflicts since these are clones?
>Are they running out of the same ORACLE_HOME?
>
>David Fitzjarrell
>
> From: Oracle-l <oracledba71_at_gmail.com>
>To: David Fitzjarrell <oratune_at_yahoo.com>
>Cc: Oracle-L Freelists <Oracle-L_at_freelists.org>
>Sent: Wednesday, October 2, 2013 5:44 AM
>Subject: Re: Fetch timing-Performance
>
>
>They ran at different times on both DBs. Both databases are using ASM and same data group. The significant wait on the second DB is CPU. There are 4 Cpus in the server. The AAS on 2nd DB is always more than 5. Awr reports high CPU usage 75% of  dbtime spent on the Cpu. The next highest wait event is direct path read write temp. Sent from my iPhone
>On Oct 1, 2013, at 1:19 PM, David Fitzjarrell <oratune_at_yahoo.com> wrote:
>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_at_gmail.com>
>>To: Oracle-L Freelists <Oracle-L_at_freelists.org>
>>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 onthe same server.Both of them have exactly same init.ora parameters andstats 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    currentrows ------- ------  -------- ---------- ---------- ---------- --------------------Parse        1      0.97      1.01          0          00          0Execute      1      0.00      0.00          0          00          0Fetch    79897    692.59    1152.05    3706124    2769429          51198431------- ------  -------- ---------- ---------- ---------- --------------------total    79899    693.57    1153.07    3706124    2769429          5 1198431Misses in library cache

 during parse: 1Optimizer mode: ALL_ROWSParsing user id: 105Rows     Row Source Operation -------  ---------------------------------------------------1198431  HASH JOIN RIGHT OUTER (cr'69429 pr706124 pw–4978time†1421317 us cost 18438 sizeP93556298 card219287)     867   MAT_VIEW ACCESS FULL TEST_MV (cr'9 pr&2 pw=0 time%73 uscostˆ sizeC350 card†7)1198431   HASH JOIN RIGHT OUTER (cr'69150 pr705862 pw–4978time…8087245 us cost 18314 size094918356 cardv60689)    844    MAT_VIEW ACCESS FULL TEST_MV (cr'9 pr=0 pw=0 times96 uscostˆ 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 costE92size0231 card“13)   9118      HASH GROUP BY (cr590 pr‰0 pw=0 time%55234 us costE92 size0231 card“13)   9207       VIEW  (cr590
 pr‰0 pw=0 time%45205 us costE91size0231 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=0time1018 us costC24 sizeH396 card08)  6706          MAT_VIEW ACCESS FULL TEST2_MV (crr7 pr$5 pw=0time"486 us cost!0 size)0444 cardp84)     920          MAT_VIEW ACCESS FULL TEST3_MV (cr1 pr0 pw=0time6090 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 uscost 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 uscost6 size‘35 card15)1198431      HASH JOIN RIGHT OUTER (cr'52192 pr704883 pw–4978time„1740385 us cost 13509 size23656281 cardR73531)2751771        MAT_VIEW ACCESS FULL TEST6_MV (crB237 prB235 pw=0time†75452 us cost544 size$678873 card'42097) 1198431        HASH JOIN  (cr'09955 pr645092 pw•4828 time‚4612883us 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 time3786908us cost28067 size24678725 cardX87461) 18096625          MAT_VIEW ACCESS FULL TEST8_MV (cr"0012 pr!8960 pw=0timet46131 us costW638 sizeT3480840 card531640)1234421          HASH JOIN RIGHT OUTER (cr$14352 pr191128  pwx7741time08160557 us cost76987 size49316140 cardY24310)  89403          MAT_VIEW ACCESS FULL TEST9_MV (cr67 pr7 pw=0time1651 us cost84 sizeq4944 card‰368) 1234421          HASH JOIN RIGHT OUTER (cr$12985 pr191091 pwx7741time06683843 us cost76583 size01921660 cardY24310) 112372            VIEW  (crQ91 pr990 pw=0 time30045 us costR60 size166000 card6400) 112372            HASH GROUP BY (crQ91 pr990 pw=0 time78962 uscostR60 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=0time5139 us cost(1 size$34014 card0637) 599666              MAT_VIEW ACCESS FULL TEST11_MV (crB08 pr976 pw=0time867835 us cost69 sizeƒ86280 cardY9020) 1234421            HASH JOIN OUTER  (cr$07794 pr187101 pwx7741time03391491 us cost33469 sizeq6841510 cardY24310)1234421            HASH JOIN RIGHT OUTER (cr‚0311 pr†8223 pwV296 timeI864926 us cost)1476 size_at_8777390 cardY24310)10419387              MAT_VIEW ACCESS FULL TEST12_MV (cr8797 pr6955pw=0 time225378 us cost_at_184 size5353205 card411785) 1234421              HASH JOIN  (crg1514 prh7540 pw"568 time#888401us cost!9858 size31761360 cardY24310)2502803              MAT_VIEW ACCESS FULL TEST13_MV (crI0883 prH4364pw=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 timet1267887us cost83366 size#31410432 cardD834816) 44029438              HASH GROUP BY (cr87483
 pr#09764 pwr2362timeq9813126 us cost83366 size˜6365952 cardD834816)233541065               MAT_VIEW ACCESS FULL TEST15_MV (cr87483 pr87402 pw=0 time2764611 us costB9931 sizeR27415732card#7609806)call     count       cpu    elapsed       disk      query    currentrows------- ------  -------- ---------- ---------- ---------- --------------------Parse        1      0.94       1.01          0          0 0           0Execute      1      0.00       0.00          0          00           0Fetch    79897   3640.81    4290.46    3698368    2763968          01198431 ------- ------  -------- ---------- ---------- ---------- --------------------total    79899   3641.76    4291.48    3698368    2763968          01198431Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 109Rows     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 uscostˆ 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•8423time40348133 us cost 17976 size70269792 cardR83248)   9118     VIEW  (cr503 prQ pw=0 time71926 us costE69 sizeu7422card‡06)   9118      HASH GROUP BY (cr503 prQ pw=0 time68628 us costE69sizeu7422 card‡06)   9207       VIEW  (cr503 prQ pw=0 time54731 us costE67sizeu7422 card‡06)    9207        SORT UNIQUE (cr503 prQ pw=0
 time51146 us costE67size48345 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 timed68us cost!0 size'8226 cardg86)    920          MAT_VIEW ACCESS FULL TEST3_MV (crd pr=0 pw=0 timeR1 uscost0 size3156 card’1)1198431    HASH JOIN RIGHT OUTER (cr'46907 pr698317 pw•8423time34470126 us cost 13390 size10627216 cardR83248)      2      MAT_VIEW ACCESS FULL TEST4_MV (cr=2 pr=0 pw=0 timeg uscost size card=2) 1198431      HASH JOIN RIGHT OUTER (cr'46905 pr698317 pw•8423time28665172 us cost 13360 size73644480 cardR83248)  1003      MAT_VIEW ACCESS FULL TEST5_MV (cr‡ prh pw=0 time126 us cost6 size27 card03)1198431      HASH JOIN RIGHT OUTER  (cr'46818 pr698249 pw•8423time21552574 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 time4206514us cost35562 size78546016 cardR83248)15768699        INDEX FAST FULL SCAN TEST7_MV_IDX1 (cru549 pru101 pw=0time&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=0timeW52544 us costW638 sizeT3480840 card531640) 1234403          HASH JOIN RIGHT OUTER (cr$10018 pr184928 pwx1386time52178325 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 pwx1386time50684012 us cost76568 size01921660 cardY24310) 112372            VIEW  (crQ91 pr pw=0 time51659 us costR48size113870 card5598) 112372            HASH GROUP BY (crQ91 pr pw=0 time95964 uscostR48 sizeV01528 card5598) 592588              HASH JOIN  (crQ91 pr pw=0 time`5467 uscost#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=0time 6789 us cost69 sizeƒ59470 cardY7105) 1234403            HASH JOIN OUTER (cr$03460 pr184838 pwx1386time47993476 us cost33469 sizeq6841510 cardY24310)1234403            HASH JOIN RIGHT OUTER (cr1645 pr†7920  pwV296time†075318 us cost)1476 size_at_8777390 cardY24310)10415162              MAT_VIEW ACCESS FULL TEST12_MV (cr6866 pr6861 pw=0 time255846 us cost_at_184 size5353205 card411785)1234403              HASH JOIN  (crf4779 prh7331 pw"568time0442429 us cost!9858 size31761360 cardY24310) 2500348              MAT_VIEW ACCESS FULL TEST13_MV (crH4885 prH4872pw=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 timeh5501566us cost83366 size#31410432 cardD834816)44029438              HASH GROUP BY (cr91815 pr#07804 pwq6007timef3312375 us cost83366 size˜6365952 cardD834816)233541065              MAT_VIEW ACCESS FULL TEST15_MV (cr91815  pr91797 pw=0 time4294475 us costB9931 sizeR27415732card#7609806)Thanks--http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 02 2013 - 21:17:35 CEST

Original text of this message