This discussion is archived
1 2 3 4 Previous Next 45 Replies Latest reply: Nov 24, 2009 8:25 AM by 724046 Go to original post RSS
  • 30. Re: Help with Parallelization on our SPARC server...we are drowning!
    614235 Explorer
    Currently Being Moderated
    Maurice,

    It looks like you are right to question exactly what a T2 processor is internally. I believed that it was 8 cores and 8 threads each for 64 virtual CPUs for all T2's, but the Sun web site says something different. Gunners said that he has a T6320 with a 1.2 GHz CPU. From the [Sun Blade T6320 Specs|http://www.sun.com/servers/blades/t6320/specs.xml] web page it says:
    Processor - Four-core 1.2-GHz, eight-core 1.4-GHz, or eight-core 1.6-GHz UltraSPARC T2 processor; one processor per system, maximum 64 threads
    Threads - Up to 64 simultaneous execution threads
    On the one hand I was right that the T2 can have 8 cores and 8 threads each for 64 threads total. On the other hand, the 1.2 GHz T2 only has 4 cores in it, for 32 threads total. Which will explain why Oracle only has the CPU_COUNT set to 32, and not 64 on Gunner's system.

    Unfortunately, this actually makes things much worse for Gunners, as he only has 4 CPU execution cores that can actually execute instructions, not 8 as I assumed. And at 1.2 GHz this will be at least half the net performance of a quad core Intel CPU running at over 2 GHz. Gunners, I am sorry, but to me it looks like you have been sold a puppy. That 1.2 GHz 4 core T2 CPU is next to useless - the cores are too slow at 1.2 GHz, and there are only 4 of them. Any current Intel or AMD quad core CPU runs at over 2 GHz and would wipe the floor with the T2, if you see what I mean.
    As far as I understood each core of the T2 is subdivided into 4 virtual CPUs.
    Maurice, I think you may have got some of the technical details of how the T2 (Niagara) CPU cores actually work wrong. Each core is a relatively simple UltraSPARC based core, and still only executes one instruction at a time. It is no different to other CPUs in this respect. The key feature is that it keeps within the CPU the state of 4 jobs or threads - which are the virtual CPUs Solaris sees. But internally there is still a normal UltraSPARC-II CPU core executing at 1.2 GHz, or more.

    The virtualization works this way. When the currently executing process, lets call it thread 1, causes the CPU core to do a memory access, the CPU would otherwise have to wait (stall) for many CPU cycles until the data came back from memory. And this is quite a long period relative to the CPU cycle time at 1.2 GHz. In the Niagara based processors, when thread 1 does a memory access, the CPU core immediately switches its registers and internal stuff to that of thread 2, and carries on by executing instructions for thread 2. That way there are no wasted, unused CPU cycles. Eventually thread 2 will do a memory access, and the CPU core will switch to thread 3; and when that accesses memory it will switch to thread 4.

    These thread switches within the CPU core are very fast. The principle of the Niagara CPU design is to use what would otherwise be idle CPU cycles during relatively long memory accesses, to instead execute instructions from another thread (process). This increases the overall efficiency of the CPU, because less execution cycles are wasted while it waits for a memory access to complete.

    The switching between threads in the CPU core only takes place when the currently executing thread performs a memory access. If a thread existed that never did a memory access - all of its data was in the CPU cache for instance - then the CPU core would never switch to another thread. Of course, such a scenario would never occur in reality, but the point is that the CPU core is not shared equally between threads. A thread executes until it does a memory access, and it only is executed again after all the other threads have also done memory accesses and the CPU core has switched between them.

    It also means that if only one thread is running, and there are no other runnable threads, then it gets full use of the 1.2 GHz CPU core. And when it does a memory access, the CPU core will wait because there are no other threads to run, and carry on with that same thread when the memory access completes.

    I know this is a bit of a detailed explanation, but it is my understanding of how the T2 and T1 Niagara based processors actually work, and how one CPU core is "shared" between multiple execution threads. The design principle was to make use of wasted CPU cycles during memory access to execute instructions from another thread. It was not designed to ensure "equal" usage of the CPU core by all threads. The goal was to increase CPU efficiency internally, so that no CPU cycles were wasted. Which is why I keep saying that as more threads do run on each CPU core, and the sharing increases, so each thread gets less and less of the 1.2 GHz of that CPU core. And at 4 concurrent threads per core, they would each achieve the equivalent of a 300 MHz CPU in terms of instructions executed per unit of time. Likewise, 8 threads per core reduces the throughput per thread to that of a 150 MHz CPU. Clearly a case of more threads equals less throughput per thread.

    John
  • 31. Re: Help with Parallelization on our SPARC server...we are drowning!
    137669 Explorer
    Currently Being Moderated
    Hi John,

    As far as I understood that's not the way the T2 works. As far as I undersood it each of it's cores are divided into 4 virtual CPUs threads being each one limited to a 1/4 of the frequency of one core.

    That's also why, even if there is no load at all on the sever, that one single serial process is much slower on a T2 as if the same process would be exeucted on a sever with ultraspac III (also @1.2Ghz).

    https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=781763.1
    So a single process may report it is nearly 100% CPU bound (as measured via getrusage() calls) from its perspective, but it can only keep one virtual CPU busy at most. So at the system level, the computer may only appear to be (100/N)% busy (where N is # virtual CPUs) due to the activity of that single process
    If the following formula is correct it would confirm my assumption:
    (100/N)% busy (where N is # virtual CPUs)

    Regards
    Maurice
  • 32. Re: Help with Parallelization on our SPARC server...we are drowning!
    614235 Explorer
    Currently Being Moderated
    The way I describe how the Niagara CPU core works in the T2 processor is based on the stuff I have read from Sun - now over 4 years ago. However, which of the two of us is more correct about what really goes on inside a T2 is another thing ;-)

    For instance, the information on [Improving Application Efficiency Through Chip Multi-Threading|http://developers.sun.com/solaris/articles/chip_multi_thread.html] describes how the multi-threading uses what would otherwise be idle cycles during memory access. There are diagrams that clearly show the timeslicing within a CPU core between each thread, and how different threads overlap in time, but only one is running on a CPU core at any moment in time. Within this it says:
    *1.2 Chip Multi-Processing With Hardware Threads*
    The OS schedules software threads on the cores in a US-IV processor. In the case of a memory access, a core needs to wait for the access to complete while the other core continues processing instructions. This is not very efficient as a core wastes clock cycles. The core using more registers and logic in hardware can context-switch the software thread to another software thread. This logic in hardware holding the thread state is called a hardware thread. The hardware thread appears as a logical processor to the OS. A processor with n hardware threads would appear as n logical processors.
    ...
    The OS schedules software threads on these logical processors. On a memory access, the core switches to a new hardware thread so it does not have to wait for the access to be complete. The context switch is in hardware so no clock cycles are wasted. Hardware threads improve the efficiency of a processor by processing instructions every clock cycle.
    So it is pretty clear that the switch between threads happens when the currently executing thread does a memory access.

    The trouble is probably that the diagrams Sun uses all show perfect uniformity between the threads and their CPU usage. Which matches up with your description of each thread getting 1/n'th of the time on a n-thread CPU core. Unfortunately I do not thing that real world applications would have such a perfectly uniform memory access pattern.

    Other things mentioned in this article include:
    Each core can process one instruction every cycle
    The Niagara processor can process eight instructions simultaneously every clock cycle or one instruction per core.
    Simultaneous multi-threading uses hardware threads layered on top of a core to execute instructions from multiple threads. The hardware threads consist of all the different registers to keep track of a thread execution state. These hardware threads are also called logical processors.
    The efficiency of a CMT-enabled system can be identified by the memory stalls, cache references and misses, and number of instructions processed per cycle. Cache misses, memory stalls, and instructions processed can be obtained using the cputrack, cpustat, and har utilities on the Solaris OS. An ideal system with no cache misses or stalls should run at 100% efficiency.
    There is also a [White Paper on Throughput Computing|http://www.sun.com/processors/whitepapers/throughput_whitepaper.pdf] (PDF) which describes the way that the Niagara processors work in the same way:
    Unlike traditional single-threaded processors and even most current multi-core processors, hardware multithreaded processors allow rapid switching between active threads as other threads stall for memory.
    The negative effects of memory latency are masked since the processor and the memory subsystems remain active in parallel to the processor execution pipeline.
    Overall, all of the documents I have read on the Niagara processor describe it the same as these two - thread switching takes place on a memory access by the currently executing thread. There are references at the end of the first article to other documents, for those that want more detail on the Niagara based processors.

    John
  • 33. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    John,

    Thanks for the reply. I should be sending all my results to Oracle Support today after all the testing they wanted me to do. Hopefully they have some answers for us.

    As for the Server, seems like I was right from the start. Then vendor sold this company a crap server for the type of work there doing and sold it off as a 8 core machine when I kept pointing it out that it was a 4 core machine based on the Sun Server specs. Does not seem like they upgraded the CPU either.
  • 34. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    this is what I am seeing
    () VADP> psrinfo -pv
    The physical processor has 64 virtual processors (0-63)
      UltraSPARC-T2 (chipid 0, clock 1165 MHz)
    So the vendor poeple are telling me this:
    Gunners wrote:
    Hello,

                The server has an 8 cores single 1.2 GHz processor with the possibility of 8 threads per core.

    # psrinfo -pv
    The physical processor has 64 virtual processors (0-63)
      UltraSPARC-T2 (chipid 0, clock 1165 MHz)
    Regards,
  • 35. Re: Help with Parallelization on our SPARC server...we are drowning!
    Taral Journeyer
    Currently Being Moderated
    What is the query that was running.

    there are two things here

    1. parallel_execution_message_size integer 32768
    Why this much big ? Did you benchmarked with different message size. This might cause you problem with high value

    2. PX Deq Credit: send blkd
    This is major event which is there which says producer is producing more then consumer. So may be due to some parallel operation are converted into serial.

    Now, the only way to look this is to find problem SQL which is running while this is happening. Also, try to reduce DOP this might help you.
  • 36. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    Taral Desai wrote:
    What is the query that was running.

    there are two things here

    1. parallel_execution_message_size integer 32768
    Why this much big ? Did you benchmarked with different message size. This might cause you problem with high value

    2. PX Deq Credit: send blkd
    This is major event which is there which says producer is producing more then consumer. So may be due to some parallel operation are converted into serial.

    Now, the only way to look this is to find problem SQL which is running while this is happening. Also, try to reduce DOP this might help you.
    We set it 32K from 8k as Oracle advised us to due to high waits.

    PX Deq Credit: send blkd waits does occur but not as much when we increase PARALLEL_MAX_SERVER to a value above 300 as our queries never get downgraded to serial. Any value below it does.
    Now, the only way to look this is to find problem SQL which is running while this is happening. Also, try to reduce DOP this might help you.
    Well we currently have not DOP set (if not set then I assume its defaulted to 1???).

    I ran the following test scenarios for Oracle:

    Scenario 1 - Baseline
    -As is, no DOP
    -Forced Parallel, no DOP
    -Objects with Defaulted degree’s of parallelism

    Scenario 2 – Setting Parallel Max Servers to 300
    -As is, no DOP
    -Forced Parallel, no DOPl
    -Objects with Defaulted degree’s of parallelism

    Scenario 3 – Setting Parallel Max Servers to 640
    -As is, no DOP
    -Forced Parallel, no DOP
    -Objects with Defaulted degree’s of parallelism

    The average timings were about 2 hours each...but for Scenario 2, Objects with Defaulted degree’s of parallelism, it completed this in 83 minutes. Best timing of them all. Some of the stats I collected over this time perdiod were as follows:
  • 37. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    SQL> @"H:\My Documents\Oracle\Databases\VAD\VADP\TestRuns\px_stats.sql";
    SELECT * FROM GV$SYSSTAT WHERE name LIKE 'Parallel operation%';
    
       INST_ID STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
    ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------
             1        318 Parallel operations not downgraded                                       32         95 2410168744
             1        319 Parallel operations downgraded to serial                                 32          0 4286011915
             1        320 Parallel operations downgraded 75 to 99 pct                              32          0  322858058
             1        321 Parallel operations downgraded 50 to 75 pct                              32         34 3281923117
             1        322 Parallel operations downgraded 25 to 50 pct                              32          7 3330456527
             1        323 Parallel operations downgraded 1 to 25 pct                               32          0 3721245209
     
    6 rows selected
    
    SELECT * FROM V$PX_PROCESS order by 1;
     
    SERVER_NAME STATUS           PID SPID            SID    SERIAL#
    ----------- --------- ---------- ------------ ------ ----------
    P000        IN USE            18 12041          1077         16
    P001        IN USE            19 12043           957         15
    P002        IN USE            20 12045          1052         15
    P003        IN USE            21 12047          1059         15
    P004        IN USE            22 12049          1076         16
    P005        IN USE            23 12051           992         15
    P006        IN USE            24 12053          1058         15
    P007        IN USE            25 12055          1043         15
    P008        IN USE            26 12057           973         15
    P009        IN USE            27 12059          1020         15
    P010        IN USE            28 12061           964         15
    P011        IN USE            29 12063          1016         15
    P012        IN USE            30 12065          1072         15
    P013        IN USE            31 12067          1009         16
    P014        IN USE            32 12069          1012         15
    P015        IN USE            33 12071          1054         15
    P016        IN USE            38 12100          1044         15
    P017        IN USE            39 12102           972         15
    P018        IN USE            40 12104           956         15
    P019        IN USE            41 12106           993         15
    P020        IN USE            42 12108           962         15
    P021        IN USE            43 12110          1078         15
    P022        IN USE            44 12112          1017         15
    P023        IN USE            45 12114          1022         15
    P024        IN USE            46 12116           958         15
    P025        IN USE            47 12118          1081         15
    P026        IN USE            48 12120          1024         15
    P027        IN USE            49 12122          1023         15
    P028        IN USE            50 12124          1056         15
    P029        IN USE            51 12126          1075         15
    P030        IN USE            52 12128           969         15
    P031        IN USE            53 12130           967         15
    P032        IN USE            54 12132          1021         15
    P033        IN USE            55 12134          1033         15
    P034        IN USE            56 12136           980         15
    P035        IN USE            57 12138          1050         16
    P036        IN USE            58 12140          1061         15
    P037        IN USE            59 12142           989         15
    P038        IN USE            60 12144          1060         15
    P039        IN USE            61 12146           963         15
    P040        IN USE            62 12148          1055         15
    P041        IN USE            63 12150          1035         15
    P042        IN USE            64 12152          1014         15
    P043        IN USE            65 12154          1082         31
    P044        IN USE            66 12156           954         15
    P045        IN USE            67 12158          1019         15
    P046        IN USE            68 12160           968         15
    P047        IN USE            69 12162          1064         15
    P048        IN USE            70 12164          1057         15
    P049        IN USE            71 12166          1070         15
    P050        IN USE            72 12168           997         15
    P051        IN USE            73 12170          1007         15
    P052        IN USE            74 12172          1039         15
    P053        IN USE            75 12174           975         15
    P054        IN USE            76 12176           966         16
    P055        IN USE            77 12178           999         15
    P056        IN USE            78 12180          1068         15
    P057        IN USE            79 12182          1002         15
    P058        IN USE            80 12184          1006         15
    P059        IN USE            81 12186          1032         15
    P060        IN USE            82 12188           959         15
    P061        IN USE            83 12190          1008         15
    P062        IN USE            84 12192          1051         15
    P063        IN USE            85 12194          1027         15
    P064        IN USE            86 12196           986         15
    P065        IN USE            87 12198          1026         15
    P066        IN USE            88 12200           994         15
    P067        IN USE            89 12202          1066         15
    P068        IN USE            90 12204           960         15
    P069        IN USE            91 12206          1065         15
    P070        IN USE            92 12208          1011         15
    P071        IN USE            93 12210          1030         15
    P072        IN USE            94 12212          1029         15
    P073        IN USE            95 12214           996         15
    P074        IN USE            96 12216           961         15
    P075        IN USE            97 12218          1079         15
    P076        IN USE            98 12220          1047         15
    P077        IN USE            99 12222          1074         16
    P078        IN USE           100 12224           970         15
    P079        IN USE           101 12226          1041         15
    P080        IN USE           102 12228          1046         15
    P081        IN USE           103 12230          1053         15
    P082        IN USE           104 12232          1001         15
    P083        IN USE           105 12234          1063         15
    P084        IN USE           106 12236           979         15
    P085        IN USE           107 12238          1031         15
    P086        IN USE           108 12240          1003         15
    P087        IN USE           109 12242          1025         15
    P088        IN USE           110 12244          1045         15
    P089        IN USE           111 12246           983         15
    P090        IN USE           112 12248           987         15
    P091        IN USE           113 12250          1010         15
    P092        IN USE           114 12252          1013         15
    P093        IN USE           115 12254           971         15
    P094        IN USE           116 12256          1080         15
    P095        IN USE           117 12258          1000         15
    P096        IN USE           118 12260           974         15
    P097        IN USE           119 12262          1073         15
    P098        IN USE           120 12264           984         16
    P099        IN USE           121 12266           976         15
    P100        IN USE           122 12268           981         15
    P101        IN USE           123 12270          1069         15
    P102        IN USE           124 12272           955         15
    P103        IN USE           125 12274           998         15
    P104        IN USE           126 12276           977         15
    P105        IN USE           127 12278           988         15
    P106        IN USE           128 12280          1037         15
    P107        IN USE           129 12282          1034         15
    P108        IN USE           130 12284          1004         15
    P109        IN USE           131 12286           990         15
    P110        IN USE           132 12288           995         15
    P111        IN USE           133 12290          1071         15
    P112        IN USE           134 12292          1040         15
    P113        IN USE           135 12294          1036         15
    P114        IN USE           136 12296           982         15
    P115        IN USE           137 12298          1038         15
    P116        IN USE           138 12300           965         15
    P117        IN USE           139 12302          1015         15
    P118        IN USE           140 12304          1005         15
    P119        IN USE           141 12306          1049         15
    P120        IN USE           142 12308          1062         15
    P121        IN USE           143 12310           991         15
    P122        IN USE           144 12312           978         15
    P123        IN USE           145 12314          1067         15
    P124        IN USE           146 12316          1048         15
    P125        IN USE           147 12318          1028         15
    P126        IN USE           148 12320           985         15
    P127        IN USE           149 12322          1042         15
    P128        IN USE           150 12328           937         15
    P129        IN USE           151 12330           912         15
    P130        IN USE           152 12332           924         15
    P131        IN USE           153 12334           864         13
    P132        IN USE           154 12336           921         15
    P133        IN USE           155 12338           900         15
    P134        IN USE           156 12340           929         15
    P135        IN USE           157 12342           827         13
    P136        IN USE           158 12344           952         15
    P137        IN USE           159 12346           836         13
    P138        IN USE           160 12348           890         15
    P139        IN USE           161 12350           886         13
    P140        IN USE           162 12352           946         15
    P141        IN USE           163 12354           876         13
    P142        IN USE           164 12356           841         13
    P143        IN USE           165 12358           926         15
    P144        IN USE           166 12360           826         13
    P145        IN USE           167 12362           839         13
    P146        IN USE           168 12364           871         13
    P147        IN USE           169 12366           916         15
    P148        IN USE           170 12368           858         13
    P149        IN USE           171 12370           868         13
    P150        IN USE           172 12372           914         15
    P151        IN USE           173 12374           932         15
    P152        IN USE           174 12376           852         13
    P153        IN USE           175 12378           869         13
    P154        IN USE           176 12380           861         13
    P155        IN USE           177 12382           898         15
    P156        IN USE           178 12384           850         13
    P157        IN USE           179 12386           835         13
    P158        IN USE           180 12388           953         15
    P159        IN USE           181 12390           855         13
    P160        IN USE           182 12392           854         13
    P161        IN USE           183 12394           828         13
    P162        IN USE           184 12396           847         13
    P163        IN USE           185 12398           832         13
    P164        IN USE           186 12400           942         15
    P165        IN USE           187 12402           849         13
    P166        IN USE           188 12404           907         15
    P167        IN USE           189 12406           913         15
    P168        IN USE           190 12408           923         15
    P169        IN USE           191 12410           948         15
    P170        IN USE           192 12412           844         13
    P171        IN USE           193 12414           933         16
    P172        IN USE           194 12416           883         13
    P173        IN USE           195 12418           877         13
    P174        IN USE           196 12420           910         15
    P175        IN USE           197 12422           939         15
    P176        IN USE           198 12424           831         13
    P177        IN USE           199 12426           931         15
    P178        IN USE           200 12428           885         13
    P179        IN USE           201 12430           856         13
    P180        IN USE           202 12432           888         13
    P181        IN USE           203 12434           911         15
    P182        IN USE           204 12436           833         13
    P183        IN USE           205 12438           882         13
    P184        IN USE           206 12440           938         15
    P185        IN USE           207 12442           878         13
    P186        IN USE           208 12444           875         13
    P187        IN USE           209 12446           896         15
    P188        IN USE           210 12448           829         13
    P189        IN USE           211 12450           917         15
    P190        IN USE           212 12452           901         15
    P191        IN USE           213 12454           920         15
    P192        IN USE           214 12456           919         15
    P193        IN USE           215 12458           863         13
    P194        IN USE           216 12460           834         13
    P195        IN USE           217 12462           915         15
    P196        IN USE           218 12464           893         15
    P197        IN USE           219 12466           880         13
    P198        IN USE           220 12468           922         15
    P199        IN USE           221 12470           905         15
    P200        IN USE           222 12472           936         15
    P201        IN USE           223 12474           838         13
    P202        IN USE           224 12476           884         13
    P203        IN USE           225 12478           918         15
    P204        IN USE           226 12480           842         13
    P205        IN USE           227 12482           941         15
    P206        IN USE           228 12484           851         13
    P207        IN USE           229 12486           870         13
    P208        IN USE           230 12488           950         15
    P209        IN USE           231 12490           927         15
    P210        IN USE           232 12492           943         15
    P211        IN USE           233 12494           830         13
    P212        IN USE           234 12496           853         13
    P213        IN USE           235 12498           940         15
    P214        IN USE           236 12500           894         15
    P215        IN USE           237 12502           867         13
    P216        IN USE           238 12504           949         15
    P217        IN USE           239 12506           848         13
    P218        IN USE           240 12508           951         15
    P219        IN USE           241 12510           904         15
    P220        IN USE           242 12512           906         15
    P221        IN USE           243 12514           897         15
    P222        IN USE           244 12516           944         15
    P223        IN USE           245 12518           845         13
    P224        IN USE           246 12520           860         13
    P225        IN USE           247 12522           873         13
    P226        IN USE           248 12524           874         13
    P227        IN USE           249 12526           866         13
    P228        IN USE           250 12528           843         13
    P229        IN USE           251 12530           892         15
    P230        IN USE           252 12532           925         15
    P231        IN USE           253 12534           902         15
    P232        IN USE           254 12536           934         15
    P233        IN USE           255 12538           908         15
    P234        IN USE           256 12540           891         15
    P235        IN USE           257 12542           899         15
    P236        IN USE           258 12544           862         13
    P237        IN USE           259 12546           859         13
    P238        IN USE           260 12548           857         13
    P239        IN USE           261 12550           947         15
    P240        IN USE           262 12552           881         13
    P241        IN USE           263 12554           872         13
    P242        IN USE           264 12556           837         13
    P243        IN USE           265 12558           928         15
    P244        IN USE           266 12560           840         13
    P245        IN USE           267 12562           865         13
    P246        IN USE           268 12564           903         15
    P247        IN USE           269 12566           945         15
    P248        IN USE           270 12568           879         13
    P249        IN USE           271 12570           895         15
    P250        IN USE           272 12572           887         13
    P251        IN USE           273 12574           935         15
    P252        IN USE           274 12576           930         15
    P253        IN USE           275 12578           909         15
    P254        IN USE           276 12580           889         13
    P255        IN USE           277 12582           846         13
    P256        AVAILABLE        278 12584               
    P257        AVAILABLE        279 12586               
    P258        AVAILABLE        280 12588               
    P259        AVAILABLE        281 12590               
    P260        AVAILABLE        282 12592               
    P261        AVAILABLE        283 12594               
    P262        AVAILABLE        284 12596               
    P263        AVAILABLE        285 12598               
    P264        AVAILABLE        286 12600               
    P265        AVAILABLE        287 12602               
    P266        AVAILABLE        288 12604               
    P267        AVAILABLE        289 12606               
    P268        AVAILABLE        290 12608               
    P269        AVAILABLE        291 12610               
    P270        AVAILABLE        292 12612               
    P271        AVAILABLE        293 12614               
    P272        AVAILABLE        294 12616               
    P273        AVAILABLE        295 12618               
    P274        AVAILABLE        296 12620               
    P275        AVAILABLE        297 12622               
    P276        AVAILABLE        298 12624               
    P277        AVAILABLE        299 12626               
    P278        AVAILABLE        300 12628               
    P279        AVAILABLE        301 12630               
    P280        AVAILABLE        302 12632               
    P281        AVAILABLE        303 12634               
    P282        AVAILABLE        304 12636               
    P283        AVAILABLE        305 12638               
    P284        AVAILABLE        306 12640               
    P285        AVAILABLE        307 12642               
    P286        AVAILABLE        308 12645               
    P287        AVAILABLE        309 12647               
    P288        AVAILABLE        310 12649               
    P289        AVAILABLE        311 12652               
    P290        AVAILABLE        312 12657               
    P291        AVAILABLE        313 12663               
    P292        AVAILABLE        314 12665               
    P293        AVAILABLE        315 12667               
    P294        AVAILABLE        316 12669               
    P295        AVAILABLE        317 12671               
    P296        AVAILABLE        318 12673               
    P297        AVAILABLE        319 12675               
    P298        AVAILABLE        320 12677               
    P299        AVAILABLE        321 12679               
  • 38. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    SELECT decode(px.qcinst_id,
                  null,
                  username,
                  ' - ' || lower(substr(s.program, length(s.program) - 4, 4))) "Username",
           decode(px.qcinst_id, null, 'QC', '(Slave)') "QC/Slave",
           to_char(px.server_set) "Slave Set",
           to_char(s.sid) "SID",
           decode(px.qcinst_id, null, to_char(s.sid), px.qcsid) "QC SID",
           px.req_degree "Req DOP",
           px.degree "Actual DOP"
      FROM v$px_session px, v$session s
     WHERE px.sid = s.sid(+)
       and px.serial# = s.serial#
     order by 5, 1 desc;
     
    Username   QC/Slave Slave Set                                SID    QC SID Req DOP Actual
    ---------- -------- ---------------------------------------- ------ ------ ------- -------
    GILLRAY    QC                                                1091   1091           
     - p125    (Slave)  2                                        1028   1091        64      64
     - p122    (Slave)  2                                        978    1091        64      64
     - p119    (Slave)  2                                        1049   1091        64      64
     - p118    (Slave)  2                                        1005   1091        64      64
     - p116    (Slave)  2                                        965    1091        64      64
     - p115    (Slave)  2                                        1038   1091        64      64
     - p113    (Slave)  2                                        1036   1091        64      64
     - p112    (Slave)  2                                        1040   1091        64      64
     - p111    (Slave)  2                                        1071   1091        64      64
     - p110    (Slave)  2                                        995    1091        64      64
     - p109    (Slave)  2                                        990    1091        64      64
     - p107    (Slave)  2                                        1034   1091        64      64
     - p105    (Slave)  2                                        988    1091        64      64
     - p100    (Slave)  2                                        981    1091        64      64
     - p099    (Slave)  2                                        976    1091        64      64
     - p098    (Slave)  2                                        984    1091        64      64
     - p097    (Slave)  2                                        1073   1091        64      64
     - p093    (Slave)  2                                        971    1091        64      64
     - p089    (Slave)  2                                        983    1091        64      64
     - p085    (Slave)  2                                        1031   1091        64      64
     - p084    (Slave)  2                                        979    1091        64      64
     - p082    (Slave)  2                                        1001   1091        64      64
     - p081    (Slave)  2                                        1053   1091        64      64
     - p080    (Slave)  2                                        1046   1091        64      64
     - p078    (Slave)  2                                        970    1091        64      64
     - p076    (Slave)  2                                        1047   1091        64      64
     - p073    (Slave)  2                                        996    1091        64      64
     - p070    (Slave)  2                                        1011   1091        64      64
     - p069    (Slave)  2                                        1065   1091        64      64
     - p068    (Slave)  2                                        960    1091        64      64
     - p067    (Slave)  2                                        1066   1091        64      64
     - p066    (Slave)  2                                        994    1091        64      64
     - p065    (Slave)  2                                        1026   1091        64      64
     - p064    (Slave)  2                                        986    1091        64      64
     - p063    (Slave)  1                                        1027   1091        64      64
     - p061    (Slave)  1                                        1008   1091        64      64
     - p060    (Slave)  1                                        959    1091        64      64
     - p059    (Slave)  1                                        1032   1091        64      64
     - p057    (Slave)  1                                        1002   1091        64      64
     - p055    (Slave)  1                                        999    1091        64      64
     - p054    (Slave)  1                                        966    1091        64      64
     - p053    (Slave)  1                                        975    1091        64      64
     - p052    (Slave)  1                                        1039   1091        64      64
     - p051    (Slave)  1                                        1007   1091        64      64
     - p050    (Slave)  1                                        997    1091        64      64
     - p049    (Slave)  1                                        1070   1091        64      64
     - p048    (Slave)  1                                        1057   1091        64      64
     - p047    (Slave)  1                                        1064   1091        64      64
     - p046    (Slave)  1                                        968    1091        64      64
     - p045    (Slave)  1                                        1019   1091        64      64
     - p044    (Slave)  1                                        954    1091        64      64
     - p041    (Slave)  1                                        1035   1091        64      64
     - p040    (Slave)  1                                        1055   1091        64      64
     - p039    (Slave)  1                                        963    1091        64      64
     - p038    (Slave)  1                                        1060   1091        64      64
     - p037    (Slave)  1                                        989    1091        64      64
     - p036    (Slave)  1                                        1061   1091        64      64
     - p034    (Slave)  1                                        980    1091        64      64
     - p033    (Slave)  1                                        1033   1091        64      64
     - p032    (Slave)  1                                        1021   1091        64      64
     - p031    (Slave)  1                                        967    1091        64      64
     - p030    (Slave)  1                                        969    1091        64      64
     - p029    (Slave)  1                                        1075   1091        64      64
     - p028    (Slave)  1                                        1056   1091        64      64
     - p027    (Slave)  1                                        1023   1091        64      64
     - p026    (Slave)  1                                        1024   1091        64      64
     - p024    (Slave)  1                                        958    1091        64      64
     - p023    (Slave)  1                                        1022   1091        64      64
     - p022    (Slave)  1                                        1017   1091        64      64
     - p021    (Slave)  1                                        1078   1091        64      64
     - p019    (Slave)  1                                        993    1091        64      64
     - p018    (Slave)  1                                        956    1091        64      64
     - p017    (Slave)  1                                        972    1091        64      64
     - p016    (Slave)  1                                        1044   1091        64      64
     - p015    (Slave)  1                                        1054   1091        64      64
     - p014    (Slave)  1                                        1012   1091        64      64
     - p013    (Slave)  1                                        1009   1091        64      64
     - p012    (Slave)  1                                        1072   1091        64      64
     - p011    (Slave)  1                                        1016   1091        64      64
     - p010    (Slave)  1                                        964    1091        64      64
     - p009    (Slave)  1                                        1020   1091        64      64
     - p008    (Slave)  1                                        973    1091        64      64
     - p007    (Slave)  1                                        1043   1091        64      64
     - p006    (Slave)  1                                        1058   1091        64      64
     - p005    (Slave)  1                                        992    1091        64      64
     - p004    (Slave)  1                                        1076   1091        64      64
     - p003    (Slave)  1                                        1059   1091        64      64
     - p001    (Slave)  1                                        957    1091        64      64
     - p000    (Slave)  1                                        1077   1091        64      64
    
    select
      sw.SID as RCVSID,
      decode(pp.server_name,
             NULL, 'A QC',
             pp.server_name) as RCVR,
      sw.inst_id as RCVRINST,
    case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
      decode(bitand(p1, 65535),
             65535, 'QC',
             'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,
      bitand(p1, 16711680) - 65535 as SNDRINST,
      decode(bitand(p1, 65535),
             65535, ps.qcsid,
             (select
                sid
              from
                gv$px_process
              where
                server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and
                inst_id = bitand(sw.p1, 16711680) - 65535)
            ) as SNDRSID,
       decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE
    from
      gv$session_wait sw,
      gv$px_process pp,
      gv$px_session ps
    where
      sw.sid = pp.sid (+) and
      sw.inst_id = pp.inst_id (+) and
      sw.sid = ps.sid (+) and
      sw.inst_id = ps.inst_id (+) and
      p1text  = 'sleeptime/senderid' and
      bitand(p1, 268435456) = 268435456
    order by
      decode(ps.QCINST_ID,  NULL, ps.INST_ID,  ps.QCINST_ID),
      ps.QCSID,
      decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),
      ps.SERVER_SET,
      ps.INST_ID;
     
        RCVSID RCVR   RCVRINST WAIT_EVENT                     SNDR    SNDRINST    SNDRSID STATE
    ---------- ---- ---------- ------------------------------ ----- ---------- ---------- --------
           959 P000          1                                QC             1       1091 NOT WAIT
          1036 P063          1                                QC             1       1091 NOT WAIT
           963 P002          1                                QC             1       1091 NOT WAIT
          1023 P003          1                                QC             1       1091 NOT WAIT
           999 P004          1                                QC             1       1091 NOT WAIT
           966 P005          1                                QC             1       1091 NOT WAIT
          1017 P006          1                                QC             1       1091 NOT WAIT
          1055 P007          1                                QC             1       1091 NOT WAIT
           958 P008          1                                QC             1       1091 NOT WAIT
          1077 P009          1                                QC             1       1091 NOT WAIT
          1064 P010          1                                QC             1       1091 NOT WAIT
           954 P011          1                                QC             1       1091 NOT WAIT
          1008 P012          1                                QC             1       1091 NOT WAIT
          1032 P013          1                                QC             1       1091 NOT WAIT
          1058 P014          1                                QC             1       1091 NOT WAIT
          1033 P015          1                                QC             1       1091 NOT WAIT
          1019 P016          1                                QC             1       1091 NOT WAIT
          1022 P017          1                                QC             1       1091 NOT WAIT
           993 P018          1                                QC             1       1091 NOT WAIT
          1059 P019          1                                QC             1       1091 NOT WAIT
          1035 P020          1                                QC             1       1091 NOT WAIT
          1072 P021          1                                QC             1       1091 NOT WAIT
           975 P022          1                                QC             1       1091 NOT WAIT
           967 P023          1                                QC             1       1091 NOT WAIT
           989 P024          1                                QC             1       1091 NOT WAIT
           956 P025          1                                QC             1       1091 NOT WAIT
           957 P026          1                                QC             1       1091 NOT WAIT
          1012 P027          1                                QC             1       1091 NOT WAIT
           997 P028          1                                QC             1       1091 NOT WAIT
          1044 P029          1                                QC             1       1091 NOT WAIT
          1021 P030          1                                QC             1       1091 NOT WAIT
          1027 P031          1                                QC             1       1091 NOT WAIT
          1007 P032          1                                QC             1       1091 NOT WAIT
           969 P033          1                                QC             1       1091 NOT WAIT
          1061 P034          1                                QC             1       1091 NOT WAIT
          1028 P035          1                                QC             1       1091 NOT WAIT
          1002 P036          1                                QC             1       1091 NOT WAIT
          1060 P037          1                                QC             1       1091 NOT WAIT
          1011 P038          1                                QC             1       1091 NOT WAIT
          1020 P039          1                                QC             1       1091 NOT WAIT
           960 P040          1                                QC             1       1091 NOT WAIT
          1065 P041          1                                QC             1       1091 NOT WAIT
           964 P042          1                                QC             1       1091 NOT WAIT
          1057 P043          1                                QC             1       1091 NOT WAIT
          1024 P044          1                                QC             1       1091 NOT WAIT
          1076 P045          1                                QC             1       1091 NOT WAIT
          1054 P046          1                                QC             1       1091 NOT WAIT
          1075 P047          1                                QC             1       1091 NOT WAIT
          1009 P048          1                                QC             1       1091 NOT WAIT
          1078 P049          1                                QC             1       1091 NOT WAIT
           965 P050          1                                QC             1       1091 NOT WAIT
          1049 P051          1                                QC             1       1091 NOT WAIT
          1047 P052          1                                QC             1       1091 NOT WAIT
          1056 P053          1                                QC             1       1091 NOT WAIT
          1016 P054          1                                QC             1       1091 NOT WAIT
          1071 P055          1                                QC             1       1091 NOT WAIT
           984 P056          1                                QC             1       1091 NOT WAIT
          1070 P057          1                                QC             1       1091 NOT WAIT
          1005 P058          1                                QC             1       1091 NOT WAIT
           980 P059          1                                QC             1       1091 NOT WAIT
          1039 P060          1                                QC             1       1091 NOT WAIT
          1038 P061          1                                QC             1       1091 NOT WAIT
          1034 P062          1                                QC             1       1091 NOT WAIT
          1043 P001          1                                QC             1       1091 NOT WAIT
           968 P064          1                                QC             1       1091 NOT WAIT
          1066 P065          1                                QC             1       1091 NOT WAIT
          1040 P066          1                                QC             1       1091 NOT WAIT
           990 P067          1                                QC             1       1091 NOT WAIT
          1046 P068          1                                QC             1       1091 NOT WAIT
          1031 P069          1                                QC             1       1091 NOT WAIT
          1053 P070          1                                QC             1       1091 NOT WAIT
          1073 P071          1                                QC             1       1091 NOT WAIT
           988 P072          1                                QC             1       1091 NOT WAIT
          1001 P073          1                                QC             1       1091 NOT WAIT
           995 P074          1                                QC             1       1091 NOT WAIT
           992 P075          1                                QC             1       1091 NOT WAIT
          1062 P076          1                                QC             1       1091 NOT WAIT
           986 P077          1                                QC             1       1091 NOT WAIT
           981 P078          1                                QC             1       1091 NOT WAIT
           979 P079          1                                QC             1       1091 NOT WAIT
          1051 P080          1                                QC             1       1091 NOT WAIT
           983 P081          1                                QC             1       1091 NOT WAIT
          1068 P082          1                                QC             1       1091 NOT WAIT
          1041 P083          1                                QC             1       1091 NOT WAIT
          1042 P084          1                                QC             1       1091 NOT WAIT
          1074 P085          1                                QC             1       1091 NOT WAIT
          1063 P086          1                                QC             1       1091 NOT WAIT
          1026 P087          1                                QC             1       1091 NOT WAIT
           994 P088          1                                QC             1       1091 NOT WAIT
           996 P089          1                                QC             1       1091 NOT WAIT
           976 P090          1                                QC             1       1091 NOT WAIT
           973 P091          1                                QC             1       1091 NOT WAIT
          1006 P092          1                                QC             1       1091 NOT WAIT
           970 P093          1                                QC             1       1091 NOT WAIT
           971 P094          1                                QC             1       1091 NOT WAIT
          1045 P095          1                                QC             1       1091 NOT WAIT
           978 P096          1                                QC             1       1091 NOT WAIT
           972 P097          1                                QC             1       1091 NOT WAIT
          1029 P098          1                                QC             1       1091 NOT WAIT
          1081 P099          1                                QC             1       1091 NOT WAIT
           987 P100          1                                QC             1       1091 NOT WAIT
          1000 P101          1                                QC             1       1091 NOT WAIT
          1052 P102          1                                QC             1       1091 NOT WAIT
           982 P103          1                                QC             1       1091 NOT WAIT
          1030 P104          1                                QC             1       1091 NOT WAIT
          1069 P105          1                                QC             1       1091 NOT WAIT
           998 P106          1                                QC             1       1091 NOT WAIT
          1079 P107          1                                QC             1       1091 NOT WAIT
          1025 P108          1                                QC             1       1091 NOT WAIT
           961 P109          1                                QC             1       1091 NOT WAIT
          1082 P110          1                                QC             1       1091 NOT WAIT
           974 P111          1                                QC             1       1091 NOT WAIT
          1050 P112          1                                QC             1       1091 NOT WAIT
          1003 P113          1                                QC             1       1091 NOT WAIT
           977 P114          1                                QC             1       1091 NOT WAIT
           985 P115          1                                QC             1       1091 NOT WAIT
          1015 P116          1                                QC             1       1091 NOT WAIT
           955 P117          1                                QC             1       1091 NOT WAIT
          1013 P118          1                                QC             1       1091 NOT WAIT
          1048 P119          1                                QC             1       1091 NOT WAIT
          1037 P120          1                                QC             1       1091 NOT WAIT
          1004 P121          1                                QC             1       1091 NOT WAIT
          1080 P122          1                                QC             1       1091 NOT WAIT
          1014 P123          1                                QC             1       1091 NOT WAIT
           962 P124          1 PX Deq: Execution Msg          QC             1       1091 WAIT
          1010 P125          1                                QC             1       1091 NOT WAIT
           991 P126          1                                QC             1       1091 NOT WAIT
          1067 P127          1                                QC             1       1091 NOT WAIT
     
  • 39. Re: Help with Parallelization on our SPARC server...we are drowning!
    Taral Journeyer
    Currently Being Moderated
    I can't understand what are you trying to say here. But 300 parallel you might nedd 64 or more CPU box to scale with that.

    Anyways, your test doesn't tell anything about performance. If your real issue is that you are not getting DOP and downgraded to serial then you should consider lowering your parallel queries.

    Tell us the SQL which you are using not to see slaves the actual SQL which is causing problem along with plan
  • 40. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    OK, let me try again.

    Here is an example of one of the queries. I am running an explain plan as well under the PARALLEL_MAX_SERVERS=300 and then 64 (which is the current setting). Currently there is no DOP set on any objects. I will post the SQL and explain plan for that. In my next post I will do it with DOP as DEFAULT on the objects that are most commonly used and largest in size(not that many).
    select 'OP_TERM_RES' METRIC_TYPE
          ,sum(STG_CNT) as STG_CNT_LOAD_&stg_load_id
          ,sum(DLI_CNT) as DLI_CNT_LOAD_&dli_load_id
          ,sum(ADW_CNT) as ADW_CNT_LOAD_&adw_load_id
          ,sum(STG_SUM) as STG_SUM_LOAD_&stg_load_id
          ,sum(DLI_SUM) as DLI_SUM_LOAD_&dli_load_id
          ,sum(ADW_SUM) as ADW_SUM_LOAD_&adw_load_id
    from (       
    select 0        as STG_CNT
          ,count(*) as DLI_CNT
          ,0        as ADW_CNT
          ,0        as STG_SUM
          ,sum(tx.JH_ILI_RES_OPENING_METRIC_AMT) as DLI_SUM
          ,0        as ADW_SUM
      from (select JH_ILI_METRIC_TYPE_OBJ_ID
              from fdss_dli.JH_EDW_DLI_ILI_METRIC_TYPE 
              where JH_ILI_METRIC_TYPE_CODE = 'TERM_RES') m
           ,(select ILI_POL_EDW_ID from fdss_dli.EDW_DLI_ILI_POL p 
               where ILI_POL_FOREX_CURCY_CODE = 'USD') p
          ,fdss_dli.EDW_DLI_ILI_COV cv
          ,fdss_dli.EDW_DLI_ILI_CESN cs 
          ,fdss_dli.JH_EDW_DLI_ILI_RES r 
          ,fdss_dli.JH_EDW_DLI_ILI_RES_TX tx
      where m.JH_ILI_METRIC_TYPE_OBJ_ID=tx.JH_ILI_METRIC_TYPE_OBJ_ID 
        and tx.JH_ILI_RES_EDW_ID=r.JH_ILI_RES_EDW_ID 
        and r.ILI_CESN_EDW_ID=cs.ILI_CESN_EDW_ID 
        and cs.ILI_COV_EDW_ID=cv.ILI_COV_EDW_ID 
        and cv.ILI_POL_EDW_ID=p.ILI_POL_EDW_ID 
        and tx.load_id=&dli_load_id 
    union all
    select 0        as STG_CNT
          ,0        as DLI_CNT
          ,count(*) as ADW_CNT
          ,0        as STG_SUM
          ,0        as DLI_SUM
          ,SUM(rf.JH_ILI_RES_OPENING_METRIC_AMT) as ADW_SUM
      from fdss_adw_vad.JH_LKP_ILI_METRIC_TYPE m
          ,fdss_adw_vad.JH_FACT_ILI_RES rf
           ,fdss_adw_vad.JH_LKP_ILI_RES r
           ,fdss_adw_vad.LKP_ILI_CESN cs
           ,fdss_adw_vad.LKP_ILI_COV cv
           ,(select ILI_POL_ADW_ID 
               from fdss_adw_vad.LKP_ILI_POL p, fdss_adw_vad.LKP_ILI_FOREX_CURCY f
               where p.ILI_FOREX_CURCY_ADW_ID=f.ILI_FOREX_CURCY_ADW_ID
                    and f.ILI_FOREX_CURCY_CODE='USD') p
      where m.JH_ILI_METRIC_TYPE_ADW_ID=rf.JH_ILI_METRIC_TYPE_ADW_ID 
        and p.ILI_POL_ADW_ID=cv.ILI_POL_ADW_ID
         and cv.ILI_COV_ADW_ID=cs.ILI_COV_ADW_ID
         and cs.ILI_CESN_ADW_ID=r.ILI_CESN_ADW_ID
         and r.JH_ILI_RES_ADW_ID=rf.JH_ILI_RES_ADW_ID 
        and rf.load_id=&adw_load_id 
         and m.JH_ILI_METRIC_TYPE_CODE = 'TERM_RES'
    union all
    select count(*)      as STG_CNT
          ,0             as DLI_CNT
          ,0             as ADW_CNT
          ,SUM(OP_TERM_RES) as STG_SUM
          ,0             as DLI_SUM
          ,0             as ADW_SUM
      from fdss_stg.STG_VN0003_POL p
          ,fdss_stg.STG_VN0004_RES v
      where p.polno=v.polno
        and v.load_id=p.load_id
        and v.load_id=&stg_load_id 
        and p.GL_CURR='USD')
    group by 'OP_TERM_RES'               
     order by 1 ;
  • 41. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    PARALLEL_MAX_SERVERS=64, NO DOP
    SQL> @BAL_ITEM.sql;
    METRIC_TYPE STG_CNT_LOAD_47 DLI_CNT_LOAD_48 ADW_CNT_LOAD_49 STG_SUM_LOAD_47     
    ----------- --------------- --------------- --------------- ---------------     
    DLI_SUM_LOAD_48 ADW_SUM_LOAD_49                                                 
    --------------- ---------------                                                 
    OP_TERM_RES          923701          923701          923701      7665157850     
         7665157850      7665157850                                                 
    
    Elapsed: 00:02:32.99
    
    Execution Plan
    ----------------------------------------------------------                      
    Plan hash value: 473001301                                                      
                                                                                    
    --------------------------------------------------------------------------------
    -------------------------------------------------                               
                                                                                    
    | Id  | Operation                                  | Name                       
    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                               
                                                                                    
    --------------------------------------------------------------------------------
    -------------------------------------------------                               
                                                                                    
    |   0 | SELECT STATEMENT                           |                            
    |     3 |   234 |       |   109K (31)| 00:01:48 |                               
                                                                                    
    |   1 |  SORT GROUP BY NOSORT                      |                            
    |     3 |   234 |       |   109K (31)| 00:01:48 |                               
                                                                                    
    |   2 |   VIEW                                     |                            
    |     3 |   234 |       |   109K (31)| 00:01:48 |                               
                                                                                    
    |   3 |    UNION-ALL                               |                            
    |       |       |       |            |          |                               
                                                                                    
    |   4 |     SORT AGGREGATE                         |                            
    |     1 |    81 |       |            |          |                               
                                                                                    
    |*  5 |      HASH JOIN                             |                            
    | 15308 |  1210K|       | 36788  (34)| 00:00:37 |                               
                                                                                    
    |*  6 |       TABLE ACCESS FULL                    | EDW_DLI_ILI_POL            
    | 16117 |   157K|       |  5972  (20)| 00:00:06 |                               
                                                                                    
    |*  7 |       HASH JOIN                            |                            
    |   260K|    17M|    13M| 30732  (36)| 00:00:31 |                               
                                                                                    
    |   8 |        INDEX FULL SCAN                     | X_EDW_DLI_ILI_COV_2        
    |   588K|  6896K|       |    81  (45)| 00:00:01 |                               
                                                                                    
    |*  9 |        HASH JOIN                           |                            
    |   260K|    14M|    14M| 29114  (36)| 00:00:29 |                               
                                                                                    
    |* 10 |         HASH JOIN                          |                            
    |   260K|    11M|    11M| 16927  (45)| 00:00:17 |                               
                                                                                    
    |  11 |          TABLE ACCESS BY INDEX ROWID       | JH_EDW_DLI_ILI_RES_TX      
    |   260K|  4320K|       |  6856  (47)| 00:00:07 |                               
                                                                                    
    |  12 |           NESTED LOOPS                     |                            
    |   260K|  8894K|       |  6856  (47)| 00:00:07 |                               
                                                                                    
    |* 13 |            TABLE ACCESS FULL               | JH_EDW_DLI_ILI_METRIC_TYPE 
    |     1 |    18 |       |     5   (0)| 00:00:01 |                               
                                                                                    
    |  14 |            BITMAP CONVERSION TO ROWIDS     |                            
    |       |       |       |            |          |                               
                                                                                    
    |  15 |             BITMAP AND                     |                            
    |       |       |       |            |          |                               
                                                                                    
    |* 16 |              BITMAP INDEX SINGLE VALUE     | X_JH_EDW_DLI_ILI_RES_TX_1  
    |       |       |       |            |          |                               
                                                                                    
    |  17 |              BITMAP CONVERSION FROM ROWIDS |                            
    |       |       |       |            |          |                               
                                                                                    
    |* 18 |               INDEX RANGE SCAN             | X_JH_EDW_DLI_ILI_RES_TX_2  
    |  2862K|       |       |   333  (54)| 00:00:01 |                               
                                                                                    
    |  19 |          INDEX FULL SCAN                   | X_JH_EDW_DLI_ILI_RES_2     
    |  6139K|    70M|       |   845  (46)| 00:00:01 |                               
                                                                                    
    |  20 |         TABLE ACCESS FULL                  | EDW_DLI_ILI_CESN           
    |  1616K|    18M|       |  9255  (19)| 00:00:10 |                               
                                                                                    
    |  21 |     SORT AGGREGATE                         |                            
    |     1 |    91 |       |            |          |                               
                                                                                    
    |* 22 |      HASH JOIN                             |                            
    | 14458 |  1284K|       | 69715  (29)| 00:01:09 |                               
                                                                                    
    |* 23 |       TABLE ACCESS FULL                    | LKP_ILI_FOREX_CURCY        
    |     1 |     9 |       |     4   (0)| 00:00:01 |                               
                                                                                    
    |* 24 |       HASH JOIN                            |                            
    |   260K|    20M|  6176K| 69633  (29)| 00:01:09 |                               
                                                                                    
    |  25 |        TABLE ACCESS FULL                   | LKP_ILI_POL                
    |   273K|  2943K|       |  2986  (23)| 00:00:03 |                               
                                                                                    
    |* 26 |        HASH JOIN                           |                            
    |   260K|    17M|    13M| 65412  (30)| 00:01:05 |                               
                                                                                    
    |  27 |         TABLE ACCESS FULL                  | LKP_ILI_COV                
    |   588K|  6896K|       |  6295  (46)| 00:00:07 |                               
                                                                                    
    |* 28 |         HASH JOIN                          |                            
    |   260K|    14M|    14M| 57580  (28)| 00:00:57 |                               
                                                                                    
    |* 29 |          HASH JOIN                         |                            
    |   260K|    11M|    11M| 47363  (27)| 00:00:47 |                               
                                                                                    
    |  30 |           TABLE ACCESS BY INDEX ROWID      | JH_FACT_ILI_RES            
    |   260K|  4320K|       |  6974  (47)| 00:00:07 |                               
                                                                                    
    |  31 |            NESTED LOOPS                    |                            
    |   260K|  8894K|       |  6974  (47)| 00:00:07 |                               
                                                                                    
    |* 32 |             TABLE ACCESS FULL              | JH_LKP_ILI_METRIC_TYPE     
    |     1 |    18 |       |     4   (0)| 00:00:01 |                               
                                                                                    
    |  33 |             BITMAP CONVERSION TO ROWIDS    |                            
    |       |       |       |            |          |                               
                                                                                    
    |  34 |              BITMAP AND                    |                            
    |       |       |       |            |          |                               
                                                                                    
    |* 35 |               BITMAP INDEX SINGLE VALUE    | X_JH_FACT_ILI_RES_1        
    |       |       |       |            |          |                               
                                                                                    
    |  36 |               BITMAP CONVERSION FROM ROWIDS|                            
    |       |       |       |            |          |                               
                                                                                    
    |* 37 |                INDEX RANGE SCAN            | X_JH_FACT_ILI_RES_2        
    |  2862K|       |       |   333  (54)| 00:00:01 |                               
                                                                                    
    |  38 |           TABLE ACCESS FULL                | JH_LKP_ILI_RES             
    |  6139K|    70M|       | 31163  (18)| 00:00:31 |                               
                                                                                    
    |  39 |          TABLE ACCESS FULL                 | LKP_ILI_CESN               
    |  1616K|    18M|       |  7284  (26)| 00:00:08 |                               
                                                                                    
    |  40 |     SORT AGGREGATE                         |                            
    |     1 |    32 |       |            |          |                               
                                                                                    
    |* 41 |      HASH JOIN                             |                            
    |   294K|  9192K|  3328K|  2899  (43)| 00:00:03 |                               
                                                                                    
    |* 42 |       TABLE ACCESS BY INDEX ROWID          | STG_VN0003_POL             
    |   120K|  1884K|       |   453  (45)| 00:00:01 |                               
                                                                                    
    |* 43 |        INDEX RANGE SCAN                    | X_STG_VN0003_POL_1         
    |   132K|       |       |    14  (58)| 00:00:01 |                               
                                                                                    
    |  44 |       TABLE ACCESS BY INDEX ROWID          | STG_VN0004_RES             
    |   602K|  9411K|       |  1338  (45)| 00:00:02 |                               
                                                                                    
    |* 45 |        INDEX RANGE SCAN                    | X_STG_VN0004_RES_1         
    |   602K|       |       |    63  (59)| 00:00:01 |                               
                                                                                    
    --------------------------------------------------------------------------------
    -------------------------------------------------                                                                                                            
                                                                                    
    Predicate Information (identified by operation id):                             
    ---------------------------------------------------                             
                                                                                    
       5 - access("CV"."ILI_POL_EDW_ID"="ILI_POL_EDW_ID")                           
       6 - filter("ILI_POL_FOREX_CURCY_CODE"='USD')                                 
       7 - access("CS"."ILI_COV_EDW_ID"="CV"."ILI_COV_EDW_ID")                      
       9 - access("R"."ILI_CESN_EDW_ID"="CS"."ILI_CESN_EDW_ID")                     
      10 - access("TX"."JH_ILI_RES_EDW_ID"="R"."JH_ILI_RES_EDW_ID")                 
      13 - filter("JH_ILI_METRIC_TYPE_CODE"='TERM_RES')                             
      16 - access("TX"."LOAD_ID"=48)                                                
      18 - access("JH_ILI_METRIC_TYPE_OBJ_ID"="TX"."JH_ILI_METRIC_TYPE_OBJ_ID")     
      22 - access("P"."ILI_FOREX_CURCY_ADW_ID"="F"."ILI_FOREX_CURCY_ADW_ID")        
      23 - filter("F"."ILI_FOREX_CURCY_CODE"='USD')                                 
      24 - access("ILI_POL_ADW_ID"="CV"."ILI_POL_ADW_ID")                           
      26 - access("CV"."ILI_COV_ADW_ID"="CS"."ILI_COV_ADW_ID")                      
      28 - access("CS"."ILI_CESN_ADW_ID"="R"."ILI_CESN_ADW_ID")                     
      29 - access("R"."JH_ILI_RES_ADW_ID"="RF"."JH_ILI_RES_ADW_ID")                 
      32 - filter("M"."JH_ILI_METRIC_TYPE_CODE"='TERM_RES')                         
      35 - access("RF"."LOAD_ID"=49)                                                
      37 - access("M"."JH_ILI_METRIC_TYPE_ADW_ID"="RF"."JH_ILI_METRIC_TYPE_ADW_ID") 
      41 - access("P"."POLNO"="V"."POLNO" AND "V"."LOAD_ID"="P"."LOAD_ID")          
      42 - filter("P"."GL_CURR"='USD')                                              
      43 - access("P"."LOAD_ID"=47)                                                 
      45 - access("V"."LOAD_ID"=47)                                                 
                                                                                    
    Note                                                                            
    -----                                                                           
       - dynamic sampling used for this statement                                   
    
    Statistics
    ----------------------------------------------------------                      
           3456  recursive calls                                                    
              0  db block gets                                                      
         208182  consistent gets                                                    
         207795  physical reads                                                     
           1188  redo size                                                          
           1001  bytes sent via SQL*Net to client                                   
           1191  bytes received via SQL*Net from client                             
              2  SQL*Net roundtrips to/from client                                  
             30  sorts (memory)                                                     
              0  sorts (disk)                                                       
              1  rows processed                                                     
    PARALLEL_MAX_SERVERS=300, NO DOP
    SQL> @BAL_ITEM.sql;
    METRIC_TYPE STG_CNT_LOAD_47 DLI_CNT_LOAD_48 ADW_CNT_LOAD_49 STG_SUM_LOAD_47
    ----------- --------------- --------------- --------------- ---------------
    DLI_SUM_LOAD_48 ADW_SUM_LOAD_49
    --------------- ---------------
    OP_TERM_RES          923701          923701          923701      7665157850
         7665157850      7665157850
    
    Elapsed: 00:02:22.26
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 473001301
    
    --------------------------------------------------------------------------------
    -------------------------------------------------
    
    | Id  | Operation                                  | Name
    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    
    --------------------------------------------------------------------------------
    -------------------------------------------------
    
    |   0 | SELECT STATEMENT                           |
    |     3 |   234 |       |   109K (31)| 00:01:48 |
    
    |   1 |  SORT GROUP BY NOSORT                      |
    |     3 |   234 |       |   109K (31)| 00:01:48 |
    
    |   2 |   VIEW                                     |
    |     3 |   234 |       |   109K (31)| 00:01:48 |
    
    |   3 |    UNION-ALL                               |
    |       |       |       |            |          |
    
    |   4 |     SORT AGGREGATE                         |
    |     1 |    81 |       |            |          |
    
    |*  5 |      HASH JOIN                             |
    | 15308 |  1210K|       | 36788  (34)| 00:00:37 |
    
    |*  6 |       TABLE ACCESS FULL                    | EDW_DLI_ILI_POL
    | 16117 |   157K|       |  5972  (20)| 00:00:06 |
    
    |*  7 |       HASH JOIN                            |
    |   260K|    17M|    13M| 30732  (36)| 00:00:31 |
    
    |   8 |        INDEX FULL SCAN                     | X_EDW_DLI_ILI_COV_2
    |   588K|  6896K|       |    81  (45)| 00:00:01 |
    
    |*  9 |        HASH JOIN                           |
    |   260K|    14M|    14M| 29114  (36)| 00:00:29 |
    
    |* 10 |         HASH JOIN                          |
    |   260K|    11M|    11M| 16927  (45)| 00:00:17 |
    
    |  11 |          TABLE ACCESS BY INDEX ROWID       | JH_EDW_DLI_ILI_RES_TX
    |   260K|  4320K|       |  6856  (47)| 00:00:07 |
    
    |  12 |           NESTED LOOPS                     |
    |   260K|  8894K|       |  6856  (47)| 00:00:07 |
    
    |* 13 |            TABLE ACCESS FULL               | JH_EDW_DLI_ILI_METRIC_TYPE
    |     1 |    18 |       |     5   (0)| 00:00:01 |
    
    |  14 |            BITMAP CONVERSION TO ROWIDS     |
    |       |       |       |            |          |
    
    |  15 |             BITMAP AND                     |
    |       |       |       |            |          |
    
    |* 16 |              BITMAP INDEX SINGLE VALUE     | X_JH_EDW_DLI_ILI_RES_TX_1
    |       |       |       |            |          |
    
    |  17 |              BITMAP CONVERSION FROM ROWIDS |
    |       |       |       |            |          |
    
    |* 18 |               INDEX RANGE SCAN             | X_JH_EDW_DLI_ILI_RES_TX_2
    |  2862K|       |       |   333  (54)| 00:00:01 |
    
    |  19 |          INDEX FULL SCAN                   | X_JH_EDW_DLI_ILI_RES_2
    |  6139K|    70M|       |   845  (46)| 00:00:01 |
    
    |  20 |         TABLE ACCESS FULL                  | EDW_DLI_ILI_CESN
    |  1616K|    18M|       |  9255  (19)| 00:00:10 |
    
    |  21 |     SORT AGGREGATE                         |
    |     1 |    91 |       |            |          |
    
    |* 22 |      HASH JOIN                             |
    | 14458 |  1284K|       | 69715  (29)| 00:01:09 |
    
    |* 23 |       TABLE ACCESS FULL                    | LKP_ILI_FOREX_CURCY
    |     1 |     9 |       |     4   (0)| 00:00:01 |
    
    |* 24 |       HASH JOIN                            |
    |   260K|    20M|  6176K| 69633  (29)| 00:01:09 |
    
    |  25 |        TABLE ACCESS FULL                   | LKP_ILI_POL
    |   273K|  2943K|       |  2986  (23)| 00:00:03 |
    
    |* 26 |        HASH JOIN                           |
    |   260K|    17M|    13M| 65412  (30)| 00:01:05 |
    
    |  27 |         TABLE ACCESS FULL                  | LKP_ILI_COV
    |   588K|  6896K|       |  6295  (46)| 00:00:07 |
    
    |* 28 |         HASH JOIN                          |
    |   260K|    14M|    14M| 57580  (28)| 00:00:57 |
    
    |* 29 |          HASH JOIN                         |
    |   260K|    11M|    11M| 47363  (27)| 00:00:47 |
    
    |  30 |           TABLE ACCESS BY INDEX ROWID      | JH_FACT_ILI_RES
    |   260K|  4320K|       |  6974  (47)| 00:00:07 |
    
    |  31 |            NESTED LOOPS                    |
    |   260K|  8894K|       |  6974  (47)| 00:00:07 |
    
    |* 32 |             TABLE ACCESS FULL              | JH_LKP_ILI_METRIC_TYPE
    |     1 |    18 |       |     4   (0)| 00:00:01 |
    
    |  33 |             BITMAP CONVERSION TO ROWIDS    |
    |       |       |       |            |          |
    
    |  34 |              BITMAP AND                    |
    |       |       |       |            |          |
    
    |* 35 |               BITMAP INDEX SINGLE VALUE    | X_JH_FACT_ILI_RES_1
    |       |       |       |            |          |
    
    |  36 |               BITMAP CONVERSION FROM ROWIDS|
    |       |       |       |            |          |
    
    |* 37 |                INDEX RANGE SCAN            | X_JH_FACT_ILI_RES_2
    |  2862K|       |       |   333  (54)| 00:00:01 |
    
    |  38 |           TABLE ACCESS FULL                | JH_LKP_ILI_RES
    |  6139K|    70M|       | 31163  (18)| 00:00:31 |
    
    |  39 |          TABLE ACCESS FULL                 | LKP_ILI_CESN
    |  1616K|    18M|       |  7284  (26)| 00:00:08 |
    
    |  40 |     SORT AGGREGATE                         |
    |     1 |    32 |       |            |          |
    
    |* 41 |      HASH JOIN                             |
    |   294K|  9192K|  3328K|  2899  (43)| 00:00:03 |
    
    |* 42 |       TABLE ACCESS BY INDEX ROWID          | STG_VN0003_POL
    |   120K|  1884K|       |   453  (45)| 00:00:01 |
    
    |* 43 |        INDEX RANGE SCAN                    | X_STG_VN0003_POL_1
    |   132K|       |       |    14  (58)| 00:00:01 |
    
    |  44 |       TABLE ACCESS BY INDEX ROWID          | STG_VN0004_RES
    |   602K|  9411K|       |  1338  (45)| 00:00:02 |
    
    |* 45 |        INDEX RANGE SCAN                    | X_STG_VN0004_RES_1
    |   602K|       |       |    63  (59)| 00:00:01 |
    
    --------------------------------------------------------------------------------
    -------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("CV"."ILI_POL_EDW_ID"="ILI_POL_EDW_ID")
       6 - filter("ILI_POL_FOREX_CURCY_CODE"='USD')
       7 - access("CS"."ILI_COV_EDW_ID"="CV"."ILI_COV_EDW_ID")
       9 - access("R"."ILI_CESN_EDW_ID"="CS"."ILI_CESN_EDW_ID")
      10 - access("TX"."JH_ILI_RES_EDW_ID"="R"."JH_ILI_RES_EDW_ID")
      13 - filter("JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
      16 - access("TX"."LOAD_ID"=48)
      18 - access("JH_ILI_METRIC_TYPE_OBJ_ID"="TX"."JH_ILI_METRIC_TYPE_OBJ_ID")
      22 - access("P"."ILI_FOREX_CURCY_ADW_ID"="F"."ILI_FOREX_CURCY_ADW_ID")
      23 - filter("F"."ILI_FOREX_CURCY_CODE"='USD')
      24 - access("ILI_POL_ADW_ID"="CV"."ILI_POL_ADW_ID")
      26 - access("CV"."ILI_COV_ADW_ID"="CS"."ILI_COV_ADW_ID")
      28 - access("CS"."ILI_CESN_ADW_ID"="R"."ILI_CESN_ADW_ID")
      29 - access("R"."JH_ILI_RES_ADW_ID"="RF"."JH_ILI_RES_ADW_ID")
      32 - filter("M"."JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
      35 - access("RF"."LOAD_ID"=49)
      37 - access("M"."JH_ILI_METRIC_TYPE_ADW_ID"="RF"."JH_ILI_METRIC_TYPE_ADW_ID")
      41 - access("P"."POLNO"="V"."POLNO" AND "V"."LOAD_ID"="P"."LOAD_ID")
      42 - filter("P"."GL_CURR"='USD')
      43 - access("P"."LOAD_ID"=47)
      45 - access("V"."LOAD_ID"=47)
    
    Note
    -----
       - dynamic sampling used for this statement
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              0  consistent gets
              0  physical reads
              0  redo size
              0  bytes sent via SQL*Net to client
              0  bytes received via SQL*Net from client
              0  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
  • 42. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    PARALLEL_MAX_SERVERS=64, DOP DEFAULT
    SQL> @BAL_ITEM.sql;
    METRIC_TYPE STG_CNT_LOAD_47 DLI_CNT_LOAD_48 ADW_CNT_LOAD_49 STG_SUM_LOAD_47
    ----------- --------------- --------------- --------------- ---------------
    DLI_SUM_LOAD_48 ADW_SUM_LOAD_49
    --------------- ---------------
    OP_TERM_RES          923701          923701          923701      7665157850
         7665157850      7665157850
    
    Elapsed: 00:06:03.24
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1512977653
    
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    
    | Id  | Operation                                    | Name
      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT                             |
      |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
    
    |   1 |  SORT GROUP BY NOSORT                        |
      |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
    
    |   2 |   VIEW                                       |
      |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
    
    |   3 |    UNION-ALL                                 |
      |       |       |       |            |          |        |      |            |
    
    |   4 |     SORT AGGREGATE                           |
      |     1 |    81 |       |            |          |        |      |            |
    
    |   5 |      PX COORDINATOR                          |
      |       |       |       |            |          |        |      |            |
    
    |   6 |       PX SEND QC (RANDOM)                    | :TQ10004
      |     1 |    81 |       |            |          |  Q1,04 | P->S | QC (RAND)  |
    
    |   7 |        SORT AGGREGATE                        |
      |     1 |    81 |       |            |          |  Q1,04 | PCWP |            |
    
    |   8 |         NESTED LOOPS                         |
      | 15308 |  1210K|       |  1893   (8)| 00:00:02 |  Q1,04 | PCWP |            |
    
    |*  9 |          HASH JOIN                           |
      |   260K|    17M|       |  1418   (9)| 00:00:02 |  Q1,04 | PCWP |            |
    
    |  10 |           PX RECEIVE                         |
      |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,04 | PCWP |            |
    
    |  11 |            PX SEND HASH                      | :TQ10002
      |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | P->P | HASH       |
    
    |  12 |             PX BLOCK ITERATOR                |
      |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | PCWC |            |
    
    |  13 |              INDEX FAST FULL SCAN            | X_EDW_DLI_ILI_COV_2
      |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | PCWP |            |
    
    |  14 |           PX RECEIVE                         |
      |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,04 | PCWP |            |
    
    |  15 |            PX SEND HASH                      | :TQ10003
      |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,03 | P->P | HASH       |
    
    |  16 |             BUFFER SORT                      |
      |     3 |   234 |       |  7399  (21)| 00:00:08 |  Q1,03 | PCWP |            |
    
    |  17 |              NESTED LOOPS                    |
      |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,03 | PCWP |            |
    
    |* 18 |               HASH JOIN                      |
      |   260K|    11M|       |   877  (11)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |  19 |                BUFFER SORT                   |
      |       |       |       |            |          |  Q1,03 | PCWC |            |
    
    |  20 |                 PX RECEIVE                   |
      |   260K|  4320K|       |   214   (8)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |  21 |                  PX SEND HASH                | :TQ10000
      |   260K|  4320K|       |   214   (8)| 00:00:01 |        | S->P | HASH       |
    
    |* 22 |                   TABLE ACCESS BY INDEX ROWID| JH_EDW_DLI_ILI_RES_TX
      |   260K|  4320K|       |   214   (8)| 00:00:01 |        |      |            |
    
    |  23 |                    NESTED LOOPS              |
      |   260K|  8894K|       |   220   (8)| 00:00:01 |        |      |            |
    
    |* 24 |                     TABLE ACCESS FULL        | JH_EDW_DLI_ILI_METRIC_TYP
    E |     1 |    18 |       |     5   (0)| 00:00:01 |        |      |            |
    
    |* 25 |                     INDEX RANGE SCAN         | X_JH_EDW_DLI_ILI_RES_TX_2
      |  2862K|       |       |     6  (50)| 00:00:01 |        |      |            |
    
    |  26 |                PX RECEIVE                    |
      |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |  27 |                 PX SEND HASH                 | :TQ10001
      |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | P->P | HASH       |
    
    |  28 |                  PX BLOCK ITERATOR           |
      |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | PCWC |            |
    
    |  29 |                   INDEX FAST FULL SCAN       | X_JH_EDW_DLI_ILI_RES_2
      |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | PCWP |            |
    
    |  30 |               TABLE ACCESS BY INDEX ROWID    | EDW_DLI_ILI_CESN
      |     1 |    12 |       |     0   (0)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |* 31 |                INDEX UNIQUE SCAN             | XPK_JH_EDW_DLI_ILI_CESN
      |     1 |       |       |     0   (0)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |* 32 |          TABLE ACCESS BY INDEX ROWID         | EDW_DLI_ILI_POL
      |     1 |    10 |       |     0   (0)| 00:00:01 |  Q1,04 | PCWP |            |
    
    |* 33 |           INDEX UNIQUE SCAN                  | XPK_EDW_DLI_ILI_POL
      |     1 |       |       |     0   (0)| 00:00:01 |  Q1,04 | PCWP |            |
    
    |  34 |     SORT AGGREGATE                           |
      |     1 |    91 |       |            |          |        |      |            |
    
    |* 35 |      HASH JOIN                               |
      | 14458 |  1284K|       |  2607   (6)| 00:00:03 |        |      |            |
    
    |* 36 |       TABLE ACCESS FULL                      | LKP_ILI_FOREX_CURCY
      |     1 |     9 |       |     4   (0)| 00:00:01 |        |      |            |
    
    |  37 |       NESTED LOOPS                           |
      |   260K|    20M|       |  2601   (6)| 00:00:03 |        |      |            |
    
    |  38 |        NESTED LOOPS                          |
      |   260K|    17M|       |  2126   (6)| 00:00:03 |        |      |            |
    
    |  39 |         NESTED LOOPS                         |
      |   260K|    14M|       |  1650   (6)| 00:00:02 |        |      |            |
    
    |  40 |          NESTED LOOPS                        |
      |   260K|    11M|       |  1175   (6)| 00:00:02 |        |      |            |
    
    |  41 |           NESTED LOOPS                       |
      |   260K|  8894K|       |   229   (8)| 00:00:01 |        |      |            |
    
    |* 42 |            TABLE ACCESS FULL                 | JH_LKP_ILI_METRIC_TYPE
      |     1 |    18 |       |     4   (0)| 00:00:01 |        |      |            |
    
    |* 43 |            TABLE ACCESS BY INDEX ROWID       | JH_FACT_ILI_RES
      |   260K|  4320K|       |   224   (8)| 00:00:01 |        |      |            |
    
    |* 44 |             INDEX RANGE SCAN                 | X_JH_FACT_ILI_RES_2
      |  2862K|       |       |     6  (50)| 00:00:01 |        |      |            |
    
    |  45 |           TABLE ACCESS BY INDEX ROWID        | JH_LKP_ILI_RES
      |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
    
    |* 46 |            INDEX UNIQUE SCAN                 | XPK_JH_LKP_ILI_RES
      |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
    
    |  47 |          TABLE ACCESS BY INDEX ROWID         | LKP_ILI_CESN
      |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
    
    |* 48 |           INDEX UNIQUE SCAN                  | XPK_LKP_ILI_CESN
      |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
    
    |  49 |         TABLE ACCESS BY INDEX ROWID          | LKP_ILI_COV
      |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
    
    |* 50 |          INDEX UNIQUE SCAN                   | XPK_LKP_ILI_COV
      |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
    
    |  51 |        TABLE ACCESS BY INDEX ROWID           | LKP_ILI_POL
      |     1 |    11 |       |     0   (0)| 00:00:01 |        |      |            |
    
    |* 52 |         INDEX UNIQUE SCAN                    | XPK_LKP_ILI_POL
      |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
    
    |  53 |     SORT AGGREGATE                           |
      |     1 |    32 |       |            |          |        |      |            |
    
    |* 54 |      HASH JOIN                               |
      |   294K|  9192K|  3328K|  2899  (43)| 00:00:03 |        |      |            |
    
    |* 55 |       TABLE ACCESS BY INDEX ROWID            | STG_VN0003_POL
      |   120K|  1884K|       |   453  (45)| 00:00:01 |        |      |            |
    
    |* 56 |        INDEX RANGE SCAN                      | X_STG_VN0003_POL_1
      |   132K|       |       |    14  (58)| 00:00:01 |        |      |            |
    
    |  57 |       TABLE ACCESS BY INDEX ROWID            | STG_VN0004_RES
      |   602K|  9411K|       |  1338  (45)| 00:00:02 |        |      |            |
    
    |* 58 |        INDEX RANGE SCAN                      | X_STG_VN0004_RES_1
      |   602K|       |       |    63  (59)| 00:00:01 |        |      |            |
    
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       9 - access("CS"."ILI_COV_EDW_ID"="CV"."ILI_COV_EDW_ID")
      18 - access("TX"."JH_ILI_RES_EDW_ID"="R"."JH_ILI_RES_EDW_ID")
      22 - filter("TX"."LOAD_ID"=48)
      24 - filter("JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
      25 - access("JH_ILI_METRIC_TYPE_OBJ_ID"="TX"."JH_ILI_METRIC_TYPE_OBJ_ID")
      31 - access("R"."ILI_CESN_EDW_ID"="CS"."ILI_CESN_EDW_ID")
      32 - filter("ILI_POL_FOREX_CURCY_CODE"='USD')
      33 - access("CV"."ILI_POL_EDW_ID"="ILI_POL_EDW_ID")
      35 - access("P"."ILI_FOREX_CURCY_ADW_ID"="F"."ILI_FOREX_CURCY_ADW_ID")
      36 - filter("F"."ILI_FOREX_CURCY_CODE"='USD')
      42 - filter("M"."JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
      43 - filter("RF"."LOAD_ID"=49)
      44 - access("M"."JH_ILI_METRIC_TYPE_ADW_ID"="RF"."JH_ILI_METRIC_TYPE_ADW_ID")
      46 - access("R"."JH_ILI_RES_ADW_ID"="RF"."JH_ILI_RES_ADW_ID")
      48 - access("CS"."ILI_CESN_ADW_ID"="R"."ILI_CESN_ADW_ID")
      50 - access("CV"."ILI_COV_ADW_ID"="CS"."ILI_COV_ADW_ID")
      52 - access("ILI_POL_ADW_ID"="CV"."ILI_POL_ADW_ID")
      54 - access("P"."POLNO"="V"."POLNO" AND "V"."LOAD_ID"="P"."LOAD_ID")
      55 - filter("P"."GL_CURR"='USD')
      56 - access("P"."LOAD_ID"=47)
      58 - access("V"."LOAD_ID"=47)
    
    Note
    -----
       - dynamic sampling used for this statement
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              0  consistent gets
              0  physical reads
              0  redo size
              0  bytes sent via SQL*Net to client
              0  bytes received via SQL*Net from client
              0  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    PARALLEL_MAX_SERVERS=300, DOP DEFAULT
    SQL> @BAL_ITEM.sql
    METRIC_TYPE STG_CNT_LOAD_47 DLI_CNT_LOAD_48 ADW_CNT_LOAD_49 STG_SUM_LOAD_47
    ----------- --------------- --------------- --------------- ---------------
    DLI_SUM_LOAD_48 ADW_SUM_LOAD_49
    --------------- ---------------
    OP_TERM_RES          923701          923701          923701      7665157850
         7665157850      7665157850
    
    Elapsed: 00:06:04.76
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1512977653
    
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    
    | Id  | Operation                                    | Name
      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT                             |
      |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
    
    |   1 |  SORT GROUP BY NOSORT                        |
      |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
    
    |   2 |   VIEW                                       |
      |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
    
    |   3 |    UNION-ALL                                 |
      |       |       |       |            |          |        |      |            |
    
    |   4 |     SORT AGGREGATE                           |
      |     1 |    81 |       |            |          |        |      |            |
    
    |   5 |      PX COORDINATOR                          |
      |       |       |       |            |          |        |      |            |
    
    |   6 |       PX SEND QC (RANDOM)                    | :TQ10004
      |     1 |    81 |       |            |          |  Q1,04 | P->S | QC (RAND)  |
    
    |   7 |        SORT AGGREGATE                        |
      |     1 |    81 |       |            |          |  Q1,04 | PCWP |            |
    
    |   8 |         NESTED LOOPS                         |
      | 15308 |  1210K|       |  1893   (8)| 00:00:02 |  Q1,04 | PCWP |            |
    
    |*  9 |          HASH JOIN                           |
      |   260K|    17M|       |  1418   (9)| 00:00:02 |  Q1,04 | PCWP |            |
    
    |  10 |           PX RECEIVE                         |
      |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,04 | PCWP |            |
    
    |  11 |            PX SEND HASH                      | :TQ10002
      |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | P->P | HASH       |
    
    |  12 |             PX BLOCK ITERATOR                |
      |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | PCWC |            |
    
    |  13 |              INDEX FAST FULL SCAN            | X_EDW_DLI_ILI_COV_2
      |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | PCWP |            |
    
    |  14 |           PX RECEIVE                         |
      |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,04 | PCWP |            |
    
    |  15 |            PX SEND HASH                      | :TQ10003
      |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,03 | P->P | HASH       |
    
    |  16 |             BUFFER SORT                      |
      |     3 |   234 |       |  7399  (21)| 00:00:08 |  Q1,03 | PCWP |            |
    
    |  17 |              NESTED LOOPS                    |
      |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,03 | PCWP |            |
    
    |* 18 |               HASH JOIN                      |
      |   260K|    11M|       |   877  (11)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |  19 |                BUFFER SORT                   |
      |       |       |       |            |          |  Q1,03 | PCWC |            |
    
    |  20 |                 PX RECEIVE                   |
      |   260K|  4320K|       |   214   (8)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |  21 |                  PX SEND HASH                | :TQ10000
      |   260K|  4320K|       |   214   (8)| 00:00:01 |        | S->P | HASH       |
    
    |* 22 |                   TABLE ACCESS BY INDEX ROWID| JH_EDW_DLI_ILI_RES_TX
      |   260K|  4320K|       |   214   (8)| 00:00:01 |        |      |            |
    
    |  23 |                    NESTED LOOPS              |
      |   260K|  8894K|       |   220   (8)| 00:00:01 |        |      |            |
    
    |* 24 |                     TABLE ACCESS FULL        | JH_EDW_DLI_ILI_METRIC_TYP
    E |     1 |    18 |       |     5   (0)| 00:00:01 |        |      |            |
    
    |* 25 |                     INDEX RANGE SCAN         | X_JH_EDW_DLI_ILI_RES_TX_2
      |  2862K|       |       |     6  (50)| 00:00:01 |        |      |            |
    
    |  26 |                PX RECEIVE                    |
      |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |  27 |                 PX SEND HASH                 | :TQ10001
      |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | P->P | HASH       |
    
    |  28 |                  PX BLOCK ITERATOR           |
      |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | PCWC |            |
    
    |  29 |                   INDEX FAST FULL SCAN       | X_JH_EDW_DLI_ILI_RES_2
      |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | PCWP |            |
    
    |  30 |               TABLE ACCESS BY INDEX ROWID    | EDW_DLI_ILI_CESN
      |     1 |    12 |       |     0   (0)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |* 31 |                INDEX UNIQUE SCAN             | XPK_JH_EDW_DLI_ILI_CESN
      |     1 |       |       |     0   (0)| 00:00:01 |  Q1,03 | PCWP |            |
    
    |* 32 |          TABLE ACCESS BY INDEX ROWID         | EDW_DLI_ILI_POL
      |     1 |    10 |       |     0   (0)| 00:00:01 |  Q1,04 | PCWP |            |
    
    |* 33 |           INDEX UNIQUE SCAN                  | XPK_EDW_DLI_ILI_POL
      |     1 |       |       |     0   (0)| 00:00:01 |  Q1,04 | PCWP |            |
    
    |  34 |     SORT AGGREGATE                           |
      |     1 |    91 |       |            |          |        |      |            |
    
    |* 35 |      HASH JOIN                               |
      | 14458 |  1284K|       |  2607   (6)| 00:00:03 |        |      |            |
    
    |* 36 |       TABLE ACCESS FULL                      | LKP_ILI_FOREX_CURCY
      |     1 |     9 |       |     4   (0)| 00:00:01 |        |      |            |
    
    |  37 |       NESTED LOOPS                           |
      |   260K|    20M|       |  2601   (6)| 00:00:03 |        |      |            |
    
    |  38 |        NESTED LOOPS                          |
      |   260K|    17M|       |  2126   (6)| 00:00:03 |        |      |            |
    
    |  39 |         NESTED LOOPS                         |
      |   260K|    14M|       |  1650   (6)| 00:00:02 |        |      |            |
    
    |  40 |          NESTED LOOPS                        |
      |   260K|    11M|       |  1175   (6)| 00:00:02 |        |      |            |
    
    |  41 |           NESTED LOOPS                       |
      |   260K|  8894K|       |   229   (8)| 00:00:01 |        |      |            |
    
    |* 42 |            TABLE ACCESS FULL                 | JH_LKP_ILI_METRIC_TYPE
      |     1 |    18 |       |     4   (0)| 00:00:01 |        |      |            |
    
    |* 43 |            TABLE ACCESS BY INDEX ROWID       | JH_FACT_ILI_RES
      |   260K|  4320K|       |   224   (8)| 00:00:01 |        |      |            |
    
    |* 44 |             INDEX RANGE SCAN                 | X_JH_FACT_ILI_RES_2
      |  2862K|       |       |     6  (50)| 00:00:01 |        |      |            |
    
    |  45 |           TABLE ACCESS BY INDEX ROWID        | JH_LKP_ILI_RES
      |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
    
    |* 46 |            INDEX UNIQUE SCAN                 | XPK_JH_LKP_ILI_RES
      |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
    
    |  47 |          TABLE ACCESS BY INDEX ROWID         | LKP_ILI_CESN
      |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
    
    |* 48 |           INDEX UNIQUE SCAN                  | XPK_LKP_ILI_CESN
      |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
    
    |  49 |         TABLE ACCESS BY INDEX ROWID          | LKP_ILI_COV
      |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
    
    |* 50 |          INDEX UNIQUE SCAN                   | XPK_LKP_ILI_COV
      |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
    
    |  51 |        TABLE ACCESS BY INDEX ROWID           | LKP_ILI_POL
      |     1 |    11 |       |     0   (0)| 00:00:01 |        |      |            |
    
    |* 52 |         INDEX UNIQUE SCAN                    | XPK_LKP_ILI_POL
      |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
    
    |  53 |     SORT AGGREGATE                           |
      |     1 |    32 |       |            |          |        |      |            |
    
    |* 54 |      HASH JOIN                               |
      |   294K|  9192K|  3328K|  2899  (43)| 00:00:03 |        |      |            |
    
    |* 55 |       TABLE ACCESS BY INDEX ROWID            | STG_VN0003_POL
      |   120K|  1884K|       |   453  (45)| 00:00:01 |        |      |            |
    
    |* 56 |        INDEX RANGE SCAN                      | X_STG_VN0003_POL_1
      |   132K|       |       |    14  (58)| 00:00:01 |        |      |            |
    
    |  57 |       TABLE ACCESS BY INDEX ROWID            | STG_VN0004_RES
      |   602K|  9411K|       |  1338  (45)| 00:00:02 |        |      |            |
    
    |* 58 |        INDEX RANGE SCAN                      | X_STG_VN0004_RES_1
      |   602K|       |       |    63  (59)| 00:00:01 |        |      |            |
    
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       9 - access("CS"."ILI_COV_EDW_ID"="CV"."ILI_COV_EDW_ID")
      18 - access("TX"."JH_ILI_RES_EDW_ID"="R"."JH_ILI_RES_EDW_ID")
      22 - filter("TX"."LOAD_ID"=48)
      24 - filter("JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
      25 - access("JH_ILI_METRIC_TYPE_OBJ_ID"="TX"."JH_ILI_METRIC_TYPE_OBJ_ID")
      31 - access("R"."ILI_CESN_EDW_ID"="CS"."ILI_CESN_EDW_ID")
      32 - filter("ILI_POL_FOREX_CURCY_CODE"='USD')
      33 - access("CV"."ILI_POL_EDW_ID"="ILI_POL_EDW_ID")
      35 - access("P"."ILI_FOREX_CURCY_ADW_ID"="F"."ILI_FOREX_CURCY_ADW_ID")
      36 - filter("F"."ILI_FOREX_CURCY_CODE"='USD')
      42 - filter("M"."JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
      43 - filter("RF"."LOAD_ID"=49)
      44 - access("M"."JH_ILI_METRIC_TYPE_ADW_ID"="RF"."JH_ILI_METRIC_TYPE_ADW_ID")
      46 - access("R"."JH_ILI_RES_ADW_ID"="RF"."JH_ILI_RES_ADW_ID")
      48 - access("CS"."ILI_CESN_ADW_ID"="R"."ILI_CESN_ADW_ID")
      50 - access("CV"."ILI_COV_ADW_ID"="CS"."ILI_COV_ADW_ID")
      52 - access("ILI_POL_ADW_ID"="CV"."ILI_POL_ADW_ID")
      54 - access("P"."POLNO"="V"."POLNO" AND "V"."LOAD_ID"="P"."LOAD_ID")
      55 - filter("P"."GL_CURR"='USD')
      56 - access("P"."LOAD_ID"=47)
      58 - access("V"."LOAD_ID"=47)
    
    Note
    -----
       - dynamic sampling used for this statement
    
    Statistics
    ----------------------------------------------------------
            748  recursive calls
              4  db block gets
       12541612  consistent gets
         533929  physical reads
           1048  redo size
           1001  bytes sent via SQL*Net to client
           1191  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
            130  sorts (memory)
              0  sorts (disk)
              1  rows processed
  • 43. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    Also this might be worth while to mention that I am doing all these tests on different server. The server is the same as the original one. The DB however is just larger in size, all other settings are the same:
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
    _lgwr_io_slaves                      integer     0
    _log_parallelism                     integer     8
    _log_parallelism_max                 integer     12
    _optimizer_cost_based_transformation string      off
    _optimizer_cost_model                string      CPU
    _realfree_heap_pagesize_hint         big integer 4M
    active_instance_count                integer     
    aq_tm_processes                      integer     1
    archive_lag_target                   integer     0
    asm_diskgroups                       string      
    asm_diskstring                       string      
    asm_power_limit                      integer     1
    audit_file_dest                      string      /JH0001/app/oracle/admin/VADP/adump
    audit_sys_operations                 boolean     FALSE
    audit_syslog_level                   string      
    audit_trail                          string      NONE
    background_core_dump                 string      partial
    background_dump_dest                 string      /JH0001/app/oracle/admin/VADP/bdump
    backup_tape_io_slaves                boolean     FALSE
    bitmap_merge_area_size               integer     0
    blank_trimming                       boolean     FALSE
    buffer_pool_keep                     string      
    buffer_pool_recycle                  string      
    circuits                             integer     
    cluster_database                     boolean     FALSE
    cluster_database_instances           integer     1
    cluster_interconnects                string      
    commit_point_strength                integer     1
    commit_write                         string      
    compatible                           string      10.2.0.1.0
    control_file_record_keep_time        integer     7
    control_files                        string      /JH4056/db/VADP/controlfile/control01.ctl, /JH4056/db/VADP/oradata/control02.ctl, /JH4056/db/VADP/redos/control03.ctl
    core_dump_dest                       string      /JH0001/app/oracle/admin/VADP/cdump
    cpu_count                            integer     32
    create_bitmap_area_size              integer     0
    create_stored_outlines               string      
    cursor_sharing                       string      EXACT
    cursor_space_for_time                boolean     FALSE
    db_16k_cache_size                    big integer 0
    db_2k_cache_size                     big integer 0
    db_32k_cache_size                    big integer 0
    db_4k_cache_size                     big integer 0
    db_8k_cache_size                     big integer 0
    db_block_buffers                     integer     0
    db_block_checking                    string      FALSE
    db_block_checksum                    string      false
    db_block_size                        integer     32768
    db_cache_advice                      string      ON
    db_cache_size                        big integer 0
    db_create_file_dest                  string      
    db_create_online_log_dest_1          string      
    db_create_online_log_dest_2          string      
    db_create_online_log_dest_3          string      
    db_create_online_log_dest_4          string      
    db_create_online_log_dest_5          string      
    db_domain                            string      
    db_file_multiblock_read_count        integer     32
    db_file_name_convert                 string      
    db_files                             integer     200
    db_flashback_retention_target        integer     1440
    db_keep_cache_size                   big integer 0
    db_name                              string      VADP
    db_recovery_file_dest                string      /JH4056/db/VADP/flash_recovery_area
    db_recovery_file_dest_size           big integer 2G
    db_recycle_cache_size                big integer 0
    db_unique_name                       string      VADP
    db_writer_processes                  integer     4
    dbwr_io_slaves                       integer     0
    ddl_wait_for_locks                   boolean     FALSE
    dg_broker_config_file1               string      /JH0001/app/oracle/product/10.2.0.4/dbs/dr1VADP.dat
    dg_broker_config_file2               string      /JH0001/app/oracle/product/10.2.0.4/dbs/dr2VADP.dat
    dg_broker_start                      boolean     FALSE
    disk_asynch_io                       boolean     FALSE
    dispatchers                          string      (PROTOCOL=TCP) (SERVICE=VADPXDB)
    distributed_lock_timeout             integer     60
    dml_locks                            integer     4860
    drs_start                            boolean     FALSE
    event                                string      
    fal_client                           string      
    fal_server                           string      
    fast_start_io_target                 integer     0
    fast_start_mttr_target               integer     300
    fast_start_parallel_rollback         string      HIGH
    file_mapping                         boolean     FALSE
    fileio_network_adapters              string      
    filesystemio_options                 string      SETALL
    fixed_date                           string      
    gc_files_to_locks                    string      
    gcs_server_processes                 integer     0
    global_context_pool_size             string      
    global_names                         boolean     FALSE
    hash_area_size                       integer     0
    hi_shared_memory_address             integer     0
    hs_autoregister                      boolean     TRUE
    ifile                                file        
    instance_groups                      string      
    instance_name                        string      VADP
    instance_number                      integer     0
    instance_type                        string      RDBMS
    java_max_sessionspace_size           integer     0
    java_pool_size                       big integer 0
    java_soft_sessionspace_limit         integer     0
    job_queue_processes                  integer     2
    large_pool_size                      big integer 0
    ldap_directory_access                string      NONE
    license_max_sessions                 integer     0
    license_max_users                    integer     0
    license_sessions_warning             integer     0
    local_listener                       string      
    lock_name_space                      string      
    lock_sga                             boolean     FALSE
    log_archive_config                   string      
    log_archive_dest                     string      /JH0001/app/oracle/admin/VADP/arch
    log_archive_dest_1                   string      
    log_archive_dest_10                  string      
    log_archive_dest_2                   string      
    log_archive_dest_3                   string      
    log_archive_dest_4                   string      
    log_archive_dest_5                   string      
    log_archive_dest_6                   string      
    log_archive_dest_7                   string      
    log_archive_dest_8                   string      
    log_archive_dest_9                   string      
    log_archive_dest_state_1             string      enable
    log_archive_dest_state_10            string      enable
    log_archive_dest_state_2             string      enable
    log_archive_dest_state_3             string      enable
    log_archive_dest_state_4             string      enable
    log_archive_dest_state_5             string      enable
    log_archive_dest_state_6             string      enable
    log_archive_dest_state_7             string      enable
    log_archive_dest_state_8             string      enable
    log_archive_dest_state_9             string      enable
    log_archive_duplex_dest              string      
    log_archive_format                   string      VADP_%t_%s_%r.arc
    log_archive_local_first              boolean     TRUE
    log_archive_max_processes            integer     2
    log_archive_min_succeed_dest         integer     1
    log_archive_start                    boolean     FALSE
    log_archive_trace                    integer     0
    log_buffer                           integer     14272512
    log_checkpoint_interval              integer     0
    log_checkpoint_timeout               integer     1800
    log_checkpoints_to_alert             boolean     FALSE
    log_file_name_convert                string      
    logmnr_max_persistent_sessions       integer     1
    max_commit_propagation_delay         integer     0
    max_dispatchers                      integer     
    max_dump_file_size                   string      UNLIMITED
    max_enabled_roles                    integer     150
    max_shared_servers                   integer     
    nls_calendar                         string      
    nls_comp                             string      
    nls_currency                         string      
    nls_date_format                      string      
    nls_date_language                    string      
    nls_dual_currency                    string      
    nls_iso_currency                     string      
    nls_language                         string      AMERICAN
    nls_length_semantics                 string      BYTE
    nls_nchar_conv_excp                  string      FALSE
    nls_numeric_characters               string      
    nls_sort                             string      
    nls_territory                        string      AMERICA
    nls_time_format                      string      
    nls_time_tz_format                   string      
    nls_timestamp_format                 string      
    nls_timestamp_tz_format              string      
    object_cache_max_size_percent        integer     10
    object_cache_optimal_size            integer     102400
    olap_page_pool_size                  big integer 0
    open_cursors                         integer     1000
    open_links                           integer     4
    open_links_per_instance              integer     4
    optimizer_dynamic_sampling           integer     4
    optimizer_features_enable            string      10.2.0.4
    optimizer_index_caching              integer     0
    optimizer_index_cost_adj             integer     10
    optimizer_mode                       string      ALL_ROWS
    optimizer_secure_view_merging        boolean     TRUE
    os_authent_prefix                    string      ops$
    os_roles                             boolean     FALSE
    parallel_adaptive_multi_user         boolean     TRUE
    parallel_automatic_tuning            boolean     FALSE
    parallel_execution_message_size      integer     32768
    parallel_instance_group              string      
    parallel_max_servers                 integer     64
    parallel_min_percent                 integer     0
    parallel_min_servers                 integer     16
    parallel_server                      boolean     FALSE
    parallel_server_instances            integer     1
    parallel_threads_per_cpu             integer     2
    pga_aggregate_target                 big integer 2560M
    plsql_ccflags                        string      
    plsql_code_type                      string      INTERPRETED
    plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
    plsql_debug                          boolean     FALSE
    plsql_native_library_dir             string      
    plsql_native_library_subdir_count    integer     0
    plsql_optimize_level                 integer     2
    plsql_v2_compatibility               boolean     FALSE
    plsql_warnings                       string      DISABLE:ALL
    pre_11g_enable_capture               boolean     FALSE
    pre_page_sga                         boolean     FALSE
    processes                            integer     300
    query_rewrite_enabled                string      FALSE
    query_rewrite_integrity              string      enforced
    rdbms_server_dn                      string      
    read_only_open_delayed               boolean     FALSE
    recovery_parallelism                 integer     0
    recyclebin                           string      OFF
    remote_archive_enable                string      true
    remote_dependencies_mode             string      TIMESTAMP
    remote_listener                      string      
    remote_login_passwordfile            string      EXCLUSIVE
    remote_os_authent                    boolean     FALSE
    remote_os_roles                      boolean     FALSE
    replication_dependency_tracking      boolean     TRUE
    resource_limit                       boolean     FALSE
    resource_manager_plan                string      
    resumable_timeout                    integer     0
    rollback_segments                    string      
    serial_reuse                         string      disable
    service_names                        string      VADP
    session_cached_cursors               integer     20
    session_max_open_files               integer     10
    sessions                             integer     1105
    sga_max_size                         big integer 5G
    sga_target                           big integer 5G
    shadow_core_dump                     string      partial
    shared_memory_address                integer     0
    shared_pool_reserved_size            big integer 200M
    shared_pool_size                     big integer 0
    shared_server_sessions               integer     
    shared_servers                       integer     1
    skip_unusable_indexes                boolean     TRUE
    smtp_out_server                      string      
    sort_area_retained_size              integer     0
    sort_area_size                       integer     0
    spfile                               string      /JH0001/app/oracle/product/10.2.0.4/dbs/spfileVADP.ora
    sql92_security                       boolean     FALSE
    sql_trace                            boolean     FALSE
    sql_version                          string      NATIVE
    sqltune_category                     string      DEFAULT
    standby_archive_dest                 string      ?/dbs/arch
    standby_file_management              string      MANUAL
    star_transformation_enabled          string      TRUE
    statistics_level                     string      TYPICAL
    streams_pool_size                    big integer 0
    tape_asynch_io                       boolean     TRUE
    thread                               integer     0
    timed_os_statistics                  integer     0
    timed_statistics                     boolean     TRUE
    trace_enabled                        boolean     TRUE
    tracefile_identifier                 string      
    transactions                         integer     1215
    transactions_per_rollback_segment    integer     5
    undo_management                      string      AUTO
    undo_retention                       integer     2400
    undo_tablespace                      string      VADP_UNDO
    use_indirect_data_buffers            boolean     FALSE
    user_dump_dest                       string      /JH0001/app/oracle/admin/VADP/udump
    utl_file_dir                         string      
    workarea_size_policy                 string      AUTO
  • 44. Re: Help with Parallelization on our SPARC server...we are drowning!
    Taral Journeyer
    Currently Being Moderated
    Well this is not in parallel. So, may be something else is doing some other run on server. Or may be this would run in parallel at runtime.
    1. Is there parallel set at table level ?
    2. Some of the table are not analyzed .
    3. some predicates are applied very late so may be you can try sub query unnesting

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points